您当前的位置:首页 > 电脑百科 > 软件技术 > office

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

时间:2020-09-24 09:57:26  来源:  作者:

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

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

 

下面我们就来看一下重新升级后的RFM模型!
基础概念

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

 

还是一样,先普及一下基础概念。
Q1:RMF模型是什么?
RFM模型是衡量客户价值和客户创利能力的重要工具和手段。在众多的客户关系管理(CRM)的分析模式中,RFM模型是被广泛提到的。该机械模型通过一个客户的近期购买行为购买的总体频率以及花了多少钱3项指标来描述该客户的价值状况。
RFM其实是三个需要分析指标的首字母
指标
英文名称
说明
最近一次消费间隔Recency越短越好(反向)
消费次数(频率)
Frequency越大越好(正向)
消费金额
Monetary越大越好(正向)上面除了R的效果是和数值相反了,其他都是正相关!
Q2:如何对分析对象进行分类
我们根据三项指标是高还是低,把分析对象划分为8类:

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

 

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

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

 


一般分四步处理:

  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行预览:

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

 

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

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

 

计算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)

 

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

 

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

打分标准的确定


每个公司对三项指标的评分标准不同,具体要看企业实际业务要求,比如间隔1-3天的 得1分,4-10得2分等等,一般根据实际来测算处理。
本次案例中,我们使用的是5分制,按均值等分处理!具体划分如下:
除了R是越大分数越低(方向),距离最后一次消耗时间越短越是优质客户,显然分数越高,也就是越小分数越高。其他都是正向变动!
这里特别感谢泠雨浅唱同学的提醒,本次我们得分分开,也更加方便维护

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

 

这里需要解释一下RFM分段的确定。先计算各指标划分为5等份的均值均值:=(最大值-最小值)/5然后我们依次使用最小值,加上对应的均值
R分段举例如下:S2公式:获取最近消耗间隔的最小值作为起始

  •  
=MIN(G:G)


S3公式:最小值,依次加上均值的数倍

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

 

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

 


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)

 

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

 

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

判断高低


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

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

 

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

 

R状态判断如下:

  •  
=IF(J2>AVERAGE(J:J),1,0)
RFM模型升级,使用Excel完成全自动分析

 

F状态和M状态同理,详见文件!
我们把三列拼接到一起,方便我们引用数据!

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

 

用户分类

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

 


前面我们已经完成了全部的基础工作,下面只要通过客户标识(分类)
再从分类表中引用即可完成分类工作
客户分类公式:

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

 

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

 


可视化呈现

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

 


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

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

 

我们主要是获取他的配色方案,自己适当加上需要的图表和指标数据即可!
由于我们模拟的都是随机数据,下面我们就使用动画给大家演示一下,动态效果!
动画演示:动态更新数据!

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

 

小结

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

 


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



Tags:RFM模型   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
让我们先来预览一下可视化部分效果:数据看板(模板文末下载) 下面我们就来看一下重新升级后的RFM模型! 基础概念 还是一样,先普及一下基础概念。 Q1:RMF模型是什么? RFM模型是衡量客...【详细内容】
2020-09-24  Tags: RFM模型  点击:(451)  评论:(0)  加入收藏
▌简易百科推荐
Excel常用电子表格公式大全   一、Excel基本公式   1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。   2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"200...【详细内容】
2021-12-21  楠方儿i    Tags:Excel   点击:(11)  评论:(0)  加入收藏
最近这段时间,我们三易生活收到了一些来自读者朋友的求助,在他们选购电脑时发现,有的设备出厂预装了“Microsoft 365”,而另一些则预装的是“Office 2021”。对于这两款同为微软...【详细内容】
2021-12-14  三易生活    Tags:Office   点击:(26)  评论:(0)  加入收藏
我们在做问卷调差或者填写一些资料表的时候,会遇到一些word文档中有小方框【□】,需要在里面打钩【√】,那么是如何操作呢,今天和大家分享一下。方法一1,打开我们需要操作...【详细内容】
2021-11-26  小七哆来咪发唆    Tags:word   点击:(36)  评论:(0)  加入收藏
试想一下,用 Excel 管理项目的时候,会有很严格的日期安排,而且项目中的各细目经常是并行作业的,这就意味着日期不一定是排序的 。 那么事项太多如何更好管理,而不至于遗忘关键节...【详细内容】
2021-11-16  Excel学习世界    Tags:Excel   点击:(22)  评论:(0)  加入收藏
与大家分享一下有关制作身份证电子版时四周圆角处理的具体方法。方法/步骤首先,我们利用WPS打开相应的身份证扫描件,选中图片,点击“裁剪图片”按钮。 此时将显示“裁剪方式”...【详细内容】
2021-11-09  数字传媒微课堂    Tags:身份证   点击:(196)  评论:(0)  加入收藏
WPS是我们的常用办公软件之一,很多人在使用WPS打印功能的时候,经常会遇到多种多样的打印小问题,今天就为大家简单讲解下打印面板的各个小功能的作用。打印文档方法: 在左上角“W...【详细内容】
2021-11-02    21世纪教育网  Tags:WPS   点击:(31)  评论:(0)  加入收藏
前言:说起办公,就会想到三大办公软件,Word、Excel、PPT。这些软件的使用是有技巧的,学会使用技巧工作效率就会提高,加班自然就能避免。 今天就来分享其中之一:Word办公的7个小技巧...【详细内容】
2021-11-02  小杰好厉害呀    Tags:Word   点击:(52)  评论:(0)  加入收藏
我们使用 Word 的时候,页面上通常会有一些默认的符号,平时大家司空见惯了,可能没有多加留意,更不知道如何去除这些符号。 今天教大家两个 Word 技巧,将 Word 中默认的一些标记符...【详细内容】
2021-11-01  Excel学习世界    Tags:Word   点击:(48)  评论:(0)  加入收藏
经常用 Excel 的表哥表姐们,想必都知道「下拉菜单」这个神器,鼠标点点点,就能轻轻录入数据:▲ 一级下拉菜单 它的制作方法也很简单,用【数据验证】功能可以直接实现! 有小伙伴表示...【详细内容】
2021-10-27  秋叶Excel    Tags:Excel   点击:(41)  评论:(0)  加入收藏
在工作中, 我们需要对业务人员的业绩进行跟进,会有如下格式的跟进表 每天要进行更新表格的时候,表头都要重新手动的进行输入,比较麻烦,今天教大家的技巧是使用公式进行自动更新首...【详细内容】
2021-10-26  Excel自学成才    Tags:   点击:(41)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条