8. Alerting on Missing Facebook Audience Inclusions/Exclusions- Basic

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

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. 

   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

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

1. Navigate to the Marketplace within Alli and select ‘Discover’

2. Either using the Search feature, or scrolling through the available options, select Alli Custom Alert

   

3. 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.