In this post I’ll be sharing how to combine various ad data sources into a single BigQuery table using Dataform in Google Cloud Platform. This uses fake dummy ad data.

Objectives

Combine data from the following data sources into four tables;

• Display (The Trade Desk and AdRoll)
• Search (Google Ads and Microsoft Ads)
• Social (Facebook, Instagram, TikTok, and Pinterest)
• All Ads Combined (Search, Display and Social)

Then to visualize the data in Looker Studio.

If you’d like to table a look at the final finished product in Looker Studio it’s below here:

Step 1: Create BigQuery tables for all Data Sources

In this simulated exercise I created a BigQuery table for each of the separate data sources. I uploaded a CSV filled with dummy ad data to each table to create it.

I created a different table for each ad data source within the Simulated_Ad_Data datset.

Step 2: Create a Dataform Repository and Workspace

This step involves creating a Dataform repository and workspace for the various SQL queries we will use to combine the ad data tables.

First you must create a Dataform repository.

And then a workspace. In this example I’m naming both of them “simulated_ads”.

Step 3: Creating a combined Display Ads table

This process involves creating a combined table in BigQuery with our Display data. In this example it’s the Trade Desk and Ad Roll.

I used this SQL below in Dataform. I saved it as:  combined_display_ads.sqlx

CREATE OR REPLACE TABLE `your-bigquery.Simulated_Ad_Data.combined_display_ads` AS

SELECT 
  'adroll' AS platform,
  `Campaign EID` AS campaign_id,
  `Campaign Name` AS campaign_name,
  `Ad Group EID` AS ad_group_id,
  `Ad Group Name` AS ad_group_name,
  `Ad EID` AS ad_id,
  `Ad Name` AS ad_name,
  Date,
  Impressions,
  Clicks,
  Spend,
  Conversions,
  Revenue,
  ROAS,
  `Video Fifty Percent` AS video_50_percent,
  `Video Seventy Five Percent` AS video_75_percent,
  `Video Hundred Percent` AS video_100_percent
FROM `your-bigquery.Simulated_Ad_Data.adroll_data`

UNION ALL

SELECT 
  'tradedesk' AS platform,
  SAFE_CAST(`Campaign ID` AS INT64) AS campaign_id,
  `Campaign Name` AS campaign_name,
  SAFE_CAST(`Ad Group ID` AS INT64) AS ad_group_id,
  `Ad Group Name` AS ad_group_name,
  SAFE_CAST(`Creative ID` AS INT64) AS ad_id,
  `Creative Name` AS ad_name,
  `Start Date` AS Date,
  Impressions,
  Clicks,
  Spend,
  `01 Click Conversion` AS Conversions,
  NULL AS Revenue,
  NULL AS ROAS,
  NULL AS video_50_percent,
  NULL AS video_75_percent,
  NULL AS video_100_percent
FROM `your-bigquery.Simulated_Ad_Data.tradedesk_data`;

Below you can see that it ran successfully and created the table in BigQuery.

This is what the table schema looks like.

This is what the actual data looks like.

Step 4: Creating a combined Search Ads table

The next step was to combine Google Ads and Microsoft ads tables into one unified Search table.

I did this using SQLX code in Dataform. I called it  combined_search_ads.sqlx. The code is below.

CREATE OR REPLACE TABLE `your-bigquery.Simulated_Ad_Data.combined_search_ads` AS

SELECT 
  'microsoft' AS platform,
  `Campaign ID` AS campaign_id,
  `Campaign Name` AS campaign_name,
  CAST(NULL AS INTEGER) AS ad_id,
  `Ad Name` AS ad_name,
  Date,
  Impressions,
  `Outbound Clicks` AS Clicks, 
  Spend AS spend,
  Conversions
FROM `your-bigquery.Simulated_Ad_Data.microsoft_ads`

UNION ALL

SELECT 
  'google' AS platform,
  `Campaign ID` AS campaign_id,
  `Campaign Name` AS campaign_name,
  `Ad ID` AS ad_id,
  `Ad Name` AS ad_name,
  Date,
  Impressions,
  Clicks,
  Cost AS spend,
  Conversions
FROM `your-bigquery.Simulated_Ad_Data.google_ads`

I ran the code and it resulted in the messages below.

This is what the combined Search ads schema looks like.

This is what the data looks like in the table.

Step 5: Creating a combined Social Ads table

Next we needed to create a combined table for Social Ads. Used this code below to combined data from Facebook, Instagram, TikTok and Pinterest.

CREATE OR REPLACE TABLE `your-bigquery.Simulated_Ad_Data.combined_social_ads` AS

SELECT
  'facebook' AS platform,
  `Campaign Name` AS campaign_name,
  `Campaign ID` AS campaign_id,
  `Ad Name` AS ad_name,
  `Ad ID` AS ad_id,
  Date AS date,
  Impressions AS impressions,
  Clicks AS clicks,
  `Amount Spent` AS spend,
  Conversions AS conversions
FROM `your-bigquery.Simulated_Ad_Data.facebook_ads`

UNION ALL

SELECT
  'instagram' AS platform,
  `Campaign Name` AS campaign_name,
  `Campaign ID` AS campaign_id,
  `Ad Name` AS ad_name,
  `Ad ID` AS ad_id,
  Date AS date,
  Impressions AS impressions,
  Clicks AS clicks,
  `Amount Spent` AS spend,
  Conversions AS conversions
FROM `your-bigquery.Simulated_Ad_Data.instagram_ads`

UNION ALL

SELECT
  'pinterest' AS platform,
  `Campaign Name` AS campaign_name,
  `Campaign ID` AS campaign_id,
  `Ad name` AS ad_name,
  `Organic Pin ID` AS ad_id,
  Date AS date,
  Impressions AS impressions,
  `Outbound Clicks` AS clicks,
  Spend AS spend,
  Conversions AS conversions
FROM `your-bigquery.Simulated_Ad_Data.pinterest_ads`

UNION ALL

SELECT
  'tiktok' AS platform,
  `Campaign Name` AS campaign_name,
  `Campaign ID` AS campaign_id,
  `Ad Name` AS ad_name,
  `Ad ID` AS ad_id,
  Date AS date,
  Impressions AS impressions,
  Clicks AS clicks,
  `Total Cost` AS spend,
  Conversions AS conversions
FROM `your-bigquery.Simulated_Ad_Data.tiktok_ads`

It created a new table called combined_social_ads.

This is what the combined social ads table schema looks like.

This is what the data inside the table looks like.

Step 6: Creating a Combined All Ads Table

After creating three new tables in BigQuery, next up was to combine those three tables into one table.

To do that I combined the combined display table, combined search table, and combined social table using this code below.

CREATE OR REPLACE TABLE `your-bigquery.Simulated_Ad_Data.combined_all_ads` AS

SELECT 
  platform,
  campaign_name,
  campaign_id,
  ad_name,
  ad_id,
  Date AS date,
  Impressions AS impressions,
  Clicks AS clicks,
  Spend AS spend,
  Conversions AS conversions,
  'Display' AS Channel
FROM `your-bigquery.Simulated_Ad_Data.combined_display_ads`

UNION ALL

SELECT 
  platform,
  campaign_name,
  campaign_id,
  ad_name,
  ad_id,
  Date AS date,
  Impressions AS impressions,
  Clicks AS clicks,
  spend,
  Conversions AS conversions,
  'Search' AS Channel
FROM `your-bigquery.Simulated_Ad_Data.combined_search_ads`

UNION ALL

SELECT 
  platform,
  campaign_name,
  campaign_id,
  ad_name,
  ad_id,
  date,
  impressions,
  clicks,
  spend,
  conversions,
  'Social' AS Channel
FROM `your-bigquery.Simulated_Ad_Data.combined_social_ads`

So below you can see the combined_all_ads.sqlx code ran and created the table.

This is what the combined all ads schema looks like.

This is what the combined all ads data looks like

We know also have all four combined dashboard SQL codes visible in Dataform > Compiled Graph.

Step 7: Visualizing all the Data in Looker Studio

And finally what we can do is visualize all that data in Looker Studio!

So to do that we use the native BigQuery connector.

In this case I’ve decided to add in all four combined data sources.

And they can live happily together like a family in our Looker Studio dashboard!

Hope you found this useful!

My Other Blog Posts

if you liked this blog post you might enjoy some of my other posts