Marketing

3 个能在电子表格里搭起来的归因模型（附公式，告别供应商锁定）

3 个能在电子表格里搭起来的归因模型（附公式，告别供应商锁定）
目录

2023 年一家 B2B SaaS 客户来找我，坚信 Google Ads 是他们最好的渠道。他们的末次点击报告就是这么说的——60% 的转化来自品牌搜索（Branded Search），而他们"巧妙地"把任何包含公司名的搜索都归到了这一类。董事会信了。CFO（首席财务官）基于这个数字批了 40 万美元的季度预算。我花了一下午拉出原始点击流（Clickstream），在一个 Google Sheet 里用三个归因模型重新跑了一遍。结论几乎相反：付费社交承接了 73% 转化中的开场环节，而品牌搜索只是在收尾时拿到了功劳。同一份数据，三个不同的模型，三种不同的战略决策。

这就是 2025 年营销归因（Marketing Attribution）的全部问题。没有"正确"的模型，但肯定有错误的模型——而大多数错误模型，正以每年 5 万美元的席位费卖给你。你不需要它们。真正能驱动决策的三个模型——末次点击、首次点击、时间衰减——一个下午就能在电子表格里搭出来。我从 2014 年起就在为客户搭建它们。数学没变。卖这些数学的供应商倒没怎么进步。

这篇文章会讲清楚三个模型、确切公式、列结构，以及一个每个团队都会踩一次的坑（我自己也踩过两次）。如果你读完之后用自己的数据把模型搭出来，你将拥有大多数内部营销团队都没有的东西：一个能在预算会议上扛住 CFO 拷问、算式经得起财务团队审视的归因模型。

为什么"完美归因"是幻想（以及真正该怎么做）

一个完美的归因模型应该知道：你的客户周二听到一档播客广告、周四 Google 你的品牌、周末打开三封营销邮件，最后在周一被同事在 Slack 里随口提了一句后完成转化。它会给每个触点按真实因果贡献加权。它不存在，未来大概也不会存在——因为一半的输入在人脑里，你没法给一条 Slack 装个埋点。

你能做的——也是我认识的每一位实战派营销人实际在做的——是选一两个归因模型，老实承认它们的偏差，然后比较答案。三个模型指向同一个方向，就是个决策；它们互相打架，那是一个值得深挖的问题，而不是可以争论的数字。

我反复回到的三个模型：

  1. 末次点击归因（Last-click Attribution）——100% 转化功劳归最后一个触点。简单，偏向漏斗底部（Bottom-funnel）渠道，但它是你老板的老板在用的模型，所以你必须会讲这种语言。
  2. 首次点击归因（First-click Attribution）——100% 功劳归第一个触点。偏向认知（Awareness）渠道。适合回答"客户从哪儿来"，对出价优化毫无用处。
  3. 时间衰减归因（Time-decay Attribution）——功劳按"距离转化的时间"加权分布在所有触点上。比较贴近长决策周期下客户的真实行为，是我在销售周期 30 天以上的 B2B（Business to Business，企业对企业）场景里的默认选择。

还有第四种——数据驱动 / Shapley 值法——Triple Whale、Northbeam、Rockerbox 都会卖给你。我用过，数学上确实更优，但需要一条干净的点击流数据库再加半个数据工程师。对每年广告投放低于 500 万美元的业务，电子表格模型能给你 80% 的洞察，代价是 0% 的工程成本。边际收益曲线非常陡。

动手前你需要的数据

只要有一份导出文件，你就能在一个 Google Sheet 里搭完三个模型：一份点击级（或会话级）日志，至少包含这四列。

列名 举例 模型靠它算什么
user_id（或 email_hash） a3f8c92 哪些行为属于同一个人
event_type click 或 conversion 发生了什么
channel paid_search, organic, email, direct 哪个营销渠道触发
timestamp 2024-11-03 14:22:01 按时间排序的顺序

可选第五列：campaign（如 google_brand_us_q4）。我几乎都会加。

数据来源。 如果你跑的是 Shopify + Klaviyo + Meta Ads，可以用 Shopify 客户导出 + Klaviyo 行为导出 + Meta Ads 洞察导出拼起来。更干净的路径：开启 GA4（Google Analytics 4）事件追踪，拉 BigQuery 导出。再干净一点的：装 Rockerbox 或 Triple Whale 像素跑 30 天，导出原始事件日志，然后自己建模型。我不建议你长期用供应商做这件事——它们的免费导出额度，已经够我下面要做的事。

一条数据卫生（Data Hygiene）警告：按 user_id 和 timestamp 去重。如果一个用户同一分钟内打开邮件又点了邮件里的链接，GA4 会记录两条事件。做归因时，把它们合并为一个触点。我 2018 年被这个坑过一次——一封群发邮件让一个客户看起来在转化前跟九个渠道互动过。真实情况是只跟一个。

模型一：末次点击归因（你已经有了，但写出来才靠谱）

数学很简单，但写出来这件事本身会逼你把列逻辑定死——而这正是大多数团队悄悄产生分歧的地方。

第一步。 筛 event_type = "conversion" 的事件。对每一次转化，按 user_id 找到该用户在转化时间戳之前的最后一个 click 事件。那个渠道拿走 100% 功劳。

在 Google Sheet 里：

=SORT(FILTER(events!A:D, events!B:B="conversion"), 4, TRUE)

这会给你按时间倒序排列的所有转化。然后对每一条转化，你需要该用户紧挨着转化发生的那次点击。一个我常用的小公式——假设事件表是 events!A:D，转化行是第 N 行：

=INDEX(
   FILTER(
      events!C:C,
      events!A:A = user_id,
      events!B:B = "click",
      events!D:D < conversion_timestamp
   ),
   COUNTA( FILTER(
      events!C:C,
      events!A:A = user_id,
      events!B:B = "click",
      events!D:D < conversion_timestamp
   ) )
)

读法是："给我这个用户在这次转化之前最后一次点击的渠道。" 内层的 COUNTA 返回行数，外层 INDEX 用这个行数去取最后一条匹配。

只要你做出一个"每行转化对应的末次点击渠道"列，剩下的就是 SUMIF 透视：

=SUMIF(last_click_results!C:C, "paid_search", last_click_results!E:E)

诚实的偏差。 末次点击会严重高估品牌搜索、直接访问（Direct Traffic）和邮件——任何倾向于成为转化前最后一次接触的渠道。它会系统性低估播客、YouTube、展示广告，以及任何漏斗顶部（Upper-funnel）的付费社交。文章开头的那个爱品牌搜索的 B2B 客户就是教科书案例。末次点击只能做基线，永远不能当结论。

模型二：首次点击归因（保住认知预算的那个）

同一份数据，换一面镜子。找每个已转化 user_id 的第一次 click 事件——不是最后一次。给那个渠道 100% 功劳。

公式结构完全一样，去掉时间戳过滤即可：

=INDEX(
   FILTER(
      events!C:C,
      events!A:A = user_id,
      events!B:B = "click"
   ),
   1
)

末尾的 1 表示"过滤后范围的第一条"。不需要 COUNTA 那套体操——首次点击永远只有唯一答案。

为什么它重要。 首次点击告诉你客户从哪里起源——是哪个渠道在所有其他影响发生之前把人带进你的生态。对于跑认知播客的品牌，这一列就是给播客续命的关键证据。对 B2B SaaS 来说，通常是有机搜索或某条具体的 LinkedIn 广告。首次点击归因回答的是 CMO（首席营销官）在替品牌预算辩护时问的那个问题："这些客户，最初到底是从哪儿来的？"

诚实的偏差。 首次点击会忽略首次触点和转化之间发生的所有事。如果一个客户通过播客发现了你，然后在邮件和再营销（Retargeting）的培育下走了三个月，首次点击仍然把 100% 功劳给播客。所以它是个有用的"守预算"工具，是个无用的"分预算"工具。用它来为现有投放辩护，不要用它来做新的投放决策。

模型三：时间衰减归因（我真正在用的那个）

这是我的默认模型。它把功劳分布在所有触点上，离转化越近权重越高。标准实现是半衰期公式：转化前 N 天的触点，权重是转化前 0 天触点的一半。

半衰期加权公式：

weight = 2 ^ ( -(days_before_conversion) / half_life_days )

其中：

  • days_before_conversion = (转化时间戳 - 触点时间戳)，单位天
  • half_life_days = 衰减的力度。7 天是大多数归因供应商的默认值。我做销售周期 30 天以上的 B2B SaaS 用 14 天，直接响应式电商用 3-5 天。

第一步——在事件表里算权重。 加两列：

days_before_conversion weight
=conv_ts - event_ts（天） =2^(-days_before_conversion / half_life)

你需要拿到该用户对应的那次转化的时间戳。最干净的做法是建一张辅助表 user_conversions，存 user_id 和 conversion_timestamp，然后用 VLOOKUP 或 XLOOKUP 把它拉回来。

第二步——按用户归一化权重。 一个用户有多个触点，每个有自己的权重。要把它们变成"百分比功劳"，把每个触点的权重除以该用户所有触点权重之和：

credit_fraction = touchpoint_weight / SUM(该用户所有触点权重)

第三步——分配转化价值。 如果一次转化值 100 美元（电商用 AOV 平均客单价；B2B 用你的 Pipeline 价值），每个触点拿 credit_fraction × 100 美元。按渠道对所有用户的所有触点求和：

=SUMIFS(
   events!G:G,    -- credit 列
   events!C:C,    -- channel 列
   "paid_search"
)

一个走完的例子。 用户旅程：第 -30 天听到播客广告，第 -20 天 Google 品牌词搜索，第 -5 天被再营销展示广告打到，第 -1 天直接访问，第 0 天转化。half_life = 7：

  • 播客：2^(-30/7) ≈ 0.052
  • 品牌搜索：2^(-20/7) ≈ 0.139
  • 再营销：2^(-5/7) ≈ 0.610
  • 直接访问：2^(-1/7) ≈ 0.906

归一化之后（每个权重除以总和，使它们加起来等于 100%）：播客 3%、品牌搜索 8%、再营销 36%、直接访问 53%。

直接访问还是赢，但再营销和品牌搜索拿到了像样的功劳。播客只剩渣——这很诚实，一个 30 天前的触点放在 7 天半衰期里，几乎衰减到零了。如果你想让播客多拿功劳，把半衰期拉长。杠杆在你手上。

为什么时间衰减是我的默认。 它不假装第一次或最后一次触点是唯一的。它按"近期度"加权，大致吻合买家心智状态的演变。它在电子表格里就能算，不需要外部工具。它产出的数字 CFO 会觉得"合理"——以我的经验，这一点没得商量。

唯一的坑：身份拼接

2019 年我给一个 DTC（Direct-to-Consumer，直接面向消费者）护肤品牌搭这些模型。离董事会还有三周。第一次跑完模型，付费社交承担了 80% 的转化，我差点直接写进汇报材料。然后我注意到点击流里有 40% 的匿名会话——user_id = "anonymous_8f72c1" 之类。匿名会话在购买前的浏览阶段是正常的。但它们是首次点击归因的毒药，因为匿名用户的第一次点击，并不是最终那个买家的第一次点击。

解法是身份识别（Identity Resolution）：当一个匿名用户给了你邮箱（订阅、注册账户、下单），用新的已知 user_id 把这之前的旧事件回填一遍。大多数 ESP（邮件服务商）和 CDP（客户数据平台）只要打开对应功能就会替你做这件事。Klaviyo 在用户注册时自动做；Segment 在调用 identify() 时做。如果你自己手搓，这就是一次性 SQL 任务：UPDATE events SET user_id = new_id WHERE session_id = old_session AND event_timestamp < signup_timestamp。这个 SQL 我有模板，二十行，能让你避开我差点交付的那个错误。

先做身份识别，再跑任何模型。 不做这一步，首次点击归因会退化成"匿名用户 X 第一次命中我们像素时在浏览哪个渠道"——纯粹是噪声。

三个不同答案，怎么办

你一定会拿到三个不同的数字。本事不是选一个，是读"分布"。

2024 年一个真实客户案例，B2B SaaS，做 HR 科技，年投放 120 万美元，三个月的点击流数据：

渠道 末次点击收入 首次点击收入 时间衰减收入
品牌搜索 28.4 万美元（47%） 3.1 万美元（5%） 16.8 万美元（28%）
付费社交（LinkedIn） 6.2 万美元（10%） 21.4 万美元（36%） 13.4 万美元（22%）
有机搜索 8.9 万美元（15%） 10.8 万美元（18%） 11.2 万美元（19%）
邮件 9.4 万美元（16%） 1.2 万美元（2%） 7.8 万美元（13%）
直接访问 5.8 万美元（10%） 6.1 万美元（10%） 5.6 万美元（9%）
播客 / 公关 1.3 万美元（2%） 17.4 万美元（29%） 5.2 万美元（10%）
合计 60 万美元 60 万美元 60 万美元

合计一致，因为每次转化只被记一次。有意思的是分布。

读这张表：

  • 品牌搜索在末次点击里被高估，在首次点击里被低估。时间衰减落在中间——我更愿意信中间那个。结论：它值得有真实预算，但不是平台默认仪表盘显示的 47%。
  • **付费社交（LinkedIn）**反过来——末次点击低估，首次点击高估。时间衰减确认它是强力的漏斗中部（Mid-funnel）驱动。正确动作：不要砍掉（末次点击报告会建议你砍），要加码。
  • 播客 / 公关是末次点击里消失的那一行，看着像虚荣支出。首次点击说它带来了 29% 的客户基础。时间衰减把它调回 10%。诚实的解读：播客是强认知驱动，在临门一脚上偏弱。如果品牌对业务重要，就值得留着。

三个模型，三面镜子，一个决策：把预算从品牌搜索向付费社交倾斜，但不要砍掉播客。这就是一份电子表格免费给你送上的结论。供应商版的同一份分析，是一份 4 万美元/年、60 天实施的合同。

电子表格方法的边界

我想老老实实讲清楚这三个模型解决不了什么。

  • 数学上它们仍是"末次触点家族"。 时间衰减软化了末次触点偏差，但没消除它。一个真正因果的归因模型会按渠道的增量贡献加权——也就是"如果你没跑这个渠道，会发生什么"。要回答这个问题，你需要关停测试（Holdout Test）或地理提升实验（Geo-lift Experiment），不是电子表格。
  • 它们看不到线下。 一个客户在会议上听说了你然后 Google 你的品牌，在点击流里看起来就是个"品牌搜索"客户。模型根本不知道会议存在。如果你的业务有大量线下影响（B2B 销售、零售、线下活动），你得在模型之上加一个"提升研究"（Lift Study）渠道。
  • 它们会塌缩到渠道级。 三个模型都告诉你"付费社交值 X% 的营收"。它们不告诉你付费社交里哪条广告系列在干活、哪条创意在转化、哪个人群是 LTV（Life Time Value，用户终身价值）最高的。要回答这些，你还需要平台级拆解和增量测试。

我见过最常见的、诚实的错误是：团队把模型搭出来，拿到一个数字，然后停。这个数字是起点，不是答案。正确的下一步永远是问"这个数字要是错的，需要什么为真？"如果模型说付费社交是最好的漏斗中部渠道，那可证伪的命题是"我把付费社交暂停两周，转化至少跌 15%。"去跑这个暂停。转化没跌，说明模型错了；转化跌了，说明模型对了——你就拿到了加码的正当理由。

我今天从零开始会怎么搭

如果我从零开始，操作顺序是这样：

  1. 拉一份点击流导出，GA4 → BigQuery，或者用 30 天 Rockerbox / Triple Whale 原始事件日志拼。存进一张表，至少四列。
  2. 在三个独立 tab 里跑三个模型。 末次点击、首次点击、时间衰减。每个 tab 都是 SUMIF 按渠道 × 功劳求和。
  3. 搭一张对比透视。 行是渠道，列是模型。看分布。那就是分析。
  4. 跑一次可证伪实验——暂停一个渠道两周，量差值。把真实差值跟模型预测比。如果模型说付费社交承担 22% 转化，一次干净的暂停里跌 15-25% 就是绿灯。落在区间外说明模型加权出了问题。
  5. 每季度更新模型。 半衰期漂移、渠道组合变化、新品发布——都会改变权重。六个月前的模型是民间传说。

这些都不需要供应商。不需要数据工程师。最贵的成本是你一个下午的时间，第二贵的成本是真正去跑那个暂停测试的纪律——而大多数团队恰恰停在这里。

反直觉的结尾：归因模型的价值不在模型本身，在于它逼你去设计的那场暂停测试。一份没人照做的模型，是 Notion 文档里的一张截图。一次暂停测试，是能上董事会的实验。搭好电子表格，再搭好实验，电子表格会在第一个季度就自己把成本赚回来。