This is legacy documentation, and may not be complete. To see the latest documentation, if you are a marketer, refer to the Marketers site. If you are a measurement partner, refer to the Measurement Partners site.

Examples

Spark icon

AI-generated Key Takeaways

Accurate calculation of advanced Active View metrics requires querying a union of all three relevant tables.
Examples are provided for calculating MRC and GroupM viewability rates by querying a union of three tables with different measurability fields.
An example demonstrates how to combine viewability metrics to calculate the number of impressions that were both full-screened and backgrounded.
Examples show how to compare measurable and viewable eligible impressions for different format categories, specifically video and display ads.

MRC and GroupM viewability rates

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

-- Write queries as a union of all 3 tables.
WITH
CombinedEventsAS(
SELECT
viewability_metrics,
campaign_id
FROMadh.google_ads_impressions
UNIONALL
SELECT
viewability_metrics,
impression_data.campaign_id
FROMadh.google_ads_active_views
UNIONALL
SELECT
viewability_metrics,
impression_data.campaign_id
FROMadh.google_ads_creative_conversions
),
MetricsAS(
SELECT
campaign_id,
SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)
ASmrc_measurable_impressions,
SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)
ASmrc_viewable_impressions,
SUM(viewability_metrics.groupm_viewable_impressions.measurable_count)
ASgroupm_measurable_impressions,
SUM(viewability_metrics.groupm_viewable_impressions.viewable_count)
ASgroupm_viewable_impressions
FROM
CombinedEvents
GROUPBY
campaign_id
)
SELECT
campaign_id,
SAFE_DIVIDE(mrc_viewable_impressions,mrc_measurable_impressions)
ASmrc_viewability_rate,
SAFE_DIVIDE(groupm_viewable_impressions,groupm_measurable_impressions)
ASgroupm_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.

-- Write queries as a union of all 3 tables.
WITH
CombinedEventsAS(
SELECT
impression_id,
campaign_id,
viewability_metrics,
TRUEASis_impression
FROMadh.google_ads_impressions
UNIONALL
SELECT
impression_id,
impression_data.campaign_id,
viewability_metrics,
FALSEASis_impression
FROMadh.google_ads_active_views
UNIONALL
SELECT
impression_id,
impression_data.campaign_id,
viewability_metrics,
FALSEASis_impression
FROMadh.google_ads_creative_conversions
),
AnnotatedImpressionsAS(
SELECT
campaign_id,
LOGICAL_OR(is_impression)ASis_valid_impression,
SUM(viewability_metrics.active_view_plus_metrics.measurable_count) > 0
ASis_av_plus_measurable,
SUM(viewability_metrics.active_view_plus_metrics.background_count) > 0
ASwas_backgrounded,
SUM(viewability_metrics.active_view_plus_metrics.fullscreen_count) > 0
ASwas_fullscreened
FROM
CombinedEvents
GROUPBY
impression_id,
campaign_id
HAVING
is_valid_impression
)
SELECT
campaign_id,
COUNT(*)AStotal_impressions,
COUNTIF(is_av_plus_measurable)ASav_plus_measurable_impressions,
COUNTIF(was_backgroundedANDwas_fullscreened)ASfullscreen_and_backgrounded_impressions
FROM
AnnotatedImpressions
GROUPBY
campaign_id

Compare measurable and viewable eligible impressions for different format categories

Video-measured ads

-- Write queries as a union of all 3 tables.
WITH
VideoImpressionsAS(
SELECTimpression_id
FROMadh.google_ads_impressions
WHEREformat_category='VIDEO'
),
CombinedEventsAS(
SELECT
impression_id,
campaign_id,
viewability_metrics,
TRUEASis_impression
FROMadh.google_ads_impressions
WHEREformat_category='VIDEO'
UNIONALL
SELECT
Im.impression_id,
Av.impression_data.campaign_id,
Av.viewability_metrics,
FALSEASis_impression
FROMVideoImpressionsASIm
INNERJOINadh.google_ads_active_viewsASAv
USING(impression_id)
UNIONALL
SELECT
Im.impression_id,
Cc.impression_data.campaign_id,
Cc.viewability_metrics,
FALSEASis_impression
FROMVideoImpressionsASIm
INNERJOINadh.google_ads_creative_conversionsASCc
USING(impression_id)
)
SELECT
campaign_id,
COUNTIF(is_impression)AStotal_impressions,
SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)
ASmrc_measurable_impressions,
SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)
ASmrc_viewable_impressions
FROM
CombinedEvents
GROUPBY
campaign_id;

Display-measured ads

-- Write queries as a union of all 3 tables.
WITH
DisplayImpressionsAS(
SELECTimpression_id
FROMadh.google_ads_impressions
WHEREformat_category='DISPLAY'
),
CombinedEventsAS(
SELECT
impression_id,
campaign_id,
viewability_metrics,
TRUEASis_impression
FROMadh.google_ads_impressions
WHEREformat_category='DISPLAY'
UNIONALL
SELECT
Im.impression_id,
Av.impression_data.campaign_id,
Av.viewability_metrics,
FALSEASis_impression
FROMDisplayImpressionsASIm
INNERJOINadh.google_ads_active_viewsASAv
USING(impression_id)
UNIONALL
SELECT
Im.impression_id,
Cc.impression_data.campaign_id,
Cc.viewability_metrics,
FALSEASis_impression
FROMDisplayImpressionsASIm
INNERJOINadh.google_ads_creative_conversionsASCc
USING(impression_id)
)
SELECT
campaign_id,
COUNTIF(is_impression)AStotal_impressions,
SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)
ASmrc_measurable_impressions,
SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)
ASmrc_viewable_impressions
FROM
CombinedEvents
GROUPBY
campaign_id;

Calculate commonly used metrics

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