Эти примеры запросов предполагают наличие базовых знаний SQL и BigQuery. Подробнее о SQL в BigQuery можно узнать здесь.
Запросы на передачу данных в Campaign Manager 360
Сопоставьте переменные прожектора с временными таблицами.
Создайте сопоставление между user_id и пользовательскими переменными Floodlight в таблице активности. Затем это можно использовать для объединения собственных данных с данными Campaign Manager 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.
Этот пример демонстрирует анализ аудиторий в контекстной и видеорекламе (Display and Video 360 audiences). Узнайте, до каких аудиторий доходят показы, и определите, какие аудитории показывают лучшие результаты, чем другие. Эти знания помогут сбалансировать количество уникальных 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
;
Динамические данные в Campaign Manager 360 Data Transfer
Количество показов на динамический профиль и ленту
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;
Количество показов, где в ленте указано dimension_1 = 'red' и dimension_2 = 'car': 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;
Форматы объявлений в Campaign Manager 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
;
Видимость
Обзор показателей видимости с примерами запросов см. в разделе «Расширенные метрики активных представлений».
Настройки часового пояса рекламодателя Google Ads
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 , GOOGLE_TV , OTHER . «Другое» относится либо к контекстно-медийной сети Google, либо к видеосети 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 Ads. До 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
В длительных сеансах просмотра YouTube рекламные блоки объединяют два объявления в один рекламный блок. (Представьте себе рекламную паузу, но ограниченную двумя объявлениями.) Объявления, показываемые в рекламных блоках, можно пропустить. Однако, если пользователь пропускает первое объявление, второе объявление также пропускается.
Показы и просмотры кампании Google Ads Trueview Instream
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_creative_conversions 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
Доставка показов рекламодателем
Этот запрос измеряет количество показов и уникальных пользователей для каждого рекламодателя. Вы можете использовать эти данные для расчета среднего количества показов на пользователя (или «частоты показа рекламы»).
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_creative_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)
;
Показы в App Store
Следующий запрос подсчитывает общее количество показов, сгруппированных по магазину приложений и самому приложению.
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