Porcentajes de visibilidad de MRC y GroupM
En este ejemplo, se muestra cómo consultar las tasas de las métricas que tienen campos de medición diferentes.
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
Cómo combinar métricas de visibilidad
En este ejemplo, se calcula la cantidad de impresiones que se visualizaron en pantalla completa y se ejecutaron en segundo plano en algún momento de la vista. Esta métrica solo se puede calcular si se combinan 2 métricas existentes.
Ten en cuenta que la cláusula GROUP BY
se incluye en el ID de impresión y en el ID de campaña. Esto es necesario para desglosar los grupos de anuncios, que son varias impresiones que pueden tener el mismo ID de consulta.
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 las impresiones medibles y visibles
En este ejemplo, se comparan las impresiones aptas y medibles, organizadas por ID de campaña junto con el recuento medible de impresiones en el que el tiempo de reproducción es 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
Calcular métricas de uso común
En este ejemplo, se calcula un número de métricas de uso común. Descarga el código de muestra.