Skip to main content
Feedback

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.

You can build 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

Step 1: Setting up a Data Integration account

note

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

Connect to your Snowflake account using Snowflake Partner Connect.

  1. Log in to "Snowsight" and navigate to Data Products > Partner Connect.
  2. Find for Data Integration.
  3. Click the Data Integration tile. A Connect to Rivery page 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: Create a Snowflake target database and schemas

You must create a dedicated database and schemas in Snowflake to store the marketing data. This ensures a structured approach 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 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: Configure variables and run the data workflow

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

  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
  1. After configuring the variables, go back to the kit's Rivers and open the Master Logic - Build All Channels Fact Table river.
  2. 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.
  3. 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 create a dashboard to visualize your marketing performance.

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

Creating dashboard filters

Filters let you explore your data interactively.

  1. Click the Filters icon.
  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 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 button to view 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 use the filters to perform cross-channel analytics and gain a complete view of your B2B paid ad performance.

On this Page