उदाहरण

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

आम तौर पर, इस्तेमाल होने वाली मेट्रिक का हिसाब लगाना

इस उदाहरण में आम तौर पर इस्तेमाल की जाने वाली कई मेट्रिक की गिनती की गई है. सैंपल कोड डाउनलोड करें.