Advanced Guide: Creating a budget pacing report

Overview

This guide explains how to create a budget pacing report as a Certified Datasource to compare your campaign’s daily spend against its lifetime budget.

Prerequisites

Certified campaign_performance datasource exists
- See Tutorial: Building and refreshing Certified Datasources
ADL 2.0 - Refresh app exists and is running v2.7 or higher

Instructions

Upgrading refresh app to v2.7

1.) Ensure that your ADL 2.0 - Refresh app(s) version is running on v2.7 or higher. Re-run the app if necessary.

Creating a campaign budget pacing datasource

Before proceeding, ensure that a datasource or report doesn’t already exist for your customer in Alli. You can do this by querying:

SQL

select count(*) 
from {alli_client}.campaign_budget_pacing

in Data Explorer or the data warehouse environment of your choice. If it already exists, you may have to modify the datasource as necessary.

1.) Create a Google Sheet with the following headers:

Header	Description	Notes
`platform`	The ad platform in which the campaign is running	Ensure that the `platform` and `campaign_name` match the values seen in the reports. You can verify this by running the following query in Data Explorer: SQL `select distinct platform, campaign_name from {alli_client}.campaign_performance where campaign_name = '{campaign name}'`
`campaign_name`	The name of the campaign
`campaign_budget_start_date`	The start date of the campaign budget	n/a
`campaign_budget_end_date`	The end date of the campaign budget	n/a
`campaign_lifetime_budget`	The budget amount of the campaign to be allocated equally across the days between the start and end dates (inclusive)	No special characters like `#` or `N/A` are allowed. `campaign_lifetime_budget` should be set to column-type `numbers only` when creating an Alli data source
`channel`	The media channel of the campaign	Not required - can be blank
`label`	A label for the campaign	Not required - can be blank

E.g., a Google Sheet with budget/pacing data for two Facebook Ads campaigns:

Please make sure your Google Sheet has at least 1 valid row following the above rules before processing to the next steps, otherwise it will error out.

2.) Create a Google Drive datasource named campaign_budget_pacing and connect the Google Sheet from the previous step.

The schema of your datasource should match the your Google Sheet, e.g., based on the above Google Sheet, the datasource’s Define your data tab should look something like:

Select Save and Load Data to save and load your datasource. Validate that the corresponding rows were loaded via the datasource Audit section.

3.) Once the datasource is populated, the budget_pacing_performance report should automatically be populated with the daily allocated budget amounts. See example below:

E.g., if we look at the campaign, KBUS_DTC_PSM_Look_Product_Traff_06.01.2024 in the Budget Pacing Google Sheet, we can observe the following:

The total budget amount is $250,000
The budget is set to be spent from 01 Jun 2024 to 12 Jun 2024 (12 days in total)

..which means that the daily budget is roughly $20,833.33 ($250,000 / 12 days)

This should manifest in the budget_pacing_performance report with the proper daily budget amount per day:

SQL

select *
from playground_core.budget_pacing_performance
where campaign_name ='KBUS_DTC_PSM_Look_Product_Traff_06.01.2024'

If the report does not exist, ensure that the Alli Data Library 2.0 - Refresh app(s) is running on v2.7 or higher.

If the report exists, but is not populated with the expected values, ensure that the platform and campaign_name values for the campaign in question matches the values seen in the campaign_performance report.