SAT Campaign Shell SQL

Example SQL for Social Ad Trafficking Campaign Shells with instructions on how to adjust.

Single Lookup Sheet Row

When to use this?

Your lookup sheet has a single row that applies to all campaigns

CODE

WITH mio as (
    SELECT
        campaign_name,
        campaign_objective as objective,
        campaign_start_date as start_date,
        campaign_end_date as end_date,
        budget_type,
        platform_budget * 100 as lifetime_budget,
        '' as daily_budget
    FROM pjs_coffee.social_mio_2022
),
lookup as (
    SELECT 
        account_id,
        promoted_object,
        geo_countries,
        instagram_account_id,
        facebook_page_id,
        conversion_domain
    FROM pjs_coffee.social_smartsheet_lookup 
        
)

SELECT * FROM mio
    CROSS JOIN lookup
WHERE mio.start_date = current_date + 3

Lines to Adjust [required]:

Line 8 & 9:
- Line 8: as lifetime_budget should be used if your campaigns budgets are lifetime budgets as daily_budget should be used if your campaigns budgets are daily budgets
- Line 9: whichever budget type you are not using should be the name
  - Ex: If you are using lifetime_budget, then Line 9 will be '' as daily_budget
  - Ex: if you are using daily_budget, then Line 9 will be '' as lifetime_budget
Line 10: pjs_coffee.social_mio_2022 should be swapped for your clients MIO datsource name
Line 20: pjs_coffee.social_smartsheet_lookup should be swapped for your clients lookup sheet datasource name

Lines to Adjust [optional]:

Line 26: WHERE mio.start_date = current_date + 3 should be adjusted so your query only pulls campaigns with start dates X ahead of the current date (where X is your decision)
- ie, for campaigns 3 days out to be trafficked WHERE mio.start_date = current_date + 3
- for campaigns 5 days out to be trafficked WHERE mio.start_date = current_date + 5

Datasource Nuance/Column Names Instructions [optional]

Lines 3 - 9:
- SAT expects the following column outputs: campaign_name, objective , campaign_start_date, budget_type, and budget (end_date is not required at this time, but will eventually be required for adset level trafficking)
- If your columns do not match the above, that’s okay, you’ll just use alias as campaign_name for anything that needs renaming.
  - Ex: in the below example, campaign_name exists, but objective does not, we have campaign_objective so our column becomes campaign_objective as objective
Lines 14 - 19:
- SAT expects the following column outputs: account_id, promoted_object, geo_countries, instagram_account_id, facebook_page_id, conversion_domain
- If your columns do not match the above, use alias to rename appropriately

Multiple Lookup Sheet Rows

When to use this?

Your lookup sheet has multiple rows (ie you traffic your campaigns using a combination of different facebook accounts, instagram accounts, pixels, or other values within the lookup sheet and need to join only to their corresponding rows

The join_key column in your lookup sheet should contain values that map up directly to a single column in your MIO

CODE

WITH mio as (
    SELECT
        campaign_name,
        campaign_objective as objective,
        campaign_start_date as start_date,
        campaign_end_date as end_date,
        budget_type,
        platform_budget * 100 as lifetime_budget,
        '' as daily_budget
    FROM pjs_coffee.social_mio_2022
),
lookup as (
    SELECT 
        account_id,
        promoted_object,
        geo_countries,
        instagram_account_id,
        facebook_page_id,
        conversion_domain
    FROM pjs_coffee.social_smartsheet_lookup 
        
)

SELECT * FROM mio
    LEFT JOIN lookup
    ON mio.geo = lookup.join_key
WHERE mio.start_time = current_date + 3

Lines to Adjust [required]:

Line 8 & 9:
- Line 8: as lifetime_budget should be used if your campaigns budgets are lifetime budgets as daily_budget should be used if your campaigns budgets are daily budgets
- Line 9: whichever budget type you are not using should be the name
  - Ex: If you are using lifetime_budget, then Line 9 will be '' as daily_budget
  - Ex: if you are using daily_budget, then Line 9 will be '' as lifetime_budget
Line 10: pjs_coffee.social_mio_2022 should be swapped for your clients MIO datsource name
Line 20: pjs_coffee.social_smartsheet_lookup should be swapped for your clients lookup sheet datasource name
Line 26: ON mio.language = lookup.join_key should specify which column your lookup sheet is joining to.
- Do you split by language? then mio.language = lookup.join_key
- Do you split by strategy? (Awareness, Prospecting, Retargeting, etc?) then mio.strategy = lookup.join_key

Lines to Adjust [optional]:

Line 27: WHERE mio.start_date = current_date + 3 should be adjusted so your query only pulls campaigns with start dates X ahead of the current date (where X is your decision)
- ie, for campaigns 3 days out to be trafficked WHERE mio.start_date = current_date + 3
- for campaigns 5 days out to be trafficked WHERE mio.start_date = current_date + 5

Datasource Nuance/Column Names Instructions [optional]

Lines 3 - 9:
- SAT expects the following column outputs: campaign_name, objective , campaign_start_date, budget_type, and budget (end_date is not required at this time, but will eventually be required for adset level trafficking)
- If your columns do not match the above, that’s okay, you’ll just use alias as campaign_name for anything that needs renaming.
  - Ex: in the below example, campaign_name exists, but objective does not, we have campaign_objective so our column becomes campaign_objective as objective
Lines 14 - 19:
- SAT expects the following column outputs: account_id, promoted_object, geo_countries, instagram_account_id, facebook_page_id, conversion_domain
- If your columns do not match the above, use alias to rename appropriately

How to deal with changing budget types (ie some Lifetime Campaign & some Daily Campaign)

Taking the top ~10 lines of SQL, you can make small adjustments to the SQL to allow for both types of campaign budgets

Original

CODE

WITH mio as (
    SELECT
        campaign_name,
        campaign_objective as objective,
        campaign_start_date as start_date,
        campaign_end_date as end_date,
        budget_type,
        platform_budget * 100 as lifetime_budget,
        '' as daily_budget
    FROM pjs_coffee.social_mio_2022
)

Updated

2 case statements to check what budget type each line is and set budgets appropriately

CODE

WITH mio as (
    SELECT
        campaign_name,
        campaign_objective as objective,
        campaign_start_date as start_date,
        campaign_end_date as end_date,
        budget_type,
        case
          when budget_type = 'Lifetime Campaign' then platform_budget * 100
          else ''
        end as lifetime_budget,
        case
          when budget_type = 'Daily Campaign' then platform_budget * 100
          else ''
        end as daily_budget
    FROM pjs_coffee.social_mio_2022
)