Customers looking to use Snowflake for marketing use cases initially face a significant challenge: it is difficult to import all relevant marketing data into Snowflake and structure it in a unified format that downstream applications can easily utilize to power data-driven marketing. This starter solution tackles this challenge by offering an integrated application that unifies data sets from different Connectors and Marketing Data providers.

In this example we are adding support for:

• Fivetran / Facebook Ads
• Omnata / LinkedIn Ads

This solution was inspired by how Snowflake runs its own end-to-end Marketing workflows entirely on top of the Snowflake Marketing Data Cloud.

In the fast-evolving marketing landscape, the emphasis on data-driven strategies has become more pronounced than ever. A significant trend is the increase in Martech investments, with 63% of Chief Marketing Officers (CMOs) planning to increase their spending within the next 12 months. Such investments are crucial for enhancing customer engagement, refining marketing strategies, and driving sales through advanced data analysis. The high ROI that businesses achieve from data-driven personalization also highlights its importance. Reports indicate that enterprises see returns of 5 to 8 times on their marketing budgets, which demonstrates the value of personalized marketing in boosting conversion rates, enhancing customer loyalty, and increasing revenue.

Additionally, the industry is shifting towards first-party data ownership, a move propelled by the deprecation of third-party cookies. This shift is essential for maintaining direct customer relationships and adapting to changing privacy norms. The promise of generative AI and the understanding that an effective AI strategy requires a robust data strategy have spurred efforts to centralize marketing data within Snowflake. Organizations aim to organize data into standard schemas that Large Language Models (LLMs) can understand, employing these models in innovative ways to personalize content and predict customer behavior. Two types of first-party data are pivotal in these efforts: Customer 360 Data and Campaign Intelligence. The former strives to provide a holistic view of the customer by integrating and managing comprehensive data. In contrast, Campaign Intelligence focuses on data related to marketing campaigns, aiming to optimize performance and strategy. These elements are fundamental to successful data-driven marketing, underscoring the need for sophisticated data management and analytics capabilities.

Context

As described in the diagram below, the two Data Foundation use cases in this starter lay the groundwork to support the two Marketing Execution use cases: Planning & Activation, and Measurement.

As described in the diagram below, the two Data Foundation use cases in this starter lay the groundwork to support the two Marketing Execution use cases: Planning & Activation, and Measurement.

More specifically, this solution covers Data Ingestion, Semantic Unification, and based Analytics use cases for Customer 360 and Campaign Intelligence data.

Architecture

This demo consists of a native application that provides a framework to easily unify marketing data sources and then leverage LLM functions to explore that data and get valuable business intelligence information from the data that was ingested.

What You Will Build

• A Native Application that ingests data from different sources and unifies it into a single source of truth for Marketing Data.

What You Will Learn

• How to build a native application in Snowflake and how to deploy the same to your account using Snow CLI quickly.
• How to use Snowpark Python to build a data pipeline that ingests data from different sources and unifies it into a single source of truth for Marketing Data.

Prerequisites

• Go to the Snowflake Account sign-up page and register for a free account. After registration, you will receive an email containing a link that will take you to Snowflake, where you can sign in.
• A Snowflake Role with access to create Compute Pools, create Warehouses and Databases.
• A GitHub Account.
• VSCode Installed.
• Snow CLI Installed.
• Docker Desktop Installed.
• Python Installed.

Clone GitHub repository

Clone the git repo to your local machine:

git clone https://github.com/Snowflake-Labs/sfguide-marketing-data-foundation-starter-v3

Create a connection

snow connection add

Refer to the following below to create a connection:

Enter connection name: marketing_demo_v3_conn
Enter account name: ******
Enter user: put your username here
Enter password: [optional]
Enter role: accountadmin
Enter warehouse: compute_wh
Enter database: snowflake
Enter schema: account_usage
Enter host: [optional]
Enter port: [optional]
Enter region: [optional]
Enter authenticator: [optional]
Enter private key file: [optional]
Enter token file path: [optional]
Wrote new connection marketing_demo_v3_conn to /Users/******/.snowflake/connections.toml

Let's set the connection to the default connection:

snow connection set-default marketing_demo_v3_conn

To review the new created connection use:

snow connection list

Refer to the desired output below:

+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| connection_name        | parameters                                                                                                                                                            | is_default |
|------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------|
| marketing_demo_v3_conn | {'account': '******', 'user': '******', 'password': '****', 'database': 'snowflake', 'schema': 'account_usage', 'warehouse': 'compute_wh',                            | True       |
|                        | 'role': 'accountadmin'}                                                                                                                                               |            |
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+

Docker Desktop

Ensure that Docker Desktop is running and that you're signed in to your Docker account.

Bypass MFA

Create a Worksheet in Snowsight and run the following command to disble MFA for 30 minutes. This statement is to allow the many cells in the Jupyter Notebook to run successfully without waiting for MFA approval from the user.

ALTER USER <replace with your user> SET MINS_TO_BYPASS_MFA = 30;

To use VSCode

Open the project with VSCode and select the deployment.ipynb file from the File explorer. Then, select the virtual environment you created as the execution kernel for this notebook.

Click on the Run All button to execute all the code cells described in this notebook, or you can execute each cell manually.

After the execution of this notebook you will see some new assets created in your Snowflake account. The application and the application package should be listed in your databases list.

Open the Native App

In the Data Products/ Apps section, you will see the recently created Marketing Data Foundation native application:

After you deploy this native app in your account, navigate to the Data Products / Apps section in Snowsight and open the
Marketing Data Foundation application.

Take some time to grant privileges to IMPORTED PRIVILEGES ON SNOWFLAKE DB, CREATE COMPUTE POOL, BIND SERVICE ENDPOINT, andCREATE WAREHOUSE. Afterward, click on Update Privileges and Launch app.

1. Select the Data provider

To execute the solution click Add New Source at the top of the page and open the Data Models & Mappings section in the application's sidebar menu. You will see the list of supported Data source providers. For this version only Facebook and LinkedIn are supported.

Select any of those options to continue with the solution. Sample data are provided for both options so it is not a requirement to have Fivetran or Omnata installed and configured.

The provided sample data emulates the structure of the raw data generated by both connectors.

For this solution, only the combinations of Fivetran/Facebook and Linked/Omnata are supported.

2. Configure the connection

After you select any of the Data providers options you will continue to the Connection configuration.

These steps will provide instructions on how to bring the data to Snowflake or connect it with data already in Snowflake.

Since we are going to use the sample data, you can continue to the Link data already in Snowflake option.

Depending on the platform selected in the previous step, please select either of these options

Facebook:

• DATABASE: FIVETRAN_CONNECTOR_DEMO SCHEMA: FACEBOOK_RAW

LinkedIn:

• DATABASE: OMNATA_CONNECTOR_DEMO SCHEMA: LINKEDIN_RAW

When you click on the connect option, the application will verify you have access to the sample data and will show you the list of tables in the selected schema.

3. Explore and Apply Mappings

This application applies a set of predefined mappings to generate a unified marketing data model that can be used for business intelligence over the data that was ingested.

You can explore or apply the mappings using the options provided in this screen:

The mappings are shown using a custom component that highlights the columns used from the source data and the target column in the unified data model:

4. Visualizations

After you apply the mappings the application will generate several schemas inside the application's database. The unified data model will be used to generate some basic visualizations that provide insights about the ingested data:

The unified data model is stored in the Database MARKETING_DATA_FOUNDATION_STARTER_V3_USERNAME (replace with your username) and the Schema TARGET.

5. AI Functions

The application also provides AI Functions that can be explored:

Data Quality

Provides access to a set of predefined Snowflake Notebooks with some sample Customer 360 cases.

AI Assistant Bot

Under the AI Assistant option you will find a Chatbot that allows you to ask questions related to either Data Engineering using your ingested data as input.

Replace with the actual value of your application.

DROP TABLE IF EXISTS MARKETING_DATA_FOUNDATION_STARTER_V3_<USERNAME>.TARGET.DIM_ACCOUNT_FIVETRAN_FACEBOOK;
DROP TABLE IF EXISTS MARKETING_DATA_FOUNDATION_STARTER_V3_<USERNAME>.TARGET.DIM_AD_GROUP_FIVETRAN_FACEBOOK;
DROP TABLE IF EXISTS MARKETING_DATA_FOUNDATION_STARTER_V3_<USERNAME>.TARGET.DIM_CAMPAIGN_FIVETRAN_FACEBOOK;
DROP TABLE IF EXISTS MARKETING_DATA_FOUNDATION_STARTER_V3_<USERNAME>.TARGET.METRICS_FIVETRAN_FACEBOOK;

DROP TABLE IF EXISTS MARKETING_DATA_FOUNDATION_STARTER_V3_<USERNAME>.TARGET.DIM_AD_GROUP_OMNATA_LINKEDIN;
DROP TABLE IF EXISTS MARKETING_DATA_FOUNDATION_STARTER_V3_<USERNAME>.TARGET.DIM_CAMPAIGN_OMNATA_LINKEDIN;

DROP TABLE IF EXISTS MARKETING_DATA_FOUNDATION_STARTER_V3_<USERNAME>.TARGET.CAMPAIGN_PERFORMANCE;

Use this cleanup script to remove all objects created in this Quickstart.

Overview

Congratulations! You have successfully learned how to easily build an end-to-end Native Application and load sample data.

What You Learned

• How to host and build a basic native app for Marketing Campaign Intelligence data.
• Generate a set of views that aggregate data from Marketing providers like LinkedIn and Facebook.
• Generate quick summaries and Charts using that data to create an integrated dashboard using Streamlit.

Resources

Want to learn more about the tools and technologies used by your app? Check out the following resources:

• Source Code on GitHub
• Snowpark Python Developer Guide
• Snowpark Guide for Data Engineers