This article will guide you through using ad set targeting data with example queries that answer specific questions about audience demographics, locations, and other targeting criteria in your ad campaigns.

Overview

The adset_targeting in the Ads table contains details on targeting parameters for the adset (e.g. location, age, gender).
The adset_targeting.target_key nested column contains the targeting category key.

[
  'age_min',
  'age_max',
  'geo_locations_countries',
  'geo_locations_location_types',
  'brand_safety_content_filter_levels',
  'excluded_custom_audiences',
  'custom_audiences',
  'targeting_automation_advantage_audience',
  'geo_locations_custom_locations',
  'flexible_spec',
  'device_platforms',
  'publisher_platforms',
  'facebook_positions',
  'instagram_positions',
  'genders',
  'excluded_geo_locations_countries',
  'Excluded_geo_locations_location_types'
]

The adset_targeting.target_values nested column contains the specific values associated with the targeting category key. The result is returned as an array.

Using Ad Set Targeting Data: Example Queries

Question 1: Which genders are being targeted in campaigns that include “Summer” in their names?

This query looks for campaigns with “Summer” in their names and filters for targeting parameters where adset_targeting.target_key is set to 'genders'. The result will show all the gender-targeted campaigns within the selected date range.

SELECT DISTINCT
 campaign_id,
 campaign_name,
 adset_targeting.target_key AS target_key,
 adset_targeting.target_values AS target_values,
 target_value
FROM
 ads_table a ARRAY
 JOIN adset_targeting ARRAY
 JOIN adset_targeting.target_values AS target_value
WHERE
 campaign_name LIKE '%Summer%'
 AND adset_targeting.target_key = 'genders'
 AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)

Question 2: Which geographical locations are targeted in campaigns that include “Tops” in their names.

This query searches for campaigns with “Tops” in their names, focusing on geo_locations_countries within adset_targeting.target_key. The results will show which countries these campaigns are aiming to reach.

SELECT DISTINCT
 campaign_id,
 campaign_name,
 adset_targeting.target_key AS target_key,
 adset_targeting.target_values AS target_values,
 target_value
FROM
 ads_table a ARRAY
 JOIN adset_targeting ARRAY
 JOIN adset_targeting.target_values AS target_value
WHERE
 campaign_name LIKE '%Tops%'
 AND adset_targeting.target_key = 'geo_locations_countries'
 AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)

Question 3: Which custom audiences are included and excluded in campaigns with “Prospecting” in their names?

This query checks for both custom_audiences (included) and excluded_custom_audiences (excluded) in adset_targeting.target_key. You’ll get a breakdown of custom audience groups, helping you see how these campaigns selectively target or avoid specific audience segments.

SELECT DISTINCT
 campaign_id,
 campaign_name,
 adset_targeting.target_key,
 simpleJSONExtractString(target_value, 'name') AS audiences_name
FROM
 ads_table a ARRAY
 JOIN adset_targeting ARRAY
 JOIN adset_targeting.target_values AS target_value
WHERE
 campaign_name LIKE '%Prospecting%'
 AND adset_targeting.target_key IN ('excluded_custom_audiences', 'custom_audiences')
 AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)

Question 4: Which age groups being targeted in campaigns that include “Bottoms” in their names.

This query focuses on campaigns with “Bottoms” in their names, filtering for age_min and age_max in adset_targeting.target_key. The output provides the age range, allowing you to assess the age demographics these campaigns are reaching.

SELECT DISTINCT
 campaign_id,
 campaign_name,
 adset_targeting.target_key AS target_key,
 adset_targeting.target_values AS target_values,
 target_value
FROM
 ads_table a ARRAY
 JOIN adset_targeting ARRAY
 JOIN adset_targeting.target_values AS target_value
WHERE
 campaign_name LIKE '%Bottoms%'
 AND adset_targeting.target_key IN ('age_min', 'age_max')
 AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)

Question 5: Which campaigns are specifically targeted at females aged 18-65 in the United States?

This complex query uses common table expressions (CTEs) to isolate different targeting parameters (country, gender, age range) for campaigns aimed at females aged 18-65 in the US. By joining these CTEs, the query outputs campaigns that meet all the specified targeting criteria. This query is particularly useful for identifying hyper-targeted campaigns and understanding their scope within specific demographics.

WITH
 country AS (
   SELECT DISTINCT adset_id
   FROM ads_table a ARRAY
   JOIN adset_targeting ARRAY
   JOIN adset_targeting.target_values AS target_value
   WHERE adset_targeting.target_key = 'geo_locations_countries'
   AND target_value = 'US'
   AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)
 ),
 gender AS (
   SELECT DISTINCT adset_id
   FROM ads_table a ARRAY
   JOIN adset_targeting ARRAY
   JOIN adset_targeting.target_values AS target_value
   WHERE adset_targeting.target_key = 'genders'
   AND target_value = 'female'
   AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)
 ),
 age_min AS (
   SELECT DISTINCT adset_id
   FROM ads_table a ARRAY
   JOIN adset_targeting ARRAY
   JOIN adset_targeting.target_values AS target_value
   WHERE adset_targeting.target_key = 'age_min'
   AND target_value = '18'
   AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)
 ),
 age_max AS (
   SELECT DISTINCT adset_id
   FROM ads_table a ARRAY
   JOIN adset_targeting ARRAY
   JOIN adset_targeting.target_values AS target_value
   WHERE adset_targeting.target_key = 'age_max'
   AND target_value = '65'
   AND event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)
 )
SELECT
 ads1.*
FROM
 ads_table AS ads1
 INNER JOIN country USING (adset_id)
 INNER JOIN gender USING (adset_id)
 INNER JOIN age_min USING (adset_id)
 INNER JOIN age_max USING (adset_id)
WHERE
 ads1.channel = 'facebook-ads'
 AND ads1.event_date = DATE_SUB(current_date(), INTERVAL 1 DAY)