EXCEL-RFM（用户）建模实战

这是数据分析BY EXCEL的第二篇（RFM实战篇），内容很干。

主要通过一个实例来撕碎RFM模型高大上的外衣，让它成为你我的掌上玩物。没错，不仅是了解，还能亲手建立一个属于自己的RFM模型。

实战数据（脱敏）下载链接已放在文末。

RFM模型的大名可谓如雷贯耳，官方说法：RFM模型是衡量客户价值和客户创利能力的重要工具和手段。

简单的说就是按照R（最近一次购买距今多少天)，F（购买了多少次）以及M（平均或者累计购买金额）将客户进行分类，按照这3个维度可以切分出8类客户，最后我们根据不同类型客户占比情况来评估客户的整体分布，并针对不同类型的客户进行有针对性的营销。

RFM模型只需要3个字段（R、F、M），就能够对客户进行价值分类，且适用于各行各业。

咳！概念一讲起来就像教科书一样乏味，我们来看一组对比。

你的原始数据是这样的：

学完后你的模型是这样的：

是不是有一丝心动了？

整个流程可以分成4步：观察数据——数据清洗——维度确认——分值计算——结果展示。

Step 1 观察数据

数据分析和建模的第一步就是就是认识数据，请，屏息观察数据30S，

不难发现：

1、源数据一共28834行，涉及12个字段，每一行代表一笔订单。

2、按“付款时间”排序，发现订单数据时间范围是2017年11月1日-2018年4月30日共6个月数据

3、总体数据规整，但”发货时间“存在缺失值，且“发货时间”为空的行，对应“订单状态”的值是“付款以后用户退款成功，交易自动关闭”，退款用户数据不该纳入模型，这是我们需要剔除的数据。

Step 2 数据清洗：

2.1 删除

首先，需要剔除掉退款的订单，

选中“订单状态”列(G列），点击“开始”模块的”排序和筛选“，勾选“筛选”

这时候“订单状态”列会出现一个小三角，点击之，

OK，所有退款的订单已经被我们筛出来了，

选中所有筛选出来的行，切记，先按'ALT + ;“（选取区域只选择可见区域，避免删除正常交易数据），然后右键，”删除行“，这样，所有退款数据被删除，我们再在“订单状态”勾选“交易成功”的订单，发现初步清洗后的数据还剩下27794行。

2.2 关键字段提取

这一步我们要整理出模型所需要的关键字段，RFM模型，当然是R、F、M三兄弟的值了：

R（最近一次购买距今多少天)，F（购买了多少次）以及M（平均或者累计购买金额）。

我们把源数据复制到另一个SHEET（养成好习惯，备份一个咯），再把多余的列删掉，留下需要的3个字段——“买家昵称”、“付款时间”、“实付金额”：

等等，RFM模型所需要的字段好像还没有出来诶！

别急，上面3个字段能够衍生出模型所需要的所有关键值。

有请数据透视表先生登场。

选中所有数据，插入“数据透视表”，把“买家昵称”拖到行的位置，

再把“付款时间”、“买家昵称”、实付金额“都拖到列的位置（按照R,F,M的逻辑）：

到这一步，还记得上一篇讲的数据透视表吗？数据透视表自动将”买家昵称“作为分组依据，换句话说，我们源数据中一个客户多次下单的数据，现在都汇总到1行了。

数据透视表默认是计数表示，也就是说，“买家昵称”那一列显示的数字，就是客户购买次数，即F的值。

那，时间，金额怎么算呢？

我们需要分别设置他们的字段格式，选中“付款时间”右键“值字段设置”，计算类型默认是计数，我们把他改成“最大值”（假如一个客户购买过3次，我们只需要获取他最近1次的购买时间，对时间类型的数据来说，时间越大值越大，比如2018年1月1日 10:00 > 2017年12月1日 10:00，这里的最大值就是最近一次购买时间）

设置完成后时间格式会变成数字，不要慌，选中“付款时间”列，设置成“年/月/日 时:分”的格式：

值的设置同理，我们想要知道每个客户平均下单金额，把计算类型改成“平均值”即可。

到这里，我们的初始数据整理基本告一段落，结果如下：

下面我们把做好的透视表的所有数据（不要复制最下面空白行和总计行）复制到另一张sheet（防止公式冲突），此后的操作都是针对复制的表格。

等等！R（最近一次购买距今多久）是要求距离现在多少天，现在只有一个最近付款的时间是什么鬼！

所以，我们插入一个辅助列。

由于订单是截止到4月30日的，我们假设建模时间是2018年5月1日，求每个客户R值，就是求5月1日这一天距离他最近一次付款时间的间隔天数。

直接上公式：

注：B2是第一个客户最近一次付款时间所在的单元格

我们先用DATE(2018,5,1)赋予2018年5月1日时间格式，便于DAYS（）计算。

再用DAYS(DATE(2018,5,1),最近一次下单日期)，直接计算出客户最后一次购买时间距离现在有多少天。

R值出炉，F值现成（买家昵称那一列），M值已定（平均实付金额列），每个客户都一个R、F、M值与之对应，我们调整下列的位置，修改下列名，整理好的数据如下：

至此，三值鼎立之势已成。

STEP 3——维度确认：

现在，每个客户都有一个对应的R、F、M值。

简单来说，维度确认是给每个客户的（R值、F值、M值）打分，我们需要建立一个评判标准，给我们期待的值（比如最后一次购买时间距离今天越近越好）打上高分，给不喜欢的值（付款时间距离今天越久越不好）打低分。

以R值为例，我们筛选出了每个客户最后一次下单距离现在x天，那这个x是不是越大越好呢？并不是！这个值越大，说明客户越久没有回购，他流失可能性也更大；这个值越小，就表明客户最后一次付款时间距离现在越近。

一般是采用5分制（以30天为维度），这里我们规定最近一次购买时间距离今天0-30天的，给它打5分，30-60天的，是4分，60-90、90-120、120-720天分别是3、2、1分。

怎么操作呢？我们创建一张新的sheet，可以制作如下辅助表格：

其中R-SCORE列就是我们给每个区间打的分数。

接着再给F（购买频次）和M平均购买金额打分：

特别说明一下，这里的分数区间都是包含最小值不包含最大值的，拿F值来说，1=<购买频次<2时，F值是1分，而这个区间的唯一整数是1，也就是给购买频次为1的客户打1分，2次2分，5次到100次客户打5分（之所以最高分的最大值都是一个大的很离谱的数字，是因为这样更可能把数据中极端的值包含在内，避免后面公式出现错误）

注：这个分值应根据实际业务进行调整。

STEP 4——分值计算

回顾一下，我们现在已经掌握了每个客户的R\F\M值，也已经创建好了打分的维度框架。

下面就是按照确认好的维度（STEP2），对每个客户的3个值进行打分。

这里复习一下上一篇文章的IF函数，一分钟迅速唠叨一下哈，函数的形式是介个样子的：IF(表达式，表达式为真的操作，表达式为假的操作）。不太形象是吧。IF(A1>1，'大于1',“等于0”)，就是如果A1的值大于1，公式所在的单元格就显示为大于1，否则就显示为等于0。IF的强大之处在于它可以无限嵌套。

以R值为例，参考我们STEP2中确定的打分维度，在M列后新建一个R-SCORE列，在F2的位置输入如下公式：

这个公式是说，当C2单元格（R值）小于30，打5分，大于等于30小于60，打4分，依次类推，当大于120天，打1分。

F、M值打分公式逻辑一样，不同的只是维度：

F值：

M值：

我们，已经，完成了第一轮打分了，结果差不多是这样的：

EXCUSE ME？第一轮？

难道还有第二轮？

恭喜你答对了！

客观请看，现在R、F、M值分值在1-5之间，3个值进行组合，111，112，113...这样可以组合出125种结果，如果将结果分成这么多类和不分没有什么两样，一般来说，我们只需要判断每个客户的R、F、M值是否大于平均值即可。

下面，我们用AVERAGE()函数，分别计算出R、F、M值的平均值（分别计算出所有客户R值的平均、F值的平均、M值的平均）:

然后，我们再把每个客户的3个值与平均值进行比较，进行二次判断。大于等于平均值的显示1，小于的显示0。（二次判断是为了简化分类结果，经过二次判断，客户分类结果最多只有8类）。

这一步很简单

用IF（值>=平均值，1，0）即可。结果如下：

这样，我们将1000名客户按照R、F、M值分成8组（111，110，101，100，011，010，001，000）

分别代表什么呢？

每一个客户的1和0分别代表着是否大于对应的平均值，我们可以把客户分成下面8组：

举个栗子，一个客户，R大于均值（1），也就是最近有购买，F大于均值（1），也就是购买频率超过平均次数，M大于均值（1），平均购买金额大于所有客户的平均金额，综合来说，近期有购买，购买频次高，每次购买金额高，这样的客户，当然是重要价值客户了！

同理，潜力客户呢，是最近有购买，购买频次高，但是每次购买金额低的客户，需要我们去挖掘，提升他们的客单，让他们转化为重要价值客户。

重要深耕客户呢，他们是最近有购买，但是不经常买，虽然每次消费金额高，我们当然要想方设法提升他们的购买频次。

下面各种类型客户，也是这样一个推理逻辑。

在操作上，依然是一个IF嵌套函数：

I、J、K列分别对应客户R值是否大于均值，F值是否大于均值，M值是否大于均值。

AND(I2=1,J2=1,K2=1)=TRUE 是说如果3个值都等于1（意思是客户3个值都大于平均值），则归为重要价值客户，以此类推。（不要被IF语句迷惑，其实就是一串嵌套）

到这一步，RFM模型地基已经建立完毕，3值鼎立之势已经被我们化成了8类。

只需插入数据透视表就可以知道店铺这一段时间，不同类型的客户人数和占比，不过，社会主义接班人的我们，还可以更进一步，一探金额和客户类型之间的关系。

我们在最后加入一列，用客户的F值 X M值，得到用户累计消费金额。

STEP 5 结果展示：

选中所有数据，插入数据透视表，

5.1 客户占比分析：

这一步，我们想知道不同类型的客户人数具体有多少，每个占比分别是多少。

将“客户类型”放到行区域，再把”客户类型“（从上面的数据透视表字段区域拖）和“客户类型”拖至值区域。没错，拖两次，一次是为了计数，一次是为了看占比。

值字段默认都是计数，数据透视表结果区域变成了这样：

右键“客户类型2”列，选择“值显示方式”，“列汇总的百分比”：

我们拿到了不同类型的客户人数及占比：

别对两列数据作图之，

5.2 客户金额分析：

我们还想知道不同类型客户他们花了多少钱以及金额占比是怎么样的，和4.1逻辑类似，这里省略透视操作：），只给结果：

基于汇总结果和订单源数据，我们发现（这里简单说两点，抛砖引玉）：

1、流失客户占比最高，达8605人，人数占比33.85%（这类客户最近无购买，当初购买频次低于平均值且平均下单金额也低），这部分客户（通过看源数据时间）集中在去年双11下单，属价格敏感型客户，在即将到来的618（同为大促，优惠力度大）我们可以尝试对他们进行唤醒。

2、挽回客户（最近未购买，购买频次低，购买金额高）7108人，人数占比27.96%，但支付金额占比最高。也就是说，对店铺销售贡献最高的客户，下单时间远、购买频次低，已经濒于流失边缘。他们和流失客户的区别在于他们平均消费金额较高，一方面，我们可以抽样获取他们的联系方式，进行回访，调查客户沉睡原因；另一方面，筛选出他们购买的产品，结合复购率进行分析，是未到回购周期（上次购买的产品还没用完），还是产品复购率本身就很低，店铺近期拉新乏力，导致挽回客户消费金额占比最高。

3、BLABLABLA..

至此，我们基于订单源数据完成了整个RFM模型的建立。

写这篇文章再+整理资料（源数据，每一步都分成单独SHEET）真的累惨了。。。。

整理不易，觉得有那么一点点帮助的话，点点右下角的好看！

感恩~

附上实战数据链接：

链接：pan.baidu.com/s/1FkI4ko

密码：um8q