Marketing data management

B2B marketing teams invest a significant portion of their budget into digital ads spread across multiple platforms (Google, Bing, YouTube, Facebook, LinkedIn, and X). This fragmentation makes unified campaign analysis challenging. With Snowflake and Data Integration, you can centralize data pipelines, automate transformations, and build dashboards to analyze marketing performance efficiently.

This guide walks through the end-to-end process of building a cross-channel ads analytics solution using Data Integration and Snowflake.

Prerequisites

• A Snowflake account with SYSADMIN privileges
• Access to ad accounts: Google Ads, Bing Ads, Facebook Ads, LinkedIn Ads, X Ads

This guide uses sample data, but you can modify the process for your real accounts.

Step 1: Setting up a Data Integration account

note

If you already have a Data Integration account, you can skip this step.

The easiest way to connect to your Snowflake account is by using Snowflake Partner Connect.

1. Log in to Snowsight and navigate to Data Products > Partner Connect.
2. Search for Data Integration.
3. Click the Data Integration tile. A Connect to Rivery screen appears, pre-populated with your Snowflake details.
4. Click Connect and Activate. The Data Integration console prompts you to create your account and set a password for use with Data Integration. This action sets up both your Data Integration account and the Snowflake target connection.

Step 2: Creating a Snowflake target database and schemas

You must create a dedicated database and schemas in Snowflake to store the marketing data. This ensures a clean and organized structure for your analytics solution.

1. Open a new SQL worksheet in Snowsight.
2. Run the following SQL query in the Snowflake console:

   begin;
   
   /* switch to the sysadmin role, which has privileges to create databases in an account. */
   USE ROLE sysadmin;
   
   /* Create database and schemas for marketing data, this step is optional */
   CREATE DATABASE MARKETING_QUICKSTART;
   USE DATABASE MARKETING_QUICKSTART;
   
   CREATE SCHEMA FACEBOOK_ADS;
   CREATE SCHEMA GOOGLE_ADS;
   CREATE SCHEMA BING_ADS;
   CREATE SCHEMA LINKEDIN_ADS;
   CREATE SCHEMA X_ADS;
   CREATE SCHEMA DIGITAL_AD_ANALYTICS;
   
   /* grant the Data Integration Role access to database */
   grant CREATE SCHEMA, MONITOR, USAGE
   on database MARKETING_QUICKSTART
   to role PC_RIVERY_ROLE;
   
   /* Grant access to all existing sachems on the database */
   grant ALL on all SCHEMAS IN DATABASE MARKETING_QUICKSTART to ROLE PC_RIVERY_ROLE;
   
   commit;

Step 3: Using the Data Integration Paid Ads kit

1. In the Data Integration console, click Kits from the left-hand menu.
2. Search for the B2B Paid Ads Campaigns Performance - with Sample Data.
3. Click Preview to view the kit details and then click the Use Kit. The prompt asks you to set up your connections. For this example, you will connect to a public PostgreSQL database that contains sample data.
4. Click the Add new + next to the PostgreSQL connection.
5. Enter the following connection details:
   • Source (Postgres sample DB)
     • Host: rds-pg-snowflake-lab-demo-us-east-2.cyaie4xhe0v4.us-east-2.rds.amazonaws.com
     • Port: 5432
     • DB: demoSnowflakeLab
     • Username: snowflake
     • Password: Sn0wflakeQuickstart$
   • Target (Snowflake)
6. For the Snowflake connection, choose your existing connection from the drop-down list
7. Click Next. This action imports all pipelines from the kit into your Data Integration account.

Step 4: Configuring variables and run the data workflow

Before running the workflow, you must set up variables to configure the target database and schemas dynamically.

1. In the Data Integration console, navigate to the Variables from the left-hand menu.
2. Click Add Variable and create the following variables with the specified values. These should match the database and schemas you created in Snowflake.

   Variable Name: DATABASE_MARKETING Value: MARKETING_QUICKSTART
   
   Variable Name: SCHEMA_MARKETING Value: DIGITAL_AD_ANALYTICS
   
   Variable Name: SCHEMA_FACEBOOK Value: FACEBOOK_ADS
   
   Variable Name: SCHEMA_LINKEDIN Value: LINKEDIN_ADS
   
   Variable Name: SCHEMA_X Value: X_ADS
   
   Variable Name: SCHEMA_BING Value: BING_ADS
   
   Variable Name: SCHEMA_GOOGLE Value: GOOGLE_ADS
3. After configuring the variables, go back to the kit's Rivers and open the Master Logic - Build All Channels Fact Table river.
4. Click Run to execute the workflow. This action triggers the data ingestion pipelines, which pull data from the different ad channels into your Snowflake database. The final step of the workflow executes an SQL query to model and unify the data into a single table.
5. Under the Activities menu, you can monitor the workflow's progress and check for successful execution.

Step 5: Setting up a Snowflake dashboard to analyze the data

With the data successfully loaded and modeled in Snowflake, you can now create a dashboard to visualize your marketing performance.

1. In the Snowsight, go to Dashboards from the left-hand navigation menu.
2. Click + Dashboard and give it a name.

Creating dashboard filters

Filters allow you to explore your data interactively.

1. Click the Filters icon on the top left.
2. Click + Filter to create the following two filters:

   • Filter 1: Channel

     • Display Name: Channel
     • SQL Keyword: :channel
     • SQL Query: select distinct CHANNEL from MARKETING_QUICKSTART.DIGITAL_AD_ANALYTICS.ALL_CHANNELS_FACT_TABLE
     • Enable the "Multiple values can be selected" and "Include an "All" option" toggles.

   • Filter 2: Channel Type

     • Display Name: Channel Type
     • SQL Keyword: :channeltype
     • SQL Query: select distinct channel_type from MARKETING_QUICKSTART.DIGITAL_AD_ANALYTICS.ALL_CHANNELS_FACT_TABLE

Creating dashboard tiles

Tiles are the individual charts and tables on your dashboard. To create your tiles, use the following SQL queries.

1. Click + New Tile > From SQL Worksheet.

2. Copy and paste the SQL for each tile below, set the chart type, and click the play to see the results.

   • Tile 1: Impressions

     select
     CHANNEL,
     sum(impressions) as Impressions
     from
     MARKETING_QUICKSTART.DIGITAL_AD_ANALYTICS.ALL_CHANNELS_FACT_TABLE
     where
     channel = :channel
     and date = :daterange
     and channel_type = :channeltype
     group by
     (CHANNEL)

• Tile 2: Clicks

       select
    CHANNEL,
       sum(clicks) as Clicks
       from
    MARKETING_QUICKSTART.DIGITAL_AD_ANALYTICS.ALL_CHANNELS_FACT_TABLE
       where
    channel = :channel
       and date = :daterange
       and channel_type = :channeltype
       group by
    (CHANNEL)

• Tile 3: Spend

       select
    CHANNEL,
       Round(sum(spend)) as Spend
       from
    MARKETING_QUICKSTART.DIGITAL_AD_ANALYTICS.ALL_CHANNELS_FACT_TABLE
       where
    channel = :channel
       and date = :daterange
       and channel_type = :channeltype
       group by
    (CHANNEL)

• Tile 4: Clicks by Channel over time

       select
    CHANNEL,
       sum(clicks) as Clicks,
       DATE
       from
    MARKETING_QUICKSTART.DIGITAL_AD_ANALYTICS.ALL_CHANNELS_FACT_TABLE
       where
    channel = :channel
       and date = :daterange
       and channel_type = :channeltype
       group by
    (CHANNEL, DATE)

• Tile 5: Campaign Results (Table)

 select
 channel_type,
 CHANNEL,
 CAMPAIGN,
 sum(impressions) as Impressions,
 sum(clicks) as Clicks,
 sum(spend) as Spend
 from
 MARKETING_QUICKSTART.DIGITAL_AD_ANALYTICS.ALL_CHANNELS_FACT_TABLE
 where
 channel = :channel
 and date = :daterange
 and channel_type = :channeltype
 group by
 (CHANNEL, CAMPAIGN, channel_type)
 order by CHANNEL_TYPE asc

After creating all the tiles, you can arrange them on the dashboard to create your preferred layout. You can now use the filters to perform cross-channel analytics and gain a complete view of your B2B paid ad performance.