1. Connecting Budget to Spend

In this example, we will be connecting Facebook campaigns, but this could be leveraged for other platform, as long as you can create a spend datasource for that platform in Alli Data.

Instructions

Data Source Set-Up

1. Set up your Creative/Budget Sheet Datasource

   1. In your Creative/Budget Sheet, your Campaign or AdSet name must be included as a column.

      1. You can still connect them without, but it would require much more in-depth SQL than what follows. So we recommend adding a column if it does not already exist, and placing the campaign name within it to allow the connection between your creative budget sheet and Facebook to be simple & easy.

   2. Login to Alli Data and select the client you want to create a data source for

   3. Navigate to Reports, select Add New Datasource

      

   4. Select the data source type (Google Sheets, Smartsheet, Airtable, etc). Give your data source a new name.

      1. Make sure start & end date columns are separate (one start date, one end date) and are pulled in as timestamp data types

         

      2. Ensure budget columns can be pulled in as numbers (ie remove extra commas or $ in the fields)

         

2. Set up your Platform Spend Datasource

   1. Login to Alli Data and select the client you want to create a datasource for

   2. Navigate to Reports, select Add New Datasource, and select the datasource type: In this example, select Facebook Ads

   3. Ensure you include the following columns:

      

      1. Date (Start Date & End Date are automatically selected her, they do not reference the start & end date of the campaign or adset, just the date the data was pulled/ingested)

      2. Account Id & Account Name

      3. Campaign Id & Campaign Name

      4. (optional) Adset Id & Adset Name (if your tracking budgets down to the adset level, and have start/end dates and budget broken out by adset in your creative/budget sheet)

      5. Spend

      6. (optional) other metrics like Impressions, clicks, etc.

SQL View Set-Up

1. Create your base 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 Example

Context

• Depending on whether you are tracking budgets to the campaign or adset level, you’ll select the appropriate SQL from below

• replace {clientname.creative_budget_sheet} with the name of the datasource you created above for your creative / budget sheet

• replace {clientname.facebook_datasource} with the name of the Facebook datasource you created above

• master.start_date & master.end_date should reference the column names of your campaigns start & end dates in your creative / budget sheet. If the column name is different, switch out every instance with your new names

  • ie if your start date column is named campaign_start_date, master.start_date in line 3, 10, and 13 would change to master.campaign_start_date

• master.campaign_name should reference the column name of your campaign’s name within the creative / budget sheet. If the column name is different, replace every instance of master.campaign_name with your new column name

• master.budget should reference the column name of your campaign’s budget within the creative / budget sheet. If the column name is different, replace every instance of master.budget with your new column name

Tracking Budgets to Campaign Level

select 
    master.campaign_name,
    master.start_date,
    master.end_date, 
    sum(spend.spend) as total_spend_to_date,
    master.budget
from {clientname.creative_budget_sheet} as master
left join {clientname.facebook_datasource} as spend
on master.campaign_name = spend.campaign_name
and spend.date_start >= master.start_date and spend.date_start <= master.end_date
group by 
    master.campaign_name,
    master.start_date,
    master.end_date, 
    master.budget

Tracking Budgets to Adset Level

select 
    master.adset_name,
    master.start_date,
    master.end_date, 
    sum(spend.spend) as total_spend_to_date,
    master.budget
from {clientname.creative_budget_sheet} as master
left join {clientname.facebook_datasource} as spend
on master.adset_name = spend.adset_name
and spend.date_start >= master.start_date and spend.date_start <= master.end_date
group by 
    master.adset_name,
    master.start_date,
    master.end_date, 
    master.budget