Sample queries in Ads Data Hub

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

Campaign Manager Data Transfer queries

Match floodlight variables with temp tables

Generate a match between user_id and custom floodlight variables in the activity table. This can then be used to join first-party data with Campaign Manager data.


/* Creating the match temp table. This can be a separate query and the
temporary table will persist for 72 hours. */

CREATE TABLE
  temp_table AS (
  SELECT
    user_id,
    REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS u1_val
  FROM
    adh.cm_dt_activities
  GROUP BY
    1,
    2 )

/* Matching to Campaign Manager impression data */

SELECT
  imp.event.campaign_id,
  temp.u1_val,
  COUNT(*) AS cnt
FROM
  adh.cm_dt_impressions AS imp
JOIN
  tmp.temp_table AS temp USING (user_id)
GROUP BY
  1,
  2

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.cm_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.cm_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 cm_dt_impressions table and the cm_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.cm_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.cm_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.cm_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.cm_dt_impressions_rdid
GROUP BY 1, 2, 3
;

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

Display and Video 360 audiences

This example shows how to analyze Display and Video 360 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.cm_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
;

Viewability

These example show how to measure Active View Plus viewability metrics.


WITH T AS (
   SELECT cm_dt_impressions.event.impression_id AS Impression,
          cm_dt_impressions.event.active_view_measurable_impressions AS AV_Measurable,
          SUM(cm_dt_active_view_plus.event.active_view_plus_measurable_count) AS AVP_Measurable
     FROM adh.cm_dt_impressions
FULL JOIN adh.cm_dt_active_view_plus
          ON (cm_dt_impressions.event.impression_id =
              cm_dt_active_view_plus.event.impression_id)
    GROUP BY Impression, AV_Measurable
)
SELECT COUNT(Impression), SUM(AV_Measurable), SUM(AVP_Measurable)
  FROM T
;


WITH Raw AS (
  SELECT
    event.ad_id AS Ad_Id,
  SUM(event.active_view_plus_measurable_count) AS avp_total,
  SUM(event.active_view_first_quartile_viewable_impressions) AS avp_1st_quartile,
  SUM(event.active_view_midpoint_viewable_impressions) AS avp_2nd_quartile,
  SUM(event.active_view_third_quartile_viewable_impressions) AS avp_3rd_quartile,
  SUM(event.active_view_complete_viewable_impressions) AS avp_complete
  FROM
    adh.cm_dt_active_view_plus
  GROUP BY
    1
)

SELECT
  Ad_Id,
  avp_1st_quartile / avp_total AS Viewable_Rate_1st_Quartile,
  avp_2nd_quartile / avp_total AS Viewable_Rate_2nd_Quartile,
    avp_3rd_quartile / avp_total AS Viewable_Rate_3rd_Quartile,
    avp_complete / avp_total AS Viewable_Rate_Completion_Quartile
FROM
  Raw
WHERE
  avp_total > 0
ORDER BY
  Viewable_Rate_1st_Quartile DESC
;

Ad Formats in cm 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.cm_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.cm_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
;

Mobile app impressions with _rdid tables

Query 1:


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

Query 2:


SELECT
  campaign_id,
  COUNT(DISTINCT device_id_md5) AS device_ids
FROM adh.google_ads_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.google_ads_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.google_ads_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
;

Subtracting one group of users from another

This example shows how to subtract one group of users from another. This technique has a wide range of applications, including counting non-converters, users with no viewable impressions, and users with no clicks.


WITH exclude AS (
  SELECT DISTINCT user_id
  FROM adh.google_ads_impressions
  WHERE campaign_id = 123
)

SELECT
  COUNT(DISTINCT imp_A.user_id) -
      COUNT(DISTINCT imp_B.user_id) AS users
FROM adh.google_ads_impressions imp_A
LEFT JOIN imp_B
USING (user_id)
WHERE imp_A.campaign_id = 876
;

Viewability

This example shows how to run a simple viewability query. Viewability provides a signal of how likely it is that a user actually saw your ad.

WITH
active_view_metrics AS (
  SELECT
    SUM(imp.num_active_view_eligible_impression) AS EligibleImpressions,
    SUM(imp.num_active_view_measurable_impression) AS MeasurableImpressions,
    SUM(view.num_active_view_viewable_impression) AS ViewableImpressions,
    SUM(IF(imp.num_active_view_eligible_impression > 0 AND (ctv.template_id IN (213) OR ctv.creative_type in (84)), 1, 0)) AS EligibleSkippableImpressions
  FROM adh.google_ads_impressions imp
  LEFT JOIN adh.google_ads_active_views view ON view.query_id = imp.query_id
  LEFT JOIN adh.google_ads_adgroupcreative agc ON imp.ad_group_creative_id = agc.ad_group_creative_id
  LEFT JOIN adh.google_ads_creative AS ctv ON agc.creative_id = ctv.creative_id
  WHERE (imp.active_view_type = 'VIDEO_MEASURABLE' OR imp.active_view_type = 'VIDEO_ENABLED')
  AND imp.customer_id = @customer_id AND imp.campaign_id = @campaign_id
)
SELECT
  EligibleImpressions,
  MeasurableImpressions,
  ViewableImpressions,
  EligibleSkippableImpressions
FROM active_view_metrics
;

General queries

Custom overlap

This query measures the overlap of 2 or more campaigns. It can be customized to measure overlap based on discretionary criteria.

/* For this query to run, @campaign_1 and @campaign_2 must be replaced with
actual campaign IDs. */

WITH flagged_impressions AS (
SELECT
  user_ID,
  SUM(IF(campaign_ID in UNNEST(@campaign_1), 1, 0)) AS C1_impressions,
  SUM(IF(campaign_ID in UNNEST(@campaign_2), 1, 0)) AS C2_impressions
FROM adh.cm_dt_impressions
GROUP BY user_ID

SELECT COUNTIF(C1_impressions > 0) as C1_reach,
 COUNTIF(C2_impressions > 0) as C2_reach,
 COUNTIF(C1_impressions > 0 and C2_impressions > 0) as overlap_reach
FROM flagged_impressions