تفترض نماذج طلبات البحث هذه معرفة عملية بـ SQL وBigQuery. اطّلِع على مزيد من المعلومات عن لغة الاستعلامات البنيوية (SQL) في BigQuery.
طلبات نقل البيانات في "مدير الحملة 360"
مطابقة متغيّرات Floodlight مع الجداول المؤقتة
أنشئ مطابقة بين user_id ومتغيّرات Floodlight المخصّصة في جدول النشاط. ويمكن بعد ذلك استخدام هذه البيانات لدمج بيانات الطرف الأول مع بيانات "مدير الحملة 360".
/* Creating the match temp table. This can be a separate query and the
temporary table will persist for 72 hours. */
CREATE TABLE
temp_table AS (
SELECT
user_id,
REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS u1_val
FROM
adh.cm_dt_activities_attributed
GROUP BY
1,
2 )
/* Matching to Campaign Manager 360 impression data */
SELECT
imp.event.campaign_id,
temp.u1_val,
COUNT(*) AS cnt
FROM
adh.cm_dt_impressions AS imp
JOIN
tmp.temp_table AS temp USING (user_id)
GROUP BY
1,
2
عرض مرات الظهور
يُعدّ هذا المثال مناسبًا لإدارة مرات الظهور، ويوضّح كيفية العثور على عدد مرات الظهور التي تم عرضها بما يتجاوز حدود تحديد عدد مرات الظهور أو ما إذا كانت بعض العملاء المحتملين غير عُرضة للإعلانات بشكلٍ كافٍ. استخدم هذه المعلومات لتحسين مواقعك وأساليبك للحصول على العدد المناسب من مرات الظهور أمام جمهور محدد.
/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345] */
WITH filtered_uniques AS (
SELECT
user_id,
COUNT(event.placement_id) AS frequency
FROM adh.cm_dt_impressions
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.country_domain_name = 'US'
GROUP BY user_id
)
SELECT
frequency,
COUNT(*) AS uniques
FROM filtered_uniques
GROUP BY frequency
ORDER BY frequency
;
إجمالي عدد ملفات تعريف الارتباط الفريدة / معدل تكرارها
يساعد هذا المثال في تحديد الأساليب وأشكال الإعلانات التي تؤدي إلى زيادة أو انخفاض عدد ملفات تعريف الارتباط الفريدة أو معدّل تكرارها.
/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */
SELECT
COUNT(DISTINCT user_id) AS total_users,
COUNT(DISTINCT event.site_id) AS total_sites,
COUNT(DISTINCT device_id_md5) AS total_devices,
COUNT(event.placement_id) AS impressions
FROM adh.cm_dt_impressions
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.placement_id IN UNNEST(@placement_ids)
AND event.country_domain_name = 'US'
;
يمكنك أيضًا تضمين معرفات الموقع أو الموضع في عبارة WHERE لتضييق نطاق الاستعلام.
إجمالي عدد ملفات تعريف الارتباط الفريدة ومتوسط التكرار حسب الحالة
ينضم هذا المثال إلى جدول cm_dt_impressions وجدول البيانات الوصفية cm_dt_state لعرض إجمالي عدد مرات الظهور وعدد ملفات تعريف الارتباط لكل ولاية ومتوسط مرات الظهور حسب المستخدم، مجمّعة حسب الولاية أو المقاطعة الجغرافية في أمريكا الشمالية.
WITH impression_stats AS (
SELECT
event.country_domain_name AS country,
CONCAT(event.country_domain_name, '-', event.state) AS state,
COUNT(DISTINCT user_id) AS users,
COUNT(*) AS impressions
FROM adh.cm_dt_impressions
WHERE event.country_domain_name = 'US'
OR event.country_domain_name = 'CA'
GROUP BY 1, 2
)
SELECT
country,
IFNULL(state_name, state) AS state_name,
users,
impressions,
FORMAT(
'%0.2f',
IF(
IFNULL(impressions, 0) = 0,
0,
impressions / users
)
) AS avg_imps_per_user
FROM impression_stats
LEFT JOIN adh.cm_dt_state USING (state)
;
شرائح جمهور "مساحة العرض والفيديو 360"
يوضّح هذا المثال كيفية تحليل شرائح جمهور "مساحة العرض والفيديو 360". تعرّف على مرات ظهور شرائح الجمهور التي تصل إلى شرائح الجمهور، وحدِّد ما إذا كانت بعض شرائح الجمهور تحقّق أداءً أفضل من غيرها. وقد تساعد هذه المعرفة في تحقيق التوازن بين عدد ملفات تعريف الارتباط الفريدة (وضع الإعلانات أمام الكثير من المستخدمين) والجودة (الاستهداف الضيق ومرات الظهور القابلة للعرض)، اعتمادًا على أهدافك.
/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */
WITH filtered_impressions AS (
SELECT
event.event_time as date,
CASE
WHEN (event.browser_enum IN ('29', '30', '31')
OR event.os_id IN
(501012, 501013, 501017, 501018,
501019, 501020, 501021, 501022,
501023, 501024, 501025, 501027))
THEN 'Mobile'
ELSE 'Desktop'
END AS device,
event.dv360_matching_targeted_segments,
event.active_view_viewable_impressions,
event.active_view_measurable_impressions,
user_id
FROM adh.cm_dt_impressions
WHERE event.dv360_matching_targeted_segments != ''
AND event.advertiser_id in UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.dv360_country_code = 'US'
)
SELECT
audience_id,
device,
COUNT(*) AS impressions,
COUNT(DISTINCT user_id) AS uniques,
ROUND(COUNT(*) / COUNT(DISTINCT user_id), 1) AS frequency,
SUM(active_view_viewable_impressions) AS viewable_impressions,
SUM(active_view_measurable_impressions) AS measurable_impressions
FROM filtered_impressions
JOIN UNNEST(SPLIT(dv360_matching_targeted_segments, ' ')) AS audience_id
GROUP BY 1, 2
;
إمكانية العرض
يوضّح هذا المثال كيفية قياس مقاييس إمكانية العرض في "العرض النشط".
WITH T AS (
SELECT cm_dt_impressions.event.impression_id AS Impression,
cm_dt_impressions.event.active_view_measurable_impressions AS AV_Measurable,
SUM(cm_dt_active_view_plus.event.active_view_plus_measurable_count) AS AVP_Measurable
FROM adh.cm_dt_impressions
FULL JOIN adh.cm_dt_active_view_plus
ON (cm_dt_impressions.event.impression_id =
cm_dt_active_view_plus.event.impression_id)
GROUP BY Impression, AV_Measurable
)
SELECT COUNT(Impression), SUM(AV_Measurable), SUM(AVP_Measurable)
FROM T
;
WITH Raw AS (
SELECT
event.ad_id AS Ad_Id,
SUM(event.active_view_plus_measurable_count) AS avp_total,
SUM(event.active_view_first_quartile_viewable_impressions) AS avp_1st_quartile,
SUM(event.active_view_midpoint_viewable_impressions) AS avp_2nd_quartile,
SUM(event.active_view_third_quartile_viewable_impressions) AS avp_3rd_quartile,
SUM(event.active_view_complete_viewable_impressions) AS avp_complete
FROM
adh.cm_dt_active_view_plus
GROUP BY
1
)
SELECT
Ad_Id,
avp_1st_quartile / avp_total AS Viewable_Rate_1st_Quartile,
avp_2nd_quartile / avp_total AS Viewable_Rate_2nd_Quartile,
avp_3rd_quartile / avp_total AS Viewable_Rate_3rd_Quartile,
avp_complete / avp_total AS Viewable_Rate_Completion_Quartile
FROM
Raw
WHERE
avp_total > 0
ORDER BY
Viewable_Rate_1st_Quartile DESC
;
البيانات الديناميكية في ميزة "نقل البيانات" في "مدير الحملة 360"
عدد مرات الظهور لكل ملف شخصي ديناميكي وخلاصة
SELECT
event.dynamic_profile,
feed_name,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
JOIN UNNEST (event.feed) as feed_name
GROUP BY 1, 2;
عدد مرّات الظهور لكل تصنيف إعداد تقارير ديناميكي في الخلاصة 1
SELECT
event.feed_reporting_label[SAFE_ORDINAL(1)] feed1_reporting_label,,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_label[SAFE_ORDINAL(1)] <> “” # where you have at least one reporting label set
GROUP BY 1;
عدد مرّات الظهور التي يكون فيها تصنيف إعداد التقارير = "أحمر" في الخلاصة 2
SELECT
event.feed_reporting_label[SAFE_ORDINAL(2)] AS feed1_reporting_label,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_label[SAFE_ORDINAL(2)] = “red”
GROUP BY 1;
عدد مرات الظهور حيث يكون سمة إعداد التقارير_1 = "أحمر" وسمة إعداد التقارير_2 = "سيارة" في الخلاصة 1
SELECT
event.feed_reporting_label[SAFE_ORDINAL(1)] AS feed1_reporting_label,
event.feed_reporting_dimension1[SAFE_ORDINAL(1)] AS feed1_reporting_dimension1,
event.feed_reporting_dimension2[SAFE_ORDINAL(1)] AS feed2_reporting_dimension1,
event.feed_reporting_dimension3[SAFE_ORDINAL(1)] AS feed3_reporting_dimension1,
event.feed_reporting_dimension4[SAFE_ORDINAL(1)] AS feed4_reporting_dimension1,
event.feed_reporting_dimension5[SAFE_ORDINAL(1)] AS feed5_reporting_dimension1,
event.feed_reporting_dimension6[SAFE_ORDINAL(1)] AS feed6_reporting_dimension1,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_dimension1[SAFE_ORDINAL(1)] = “red”
AND event.feed_reporting_dimension2[SAFE_ORDINAL(1)] = “car”
GROUP BY 1,2,3,4,5,6,7;
أشكال الإعلانات في أداة "نقل البيانات" في "مدير الحملة 360"
توضّح هذه الأمثلة كيفية تحديد أشكال الإعلانات التي تحقق الحد الأقصى من عدد ملفات تعريف الارتباط أو معدل تكرار مرات الظهور الفريدة. استخدم هذه المعلومات للمساعدة في تحقيق التوازن بين إجمالي عدد ملفات تعريف الارتباط الفريدة وعدد مرات مشاهدة المستخدمين للإعلانات.
عرض مرات الظهور
/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset.*/
WITH filtered_uniques AS (
SELECT
user_id,
CASE
WHEN creative_type LIKE '%Video%' THEN 'Video'
WHEN creative_type IS NULL THEN 'Unknown'
ELSE 'Display'
END AS creative_format,
COUNT(*) AS impressions
FROM adh.cm_dt_impressions impression
LEFT JOIN YOUR_BQ_DATASET.campaigns creative
ON creative.rendering_id = impression.event.rendering_id
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.country_domain_name = 'US'
GROUP BY user_id, creative_format
)
SELECT
impressions AS frequency,
creative_format,
COUNT(DISTINCT user_id) AS uniques,
SUM(impressions) AS impressions
FROM filtered_uniques
GROUP BY frequency, creative_format
ORDER BY frequency
;
عدد ملفات تعريف الارتباط وتكرارها الفريد
/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset. */
WITH filtered_impressions AS (
SELECT
event.campaign_id AS campaign_id,
event.rendering_id AS rendering_id,
user_id
FROM adh.cm_dt_impressions
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.country_domain_name = 'US'
)
SELECT
Campaign,
CASE
WHEN creative_type LIKE '%Video%' THEN 'Video'
WHEN creative_type IS NULL THEN 'Unknown'
ELSE 'Display'
END AS creative_format,
COUNT(DISTINCT user_id) AS users,
COUNT(*) AS impressions
FROM filtered_impressions
LEFT JOIN YOUR_BQ_DATASET.campaigns USING (campaign_id)
LEFT JOIN YOUR_BQ_DATASET.creatives USING (rendering_id)
GROUP BY 1, 2
;
إعلانات Google
مرات ظهور التطبيقات المتوافقة مع الأجهزة الجوّالة باستخدام الجداول _rdid
الاستعلام 1:
SELECT
campaign_id,
COUNT(*) AS imp,
COUNT(DISTINCT user_id) AS users
FROM adh.google_ads_impressions
WHERE is_app_traffic
GROUP BY 1
;
الاستعلام 2:
SELECT
campaign_id,
COUNT(DISTINCT device_id_md5) AS device_ids
FROM adh.google_ads_impressions_rdid
GROUP BY 1
;
ويمكن ضم النتائج باستخدام مَعلمة campaign_id.
عرض الخصائص الديمغرافية
يوضّح هذا المثال كيفية تحديد الحملات التي تصل إلى فئة ديمغرافية معيّنة.
/* For this query to run, @customer_id
must be replaced with an actual ID. For example [12345] */
WITH impression_stats AS (
SELECT
campaign_id,
demographics.gender AS gender_id,
demographics.age_group AS age_group_id,
COUNT(DISTINCT user_id) AS users,
COUNT(*) AS impressions
FROM adh.google_ads_impressions
WHERE customer_id = @customer_id
GROUP BY 1, 2, 3
)
SELECT
campaign_name,
gender_name,
age_group_name,
users,
impressions
FROM impression_stats
LEFT JOIN adh.google_ads_campaign USING (campaign_id)
LEFT JOIN adh.gender USING (gender_id)
LEFT JOIN adh.age_group USING (age_group_id)
ORDER BY 1, 2, 3
;
إمكانية العرض
لإلقاء نظرة عامة على إمكانية العرض باستخدام نماذج طلبات البحث، يُرجى الاطّلاع على مقاييس العرض النشط المتقدمة.
إعدادات المنطقة الزمنية لمعلِن "إعلانات Google"
SELECT
customer_id,
customer_timezone,
count(1) as impressions
FROM adh.google_ads_impressions i
INNER JOIN adh.google_ads_customer c
ON c.customer_id = i.customer_id
WHERE TIMESTAMP_MICROS(i.query_id.time_usec) >= CAST(DATETIME(@date, c.customer_timezone) AS TIMESTAMP)
AND TIMESTAMP_MICROS(i.query_id.time_usec) < CAST(DATETIME_ADD(DATETIME(@date, c.customer_timezone), INTERVAL 1 DAY) AS TIMESTAMP)
GROUP BY customer_id, customer_timezone
نوع المستودع
يوضح نموذج الاستعلام هذا مفهوم نوع المخزون. يمكنك استخدام الحقل
inventory_type لتحديد المستودع الإعلاني الذي تم عرض إعلاناتك عليه، مثل
Gmail أو YouTube Music. القيم المتاحة: YOUTUBE وYOUTUBE_TV
وYOUTUBE_MUSIC وSEARCH وGMAIL وOTHER. ويشير "غير ذلك" إلى "شبكة Google الإعلانية"
أو "شبكة الفيديو"
SELECT
i.campaign_id,
cmp.campaign_name,
i.inventory_type,
COUNT(i.query_id.time_usec) AS impressions
FROM adh.google_ads_impressions i
LEFT JOIN adh.google_ads_campaign cmp ON (i.campaign_id = cmp.campaign_id)
WHERE
TIMESTAMP_MICROS(i.query_id.time_usec)
BETWEEN @local_start_date
AND TIMESTAMP_ADD(@local_start_date,INTERVAL @number_days*24 HOUR)
GROUP BY 1, 2, 3
ORDER BY 4 DESC
طلبات بحث مجموعة الإعلانات المتسلسلة على YouTube
تجمّع مجموعات الإعلانات المتسلسلة 2 الإعلانات في فاصل إعلاني واحد خلال جلسات المشاهدة الأطول على YouTube. (فكِّر في عرض فاصل إعلاني، ولكن لا يمكن عرض أكثر من إعلانَين). تظل الإعلانات المعروضة في مجموعات الإعلانات المتسلسلة قابلة للتخطي. في المقابل، إذا تخطّى المستخدم الإعلان الأول، يتم أيضًا تخطّي الإعلان الثاني.
مرات ظهور ومرّات ظهور حملة TrueView ضمن البث في "إعلانات Google"
SELECT
cmp.campaign_name,
imp.is_app_traffic,
COUNT(*) AS total_impressions,
COUNTIF(clk.click_id IS NOT NULL) AS total_trueview_views
FROM adh.google_ads_impressions imp
JOIN adh.google_ads_campaign cmp USING (campaign_id)
JOIN adh.google_ads_adgroup adg USING (adgroup_id)
LEFT JOIN adh.google_ads_clicks clk ON
imp.impression_id = clk.impression_id
WHERE
imp.customer_id IN UNNEST(@customer_ids)
AND adg.adgroup_type = 'VIDEO_TRUE_VIEW_IN_STREAM'
AND cmp.advertising_channel_type = 'VIDEO'
GROUP BY 1, 2
مقاييس إمكانية العرض في "مساحة العرض والفيديو 360" حسب العناصر
WITH
imp_stats AS (
SELECT
imp.line_item_id,
count(*) as total_imp,
SUM(num_active_view_measurable_impression) AS num_measurable_impressions,
SUM(num_active_view_eligible_impression) AS num_enabled_impressions
FROM adh.dv360_youtube_impressions imp
WHERE
imp.line_item_id IN UNNEST(@line_item_ids)
GROUP BY 1
),
av_stats AS (
SELECT
imp.line_item_id,
SUM(num_active_view_viewable_impression) AS num_viewable_impressions
FROM adh.dv360_youtube_impressions imp
LEFT JOIN
adh.dv360_youtube_active_views av
ON imp.impression_id = av.impression_id
WHERE
imp.line_item_id IN UNNEST(@line_item_ids)
GROUP BY 1
)
SELECT
li.line_item_name,
SUM(imp.total_imp) as num_impressions,
SUM(imp.num_measurable_impressions) AS num_measurable_impressions,
SUM(imp.num_enabled_impressions) AS num_enabled_impressions,
SUM(IFNULL(av.num_viewable_impressions, 0)) AS num_viewable_impressions
FROM imp_stats as imp
LEFT JOIN av_stats AS av USING (line_item_id)
JOIN adh.dv360_youtube_lineitem li ON (imp.line_item_id = li.line_item_id)
GROUP BY 1
طلبات YouTube Reserve
عرض مرات الظهور حسب المعلن
يقيس طلب البحث هذا عدد مرات الظهور والمستخدمين المختلفين لكل معلن. يمكنك استخدام هذه الأرقام لحساب متوسط عدد مرات الظهور لكل مستخدم (أو "معدل تكرار الإعلان").
SELECT
advertiser_name,
COUNT(*) AS imp,
COUNT(DISTINCT user_id) AS users
FROM adh.yt_reserve_impressions AS impressions
JOIN adh.yt_reserve_order order ON impressions.order_id = order.order_id
GROUP BY 1
;
تخطّي الإعلانات
يقيس طلب البحث هذا عدد مرات تخطي الإعلان لكل عميل وحملة ومجموعة إعلانية وتصميم إعلان.
SELECT
impression_data.customer_id,
impression_data.campaign_id,
impression_data.adgroup_id,
impression_data.ad_group_creative_id,
COUNTIF(label = "videoskipped") AS num_skips
FROM
adh.google_ads_conversions
GROUP BY 1, 2, 3, 4;
طلبات البحث العامة
طرح مجموعة من المستخدمين من مجموعة أخرى
يوضح هذا المثال كيفية طرح مجموعة من المستخدمين من مجموعة أخرى. يتضمّن هذا الأسلوب مجموعة كبيرة من التطبيقات، بما في ذلك احتساب الأشخاص الذين لم يجروا إحالات ناجحة، والمستخدمين الذين لم يسجّلوا مرات ظهور قابلة للعرض، والمستخدمين الذين لم يجروا إحالات ناجحة.
WITH exclude AS (
SELECT DISTINCT user_id
FROM adh.google_ads_impressions
WHERE campaign_id = 123
)
SELECT
COUNT(DISTINCT imp.user_id) -
COUNT(DISTINCT exclude.user_id) AS users
FROM adh.google_ads_impressions imp
LEFT JOIN exclude
USING (user_id)
WHERE imp.campaign_id = 876
;
تداخل مخصّص
يقيس طلب البحث هذا التداخل بين حملتَين أو أكثر. يمكن تخصيصها لقياس التداخل بناءً على معايير تقديرية.
/* For this query to run, @campaign_1 and @campaign_2 must be replaced with
actual campaign IDs. */
WITH flagged_impressions AS (
SELECT
user_ID,
SUM(IF(campaign_ID in UNNEST(@campaign_1), 1, 0)) AS C1_impressions,
SUM(IF(campaign_ID in UNNEST(@campaign_2), 1, 0)) AS C2_impressions
FROM adh.cm_dt_impressions
GROUP BY user_ID
SELECT COUNTIF(C1_impressions > 0) as C1_cookie_count,
COUNTIF(C2_impressions > 0) as C2_cookie_count,
COUNTIF(C1_impressions > 0 and C2_impressions > 0) as overlap_cookie_count
FROM flagged_impressions
;
برنامج الإعلانات التي يبيعها الشركاء - بيع منتجات إضافية
يقيس طلب البحث هذا مرات الظهور ونسبة النقر إلى الظهور للمستودع الذي يبيعه الشريك.
SELECT
a.record_date AS record_date,
a.line_item_id AS line_item_id,
a.creative_id AS creative_id,
a.ad_id AS ad_id,
a.impressions AS impressions,
a.click_through AS click_through,
a.video_skipped AS video_skipped,
b.pixel_url AS pixel_url
FROM
(
SELECT
FORMAT_TIMESTAMP('%D', TIMESTAMP_MICROS(i.query_id.time_usec), 'Etc/UTC') AS record_date,
i.line_item_id as line_item_id,
i.creative_id as creative_id,
i.ad_id as ad_id,
COUNT(i.query_id) as impressions,
COUNTIF(c.label='video_click_to_advertiser_site') AS click_through,
COUNTIF(c.label='videoskipped') AS video_skipped
FROM
adh.partner_sold_cross_sell_impressions AS i
LEFT JOIN adh.partner_sold_cross_sell_conversions AS c
ON i.impression_id = c.impression_id
GROUP BY
1, 2, 3, 4
) AS a
JOIN adh.partner_sold_cross_sell_creative_pixels AS b
ON (a.ad_id = b.ad_id)
;
مرات الظهور في متجر التطبيقات
يحسب طلب البحث التالي إجمالي عدد مرات الظهور مجمّعة حسب متجر التطبيقات والتطبيق.
SELECT app_store_name, app_name, COUNT(*) AS number
FROM adh.google_ads_impressions AS imp
JOIN adh.mobile_app_info
USING (app_store_id, app_id)
WHERE imp.app_id IS NOT NULL
GROUP BY 1,2
ORDER BY 3 DESC