مثال ها

نرخ مشاهده پذیری 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

معیارهای رایج را محاسبه کنید

این مثال تعدادی از معیارهای رایج را محاسبه می کند. دانلود نمونه کد .