Örnekler

MRC ve GroupM görüntülenebilirlik oranları

Uyarı: Gelişmiş Aktif Görüntüleme metriklerini doğru şekilde hesaplamak için sorgularınızı 3 tablonun birleşiminden oluşacak şekilde yazmanız gerekir.

Bu örnekte, farklı ölçülebilirlik alanları olan metriklerin oranlarının nasıl sorgulanacağı gösterilmektedir.

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

Görüntülenebilirlik metriklerini birleştirme

Bu örnekte, görüntülemenin belirli bir noktasında hem tam ekranda hem de arka plana alınmış gösterimlerin sayısı hesaplanmaktadır. Bu metrik yalnızca 2 mevcut metrik birleştirilerek hesaplanabilir.

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

Farklı biçim kategorileri için ölçülebilir ve görüntülenebilir uygun gösterimleri karşılaştırma

Ölçülmüş video reklamlar

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

Ölçülmüş görüntülü reklamlar

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

Yaygın olarak kullanılan metrikleri hesaplama

Bu örnekte, yaygın olarak kullanılan bir dizi metrik hesaplanmıştır. Örnek kodu indirin.