範例

MRC 和 GroupM 可視率

注意：為了準確計算進階 Active View 指標，您必須針對全部 3 個資料表的聯集編寫查詢。

以下範例說明如果有多個可評估度欄位，應如何查詢指標比率。

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

結合可視度指標

這個範例顯示在觀看期間的某個時間點，廣告以全螢幕模式在背景中播放的曝光次數。必須合併 2 個現有指標才能計算這項指標。

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

針對各種格式類別比較符合資格的可評估及可視曝光

影片評估廣告

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

多媒體評估廣告

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

計算常用指標

這個範例會計算幾個常用指標。下載程式碼範例。