أمثلة

نسب إمكانية العرض من مجلس تقييم الوسائط وGroupM

تحذير: لاحتساب مقاييس "العرض النشط" المتقدّمة بدقة، يجب كتابة طلبات البحث كاتحاد لجميع الجداول الثلاثة.

يوضّح هذا المثال كيفية طلب أسعار المقاييس التي تحتوي على حقول قياس مختلفة.

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

دمج مقاييس إمكانية العرض

يحسب هذا المثال عدد مرّات الظهور التي تم عرضها على الشاشة الكاملة وعرضها في الخلفية في وقت معيّن أثناء المشاهدة. لا يمكن حساب هذا المقياس إلا من خلال دمج مقياسَين حاليَين.

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

احتساب المقاييس الشائعة الاستخدام

يحسب هذا المثال عددًا من المقاييس المستخدَمة بشكل شائع. تنزيل نموذج الرمز البرمجي