RFM模型升级，使用Excel完成全自动分析！

昨天发了一篇相关的RFM分析，但是时间仓促，部分内容有待完善，所以临时决定删除，决定重新整理完善再发。今天又花了点时间重新整理，现在可以实现全自动分析了，并完善了可视化部分！

让我们先来预览一下可视化部分效果：数据看板（模板文末下载）

下面我们就来看一下重新升级后的RFM模型！

基础概念

还是一样，先普及一下基础概念。

Q1：RMF模型是什么？

RFM模型是衡量客户价值和客户创利能力的重要工具和手段。

在众多的客户关系管理(CRM)的分析模式中，RFM模型是被广泛提到的。

该机械模型通过一个客户的近期购买行为、购买的总体频率以及花了多少钱3项指标来描述该客户的价值状况。

RFM其实是三个需要分析指标的首字母

指标	英文名称	说明
最近一次消费间隔	Recency	越短越好（反向）
消费次数（频率）	Frequency	越大越好（正向）
消费金额	Monetary	越大越好（正向）

上面除了R的效果是和数值相反了，其他都是正相关！

Q2：如何对分析对象进行分类

我们根据三项指标是高还是低，把分析对象划分为8类：

从分类的名称中大家应该都直接看出，企业是盈利为目的的，所以消耗金额划分是更好重要一些！金额高的都是在“重要”类别中！其他字面意思即可！

分析思路

一般分四步处理：

1. 计算R、F、M值
2. 对R、F、M值打分
3. 根据分值和平均分，计算个指标是高还是低
4. 分析及可视化

要分析，我们就需要数据，其实根据需要分析的指标，我们可以推测出需要的数据，至少需要：

1. 会员或者客户的ID（名称）-分析的主体，对其计数（F）
2. 每次消耗的时间-计算最近消费时间（R）
3. 消耗金额-用于计算 （M）

上面三项是必须的，除此之外，我们可以还可以增加一些辅助分析的字段，比如分公司名称，区域，城市等等!

本次分析，为我们模拟的数据，数据源情况如下：

• 数据量级：1500条，
• 用户数量：26个用户，
• 时间周期：2020-1-1到2020-9-15
• 消耗金额：100-1000

数据源前20行预览：

万事具备，下面我们就按照步骤来建立模型！

建立模型

计算R、F、M值

RFM三个值中只有R有点难度，其他都非常简单，大家可以通过透视表手段处理，这里我们先做到全自动，所以直接全部使用函数处理，方便大家套模板！

公式如下：

R值：数组公式（三键录入）

=TODAY()-MAX(($A$2:$A$1501=$F2)*($B$2:$B$1501))

F值：消费频率。直接计数即可

=COUNTIF(A:A,F2)

M值：消费金额。直接按客户求合计即可

=SUMIF(A:A,F2,C:C)

以上我们就顺利的计算出三项指标，如此简单。

打分标准的确定

每个公司对三项指标的评分标准不同，具体要看企业实际业务要求，比如间隔1-3天的 得1分，4-10得2分等等，一般根据实际来测算处理。

本次案例中，我们使用的是5分制，按均值等分处理！具体划分如下：

除了R是越大分数越低（方向),距离最后一次消耗时间越短越是优质客户，显然分数越高，也就是越小分数越高。其他都是正向变动！

这里特别感谢泠雨浅唱同学的提醒，本次我们得分分开，也更加方便维护

这里需要解释一下RFM分段的确定。

先计算各指标划分为5等份的均值

均值：=(最大值-最小值)/5

然后我们依次使用最小值，加上对应的均值

R分段举例如下：

S2公式：获取最近消耗间隔的最小值作为起始

=MIN(G:G)

S3公式：最小值，依次加上均值的数倍

=(MAX($G:$G)-$S$2)/5*ROW(A1)+$S$2

R分段的意思如下：

• [3,7)     天--5分
• [7,10)   天--4分
• [10,14) 天--3分
• [14,17) 天--2分
• [17,∞)  天--1分

F和M计算分段同理，这里就不再啰嗦，大家拿到文件后可以再细细琢磨！

如何打分

根据上一步的分段，我们可以使用IF来处理，但是IF来写实在是麻烦，直接考虑有没有简单的函数，这里可以使用VLOOKUP近似查找或者LOOKUP处理！

案例中使用的是LOOKUP！

R得分：

=LOOKUP(G2,$S$2:$S$6,$T$2:$T$6)

F和M得分公式原理一致！

判断高低

分类中，我们有高和底，这里的高低可能各企业也有自己的标准，或者一般使用均值作为基准点，高于平均值：高，否则就是低，方便我们处理，我们把高用1表示，低用0表示，那么8个分类表中，我们新增一列，状态

=SUBSTITUTE(SUBSTITUTE(AB2&AC2&AD2,"高",1),"低",0)

R状态判断如下：

=IF(J2>AVERAGE(J:J),1,0)

F状态和M状态同理，详见文件！

我们把三列拼接到一起，方便我们引用数据！

用户分类

前面我们已经完成了全部的基础工作，下面只要通过客户标识（分类）

来从分类表中引用即可完成分类工作

客户分类公式：

=INDEX(AA:AA,MATCH(模型!P2,AE:AE,))

可视化呈现

可视化部分，先做好看一点，可以找一个数据看板的模板参考一下，比如本次就是随便找了一个图片（大概如下）

我们主要是获取他的配色方案，自己适当加上需要的图表和指标数据即可！

由于我们模拟的都是随机数据，下面我们就使用动画给大家演示一下，

动态效果！

动画演示：动态更新数据！

小结

RFM模型分析的目的是为了根据不同的客户（会员）类型采用不同的管理方法，以实现用户的细化管理，进而产生更大的收益！