案例用Excel做简单的RFM分析.docx
- 文档编号:29066686
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:12
- 大小:637.75KB
案例用Excel做简单的RFM分析.docx
《案例用Excel做简单的RFM分析.docx》由会员分享,可在线阅读,更多相关《案例用Excel做简单的RFM分析.docx(12页珍藏版)》请在冰豆网上搜索。
案例用Excel做简单的RFM分析
案例:
用Excel对会员客户交易数据进行RFM分析
作者:
***
乾兆亿(R)数据分析创始人
企业经营数据分析专家
高级咨询顾问、高级企业培训师
背景:
一个会员服务的企业,有近1年约1200个会员客户的收银数据。
由于公司想针对不同类别不活跃客户进行激活促销;同时,为回馈重点客户,也计划推出一系列针对重点客户的优惠活动,希望保留这些客户,维持其活跃度。
因此希望利用该数据进行客户分类研究。
根据客户的需求,RFM模型相对简单并且直接,按照R(Recency-近度)、F(Frequency-频度)和M(Monetary-额度)三个维度进行细分客户群体。
由于该客户的数量较少(约1200个),所以,采用3x3x3=27个魔方(1200/27=44左右)较为合适,虽然平均每类客户数量较少,考虑到集中度分布情况,数量多的分类也能够有200-300左右,适合针对会员客户进行短期的电话、短信营销或者信函营销的数量。
RFM模型原理:
RFM模型是一个简单的根据客户的活跃程度和交易金额贡献所做的分类。
因为操作简单,所以,较为常用。
近度R:
R代表客户最近的活跃时间距离数据采集点的时间距离,R越大,表示客户越久未发生交易,R越小,表示客户越近有交易发生。
R越大则客户越可能会“沉睡”,流失的可能性越大。
在这部分客户中,可能有些优质客户,值得公司通过一定的营销手段进行激活。
频度F:
F代表客户过去某段时间内的活跃频率。
F越大,则表示客户同本公司的交易越频繁,不仅仅给公司带来人气,也带来稳定的现金流,是非常忠诚的客户;F越小,则表示客户不够活跃,且可能是竞争对手的常客。
针对F较小、且消费额较大的客户,需要推出一定的竞争策略,将这批客户从竞争对手中争取过来。
额度M:
表示客户每次消费金额的多少,可以用最近一次消费金额,也可以用过去的平均消费金额,根据分析的目的不同,可以有不同的标识方法。
一般来讲,单次交易金额较大的客户,支付能力强,价格敏感度低,是较为优质的客户,而每次交易金额很小的客户,可能在支付能力和支付意愿上较低。
当然,也不是绝对的。
RFM的分析工具有很多,可以使用SPSS或者SAS进行建模分析,然后深度挖掘。
IBMSPSS还有个Modeler,有专门的RFM挖掘算法供使用。
本文为了普及,介绍使用Excel(2007版)做初步的RFM分析。
操作步骤:
第一步:
数据的清洗
原始数据集:
数据请参考附件Excel(模拟数据.xlsx)。
大家可以下载练习。
该数据集共有26600多条数据,包含记录ID(数据库的primarykey)、客户编号、收银时间、销售金额、销售类型共5个字段
通过简单的筛选,可以看到,在交易金额中有0消费额,有负数消费额,继续查看交易类型,发现为“赠送”和“退货”所造成。
这些数据在本次分析中用不到,所以在数据处理时需要通过筛选除去。
Excel操作:
Ø鼠标点击第一行的行标“1”以选中第一行
Ø菜单栏点击“数据”,快捷按钮栏点选“筛选”
Ø鼠标点击“销售类型”筛选下拉按钮,可以看到所有数据集中有的销售类型
Ø点选“销售金额”字段的小角标也可以看到有负数出现。
第二步:
数据处理
根据分析需要,R用客户最后成交时间跟数据采集点时间的时间差(天数)作为计量标准;F根据数据集中每个会员客户的交易次数作为计量标准(1年的交易次数);M以客户平均的交易额为计量标准。
通过Excel的透视表即可计算以上RFM数据。
Excel操作:
Ø菜单栏点击“插入”
Ø快捷按钮栏点击“透视表”
Ø选择数据区域,确认所有的数据都被选择
Ø选择在“新工作表”中插入数据,然后点击“确定”
Ø将“客户编号”拖入“行标签”栏
Ø将“收银时间”、“记录ID”、“交易金额”拖入数值计算栏
Ø点击“收银时间”数值计算栏按钮,选择“值字段设置”
Ø在“计算类型”中选择“最大值”
Ø在对话框左下角,点击“数字格式”,设定时间格式为:
yyyy-mm-dd,然后“确定”
Ø点击“销售金额”数值计算栏按钮,选择“值字段设置”
Ø在“计算类型”中选择“平均值”,然后“确定”
Ø在“记录ID”数值计算按钮栏,选择“值字段设置”
Ø在“计算类型”中选择“计数”,然后“确定”
Ø在透视表顶部筛选项“销售类型”处,点击下拉按钮小角标,在“选择多项”前的小方框中打勾,然后点掉“退货”和“赠送”前的勾,然后“确定”会得到如下结果。
以上我们得到了:
1)F值:
客户这1年共消费了多少次
2)M值:
客户每次交易的平均消费金额
但是,R值还需要做些处理。
目前R值只得到的是客户最近一次消费日期,需要计算距离数据采集日期的天数。
Excel操作:
Ø鼠标拉选列标签ABCD,选中透视表所在的四列
Ø按ctrl^C(复制),点击“开始”菜单栏下,快捷按钮栏“粘帖”下的小下拉三角标,选择“粘帖值”【或者点“选择性粘帖”,然后选择粘帖值】,用单纯的数据形式覆盖原有透视表。
Ø在C1单元格中输入数据采集日期2010-09-27,格式为yyyy-mm-dd
Ø然后选中C1单元格,复制其中内容
Ø选中B5:
B1204【快捷操作:
点中B5,同时按住Shift^Ctrl后点击向下箭头,松开ctrl键,继续按住shift键,按一次向上箭头,取消数据最后一行的汇总数据】
Ø点击“开始”菜单栏下快捷按钮栏上的“粘帖”按钮下方的下拉箭头,选择“选择性粘帖”,在对话框中勾选“减”,然后“确定”
Ø在不取消目前选择的情况下,选择“开始”菜单栏下快捷按钮栏上的格式化下拉菜单,选择“数字”
因为得到的数据为最后交易日期减去数据采集日期的天数,是负值,所以,还需要处理。
Ø在D1单元格中输入-1
Ø然后ctrl^C复制D1单元格中的值(-1)
Ø然后选中B5:
B1204【快捷操作同上】
Ø“开始”-“粘帖”下拉按钮-“选择性粘帖”-在计算部分选择“乘”,然后点击“确定”
最后得到:
到此,我们得到R,F,M针对每个客户编号的值
第三步:
数据分析
R-score,F-score,M-score的值,为了对客户根据R,F,M进行三等分,我们需要计算数据的极差(最大值和最小值的差),通过对比R(或者F,M)值和极差三等分距,来确定R(或者F,M)的R-score,F-score,M-score。
所以先计算R、F、M的最大值、最小值、极差三等分距
Excel操作:
ØF1到H1代表R\F\M的最大值,利用公式“=max(B5:
B1204)”计算,(计算F时B换成C,M时B换成D即可)
ØF2到H2代表R\F\M的最小值,利用公式“=min(B5:
B1204)”计算(计算F时B换成C,M时B换成D即可)
ØF3到H3代表R\F\M的极差三等分距,利用公式“=(F1-F2)/3”计算(计算F时F换成G,M时F换成H即可)
Ø【以上快捷操作可用,先输入F1,F2,F3单元格里的公式,选择F1:
F3三个单元格,然后拉动右下角的黑色小十字叉,向右拖动复制F列公式到G和H列即可】
R-score的计算公式为:
ØE5单元格内输入:
“=IF(ROUNDUP((B5-$F$2)/$F$3,0)=0,1,ROUNDUP((B5-$F$2)/$F$3,0))”
Ø之所以使用IF判断函数,主要是考虑到当R值为最小值时,roundup(B5,0)为0,用if函数判断如果为0,则强制为1。
Ø之所以用$F$2锁定引用的单元格,是为了后续的公式复制,最小值和极差三等分距不会发生相对引用而变化位置【锁定引用单元格除了手工添加$符号外,快捷方式是选中引用的单元格按F4快捷键,此处都比较麻烦,手工输入$符号还快些】
Ø【另外一种简单的处理方式就是直接用公式“=ROUNDUP((B5-$F$2)/$F$3,0)”,然后用ctrl^H快捷操作,将0值替换成1即可,这个替换需要将公式复制-快捷粘帖为数值后进行】
F-score和M-score如法炮制。
ØF5单元格公式为:
=IF(ROUNDUP((C5-$G$2)/$G$3,0)=0,1,ROUNDUP((C5-$G$2)/$G$3,0))
ØG5单元格公式为:
=IF(ROUNDUP((D5-$H$2)/$H$3,0)=0,1,ROUNDUP((D5-$H$2)/$H$3,0))
RFM-score的计算,利用分别乘以100-10-1然后相加的方式,让R、F、M分别为一个三位数字的三个百分位、十分位和个位表达,该三位数的三个位代表了3x3x3=27魔方三个维度上的坐标。
ØH5单元格的公式为:
=E5*100+F5*10+G5
Ø选中E4到H4区域,双击右下角小黑色十字叉,复制E4到H4公式到所有客户数据中
Ø得到结果如下:
接下来的步骤就是统计各个魔方上的客户数量
再次利用透视表形成统计结果
Excel操作:
Ø“插入”菜单栏下快捷按钮栏按“透视表”,在数据表区域中选择A4:
H1204【确认这个选择,自动跳出来的区域要改一下的哦】,然后点击“确定”
Ø将RFM-Score拖入“行标签”中,将“客户编号”拖入“数值计算”栏中,点击“数值计算”栏中的“客户编号”项,选择“字段数值设置”,选择计算方法为“计数”,得到处理结果如下:
第四步:
数据分析结果解读和可视化
得到这个分析结果,利用Excel的条件格式功能可以对得到的数据分析结果做简单的视觉化。
Excel操作:
Ø将透视表中B列拉宽(如上图)
Ø选中B5:
B22列
Ø“开始”菜单栏下快捷按钮栏点击“条件格式”下拉菜单中选择“数据条”,然后选择一个颜色即可
通过条形图的视觉化,可以直观地对比哪类客户数量较多。
第五步:
数据分析结果的商业解读(略)
因为此数据为模拟数据,且数据分析过程中有许多需要商业活动参与执行者参与的过程。
此案例仅仅是一种操作的演示。
具体的分析结果解读,读者可以自行参考。
有想要深度讨论的,可以加我QQ(5316637,请注明:
数据分析探讨)或者加入企业经营数据分析专业交流群(群 号:
109742944)进行交流。
祝大家生活愉快!
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 案例 Excel 简单 RFM 分析
![提示](https://static.bdocx.com/images/bang_tan.gif)