उदाहरण

MRC और 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;

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

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