Snowflake中基于Amplitude数据实现Last Non-Direct Click归因

针对存储在Snowflake的Amplitude会话数据集复现Last Non-Direct Click归因的需求，全程使用Snowflake原生优化的窗口函数实现，无自关联、无笛卡尔积，亿级会话体量下可直接并行跑数，计算逻辑100%匹配GA官方归因规则。

核心逻辑拆解 #

先把规则转化为可批量计算的分组逻辑，避免逐行回溯的低效操作：

• 所有非direct的会话本身就是归因锚点，会作为后续连续direct会话的归因值
• 从一个非direct会话开始，到下一个非direct会话出现前的所有连续direct会话，和当前锚点归为同一组
• 最开头没有任何前置非direct锚点的连续direct会话（即首条会话为direct的场景），归因值统一为direct

实现代码 #

假设源会话表为amplitude_sessions，包含user_id（用户唯一标识）、session_id、session_start_ts（会话开始时间戳）、marketing_channel（原始营销渠道）四个字段，SQL如下：

WITH session_with_group AS (
    SELECT
        user_id,
        session_id,
        session_start_ts,
        marketing_channel,
        -- 按用户、时间顺序累计非direct渠道出现次数，生成连续分组ID
        SUM(CASE WHEN marketing_channel != 'direct' THEN 1 ELSE 0 END) OVER (
            PARTITION BY user_id
            ORDER BY session_start_ts ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS channel_group_id
    FROM amplitude_sessions
)
SELECT
    user_id,
    session_id,
    session_start_ts,
    marketing_channel AS original_marketing_channel,
    -- 同组内向前取最近的非direct渠道，无匹配值时填direct
    COALESCE(
        LAST_VALUE(CASE WHEN marketing_channel != 'direct' THEN marketing_channel END IGNORE NULLS) OVER (
            PARTITION BY user_id, channel_group_id
            ORDER BY session_start_ts ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
        'direct'
    ) AS attributed_channel
FROM session_with_group
ORDER BY user_id, session_start_ts ASC;

逻辑校验（完全匹配规则场景） #

• 首条会话为direct时，所属分组ID为0，组内无非direct锚点，COALESCE自动返回direct
• 非direct渠道的会话，会触发分组ID+1，组内LAST_VALUE直接取到自身渠道值作为归因结果
• 非direct渠道后的连续direct会话，分组ID和前序锚点一致，会自动填充锚点渠道值：比如会话4为organic search，后续会话5/6/7均为direct，四个会话同属一个分组，三个direct会话的归因值均为organic search；会话3为direct时，和前序paid渠道同组，归因值为paid

性能说明 #

• 全程仅对源表做两次窗口函数扫描，无join、无自定义UDF，Snowflake MPP引擎会自动按user_id分区并行计算，无需额外调优
• 避免了常规自关联写法的笛卡尔积风险，不会出现多渠道交叉时抓错最近非direct值的问题

注意：不要省略channel_group_id分组直接做全局IGNORE NULLS填充，否则会出现跨非direct渠道的串值问题——比如新的非direct渠道出现后，后续direct会话会错误填充更早的渠道值，分组逻辑可以严格保证每个direct块只取块内的锚点值。

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