דוגמאות

שיעור הניראות של 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

שילוב מדדי הניראות

בדוגמה הזו אנחנו מחשבים את מספר החשיפות שהיו במסך מלא וגם ברקע בשלב מסוים במהלך התצוגה. אפשר לחשב את המדד הזה רק על ידי שילוב של 2 מדדים קיימים.

חשוב לשים לב שהסעיף 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

חישוב מדדים נפוצים

בדוגמה הזו מחושבים כמה מדדים נפוצים. הורדה של קוד לדוגמה