2. Singling Out Over-Pacing 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: Alli Data

1. Create your over-pacing SQL

   1. Login to Alli Data and select the client you want to create an query 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 Example

Context:

• {client_name.example_connected_fb} should correspond to the name of the SQL View/Report you created earlier (in the Connecting Budget to Spend step). Replace this with your client name and the name you gave your report

• select * will pull in all the columns from the report you name in your from statement

• We add Percent Spent by dividing total_spend_to_date by budget. If either of these columns have different names in your view, switch out those names

• Replace the {#} with whatever threshold you are looking for

  • where percent_spend > looks at our newly created columns, and only pulls out rows where the Percent Spend is greater than or equal to the number we pass.

  • To adjust that, for example to see campaigns that are overpaced: utilize where percent_spend >= 100. Or to filter to campaigns with greater than or equal to 80%, utilize where percent_spend >= 80

  • >= is inclusive, so greater than or equal to our threshold. You can also utilize > to only select campaigns with greater than the threshold you set

select *,
        (total_spend_to_date / budget) * 100 as percent_spend 
from {client_name.example_connected_fb}
where percent_spend >= {#}

Live Example with real datasources

select *,
        (total_spend_to_date / budget) * 100 as percent_spend 
from client_name.example_connected_fb
where percent_spend > 100

Instructions: Data Studio

1. Create a blank data studio report

   1. Navigate to Datastudio reporting and select Create → Report

      

   2. Select Bigquery as your data source type

      

   3. On the left, select Custom Query and choose your Bigquery project titled alli-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

   7. Calculate Percent Spend

      1. Click on your chart, and on the bottom far right, select Add Field

         

      2. In the pop-up, rename your new value, and place the formula to calculate Percent Spend in the open box (total_spend_to_date / budget) using the column names you can see on the left

         

      3. Click Save and then Done to finalize adding the new column

      4. Now pull that new column into the Dimension section & click on the edit button to the left of the new column

         

      5. Select Type → Number → Percent to change the Percent Spend column from being displayed as a number to a percent (You can do this for any metric column, as well as change it’s visual name in the chart, or even how it is aggregated)

         

      6. To filter to only over-pacing campaigns, with the chart selected, scroll to the bottom of the right side bar and select Add A Filter

         

      7. Select include and then choose your newly created Percent Spend column

         

      8. In the operator options, select either Greater than or equal to (>=) or Greater than (>) according to your preference

      9. Finally, select the threshold you would like to set. Remember we are filtering the original value of Percent Spend, not the visual %, so to filter greater than 100%, we use > 1. Select Save when you are complete

And you’re done! You can use that filter on any chart in the DataStudio report, or create new ones for different charts (ie creating a chart of >= 80%, a chart of >= 100%, and a chart of <= 10% spent)