– 功能：分析用户从DSP广告和赞助广告（Sponsored Ads）的展示到最终转化的完整路径，
– 计算各路径的覆盖用户数、展示量、成本、销售额等指标，评估不同广告路径的转化效果。
– 适用场景：广告投放策略优化、转化路径归因分析
– 1. 整合广告展示数据（DSP + 赞助广告）
– 作用：收集两类广告的用户展示记录，统一格式并聚合基础指标（首次展示时间、总展示量、总成本）
WITH impressions AS (
– 1.1 DSP广告展示数据
SELECT
-- 可选配置：如需将特定campaign_id归类（如Awareness/Conversion），可启用下方CASE语句<br/>
-- CASE campaign_id <br/>
-- WHEN 11111111111111 THEN 'Display_Awareness'<br/>
-- WHEN 222222222222 THEN 'Display_Conversion'<br/>
-- ELSE 'Other' <br/>
-- END AS campaign,<br/>
'DSP' AS product_type, -- 广告类型：DSP<br/>
campaign, -- 广告活动名称（如需重命名可启用上方CASE）<br/>
user_id, -- 用户唯一标识<br/>
MIN(impression_dt) AS impression_dt_first, -- 用户对该活动的首次展示时间<br/>
SUM(impressions) AS impressions, -- 总展示量<br/>
SUM(total_cost) AS total_cost -- 总成本（原始单位：毫分）<br/>
FROM dsp_impressions
WHERE user_id IS NOT NULL – 过滤无效用户
-- 可选：如需限定特定活动，可添加条件（如 AND campaign_id IN (xxx, yyy)）<br/>
GROUP BY product_type, campaign, user_id – 按广告类型、活动、用户分组
UNION ALL – 合并DSP和赞助广告数据
– 1.2 赞助广告展示数据（Sponsored Products/Display/Brands）
SELECT
-- 可选配置：如需将特定campaign重命名，可启用下方CASE语句<br/>
-- CASE campaign <br/>
-- WHEN 'SP_campaign_name1' THEN 'SP_Awareness_name'<br/>
-- WHEN 'SP_campaign_name2' THEN 'SP_Conversion_name'<br/>
-- ... 其他活动映射 ...<br/>
-- ELSE 'Other' <br/>
-- END AS campaign,<br/>
ad_product_type AS product_type, -- 广告类型（如sponsored_products）<br/>
campaign, -- 广告活动名称（如需重命名可启用上方CASE）<br/>
user_id, -- 用户唯一标识<br/>
MIN(event_dt) AS impression_dt_first, -- 用户对该活动的首次展示时间<br/>
SUM(impressions) AS impressions, -- 总展示量<br/>
SUM(spend) AS total_cost -- 总成本（原始单位：微分）<br/>
FROM sponsored_ads_traffic
WHERE user_id IS NOT NULL – 过滤无效用户
-- 可选：如需限定特定活动，可添加条件（如 AND campaign IN ('xxx', 'yyy')）<br/>
GROUP BY product_type, campaign, user_id – 按广告类型、活动、用户分组
),
– 2. 收集转化数据（购买行为）
– 作用：提取用户的购买转化记录，关联到对应的广告活动，聚合转化指标
converted AS (
SELECT
-- 可选配置：与impressions保持一致的campaign命名规则，需同步启用<br/>
-- CASE campaign_id <br/>
-- WHEN 11111111111111 THEN 'Display_Awareness'<br/>
-- WHEN 222222222222 THEN 'Display_Conversion'<br/>
-- ELSE 'Other' <br/>
-- END AS campaign,<br/>
campaign, -- 广告活动名称（需与impressions中的campaign保持一致）<br/>
user_id, -- 用户唯一标识<br/>
MAX(conversion_event_dt) AS conversion_event_dt_last, -- 用户对该活动的最后转化时间<br/>
SUM(product_sales) AS product_sales, -- 总销售额<br/>
SUM(purchases) AS purchases -- 总购买量<br/>
FROM amazon_attributed_events_by_traffic_time
WHERE
purchases + total_purchases_clicks > 0 -- 过滤有购买行为的记录<br/>
AND user_id IS NOT NULL -- 过滤无效用户<br/>
-- 可选：如需限定特定活动，可添加条件（如 AND (campaign_id IN (xxx) OR campaign IN ('yyy'))）<br/>
GROUP BY campaign, user_id – 按活动、用户分组
),
– 3. 过滤有效展示（展示时间早于转化时间）
– 作用：关联展示数据和转化数据，仅保留“展示在转化之前”的有效记录，并转换成本单位为美元
filter_impressions AS (
SELECT
i.user_id AS imp_user_id, -- 展示用户ID<br/>
c.user_id AS pur_user_id, -- 转化用户ID（未转化则为NULL）<br/>
i.campaign, -- 广告活动<br/>
i.impressions, -- 展示量<br/>
i.impression_dt_first, -- 首次展示时间<br/>
-- 转换成本单位为美元：<br/>
-- 赞助广告（微分→美元：÷1亿），DSP广告（毫分→美元：÷10万）<br/>
CASE <br/>
WHEN i.product_type IN ('sponsored_products', 'sponsored_display', 'sponsored_brands')<br/>
THEN i.total_cost / 100000000 -- 赞助广告成本转换<br/>
ELSE i.total_cost / 100000 -- DSP广告成本转换<br/>
END AS total_cost,<br/>
c.conversion_event_dt_last, -- 最后转化时间（未转化则为NULL）<br/>
COALESCE(c.product_sales, 0) AS product_sales, -- 销售额（未转化则为0）<br/>
COALESCE(c.purchases, 0) AS purchases -- 购买量（未转化则为0）<br/>
FROM impressions i
– 左连接转化数据（按用户+活动关联，确保转化归因到对应的展示）
LEFT JOIN converted c
ON c.user_id = i.user_id <br/>
AND c.campaign = i.campaign<br/>
– 过滤条件：仅保留“有转化且展示在转化前”或“无转化”的展示记录
WHERE
(c.user_id IS NOT NULL AND i.impression_dt_first < c.conversion_event_dt_last) -- 有效转化的展示<br/>
OR c.user_id IS NULL -- 未转化的展示<br/>
),
– 4. 标记用户广告接触顺序
– 作用：按用户分组，对其接触的广告活动按“首次展示时间”排序，标记接触顺序
ranked AS (
SELECT
-- 创建包含"顺序+活动"的结构化数据（便于后续排序）<br/>
NAMED_ROW(<br/>
'order', -- 顺序字段（按首次展示时间排序的序号）<br/>
ROW_NUMBER() OVER (<br/>
PARTITION BY f.imp_user_id <br/>
ORDER BY f.impression_dt_first -- 按首次展示时间升序（最早的在前）<br/>
),<br/>
'campaign', -- 广告活动<br/>
f.campaign<br/>
) AS campaign_order,<br/>
imp_user_id -- 用户ID<br/>
FROM filter_impressions f
WHERE f.imp_user_id IS NOT NULL – 过滤无效用户
),
– 5. 生成用户广告接触路径
– 作用：按用户分组，将其接触的广告活动按时间顺序汇总为“路径”数组
assembled AS (
SELECT
-- 收集用户的所有广告接触记录，去重后按顺序排序，生成路径<br/>
ARRAY_SORT(COLLECT(DISTINCT a.campaign_order)) AS path,<br/>
a.imp_user_id -- 用户ID<br/>
FROM ranked a
GROUP BY a.imp_user_id
),
– 6. 用户级别去重（聚合展示和转化指标）
– 作用：按用户去重，避免同一用户多条记录重复计算，聚合用户级指标
filter_impressions_dedupe AS (
SELECT
imp_user_id, -- 用户ID<br/>
SUM(impressions) AS impressions, -- 该用户的总展示量<br/>
SUM(total_cost) AS total_cost, -- 该用户的总成本<br/>
-- 标记用户是否有转化（1=有转化，0=无转化）<br/>
MAX(CASE WHEN pur_user_id IS NOT NULL THEN 1 ELSE 0 END) AS has_purchase,<br/>
MAX(product_sales) AS product_sales, -- 该用户的销售额（取最大值，因同一用户可能多条记录）<br/>
MAX(purchases) AS purchases -- 该用户的购买量（取最大值）<br/>
FROM filter_impressions
GROUP BY imp_user_id – 按用户去重
),
– 7. 关联路径与效果指标（按路径聚合）
– 作用：将用户的广告接触路径与用户级指标关联，按路径汇总整体效果
assembled_with_imp_conv AS (
SELECT
path, -- 广告接触路径（按时间排序的活动数组）<br/>
COUNT(DISTINCT a.imp_user_id) AS reach, -- 该路径覆盖的独立用户数<br/>
SUM(b.impressions) AS total_impressions, -- 该路径的总展示量<br/>
SUM(b.total_cost) AS total_cost, -- 该路径的总成本<br/>
SUM(b.has_purchase) AS converted_users, -- 该路径中有转化的用户数<br/>
SUM(b.product_sales) AS total_sales, -- 该路径带来的总销售额<br/>
SUM(b.purchases) AS total_purchases -- 该路径带来的总购买量<br/>
FROM assembled a
– 关联用户级去重后的指标
LEFT JOIN filter_impressions_dedupe b
ON a.imp_user_id = b.imp_user_id<br/>
GROUP BY path – 按路径分组汇总
)
– 最终输出：各广告路径的效果指标
SELECT
path,
reach AS path_user_count, – 路径覆盖用户数
total_impressions, – 总展示量
total_cost, – 总成本（美元）
converted_users, – 转化用户数
total_sales, – 总销售额
total_purchases, – 总购买量
– 计算用户购买率（避免除零错误：当reach=0时返回0）
CASE WHEN reach = 0 THEN 0
ELSE converted_users / reach <br/>
END AS user_conversion_rate
FROM assembled_with_imp_conv
– 可选：如需按转化效果排序，可添加 ORDER BY user_conversion_rate DESC
;