10. Pacing Alert

Intro

Overall, think of the construction of this pacing alert as taking individual building blocks for each of the platforms you want to track within a single alert or single query and stacking those. So whether you are tracking 1 platform or 7 platforms in a single query, each platform will have 2 essential building blocks. In some cases, a single source of truth (either budget or spend) might be able to cover multiple platforms, like using Alli Data Library datasource, which may contain multiple platforms spend in one, or using a consolidated MIO for all social campaigns, which could contain multiple platforms budgets & dates.

Building blocks:

Budget source of truth that contains:
- platform (Facebook, Snapchat, Adwords, Bing, etc)
- campaign name (or the building blocks to create it)
- start date
- end date
- budget
Campaign spend source of truth that contains:
- campaign name
- campaign id
- date
- spend

In the query example below, we’ll be using WITH statements to build and utilize our building blocks. It allows us to create each of the building blocks ahead of time, do some extra categorization or data transformation in each, and then use the final cleaned product in a final query.

ex:

CODE

with campaign_budgets as (select...from....),

facebook_spend as (select...from....),

snapchat_spend as (select...from...),

master_spend as (
select * from facebook_spend
union all
select * from snapchat_spend)

select * from campaign_budgets
left join master_spend
on campaign_budgets.campaign_name = master_spend.campaign_name
and master_spend.date >= campaign_budgets.start_date 
and master_spend.date <= campaign_budgets.end_date

You can see each building block is named campaign_budgets, facebook_spend, snapchat_spend

in lines 7-10, we reference earlier building blocks (facebook_spend & snapchat_spend) to build a “master” building block for platform spend
lines 12-14, we again reference our earlier building blocks, the master_spend & campaign_budgets
notice in line 10, after the last building block, we do not need a comma (with the other building blocks, we separated them by a comma - ie see line 1, 3 and 5)

Focus on each building block individually. Ideally within each, adjust column names to match other building blocks (ie renaming campaignname to campaign_name in), which will make the final outer query simpler

Example Building Blocks:

`campaign_budgets`

CODE

with campaign_budgets as 
(select *,
    six_flags.social_objective_encode(bid_objective_ad_type) as objective,
    six_flags.social_placementtoplatform_encode(placement) as platform,
    case
        when contracted_spend is null then 0
        when contracted_spend = '' then 0
        when regexp_instr(lower(contracted_spend),'([a-z])') = TRUE then 0
        else cast(replace(replace(contracted_spend,'$',''),',','') as float) * 0.8678
    end as budget, 
    replace(TO_CHAR(date (cast(start_date as date)), 'Month'),' ','')  as month,
    replace(TO_CHAR(date (cast(start_date as date)), 'YYYY'),' ','')  as year ,
    case
        when platform = 'Snapchat' then parkproduct + ' | ' +  campaign + ' | ' + month + ' | ' + cpe
        else 'Six Flags|' + parkproduct + '|' + campaign + '|' + month + '|' + year + '|' + objective + '|' + cpe 
    end as campaign_name_budgetsource
from six_flags.social_creative_master_mio
where parkproduct != '' and campaign != '' and cpe != ''),

Lines 3 - 16 use categorizations & SQL to create a campaign name. If your budget source of truth already contained campaign_name, your sql could be much simpler, like:

CODE

with campaign_budgets as 
(select platform,
        campaign_name as campaign_name_budgetsource,
        start_date,
        end_date,
        budget
from six_flags.social_creative_master_mio),

`facebook_spend` / `snapchat_spend` / `adwords_spend` : aka `platform_spend`

CODE

snapchat_spend as 
(select 'Snapchat' as platform, 
cast(date as date), 
campaignid::text as campaign_id,
campaignname::text as campaign_name_platform, 
sum(spend) as daily_spend from six_flags.snapchat_consumer
group by platform, date, campaign_id, campaign_name_platform),

fb_spend as 
(select 'Facebook/Instagram' as platform, 
cast(date as date),
campaign_id::text, 
campaign_name::text as campaign_name_platform, 
sum(cost) as daily_spend 
from six_flags_core.social_campaign 
group by platform, date, campaign_id, campaign_name_platform),

adwords_spend as 
(select 'Adwords' as platform, 
cast(date as date), 
campaignid::text as campaign_id,
campaignname::text as campaign_name_platform, 
sum(cost) as daily_spend from six_flags.adwords_insights_campaign
group by platform, date, campaign_id, campaign_name_platform)

in these 3 blocks, we’re ensuring each platform has a) the same level of data (ie grouped up by the platform, date, campaign_id and campaign_name and b) the same # of columns in the same exact order. (In our master_spend block, we’ll union them, so the order of the columns is very important
We’re also (not required) ensuring data types are the same (in many cases this will not be an issue, but on the off chance that data types of the columns are different:
- cast(date_column_name as date) converts / ensures date type
- ::text converts / ensures the column is text/string

`master_spend`

CODE

master_spend as (select * from snapchat_spend
union all 
select * from fb_spend
union all
select * from adwords_spend),

very simply, we’re stacking each platform on top of each other so we can just reference a single datasource name later in the final query (rather than each individually)

`master_join`

CODE

master_join as (select 
campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date as campaign_start_date, 
campaign_budgets.end_date as campaign_end_date,
sum(master_spend.daily_spend) as total_spend_to_date,
budget
from campaign_budgets 
left join master_spend
on campaign_budgets.campaign_name_budgetsource = master_spend.campaign_name_platform and 
master_spend.date >= campaign_budgets.start_date and master_spend.date <= campaign_budgets.end_date
and master_spend.platform = campaign_budgets.platform
group by campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date, 
campaign_budgets.end_date,
campaign_budgets.budget)

here we select all the columns from our budget source of truth (campaign_budgets) joining our master_spend view.
- CODE
```
on campaign_budgets.campaign_name_budgetsource = master_spend.campaign_name_platform and 
master_spend.date >= campaign_budgets.start_date and master_spend.date <= campaign_budgets.end_date
```
- the on clause joins the two views on campaign_name (campaign_budgets.campaign_name_budgetsource = master_spend.campaign_name_platform) and only where the date in the platform spend datasource is within the bounds of the start/end date set in our the budget source of truth (master_spend.date >= campaign_budgets.start_date and master_spend.date <= campaign_budgets.end_date)
Because this is the last building block in our query, we do not need a comma after the closing parentheses

We could stop here and just use that master_join SQL as the final query like this:

CODE

select 
campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date as campaign_start_date, 
campaign_budgets.end_date as campaign_end_date,
sum(master_spend.daily_spend) as total_spend_to_date,
budget
from campaign_budgets 
left join master_spend
on campaign_budgets.campaign_name_budgetsource = master_spend.campaign_name_platform and 
master_spend.date >= campaign_budgets.start_date and master_spend.date <= campaign_budgets.end_date
and master_spend.platform = campaign_budgets.platform
group by campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date, 
campaign_budgets.end_date,
campaign_budgets.budget

but if we want to filter out campaigns, for example, only looking at campaigns from our budget source of truth that are (or should be) currently running (ie filtering the start/end date) then we can use the master_join building block, and then a final outer query

`final_outer_query`

CODE

select platform,
    campaign_name_budgetsource, 
    campaign_name_platform,
    campaign_id,
    campaign_start_date, 
    campaign_end_date, 
    total_spend_to_date, 
    budget, 
    round((total_spend_to_date/NULLIF(budget,0))*100,2) as percent_spend 
from master_join
where percent_spend is not null

Here, we pull from master_join and calculate percent_spend.
In the query we are filtering to campaigns with spend (past and present): where percent_spend is not null
In the above example, if we wanted to filter to active campaigns, we could use: where campaign_end_date >= current_date. so where the the end date is past or equal to today

Put it all together:

CODE

with campaign_budgets as 
(select *,
    six_flags.social_objective_encode(bid_objective_ad_type) as objective,
    six_flags.social_placementtoplatform_encode(placement) as platform,
    case
        when contracted_spend is null then 0
        when contracted_spend = '' then 0
        when regexp_instr(lower(contracted_spend),'([a-z])') = TRUE then 0
        else cast(replace(replace(contracted_spend,'$',''),',','') as float) * 0.8678
    end as budget, 
    replace(TO_CHAR(date (cast(start_date as date)), 'Month'),' ','')  as month,
    replace(TO_CHAR(date (cast(start_date as date)), 'YYYY'),' ','')  as year ,
    case
        when platform = 'Snapchat' then parkproduct + ' | ' +  campaign + ' | ' + month + ' | ' + cpe
        else 'Six Flags|' + parkproduct + '|' + campaign + '|' + month + '|' + year + '|' + objective + '|' + cpe 
    end as campaign_name_budgetsource
from six_flags.social_creative_master_mio
where parkproduct != '' and campaign != '' and cpe != ''),

snapchat_spend as 
(select 'Snapchat' as platform, 
cast(date as date), 
campaignid::text as campaign_id,
campaignname::text as campaign_name_platform, 
sum(spend) as daily_spend from six_flags.snapchat_consumer
group by platform, date, campaign_id, campaign_name_platform),

fb_spend as 
(select 'Facebook/Instagram' as platform, 
cast(date as date),
campaign_id::text, 
campaign_name::text as campaign_name_platform, 
sum(cost) as daily_spend 
from six_flags_core.social_campaign 
group by platform, date, campaign_id, campaign_name_platform),

master_spend as (select * from snapchat_spend
union all 
select * from fb_spend),

master_join as (select 
campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date as campaign_start_date, 
campaign_budgets.end_date as campaign_end_date,
sum(master_spend.daily_spend) as total_spend_to_date,
budget
from campaign_budgets 
left join master_spend
on campaign_budgets.campaign_name_budgetsource = master_spend.campaign_name_platform and 
master_spend.date >= campaign_budgets.start_date and master_spend.date <= campaign_budgets.end_date
and master_spend.platform = campaign_budgets.platform
group by campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date, 
campaign_budgets.end_date,
campaign_budgets.budget)


select platform,
    campaign_name_budgetsource, 
    campaign_name_platform,
    campaign_id,
    campaign_start_date, 
    campaign_end_date, 
    total_spend_to_date, 
    budget, 
    round((total_spend_to_date/NULLIF(budget,0))*100,2) as percent_spend 
from master_join
where percent_spend is not null

McDonald’s Example:

incorporates Twitter, Snapchat & Facebook

CODE

with campaign_budgets as 
(select mcdonalds.social_tactic_encode(tactic) as platform, 
        '|' + replace(to_char(start_date,'MM'),'0','') + '/' + to_char(start_date,'DD') + '-' + replace(to_char(end_date,'MM'),'0','') + '/' + to_char(end_date,'DD') + '|' + to_char(start_date,'YYYY') as campaign_name_ending,
        cpe_code + '|' + campaign_name + campaign_name_ending as campaign_name_budgetsource,
        start_date, 
        end_date, 
        total_budget as budget  
        from mcdonalds."2021_social_mio"),
        
fb_spend as 
(select cast('Facebook/Instagram' as varchar(25)) as platform, 
cast(date_start as date) as date, 
campaign_id::text as campaign_id,
campaign_name::text as campaign_name_platform, 
sum(spend) as daily_spend from mcdonalds.facebook_ad_insights_parent
group by platform, date, campaign_id, campaign_name_platform),

snapchat_spend as 
(select cast('Snapchat' as varchar(25)) as platform, 
cast(date as date) as date, 
campaignid::text as campaign_id,
campaignname::text as campaign_name_platform, 
sum(spend) as daily_spend from mcdonalds.snapchat
group by platform, date, campaign_id, campaign_name_platform),

twitter_spend as 
(select cast('Twitter' as varchar(25)) as platform, 
cast(date as date) as date, 
campaign_id::text as campaign_id,
campaign_name::text as campaign_name_platform, 
sum(billed_charge_local_micro) as daily_spend from mcdonalds.twitter_28_day
group by platform, date, campaign_id, campaign_name_platform
),


master_spend as (select * from snapchat_spend
union all 
select * from fb_spend
union all 
select * from twitter_spend)

select 
campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date as campaign_start_date, 
campaign_budgets.end_date as campaign_end_date,
sum(master_spend.daily_spend) as total_spend_to_date,
budget
from campaign_budgets 
left join master_spend
on campaign_budgets.campaign_name_budgetsource = master_spend.campaign_name_platform and 
master_spend.date >= campaign_budgets.start_date and master_spend.date <= campaign_budgets.end_date
and master_spend.platform = campaign_budgets.platform
group by campaign_budgets.platform,
campaign_budgets.campaign_name_budgetsource,
master_spend.campaign_id, 
master_spend.campaign_name_platform,
campaign_budgets.start_date, 
campaign_budgets.end_date,
campaign_budgets.budget

Once you are finished, name your alert and remember to save/publish your alert. Now that we have campaign budgets and platform spend connected, and your view published above, you can unlock a multitude of alerts off of that, including pacing (over/underpacing alerts) as well as launch date/end date alerts (like, if a campaign should be active based on start/end dates, but has no spend)

Over Pacing / Under Pacing Alerts

After your view above is saved & published (you can confirm this by checking there is a next to published), copy the name you just created. (In the photo below, it's total_social_pacing_example, so the total view is six_flags.total_social_pacing_example because you are in the Six Flags client in this photo)

Using this view as our base, you’ll open up a New Data Report:

Example SQL:

replace six_flags.total_social_pacing_example with the % you want to alert on, like > 100% spend

CODE

select * from six_flags.total_social_pacing_example
where percent_spend > 100

or > 95%

CODE

select * from six_flags.total_social_pacing_example
where percent_spend > 95

Active Campaigns W/ No Spend

Just like the overpacing/underpacing alert, you can use the pacing view you created above as the base for an alert that checks for campaigns that should have started “today” ie the current date, but have no spend

**remember, your spend data in this instance is only as up to date as your datasource, so make sure your platform contains a) current day spend and b) you are running the alert late enough in the day you are sure data has loaded

CODE

select * from six_flags.total_social_pacing_example
where campaign_start_date = current_date 
and total_spend_to_date = 0

Intro

Building blocks:

Example Building Blocks:

campaign_budgets

facebook_spend / snapchat_spend / adwords_spend : aka platform_spend

master_spend

master_join

final_outer_query

Put it all together:

McDonald’s Example:

Over Pacing / Under Pacing Alerts

Active Campaigns W/ No Spend

`campaign_budgets`

`facebook_spend` / `snapchat_spend` / `adwords_spend` : aka `platform_spend`

`master_spend`

`master_join`

`final_outer_query`