ACOS有救了！亚马逊AMC营销云实战指南，精准转化高意向客户（附SQL代码）

-- 功能：分析用户从DSP广告和赞助广告（Sponsored Ads）的展示到最终转化的完整路径，
--       计算各路径的覆盖用户数、展示量、成本、销售额等指标，评估不同广告路径的转化效果。
-- 适用场景：广告投放策略优化、转化路径归因分析


-- 1. 整合广告展示数据（DSP + 赞助广告）
-- 作用：收集两类广告的用户展示记录，统一格式并聚合基础指标（首次展示时间、总展示量、总成本）
WITH impressions AS (
   -- 1.1 DSP广告展示数据
   SELECT
     -- 可选配置：如需将特定campaign_id归类（如Awareness/Conversion），可启用下方CASE语句
     -- CASE campaign_id 
     --   WHEN 11111111111111 THEN 'Display_Awareness'
     --   WHEN 222222222222 THEN 'Display_Conversion'
     --   ELSE 'Other' 
     -- END AS campaign,
     'DSP' AS product_type,  -- 广告类型：DSP
     campaign,               -- 广告活动名称（如需重命名可启用上方CASE）
     user_id,                -- 用户唯一标识
     MIN(impression_dt) AS impression_dt_first,  -- 用户对该活动的首次展示时间
     SUM(impressions) AS impressions,            -- 总展示量
     SUM(total_cost) AS total_cost               -- 总成本（原始单位：毫分）
   FROM dsp_impressions
   WHERE user_id IS NOT NULL  -- 过滤无效用户
     -- 可选：如需限定特定活动，可添加条件（如 AND campaign_id IN (xxx, yyy)）
   GROUP BY product_type, campaign, user_id  -- 按广告类型、活动、用户分组

   UNION ALL  -- 合并DSP和赞助广告数据

   -- 1.2 赞助广告展示数据（Sponsored Products/Display/Brands）
   SELECT
     -- 可选配置：如需将特定campaign重命名，可启用下方CASE语句
     -- CASE campaign 
     --   WHEN 'SP_campaign_name1' THEN 'SP_Awareness_name'
     --   WHEN 'SP_campaign_name2' THEN 'SP_Conversion_name'
     --   ... 其他活动映射 ...
     --   ELSE 'Other' 
     -- END AS campaign,
     ad_product_type AS product_type,  -- 广告类型（如sponsored_products）
     campaign,                         -- 广告活动名称（如需重命名可启用上方CASE）
     user_id,                          -- 用户唯一标识
     MIN(event_dt) AS impression_dt_first,  -- 用户对该活动的首次展示时间
     SUM(impressions) AS impressions,        -- 总展示量
     SUM(spend) AS total_cost               -- 总成本（原始单位：微分）
   FROM sponsored_ads_traffic
   WHERE user_id IS NOT NULL  -- 过滤无效用户
     -- 可选：如需限定特定活动，可添加条件（如 AND campaign IN ('xxx', 'yyy')）
   GROUP BY product_type, campaign, user_id  -- 按广告类型、活动、用户分组
),


-- 2. 收集转化数据（购买行为）
-- 作用：提取用户的购买转化记录，关联到对应的广告活动，聚合转化指标
converted AS (
   SELECT
     -- 可选配置：与impressions保持一致的campaign命名规则，需同步启用
     -- CASE campaign_id 
     --   WHEN 11111111111111 THEN 'Display_Awareness'
     --   WHEN 222222222222 THEN 'Display_Conversion'
     --   ELSE 'Other' 
     -- END AS campaign,
     campaign,  -- 广告活动名称（需与impressions中的campaign保持一致）
     user_id,   -- 用户唯一标识
     MAX(conversion_event_dt) AS conversion_event_dt_last,  -- 用户对该活动的最后转化时间
     SUM(product_sales) AS product_sales,                    -- 总销售额
     SUM(purchases) AS purchases                              -- 总购买量
   FROM amazon_attributed_events_by_traffic_time
   WHERE 
     purchases + total_purchases_clicks > 0  -- 过滤有购买行为的记录
     AND user_id IS NOT NULL                 -- 过滤无效用户
     -- 可选：如需限定特定活动，可添加条件（如 AND (campaign_id IN (xxx) OR campaign IN ('yyy'))）
   GROUP BY campaign, user_id  -- 按活动、用户分组
),


-- 3. 过滤有效展示（展示时间早于转化时间）
-- 作用：关联展示数据和转化数据，仅保留"展示在转化之前"的有效记录，并转换成本单位为美元
filter_impressions AS (
   SELECT
     i.user_id AS imp_user_id,         -- 展示用户ID
     c.user_id AS pur_user_id,         -- 转化用户ID（未转化则为NULL）
     i.campaign,                       -- 广告活动
     i.impressions,                    -- 展示量
     i.impression_dt_first,            -- 首次展示时间
     -- 转换成本单位为美元：
     -- 赞助广告（微分→美元：÷1亿），DSP广告（毫分→美元：÷10万）
     CASE 
       WHEN i.product_type IN ('sponsored_products', 'sponsored_display', 'sponsored_brands')
       THEN i.total_cost / 100000000   -- 赞助广告成本转换
       ELSE i.total_cost / 100000      -- DSP广告成本转换
     END AS total_cost,
     c.conversion_event_dt_last,       -- 最后转化时间（未转化则为NULL）
     COALESCE(c.product_sales, 0) AS product_sales,  -- 销售额（未转化则为0）
     COALESCE(c.purchases, 0) AS purchases            -- 购买量（未转化则为0）
   FROM impressions i
   -- 左连接转化数据（按用户+活动关联，确保转化归因到对应的展示）
   LEFT JOIN converted c 
     ON c.user_id = i.user_id 
     AND c.campaign = i.campaign
   -- 过滤条件：仅保留"有转化且展示在转化前"或"无转化"的展示记录
   WHERE 
     (c.user_id IS NOT NULL AND i.impression_dt_first < c.conversion_event_dt_last)  -- 有效转化的展示
     OR c.user_id IS NULL  -- 未转化的展示
),


-- 4. 标记用户广告接触顺序
-- 作用：按用户分组，对其接触的广告活动按"首次展示时间"排序，标记接触顺序
ranked AS (
   SELECT
     -- 创建包含"顺序+活动"的结构化数据（便于后续排序）
     NAMED_ROW(
       'order',  -- 顺序字段（按首次展示时间排序的序号）
       ROW_NUMBER() OVER (
         PARTITION BY f.imp_user_id 
         ORDER BY f.impression_dt_first  -- 按首次展示时间升序（最早的在前）
       ),
       'campaign',  -- 广告活动
       f.campaign
     ) AS campaign_order,
     imp_user_id  -- 用户ID
   FROM filter_impressions f
   WHERE f.imp_user_id IS NOT NULL  -- 过滤无效用户
),


-- 5. 生成用户广告接触路径
-- 作用：按用户分组，将其接触的广告活动按时间顺序汇总为"路径"数组
assembled AS (
   SELECT
     -- 收集用户的所有广告接触记录，去重后按顺序排序，生成路径
     ARRAY_SORT(COLLECT(DISTINCT a.campaign_order)) AS path,
     a.imp_user_id  -- 用户ID
   FROM ranked a
   GROUP BY a.imp_user_id
),


-- 6. 用户级别去重（聚合展示和转化指标）
-- 作用：按用户去重，避免同一用户多条记录重复计算，聚合用户级指标
filter_impressions_dedupe AS (
   SELECT
     imp_user_id,  -- 用户ID
     SUM(impressions) AS impressions,  -- 该用户的总展示量
     SUM(total_cost) AS total_cost,    -- 该用户的总成本
     -- 标记用户是否有转化（1=有转化，0=无转化）
     MAX(CASE WHEN pur_user_id IS NOT NULL THEN 1 ELSE 0 END) AS has_purchase,
     MAX(product_sales) AS product_sales,  -- 该用户的销售额（取最大值，因同一用户可能多条记录）
     MAX(purchases) AS purchases            -- 该用户的购买量（取最大值）
   FROM filter_impressions
   GROUP BY imp_user_id  -- 按用户去重
),


-- 7. 关联路径与效果指标（按路径聚合）
-- 作用：将用户的广告接触路径与用户级指标关联，按路径汇总整体效果
assembled_with_imp_conv AS (
   SELECT
     path,  -- 广告接触路径（按时间排序的活动数组）
     COUNT(DISTINCT a.imp_user_id) AS reach,  -- 该路径覆盖的独立用户数
     SUM(b.impressions) AS total_impressions,  -- 该路径的总展示量
     SUM(b.total_cost) AS total_cost,          -- 该路径的总成本
     SUM(b.has_purchase) AS converted_users,   -- 该路径中有转化的用户数
     SUM(b.product_sales) AS total_sales,      -- 该路径带来的总销售额
     SUM(b.purchases) AS total_purchases       -- 该路径带来的总购买量
   FROM assembled a
   -- 关联用户级去重后的指标
   LEFT JOIN filter_impressions_dedupe b 
     ON a.imp_user_id = b.imp_user_id
   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 
   END AS user_conversion_rate  
FROM assembled_with_imp_conv
-- 可选：如需按转化效果排序，可添加 ORDER BY user_conversion_rate DESC