在电子表格里搭一个营销组合模型：4 个渠道、广告残留衰减、ChatGPT 写公式
目录
周二早上 9:47。一个我合作的 DTC（Direct-to-Consumer，直接面向消费者）品牌正在以每月 1.4 万美元的速度烧钱，自己看不见。CMO 觉得付费搜索是他们的最佳渠道，平台仪表盘也这么显示。我前一晚已经从每个平台拉出了 26 周的周度花费和营收数据，贴进一张 Google Sheet，然后让 ChatGPT 写广告残留公式。到 12:55，我们跑出来的模型说：付费社交在真正干活，展示广告效果几乎立刻衰减，他们在品牌搜索上多花了 18%。CMO 盯着屏幕："为什么 Google Ads 不给我看这个？" 因为 Google Ads 看不到邮件、付费社交和展示——它只能看到 Google Ads。
这就是 MMM（Marketing Mix Model，营销组合模型）的全部意义：一份渠道中立的、真正在驱动营收的视图，从你自己的数据里建出来，工具你已经有。供应商每年收 4 万美元卖给你同样的答案。数学 30 年没变。ChatGPT 能替你写 80% 的公式。你不需要统计学背景，只需要一个 Google 账号和三个小时。
这篇文章会完整走一遍搭建过程、ChatGPT 给我写的公式，以及暴露每月 1.4 万美元浪费的敏感性测试。不需要供应商、不需要代理商、不需要博士学位。
MMM 真正在做什么（以及它不做什么）
MMM 是一个回归模型，估计每个营销渠道对营收的贡献，同时考虑一个广告效果随时间衰减的过程。把一个真正的 MMM 和一个朴素的"最后一次点击是哪个"模型区分开的是两个概念：
- Adstock（广告残留）。 你周一看到的一条付费社交广告，可能周日还在轻推你下单。效果是逐渐衰减的。标准公式是几何衰减：
Adstock(t) = Media(t) + λ × Adstock(t-1)。Lambda 是保留率（介于 0 和 1 之间）——0.3意味着每周的效果保留上周的 30%。半衰期是log(0.5) / log(λ)周。 - 边际递减。 把付费搜索花费翻倍，营收不会翻倍。不用微积分建模这一点的最简方法：对花费取对数。
revenue ~ log(spend)是一个教科书级别的"接近饱和"代理式，一列就放得下。
这就是全部的理论含量。剩下的全是水电工活。
动手前你需要准备什么
- 至少 26 周的周度数据。不到 12 周回归会不稳定。从每个平台的 API 或导出拉：每个渠道的周度花费和周度营收（或者转化数，或者 AOV × 订单数——选一个 KPI 咬住不换）。
- 一个 KPI。我用"周度新客营收"，不用混合营收。新客营收才是渠道真正创造的。混合营收被复购污染了，那些复购不是渠道带来的。
- 四个渠道。付费搜索、付费社交、展示、邮件。有播客就五个，有联盟营销就六个。超过六个数学就会快速劣化。
- 一张 Google Sheet，一个 ChatGPT 标签页，三个小时。
我们给那个 DTC 客户用的是：每个平台导出的 26 周周度花费，Shopify 里的周度新客营收。搭模型总耗时 3 小时 8 分钟，包括一次走错路和一杯咖啡。
搭建过程，一步一步来
第一步——摆好数据
五列：week、paid_search_spend、paid_social_spend、display_spend、email_spend、revenue。一周一行，二十六行。暂时不写公式。
第二步——让 ChatGPT 写广告残留公式
打开 ChatGPT（或者 Claude、Gemini——这种公式哪个模型都差不多）。粘贴：
"I have weekly marketing spend in column B, starting at B2. Write a Google Sheets formula for column G that calculates adstock with a retention rate (lambda) of 0.4, where the formula references the cell in G above it, starting with the value in B2 for the first row."
ChatGPT 会返回类似这样的：
=IF(ROW()=2, B2, B2 + 0.4 * G1)这就是递归的广告残留。向下拖 26 行。这就是付费搜索的广告残留。给 H、I、J 三列分别写另外三个渠道的公式，lambda 不一样。
第三步——选你的 lambda
这是唯一需要拍脑袋的地方。一组对大多数电商品牌都够用的起点值：
| 渠道 | Lambda | 半衰期 |
|---|---|---|
| 付费搜索 | 0.2 | 约 0.4 周（3-4 天效果就衰减） |
| 付费社交 | 0.3 | 约 0.6 周 |
| 展示 | 0.1 | 约 0.1 周（基本上一周后就归零） |
| 邮件 | 0.4 | 约 0.8 周（好标题 5-6 天仍在拉新） |
"正确"的 lambda 是行业里能吵一周的辩题。第一版用这些已经够接近了。你会在第六步做敏感性测试。
第四步——对残留后的花费取对数
加四列：=LN(G2)、=LN(H2)、=LN(I2)、=LN(J2)。这就是边际递减的小技巧。在 log(spend) 上做线性回归，等价于一条"花得越多曲线越平"的曲线——和真实的反应曲线形状一致。
第五步——跑回归
Google Sheet 自带 LINEST 函数。在另一个标签页里：
=LINEST(revenue_range, log_adstock_range, TRUE, TRUE)后面两个 TRUE, TRUE 标志位会给你截距、每个渠道的系数、以及标准误。这就是你的 MMM。
我们客户的输出：
| 渠道 | 系数 | 解读 |
|---|---|---|
| 付费搜索 | 1.42 美元 | 每 1 美元残留后的搜索带来约 1.42 美元营收 |
| 付费社交 | 2.87 美元 | 每 1 美元残留后的社交带来约 2.87 美元营收 |
| 展示 | 0.31 美元 | 每 1 美元残留后的展示只带来 0.31 美元 |
| 邮件 | 4.15 美元 | 每 1 美元残留后的邮件带来 4.15 美元 |
系数高于 1.0 意味着正 ROI（投资回报率）。低于 1.0 意味着你在最后一美元上亏钱。这家客户的展示，是在烧钱。 这就是那个 1.4 万美元/月的浪费——按平台口径展示的 ROAS（Return on Ad Spend，广告支出回报率）是 1.2x，但 MMM 里只有 0.31x。差别在于：平台把"展示曝光 24 小时内发生的所有转化"都记在展示头上，不管真正促成转化的是什么。MMM 把这部分剥掉了。
第六步——±20% 敏感性测试
这是大多数团队跳过、却最重要的部分。lambda 是猜的。如果把 lambda 从 0.3 调到 0.36，付费社交的系数从 2.87 跳到 0.40，那 2.87 这个数字毫无意义。
我让 ChatGPT 搭一张小敏感性表：
"Build a Google Sheet table that shows the paid social coefficient under 5 lambda values: 0.1, 0.2, 0.3, 0.4, 0.5. Use LINEST on the recalculated adstock for each."
客户的付费社交系数在五个 lambda 下都稳定在 2.20 美元以上。付费搜索在 1.20–1.60 美元。邮件稳定高于 3.50 美元。展示在每一种情况下都低于 0.60 美元。结论对参数猜值是稳健的。 这才是你要的信号。一个在 ±20% 区间里从"神"变"渣"的渠道，没告诉你任何真实的东西——它告诉你的是模型过拟合了。一个符号和数量级在 ±20% 里都不动的渠道，在告诉你一个你可以照做的结论。
电子表格 MMM 不会告诉你的四件事
我想老实讲清楚边界，因为"在电子表格里搭模型"如果只讲到这会让人轻视。
- 它分不清创意和渠道。 付费社交上出现 2 倍提升，可能是某一条爆款创意撑起来的。模型把功劳算在了渠道头上。
- 它是相关，不是因果。 系数 2.87 美元不能证明付费社交"导致"了 2.87 美元的营收，只能证明在做了广告残留变换之后，付费社交和 2.87 美元营收"相关"。因果性的金标准是关停测试（Holdout Test）：把这个渠道暂停两周，量差值。电子表格模型告诉你哪些关停测试值得跑。
- 它假设 lambda 稳定。 现实里广告残留的衰减会随季节、创意疲劳、竞品活动漂移。每月刷新一次才能让模型保持诚实。
- 它塌缩到周粒度。 日数据更强力，但处理日数据要 BigQuery 或 Python。第一版用周数据完全够。
我今天从零开始会怎么搭
如果我从零开始，操作顺序是：
- 拉 26 周的周度花费，从每个平台的导出取。存到一张表里，一列 KPI。
- 让 ChatGPT 写广告残留和 LINEST 公式。 贴进去，按需调一调。
- 跑回归。 把系数用人话读出来。
- 对每个 lambda 做 ±20% 敏感性测试。 翻符号的渠道直接弃掉。
- 对最弱的渠道跑 2 周关停（客户的案例是展示）。如果营收没掉，停掉这部分花费；如果营收掉了，证明模型对了——你刚拿到加码强渠道的正当理由。
- 每月刷新。 新的一周进去，新的系数出来。
给客户完整搭一遍是 3 小时。模型导出的决策：砍掉展示（每月省 1.4 万美元），把预算重新分配到付费社交和邮件（合计系数 7.02 美元，付费搜索 1.42 美元）。预计 90 天提升：花费持平，营收涨 22%。CMO 的反应是："为什么 12 个月前我手上没有这个？" 因为 Google Ads 永远不会让你少花 Google Ads 的钱。一张电子表格会。
反直觉的结尾：Google Sheet 里的 MMM 不是供应商 MMM 的"低配版"。对年营收 5000 万美元以下的品牌，它是更优的那个版本。跑得快、能审计、会告诉你去砍掉那个为供应商续费的渠道。搭出来。做敏感性测试。跑一次关停。然后照它说的做。