史上最全详细步骤！教你如何快速用excel制作会员rfm分析模型！——九数云BI

1. 会员rfm分析：数据源准备

在开始之前我们需要准备好分析用的源始数据 。

我们能够方便获得的是店铺在一定时间内的交易数据，包含：用户名、订单编号、订单金额、订单时间等字段的数据。我们利用交易数据可以提取到我们需要的RFM数据，利用简单的IF函数和数据透视表就可以实现了。

因为Excel表格太大拖不动的缘故，我选择了网站2018年-2020年，2年的交易数据，对他们进行处理后得到如下数据：

对会员ID做了模糊化处理，一共得到19万条的用户数据，分别知道每一个会员的RFM对应的值是多少。

 

2. 会员rfm分析：数据计算方法

在我们知道每一名用户对应的R/F/M值以后，我们需要知道RFM三个数值分别是多少才算是高 or 低？

1）会员rfm分析方法一：四分位数法

① 四分位数理论

四分位数是统计学分位数中的一种，把所有数值从低到高（或者从高到底）排列并分成四等份，处于三个分割点位置的数值就是四分位数。

一般表示为：

• Q1：样本排列中处于25%位置的数字；
• Q2：又称为中位数，指的是样本排列中处于50%位置，即中间位置的数据；
• Q3：样本排列中处于75%位置的数字。

假设样本数据项数一共是N：

• 则Q1的位置数值=（N+1）/4；
• Q2的位置数值=（N+1)/2；
• Q3的位置数值=3（N+1)/4。

如果（N+1）恰好是4的倍数，则确定四分位数比较简单，如果不是4的倍数，相关位置的四分位数就应该是相邻两个数值的标志值的平均数。

权数的大小取决于两个数值距离的远近，距离越近权数越大，距离越远，权数越小，权数之和等于1。

例如一组样本数据如下所示：

N=10，则Q1的位置=11/4=2.75（12和196两个数值）；Q2的位置=5.5；Q3的位置=8.25。

则有：

• Q1=0.25*第2项+0.75*第3项=0.25*12+0.75*196=150；
• Q2=0.5*第5项+0.5*第6项=0.5*204+0.5*211=207.5；
• Q3=0.75*第8项+0.25*第9项=0.75*234+0.25*261=240.75。

在对应到RFM值的时候，我们可以定义为50%位置以下的数据值为【低】，即小于207.5的数值为低，50%位置以上的数据值为【高】，即大于207.5的数值为高。

也可以定义为75%位置以下的数据值为【低】，即小于240.75的数值为低，75%位置以上的数据值为【高】，即大于240.75的数值为高。

笔者的一般做法是：这两种划分位置都算一下，最后看结果，根据二八原则来确定选择哪一种高低分类更合理。

② 四分位数的应用

笔者直接按照“75%位置以下的数据值算低，75%以上的数据值算高”的方式来分别计算RFM三个数值的高低。

先算【R】值，因为R值越小越好，所以R值我们这边先按【倒序】排列，如此可得：

• N=1096，Q3的位置=3（N+1)/4=822.75；
• Q3=0.75*第822项+0.25*第823项=0.75*274+0.25*273=273.75。

所以，R值≥273.75的值定性为低，R值＜273.75的值定性为高，即距离统计时间越短的用户，价值越高。

计算【F】值：

• N=158，Q3的位置=3（N+1)/4=119.25；
• Q3=0.75*第119项+0.25*第120项=0.75*129+0.25*131=129.5。

所以，F值≥129.5的值定性为高，F值＜129.5的值定性为低。

计算【M】值：

N=26387，Q3的位置=3（N+1)/4=19791，恰好是4的倍数，故Q3=第19791项=2018。所以M值≥2018的值定性为高，M值＜2018的值定性为低。

三个数值都计算好之后，整理如下：

如此，我们通过简单的IF函数就可以计算出每一名用户R值的属性了，如下图所示：

按照相同的方式为F值和M值作出高低定义，结果如下：

然后我们根据如下表格，对所有的用户进行分层：

就可以得到各个用户的标签了，如下图所示：

2）会员rfm分析方法二：评分法

评分法就是先将RFM的价值按照从低到高划分为1-5分，然后对每一个RFM的值进行打分，这样每一个RFM值都在1-5分之间，然后分再分别算出RFM三个数值各自的平均值，高于平均值的定性为“高”，反之定性为“低”。

我们用同样的一组数据来实操给大家看一下。

我们先给RFM三个数值进行打分，结合业务的具体特点，笔者做出如下定义：

有人会问了，我怎么知道小于90天的就是5分呢？

会员rfm分析三种方式：

1. 经验法，根据自己对业务所积累的经验来进行划分，当然也可以参考行业的常规定义；
2. 将数据进行5等分，分成5个区间块，每一个区间范围分别从高到低进行打分；
3. 有点复杂，举例来说：要算F值的划分，可以利用数据透视知道不同的购买次数对应的会员人数是多少？然后对购买次数进行分组，看会员数的占比情况，不断调整分组步长来进行划分。也就是说统计历史数据，看F值对应的会员人数占比分别是多少（F=2的在历史数据中的会员人数占比）？掌握F的数据分布情况后再进行打分。

根据上表，我们对RFM三个数值分别进行打分，同样用的是IF公式，我们将会得到如下表格：

然后分别算出R值的平均值是1.78，F值的平均值是1.41，M值的平均值是1.96。

如果某一用户的R值大于整列的平均值，则定性为“高”，如果低于平均值，则定性为“低”，如下图所示：

之后的步骤就同方式一所介绍的一样了，根据RFM值高低的不同，将所有的用户分成8个类型即可。

这里介绍的两种方法是只要通过EXCEL的形式就可以计算出来的，针对规模较小，或者是没有数据分析部的公司。

当然你也可以选择其他软件优化rfm分析模型，比如九数云，它支持百万行数据实时处理实时预览，提供比Excel更强大、比SQL查询更简便的数据处理方式，拖拽式的图表和看板制作方式，能够帮助用户快速提高数据大局观，优化业务流程并提升效率。

 

上一篇: 手把手教你如何使用会员rfm进行用户分群！不来看看吗？——九数云
下一篇: erp数据管理三原则：让你轻松科学化管理数据！——九数云BI