Sample queries in Ads Data Hub

These sample queries assume working knowledge of SQL and BigQuery. Learn more about SQL in BigQuery.

DCM Data Transfer queries

Impression Delivery

This example is good for impression management, and shows how to find the number of impressions that were served beyond frequency caps or if certain prospects were underexposed to ads. Use this knowledge to optimize your sites and tactics to get the right number of impressions in front of a chosen audience.

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345] */

WITH filtered_uniques AS (
  SELECT
    user_id,
    COUNT(event.site_placement_id) AS frequency
  FROM adh.dcm_dt_impressions
  WHERE user_id != '0'
    AND event.advertiser_id IN UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.country_domain_name = 'US'
  GROUP BY user_id
)
SELECT
  frequency,
  COUNT(*) AS uniques
FROM filtered_uniques
GROUP BY frequency
ORDER BY frequency
;

Total Reach/Frequency

This example helps identify tactics and ad formats that lead to increases or decreases in reach or frequency.

/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */

SELECT
  COUNT(DISTINCT user_id) AS total_users,
  COUNT(DISTINCT event.site_id) AS total_sites,
  COUNT(DISTINCT device_id_md5) AS total_devices,
  COUNT(event.site_placement_id) AS impressions
FROM adh.dcm_dt_impressions
WHERE user_id != '0'
  AND event.advertiser_id IN UNNEST(@advertiser_ids)
  AND event.campaign_id IN UNNEST(@campaign_ids)
  AND event.site_placement_id IN UNNEST(@placement_ids)
  AND event.country_domain_name = 'US'
;

You can also include site or placement IDs in the WHERE clause to narrow your query.

Total reach and average frequency by state

This example joins the dcm_dt_impressions table and the dcm_dt_state metadata table to show total impression reach, user reach, and average impression by user, grouped by North America geographic state or province.


WITH impression_stats AS (
  SELECT
    event.country_domain_name AS country,
    CONCAT(event.country_domain_name, '-', event.state) AS state,
    COUNT(DISTINCT user_id) AS users,
    COUNT(*) AS impressions
  FROM adh.dcm_dt_impressions
  WHERE event.country_domain_name = 'US'
    OR event.country_domain_name = 'CA'
  GROUP BY 1, 2
)
SELECT
  country,
  IFNULL(state_name, state) AS state_name,
  users,
  impressions,
  FORMAT(
    '%0.2f',
    IF(
      IFNULL(impressions, 0) = 0,
      0,
      impressions / users
    )
  ) AS avg_imps_per_user
FROM impression_stats
LEFT JOIN adh.dcm_dt_state USING (state)
;

Mobile app impressions with _rdid tables

The following queries must be done separately. RDID and non-RDID tables cannot be joined in the same query (even using temp tables).

Query 1:


SELECT
  event.campaign_id,
  event.site_placement_id,
  event.country_domain_name,
  COUNT(*) AS impressions,
  COUNT(DISTINCT user_id) AS users
FROM adh.dcm_dt_impressions
WHERE is_in_rdid_project
GROUP BY 1, 2, 3
;

Query 2:


SELECT
  event.campaign_id,
  event.site_placement_id,
  event.country_domain_name,
  COUNT(DISTINCT device_id_md5) AS device_ids
FROM adh.dcm_dt_impressions_rdid
GROUP BY 1, 2, 3
;

The results can be joined using campaign_id, site_placement_id, and country_domain_name.

DBM Audiences

This example shows how to analyze DBM audiences. Learn which audiences impressions are reaching, and determine if some audiences perform better than others. This knowledge can help balance reach (putting ads in front of a lot of users) and quality (narrow targeting and viewable impressions), depending on your goals.

/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */

WITH filtered_impressions AS (
  SELECT
    event.event_time as date,
    CASE
      WHEN (event.browser_enum IN ('29', '30', '31')
            OR event.os_id IN
              (501012, 501013, 501017, 501018,
               501019, 501020, 501021, 501022,
               501023, 501024, 501025, 501027))
      THEN 'Mobile'
      ELSE 'Desktop'
    END AS device,
    event.xbid_matching_targeted_segments,
    event.active_view_viewable_impressions,
    event.active_view_measurable_impressions,
    user_id
  FROM adh.dcm_dt_impressions
  WHERE event.xbid_matching_targeted_segments != ''
    AND event.advertiser_id in UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.xbid_country_code = 'US'
)
SELECT
  audience_id,
  device,
  COUNT(*) AS impressions,
  COUNT(DISTINCT user_id) AS uniques,
  ROUND(COUNT(*) / COUNT(DISTINCT user_id), 1) AS frequency,
  SUM(active_view_viewable_impressions) AS viewable_impressions,
  SUM(active_view_measurable_impressions) AS measurable_impressions
FROM filtered_impressions
JOIN UNNEST(SPLIT(xbid_matching_targeted_segments, ' ')) AS audience_id
GROUP BY 1, 2
;

Ad Formats in DCM Data Transfer

These examples shows how to determine which ad formats are maximizing reach, or frequency of impressions. Use this knowledge to help balance total reach, and user exposure to ads.

Impression delivery

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset.*/

WITH filtered_uniques AS (
  SELECT
    user_id,
    CASE
      WHEN creative_type LIKE '%Video%' THEN 'Video'
      WHEN creative_type IS NULL THEN 'Unknown'
      ELSE 'Display'
    END AS creative_format,
    COUNT(*) AS impressions
  FROM adh.dcm_dt_impressions impression
  LEFT JOIN YOUR_BQ_DATASET.campaigns creative
    ON creative.rendering_id = impression.event.rendering_id
  WHERE user_id != '0'
    AND event.advertiser_id IN UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.country_domain_name = 'US'
  GROUP BY user_id, creative_format
)
SELECT
  impressions AS frequency,
  creative_format,
  COUNT(DISTINCT user_id) AS uniques,
  SUM(impressions) AS impressions
FROM filtered_uniques
GROUP BY frequency, creative_format
ORDER BY frequency
;

Reach and frequency

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset. */

WITH filtered_impressions AS (
  SELECT
    event.campaign_id AS campaign_id,
    event.rendering_id AS rendering_id,
    user_id
  FROM adh.dcm_dt_impressions
  WHERE user_id != '0'
    AND event.advertiser_id IN UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.country_domain_name = 'US'
)
SELECT
  Campaign,
  CASE
    WHEN creative_type LIKE '%Video%' THEN 'Video'
    WHEN creative_type IS NULL THEN 'Unknown'
    ELSE 'Display'
  END AS creative_format,
  COUNT(DISTINCT user_id) AS users,
  COUNT(*) AS impressions
FROM filtered_impressions
LEFT JOIN YOUR_BQ_DATASET.campaigns USING (campaign_id)
LEFT JOIN YOUR_BQ_DATASET.creatives USING (rendering_id)
GROUP BY 1, 2
;

AdWords

Mobile app impressions with _rdid tables

Query 1:


SELECT
  campaign_id,
  COUNT(*) AS imp,
  COUNT(DISTINCT user_id) AS users
FROM adh.adwords_impressions
WHERE is_in_rdid_project
GROUP BY 1
;

Query 2:


SELECT
  campaign_id,
  COUNT(DISTINCT device_id_md5) AS device_ids
FROM adh.adwords_impressions_rdid
GROUP BY 1
;

The results can be joined using campaign_id.

In target delivery - demographics

This example shows how to determine which campaigns are reaching a given demographic.

/* For this query to run, @customer_id
must be replaced with an actual ID. For example [12345] */

WITH impression_stats AS (
  SELECT
    campaign_id,
    demographics.gender AS gender_id,
    demographics.age_group AS age_group_id,
    COUNT(DISTINCT user_id) AS users,
    COUNT(*) AS impressions
  FROM adh.adwords_impressions
  WHERE customer_id = @customer_id
  GROUP BY 1, 2, 3
)
SELECT
  campaign_name,
  gender_name,
  age_group_name,
  users,
  impressions
FROM impression_stats
LEFT JOIN adh.adwords_campaign USING (campaign_id)
LEFT JOIN adh.gender USING (gender_id)
LEFT JOIN adh.age_group USING (age_group_id)
ORDER BY 1, 2, 3
;

In target delivery - interest groups

This example shows how to determine which campaigns are reaching a given interest group.


WITH impression_stats AS (
  SELECT
    campaign_id,
    affinity_id,
    COUNT(DISTINCT user_id) AS users,
    COUNT(*) AS impressions
  FROM adh.adwords_impressions AS impressions
  CROSS JOIN UNNEST(impressions.affinity) as affinity_id
  WHERE customer_id = @customer_id
  GROUP BY 1, 2
)
SELECT
  campaign_name,
  affinity_name,
  affinity_category,
  users,
  impressions
FROM impression_stats
LEFT JOIN adh.adwords_campaign USING (campaign_id)
LEFT JOIN adh.affinity USING (affinity_id)
ORDER BY 1, 2, 3
;