أمثلة

قابلية عرض مجلس تقييم الوسائط (MRC) وGroupM

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

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

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

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

تجدر الإشارة إلى أنّ عبارة GROUP BY مضمّنة في كلٍّ من رقم تعريف الظهور ورقم تعريف الحملة. يُعدّ ذلك ضروريًا لتقسيم لوحات الإعلانات، وهي عبارة عن مرات ظهور متعددة يمكن أن تتضمّن معرّف طلب البحث نفسه.

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

مقارنة مرات الظهور المؤهَّلة القابلة للقياس والعرض

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

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

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

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