【时序+表计算】高级案例：顾客复购率矩阵分析

注：本案例在《数据可视化分析（第二版）：分析原理与Tableau、SQL实践》（2023.9）一书中，已经完全重写。推荐阅读图书，或者访问：客户留存分析：LOD表达式、表计算的结合 。

今天在客户哪里做了一个销售人员的转化率分析，充分地使用了高级计算的多个函数，非常具有代表性。这里使用超市数据作为模版，迫不及待地与大家分享，并计划加入到明年的新书中。

由于超市的数据没有销售人员数据，因此这里使用“客户名称”，完成客户的复购率分析。完成的问题描述如下：

各个季度的新客户，在接下来第1、第2、第3……第N季度的复购率分别是多少？

——喜乐君

源文件可以从public下载： 复购率矩阵public

1、问题分析

按照此前问题解析的方法，这个问题是典型的高级分析——同时包含了两个层次。何为高级分析，在问题的筛选器、问题层次和聚合度量中，只有有一个位置包含了另一个层次的聚合。高级的高级分析甚至会同时在多个位置引用其他层次的聚合，比如“购物篮分析”。

主视图问题的维度是“各个季度”，聚合度量是“复购率”；聚合度量依赖于另一个层次的聚合，即每个季度有消费的客户数量除以对应「获客季度」新增的所有客户。我们把每个客户的「获客季度」称之为他所在的矩阵。这有点类似于LOD15案例中的第一题“不同客户矩阵的销售”。

本文创建了一个计算字段：first od ，用来计算每个客户的首次订单日期。在视图中这个日期以连续的季度为层次聚合，统称之为“获客季度”。

1.1 单一层次：每个季度的客户数量

在《数据可视化分析：Tableau原理与实践》第十章中，我介绍了“高级分析”的四个步骤，其中入门之基础，就是先做主视图。先不考虑接下来的矩阵，就是如下的图形。

1.2 在主视图基础上，增加客户所在的矩阵

视图的维度是“季度”，我们要引用“每个客户的首次订单所在的季度”（层次是 客户，聚合是min订单日期），很明显二者是独立的层次，因此使用fixed lod计算。之后把这个字段加入视图中，作为颜色，这样就是“客户不同获客季度矩阵，在接下来各个订单日期季度消费的客户数量”。

这样，我们可以说包含了问题分析需要的两个维度（订单日期季度和客户首次订单日期），以及需要的聚合度量（客户数量）。接下来的关键是调整视图并完成计算了。

2、构建基本的矩阵

上面的颜色字段改为连续季度后，明显线条会很多（如下图）。而且由于日期默认连续，颜色也是渐变的。

数据太多，折线反而显得混乱，我们可以尝试使用数字构建的矩阵代替。构建矩阵需要把日期改为离散，可以使用智能推荐调整。于是就有了下面的矩阵分布。

这样，我们就有了数据的雏形了，不过复购率还为时尚早。这里有两个问题：

a、矩阵中仅有每个订单日期季度的消费客户数量，却没有每个“获客季度”（first od）的客户数量总和。缺少这个数据，就无法计算比率；

b、矩阵中的数据，仅仅在每一行上有意义，不同获客季度的客户数量不同，因此相互之间的比较就没有意义，因此需要借助每个获客季度的客户数量总和，计算每个消费日期季度的复购比率（相当于获客季度的确定性样本）。

c、每个获客季度的时间不同，接下来的消费日期季度显然也不同。如何比较2017Q3的获客客户 和 2018年Q2的获客客户，分别在接下来了四个季度的复购率，才是关键。此后横轴的绝对日期轴就需要调整。

这个转变有点难度，我们一点点来。

3、增加更高层次的聚合——每个获客季度的客户数量

上面a的问题，就是要找到每个获客季度的客户数量，这个数据，是可以由不同消费日期季度的客户数量，通过不重复的计数计算获得的。获客季度的客户数量，层次高于当前视图。可以用下图的仪表板来表示。我们需要在左侧的工作表中，增加右侧的聚合“每个获客季度的客户数量”。

高于当前视图的二次聚合，优先表计算。这里相当于以每个获客季度为分区，以订单日期季度为方向，计算客户计数的二次求和，可以用window_sum函数计算，更简单的语法是直接使用total函数。即：

total( countd([客户名称]))

不过，直接在视图中添加这个计算后，由于度量默认聚合，聚合生成坐标轴，因此矩阵中增加了坐标轴。此时需要字段上右键改为“离散”，此时依然不对，因为计算的分区错误。还需要配合设置计算依据为：订单日期季度。

表计算的关键就是：谁和谁计算，对应的字段是方向。

这样，视图中就有了统一的比较依据。

当然，上面的计算，也可以直接通过“分析——合计”拖拽完成，然后把合计放在左侧，而非默认右侧。这里之所以手动完成，是因为后面计算复购率依然要用。而且，所有的参考线，不过都是表计算的化身而已。

这里增加一个简单的步骤，计算复购率，就是每个矩阵中的客户计数，除以对应获客季度的客户计数，即：

countd([客户名称]) / total( countd([客户名称]))

注意，这里的计算依据依然要设置。可以用这个字段替换此前的标签，视图就有了如下的变化。

至此，复购率的计算就完成了。由于这里使用的是每个客户名称的首次订单日期，因此第一个季度自然都是100%。在业务环境中，销售人员的成交通常晚于入职日期，因此首月的转化可以视为客户消费能力和培训效果的指标。

这里最后面临一个问题，虽然可以直观的查看每个获客季度在此后各个季度的复购率，但是无法直观地纵向比较。这就涉及到如何把绝对的日期轴，通过处理更改为相对的日期轴。

4、通过表计算更改坐标轴——公公基准的日期

这个步骤对应TC10的第二个案例，非常经典了。

如何将绝对日期改为相对日期？即相对于每个获客季度的第1、第2、第3……呢？只是日期的次序转化，首先考虑使用Tableau表计算的index函数。

直接在订单日期后输入index()，数值默认生成连续的坐标轴，这里还需要改为离散，如下图。

这里的index是基于所有日期的基准，我们希望仅保留基准index，而不再显示订单日期本身。注意，只是不显示，不能移除，因为订单日期字段是index字段的依据。可以把它拖到左侧标记的“详细信息”中。

此时进入了最重要的一步——如何设置表计算的依据。默认的index只有1，因为它默认采用的相对分区方法（表、区、单元格）无法识别标记中的字段。这里，每个获客日期为独立的分区，index的依据是订单日期——第一个月就是绝对日期的相对坐标而已。因此设置index表计算的计算依据为：订单日期。如下：

这样，就几近完美了。接下来，可以调整一下颜色，比如识别复购率大于20%的矩阵；也可以隐藏第一个季度（受限于这里的方法，第一个季度都是100%，影响了其他复购率的显示）。

这样，我们就可以情绪地看到每个获客季度在此后各个季度的复购率情况（在这个季度有消费即为复购）。通过2017年第一季度的指标可以看出，起初几个季度的复购并不高，但是到了后面几个季度，复购反而提高了，这说明老客户的购买能力比较好。这也和客户的首次矩阵相对应。

上面这个客户分布矩阵，可以说是fixed lod的第一图了。

而这里的复购，其实是融合了这个图形的思路，同时增加了表计算TC第二个案例的很多方法——公公基准。

@喜乐君

Dec 16, 2020 Night 11:40