نرخ مشاهده پذیری 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
معیارهای رایج را محاسبه کنید
این مثال تعدادی از معیارهای رایج را محاسبه می کند. دانلود نمونه کد .