业务4:医药行业的药品覆盖分析（上）-喜乐君-敏捷BI布道师

前几天去石家庄拜访Tableau的老客户（一家专利药上市公司），聊起来覆盖率分析。我隐约记得前年问过我类似的问题，我没有找到很好的解决方案，如今可以略显轻松的回答这个问题。所以本期临时转一下行业，从医药行业的药品覆盖分析，讲解分析框架和方法。这个方法背后，其实与金融行业的账户迁徙分析如出一辙。

一、覆盖和覆盖率的分析说明

“覆盖”，顾名思义是特定区域的抵达，它的主体是药品、食品、服务等有形或无形的商品，而它的对象则是城市、门店、客户等字段及其相关属性。衡量覆盖的指标也多样，比如获得一个新机构客户视为一次全新覆盖（即常说的新客户），或者当月进货金额超过1万元视为一次“有效覆盖”。因此，常见的覆盖/覆盖率问题如下所示：

上个月，河北省覆盖的有效覆盖医药门店有多少家？
以过去6个月为口径，各省份覆盖的不同类型的医药门店分别多少价？
以过去6个月为口径，各省份的流失终端有多少家？
以本月期初门店为参考，当前各省份的医药门店覆盖率分别多少？
本月，各省份的高潜终端、高产终端、新开终端分别几家？

可见，覆盖分析的几个关键是：识别覆盖的时间范围、确认覆盖的聚合指标。特别是计算“覆盖率”时，需要确认对比的基准是何时何数，比如相对于今年年初的终端覆盖率，相对于本月期初的终端覆盖率等。

接下来，以“六个月内覆盖门店”“上月流失终端”“本月新增覆盖”“本月高潜终端”为分析指标，讲解分析逻辑和对应的数据表逻辑。

这里的分析指标，可以视为是“分析层”的分析框架；为了完成分析层的指标计算，需要找到对应的数据表，确认聚合计算的起点；而对应的数据表对应具体的业务流程。这就是“分析-数据-业务”的三层结构。具体参考此前的文章：业务01：金融业务中的Tableau分析框架

二、覆盖分析的数据表范例及计算

覆盖率多半需要时间上的对比才有意义，因此，覆盖分析对应的数据表，应该是“不同时间、给不同对象的销售记录”。由于这里覆盖的分析对象是“门店终端”，以订单或者交易的数据明细不适合于这个分析，因此我们可以聚合到分析所需要的更高层次，比如各月、各门店的销售金额。

考虑到还会有产品分类的覆盖率，所以这里以“日期*药品大类*销售终端”为层次，对销售额、数量等做对应的聚合处理。如下的分析角度为例：

销售年月	产品大类	终端编码	销售金额	销售数量	备注
2021/4	处方药	001	100	2	新客
2021/5	处方药	001	200	4
2021/7	处方药	001	300	6
2021/6	常规药	022	100	5	新客
2021/7	常规药	022	200	20
2021/7	处方药	031	100	1	新客

我们把这个数据表称之为“药品销售维度聚合表”，它以“销售年月*产品大类*终端编码”为行级别唯一字段，描述了数据表对应的业务场景：在何时、给谁whom、卖给了何物what，以及对应的聚合描述how much“。

通过上面的聚合表可以得出：

“六个月内覆盖门店”=3家（对过去六个月有销售的终端的不重复计数）
“本月高潜终端”=2家（2021年7月，进货金额超过200的终端）
“本月新增覆盖”=1家（2021年7月，第一次进货销售的终端不重复计数）
“上月流失终端”=1家（2021年6月，没有进货，但是之前有进货的终端）

在这里，前面两个数据都可以通过“行级别筛选条件+聚合”的方式计算而来，具体到语法，就是逻辑判断和聚合函数的结合。而后面两者则建立在更加复杂的逻辑判断基础上，需要结合行级别的逻辑判断和行间的比较才行，行间比较涉及到跨行，是表计算/窗口函数的领地。

1、“六个月内覆盖终端”

这里“六个月内”是逻辑判断，而”覆盖门店（数量）”是聚合计算。转化为计算机可以识别的逻辑过程，就是：

COUNTD( if DATEDIFF(‘month’,[销售年月], today() )<=5

// AND [销售额]>0 //对于这里而言，此句多余，适用于后面切片数据

then [终端编码] end )

在这里，使用了DATEDIFF函数来判断范围是否在六个月之内（这里today()=2021/7/25），if判断会为满足条件的终端编码创建一个辅助列。最后，通过不重复计数对终点编码执行聚合，就获得了符合条件的结果。

说到这里，一定要记住《业务可视化分析》中至关重要的一句话：问题分为样本范围、问题描述和问题答案，聚合回答答案。

那为什么这里要把筛选范围写到聚合之中，而非像传统的分析一样置于聚合之外呢？比如把“销售年月”拖拽到筛选器，然后选择最近六个月，再执行COUNTD([终端编码])聚合。并非不可，单说这两个答案而言，结果完全等价，逻辑截然不同；从性能的角度看，而且后者明显要优于前者（可以参考【高级】Tableau性能优化之关键：两种计算对性能的影响实证）。

那为什么会有第一种写法，而且这种写法在IT主导的分析领域如此盛行？这里有两个考虑：

如果多个“包含条件的聚合”字段要在同一个工作表显示，而且条件不同，就无法通过额外的筛选环节来完成，只能把筛选的逻辑判断置于聚合之中。
性能和效率通常难以兼得，IT经常希望获得“所见即所得”的效果，字段A就是A，和其他条件无关，追求这种极致简化的结果，就是把“2017年的销售额”和“2018年的销售额”通过两个if+sum的组合分别独立开来。

重要的是平衡。两种方法适用的场景不同，背后对应的是行级别计算和聚合计算的差异，即“第三字段分类”的复杂逻辑。合理的选择在于明确原理，从而识别最佳的场景，后面会有单独的文章深入讲解此事。

2、“本月高潜终端”=2家

基于上述的方法，“本月高潜终端”的计算方法就一目了然了，只是相对于“六个月内覆盖门店”增加了一个判断条件。

“本月”，即销售年月= today的年月，通过datediff完成
“高潜”，基于数据表度量的判断，这里是销售额>=200

二者合并，计算逻辑如下所示：

COUNTD(
if DATEDIFF(‘month’,[销售年月], today() )=0 AND [销售额]>=200
then [终端编码] end
)

3、“本月新增覆盖”

这个逻辑就又和上面两个截然不同，“本月”的范围简单，关键是如何判断“新增”。就逻辑上而言，“本月新增”和“上月流失”属于近似的范畴，都要借助于多个行之间的比较来实现逻辑判断。

本月新增：本月之前没有更多的[销售年月]所对应的数据行
上月流失：上月流失是空行，而上月之前的[销售年月]有对应的数据行

涉及到“本月之前”的数据行，筛选的性质就完全变了。从之前单行内的逻辑判断，转变成了跨行的逻辑判断。也正因为此，喜乐君发现有些客户把数据转化为如下的样式：

终端编码	产品大类	202104销售	202105销售	202106销售	202107销售
001	处方药	100	200	0	300
011	常规药	0	0	100	200

这种数据结果，是从“分析指标”到“数据表”转化过程的反映，这种朴素的、直白的转化看似解决了当前的问题，却无形中买下了更深的地雷，无法自动扩展未来的更多年月日期。顺其自然地方法是预先把日期写到2021年12月，然后每个月修改后面的逻辑判断。

条条大路通罗马，这既是谚语，也是真理。

两点之间直线最短，这既是原理，也是方法论。

首先要明确地说，从长远的分析来看，上面把日期转化为列的方法，犹如在有飞机的年代骑自行车去罗马，是不可取的。它不仅无助于解决“本月新增”“上月流失”这样的复杂聚合，也让“过去六个月覆盖”“本月高潜覆盖”的分析变得复杂。

其次，要在之前关系数据的结果中通过增加辅助列的方式完成。

先看“本月新增”。

本月新增的关键在于证明“本月是首月”。而“首月”（MIN年月）实际上潜在包含聚合计算的。如果转化为通俗的逻辑判断，就是：

if ~~[销售年月]~~ [今天所在月]= [该零售终端的首次销售年月] then [终端编码] else NULL end

问题在于， [该零售终端的首次销售年月] 是指定在终端编码层次上的聚合计算，它要在上述的if判断之前就要完成，即预先计算。Tableau最为伟大的成就之一，在于使用无比优雅而简洁的FIXED LOD逻辑完成了这个过程：

{ FIXED [终端编码]: MIN([销售年月]) }

当然，如果要按照产品大类分别计算每个终端在不同产品上的首次日期，那么指定的层次就要改为 [终端编码]* [产品大类] 。在这里，而且结合如下所示：

if datediff(‘month’, today() , { FIXED [终端编码], [产品大类] : MIN([销售年月]) } )=0
then [终端编码] else NULL end

#上述的分析逻辑，在“15大LOD表达式”中曾有介绍，也是典型的fixed lod案例。

当然，这样的逻辑对于业务用户而言，已经进入了难以理解的地步。毕竟熟练使用FIXED LOD不仅需要非常熟悉Tableau的产品和语法逻辑，更要有层次分明、清晰条理的数据抽象逻辑。那IT用户能否预先为此建立准备，把这种建立在跨行的聚合问题，转化为单行内的问题呢？

比如在每一行后面增加一个辅助列字段，来判断客户的过去是否到访，甚至于到访次数。数据结果大致如下：

销售年月	产品大类	终端编码	销售金额	销售数量	期初累计销售	备注
2021/4	处方药	001	100	2	0	新客
2021/5	处方药	001	200	4	100
2021/7	处方药	001	300	6	300
2021/6	常规药	022	100	5	0	新客
2021/7	常规药	022	200	20	100
2021/7	处方药	031	100	1	0	新客

在每个数据行后面，预先增加一列“期初累计销售”，与此前的“销售金额”相对应。比如022终端在2021/6首次购买常规药100，期初为0代表之前从没有进货；当月的累计就是次月的期初，因此2021/7月进货200，而期初100（截止上月月末）；以此类推，下个月的期初就是2021/7月底的累计300了。

这样，新客户的判断就变成了

IF [期初累计销售] =0 then [终端编码] end

这样，“本月新增客户”的判断也就异常优雅简洁了。

COUNTD(
IF DATEDIFF(‘month’,[日期],today())=0 AND [期初累计销售] =0
then [终端编码] end
)

当然，这个逻辑背后被没有改变筛选中增加聚合的本质。“”本月新增覆盖终端“，看似只是对终端编码的不重复计数，难点却在于本月的范围判断和”新增“的聚合判断。

4、“上月流失终端”

乍一看，“上月流失终端”和“本月新增终端”如出一辙，但是仔细一想，新增终端在明细中对应数据行，但是流失终端在当月却毫无痕迹可以聚合。

我们如何对不存在的数据行进行判断，此时，上面的数据表就又需要更新一步了。为了分析流失，我们要保留从终端新开以来所有月份的记录，如果当月没有进货就以0填充。于是就有了如下的结构：

销售年月	产品大类	终端编码	销售金额	销售数量	期初累计销售	备注
2021/4	处方药	001	100	2	0	新客
2021/5	处方药	001	200	4	100
2021/6	处方药	001	0	0	100	无交易
2021/7	处方药	001	300	6	300
2021/6	常规药	022	100	5	0	新客
2021/7	常规药	022	200	20	100
2021/7	处方药	031	100	1	0	新客