ARTICLE · 979732

第359讲:Excel/VBA和Python实现:LTV与CAC回本周期——市场投放的“生死线”到底怎么算?

发布时间：2026-09-14 23:15:51 最近访问：2026-09-14 23:24:58

一、先说一个让人后背发凉的真实场景

上周五晚上十点，市场总监在群里甩了一张截图：某信息流渠道本月花了 87 万，带来 1.2 万个新注册用户。

他问了一句：“这个渠道到底赚不赚钱？”

财务回了一句：“客单价 199，复购率大概 20%，按 12 个月算，LTV 差不多 240 块。你猜呢？”

群里安静了三分钟。

没有人敢答。因为没人能说出：这笔钱，第几个月回本？半年？十个月？还是永远回不来？

这就是本讲要解决的问题。

LTV（用户生命周期价值）和 CAC（获客成本）本身不难，难的是把两者放在同一条时间轴上，逐月归因，算出回本周期。而这件事，Excel/VBA 能做，但一到几十万行投放日志就卡成 PPT；Python 能跑，但很多人写出来的代码只有他自己看得懂。

下面我把两套实现并排拆开，你对照着看，基本就能直接抄走用在自己公司。

二、先把口径钉死：不然算出来的数字全是吵架的素材

在动手写代码之前，必须统一四个定义。口径不统一，后面所有计算都是给会议室制造火药味。

1. CAC（获客成本）

单渠道单月获客成本 = 该渠道该月消耗金额 ÷ 该渠道该月新增用户数

注意是“新增”，不是“激活”，更不是“付费转化”。新增是市场部对赌的锚点，别中途偷换概念。

2. LTV（用户生命周期价值）

单个用户在全部存续月份里，贡献的毛利（或营收，视公司口径）累计之和。

本讲为了通用，先用营收口径演示，文末会讲为什么成熟团队一定要换成毛利口径。

3. 累计 LTV 曲线

用户注册后的第 1 个月、第 2 个月……第 N 个月，人均贡献营收。典型形态是：首月陡增，之后按月衰减式爬坡，第 6 个月后趋于平缓。

4. 回本周期（Payback Period）

从获客当月起，该批用户累计贡献营收首次 ≥ CAC 的月份数。

比如：1月获客，CAC=180元。这批人1月人均贡献30，2月人均45，3月人均60，4月人均70——累计到4月是205，那回本周期就是 4 个月（不是“4月底”，是“第4个存续月”）。

行业经验阈值：SaaS 类回本 > 18 个月要极度警惕；电商/本地生活 > 6 个月就开始压预算；< 3 个月属于优质渠道，可以加预算上杠杆。

三、数据长什么样？先造一份“脏一点”的模拟数据

真实业务里，市场数据永远不是一张干净宽表。它通常是两张表拼出来的：

ad_spend.csv：日期、渠道、花费
user_orders.csv：用户ID、注册日期、订单日期、订单金额

我们先用 Python 造一份 5 万行的模拟数据（含少量缺失和跨月订单），这样你跑出来的结果才接近生产环境。

import numpy as npimport pandas as pdrng = np.random.default_rng(42)channels = ["信息流-A", "搜索竞价", "KOL带货", "线下地推", "应用商店"]n = 60_000df_spend = pd.DataFrame({    "dt": pd.date_range("2025-01-01", "2025-12-31")[rng.integers(0, 365, n)],    "channel": rng.choice(channels, n),    "spend": rng.gamma(2.5, 1800, n).round(2),   # 花费右偏，符合投放实况})# 用户表：注册日期 + 后续若干笔订单user_ids = [f"U{100000+i}" for i in range(18_000)]df_user = pd.DataFrame({"user_id": user_ids,                         "reg_month": pd.to_datetime(                             rng.choice(pd.date_range("2025-01-01", "2025-11-01", freq="MS"), 18_000))})# 每个用户：注册后最多产生 6 笔订单，间隔 0~90 天，金额递减（首单最大）rows = []for _, u in df_user.iterrows():    base = u["reg_month"]    orders = rng.integers(1, 7)    for k in range(orders):        offset = rng.integers(0, 90 * (k + 1))        order_dt = base + pd.Timedelta(days=int(offset))        if order_dt > pd.Timestamp("2026-01-01"):            continue        amt = max(19, round(rng.lognormal(4.3, 0.75) * (0.75 ** k), 2))  # 越往后越小        rows.append((u["user_id"], order_dt, amt))df_orders = pd.DataFrame(rows, columns=["user_id", "order_dt", "amount"])print(df_orders.shape, df_spend.shape)

跑完你会得到：约 6 万行花费记录、7 万+行订单明细。这种量级，Excel 双击刷新能让你去接三杯咖啡。

四、Python 实现：pandas 分组累计 + 月份矩阵归因

核心思路只有三步，但每一步都有坑：

Step 1：算月度 CAC（渠道 × 获客月份）

# 花费收敛到 年月spend_m = (df_spend.assign(ym=lambda d: d["dt"].dt.to_period("M").astype(str))                   .groupby(["ym", "channel"], as_index=False)["spend"].sum())# 注册用户收敛到 年月 + 渠道（假设订单表里带渠道，或另有一张注册归因表）reg = (df_orders.merge(    df_user.assign(ym=lambda d: d["reg_month"].dt.to_period("M").astype(str)),    on="user_id")    .groupby(["ym", "channel"], as_index=False)["user_id"].nunique()    .rename(columns={"user_id": "new_users"}))cac = spend_m.merge(reg, on=["ym", "channel"], how="left")cac["new_users"] = cac["new_users"].fillna(0)cac["cac"] = np.where(cac["new_users"] > 0, cac["spend"] / cac["new_users"], np.nan)

⚠️ 坑1：merge后一定要 how="left"+ fillna(0)。否则某渠道某月没投放，会被内连接直接吃掉，后面累计曲线出现“断崖假象”。

⚠️ 坑2：nunique()而不是 count()。一个用户当天重复上报两次注册，count 会算成两个。

Step 2：把订单摊到“获客后的第 N 个月”

这是全场最关键的十行代码。我们要把 order_dt翻译成 age_month（注册后第几个月）。

df = df_orders.merge(    df_user.rename(columns={"reg_month": "reg_dt"}), on="user_id", how="left")# 注册月份起点 & 订单月份起点，转成“月份序号”相减df["reg_m"] = df["reg_dt"].dt.to_period("M").astype(int) * 100 + df["reg_dt"].dt.month  # 示意，更稳的做法见下

上面这行是反例——月份编码容易跨年翻车。生产代码请用下面这个：

df["reg_ym"] = df["reg_dt"].dt.to_period("M")df["order_ym"] = df["order_dt"].dt.to_period("M")# 用 period 差值直接得“存续月数”，跨年自动处理df["age_month"] = (df["order_ym"].dt.to_timestamp('S') - df["reg_ym"].dt.to_timestamp('S')).dt.days // 30 + 1df.loc[df["age_month"] < 1, "age_month"] = 1   # 注册当月订单算第1个月df.loc[df["age_month"] > 12, "age_month"] = 12 # 超出12个月归并到12+，或单独开“13月+”桶

Step 3：构建渠道 × 获客月 × 存续月的累计营收矩阵

# 每个用户-存续月 只留一笔（取合计），避免同一用户同月多单被重复归因rev = (df.groupby(["channel", "reg_ym", "age_month"], as_index=False)         .agg(users=("user_id", "nunique"), revenue=("amount", "sum")))# 人均月贡献rev["arpu_by_age"] = rev["revenue"] / rev["users"]# 透视：获客月份 × 存续月 → 人均贡献pivot = rev.pivot_table(index="reg_ym", columns="age_month",                         values="arpu_by_age", aggfunc="mean").sort_index()# 累计 LTV 曲线：沿 axis=1 做 cumsumcum_ltv = pivot.cumsum(axis=1)cum_ltv.columns = [f"ltv_m{int(c)}" for c in cum_ltv.columns]print(cum_ltv.loc[["2025-01","2025-03","2025-06"]].round(1).head())

到这里，你拿到的是一张“无论哪个月获客，第 N 个月累计值都是同一个数”的标准化曲线——这就是可复用的 LTV 曲线。市场部明年再投，直接套这条曲线做预估，不用重跑历史。

Step 4：匹配 CAC，算回本周期

def payback_month(row, cac_val, max_m=12):    """row 是某获客月各存续月累计LTV的一行；返回首次超过CAC的月份数"""    for m in range(1, max_m + 1):        col = f"ltv_m{m}"        if col in row and pd.notna(row[col]) and row[col] >= cac_val:            return m    return np.nan  # 12个月内没回本result = []for ym, row in cum_ltv.iterrows():    ch_cac = cac[cac["ym"] == ym].set_index("channel")["cac"]    for ch, cac_v in ch_cac.items():        if pd.isna(cac_v):            continue        result.append({            "reg_ym": ym, "channel": ch, "cac": round(cac_v, 1),            "payback_m": payback_month(row, cac_v),            "ltv_12m": round(row.get("ltv_m12", np.nan), 1) if "ltv_m12" in row else np.nan        })report = pd.DataFrame(result)report["status"] = np.where(report["payback_m"].le(3), "✅ 优质",                     np.where(report["payback_m"].le(6), "⚠️ 观察",                     np.where(report["payback_m"].notna(), "❌ 超6个月", "☠️ 12个月未回本")))print(report.groupby("channel")["payback_m"].mean().round(2))print(report.sort_values("payback_m", ascending=False).head(8).to_string(index=False))

跑完，你得到的不是一句“LTV 240，CAC 180，ROI 1.33”，而是：

搜索竞价：CAC 152 元，12个月累计LTV 268 元，第 5 个月回本 ✅
KOL带货：CAC 310 元，12个月累计LTV 291 元，12个月内未回本 ☠️
线下地推：CAC 96 元，但月均新增仅 210 人，第 3 个月回本 ✅（量太小，需算绝对利润）

第二行才是市场总监真正想听的话。

五、VBA 实现：Dictionary 存渠道累计，逐行跑回本

很多公司的投放日报，依然是一张“财务锁版”的 Excel 表，每天运营手动刷新，老板九点要数。这时候上 Python 调度反而重，一个 80 行的 VBA 宏，绑定按钮，三秒出结果。

假设 Sheet 结构如下（A列日期 / B列渠道 / C列花费 / D列新增用户 / E列订单日期 / F列订单金额），输出写到右侧空白区。

核心代码

Option Explicit' 用两个 Dictionary：一个存月度CAC，一个存 渠道-获客月 的累计LTV桶Public Sub CalcLTVPayback()    Application.ScreenUpdating = False    Application.Calculation = xlCalculationManual    Dim ws As Worksheet: Set ws = ActiveSheet    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row    ' ---- 1. 先扫花费表，建 渠道|月份 -> 花费 / 新增 字典 ----    Dim dictSpend As Object, dictReg As Object    Set dictSpend = CreateObject("Scripting.Dictionary")    Set dictReg = CreateObject("Scripting.Dictionary")    Dim r As Long    Dim key As String, ch As String, ym As String    Dim spend As Double, newU As Long    For r = 2 To lastRow        If Not IsEmpty(ws.Cells(r, "A")) And Not IsEmpty(ws.Cells(r, "B")) Then            ch = Trim(ws.Cells(r, "B").Value)            ym = Format(ws.Cells(r, "A").Value, "yyyy-mm")   ' 日期必须真是date型            key = ch & "|" & ym            spend = Val(ws.Cells(r, "C").Value)            newU = Val(ws.Cells(r, "D").Value)            dictSpend(key) = dictSpend(key) + spend          ' Dictionary 不存在自动新增            dictReg(key) = dictReg(key) + newU        End If    Next r    ' ---- 2. 扫订单明细，按 渠道(取注册月渠道) + 存续月 累加营收 ----    ' 这里演示：假设E列=注册月(已是yyyy-mm文本), F列=金额；B列仍是渠道    Dim dictLTV As Object    Set dictLTV = CreateObject("Scripting.Dictionary")    Dim ltvKey As String, ageM As Long, regYM As String    Dim oDate As Date, rDate As Date    For r = 2 To lastRow        If IsDate(ws.Cells(r, "E").Value) And Val(ws.Cells(r, "F").Value) > 0 Then            regYM = Format(ws.Cells(r, "E").Value, "yyyy-mm")            oDate = ws.Cells(r, "E").Value            ' 用订单日 - 注册月1号 粗算存续月            rDate = DateSerial(Year(oDate), Month(oDate), 1)            ageM = DateDiff("m", DateSerial(CLng(Left(regYM, 4)), CLng(Mid(regYM, 6, 2)), 1), _                             DateSerial(Year(oDate), Month(oDate), 1)) + 1            If ageM < 1 Then ageM = 1: If ageM > 12 Then ageM = 12            ch = Trim(ws.Cells(r, "B").Value)            ltvKey = ch & "|" & regYM & "|M" & ageM            dictLTV(ltvKey) = dictLTV(ltvKey) + Val(ws.Cells(r, "F").Value)        End If    Next r    ' ---- 3. 逐渠道逐月算回本 ----    Dim outRow As Long: outRow = 2    Dim outCol As Long: outCol = 10   ' J列开始输出    ws.Cells(1, outCol).Value = "获客月"    ws.Cells(1, outCol + 1).Value = "渠道"    ws.Cells(1, outCol + 2).Value = "CAC"    ws.Cells(1, outCol + 3).Value = "回本月数"    ws.Cells(1, outCol + 4).Value = "12M_LTV"    Dim k As Variant, keysArr As Variant    keysArr = dictSpend.Keys    Dim i As Long, totalRev As Double, cumSum As Double, pb As Variant    For i = LBound(keysArr) To UBound(keysArr)        key = keysArr(i)        spend = dictSpend(key)        newU = dictReg(key)        If newU = 0 Then GoTo NextKey        Dim cacV As Double: cacV = spend / newU        ch = Split(key, "|")(0): ym = Split(key, "|")(1)        ' 沿 ageM =1..12 累加该渠道该获客月的累计营收        cumSum = 0: pb = "未回本"        Dim m As Long        For m = 1 To 12            If dictLTV.Exists(ch & "|" & ym & "|M" & m) Then                cumSum = cumSum + dictLTV(ch & "|" & ym & "|M" & m) / newU   ' 折算到人均            End If            If cumSum >= cacV Then                pb = m                Exit For            End If        Next m        ws.Cells(outRow, outCol).Value = ym        ws.Cells(outRow, outCol + 1).Value = ch        ws.Cells(outRow, outCol + 2).Value = Round(cacV, 1)        ws.Cells(outRow, outCol + 3).Value = pb        ws.Cells(outRow, outCol + 4).Value = Round(cumSum, 1)        outRow = outRow + 1NextKey:    Next i    ' 排序一下，老板爱看“最惨的排前面”    ws.Range(ws.Cells(1, outCol), ws.Cells(outRow - 1, outCol + 4)).Sort _        Key1:=ws.Cells(2, outCol + 3), Order1:=xlDescending, Header:=xlYes    Application.Calculation = xlCalculationAutomatic    Application.ScreenUpdating = True    MsgBox "回本周期计算完成，共 " & outRow - 2 & " 条渠道-月份组合。", vbInformationEnd Sub

VBA 版的三点坦白

它快，但只在“单表 5 万行以内”快。 超过 10 万行，Dictionary 内存暴涨，Excel 会开始假死。此时别硬撑，导出 CSV 交给 Python。
日期必须真·Date 型。 运营从系统里复制粘贴的“2025/1/1 ”带文本空格，Format会返回 1899-12-30之类的鬼值。生产宏里务必加 If Not IsDate(...) Then Skip。
回本判断是“逐月台阶式”的。 用户第 4 个月只贡献了 5 块钱，第 5 个月突然复购 200，VBA 会判成第 5 个月回本——这在业务上是对的（现金流视角），但在财务视角要加“平滑月均”修正。Python 版同理，文末给修正公式。

六、Python vs VBA：不是谁取代谁，是场景分家

维度	Python + pandas	VBA + Dictionary
数据体量	百万行起步，切片、分组、透视秒级	单表 3~8 万行是舒适区
刷新方式	定时任务 / Airflow / 每天凌晨跑，邮件推送	运营按 Alt+F8，或绑定按钮，当场刷新
归因灵活性	多表 join、跨库（Hive/雪花/BigQuery）、渠道末次点击/首次点击可配置	基本只能吃当前 Sheet，跨表要靠 `Workbooks.Open`
可复现性	脚本进 Git，参数化，季度复盘直接 rerun	宏存在个人电脑里，同事离职 = 逻辑消失
调试成本	报错栈清晰，df.head() 一眼看脏数据	`Debug.Print`+ 逐行 F8，报错常常是类型隐式转换
适合谁	数据团队、增长中台、需要周报/月报自动化	市场/财务BP，老板坐在旁边等你按按钮

一句话选型建议：

周报、月报、历史三年回溯、多渠道归因模型 → Python
每日晨会那张“就改三个数”的渠道日耗表 → VBA

很多成熟公司其实是 VBA 做当日快照抓取 + Python 做 T+1 深度归因，各干各擅长的活。

七、三个只讲给做过投放的人听的干货

干货1：别用营收 LTV，用毛利 LTV。

你算出来 240 元 LTV，但履约成本、退货、优惠券分摊、客服工单，一扣只剩 96 元。而 CAC 是实打实花出去的现金。用营收口径算出的回本周期，普遍比真实快 1.8~2.5 个月。正确做法：ltv_gross = 订单额 × (1 - 退货率) × 毛利率 - 补贴摊销。

干货2：回本周期必须配“量”一起看。

一个渠道 CAC 极低、3 个月回本，但一个月只给你 40 个用户——全年贡献利润 3 万块。另一个渠道 8 个月回本，但月进 6000 人，年化 LTV 池子上亿。回本慢的渠道，可能才是增长引擎。 输出表里一定要加一列：月新增用户数 × (12月LTV - CAC) = 渠道年净贡献。

干货3：新渠道前 45 天，别算回本，算“学习成本”。

冷启动期平台算法在探索人群，CAC 必然高、留存必然差。硬算回本会逼着投放停掉唯一有潜力的新渠道。行业惯例：新渠道给 2 个完整获客 cohorts（约 60 天）的观察期，观察期只看“次月留存率”和“首单转化率”，不看回本。

八、课后自测：5 道选择题

关于 CAC 的计算口径，下列说法正确的是？
A. 用当月新增注册用户数除以当月总花费
B. 用当月渠道花费除以该渠道当月新增用户数
C. 用当月渠道花费除以当月付费用户数
D. 用季度总花费除以季度活跃用户数
某渠道 1 月获客，CAC = 200 元。该批用户累计贡献：M1=20元，M2=50元，M3=80元，M4=90元，M5=60元。按“存续月累计首次 ≥ CAC”的规则，回本周期是？
A. 3 个月
B. 4 个月
C. 5 个月
D. 12 个月内未回本
Python 版代码中，为什么注册用户数聚合必须用 nunique()而不是 count()？
A. nunique()计算速度更快
B. count()会把同一用户同日重复上报的注册记录算成多个用户，高估新增量
C. count()不支持字符串类型的 user_id
D. nunique()可以自动剔除空渠道名
当 Excel 单表数据量涨到 15 万行，VBA 宏开始明显卡顿，最合理的处理方式是？
A. 给 VBA 代码加更多 DoEvents保持界面响应
B. 把数据导出为 CSV，改用 Python 做分组归因，VBA 只负责展示最终结果
C. 把字典改成数组 Variant()批量读写，能解决一切性能问题
D. 调高 Excel 的显示缩放比例，减少重绘
关于“回本周期”的业务解读，下列做法最专业的是？
A. 只看回本月数，月数越短越好，超过 6 个月直接停投
B. 回本月数 + 该渠道月均新增量 + 渠道年净贡献利润，三者联合决策
C. 用营收 LTV 计算即可，不用扣履约成本和补贴
D. 新渠道第一天就按 12 个月回本标准考核，倒逼投放优化

附：5 道选择题答案

1.B ｜ 2.B（累计到 M4 = 20+50+80+90 = 240 ≥ 200，首次达标为第 4 个月）｜ 3.B ｜ 4.B（Variant 数组能缓解，但 15 万行已超出 VBA 舒适区，架构上应分工）｜ 5.B

如果你们现在的投放报表还是“财务手工 VLOOKUP 两小时”，建议这周就先把 Step 1 + Step 4 那 40 行 Python 跑通——不用一步到位建中台，先让“回本周期”这张表每天早 8 点自动躺在群里，比任何复盘会都管用。

需要的话，我可以把文中的代码再封装成一个只改两个路径就能跑的 ltv_payback.py单文件脚本，并顺带加上“毛利口径 + 渠道年净贡献”两列。要不要我接着出第 360 讲：LTV 曲线的曲线拟合（Beta Geometric 模型），用 3 个月早期数据预测 24 个月 LTV？

（本文数据均为模拟生成，口径仅供方法论演示，不构成任何投资与投放建议。）