一般來說，相同購買平台中名稱相同的欄位可以跨資料表彙整在一起。例如，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
Reserve 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 以外，您也可以使用某些其他欄位來彙整資料表。如要瞭解如何在廣告資料中心彙整資料表，請從下拉式選單中選取一個可彙整的欄位。本節僅列舉部分內容做為範例。
- 全部
- 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 將轉換資料連結至曝光資料。
用途：根據國家/地區和點閱後轉換/參與收視轉換區分曝光和轉換統計資料。
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