业务4:医药行业的药品覆盖分析(上)

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

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

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

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

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

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

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

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

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

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

销售年月产品大类终端编码销售金额销售数量备注
2021/4处方药0011002新客
2021/5处方药0012004
2021/7处方药0013006
2021/6常规药0221005新客
2021/7常规药02220020
2021/7处方药0311001新客

我们把这个数据表称之为“药品销售维度聚合表”,它以“销售年月*产品大类*终端编码”为行级别唯一字段,描述了数据表对应的业务场景:在何时、给谁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处方药1002000300
011常规药00100200

这种数据结果,是从“分析指标”到“数据表”转化过程的反映,这种朴素的、直白的转化看似解决了当前的问题,却无形中买下了更深的地雷,无法自动扩展未来的更多年月日期。顺其自然地方法是预先把日期写到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处方药00110020新客
2021/5处方药0012004100
2021/7处方药0013006300
2021/6常规药02210050新客
2021/7常规药02220020100
2021/7处方药03110010新客

在每个数据行后面,预先增加一列“期初累计销售”,与此前的“销售金额”相对应。比如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处方药00110020新客
2021/5处方药0012004100
2021/6处方药00100100无交易
2021/7处方药0013006300
2021/6常规药02210050新客
2021/7常规药02220020100
2021/7处方药03110010新客
第三个版本的数据表结构

基于这样的结构,就可以通过判断当月的销售金额识别是否流失了。

COUNT(

IF DATEDIFF(‘month’, [销售年月], today())=1 and [销售金额] =0

then [终端编码] end

——上月流失终端的计算逻辑@喜乐君

基于这样的数据结构,分析师还可以进一步打开更多的分析世界。比如分析“上月流失本月复购的既存终端数量” “上月有覆盖的高潜终端,本月流失的比率” “相对于年初的所有终端,当前的有效覆盖比率”有多少等。

这些高级的分析主题,无一例外使用了前后月份的对比计算,二者正是很多行业中客户迁徙分析、账户滚动分析的理论基础。后文择时深入讲解。

【补充】如果这里不增加没有交易的空行,能否通过类似的if逻辑完成计算呢?似乎也是可行的。如果终端编码没有任何一个日期等于上个月,则可以给这个终端打标签 ,无法把标签标记到了其他月份行上而已。可能需要多重lod计算。后续考虑。

三、结构化数据表的来源

通过多个“分析指标”的循序渐进讲解,喜乐君介绍了包含逻辑判断条件的聚合计算,解释了覆盖分析中推荐的结构化数据表的演进过程。接下来,我们有必要思考一个问题:这个数据表在真实的业务过程中存在吗?如果存在它对应什么业务场景;如果不存在,它如何而来。

销售年月产品大类终端编码销售金额销售数量期初累计销售备注
2021/4处方药00110020新客
2021/5处方药0012004100
2021/6处方药00100100无交易
…………

1

业务01:金融业务中的Tableau分析框架 讲解的“分析-数据-业务”的三层框架中,喜乐君解释了彼此之间的关联。数据是业务的映射,数据表是数据的记录,那数据表也一定是业务的反映吗?

不一定,甚至说大部分时候都不是。

对于医药公司而言,基于CRM或者DDI(药品流向)系统的每一笔交易都会被如实记录,它们会进入对于的数据明细表存档。从这个意义上讲,数据是业务的映射,数据表是业务的反映。

但是,每个终端在每个月的进货总金额、总数量,却并非对应业务的具体过程,而是非常多的业务过程的聚合、重组。所以我们说,“每个终端在每个月的销售额”是在逻辑意义上存在的问题,是抽象在领导和分析师大脑中的;同样,它对应的数据表也需要聚合而言,是独立于业务系统的过程。

这个从具体的业务数据表,到抽象的逻辑数据表的过程,就是数据仓库的搭建过程。从数据表的角度看,数据仓库旨在搭建面向抽象业务场景的数据表,从而加速数据分析的过程、提高效率。

当然,我们也可以把数据仓库的准备过程分为两种主要的类型:

  • 多个数据表join成为一个完整的、具体的数据表的过程,不更改行级别的层次,不包含聚合
  • 多个数据表,借助聚合aggregate和join连接成为抽象数据表的过程,发生了层次上的聚合变化
  • 在特殊情况下,上述过程可能包含结构性的转置过程。

2

这里,喜乐君采用一家医药客户的脱敏数据,来看一下这个过程。由于历史的原因,他们使用了上述最不推荐的数据结构:把日期以列的方式显示。

假定从这里出发,可以通过prep的转置、拆分功能,快速转化为推荐的第三种样式。

推荐的数据结构

不过这个只是临时之计,在下一篇文章中,喜乐君要从数据的交易明细出发,讲解生成上述数据结构的完整过程。

这里先使用上述转置后的结果表出发,讲解这里的逻辑。由于Prep中不能直接完成聚合,可以把判断条件在其中完成,如图所示。不过,考虑到可视化的关键是交互,查询的日期会随着变化,因此更推荐在desktop中完成所有的后续计算和筛选过程。

在prep中完成基于筛选样本的标记(静态标记)

3

这里使用prep输出的结果,在desktop中完成计算和展现。

第一,我们默认把时间分析锚点设置为「年月」日期的最后日期,通常它会对应当前月份或当前天。可以使用最简单的FIXED LOD计算快速完成。如下图

第二,用上述的计算逻辑,完成覆盖的指标计算,注意聚合中包含判断。这里使用iif简化if-then-else语法。

这样,不需要增加筛选条件,就把筛选和聚合结合在一起,从而统一展现多个数据值。

在这里,由于流失判断是基于行级别的,每个终端不是在A品种、X地区没有销售,就是在B品种、Y地区没有销售,所以每个终端都至少会返回一次 自己的终端代码,导致流失终端过多。这里可以把“流失”改为在本月没有任何进货,那么逻辑就可以做进一步调整:

在使用fixed计算时,分析会锁定到指定的层次,它是绝对的、独立的,因此在分析要特别注意。喜乐君更建议在明细中通过增加“上月累计”“上月销售”的方式完成判断,相对更加稳定。

下一期,会通过Prep 完成上述的聚合表,这将是一个复杂的逻辑过程。

©️喜乐君 Aug 31, 2021

发布者:喜乐君

喜乐君 | Tableau Partner,Tableau Desktop and Server QA Certification

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Google photo

您正在使用您的 Google 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

%d 博主赞过: