excel怎么数据透视分析实现客户的RFM模型分析

说实话，我第一次接触RFM模型的时候，完全是一头雾水。什么R值F值M值的，听起来像是某种神秘的代码。后来在实际工作中慢慢摸索，才真正理解这个看似简单的三维模型背后居然藏着这么大的威力。今天我就把用Excel数据透视表做客户RFM分析这套方法完完整整地分享出来，保证你看完就能上手操作。

先说句掏心窝的话。很多公司花大价钱买各种数据分析软件，结果连最基本的客户分层都没做好。其实Excel自带的数据透视表功能，配合RFM模型，完全可以解决百分之八十以上的客户分析需求。关键不在于工具多高级，而在于思路对不对。

什么是RFM模型

RFM模型其实是三个英文单词的缩写，看起来简单，但真正用起来有很多门道。

R是Recency，中文叫最近购买时间。这个指标看的是客户上一次消费离现在有多远。直觉告诉我们，最近买过东西的客户，未来再次购买的可能性肯定比那些很久没来的客户高。这个逻辑其实很朴素，但朴素的东西往往最实用。

F是Frequency，中文叫购买频率。简单说就是客户在一定时间内买了多少次东西。买的次数越多，说明这个客户越忠诚，粘性越好。一个季度买十次和买一次的客户，重要性显然不在一个档次上。

M是Monetary，中文叫购买金额。这个最好理解，就是客户累计花了多少钱或者平均每次花多少钱。贡献大的客户自然要重点关注，这是最基本的经济账。

把这三个维度组合起来，就能把客户分成不同的群体。有的人虽然很久没来，但以前花的钱很多，这种客户要重点召回。有的人虽然买得频繁，但每次金额很小，这种客户可能需要提升客单价。还有的人又近又频又高花钱，那就是妥妥的超级VIP。

为什么选择Excel数据透视表来做RFM分析

你可能会问，市面上专业BI工具那么多，为什么非要执着于Excel？答案很简单：门槛低、上手快、够实用。

数据透视表是Excel里最被低估的功能之一。它能在几秒钟内完成海量数据的分类汇总，要是用普通函数处理同等规模的数据，够你忙活半天的。更重要的是，整个分析过程是可视化的，你拖拖拽拽就能看到不同维度的数据切片，所见即所得。

还有一个实际的好处。大多数公司的客户数据本身就存储在Excel或者导出自ERP系统，直接在Excel里做分析，不用折腾数据导入导出，省心省力。我见过很多团队，数据就在系统里躺着没人分析，就是因为觉得导数据太麻烦。用Excel的话，这个障碍基本不存在。

做RFM分析前的数据准备

老话说得好，磨刀不误砍柴工。数据准备这个环节看似枯燥，但直接决定了后面分析的质量。我见过太多人兴冲冲开始分析，最后发现数据有问题，全部白费功夫。

首先你得有原始的交易记录。这份数据至少要包含以下几个字段：客户ID、交易日期、交易金额。客户ID要唯一标识一个人，不能今天一个ID明天又换一个。交易日期要规范，精确到天就行。金额最好统一货币单位，别有乱七八糟的符号。

拿到原始数据后，有几个检查步骤必须做。第一是去重，同一笔交易别重复出现。第二是异常值处理，那种明显输错的价格该修正修正，该剔除剔除。第三是日期范围，要明确你分析的是哪个时间段的数据，比如最近一年或者最近两年。

举个例子，假设你有一份这样的基础数据：

td>320

这份数据看起来挺干净的，但要做RFM分析，还需要在旁边增加几列辅助数据。这个我们下一节详细说。

用数据透视表计算RFM各项指标

正式进入操作环节。我会分步骤演示，每个步骤都说清楚为什么要这么做。

第一步：确定分析基准日期

在计算最近购买时间之前，你得先定一个"今天"。因为RFM是动态变化的，你不可能每次分析都重新调整公式。最佳实践是选定一个固定日期作为基准，比如本月的第一天，或者你做分析的当天。

假设我们选定2024年6月1日作为基准日。那么所有客户在这个日期之前的交易记录都纳入分析范围。

第二步：计算R值

R值是每个客户距离最后一次购买有多少天。Excel里可以用MAX函数配合IF函数来计算。

在原始数据旁边新建一列叫"距今天数"，公式可以这样写：=DATEDIF(MAX(IF(A:A=A2,B:B)),DATE(2024,6,1),"d")。这个公式的意思是，找出每个客户所有交易记录中最大的日期，然后用基准日期减去它，得到天数。

如果你觉得公式太复杂，也可以用数据透视表来做。先选中所有数据，插入数据透视表，把客户ID拖到行区域，交易日期拖到值区域，汇总方式选最大值。这样透视表就会显示每个客户最近一次购买日期，然后你在旁边用公式计算天数就可以了。

第三步：计算F值

F值相对简单，就是数每个客户在分析期间内有多少笔交易。数据透视表里直接把客户ID放行区域，交易日期放值区域，汇总方式选计数，就搞定了。

要注意区分订单数和商品件数。F值应该算的是交易次数，不是商品数量。一个人一次买了十件东西，算一次交易而不是十次。

第四步：计算M值

M值有两种常见的算法。一种是算总消费金额，就是把所有交易金额加起来。另一种是算平均客单价，用总金额除以交易次数。两种都可以用，看你侧重哪个维度。

数据透视表同样可以搞定：客户ID放行，金额放值，汇总方式选求和就是总金额，选平均值就是客单价。

第五步：整合RFM数据

经过上面几步，你应该得到一份类似这样的中间数据：

有了这份数据，后面的分级就好办了。

对客户进行RFM分级

现在到了最关键的一步：给每个客户的R、F、M分别打分，然后组合成最终的客户分层。

评分标准怎么定

评分方法主要有两种流派。第一种是等频划分，把所有客户按数值大小排序，然后分成n组，每组人数差不多。第二种是等距划分，按数值的绝对区间来分，比如R值小于30天算一分，30到60天算两分。

我个人的经验是等频划分更合理。因为不同行业、不同公司的客户结构差异很大，用绝对数值往往不太靠谱。比如一个客单价几千块的行业，你用五百块做分界线可能没什么意义，但用中位数来划分就稳定得多。

具体怎么操作呢？假设你用五分制。先把R值这一列从小到大排序，最小的百分之二十客户R值打5分，接下来的百分之二十打4分，以此类推。F值和M值也是同样操作，但要注意F值是越大越好，所以排序方向和R值相反。

Excel里可以用PERCENTRANK函数来辅助打分，或者用RANK.EQ函数配合一些计算都能实现。这块稍微有点复杂，但网上有很多现成的模板，搜RFM分析模板能搜到不少，改一改就能用。

组合RFM得分

打分完成后，每个客户都有一个三位数的编码，比如545、235、451等等。这个编码就是他的RFM标签。

接下来可以根据编码进行客户分层。常见的分法是把客户分成八到十二个群体。重要价值客户是555，重要发展客户是515，重要保持客户是355，重要挽留客户是135，以此类推。

你可能会问，为什么不是三维度的排列组合都算一遍？因为有些组合在实际业务中几乎不存在，比如既高频又高消费但最近很久没来的客户，这种本来就很少见，分太细反而增加管理成本。

数据透视表的高级应用技巧

掌握了基础操作后，还有几个进阶技巧能让你的分析更上一层楼。

第一个是动态数据源。很多新手做数据透视表的时候，数据源是手动选的一大片区域，结果新数据加进来后透视表不更新。解决办法是把数据源转换成表格，插入透视表的时候选择表格，这样新数据进来后刷新一下就行。

第二个是切片器的使用。数据透视表配合同切片器，可以做出类似BI工具的交互效果。比如你想只看某个地区的客户分布，或者只看某个消费层级的客户，加上切片器后点选一下就过滤，非常方便。

第三个是分组功能。对于日期字段，透视表自带按年按季度按月份分组的功能，不用自己折腾公式。对于金额字段，也可以手动设置区间分段，比如0到500、500到1000、1000以上这样的区间。

还有一个小技巧是做透视图而不是透视表。图表比表格更直观，汇报的时候效果更好。你可以在透视表的基础上插入柱形图或者饼图，然后调整一下样式就能直接拿去开会用了。

常见问题和注意事项

在实操过程中，有几个坑我踩过也见过别人踩过，这里给大家提个醒。

• 数据时间窗口要固定。你不能用最近三个月的数据来分析F值，因为时间太短，很多客户的真实购买频率体现不出来。一般建议用一年或者更长的数据。
• 异常值要处理。有些客户可能是批发商，一次性买很多，这种客户会拉高M值。如果你做的是零售业务，可能需要把这类客户单独拎出来处理。
• 评分要定期更新。客户是动态变化的，上个月的高价值客户这个月可能就流失了。RFM评分至少一个季度要重新做一次，热门行业可能需要更频繁。
• 不要迷信分数。RFM是一个很好的分析框架，但它不是万能的。有些客户虽然RFM分数不高，但有很强的品牌忠诚度或者战略价值，这些要结合业务判断，不能完全看数据。

最后我想说，数据分析工具终究只是工具，真正值钱的是你对业务的理解和判断。Excel数据透视表能帮你看见数据，但看懂数据、做出决策的还是你自己。Raccoon - AI 智能助手这样的工具可以辅助你更高效地处理和分析数据，但核心的业务洞察还是需要人来完成。

希望这篇文章对你有帮助。如果你在实际操作中遇到什么问题，欢迎一起探讨。