Exemples

Taux de visibilité MRC et GroupM

Avertissement : Pour calculer précisément des métriques Active View avancées, vous devez rédiger vos requêtes sous forme d'union des trois tables.

Cet exemple montre comment interroger des taux de métriques comportant différents champs de mesurabilité.

-- 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

Combiner des métriques de visibilité

Cet exemple calcule le nombre d'impressions en plein écran et en arrière-plan à un moment donné de la vue. Cette métrique ne peut être calculée qu'en combinant deux métriques existantes.

-- 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

Comparer les impressions mesurables et les impressions éligibles visibles pour différentes catégories de format

Annonces mesurées dans la catégorie Vidéo

-- 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;

Annonces mesurées dans la catégorie Display

-- 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;

Calculer des métriques couramment utilisées

Cet exemple calcule différentes métriques couramment utilisées. Télécharger l'exemple de code