Examples

MRC and GroupM viewability rates

This example shows how to query rates of metrics that have different measurability fields.

WITH CombinedEvents AS (
  SELECT viewability_metrics, campaign_id FROM adh.google_ads_impressions
  UNION ALL 
  SELECT viewability_metrics, impression_data.campaign_id FROM adh.google_ads_active_views
  UNION ALL
  SELECT viewability_metrics, impression_data.campaign_id FROM adh.google_ads_creative_conversions
),
Metrics AS (
SELECT
  campaign_id,
  SUM(viewability_metrics.mrc_viewable_impressions.measurable_count) AS mrc_measurable_impressions,
  SUM(viewability_metrics.mrc_viewable_impressions.viewable_count) AS mrc_viewable_impressions,
  SUM(viewability_metrics.groupm_viewable_impressions.measurable_count) AS groupm_measurable_impressions,
  SUM(viewability_metrics.groupm_viewable_impressions.viewable_count) AS groupm_viewable_impressions
FROM
  CombinedEvents
GROUP BY
  campaign_id
)
SELECT
  campaign_id,
  SAFE_DIVIDE(mrc_viewable_impressions, mrc_measurable_impressions) AS mrc_viewability_rate,
  SAFE_DIVIDE(groupm_viewable_impressions, groupm_measurable_impressions) AS groupm_viewability_rate
FROM
  Metrics

Combine viewability metrics

This example calculates the number of impressions that were both full-screened and backgrounded at some point during the view. This metric can only be calculated by combining 2 existing metrics.

Note that the GROUP BY clause is included in both the impression ID and the campaign ID. This is necessary to break out ad pods, which are multiple impressions can have the same query id.

WITH CombinedEvents AS (
  SELECT impression_id, campaign_id, viewability_metrics, TRUE AS is_impression FROM adh.google_ads_impressions
  UNION ALL 
  SELECT impression_id, impression_data.campaign_id, viewability_metrics, FALSE AS is_impression FROM adh.google_ads_active_views
  UNION ALL
  SELECT impression_id, impression_data.campaign_id, viewability_metrics, FALSE AS is_impression FROM adh.google_ads_creative_conversions
),
AnnotatedImpressions AS (
  SELECT
    campaign_id,
    LOGICAL_OR(is_impression) AS is_valid_impression,
    SUM(viewability_metrics.active_view_plus_metrics.measurable_count) > 0 AS is_av_plus_measurable,
    SUM(viewability_metrics.active_view_plus_metrics.background_count) > 0 AS was_backgrounded,
    SUM(viewability_metrics.active_view_plus_metrics.fullscreen_count) > 0 AS was_fullscreened
  FROM
    CombinedEvents
  GROUP BY
    impression_id,
    campaign_id
  HAVING
    is_valid_impression = TRUE
)
SELECT
  campaign_id,
  COUNT(*) AS total_impressions,
  COUNTIF(is_av_plus_measurable) AS av_plus_measurable_impressions,
  COUNTIF(was_backgrounded AND was_fullscreened) AS fullscreen_and_backgrounded_impressions
FROM
  AnnotatedImpressions
GROUP BY
  campaign_id

Compare measurable and viewable eligible impressions

This example compares measurable and viewable eligible impressions, organized by campaign ID alongside the measurable count of impressions where watchtime is observable.

WITH
 DisplayCampaigns AS (
   // remove DISTINCT() if you want to return repeat rows
   SELECT DISTINCT() campaign_id
   FROM adh.google_ads_campaign
   WHERE advertising_channel_type = "DISPLAY"
   GROUP BY 1
 ),
 CombinedEvents AS (
   SELECT
     viewability_metrics,
     campaign_id,
     TRUE AS is_impression
   FROM adh.google_ads_impressions

   UNION ALL

   SELECT
     viewability_metrics,
     impression_data.campaign_id,
     FALSE AS is_impression
   FROM
     adh.google_ads_active_views

   UNION ALL

   SELECT
     viewability_metrics,
     impression_data.campaign_id,
     FALSE AS is_impression
   FROM
     adh.google_ads_creative_conversions
 )
SELECT
 campaign_id,
 COUNTIF(is_impression) AS impressions,
 //MRC Viewable
 SUM(viewability_metrics.mrc_viewable_impressions.measurable_count) AS mrc_measurable_impressions,
 SUM(viewability_metrics.mrc_viewable_impressions.viewable_count) AS mrc_viewable_impressions,
 //Time-on-screen
 SUM(viewability_metrics.time_on_screen.measurable_count) AS tos_count,
 SUM(viewability_metrics.time_on_screen.msec) AS tos_msec
FROM
 DisplayCampaigns
JOIN
 CombinedEvents USING (campaign_id)
GROUP BY
 campaign_id

Calculate commonly used metrics

This example calculates a number of commonly used metrics. Download sample code.