客户复购分析

1. 报表总览

1.1. 背景描述

很多产品吸引客户户首次下单购买的策略都是通过让利促销的方式，当发现客户长时间没光顾了，为了重新激活该客户会再次推送优惠券或促销活动让客户回流，可能又会产生第二次购买，但这种方式的促进作用是在呈漏斗式下降的，并且促销也有成本。若要最大化效益，就需要筛选出潜在价值高的客户，有针对性的倾斜资源以及制定相应群体的促销策略。

可以通过RFM 分析和研究客户的复购率来发现其中的规律。RFM 分析可以划分客户群体，评估客户价值和潜力；复购率又称重复购买率，是指客户或签约者对某品牌产品或者服务的重复购买次数，复购率越高，则反应出客户对该品牌的忠诚度就越高，反之则越低。因此不管是做什么产品的，都有一个共同点就是想提高自己客户的复购率。

1.2. 最终实现效果

2. 定制方法

2.1. 数据准备

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

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

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

2.1.3. 新建电子表格

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

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

2.2. 数据处理阶段

数据处理思路如下

基于【数据表】Sheet中客户的6个原始数据，通过excel公式计算派生出很多新的字段，主要通过RFM分析和复购率来研究客户复购。

复购率计算思路如下：

RFM分析思路如下：

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

指标	客户特征	客户分类
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公式	说明
G2	新行业分类	=IF(F4="汽车","企业",IF(F4="零售","企业",IF(F4="地产","企业",IF(F4="物流","企业",IF(F4="教育","政府",F4)))))	基础数据的【行业】划分太细同时业务规模太小，不满足管理层分析需要，故衍生出【新行业分类】的新维度
H2	月份	=MONTH(B2)	取出签约日期中的月份
I2	过渡组	=CONCAT(H2," ",D2) 【该公式仅WPS支持】 =H2&" "&D2 【WPS和Excel都支持】	为了便于分组，使用连字符“&”或公式CONCAT来合并“月份”和“客户编号”字段
J2	月内购买次数	=COUNTIFS(I:I,I2,D:D,D2)	按照“过渡组”分组（同时按照月份和客户编号进行分组），统计每月各客户的交易次数
K2	月内多次购买的人数	=IF(J2>=2,1,0)	对每月多次购买的客户标记 1
L2	购买次数（F）	=COUNTIFS(D:D,D2)	由于每一行数据代表一个订单合同数据，因此可直接统计每个“客户编码”出现次数来作为客户的购买次数
M2	客户平均单价（M）	=AVERAGEIFS(F:F,D:D,D2)	按照“客户编码”分组，求“合同金额”列的组内均值需要设置该列的单元格格式为“数字”，保留两位小数
N2	合同签约距今时间（天）	=DATEDIF(B2,TODAY(),"D")	计算合同的“签约日期”距离当前时间的天数
O2	最近签约距今时间（R）	=MIN(IF((D:D=D2),N:N)) 输入后不要立刻按下回车退出，要同时按下“alt+shift+enter”键才能正确输入数组公式	按照“客户编码”分组，求出每个客户的“签约距离时间”的最小值，即最近一次签约距离时间
P2	R（向量化）	=IF(O2<AVERAGE(O:O),1,0)	将客户特征向量化，将小于总体均值的设为 1，大于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。
Q2	F（向量化）	=IF(L2>AVERAGE(L:L),1,0)	将客户特征向量化，将大于总体均值的设为 1，小于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。
R2	M（向量化）	=IF(M2>AVERAGE(M:M),1,0)	将客户特征向量化，将大于总体均值的设为 1，小于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。
S2	RFM 指标	=CONCAT(P2:R2) 【该公式仅WPS支持】 =P2&Q2&R2 【WPS和Excel都支持】	使用连字符“&”或公式CONCAT将三个 RFM 向量化值拼接起来
T2	RFM	=VLOOKUP(S2,分析表!$J$4:$K$11,2,FALSE)	用VLOOKUP函数到分析表内按事先定义好的分类标准进行划分其中的“分析表!$J$4:$K$11”请以实际地址为准

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

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

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

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. 复购率

从“数据表”中创建一张数据透视表到“分析表”内，从字段列表中选择“月份”到行区；选择字段“客户编码”到值区，重命名该值字段名称为“月购总人数”。

进入“分析”页签下的-“字段、项目”内创建计算字段

插入字段“月内多次购买的人数”

设置“字段1”的值字段汇总方式为“求和”，值字段名称为“月内重复购买的人数”。

在最右边新建一列，手动输入公式计算：复购率=月内重复购买人数/月购总人数。设置单元格格式为“百分比”，不保留小数

2.4. 图表详解

新增“大屏”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%。 ⑤ 选中横坐标轴和纵坐标轴，点击“图表工具-设置格式”，设置图表选项-填充与线条内的 “填充”和“线条”的属性为无。 ⑥ 选中曲线，点击“图表工具-设置格式”。进入“系列选项“-“填充与线条”，设置线条颜色为“RGB（48,210,204）”，设置“标记”内的“数据标记选项”为无； ”设置“系列选项”-“系列”，勾选平滑线。
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,140,237)、(104,141,250)、(36,188,255)、(78,236,246)。