Estas consultas de exemplo pressupõem conhecimentos práticos de SQL e BigQuery. Saiba mais acerca do SQL no BigQuery.
Consultas de Transferência de dados do Campaign Manager 360
Faça a correspondência de variáveis do Floodlight com tabelas temporárias
Gere uma correspondência entre user_id e variáveis do Floodlight personalizadas na tabela de atividade. Em seguida, isto pode ser usado para juntar dados originais a dados do 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
Fornecimento de impressões
Este exemplo é útil para a gestão de impressões e mostra como determinar o número de impressões publicadas para além dos limites de frequência ou se certas perspetivas de venda tiveram pouca exposição aos anúncios. Use estes conhecimentos para otimizar os seus sites e as táticas para apresentar o número certo de impressões ao público-alvo escolhido.
/* 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
;
Contagem total de cookies únicos/frequência
Este exemplo ajuda a identificar táticas e formatos de anúncios que geram aumentos ou diminuições na contagem de cookies únicos ou na frequência.
/* 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'
;
Também pode incluir IDs de sites ou de posicionamentos na cláusula WHERE para restringir a consulta.
Contagem total de cookies únicos e frequência média por estado
Este exemplo junta a tabela cm_dt_impressions e a tabela de metadados cm_dt_state para apresentar o número de impressões, as contagens de cookies por estado e a impressão média por utilizador, agrupados por estado geográfico ou província da América do Norte.
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)
;
Públicos-alvo do Display & Video 360
Este exemplo mostra como analisar públicos-alvo do Display & Video 360. Saiba que públicos-alvo as impressões estão a alcançar e se determinados públicos-alvo têm um melhor desempenho do que outros. Estas informações podem ajudar a equilibrar a contagem de cookies únicos (ao apresentar anúncios a muitos utilizadores) e a qualidade (ao restringir a segmentação e as impressões visíveis), consoante os seus objetivos.
/* 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
;
Visibilidade
Estes exemplos mostram como medir as métricas de visibilidade da Vista ativa 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
;
Dados dinâmicos na Transferência de dados do Campaign Manager 360
Número de impressões por perfil dinâmico e feed
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;
Número de impressões por etiqueta de relatórios dinâmica no feed 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;
Número de impressões em que a etiqueta de relatórios = "red" no feed 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;
Número de impressões em que a dimensão de relatórios_1 = "red" e a dimensão de relatórios_2 = "car" no feed 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;
Formatos de anúncios na Transferência de dados do Campaign Manager 360
Estes exemplos mostram como determinar quais os formatos de anúncios que estão a maximizar a contagem de cookies únicos ou a frequência de impressões. Use estas informações para ajudar a equilibrar a contagem total de cookies únicos e a exposição dos utilizadores aos anúncios.
Fornecimento de impressões
/* 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
;
Contagem de cookies únicos e frequência
/* 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 Ads
Impressões de apps para dispositivos móveis com tabelas _rdid
Consulta 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
;
Consulta 2:
SELECT
campaign_id,
COUNT(DISTINCT device_id_md5) AS device_ids
FROM adh.google_ads_impressions_rdid
GROUP BY 1
;
Pode juntar os resultados ao usar campaign_id.
Fornecimento de grupos demográficos
Este exemplo mostra como determinar que campanhas estão a alcançar um determinado grupo demográfico.
/* 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
;
Visibilidade
Para obter uma vista geral da visibilidade com exemplos de consulta, consulte Métricas avançadas da Vista ativa
Definições do fuso horário do anunciante do 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
Tipo de inventário
Este exemplo de consulta demonstra o conceito de tipo de inventário. Pode usar o campo inventory_type para determinar o inventário em que os seus anúncios foram publicados, como o Gmail ou o YouTube Music. Valores possíveis: YOUTUBE, YOUTUBE_TV, YOUTUBE_MUSIC, SEARCH, GMAIL, OTHER. "Other" refere-se à Rede de Display da Google ou à Rede Google Video.
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
Trabalhe com modelos de atribuição
O Ads Data Hub suporta os modelos de atribuição com orientação por dados (AOD) e de atribuição ao último clique (AUC) nas tabelas de conversões do Google Ads. Antes de 19 de setembro de 2023, só era suportada a AUC. Os exemplos seguintes mostram como encontrar conversões que usam qualquer um dos modelos e como usar a tabela de metadados das definições de conversão.
Encontre conversões de atribuição com orientação por dados
Este exemplo encontra conversões que usam o modelo 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;
Encontre conversões de atribuição ao último clique
Para manter o comportamento antigo, adicione uma cláusula WHERE às suas consultas para filtrar
a conversão de atribuição ao último clique dos seus
resultados:
SELECT COUNT(*)
FROM adh.google_ads_conversions
WHERE conversion_type = 123
AND conversion_attribution_model_type = 'LAST_CLICK';
Use a tabela de metadados para filtrar por nome de conversão
A tabela de metadados das definições de conversão permite-lhe filtrar por nomes significativos em vez de números.
Por exemplo, em vez de filtrar as conversões por conversion_type:
SELECT COUNT(*)
FROM adh.google_ads_conversions
WHERE conversion_type = 291496508;
Use uma cláusula JOIN para filtrar usando os campos na tabela de metadados
das definições de conversão:
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;
Consultas de agrupamentos de anúncios do YouTube
Os agrupamentos de anúncios agrupam 2 anúncios numa única pausa para anúncios durante sessões de visualização do YouTube mais longas. (Semelhante a um intervalo publicitário, mas limitado a 2 anúncios.) Os anúncios publicados em agrupamentos de anúncios continuam a ser ignoráveis. No entanto, se um utilizador ignorar o primeiro anúncio, o segundo anúncio também é ignorado.
Impressões de campanhas TrueView in-stream e visualizações TrueView do Google Ads
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
Métricas de visibilidade do Display & Video 360 por elementos publicitários
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
Consultas do YouTube Reserve
Fornecimento de impressões por anunciante
Esta consulta mede o número de impressões e utilizadores distintos por anunciante. Pode usar estes números para calcular o número médio de impressões por utilizador (ou "frequência de anúncios").
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
;
Anúncios ignorados
Esta consulta mede o número de anúncios ignorados por cliente, campanha, grupo de anúncios e criativo.
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;
Consultas gerais
Subtraia um grupo de utilizadores de outro
Este exemplo mostra como subtrair um grupo de utilizadores de outro. Esta técnica tem uma vasta gama de aplicações, incluindo a contabilização de utilizadores sem conversão, utilizadores sem impressões visíveis e utilizadores sem cliques.
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
;
Sobreposição personalizada
Esta consulta mede a sobreposição de 2 ou mais campanhas. Pode ser personalizada para medir a sobreposição com base em critérios discricionários.
/* 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
;
Vendas de parceiros – Venda cruzada
Esta consulta mede as impressões e os cliques de destino de inventário vendido por parceiros.
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)
;
Impressões da loja de apps
A consulta seguinte contabiliza o número total de impressões agrupadas por loja de apps e app.
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