3. Finding "Gaps" in Tracked Campaigns

These views build upon the views/SQL created to connect Creative / Budget Sheet information to Facebook spend. You can see instructions on how to create that base view here.

Instructions

Create your “gap” SQL

1. Login to Alli Data and select the client you want to create an action for

2. Navigate to Reports → Explorer and select New Data Report

   

3. Give your report a Name and update the Report Type to Custom Redshift

   

4. Copy and paste the SQL Example in from below and update anything within and including { } to match your client settings

5. Once your SQL is updated, click Publish to update the query

6. Click Preview to ensure the output is as expected

SQL (Alli Data)

Context

• {clientname.facebook_datasource} references the Facebook data source you created in the Connecting Budget to Spend step

• {clientname.creative_budget_sheet} references the creative / budget sheet data source you created in the Connecting Budget to Spend step

• column names should match what is available in the above. So, for example, if the campaign name is called creative_name in your creative / budget sheet, replace the campaign_name in line 10 with creative_name

Query

select campaign_name from {clientname.facebook_datasource}
    where date_start = current_date
    group by campaign_name
    having sum(spend) > 0
 except 
    select campaign_name from {clientname.creative_budget_sheet}

Live Example Using Fake Datasources

select campaign_name from client_name.facebook
    where date_start = current_date
    group by campaign_name
    having sum(spend) > 0
 except 
    select campaign_name from client_name.example_budget_sheet

Utilize SQL In DataStudio

Instructions

Brand New Blank DataStudio

Create a blank data studio report

1. Navigate to Datastudio reporting and select Create → Report

   

2. Select Bigquery as your datasource type

   

3. On the left, select Custom Query and choose your Bigquery project titled alli-{your bq name}.

4. In the Edit Custom Query box, copy & paste the exact SQL you created originally in the Connect Budget to Spend step earlier. We’ll be applying the filters and creating the new Percent Spend column in Datastudio using it’s native features; much easier!

   

5. After pasting your SQL, click Add, and in the pop-up that follows select Add to Report

   

6. Now you can rename your report, and drag & drop columns according to what you’d like to view

   

   1. Drag & drop your date column into the Date Range Dimension field

   2. Drag & drop your non-numerical columns (like campaign_name) into the Dimension section

   3. Drag & drop your numerical columns (spend to date & budget) into the Metric section

Adding to Existing DataStudio Report

1. Add a new datasource

   1. On the top navigation bar, select Manage Resources → Manage added data sources

      

   2. Select Add A Data Source

      

   3. Select Bigquery as your datasource type

      

   4. On the left, select Custom Query and choose the Moroch Bigquery project titled alli-data-moroch.

   5. In the Edit Custom Query box, copy & paste the Bigquery SQL below

      

   6. After pasting your SQL, click Add, and in the pop-up that follows select Add to Report

      

   7. (Optional) Your data source has been added and now you can rename your data sources to make it easier to differentiate (you’ll notice both data sources in this example are named Bigquery) by clicking the Edit button & renaming at the top left

      

      

   8. After renaming, select Close and you can begin leveraging this new data source. You can copy existing charts and switch out the data source in the Data source section of any chart. Or you can select Add a Chart and create a brand new chart

      

      

SQL (Bigquery / Datastudio)

Context

• {clientname.facebook_datasource} references the Facebook data source you created in the Connecting Budget to Spend step

• {clientname.creative_budget_sheet} references the creative / budget sheet data source you created in the Connecting Budget to Spend step

• column names should match what is available in the above. So, for example, if the campaign name is called creative_name in your creative / budget sheet, replace the campaign_name in line 10 with creative_name

select distinct campaign_name
from {clientname.facebook_datasource}
where campaign_name in 
(
select campaign_name from {clientname.facebook_datasource}
    where cast(date_start as date) = current_date
    group by campaign_name
    having sum(spend) > 0
 except distinct
    select campaign_name from {clientname.creative_budget_sheet}
)

Live Example with Fake Datasources

select distinct campaign_name
from client_name.facebook
where campaign_name in 
(
select campaign_name from client_name.facebook
    where cast(date_start as date) = current_date
    group by campaign_name
    having sum(spend) > 0
 except distinct
    select campaign_name from client_name.example_budget_sheet
)