Эти примеры запросов предполагают знание 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
;
Общее количество/частота уникальных файлов cookie
Этот пример помогает определить тактики и форматы рекламы, которые приводят к увеличению или уменьшению количества или частоты использования уникальных файлов cookie.
/* 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, чтобы сузить запрос.
Общее количество уникальных файлов cookie и средняя частота по штатам.
В этом примере таблица cm_dt_impressions и таблица метаданных cm_dt_state объединяются, чтобы показать общее количество показов, количество файлов cookie для каждого штата и среднее количество показов по пользователю, сгруппированных по географическим штатам или провинциям Северной Америки.
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
В этом примере показано, как анализировать аудитории Display and Video 360. Узнайте, какие аудитории получают показы, и определите, работают ли одни аудитории лучше, чем другие. Эти знания могут помочь сбалансировать количество уникальных файлов cookie (показ рекламы большому количеству пользователей) и качество (узкий таргетинг и видимые показы) в зависимости от ваших целей.
/* 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
;
Видимость
В этом примере показано, как измерить показатели видимости Active View Plus.
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
В этих примерах показано, как определить, какие форматы объявлений обеспечивают максимальное количество уникальных файлов cookie или частоту показов. Используйте эти знания, чтобы сбалансировать общее количество уникальных файлов cookie и воздействие рекламы на пользователей.
Доставка показов
/* 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
;
Уникальное количество и частота использования файлов cookie
/* 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
;
Видимость
Обзор видимости с примерами запросов см. в разделе «Расширенные показатели Active View».
Настройки часового пояса рекламодателя 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
Работа с моделями атрибуции
Ads Data Hub поддерживает модели атрибуции на основе данных (DDA) и атрибуции по последнему клику (LCA) в таблицах конверсий Google Рекламы. До 19 сентября 2023 г. поддерживалась только LCA. В следующих примерах показано, как найти конверсии, использующие любую модель, и как использовать таблицу метаданных настроек конверсии.
Найдите конверсии атрибуции на основе данных
В этом примере находятся конверсии, использующие модель DDA:
SELECT
s.name
SUM(conv.num_conversion_micros)/1000000 AS num_convs
FROM adh.google_ads_conversions AS conv
JOIN adh.google_ads_conversion_settings AS s
ON (conv.conversion_type = s.conversion_type_id)
WHERE s.action_optimization = 'Primary'
AND s.attribution_model = 'DATA_DRIVEN'
GROUP BY 1;
Найдите конверсии атрибуции по последнему клику
Чтобы сохранить устаревшее поведение, добавьте в свои запросы предложение WHERE , чтобы фильтровать конверсии атрибуции по последнему клику в результатах:
SELECT COUNT(*)
FROM adh.google_ads_conversions
WHERE conversion_type = 123
AND conversion_attribution_model_type = 'LAST_CLICK';
Используйте таблицу метаданных для фильтрации по названию конверсии.
Таблица метаданных настроек преобразования позволяет фильтровать по значимым именам, а не по числам.
Например, вместо фильтрации конверсий по conversion_type :
SELECT COUNT(*)
FROM adh.google_ads_conversions
WHERE conversion_type = 291496508;
Используйте предложение JOIN для фильтрации по полям в таблице метаданных настроек преобразования:
SELECT SUM(num_conversion_micros)/1000000 AS num_convs
FROM adh.google_ads_conversions AS conv
JOIN adh.google_ads_conversion_settings AS s
ON (conv.conversion_type = s.conversion_type_id)
WHERE s.name = 'LTH Android Order';
SELECT s.name, SUM(conv.num_conversion_micros)/1000000 AS num_convs
FROM adh.google_ads_conversions AS conv
JOIN adh.google_ads_conversion_settings AS s
ON (conv.conversion_type = s.conversion_type_id)
WHERE s.conversion_category = 'PURCHASE'
AND s.action_optimization = 'Primary'
GROUP BY 1;
Запросы модулей объявлений YouTube
Рекламные блоки группируют 2 объявления в одну рекламную паузу во время длительных сеансов просмотра на YouTube. (Вспомните рекламную паузу, но ограниченную двумя объявлениями.) Объявления, отображаемые в рекламных блоках, по-прежнему можно пропустить. Однако если пользователь пропускает первое объявление, второе объявление также будет пропущено.
Показы кампании GoogleAds Trueview In-Stream и просмотры TrueView
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
Показатели видимости в Display and Video 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
Доставка показов рекламодателем
Этот запрос измеряет количество показов и отдельных пользователей на каждого рекламодателя. Вы можете использовать эти цифры для расчета среднего количества показов на одного пользователя (или «частоты показа рекламы»).
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