ModelMonkey Logo
分析中级阅读约 3 分钟

如何在销售管道 CRM 中追踪阶段回退

检测管道商机向后滑落的情况，在 Google Sheets 中标记每次回退，并生成销售总监可直接采取行动的周报汇总。

本指南介绍如何在 Google Sheets 中利用 CRM 的阶段历史导出数据检测阶段回退情况，用公式标记每一次向后移动，并将结果汇总为仪表板，供销售总监在周一晨会时直接查阅。当一笔商机从「提案」退回到「需求发现」阶段，这是一个值得深究的信号——而大多数 CRM 仪表板不会主动为你呈现这些信息。

开始前的准备

  • CRM 阶段历史导出文件（HubSpot Pipeline Activity、Salesforce OpportunityFieldHistory 或同类数据），至少包含以下字段：商机 ID、阶段名称、阶段变更日期及负责人/所有者列
  • 已将上述导出数据载入 Google Sheets——根据历史数据跨度和团队活跃度，实际行数通常在 5,000 至 8 万行之间
  • 熟悉 VLOOKUP、QUERY 及手动区间排序操作
  • 已按正确顺序定义好销售管道阶段列表（若销售人员对「需求发现」阶段使用了五种不同名称，请先统一规范后再进行后续操作）

分步指南

1

从 CRM 导出正确的数据

标准 CRM 商机报告为每笔商机提供一行记录，显示该商机当前所处的阶段——这对本次任务并无用处。我们需要的是每次阶段变更对应一行的历史记录日志，记录每笔商机在何时发生了移动、移动方向如何。这两种导出格式截然不同，请在着手搭建之前确认你获取的是正确的那份数据。

在 HubSpot 中，相关数据位于「报告 > 销售 > 商机阶段历史」。在 Salesforce 中，查询 OpportunityFieldHistory 对象并筛选 Field = 'StageName'。在 Pipedrive 中，在「报告」下导出「管道变更日志」。所有主流 CRM 均包含此类数据，但获取路径各有不同。

  • 以 CSV 格式下载，至少包含以下列：商机 ID、阶段名称、阶段变更日期及商机负责人
  • 如有条件，请同时导出商机金额——一笔 120 万元商机的回退与一笔 2 万元商机的回退，背后对应的是截然不同的对话
  • 至少导出 6 个月的历史数据以供趋势分析；仅凭一周的数据几乎无法判断任何规律
  • 10 人以上的销售团队、6 个月数据量，预计行数在 1.5 万至 6 万行之间

Pro Tip

若导出文件同时包含「来源阶段」和「目标阶段」两列，请优先使用该格式——它可以跳过下方的两个步骤。若每条记录只提供当前阶段，则需要在第 4 步中自行计算上一阶段。
2

审查并清理阶段名称

在搭建任何内容之前，先对「阶段」列进行透视，查看所有不重复的值。在这一步，你往往会发现「演示」、「产品演示」和「演示/展示」其实是同一个阶段，只是不同销售人员填写的方式不同——而此时每个比较公式都会将它们视为三个独立阶段，从而遗漏三分之二的回退事件。

创建一个双列清理工作表，A 列填写「原始名称」，B 列填写「标准名称」，然后在原始数据旁添加一个清理列：

  • =IFERROR(VLOOKUP(TRIM(C2), Cleanup!$A:$B, 2, FALSE), C2) — IFERROR 会将已正确匹配的名称原样传递
  • TRIM 不可省略；CRM 导出数据中经常包含前导或尾随空格，在单元格中肉眼不可见，却会导致所有精确匹配失败
  • 在草稿列中使用 =UNIQUE(C2:C) 提取所有不重复的阶段名称，再据此构建清理对照表
  • 清理列效果确认无误后，将其复制并通过「选择性粘贴 > 仅粘贴值」覆盖原「阶段」列，然后删除辅助列
3

创建阶段顺序查找表

只有在数值上定义了「向前」的方向，阶段回退才有意义。新建一个名为 StageOrder 的工作表，包含两列：阶段和顺序。列出每个标准阶段名称，并为其分配一个整数。数字的大小方向即为回退逻辑的判断依据。

阶段顺序
潜在客户开发1
资质评估2
需求发现3
产品演示4
方案提案5
商务谈判6
成交7
丢单99

「丢单」分配的是 99，而非 8。若将其设为 8，则一笔从「丢单」重新进入「商务谈判」的商机会被标记为回退，但实际上这是重启商机——属于另一类需单独追踪的情况。

  • 仅使用整数，不使用小数，以确保比较结果无歧义
  • 若你的销售管道存在并行路径（例如「技术评估」可与「方案提案」同步进行），可为并行阶段分配相同的顺序编号，并由团队共同决定跨路径移动是否算作回退
  • 在 StageOrder 中添加「状态」列（启用/已废弃），以便在年中阶段名称更名时不丢失历史数据
  • 每当 CRM 管理员新增阶段时，请及时更新此表——缺失的阶段会导致 VLOOKUP 报错并被 IFERROR 捕获，提示你有内容需要修正

Pro Tip

锁定 StageOrder 工作表，防止销售人员或 CRM 管理员在分析过程中意外修改数字。可通过「数据 > 保护工作表和范围」进行保护。
4

对数据排序并附加阶段序号

这是整个方案的核心枢纽。你需要按商机 ID 升序、再按变更日期升序对数据进行排序。这样可以将每笔商机的事件按时间顺序排列，从而在同一商机内逐行进行比较。排序一旦出错，所有回退标记都将失去意义。

在 Google Sheets 中：「数据 > 排序范围」，先按商机 ID（A→Z）排序，再添加第二排序级别按变更日期（A→Z）排序。4 万行数据大约需要 10 至 15 秒。

接下来在数据中添加三个辅助列：

G 列（Stage_Order）——每行阶段的数字序号：

=IFERROR(VLOOKUP(C2, StageOrder!$A:$B, 2, FALSE), 0)

H 列（Prev_Stage_Name）——上一行的阶段名称，仅在属于同一商机时返回：

=IF(A2=A1, C1, "")

I 列（Prev_Stage_Order）——对数字序号进行同一商机的判断：

=IF(A2=A1, G1, "")
  • 将三个公式从第 2 行向下拖至数据末行
  • 当商机 ID 发生变化时（即新商机的第一条事件），H 列和 I 列返回空值——这是正确的，因为没有上一阶段可供比较
  • 任何 G 列返回 0 的行表示存在无法识别的阶段名称；筛选出这些行并检查第 2 步中的清理对照表

Pro Tip

数据量超过 5 万行时，每次编辑后这些辅助列的重新计算可能需要 20 至 40 秒。初始设置完成后，将 G 至 I 列复制并通过「选择性粘贴 > 仅粘贴值」将其固化。每周加载新数据时重新执行一次即可。
5

标记回退事件

数据已排序且数字阶段序号已就位，回退标记只需进行两项比较：是否存在可供对比的上一阶段（即 I 列不为空），以及当前序号是否低于上一序号？序号更低意味着商机向后移动。

J 列（Regression_Flag）：

=IF(I2="", "First Entry", IF(G2<I2, "Regression", IF(G2=I2, "No Change", "Forward")))

K 列（Regression_Path）——具体的阶段转变路径，便于总监查阅：

=IF(J2="Regression", H2&" → "&C2, "")

这将生成类似「方案提案 → 需求发现」和「商务谈判 → 产品演示」的条目——这些正是销售副总裁希望深入分析的转变路径。

  • 筛选 J 列中标注为「Regression」的行，在大规模信任输出结果之前，抽查 10 至 15 行进行核实
  • 「No Change」条目出现的情况是：商机在 CRM 中被保存但阶段未发生变化（常见于销售人员仅修改预计关单日期或金额等其他字段时）；这些属于噪音，并非回退
  • 注意同一阶段被重复经历两次的商机——这在序列中表现为「Forward」→「No Change」→「Regression」，通常意味着存在数据录入问题，值得向 CRM 管理员反馈
  • 「First Entry」行因 I 列为空，会自动从分析中排除；下游查询中无需额外添加 IFERROR
6

使用 QUERY 构建回退汇总

J 列已标记所有回退事件，运营报告最关注的聚合维度包括：哪些销售人员回退次数最多、哪些阶段转变最频繁，以及月度趋势是否有所改善。QUERY 处理 8 万行以上数据的聚合运算通常不超过 2 秒——此处请勿使用 COUNTIFS，在工作表同时运行其他计算的情况下，超过 3 万行时 COUNTIFS 会明显卡顿。

新建名为 Regression_Summary 的工作表，从以下三个查询开始：

按销售人员统计回退次数：

=QUERY(Data!A:K, "SELECT E, COUNT(A) WHERE J='Regression' GROUP BY E ORDER BY COUNT(A) DESC LABEL E 'Rep', COUNT(A) 'Regression Count'", 1)

最常见的回退路径：

=QUERY(Data!A:K, "SELECT K, COUNT(A) WHERE J='Regression' GROUP BY K ORDER BY COUNT(A) DESC LIMIT 10 LABEL K 'Regression Path', COUNT(A) 'Count'", 1)

月度趋势：

=QUERY(Data!A:K, "SELECT YEAR(D), MONTH(D), COUNT(A) WHERE J='Regression' GROUP BY YEAR(D), MONTH(D) ORDER BY YEAR(D) DESC, MONTH(D) DESC LABEL YEAR(D) 'Year', MONTH(D) 'Month', COUNT(A) 'Regressions'", 1)
  • 将 Data!A:K 替换为你实际的工作表名称和标签页名称
  • QUERY 的日期函数 YEAR() 和 MONTH() 要求日期列为 Google Sheets 的真实日期值，而非文本字符串；若日期在导入后变成文本（Salesforce 导出数据中较为常见），请先使用 =DATEVALUE(D2) 进行转换，再运行上述查询
  • 在销售人员汇总表旁添加回退率列——每位销售人员的回退次数除以其总阶段事件数，比原始计数更能客观反映实际情况（拥有 200 笔商机且回退 20 次的销售人员，与拥有 30 笔商机且回退 3 次的销售人员，回退率同为 10%，但原始计数看起来差异悬殊）
  • 若「变更日期」列中同时存在「2024-01-15」、「1/15/24」和「15 Jan 2024」等多种混合格式（当导出数据来自多个 CRM 地区时，这种情况很常见），则需要先进行日期标准化处理，QUERY 才能按日期进行筛选

Pro Tip

混合日期格式是一个独立的清理问题。结合使用 IFERROR、DATEVALUE 和 REGEXEXTRACT 可以解析大多数格式，但第一次处理格式高度混乱的日期列时，请预留 30 至 60 分钟的处理时间。
7

构建总监仪表板标签页

汇总工作表用于深度分析，仪表板标签页则是周一晨会时投屏展示的内容。建议保持 3 个面板：核心数字、销售人员明细和高频回退路径。内容一旦超过这个范围，这份电子表格就会变成总监不再查阅的摆设。

仪表板若要自动更新，QUERY 公式必须从实时数据中拉取，因此此处不要像第 4 步中的辅助列那样将值固化。

  • 面板 1（核心数字）： 两个带日期范围的 QUERY 计数——一个统计本周，一个统计上周——再加一个显示差值的简单减法单元格。回退次数上升时标红，下降时标绿。
  • 面板 2（本季度销售人员明细）： 使用第 6 步中的销售人员 QUERY，并通过 DATE(YEAR(TODAY()), MONTH(TODAY())-MOD(MONTH(TODAY())-1, 3), 1) 作为日期下限筛选本季度数据。使用 LIMIT 5 将结果限制为 5 行，使表格保持固定大小。
  • 面板 3（高频回退路径）： 路径查询限制显示前 5 条，并附注每条路径占总回退次数的百分比。该百分比列需手动计算，但值得添加。
  • 添加「最后更新时间」单元格，公式为 =TEXT(NOW(), "YYYY年M月D日")，让打开文件的人一眼知道数据是否为最新
  • 通过「数据 > 保护工作表和范围」锁定仪表板标签页，防止他人编辑——误触一个单元格就可能导致 QUERY 公式失效，而且问题往往不会立即显现，直到有人发现数据停止更新才会察觉

总结

你已搭建了一套适用于任意 CRM 导出数据的回退检测体系：阶段顺序映射、基于商机 ID 校验的逐行比较，以及可处理 8 万行以上数据且无明显延迟的 QUERY 聚合。总监仪表板让你能够以有据可查的方式回答「管道健康度是否在改善」，而非给出一个模糊的承诺。

大多数团队在运行这套流程一个月后都会提出同一个问题：能否自动化每周的数据刷新？这正是手动流程的上限所在——检测逻辑已足够健壮，但每周仍需有人手动完成下载、清理、排序和重新导入等操作。查看 ModelMonkey 方案——支持 Google Sheets 和 Excel 两种环境。

常见问题

阶段回退与商机重启有什么区别？

阶段回退是指商机在有效管道中向后移动（例如从「方案提案」退回「需求发现」）。商机重启则是指已标记为「丢单」的商机重新进入任意有效阶段。在 StageOrder 表中将「丢单」赋值为 99，可以防止重启商机被误判为回退——因为任何有效阶段（序号 1 至 6）在数值上均低于 99，公式会将其识别为「Forward」而非「Regression」。如需单独追踪重启商机，可筛选 Prev_Stage_Name 等于「丢单」的行。

我的 CRM 只能导出当前商机阶段，不提供阶段历史记录，还能检测回退吗？

可以，但你需要在不同时间点获取两份数据快照。本周导出完整商机列表，下周再导出一次。通过商机 ID 将上周的阶段用 VLOOKUP 匹配到本周的导出数据中。凡是当前阶段序号低于上次快照阶段序号的，即为回退。这种方法的局限在于，你只能捕捉到两次导出时间段内发生的回退——同一周内多次向后移动将折叠为单一信号。

公式如何处理商机先跳过阶段向前推进、之后再发生回退的情况？

第 5 步的逐行比较能正确处理此类情况，因为它将每条事件与该商机紧邻的上一条事件进行比较，而非与最初的第一条事件比较。一笔经历「潜在客户开发 → 产品演示 → 方案提案 → 需求发现」的商机，最后一次移动会被标记为从「方案提案」（序号 5）回退至「需求发现」（序号 3），结果完全准确。

为什么汇总表使用 QUERY 而不是 COUNTIFS？

5,000 行以内，COUNTIFS 完全够用。超过 3 万行后，含多个条件的 COUNTIFS 会在每次重新计算时逐一评估所有单元格组合。在同时运行其他公式的工作表中，这会导致重算时间超过 60 秒，有时甚至更长。QUERY 使用类似 SQL 的计算引擎，聚合速度显著更快——对 8 万行数据进行销售人员维度的汇总，通常在 3 秒以内完成。如果工作表变得卡顿，COUNTIFS 通常是罪魁祸首。

CRM 管理员新增销售管道阶段后应该怎么做？

请立即将新阶段及其序号添加到 StageOrder 表中。在你添加之前，含有该阶段名称的历史行将从 VLOOKUP 返回 0（由 IFERROR 捕获并作为标记显示）。一旦你在 StageOrder 中添加该行，下次重新计算时这些 0 值将自动修正。更复杂的情况是：当一个阶段被插入两个现有阶段之间——例如在「产品演示」（序号 4）和「方案提案」（序号 5）之间加入「技术评估」——此时其上方所有阶段都需要重新编号，以保持相对顺序的正确性。