WITH touchpoints_ranked AS (
SELECT
user_id,
channel,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS touch_order
FROM marketing_touchpoints
WHERE event_type != 'purchase'
),
first_channel AS (
SELECT user_id, channel AS first_touch_channel
FROM touchpoints_ranked
WHERE touch_order = 1
)
SELECT
fc.first_touch_channel,
COUNT(*) AS conversions,
SUM(c.conversion_value) AS total_value,
ROUND(SUM(c.conversion_value) * 100.0 / (SELECT SUM(conversion_value) FROM conversions), 1) AS value_share_pct
FROM first_channel fc
JOIN conversions c ON fc.user_id = c.user_id
GROUP BY fc.first_touch_channel
ORDER BY total_value DESC;
输出示例:
first_touch_channel
conversions
total_value
value_share_pct
paid_ads
1
200
40.0
social_media
1
150
30.0
organic_search
1
100
20.0
email
1
50
10.0
2. 末次触达归因 (Last-Touch)
规则
转化金额 100% 归因给转化前的 最后一个触点。
SQL 实现
WITH touchpoints_ranked AS (
SELECT
user_id,
channel,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS touch_order_desc
FROM marketing_touchpoints
WHERE event_type != 'purchase'
),
last_channel AS (
SELECT user_id, channel AS last_touch_channel
FROM touchpoints_ranked
WHERE touch_order_desc = 1
)
SELECT
lc.last_touch_channel,
COUNT(*) AS conversions,
SUM(c.conversion_value) AS total_value,
ROUND(SUM(c.conversion_value) * 100.0 / (SELECT SUM(conversion_value) FROM conversions), 1) AS value_share_pct
FROM last_channel lc
JOIN conversions c ON lc.user_id = c.user_id
GROUP BY lc.last_touch_channel
ORDER BY total_value DESC;
输出示例:
last_touch_channel
conversions
total_value
value_share_pct
paid_ads
2
250
50.0
email
2
250
50.0
3. 线性归因 (Linear)
规则
转化金额 平均分配 给所有触点。
SQL 实现
WITH touch_counts AS (
SELECT user_id, COUNT(*) AS touch_count
FROM marketing_touchpoints
WHERE event_type != 'purchase'
GROUP BY user_id
),
attribution AS (
SELECT
t.user_id,
t.channel,
c.conversion_value / tc.touch_count AS attributed_value
FROM marketing_touchpoints t
JOIN conversions c ON t.user_id = c.user_id
JOIN touch_counts tc ON t.user_id = tc.user_id
WHERE t.event_type != 'purchase'
)
SELECT
channel,
COUNT(*) AS touch_count,
ROUND(SUM(attributed_value), 2) AS total_attributed_value,
ROUND(SUM(attributed_value) * 100.0 / (SELECT SUM(conversion_value) FROM conversions), 1) AS value_share_pct
FROM attribution
GROUP BY channel
ORDER BY total_attributed_value DESC;
WITH conversions AS (
-- 实际生产中从订单表获取转化时间
SELECT 1 AS user_id, '2026-03-02 10:30:00' AS conversion_time, 100.0 AS conversion_value UNION ALL
SELECT 2, '2026-03-02 09:00:00', 200.0 UNION ALL
SELECT 3, '2026-03-02 14:30:00', 150.0 UNION ALL
SELECT 4, '2026-03-01 12:30:00', 50.0
),
touch_with_decay AS (
SELECT
t.user_id,
t.channel,
c.conversion_value,
TIMESTAMPDIFF(HOUR, CAST(t.event_time AS TIMESTAMP_LTZ), CAST(c.conversion_time AS TIMESTAMP_LTZ)) AS hours_to_conversion,
-- 指数衰减权重: 2(-hours/24)，半衰期 24 小时
POW(2, -TIMESTAMPDIFF(HOUR, CAST(t.event_time AS TIMESTAMP_LTZ), CAST(c.conversion_time AS TIMESTAMP_LTZ)) / 24.0) AS decay_weight
FROM marketing_touchpoints t
JOIN conversions c ON t.user_id = c.user_id
WHERE t.event_type != 'purchase'
),
normalized AS (
SELECT
user_id,
channel,
conversion_value,
decay_weight,
decay_weight / SUM(decay_weight) OVER (PARTITION BY user_id) AS normalized_weight
FROM touch_with_decay
)
SELECT
channel,
COUNT(*) AS touch_count,
ROUND(SUM(conversion_value * normalized_weight), 2) AS total_attributed_value,
ROUND(SUM(conversion_value * normalized_weight) * 100.0 / (SELECT SUM(conversion_value) FROM conversions), 1) AS value_share_pct
FROM normalized
GROUP BY channel
ORDER BY total_attributed_value DESC;
输出示例:
channel
touch_count
total_attributed_value
value_share_pct
paid_ads
3
190.90
38.2
email
2
161.50
32.3
social_media
3
96.30
19.3
organic_search
2
51.29
10.3
半衰期选择
业务类型
推荐半衰期
原因
电商促销
6-12 小时
决策周期短
SaaS 试用
24-48 小时
中等决策周期
B2B 销售
72-168 小时
长决策周期
5. 位置归因 (U-Shape)
规则
触点位置
权重
首个触点
40%
末个触点
40%
中间触点
平分剩余 20%
特殊情况:
单触点: 100%
2 触点: 各 50%
SQL 实现
WITH touchpoints_ranked AS (
SELECT
t.user_id,
t.channel,
c.conversion_value,
ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.event_time) AS touch_order,
COUNT(*) OVER (PARTITION BY t.user_id) AS total_touches
FROM marketing_touchpoints t
JOIN conversions c ON t.user_id = c.user_id
WHERE t.event_type != 'purchase'
),
attribution AS (
SELECT
user_id,
channel,
conversion_value,
CASE
WHEN total_touches = 1 THEN 1.0
WHEN total_touches = 2 THEN 0.5
WHEN touch_order = 1 THEN 0.4
WHEN touch_order = total_touches THEN 0.4
ELSE 0.2 / (total_touches - 2)
END AS weight
FROM touchpoints_ranked
)
SELECT
channel,
COUNT(*) AS touch_count,
ROUND(SUM(conversion_value * weight), 2) AS total_attributed_value,
ROUND(SUM(conversion_value * weight) * 100.0 / (SELECT SUM(conversion_value) FROM conversions), 1) AS value_share_pct
FROM attribution
GROUP BY channel
ORDER BY total_attributed_value DESC;
输出示例:
channel
touch_count
total_attributed_value
value_share_pct
paid_ads
3
200.00
40.0
email
2
150.00
30.0
social_media
3
95.00
19.0
organic_search
2
55.00
11.0
6. 多模型对比
一键对比所有模型
-- 将各模型结果合并对比
WITH first_touch AS (
-- 首次触达归因结果
SELECT channel, ROUND(SUM(conversion_value), 2) AS first_touch_value
FROM (
SELECT user_id, channel, conversion_value
FROM touchpoints_ranked tr
JOIN conversions c ON tr.user_id = c.user_id
WHERE touch_order = 1
)
GROUP BY channel
),
last_touch AS (
-- 末次触达归因结果
SELECT channel, ROUND(SUM(conversion_value), 2) AS last_touch_value
FROM (
SELECT user_id, channel, conversion_value
FROM touchpoints_ranked tr
JOIN conversions c ON tr.user_id = c.user_id
WHERE touch_order = total_touches
)
GROUP BY channel
)
-- 合并对比...
常见问题
1. 排除转化事件本身
-- 错误: 将 purchase 事件也计入触点
WHERE event_type IN ('view', 'click', 'purchase')
-- 正确: 排除转化事件
WHERE event_type != 'purchase'
-- 建议在分析中区分
SELECT
CASE WHEN total_touches = 1 THEN 'single_touch'
WHEN total_touches <= 3 THEN 'multi_touch'
ELSE 'high_touch'
END AS journey_type,
COUNT(*) AS user_count
FROM touchpoints_ranked
GROUP BY 1;
5. 归因窗口期
-- 限制归因窗口（如 30 天内触点）
WHERE TIMESTAMPDIFF(DAY, CAST(t.event_time AS TIMESTAMP_LTZ), CAST(c.conversion_time AS TIMESTAMP_LTZ)) <= 30
模型选择建议
业务目标
推荐模型
原因
评估拉新渠道效果
首次触达
反映渠道获取新用户能力
优化转化漏斗
末次触达
反映临门一脚的渠道
公平分配预算
线性
所有触点平等对待
短周期促销
时间衰减
近期触点影响更大
全链路 ROI 报告
U-Shape
兼顾首末和中间触点
性能优化建议
场景
优化策略
大数据量归因
按转化日期分区，限制归因窗口
多模型并行计算
将触点排序结果物化为 Dynamic Table
高频查询
预计算归因结果，按天刷新
-- 推荐: 将触点排序物化为 Dynamic Table
CREATE DYNAMIC TABLE dws_touchpoints_ranked
REFRESH INTERVAL 1 HOUR
AS
SELECT
t.user_id,
t.channel,
t.event_time,
t.event_type,
ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.event_time) AS touch_order,
COUNT(*) OVER (PARTITION BY t.user_id) AS total_touches
FROM marketing_touchpoints t
WHERE event_type != 'purchase';
