-- 功能：分析用户从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

;