通常情况下，不同表中同一购买渠道内名称相同的字段可联接在一起。例如，adh.google_ads_impressions.impression_id 可与 adh.google_ads_clicks.impression_id 联接在一起。在此示例中，Google 展示广告是共用购买渠道，impression_id 是共用字段，google_ads_impressions 和 google_ads_clicks 是两个不同的表。
一些复杂情况发生时，可能会导致难以联接不同购买渠道的数据。不同的 Google 产品会使用不同的用户 ID，并且单个产品内的用户 ID 也可能会因登录状态而异。
请参考下表，了解如何跨产品联接。通常情况下，您可以在每个购买渠道内进行联接，但不能在不同的购买渠道之间联接。
| 购买渠道 | 产品 | 表 | 可联接的 ID |
|---|---|---|---|
| Google 展示广告 | Google 视频合作伙伴（不包括 YouTube） | adh.google_ads_*
|
|
| Google Marketing Platform | 通过数据传输： Display & Video 360 Campaign Manager 360 |
adh.dv360_dt_* adh.cm_dt_*
|
|
| YouTube Google 出售计划 | YouTube 预订型广告资源 YouTube（在 Google Ads 中） YouTube（在 Display & Video 360 中） |
adh.yt_reserve_*
adh.google_ads_*
adh.dv360_youtube_*
|
|
| YouTube 合作伙伴出售计划 | Google Ad Manager Freewheel |
adh.partner_sold_cross_sell_*
adh.freewheel_* |
|
| 键 | user_id |
external_cookie |
device_id_md5 |
示例
除了用户 ID 和设备 ID 之外，您还可以使用多个其他字段来联接表。如需了解如何在广告数据中心联接表，请从下拉菜单中选择可联接的字段。本部分仅列举部分示例。
- 全部
- user_id
- impression_id
- campaign_id
- customer_id
- region_id
此示例演示了如何使用 user_id 联接展示次数表、广告素材表和转化次数表。
应用场景：了解品牌塑造广告系列是否进一步提高转化次数。
WITH imp AS (
SELECT
user_id,
COUNTIF(campaign_id IN UNNEST(@brand_campaign_ids)) AS brand_impression,
COUNTIF(campaign_id IN UNNEST(@perf_campaign_ids)) AS perf_impression,
FROM adh.google_ads_impressions
WHERE
campaign_id IN UNNEST(ARRAY_CONCAT(@perf_campaign_ids, @brand_campaign_ids))
AND user_id != '0'
GROUP BY 1
),
conv AS (
SELECT
c.user_id,
COUNT(1) AS conv_count
FROM adh.google_ads_conversions
WHERE
impression_data.campaign_id IN UNNEST(@perf_campaign_ids)
AND conversion_type IN UNNEST(@conversion_type_list)
AND user_id != '0'
GROUP BY 1
)
SELECT
SUM(IF(has_brand_traffic AND NOT has_perf_traffic, conv_count, 0)) AS brand_only,
SUM(IF(NOT has_brand_traffic AND has_perf_traffic, conv_count, 0)) AS perf_only,
SUM(IF(has_brand_traffic AND has_perf_traffic, conv_count, 0)) AS overlap
FROM
(
SELECT
imp.user_id,
imp.brand_impression > 0 AS has_brand_traffic,
imp.perf_impression > 0 AS has_perf_traffic,
conv.conv_count
FROM imp
JOIN conv
USING (user_id)
)
此示例演示了如何使用 impression_id 将转化数据与展示次数数据相关联。
应用场景：根据国家/地区和点击型转化 (CTC)/感兴趣的观看转化 (EVC) 细分展示和转化统计信息。
SELECT
imp.location.country,
COUNT(1) AS num_imps,
SUM(IF(attribution_event_type = 'CLICK', 1, 0)) AS ctc_convs,
SUM(IF(attribution_event_type = 'ENGAGED_VIEW', 1, 0)) AS evc_convs
FROM adh.google_ads_impressions AS imp
LEFT JOIN adh.google_ads_conversions AS conv
ON (
imp.impression_id = conv.impression_id
AND conv.conversion_type IN UNNEST(@conversion_type_list))
WHERE imp.campaign_id IN UNNEST(@campaign_ids)
GROUP BY 1
此示例演示了如何根据多个 ID 联接多个表。
应用场景：列出已关联到特定广告系列的素材资源。
SELECT
cmp.campaign_id,
adg.adgroup_id,
cr.video_message.youtube_video_id
FROM adh.google_ads_campaign AS cmp
JOIN adh.google_ads_adgroup AS adg
USING (campaign_id)
JOIN adh.google_ads_adgroupcreative AS agc
USING (adgroup_id)
JOIN adh.google_ads_creative AS cr
ON (agc.customer_id = cr.customer_id
AND agc.creative_id = cr.creative_id)
WHERE campaign_id = 123
GROUP BY 1, 2, 3
此示例演示了如何联接元数据表。
应用场景：将展示次数表与状态元数据表联接起来，并按状态显示唯一身份 Cookie 数量和平均频次。
SELECT
IFNULL(reg.region_name, 'unspecified') AS state,
COUNT(DISTINCT user_id) AS users,
COUNT(1) AS impressions,
FORMAT('%0.2f', COUNT(1) / COUNT(DISTINCT user_id)) AS avg_imp_per_user
FROM adh.google_ads_impressions AS imp
LEFT JOIN adh.region AS reg
ON (imp.location.geo_region_id = reg.region_id)
WHERE
imp.location.country = 'US'
GROUP BY 1