Ejemplos

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.