业务5:医药行业的药品覆盖分析数据准备（下）

备注：本文旨在从超市的业务数据，借助于Prep完成“业务4”一文的切片式、周期快照式的数据结构。部分分析计算并非必须使用这样的结构。本文对本文内容不甚满意，后期修改。Sep 5, 2021

上期写了业务4:医药行业的药品覆盖分析（上），重点介绍了业务层面的分析意义、指标过程，并解释了标准的数据表的数据结构。

简而言之，覆盖率旨在分析药品在终端的销售触达质量，并以此对终端做时间或质量上的价值分类，比如新增终端/流失终端/高潜终端/成长终端等。这个逻辑上的分析指标，对应的是药品在不同时间节点、不同终端的销售数据。因此，标准的数据表需要包含产品类别、时间节点、终端及相关的度量字段（如进货数量、金额等）。

以笔者的经验，这个分析的关键在于数据准备，一方面把业务明细的交易聚合到恰当的层次（聚合度既不能太高减少了分析维度，又不能太低增加了计算负担），另一方面把必要的分析过程转化为数据表字段从而简化复杂的计算逻辑（比如在明细行中增加上一期金额和期初金额等，从而更快捷地判断迁徙变化）。

本文讲深入讲解这个过程。

一、从常见的excel聚合表到标准的数据结构表

笔者这里是某家医药公司的脱敏数据结构，数据表中包含了药品分类、终端分类、地区分类、业务员分类、日期等关键字段。根据这个可以总结它对应的业务场景：

业务员who、在何时when、于何地where、向哪个终端门店whom、提供了何种产品what，对应的交易金额、数量how much。

1、不当的数据结构和推荐的数据样式

标准的关系数据结构是一维表，随着业务增加纵向延伸，相同的数据都构成单一的字段列。不过，有一种典型的错误是，把日期分为多列向右侧延伸，这样一维的数据表就变成了两维的数据表，当新的业务数据增加时，不仅要考虑纵向增加新的业务员、终端门店等，还要向右侧增加日期。这样，虽然能简化短期的一些计算，但却是自动化的牢笼。这种数据通常是如下的样式：

业务用户时常创建类似的中间表，包含了层次聚合（这里是从明细的交易聚合到了工号、药品种类和月份），又包含了结构化的调整（转置）。特别是，包含了维度转置列的数据，通常只能满足特定的分析需求，就像上述的数据无法完成各月份的折线图趋势一样。

在这里，喜乐君使用Tableau Prep Builder，仅保留业务中的关键字段，然后重新转置回标准的关系数据样式。就构成了覆盖率标准数据结构的雏形。

「数据是业务的映射」，数据表是业务交易场景或者业务逻辑问题的反映。这里的标准数据，就和之前的业务场景完美对应了。

注意，在这里删除了元数据中的很多“分析字段”，比如“2年覆盖”“覆盖家数”等。按照喜乐君《业务可视化分析》“第三字段分类”的介绍，这些字段属于分析字段，而非业务字段，它们不应该存在于底层业务数据表中，而应该存在于分析过程；就像利润率不应该出现在数据明细中一样。

锁定家数

2年覆盖

覆盖家数

6个月判断覆盖家数

未开户家数

当月新开家数

当月新开金额

当月流失家数

当年流失家数

当季度新开家数

当季度新开金额

当年新开家数

当年新开金额

休眠终端(6个月)

休眠终端(3个月)

当月金额

当月级别

滚动月均金额

滚动级别

2、基于业务分析的数据优化

当然，建立在上述数据表基础上，要计算“休眠终端（三个月）”或者“流失终端”，就可以使用前文业务4:医药行业的药品覆盖分析（上）的if方法完成（前提是这里的月份是完整的）。有时候，为了优化后续的分析过程，可以适当地把部分逻辑判断过程，在数据准备的阶段预先完成，注意，这里只应该是逻辑判断，完全不能包含聚合。比如：

过去三个月休眠终端：需要同时满足两个条件，进货金额或数量为0、过去三个月同时前述条件。业务分析师经常迷路于分析世界，关键的原因是难以把业务语言转化为计算机语言。考虑到所有的筛选都是逻辑上的判断（更准确的说是1和0，true和false），可以通过以下判断完成：

if datediff(‘month’,[日期年月], today() ) <=2 and [销售金额]=0 then [终端代码] else null end

在《数据可视化分析》第八章，喜乐君把计算分为行级别计算和聚合计算，行级别计算包含字符串计算、日期计算和判断条件为行级别的逻辑计算。这里的计算就是在行级别完成的，满足日期年月和today今天比较三个月内，同时对应的销售金额为0，则返回终端代码。

为什么是返回终端代码，而不是喜乐君经常写的返回1呢？这要考虑行级别的数据级别，避免重复带来的重复聚合。

不过，这里只是为了讲解原理。考虑到观察日（比如这里的today）可能在分析过程中调整，喜乐君建议所有基于分析的逻辑过程都应该在分析层面（逻辑层）完成，确保分析过程的敏捷、高效。

当然，业务用户有时候会反复使用一些计算，数据准备人员（通常是IT）也不妨把辅助性的行级别计算在明细中适当处理。比如，这里喜乐君就建议在数据明细中增加“上个月销售额”一列，基于这一列，业务人员无需if计算，就能轻松完成“上月流失、本月覆盖”的终端家数。IT人员可能需要SQL的join连接，喜乐君却只能通过prep Builder来完成了。如下所示：

通过日期的错位连接join，可以轻松获得上个月对应的度量。这样就为后续的计算提供了极大的简化空间。

当然，连接时务必确认连接join的层次是一致的，并且以主表为左侧取左连接。

到这里，本文算是告一段落，也是接下来的入门。在业务分析中，困难来自于另一个方面：销售明细中，不会记录当月没有销售的信息。上述通过转置，短期内跳过了这个问题，接下来，我们必须正面应对，讲解如何从明细数据产出标准数据的方法。

二、数据表样式：基于交易数据到药品覆盖中间表

为了方便大家练习，这里使用Tableau自带的超市数据，模拟药品数据。超市数据可以视为是典型的批发场景，客户是区域性的商贸个体户（对应客户id），它们从超市集中采购，然后分发到最终用户手中。这里订单日期保留到2021年8月，仅保留关键字段解释逻辑。

1、事实表——与业务场景相对应的数据表

之前讲，数据都是业务的记录，数据表既是业务的反映，其实也是分析的反映。数据准备的关键是区分业务场景和分析的不同，针对性的准备差异化的数据。

在超市数据中，最具体的一笔业务，就是某个业务员在某时某分、于某地、给某个客户提供了具体某些产品。不可再细分的最低聚合度的数据明细，其实就是最为具体的业务的反映。在数据仓库中，通常把它们称之为“事实表”Fact table。

在《数据仓库工具箱》the Data Warehouse Toolkit一书中，作者Ralph Kimball和Margy Ross有如是写到：

A fact table contains the numeric measures produced by an operational measurement event in the real world. At the lowest grain, a fact table row corresponds to a measurement event and vice versa.Thus the fundamental design of a fact table is entirely based on a physical activity and is not influenced by the eventual reports that may be produced. (PP41-42)

__the Data Warehouse Toolkit

看到这本书之后，我才升华了自己基于业务实践的想法。正如上述所言，“从最低的数据粒度而言，事实表的数据行对应业务交易，反之亦然。因此事实表设计完全建立在真实的业务活动之上，而不受它可能产生的业务分析所影响。”

在喜乐君《业务可视化分析》中，首次提出了“第三字段分类”的概念，其实就是基于这里的框架。行级别字段完全来自于业务过程，因此又称之为“业务字段”，而聚合的字段则与数据分析相对应，因此又称之为“分析字段”。

具体到覆盖率分析中，“三个月覆盖数量”“上月购买本月流失的客户比例”等都是直接对应业务分析问题的，它们需要从数据事实表中聚合而来。为了提高分析的效率，就有了建立在事实表之上、融合业务分析需求的中间聚合表，又可以称之为“聚合事实表”Aggregate Fact Tables，如果是以多维度方式存放，就是OLAP cubes。

聚合事实表通常兼顾了真实的业务实践，与分析需求之间的平衡。

2、聚合事实表——指定分析场景的数据聚合

为了分析不同产品线在不同单一时间或时间区间的客户覆盖情况，并分析客户流失、新增、随时间的迁徙变化等，有必要先把“订单ID*产品ID”的交易明细聚合到较高的层次——这里选择“每个客户*每个月份*类别”。读者可以根据自己的分析选择合适的聚合层次。

在prep builder中，通过增加聚合节点，可以轻松实现特定层次的聚合。如下所示：

此类的聚合中间表是分析中最重要的数据表形式，也是数据仓库的关键。很多分析都可以直接使用“聚合中间表”完成，借助于tableau LOD功能，无需复杂的周期快照数据表，比如每月新增客户、每月高潜客户、六个月覆盖终端。

只有涉及到“没有消费的月份”统计的问题，才需要更进一步的分析。比如上个月流失但这个月复购的客户数量等。这里的关键是，如果客户在某些月份没有购买，那么在数据表中就不会有任何值对应。比如这里“丁君-15280”仅仅在2017年3月、2019年6月、2019年8月和2020年6月四个月份有购买，那么就会只有四行描述。

因此，在高级业务分析中，有一种被称之为“周期快照事实表”Periodic Snapshot Fact Tables，它的数据层次通常和周期的日期有关，甚至会包含一些业务数据中根本不存在的数据。比如“丁君-15280”在2020年1月没有销售，但是不影响在本月可以刷新一下所有的状态。有时候我们也可以把这种数据称之为“切片数据”。

这里最关键的一步，就是把“业务数据表”转化为面向分析的“周期快照”数据表。特别注意，对于销售场景而言，周期快照不是业务的映射，而是基于业务交易、对分析问题的映射。

3、周期快照数据表：基于日历表的客户销售日历表

周期快照，可以理解为定时刷新并增量保存，在特定的行业，这种数据记录方式有具体的业务意义，比如银行中每个账户的每天余额，可以按照“日期*账户”的行级别层次记录下来，就是典型的周期快照。

在这里的批发零售中，并非每个客户每个月份都有进货，所以日常中也不会使用上述的周期快照来记录。在这里，为了完成特定的迁徙分析场景，我们有必要生成生成如下的数据结构：

客户ID	快照日期	产品类别	度量
A001	2021-1	处方药	1
A001	2021-2	处方药	0
A001	2021-3	处方药	1
A001	2021-4	处方药	0

这里的关键是，如何为每个客户ID匹配一个公共的日期——这里使用每月刷新快照，因此暂且称之为“月历”。方法一是使用Excel创建单独的数据源，方法二是使用Prep、SQL等工具直接从数据库中取一个月历列表。

这里使用Prep的聚合功能，从明细数据中取“订单日期”并将分组级别设置为“月开始日期”（即年月开始日期）。不过，如何保证中间不会出现空缺的月份呢？这完全依赖于数据库每个月份至少有一笔业务交易，哪怕是春节或者疫情期间，否则对应月份就会跳过。考虑到这种极端情况对应的业务完全暂停，加上当月的快照似乎也没有分析价值。在这里，超市数据中没有空缺的月份。

接下来是关键，如何为每个客户ID匹配这个“月历日期”呢？这里，喜乐君先用一个最简单的实例来整理一下逻辑。

这里假设一个门店01，它在四个月有业务记录，为了生成一个月度的周期快照，这里需要与“月历表”做数据匹配关联。考虑到2019年6月没有对应的业务记录，“相等连接”显然是不可行的，需要“范围匹配”方式才能让2019年6月出现在“门店01”的后面。

这里使用了交易聚合表中的“日期”<=月历表“月历”的方式，就可以构建上图下方的一对多的匹配关系，在此基础上，进一步通过“聚合”精简数据到每个月月历保留一行数据。

保留最后一行存在两种情形：当月有业务记录，那么返回的就是当月的日期，否则返回的就是上个月日期。接下来，使用超市数据完成这个过程，并介绍如何增加当月的销售金额和累计销售金额等聚合数据。

在实际业务中，喜乐君通常会保留一个最小的样本，再最终流程确认无误后再撤销 。如下图所示，将此前“客户*年月”的聚合表和上述的“月历表”建立连接join，默认使用“相等匹配”。虽然可以通过外连接outer join或者月历表的left显示所有的月历，但是这样出现的月历是缺乏意义的重复，无法和客户id、产品类别等对应。

“周期快照数据表”的客户id、类别等字段应该来自于交易的聚合表，而日期应该来自于月历表。正确的做法是使用“范围匹配”，这里使用月历表的“月历”大于等于交易聚合表中的“日期”。

由于这里的月历和订单日期是一对多的关系，我们还需要把月历当月的销售、订单数等字段添加到数据，这里也有多种方法，一种是基于计算，另一种是基于连接join。这里先使用计算的方法。如下图所示：

接下来，需要额外增加一个“筛选环节”，即每个门店的每个月历只保留一行数据。此处的筛选有两个方法，其一是使用计算（需要fixed LOD），其二是使用聚合节点完成筛选，它们背后的本质其实是一致的。在这里，喜乐君使用后者，这个方法还有一些额外的好处，可以为每个客户、每个月历返回它的历史累计消费金额、累计数量、累计订单等数据。

至此，我们就生成了一个以月度为更新频率的“周期快照数据表”，可以记录每个客户在每个月份的购买情况。

三、分析需求与数据表改进

参考前文，这里先分析几个覆盖率的典型指标：“六个月内覆盖门店”“上月流失终端”“本月新增覆盖”“本月高潜终端”。然后完成必须依赖“周期快照数据表”的迁徙分析。

1、分析字段的最佳位置：Tableau Desktop可视化

首先，确认分析观察点和数据中的最后日期对应。比如这里最大日期为2020年8月，因此观察点就是8月底。如果30天后，数据中出现了9月份增量，就自动取9月为观察点。

借助于Tableau Desktop敏捷的分析能力和计算，这里可以创建一个字段作为全局观察点，并随着数据更新自动变化。如下：

{fixed : MAX(订单月历)}

或 {MAX(订单月历)}

max订单月历

“六个月内覆盖门店”，即相对于观察日看过去六个月门店是否有销售记录。如下图所示，如果至少有一个“当月销售”不为空，那么就标记为“覆盖门店”，否则就是“非覆盖门店”。

分析的关键是，如何把逻辑上的分析需求语言，转化为计算机可以识别的数据符号，其中的关键是逻辑判断。

所有的“区间范围”，喜乐君都推荐使用Datediff函数完成；具体到这里，可以用如下的方式完成。注意，每一个计算都应该清晰是在行级别完成，还是在视图层次完成的。由于所有的字符串函数、日期函数都是行级别的，这里的“六个月内覆盖门店”也是如此。

由于覆盖率都是聚合指标，建议在上述的计算之外预先添加聚合函数。按照类似的逻辑，参考前文的分析过程。可以把“上月流失终端”等其他字段完成。

上述是IT普遍使用的计算方法，本质上是在行级别预先打标签，而后按需要聚合的过程。只是，特别注意这种方法对性能的影响，行级别计算会大幅降低计算效率。所以，这种方法最后，喜乐君强烈强烈建议增加必要的筛选条件。比如上述的所有计算，都没有超过六个月时间范围，所以，最佳策略是增加最近半年或者本年的日期筛选器，添加到上下文筛选有助于提高性能。

特别注意，上述的大部分逻辑，都可以基于中间聚合表完成，后续介绍。很多时候，周期快照并非必备选择。

@喜乐君
Sep 5, 2021