1.  报表总览

1.1. 概论

很多产品吸引客户户首次下单购买的策略都是通过让利促销的方式，发现客户好久没来了，为了激活客户会再次推送优惠券或促销活动，可能又会产生第二次购买，但这种方式的促进作用是在呈漏斗式下降的，而且促销也有成本。因此如何促进和提升复购率就成为了一个大问题。

1.2. 场景意义

研究客户的复购率可以帮我们观察用户的忠诚度。设法提升复购率，可以提高用户购买的频次。重复购买率简称复购率，指签约者对某品牌产品或者服务的重复购买次数，重复购买率越高，则反应出签约者对品牌的忠诚度就越高，反之则越低。因此不管是做什么产品的，都有一个共同点就是想提高自己客户的复购率。复购率不高并不一定是因为产品问题，因此可以通过RFM 分析和研究客户的复购率来发现其中的规律。

最终实现效果如下：

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”键才能正确输入数组公式

按照“客户编码”分组，求出每个客户的“签约距离时间”的最小值，即最近一次签约距离时间
P2R（向量化）=IF(O2<AVERAGE(O:O),1,0)

将客户特征向量化，将小于总体均值的设为 1，大于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。

Q2F（向量化）=IF(L2>AVERAGE(L:L),1,0)将客户特征向量化，将大于总体均值的设为 1，小于总体均值的设为 0 ，使得 1 都是保持正向特征，0 保持负向特征。

R2M（向量化）=IF(M2>AVERAGE(M:M),1,0)
S2RFM 指标

=CONCAT(P2:R2)   【该公式仅WPS支持】

=P2&Q2&R2         【WPS和Excel都支持】

使用 连字符“&”或公式CONCAT将 三个 RFM 向量化值拼接起来
T2RFM=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”的值字段名称为“月内重复购买的人数”

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

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%。

⑤ 选中横坐标轴和纵坐标轴，点击“图表工具-设置格式”，设置字体为微软雅黑白色加粗，设置图表选项-填充与线条 内的 “填充”和“线条”的属性为无。

⑥ 选中曲线，点击“图表工具-设置格式”。设置“系列选项“-“填充与线条”，设置线条颜色为“中绿宝石色（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)。


本示例是在WPS表格上使用Smartbi的Excel融合分析功能实现

资源下载

资源名说明
客户复购分析数据.xlsx示例数据

报表资源文件
客户复购分析.png图片资源