Page Summary
-
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
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.
-- Write queries as a union of all 3 tables.
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
)
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 for different format categories
Video-measured ads
-- Write queries as a union of all 3 tables.
WITH
VideoImpressions
AS
(
SELECT
impression_id
FROM
adh
.
google_ads_impressions
WHERE
format_category
=
'VIDEO'
),
CombinedEvents
AS
(
SELECT
impression_id
,
campaign_id
,
viewability_metrics
,
TRUE
AS
is_impression
FROM
adh
.
google_ads_impressions
WHERE
format_category
=
'VIDEO'
UNION
ALL
SELECT
Im
.
impression_id
,
Av
.
impression_data
.
campaign_id
,
Av
.
viewability_metrics
,
FALSE
AS
is_impression
FROM
VideoImpressions
AS
Im
INNER
JOIN
adh
.
google_ads_active_views
AS
Av
USING
(
impression_id
)
UNION
ALL
SELECT
Im
.
impression_id
,
Cc
.
impression_data
.
campaign_id
,
Cc
.
viewability_metrics
,
FALSE
AS
is_impression
FROM
VideoImpressions
AS
Im
INNER
JOIN
adh
.
google_ads_creative_conversions
AS
Cc
USING
(
impression_id
)
)
SELECT
campaign_id
,
COUNTIF
(
is_impression
)
AS
total_impressions
,
SUM
(
viewability_metrics
.
mrc_viewable_impressions
.
measurable_count
)
AS
mrc_measurable_impressions
,
SUM
(
viewability_metrics
.
mrc_viewable_impressions
.
viewable_count
)
AS
mrc_viewable_impressions
FROM
CombinedEvents
GROUP
BY
campaign_id
;
Display-measured ads
-- Write queries as a union of all 3 tables.
WITH
DisplayImpressions
AS
(
SELECT
impression_id
FROM
adh
.
google_ads_impressions
WHERE
format_category
=
'DISPLAY'
),
CombinedEvents
AS
(
SELECT
impression_id
,
campaign_id
,
viewability_metrics
,
TRUE
AS
is_impression
FROM
adh
.
google_ads_impressions
WHERE
format_category
=
'DISPLAY'
UNION
ALL
SELECT
Im
.
impression_id
,
Av
.
impression_data
.
campaign_id
,
Av
.
viewability_metrics
,
FALSE
AS
is_impression
FROM
DisplayImpressions
AS
Im
INNER
JOIN
adh
.
google_ads_active_views
AS
Av
USING
(
impression_id
)
UNION
ALL
SELECT
Im
.
impression_id
,
Cc
.
impression_data
.
campaign_id
,
Cc
.
viewability_metrics
,
FALSE
AS
is_impression
FROM
DisplayImpressions
AS
Im
INNER
JOIN
adh
.
google_ads_creative_conversions
AS
Cc
USING
(
impression_id
)
)
SELECT
campaign_id
,
COUNTIF
(
is_impression
)
AS
total_impressions
,
SUM
(
viewability_metrics
.
mrc_viewable_impressions
.
measurable_count
)
AS
mrc_measurable_impressions
,
SUM
(
viewability_metrics
.
mrc_viewable_impressions
.
viewable_count
)
AS
mrc_viewable_impressions
FROM
CombinedEvents
GROUP
BY
campaign_id
;
Calculate commonly used metrics
This example calculates a number of commonly used metrics. Download sample code .
