客户复购分析

1. 报表总览

1.1. 背景

1.2. 场景意义

我们通过RFM 分析和研究客户的复购率来为我们

RFM 分析是美国数据库营销研究所提出的一种简单实用客户分析方法，发现客户数据中有三个神奇的要素：

最近一次消费时间（R）：客户距离最近的一次采购时间的间隔。
最近一段时间内消费频次（F）：指客户在限定的期间内所购买的次数。
最近一段时间内消费金额（M）:客户的消费能力，通常以客户单次的平均消费金额作为衡量指标。

这三个要素构成了数据分析最好的指标。RFM 分析就是通过三个关键指标对客户进行观察和分类，判断每类细分用户的价值。针对不同的特征的客户进行相应的营销策略。

研究客户的复购率可以帮我们观察用户的忠诚度。提升复购率，可以提高用户购买的频次。业务的持续增长除了拉新获客的持续输入，因此存量用户的复购尤为重要。

2. 示例

2.1. 数据准备

2.1.1. 进入“数据准备”，新建“数据模型”，导入文件“客户复购数据.xlsx”，如下

保存为“客户复购数据”。

2.1.2. 打开电子表格，登录时勾选“默认报表类型”为“Excel融合分析”

2.1.3. 新建电子表格

修改sheet页的名称为“数据表”，

从数据集面板上，将需要的字段拖拽到相应位置上，如下：

2.2. 数据处理阶段

2.2.1. 新建一个sheet表“分析表”，在表格中创建如下客户分类表格：

指标	客户特征	客户分类
111	重要价值客户	最近消费时间近、消费频次和消费金额都很高（VIP）
101	重要发展客户	最近消费时间较近、消费金额高，但频次不高，忠诚度不高，很有潜力的用户，必须重点发展。
011	重要保持客户	最近消费时间交远，消费金额和频次都很高。
001	重要挽留客户	最近消费时间较远、消费频次不高，但消费金额高的用户，可能是将要流失或者已经要流失的用户，应当基于挽留措施。
110	一般价值客户	最近消费时间近，频率高但消费金额低，需要提高其客单价。
100	一般发展客户	最近消费时间较近、消费金额，频次都不高。
010	一般保持客户	最近消费时间较远、消费频次高，但金额不高。
000	一般挽留客户	都不高。

如下：

2.2.2. 切回sheet表“数据表”下

从G1开始添加新列，“新行业分类、月份、过渡组、月内购买人数、月内多次购买的人数、月内重复购买的人数、购买次数（F）、客户平均单价（M）、消费距离时间（天）、最近一次消费距离时间（R）、R（向量化）、F（向量化）、M（向量化）、RFM、指标、RFM”，表格填充颜色为“#FFC000”，字体颜色为白色。

可以利用excel本身自带的计算公式来对数据进行预处理，新列单元格的计算公式如下：

单元格	列名	Excel公式	说明
G1	新行业分类	=IF(F4="汽车","企业",IF(F4="零售","企业",IF(F4="地产","企业",IF(F4="物流","企业",IF(F4="教育","政府",F4)))))	基础数据的【行业】划分太细同时业务规模太小，不满足管理层分析需要，故衍生出【新行业分类】的新维度
H1	月份	=MONTH(B2)	取出签约日期中的月份
I1	过渡组	=CONCAT(H2," ",D2)	合并“月份”和“客户编号”字段便于分组
J1	月内购买次数	=COUNTIFS(I:I,I2,D:D,D2)	按照“过渡组”分组（同时按照月份和客户编号进行分组），统计每月客户的
K1	月内多次购买的人数	=IF(J2>=2,1,0)
L1	月内重复购买的人数	=SUMIFS(K:K,H:H,H2)
M1	购买次数（F）	=COUNTIFS(D:D,D2)	由于每一行数据代表一个订单合同数据，因此可直接统计每个“客户编码”出现次数来作为客户的购买次数
N1	客户平均单价（M）	=AVERAGEIFS(F:F,D:D,D2)	按照“客户编码”分组，求“合同金额”列的组内均值
O1	消费距离时间（天）	=DATEDIF(B2,TODAY(),"D")	计算合同的“签约日期”距离当前时间的天数
P1	最近一次消费距离时间（R）	=MIN(IF((D:D=D2),O:O)) 输入后不要立刻按下回车退出，要同时按下“alt+shift+enter”键才能正确输入数组公式	按照“客户编码”分组，求出每个客户的“消费距离时间”的最小值，即最近一次消费距离时间
Q1	R（向量化）	=IF(P2<AVERAGE(P:P),1,0)	将客户特征向量化，将小于总体均值的设为 1，大于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。
R1	F（向量化）	=IF(M2>AVERAGE(M:M),1,0)	将客户特征向量化，将大于总体均值的设为 1，小于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。
S1	M（向量化）	=IF(N2>AVERAGE(N:N),1,0)	将客户特征向量化，将大于总体均值的设为 1，小于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。
T1	RFM 指标	=CONCAT(Q2:S2)	使用 CONCATENATE() 函数将 RFM 向量化值拼接起来
U1	RFM	=VLOOKUP(T2,分析表!$J$4:$K$11,2,FALSE)	用VLOOKUP函数到分析表内按事先定义好的分类标准进行划分

2.2.3. 点击 刷新数据，查看报表数据情况。

2.2.4. 点击“公式”内的“名称管理器”，重新定义名称为 ”数据表”。

2.3. 创建数据透视表

选中整张报表，点击excel工具栏内的“插入”-“数据透视表”。

将“数据透视表”选择放在新的工作表上，点击确定保存退出。更改工作表名为“分析表”。

2.3.1. 客户所在的行业数据

在数据透视表的字段列表中拖拽字段“月份”到列区内，拖拽“新行业分类”到行区和值区内。

在空白区域建立一个静态图表，表格内映射总计区域对应的值

从“数据表”中创建一张数据透视表到“分析表”内，从字段列表中选择“新行业分类”到行区，选择“最近购买时间距今天数（R）”、“合同金额（M）”、“购买频次（F）”到值区

2.3.2. RMF数据分析

从“数据表”中创建一张数据透视表到“分析表”内，从字段列表中选择“RFM”到行区，选择“合同金额（M）”、“RFM”到值区

2.3.3. 复购率

从“数据表”中创建一张数据透视表到“分析表”内，接着访问“分析”页签下的-“字段、项目”内的“计算字段”创建计算字段

计算字段内容如下：

接着从字段列表中选择“月份”到行区，选择“复购率”到值区。

3. 图表详解

新增“大屏”Sheet，接着插入图片作为大屏的底图，具体各图表配置如下：

图表名	图表类型	具体操作
客户行业分析	线图	① 点击图形按钮创建图形，创建一个线图 ② 选择“图表工具”-“选择数据” 选择sheet页“分析表”的所需数据区域 ③ 设置样式为“样式2” ④ 添加图表元素-图例，设置显示位置为“上部”。 ⑤ 选中整个图表，然后点击“图表工具-设置格式”。设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。 ⑥ 选中横坐标轴，点击“图表工具-设置格式”，设置字体为微软雅黑白色加粗，设置图表选项-填充与线条内的 “填充”和“线条” 的属性为无； ⑦ 选中网格线，点击“图表工具-设置格式”，设置图表选项-填充与线条内的线条为白色实线，透明度为80%； ⑨ 选中图例，点击“图表工具-设置格式”，设置图表选项-填充与线条内的“填充”为无填充； ⑩ 分别选中代表企业、政府、金融的线条，点击“图表工具-设置格式”，设置线条颜色和标记颜色为 RGB(48，210，204) 、RGB(233，83，39)、RGB(242，159，21)；
客户行业分析	环形图	① 点击图形按钮创建图形，创建一个环形图 ② 选择“图表工具”-“选择数据”，选择sheet页“分析表”的所需数据区域 ③ 选中整个图表，然后点击“图表工具-设置格式”，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。 ④ 选中整个图表，点击右上角的“图表元素”按钮，勾选“数据标签”，单击“更多选项”，勾选标签的显示值为“类别名称”、“百分比”、“显示引导线”。 ⑤ 选中环形，点击“图表工具-设置格式”，设置 “系列”内的圆环图分离程度和内径大小。 ⑥ 依次选中每一个扇区和数据标签，点击“图表工具-设置格式”，设置填充颜色和文本颜色依次为RGB(48，210，204) 、RGB(233，83，39)、RGB(242，159，21)；
月复购趋势	线图	① 点击图形按钮创建图形，创建一个线图； ② 选择“图表工具”-“选择数据”，选择sheet页“分析表”的所需数据区域。 ③ 选中整个图表，然后点击“图表工具-设置格式”，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。 ④ 选中网格线，点击“图表工具-设置格式”，设置图表选项-填充与线条内的线条格式（如图所示）为白色实线，透明度为80%。 ⑤ 选中横坐标轴和纵坐标轴，点击“图表工具-设置格式”，设置字体为微软雅黑白色加粗，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。
MF-R分析	气泡图	① 点击图形按钮创建图形，创建一个气泡图 ② 选择“图表工具”-“选择数据”，选择sheet页“分析表”的所需数据区域 ③ 选中整个图表，然后点击“图表工具-设置格式”，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。 ④ 选中横坐标轴和纵坐标轴，点击“图表工具-设置格式”，设置字体为微软雅黑白色加粗，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。设置横坐标轴标题为“购买频次”，纵坐标轴标题为“合同金额”。 ⑤ 选中整个图表，点击右上角的“图表元素”按钮，勾选“坐标轴”和“轴标题”，显示横、纵坐标轴；勾选“网格线”，只显示“主轴主要水平网格线；勾选“数据标签”，单击”更多选项，设置“标签选项”-“标签”中的标签只包括“x值”，标签位置“靠下”。分别选中代表企业、政府、金融的气泡和数据标签，点击“图表工具-设置格式”，设置气泡颜色和文本颜色为 RGB(48，210，204)、 RGB(233，83，39)、RGB(242，159，21)；
客户价值分类	条形图	① 点击图形按钮创建图形，创建一个条形图； ② 选择“图表工具”-“选择数据”，选择sheet页“分析表”的所需数据区域； ③ 选中整个图表，然后点击“图表工具-设置格式”，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无； ④ 选中整个图表，点击右上角的“图表元素”按钮，勾选“坐标轴”，只显示纵坐标轴；勾选“数据标签”，手动适当调节位置；勾选“网格线”，只显示主轴主要垂直网格线； ⑤ 选中条形，点击“图表工具-设置格式”，设置填充颜色依次为(56,159,215)、(74,101,29)、(83,116,206)、(86,108,205)、(89,96,204)、(90,93,204)、(99,70,201)、(100,66,200)； ⑥ 选中网格线，点击“图表工具-设置格式”，设置图表选项-填充与线条为白色实线，透明度为80%； ⑦ 分别单独选中纵坐标轴和数据标签，点击“图表工具-设置格式”，设置字体为微软雅黑白色加粗，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。
客户价值分类占比	环形图	① 点击图形按钮创建图形，创建一个环形图； ② 选择“图表工具”-“选择数据”，选择sheet页“分析表”的所需数据区域； ③ 选中整个图表，然后点击“图表工具-设置格式”，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无； ④ 选中整个图表，点击右上角的“图表元素”按钮，勾选“数据标签”，单击“更多选项”，勾选标签的显示值为“类别名称”、“值”、“显示引导线”。 ⑤ 选中环形，点击“图表工具-设置格式”，设置 “系列”内的圆环图分离程度和内径大小。 ⑥ 依次选中每一个扇区和数据标签，点击“图表工具-设置格式”，设置填充颜色和文本颜色依次为(239,149,217)、(207,185,152)、(16,168,170)、(253,178,71)、(1,112,192)、(104,141,250)、(36,188,255)、(78,236,246)。