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 .
