Spark SQL构建用户购买阶段营销活动路径及转化统计

问题分析 #

需要基于用户的广告点击（click）和应用内购买（purchase）事件，为每个用户拆分独立的营销触点路径（每次购买后开启新路径），并统计路径的转化状态及转化次数。

数据集预处理 #

首先将原始数据集注册为临时视图，方便Spark SQL操作：

df.createOrReplaceTempView("user_activity")

完整Spark SQL解决方案 #

WITH user_events AS (
    -- 过滤有效事件，标记事件类型：触点（touch）/转化（conversion）
    SELECT 
        user_id,
        to_timestamp(timestamp) AS event_time,
        CASE 
            WHEN activity = 'click' AND campaign != 'NULL' THEN 'touch'
            WHEN activity = 'event' AND event_name = 'purchase' THEN 'conversion'
        END AS event_type,
        campaign
    FROM user_activity
    WHERE 
        (activity = 'click' AND campaign != 'NULL') 
        OR (activity = 'event' AND event_name = 'purchase')
),
ranked_events AS (
    -- 按用户对事件按时间排序，生成行号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
    FROM user_events
),
path_markers AS (
    -- 为每个事件标记所属路径：以后续第一个触点的行号作为分组依据
    SELECT 
        r1.user_id,
        r1.event_time,
        r1.campaign,
        r1.event_type,
        COALESCE(
            MIN(CASE WHEN r2.event_type = 'touch' THEN r2.rn END) OVER (PARTITION BY r1.user_id ORDER BY r1.rn ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
            MAX(rn) OVER (PARTITION BY r1.user_id) + 1
        ) AS next_touch_rn
    FROM ranked_events r1
),
path_stats AS (
    -- 按用户和路径标记分组，构建路径并统计转化数
    SELECT 
        user_id,
        COLLECT_LIST(CASE WHEN event_type = 'touch' THEN campaign END) AS path,
        SUM(CASE WHEN event_type = 'conversion' THEN 1 ELSE 0 END) AS total_conversions
    FROM path_markers
    GROUP BY user_id, next_touch_rn
),
final_result AS (
    -- 生成转化状态列，过滤空路径
    SELECT 
        user_id,
        path,
        CASE WHEN total_conversions > 0 THEN 1 ELSE 0 END AS converted,
        total_conversions
    FROM path_stats
    WHERE SIZE(path) > 0
)
SELECT * FROM final_result ORDER BY user_id;

结果说明 #

运行上述SQL后，输出结果与需求格式一致：

关键步骤解释 #

1. user_events：过滤无效数据，统一事件类型为触点（广告点击）和转化（购买）。
2. ranked_events：按用户对事件时间排序，生成行号用于后续窗口计算。
3. path_markers：通过窗口函数标记每个事件所属的路径分组，确保购买事件后开启新路径。
4. path_stats：按路径分组收集触点列表，统计对应路径的购买转化次数。
5. final_result：生成转化状态列（1表示有转化，0表示无转化），过滤无触点的无效路径。

内容的提问来源于stack exchange，提问作者programmer987