广告投放 ROI 分析：多渠道归因模型的选择与 SQL 实现

广告投放 ROI 分析多渠道归因模型的选择与 SQL 实现大家好我是朱大喜做了这么久数据分析我发现最让运营和市场同学头疼的问题之一就是——我每个月花了几百万投广告到底哪部分钱花得值今天咱们就聊聊多渠道归因这件事从理论选型到 SQL 落地一文讲透。一、归因模型的本质与选型先说说什么是归因。简单理解就是一个用户最终下单了但在下单前他可能点过百度广告、看过抖音直播、收到过短信优惠券还通过朋友分享进入过小程序。那这单交易到底应该算在哪个渠道的功劳上这就引出了不同的归因模型。每种模型背后都是一种功劳分配逻辑没有绝对的对错只有适不适合你的业务。常见的归因模型有这几种末次点击归因Last Click功劳 100% 归给最后一次触达渠道。简单粗暴电商行业用得最多但容易高估收割型渠道如品牌词搜索低估种草型渠道如今日头条信息流。首次点击归因First Click功劳 100% 归给第一次触达渠道。适合评估渠道获客能力但忽略了转化过程中其他渠道的助攻作用。线性归因Linear所有触达渠道平均分配功劳。相对公平但抹平了不同渠道在用户决策不同阶段的影响力差异。时间衰减归因Time Decay越靠近转化时刻的渠道分到的功劳越多。符合决策越近影响力越大的直觉。位置归因Position Based / U-Shaped首次和末次各分 40%中间的渠道均分剩下的 20%。这是对首次和末次的折中版本。数据驱动归因Data-Driven通过机器学习模型如 Shapley Value、Markov Chain来分配功劳。理论上最科学但实现成本高。在实际项目中我们通常会建议客户同时采用两到三种归因模型做对比分析。比如用末次点击来看直接转化效果用位置归因来平衡获客和转化两个模型的结果一交叉渠道的真实价值就清晰了。二、用户行为路径的数据准备归因分析的第一步是拼出每个用户的完整行为路径。在数据仓库中用户行为通常分散在多张表中——广告点击日志、APP 启动日志、订单表等。我们需要用 SQL 把它们按时间顺序串起来。-- 构建用户完整行为路径表 -- 将广告点击、APP启动、订单等行为按用户时间串联 WITH user_behavior_path AS ( SELECT user_id, event_time, event_type, channel, campaign_id, -- 使用窗口函数生成序号用于后续分析 ROW_NUMBER() OVER ( PARTITION BY user_id, DATE(event_time) ORDER BY event_time ) AS touch_seq, -- 当天是否有下单 MAX(CASE WHEN event_type order THEN 1 ELSE 0 END) OVER ( PARTITION BY user_id, DATE(event_time) ) AS has_conversion FROM ( -- 广告点击事件 SELECT user_id, click_time AS event_time, ad_click AS event_type, channel, campaign_id FROM dwd_ad_click_log WHERE click_time 2026-06-01 AND click_time 2026-07-01 UNION ALL -- 订单事件 SELECT user_id, order_time AS event_time, order AS event_type, direct AS channel, -- 自然流量标记为 direct NULL AS campaign_id FROM dwd_order_detail WHERE order_time 2026-06-01 AND order_time 2026-07-01 AND order_status PAID ) all_events ), -- 提取有转化的用户路径 -- 只保留当天有下单行为的用户且只取下单前的触达 converted_paths AS ( SELECT user_id, DATE(event_time) AS event_date, channel, campaign_id, touch_seq, -- 找到当天订单的触达序号用于截取下单前的触达 MAX(CASE WHEN event_type order THEN touch_seq END) OVER ( PARTITION BY user_id, DATE(event_time) ) AS order_seq FROM user_behavior_path WHERE has_conversion 1 AND event_type ad_click -- 只保留广告触达排除订单本身 ) SELECT user_id, event_date, channel, campaign_id, touch_seq FROM converted_paths WHERE touch_seq order_seq -- 只取下单前的触达 ORDER BY user_id, event_date, touch_seq;这段 SQL 的关键在于用ROW_NUMBER()给每个用户在当天的所有行为打上序号然后用MAX(CASE WHEN ...)标记出订单发生的位置。这样我们就能截取下单前的所有广告触达形成完整的转化路径。三、各归因模型的 SQL 实现有了用户行为路径我们就可以在上面跑各种归因逻辑了。下面分别实现末次点击、首次点击、线性归因和位置归因四种模型最终的归因结果统一写入一张归因结果表。-- 归因结果汇总表 -- 使用多表 UNION ALL 将不同归因模型的结果合并 WITH user_paths AS ( -- 复用上一步的用户路径查询 SELECT user_id, event_date, channel, campaign_id, touch_seq, -- 标记首次和末次 FIRST_VALUE(touch_seq) OVER (PARTITION BY user_id, event_date ORDER BY touch_seq) AS first_seq, LAST_VALUE(touch_seq) OVER ( PARTITION BY user_id, event_date ORDER BY touch_seq ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_seq, -- 总触达次数 COUNT(*) OVER (PARTITION BY user_id, event_date) AS total_touches FROM user_behavior_path WHERE event_type ad_click AND touch_seq ( SELECT MIN(touch_seq) FROM user_behavior_path up2 WHERE up2.user_id user_behavior_path.user_id AND up2.event_date user_behavior_path.event_date AND up2.event_type order ) ), -- 末次点击归因只有最后一次触达的渠道分到100% last_click_attribution AS ( SELECT event_date, channel, campaign_id, COUNT(DISTINCT user_id) AS attributed_conversions, last_click AS model_name FROM user_paths WHERE touch_seq last_seq -- 只取末次触达 GROUP BY event_date, channel, campaign_id ), -- 首次点击归因只有第一次触达的渠道分到100% first_click_attribution AS ( SELECT event_date, channel, campaign_id, COUNT(DISTINCT user_id) AS attributed_conversions, first_click AS model_name FROM user_paths WHERE touch_seq first_seq -- 只取首次触达 GROUP BY event_date, channel, campaign_id ), -- 线性归因每个触达渠道平均分配功劳 linear_attribution AS ( SELECT event_date, channel, campaign_id, -- 每个用户对该渠道贡献 1/total_touches 的功劳 SUM(1.0 / total_touches) AS attributed_conversions, linear AS model_name FROM user_paths GROUP BY event_date, channel, campaign_id ), -- 位置归因U-Shaped首次40% 末次40% 中间20%均分 position_attribution AS ( SELECT event_date, channel, campaign_id, SUM( CASE WHEN touch_seq first_seq THEN 0.4 -- 首次触达40% WHEN touch_seq last_seq THEN 0.4 -- 末次触达40% WHEN total_touches 2 THEN 0.2 / (total_touches - 2) -- 中间渠道均分20% ELSE 0 END ) AS attributed_conversions, position_based AS model_name FROM user_paths GROUP BY event_date, channel, campaign_id ) -- 合并所有归因模型结果 SELECT * FROM last_click_attribution UNION ALL SELECT * FROM first_click_attribution UNION ALL SELECT * FROM linear_attribution UNION ALL SELECT * FROM position_attribution;四、归因结果的业务解读与 ROI 计算归因结果出来后结合各渠道的投放成本就能算出真正的 ROI 了。这是整个分析最有价值的部分——让每一分钱都花得明明白白。import pandas as pd import matplotlib.pyplot as plt # 加载归因结果与渠道成本 # 从 MySQL 读取归因结果表 attribution_df pd.read_sql( SELECT event_date, channel, model_name, SUM(attributed_conversions) AS conversions FROM attribution_results WHERE event_date 2026-06-01 GROUP BY event_date, channel, model_name , conn) # 各渠道的广告投放成本数据通常从广告后台导出 channel_cost pd.DataFrame({ channel: [百度SEM, 今日头条, 抖音直播, 微信朋友圈, 小红书], daily_cost: [15000, 12000, 20000, 8000, 5000], # 日均投放成本元 }) # 合并归因结果与渠道成本 # 计算各渠道在末次点击归因下的 ROI last_click attribution_df[attribution_df[model_name] last_click] roi_df last_click.groupby(channel)[conversions].sum().reset_index() roi_df roi_df.merge(channel_cost, onchannel, howleft) # ROI (转化价值 - 投放成本) / 投放成本 # 假设每单平均价值 200 元 avg_order_value 200 roi_df[conversion_value] roi_df[conversions] * avg_order_value roi_df[total_cost] roi_df[daily_cost] * 30 # 月总成本 roi_df[ROI] (roi_df[conversion_value] - roi_df[total_cost]) / roi_df[total_cost] roi_df[ROI_pct] roi_df[ROI].apply(lambda x: f{x:.1%}) print( 末次点击归因 - 各渠道月度 ROI ) print(roi_df[[channel, conversions, total_cost, conversion_value, ROI_pct]]) # 多模型归因对比 # 对比不同归因模型下各渠道的贡献差异 pivot attribution_df.groupby([channel, model_name])[conversions].sum().unstack() print(\n 各模型归因结果对比月度转化数 ) print(pivot) # 计算归一化占比方便横向对比 pivot_pct pivot.div(pivot.sum(axis0), axis1) print(\n 各模型归因占比对比 ) print(pivot_pct.applymap(lambda x: f{x:.1%}))跑完这组数据后通常会看到一些有趣的洞察。比如某个渠道在末次点击模型下表现平平但在首次点击模型下贡献很大——这说明它是个强获客渠道虽然直接转化一般但在拉新上功不可没。单纯看末次点击很容易误杀。另外一个常见的发现是投了几个月一直亏的渠道换一个归因模型看其实是盈利的。这往往是归因方式本身在吃预算——末次点击把大部分功劳给了品牌词搜索和直接访问导致信息流渠道看起来很不划算。五、总结多渠道归因这事技术实现本身不算难SQL 几个窗口函数就搞定了。真正的难点在于两点一是用哪个模型这事没有标准答案得结合你的业务特征来选——电商偏末次、内容平台偏首次、B2B 则要考虑长周期多触点。二是有了数据敢不敢改预算很多时候归因结果和老板的直觉不符这时候就需要数据分析师用数据说话用 A/B 测试来验证归因结论。我的建议是先用两到三种模型并行跑观察一段时间选一个最符合业务逻辑的作为主模型。归因不是一锤子买卖而是一个持续迭代的过程。下篇我们聊聊 AI 客服质检的数据分析敬请期待