如何用Excel2010进行RFM分析 1

1、如何用excel2010进行rfm分析背景：一个会员服务的企业，有近1年约1200个会员客户的收银数据。由于公司想针对不同类别不活跃客户进行激活促销；同时，为回馈重点客户，也计划推出一系列针对重点客户的优惠活动，希望保留这些客户，维持其活跃度。因此希望利用该数据进行客户分类研究。根据客户的需求，rfm模型相对简单并且直接，按照r(recency-近度）、f(frequency-频度）和m（monetary-额度）三个维度进行细分客户群体。由于该客户的数量较少（约1200个），所以，采用3x3x3=27个魔方（1200/27=44左右）较为合适，虽然平均每类客户数量较少，考虑到集中度分布情况，数

2、量多的分类也能够有200-300左右，适合针对会员客户进行短期的电话、短信营销或者信函营销的数量。rfm模型原理：rfm模型是一个简单的根据客户的活跃程度和交易金额贡献所做的分类。因为操作简单，所以，较为常用。近度r：r代表客户最近的活跃时间距离数据采集点的时间距离，r越大，表示客户越久未发生交易，r越小，表示客户越近有交易发生。r越大则客户越可能会“沉睡”，流失的可能性越大。在这部分客户中，可能有些优质客户，值得公司通过一定的营销手段进行激活。频度f：f代表客户过去某段时间内的活跃频率。f越大，则表示客户同本公司的交易越频繁，不仅仅给公司带来人气，也带来稳定的现金流，是非常忠诚的客户；f越小

3、，则表示客户不够活跃，且可能是竞争对手的常客。针对f较小、且消费额较大的客户，需要推出一定的竞争策略，将这批客户从竞争对手中争取过来。额度m：表示客户每次消费金额的多少，可以用最近一次消费金额，也可以用过去的平均消费金额，根据分析的目的不同，可以有不同的标识方法。一般来讲，单次交易金额较大的客户，支付能力强，价格敏感度低，是较为优质的客户，而每次交易金额很小的客户，可能在支付能力和支付意愿上较低。当然，也不是绝对的。rfm的分析工具有很多，可以使用spss或者sas进行建模分析，然后深度挖掘。ibm spss还有个modeler，有专门的rfm挖掘算法供使用。本文为了普及，介绍使用e

4、xcel（2010版）做初步的rfm分析。操作步骤：第一步：数据的清洗原始数据集：数据请参考附件excel（模拟数据.xlsx）。大家可以下载练习。该数据集共有26600多条数据，包含记录id（数据库的primarykey）、客户编号、收银时间、销售金额、销售类型共5个字段。excel操作：（1）鼠标点击第一行的行标“1”以选中第一行。（2）菜单栏点击“数据”，快捷按钮栏点选“筛选”。即可得到如下图所示的excel表格形式，基本上来说稍微会用excel的人都会知道筛选如何操作。通过筛选可以看出“销售类型”这一栏有四种类型，其中“赠送”和“退货”是我们不需要的数据。第二步：数据处理根据分析需要，

5、r用客户最后成交时间跟数据采集点时间的时间差（天数）作为计量标准；f根据数据集中每个会员客户的交易次数作为计量标准（1年的交易次数）；m以客户平均的交易额为计量标准。通过excel的透视表即可计算以上rfm数据。excel操作：（1）鼠标点击excel表中有数据的任意栏，ctrl+a将数据全部选中。（2）菜单栏点击“插入”，快捷按钮栏点击“数据透视表”。会出现如下图所示的内容。（3）点击“确定”，即可新建一个新的透视表（如下图所示）。（4）将“客户编号”拖入“行标签”栏；（5）将“收银时间”、“记录id”、“销售金额”拖入数值计算栏；（6）将“销售类型”拖入“报表筛选”栏；（6）点击“收银时间

6、”右击鼠标，选择“值字段设置”在“计算类型”中选择“最大值”；（7）将b栏全部选中，右击鼠标，“区域设置”选择“英语（英国）”设定时间格式为：yyyy-mm-dd，然后“确定”（如下图所示）。（8）点击“销售金额”，右击鼠标，点击“数值计算栏”按钮，选择“值字段设置”，在“计算类型”中选择“平均值”，然后“确定”；同理：鼠标右击“记录id”选择“值字段设置”，在“计算类型”中选择“计数；”（9）在透视表顶部筛选项“销售类型”处，点击下拉按钮小角标，在“选择多项”前的小方框中打勾，然后在“特价”和“正常”前的勾，选择“确定”会得到如下结果。以上我们得到了： 1）f值：客户这1年共消费了

7、多少次； 2）m值：客户每次交易的平均消费金额；但是，r值还需要做些处理。目前r值只得到的是客户最近一次消费日期，需要计算距离数据采集日期的天数。excel操作：1）将abcd四列全部选中后，ctrl+c（复制），点击“开始”菜单栏下，快捷按钮栏“粘帖”下的小下拉三角标， 选择“值”【或者点“选择性粘帖”，然后选择“数值”】，用单纯的数据形式覆盖原有透视表。2）在c1单元格中输入数据采集日期2010-09-27，格式为yyyy-mm-dd 然后选中c1单元格，复制其中内容。3）选中b5：b1204【快捷操作：点中b5,同时按住shiftctrl后点击向下箭头，松开ctrl键

8、， 继续按住shift键，按一次向上箭头，取消数据最后一行的汇总数据】。4）点击“开始”菜单栏下快捷按钮栏上的“粘帖”按钮下方的下拉箭头，选择“选择性粘帖”，在对话框“粘贴”中勾选“数值”，在“运算”中勾选“减”，然后“确定”，如图所示：。5）操作结束后，会得到如下图所示的excel：6）在不取消目前选择的情况下，在“开始”菜单栏选择“数值”，如下图所示：7）得到如下图所示的excel：8）在d1栏输入-1，复制d1栏，选中b5：b1204【快捷操作：点中b5,同时按住shiftctrl后点击向下箭头，松开ctrl键， 继续按住shift键，按一次向上箭头，取消数据最后一行的汇总数据】。9）点

9、击“开始”菜单栏下快捷按钮栏上的“粘帖”按钮下方的下拉箭头，选择“选择性粘帖”，在对话框“粘贴”中勾选“数值”，在“运算”中勾选“乘”，然后“确定”，如图所示：。最后得到：到此，我们得到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操作：1

10、）在3行的上方插入新的一行，对应的表格内输入如下图所示的文字：2）f1到h1代表rfm的最大值，利用公式“=max(b5:b1204)”计算,（计算f时b换成 c，m时b换成d即可）；3）f2到h2代表rfm的最小值，利用公式“=min(b5:b1204)”计算（计算f时b换成c， m时b换成d即可）；4）f3到h3代表rfm的极差三等分距，利用公式“=(f1-f2)/3”计算（计算f时f换成g， m时f换成h即可）【以上快捷操作可用，先输入f1,f2,f3单元格里的公式，选择f1：f3三个单元格，然后拉动右下角的黑色小十字叉，向右拖动复制f列公式到g和h列即可】5）r-score的计算公式为

11、：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)”，然后用 ctr

12、lh快捷操作，将0值替换成1即可，这个替换需要将公式复制-快捷粘帖为数值后进行】6）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魔方三个维度上的坐标。7）h5单元格的公式为：“=e5*100+f5*10+g5 ”8）选中e4到h4区域，双击右下角小黑色十字叉，复制e4到h4公式到所有客户数据中。9）得到结果如下：接下来的步骤就是统计各个魔方上的客户数量。再次利用透视表形成统计结果。excel操作：1）“插入”菜单栏下快捷按钮栏按“透视表”，在数据表区域中选择a4：h1204【确认这 个选择，自动跳出来的区域要改一下的哦】，然后点击“确定”。如下图：2）将rfm-score拖入“行标签”中，将 “标签栏”（客户编号）拖入“数值计算”栏中，点击“数值计算”栏中的“标签栏”项，选择“字段数值设置”，选择