在上一篇文章中，我描述了使用同类群组分析来确定客户群动态的原因。 今天是时候讨论队列分析的数据准备技巧了。
绘制图片很容易，但是要使其在“引擎盖下”正确阅读和正确显示，还需要做很多工作。 在本文中，我们将讨论如何实施同类群组分析。 我将在谈论使用Excel的实现，并在另一篇文章中谈论R。
不管我们是否喜欢，但实际上Excel是一种数据分析工具。 更多“自大”的分析师会认为这是一个薄弱且不方便的工具。 另一方面，实际上，成千上万的人使用Excel进行数据分析，因此，这很容易击败R / python。 当然，当我们谈论高级分析和机器学习时，我们将使用R / python。 而且，我希望大多数分析都是使用这些工具完成的。 但是值得肯定的是，事实是，绝大多数公司都在Excel中处理和显示数据，这是普通分析师，经理和产品所有者使用的工具。 此外，由于流程的简单性和清晰性，Excel很难被击败，因为 您可以动手掌握计算和模型。
因此，我们如何在Excel中进行同类群组分析？ 为了解决此类问题，您需要确定两件事：
在流程开始时我们拥有什么数据
在流程结束时，我们的数据应该是什么样的。
要收集队列分析，我们不仅需要日期和分区的后备数据。 我们需要各个客户级别的数据。 在流程开始时，我们需要：
日历日期
客户编号
客户注册日期
该客户在该日历日期的销量
要克服的第一个困难是获取此数据。 如果您有正确的存储空间，则应该已经拥有它们。 另一方面，如果到目前为止，您仅实现了按日销售总额的数据记录，那么您只有有关“产品”的客户数据。 对于同类群组分析，您将必须实施ETL并将客户端上下文中的数据放入您的存储中，否则您将不会成功。 最重要的是，如果您将“产品”和分析数据分离到不同的数据库中，因为 产品的分析任务和功能任务有不同的目标：争夺资源。 分析师需要针对许多用户的快速汇总和计算，产品需要快速为特定用户提供服务。 我将撰写有关存储组织的另一篇文章。
因此，您有开始数据：
我们需要做的第一件事是将它们转换为“梯子”。 为此，您需要在该表上方构建一个数据透视表，该数据透视表的行-注册日期，列-日历日期，作为值-客户ID数。 如果正确提取了数据，则应该得到这样的三角形/梯形图：
通常，梯形图是我们的同类群组图表，其中每行显示单独的同类群组的动态。 此显示中的时间客户仅在一行内移动。 因此，队列的动态反映了与一段时间内来的一组客户关系的发展。 通常，为了方便起见，而又不损失质量，您可以将队列组合成“行”。 例如，您可以按周和月对它们进行分组。 同样，您也可以对列进行分组。 也许您的产品开发进度不需要详细说明。
在此阶梯的基础上，您可以根据我的文章构建一个图表（我确实指出，我已将几行分组为一行，以便使队列更小）：
这是一个具有累积区域的图形，其中每行是一行，与日期水平。
实现“流程”计划的逻辑稍微复杂一些。 对于线程，我们需要做一些额外的计算。 在线程逻辑中，每个客户端到达不同的状态：
- 新客户-注册日期与日历日期之间相差少于7天的任何客户
- 已重新激活-不再是新客户但在上一个日历月内未产生收入的所有客户
- 有效-任何不是新客户但在当月产生收入的客户
- 离开-任何连续两个月未产生收入的客户
首先，您应该在公司中修复这些定义，以便可以正确实现此逻辑并自动计算状态。 总的来说，这4个定义对营销有深远的影响。 您的吸引，保留和返回策略将基于您认为客户所处的状态。 而且，如果您开始在预测客户退出方面实施机器学习模型，那么定义将成为这些模型成功的基石。 总的来说，我将撰写另一篇有关工作组织和分析方法学重要性的文章。 在上面，我仅给出了这些定义的示例。
在Excel中，您需要创建一个附加列，以在其中输入上述逻辑。 就我们而言，我们必须“出汗”。 我们有两种标准：
- 注册日期和日历日期之间的差额-每行都有此数据，然后您只需要计算一下即可（Excel中的减去日期仅表示天数的差额）
- 当月和上个月的收入数据。 该行无法提供此数据。 此外，考虑到我们的表中不能保证订单的事实，您无法确切地说出该客户在每月其他几天的数据。
有两种方法可以解决两种标准的问题：
- 要求在数据库中执行此操作。 SQL允许使用分析功能为每个客户端计算当月和上个月的收入金额（当月的SUM（收入）OVER（PARTITION BY client_id，calendar_month，然后是LAG以获得上个月的抵消额）：
- 在Excel中，您必须像这样实现它：
- 对于当前月份：SUMMES（），条件将是客户ID和日历日单元格中的月份
- 对于最后一个月：SUMMES（），条件将是客户ID和日历日单元格的月份减去正好1个日历月。 在这种情况下，我要提请您注意必须减去日历月而不是30天这一事实。 否则，由于月份中天数的不均匀，您可能会遇到图像模糊的风险。 还可以使用IF ERROR函数为过去一个月未使用的客户替换错误的值。
通过添加上个月本月的收入列，您可以考虑所有因素（日期差和当月/上个月的收入金额）来构建嵌入式条件IF：
IF（日期差<7；“新”；
IF（AND（AND（上个月的收入= 0；当月的收入> 0））；“重新激活”；
IF（AND（上月收入> 0；当月收入> 0）；“当前”
IF（AND（AND（上个月的收入= 0；当月的收入= 0）；“已离开”；“错误”））））
在这里仅需要“错误”即可控制您在录音中没有记错。 MECE状态标准（ https://en.wikipedia.org/wiki/MECE_principle ）的逻辑，即 如果所有操作都正确完成，那么每个状态都将被固定为4个状态中的一种
您应该这样得到：
现在可以使用数据透视表将该表重建为绘图表。 您需要将其转换为表格：
日历日期（列）
状态（行）
客户端ID的数量（单元格中的值）
接下来，我们只需要基于数据构建条形图，并在X轴上累积日历日期，行是状态，客户数量是列高。 您可以通过更改“选择数据”菜单中的行顺序来更改统计图上的状态顺序。 结果，我们得到以下图片：
现在我们可以开始解释和分析了。