How-to Set up an Alert When Expected Active Campaigns Not Active

This alert compares active campaigns today to your reference sheet containing campaigns & their start/end dates, to pull out a list of campaigns that you expect to be live (based on their start/end dates) that are not live today

Caveats:

This alert is only as up to date and accurate as your data. If you run this alert too early in the before your platform datasources have loaded for the day, or if they have issues loading that day, you may be falsely alert.

Either plan to run this alert later in the day, or instead of filtering to the current day, filter to the previous day instead of current_date in all instances and look at the previous day.

Instructions

This example is for a specific MIO / Trafficking sheet structure

Data Source Requirements

Live Campaign Expectations Source of Truth

like an MIO or trafficking sheet, this should provide a source of truth for what clients should be live in the current day. We’ll use start & end date in this example to filter
columns required
- campaign name
- start date
- end date

Platform Spend/Impressions Source of Truth

performance datasource that contains campaigns & their spend (or impressions) by day
columns required:
- date
- campaign_name
- spend and/or impressions

SQL View Set-Up

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
4. Give your report a Name and update the Report Type to Custom Redshift
5. Copy and paste the SQL Example in from below and update anything within and including { } to match your client settings
6. Once your SQL is updated, click Publish to update the query
7. Click Preview to ensure the output is as expected

SQL Example Building Blocks

You will need to replace {client_name} with your client name.

This SQL example follows a specific datasource structure that has already been ingested into Alli Data with the name social_mio_2022. You can replace this with your data source name.

Live Campaign Expectations Source of Truth

No matter the column names in the datasource, your query should output the following column names platform, ref_campaign_name,start_date & end_date

CODE

with reference as
(select platform_partner as platform,
  campaign_name as ref_campaign_name,
  campaign_start_date as start_date,
  campaign_end_date as end_date
  from {client_name}.social_mio_2022
  where current_date >= campaign_start_date and current_date <= campaign_end_date),

Platform Source(s) of Truth

No matter the column names in the datasource, your query should output the following column names platform, platform_campaign_name, & today_impressions

CODE

facebook as
(select cast('Facebook Instagram' as varchar(25)) as platform,
  campaign_name as platform_campaign_name,
  sum(impressions) as today_impressions
  from {client_name}.facebook_ad_insights_parent
  where date_start = current_date
  group by platform, campaign_name),

Platform Soures(s) of Truth Combined

CODE

master_platform as 
(select * from facebook)

You can add in multiple platforms by adding a new “Platform Source of Truth” for each and unioning it in your master_platform statement. Example:

CODE

facebook as
(select cast('Facebook Instagram' as varchar(25)) as platform,
  campaign_name as platform_campaign_name,
  sum(impressions) as today_impressions
  from {client_name}.facebook_ad_insights_parent
  where date_start = current_date
  group by platform, campaign_name),

snapchat as (select cast('SnapChat' as varchar(25)) as platform,
  campaignname as platform_campaign_name,
  sum(impressions) as today_impressions
  from {client_name}.snapchat
  group by platform, platform_campaign_name),

master_platform as 
(select * from facebook
union all
select * from snapchat)

Final outer query:

CODE

select
  reference.platform,
  reference.ref_campaign_name,
  reference.start_date,
  reference.end_date,
  master_platform.platform_campaign_name,
  master_platform.today_impressions
from reference left join master_platform
on reference.ref_campaign_name = master_platform.platform_campaign_name
and reference.platform = master_platform.platform
where master_platform.platform_campaign_name is null

Put it all together:

CODE

with reference as
(select platform_partner as platform,
campaign_name as ref_campaign_name,
campaign_start_date as start_date,
campaign_end_date as end_date
from {client_name}.social_mio_2022
where current_date >= campaign_start_date and current_date <= campaign_end_date),

facebook as
(select cast('Facebook Instagram' as varchar(25)) as platform,
campaign_name as platform_campaign_name,
sum(impressions) as today_impressions
from {client_name}.facebook_ad_insights_parent
where date_start = current_date
group by platform, campaign_name),

snapchat as (select cast('SnapChat' as varchar(25)) as platform,
campaignname as platform_campaign_name,
sum(impressions) as today_impressions
from {client_name}.snapchat
where date = current_date - 1
group by platform, platform_campaign_name),

master_platform as
(select * from facebook
union all 
select * from snapchat)

select
reference.platform,
reference.ref_campaign_name,
reference.start_date,
reference.end_date,
master_platform.platform_campaign_name,
master_platform.today_impressions
from reference left join master_platform
on reference.ref_campaign_name = master_platform.platform_campaign_name
and reference.platform = master_platform.platform
where master_platform.platform_campaign_name is null

Instructions: Setting Up Your Custom Alert

Navigate to the Marketplace within Alli and select ‘Discover’
Either using the Search feature, or scrolling through the available options, select Alli Custom Alert
The Set-Up instructions drop down contains all the relevant instructions and tips for setup, and are also available on the install page. click Install App & follow those instructions.