8. Alerting on Missing Facebook Audience Inclusions/Exclusions- Basic
This view allows you to classify your adsets into groups, and alert on adsets are missing audience inclusions or exclusions, or have extra audience inclusions/exclusions applied.
This details a very basic method of querying to determine if the # of audiences inclusions and the # of audience exclusions are correct. (This version does not specify which specific audience is missing.
Instructions
Data Source Set-Up
This requires that structural data has been set up (specifically the facebookads_structure_adsets
datasource). If your client does not have this datasource, please reach out to the PMG team to get your client onboarded.
SQL View Set-Up
Create your base SQL
Login to Alli Data and select the client you want to create an action for
Navigate to Reports → Explorer and select New Data Report
Give your report a Name and update the Report Type to Custom Redshift
Copy and paste the SQL Example in from below and update anything within and including
{
}
to match your client settingsOnce your SQL is updated, click Publish to update the query
Click Preview to ensure the output is as expected
SQL Example
Context
Lines 4 - 8 is your case statement to build your breakouts (ie how do you group your adsets to determine if their inclusions/exclusions are correct?)
To add more groupings, simple add a another
when
……then
line to build the grouping (or adjust one of the ones present already)
Line 11 displays the number of included audiences according to the datasource (
number_of_targeted_audiences
)Line 12 displays the number of excluded audiences according to the datasource (
number_of_excluded_audiences
)Lines 13-20 is your case statement for missing or extra audience inclusions
each audience type built in the case statement above (lines 4-8) has 2 lines; one for having less than the expected # of included audiences, and one for having more. ( Ex: Lookalikes is on line 14 & 15)
less than expected:
WHEN (audience_type = 'Lookalikes' and number_of_targeted_audiences < 1) then 'Missing Targeted Audience'
adjust the ‘Lookalikes’ to match your audience type created above, and adjust
< #
to the expected # of audiences
more than expected:
WHEN (audience_type = 'Lookalikes' and number_of_targeted_audiences > 1) then 'Extra Targeted Audience'
adjust the ‘Lookalikes’ to match your audience type created above, and adjust
< #
to the expected # of audiences
Lines 22-30 is your case statement for missing or extra audience exclusions
each audience type built in the case statement above (lines 4-8) has 2 lines; one for having less than the expected # of excluded audiences, and one for having more. ( Ex: Lookalikes is on line 23 & 24)
less than expected:
WHEN (audience_type = 'Lookalikes' and number_of_excluded_audiences < 6) then 'Missing Excluded Audience'
adjust the ‘Lookalikes’ to match your audience type created above, and adjust
< #
to the expected # of excluded audiences
more than expected:
WHEN (audience_type = 'Lookalikes' and number_of_excluded_audiences > 6) then 'Extra Excluded Audience'
adjust the ‘Lookalikes’ to match your audience type created above, and adjust
< #
to the expected # of excluded audiences
select account_id,
id as adset_id,
name as adset_name,
case
when name ilike '%Lookalikes%' then 'Lookalike'
when name ilike '%Website Visitors%' then 'Website Visitors'
else 'Prospecting'
end as audience_type,
targeting_custom_audiences,
targeting_excluded_custom_audiences,
LEN(targeting_custom_audiences) - LEN(REPLACE(targeting_custom_audiences, '{', '')) as number_of_targeted_audiences,
LEN(targeting_excluded_custom_audiences) - LEN(REPLACE(targeting_excluded_custom_audiences, '{', '')) as number_of_excluded_audiences,
CASE
WHEN (audience_type = 'Lookalikes' and number_of_targeted_audiences < 1) then 'Missing Targeted Audience'
WHEN (audience_type = 'Lookalikes' and number_of_targeted_audiences > 1) then 'Extra Targeted Audience'
WHEN (audience_type = 'Website Visitors' and number_of_targeted_audiences < 1) then 'Missing Targeted Audience'
WHEN (audience_type = 'Website Visitors' and number_of_targeted_audiences > 1) then 'Extra Targeted Audience'
WHEN (audience_type = 'Prospecting' and number_of_targeted_audiences < 2) then 'Missing Targeted Audience'
WHEN (audience_type = 'Prospecting' and number_of_targeted_audiences > 2) then 'Extra Targeted Audience'
else null
end as targeted_audience_issues,
CASE
WHEN (audience_type = 'Lookalikes' and number_of_excluded_audiences < 6) then 'Missing Excluded Audience'
WHEN (audience_type = 'Lookalikes' and number_of_excluded_audiences > 6) then 'Extra Excluded Audience'
WHEN (audience_type = 'Website Visitors' and number_of_excluded_audiences < 1) then 'Missing Excluded Audience'
WHEN (audience_type = 'Website Visitors' and number_of_excluded_audiences > 1) then 'Extra Excluded Audience'
WHEN (audience_type = 'Prospecting' and number_of_excluded_audiences < 7) then 'Missing Excluded Audience'
WHEN (audience_type = 'Prospecting' and number_of_excluded_audiences > 7) then 'Extra Excluded Audience'
else null
end as excluded_audience_issues
from six_flags.facebookads_structure_adsets as struct
where effective_status = 'ACTIVE'
and (targeted_audience_issues is not null or excluded_audience_issues is not null)
order by audience_type
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.
![](../../__attachments/2427715598/image-20210512-192740.png?inst-v=9d2044ed-f5d2-41ad-960f-0e53811769aa)