作为Tableau学习路上最大的拦路虎之一,LOD如此强大,但又如此变化莫测,需要反复的练习和琢磨才行。在LOD的第三篇中,我们详细介绍了分析LOD类型命题的思路,即可视化焦点+引用背景的分解方法(【Tableau】详细级别表达式LOD_(3))。这篇博客,我们将详细地介绍官方的15大详细级别表达式de会员分析部分,并用上面的思路做进一步的分解,有助于初学者更好地理解这些案例。

下面的链接,可以看到LOD15大详细级别表达式的中英文版原文。

「可视化焦点+引用背景」的分解方法,其本意就是让我们把解决问题分为台前和幕后两个阶段,台前的部分是数据冰山上的可视化部分,而幕后的部分则是使用LOD引用视图详细级别中没有的聚合。这就是官方博客中第二段的首句的意思。

“LOD表达式提供了一种简单的方法,用以计算不在可视化详细级别层面的数据聚合。你因此可以在视图中以多种方式整合这些数据。”

在前一篇文章中,我们花了极大的篇幅介绍了官方的15个LOD案例,几乎涵盖了常见的各种用法。在本篇中,我想只针对【会员分析】这一个主题,根据主题分析的方法,介绍如何将Tableau 的LOD分析工具,应用到真实的业务环境中去。
在15大LOD中,和会员紧密相关的是案例1 客户订单频率分析,案例2 客户阵列分析,案例5 新客户争取率(获客率)分析,案例10,各阵列的回头客分析,案例15,各客户阵列的年度购买频率分析。
为什么会员分析如此高频又如何重要?一方面,只要有会员标识的数据,相对都更加完整,我们有两个唯一的标识(用户唯一标识和订单唯一标识),可以增加分析的维度,另一方面,对顾客和会员的分析,是可以立刻反映公司经营质量的重要指标。
(前面的案例分析和第一篇一致,近期会二次修改,如果看过上篇的,可以直接看最后一个案例——大结局和大升华)
【Apr 15, 2019补充】前几天在河南中原做培训课,题目讲的很清楚,很多人最后说“就是不知道为什么做这样的题”,其实都是真实的业务需求;因此,高级的数据分析师和商业数据分析,都是从问题、从业务开始的,了解业务是深耕分析的前提。
为此,我们在每个题目前面加上一段虚拟的对话,帮助更多人了解这些题目本身的用意。

领导:小王,我刚上任不久(会员营销部部长),对很多数据还不熟悉,最近我要了解一下我们真实的业务状况,特别是了解一下为什么最近几年的部门负责人频繁更换,作为业务老员工,你可要给我最真实的数据。

小王:没问题的,领导。你说想知道什么吧。

领导:我们的所有消费基本都是会员制的,作为会员营销部,我们重点围绕会员分析。首先,近年来我们的销售持续下滑,我想知道过去几年所有的会员消费质量,最简单的分析大概就是知道每个人总共来过多少次吧,看看是一次性的客户多,还是高频词反复购买的频次多,这样我就可以推测是否是会员出了问题。

小王:好的领导,就是要看“所有客户购买次数的分布”吧,你看第一个题目😄……

领导:我刚才看了一下第一个题目,问题很严重啊,一次性客户基本特别多,根本不带回头再来的!我今年刚来,我要知道每年有多少新客户消费,多少老客户消费,新老客户就按顾客是哪年来的看吧(休想把前几年的问题归罪到我头上!)上一任做的时间最短,不知道去年的情况有没有好转?

小王:好的领导,你想看看这几年每年的消费构成是吧,以客户的新增年度来区分,我们也称之为矩阵分析,您看第二题目吧😄 ……

领导:这个结果比第一题还惨啊,最后一年的消费,竟然还有这么多都是第一年的老客户贡献的,后面几任营销部门负责人都吃shi去了吗?(🤫)看来我的任务任重道远啊…… 领导一直换,下面的区域负责人没变啊,给我看看下面几个市场,谁家的新客户增长最多,谁在偷懒?我要找个做的最差的先枪毙了!

小王:好的领导,看见每个市场的新增客户趋势是吧,我们看看累计趋势吧,每条线的最后就是这个市场现在的所有新客户,可以看到对比增长的趋势,和最后的绝对值数量,请看第三题😄……

领导:看不下去了……自从首任领导之后,新客户增长就完全放缓,几乎不增长了啊,前面的四大市场同时放缓,我都不知道该处理谁了……难怪都是领导下岗…… 我觉得我要调整思路,看来新增会员很难,我也要走短平快路线,帮我看见那几个季度的会员复购水平最好,我要重点去开发这些老客户(啃老本)

小王:好的领导,我帮您分析一下过去每个季度,每个客户在第一次购买之后复购的时间间隔吧,有的第一次消费后三个月(一个季度内)又来了好多次,有的人过了半年才能一次,我用一个矩阵给你看准确的复购会员数量,请看第四题……

领导:不错小王,Tableau很扎实,看图上的信息,现在的消费很多还是2011年的第一批老客户,就这些人了,我要继续开发,他们再不来,我就该不来了…… 对了上面图表这么多,你能帮我尽可能用一个代替吗?我想看每年的新增会员在接下来每个消费年度的消费情况,比如今年的消费中,过去几年的新增客户分别购买了几次,这样以后我就用一个图自己筛选了。

小王:好的领导,我给你做一个终极图,你可以筛选每个年份,看到当年消费中的会员构成分别来自哪个年份(每个年份一条线一个颜色),同时我把这些顾客的消费频次放上,同时给你看看至少消费一次、两次、……N次的数量占比,你就知道那些客户贡献的多了。

领导:漂亮!不会是最后一个题吧,谢谢你,谢谢Tableau

1、客户订单频率 Customer order frequency

在零售分析中,我们经常会遇到“顾客购买频次”分析,通过查看顾客购买次数的数量和分布,来分析顾客的复购黏性。

命题:客户的购买频率分布,即购买过一次、两次、三次……的顾客分别多少。

  • 可视化焦点:不同购买次数下的顾客数量。
    • 可视化维度:购买频次(1,2,3,N)
  • 需要引用的背景信息:每个顾客的购买次数
    • fixed [customer ID] : countd(order)

在视图中,我们仅使用两个维度——购买频次和顾客的计数,也就是两个都是数值。用官方的方式说,这是用一个数值去区分另一个数值(breaking out a measure by another measure ),LOD fixed可以轻松地把一个度量转化为维度,这是include和exclude不能做的。

1 顾客购买品牌.png
//要点:Fixed LOD的独特之处是返回的多个数据结果可以作为维度使用。相比之下,include和exclude都只能作为度量使用。

2、阵列分析 cohort analysis

这个命题希望看到每个年度的新增客户在当年的销售贡献。我们把顾客获得的年度,简单称之为顾客新增,或者叫新增年度。从视图的角度看,视图的关注焦点是什么?每年中不同顾客阵列的贡献金额。因此,可视化中的维度是年、销售额sum和顾客标签(新增年度)。

在视图中我们缺少什么信息,需要我们从背景信息中引用呢?我们缺少每个顾客的标签(新增年度),因为视图不是顾客详细级别的,这就需要LOD来实现不在视图详细级别的维度的聚合。(LOD provide a way to easily compute aggregations that are not in the level of the visulization.)

首先我们按照客户首次购买的年份将客户分组,虽然这个LOD fixed返回的是首次购买的完整日期,但是我们使用它的年度部分,因此把所有客户按照年度分为几组,比如2010、2011、2012……

1st order date = {Fixed [customer ID]: MIN([order date])

为了对比各个阵列的年度销售贡献额,我们把order year拖入列,profit加入行,生成一个可视化图之后,再引用背景信息(首次购买日期)到颜色中,形成不同年度客户的贡献金额。注意,这个视图的详细级别是顾客,因此一个顾客只会对应一个最早购买日期。

Screen Shot 2018-11-26 at 09.43.39.png

接下来的工作,就是通过表计算实现阵列的百分比分析了。
//要点:一方面使用了fixed LOD作为维度使用的功能,另一方面通过把LOD字段加入到颜色标记,进一步增加了数据的表现深度。

5. 新客户争取率 New customer acquisition

命题:what is the toal number of customers we’ve acquired per market by day?

Screen Shot 2019-04-15 at 8.06.22 AM.png

这个命题的可视化焦点是每个市场每天的新客户获得数量,因此需要把三个维度或度量加入到视图:日期(天)、市场、新顾客数量。 注意,这个题目的数据,应该是订单级别的,而不是商品级别的。

而新顾客数量,就是我们需要使用LOD额外引用的背景信息。

要特别注意的是,这个题目和前面第二题不同,前面我们通过 {fixed customer: MIN(order date) }返回每个客户的最早订单日期,因为第二题的详细级别是顾客,因此顾客和最早购买日期是一一对应的。但是这个命题中,查看客户争取数量,是以日期为详细级别的,一个顾客的订单可以分布在多个日期之中,因此就需要对应不同的标签。这是这个命题的关键。

因此,额外的背景信息就是,每个顾客在每个消费日期下对应的标签,是新客户,还是老客户?判断新老客户,又需要与首次购买日期做对比,因此首先会用到Fixed LOD:

1st order date = {Fixed [customer ID]: MIN([order date])

上面的LOD表达式是在顾客详细级别上的计算,接下来我们需要在行级别做每个订单的判断,因此使用if函数。将每一次的订单时间和首次购买时间做匹配,给每个订单打标签

IFF( [1st order date]=[order date], ‘new’,’existing’ )

在准备好了背景数据之后,我们就可以构建视图了,这里把上面的new/existing加入了筛选器,仅保留了新客户的数据,也就是只有每天新客户的首次订单。既然一个新客户只对应一个订单,就可以通过通过计算顾客的数量,加上running total,生成新客户的数量。

Screen Shot 2018-11-26 at 12.12.05.png
//要点:在上一个的用例基础上,这个增加了if判断、筛选器和表计算。而Fixed LOD只有一个。这个的难点在于理解命题的整个逻辑。

10.各个阵列的回头客

After customers are acuqired, how many quarters elapse before they make another purchase?

在零售分析中,会员分析的RFM模型会被广泛引用,RFM分别代表会员最近一次消费(Recency)、消费频率(Frequency)、消费金额(Monetary),三者构成了衡量客户价值的重要指标。通过分析最近一次消费的时间可以判断会员状态(流失?沉睡?存活?),通过分析消费频次可以判断顾客的忠诚度,而通过分析消费金额,则可以推算顾客的贡献度(高价值顾客、低价值顾客等)。

在这个题目中,我们要分析顾客两次消费的间隔,因此我们需要给每位顾客分别打两个标签:最近一次消费时间、最近第二次消费时间。

我们在视图中构建矩阵,横轴和纵轴分别会是日期和日期间隔,数据为两次消费时间的间隔;视图中没有顾客的维度,但是又是以顾客为详细级别的。为了解决“视图中没有的维度但又要引用这个级别的数据计算”,我们需要使用fixed返回顾客的消费日期。

  • 可视化的焦点:下单日期和购买频次间隔,顾客详细级别。
  • 需要引用的背景信息:每个顾客在两次购买中间的间隔,顾客的订单详细级别。

这个题目的关键是,我们需要使用两次Fixed函数,返回两个日期,第二个日期建立在第一个日期的计算基础上。
首次消费日期:如果我们把每个顾客的所有消费日期看作一个整体,我们可以理解为一个数组;首次消费日期,就是从每个数组中找到其中的最小值。我们使用fixed指定每个顾客返回这个结果,因此公式如下:

1st purchase date = {Fixed [Customer ID] : MIN([Order Date])}
Fixed LOD是直接在数据源层面计算的,返回的结果是行级别的,因此每个顾客的每一条记录都对应了一个1st purchase date字段。

接下来,我们需要返回第二次购买时间,如何返回?我们需要使用一个临时字段,新建一个新的数组,剔除首次购买时间。我们可以使用IIF函数,把首次消费时间返回为NULL,其他的按照原值返回。公式如下:

IIF( {fixed [Customer ID] :min([Order Date])} =[Order Date], null, [Order Date])

如果创建了第一个字段,也可以使用上面的第一个字段替换,简化为:

IIF( [1st purchase date ]=[Order Date], null, [Order Date])

这个字段暂且命名为[Repeat Purchase],返回的是顾客剔除首次消费日期外的其他消费日期,再次基础上,我们再用fixed LOD+ min函数,返回第二次消费日期即可,如果直接在上面的基础上修改,就是:

2nd purchase date = {Fixed [Customer ID] : MIN([Repeat Purchase])}

如果不使用中间临时字段,直接在上面的临时字段上修改,那么结果就是:

{ FIXED [Customer ID]:min(IIF({fixed [Customer ID] :min([Order Date])}=[Order Date],null,[Order Date]))}

这样就生成了 第二次消费日期。

至此,我们就使用Fixed LOD返回了每个顾客的两次消费日期,接下来,我们需要使用datediff函数,以季度为参数,计算两次日期的间隔,然后生成视图。需要注意的是,视图中的日期,使用的是首次消费的日期。

Quarters to Repeat Purchase = datediff(‘quarter’,[1st Purchase],[2nd Purchase])

Screen Shot 2018-11-29 at 08.10.50.png
//要点:使用fixed LOD返回两个固定的消费日期(第一次和第二次),这是背景信息的关键,之后就是datediff函数和可视化了。

15、「高难度:3次LOD+2次表计算」各个客户阵列的年度购买频率

在每个订单年度中,来自各个顾客阵列(即顾客的获客年份)中,购买至少1次、2次、3次以至于N次的顾客的占比?
What percent of customers from each cohort (year of acquisition) purchased at least 1, 2, 3, N times in order date (2014)?

几个关键词和对应的Tableau计算:

  • 阵列(获客年份)、
  • 1次2次N次(客户购买频率,同第一题,用fixed-LOD)、
  • 至少购买X次(意味着要累计计算,或者window_sum)、
  • 占比(意味着要计算总额百分比)
  • 顾客阵列的百分比(需要计算各个阵列的顾客数量,视图中没有,需要计算,而视图中又没不在顾客层面,因此需要LOD返回每个阵列的顾客数量)

需要注意的,本题和其他题目相比,增加了一个维度筛选器,Fixed LOD在维度筛选器之前,表计算total在维度筛选器之后,因此计算顾客阵列的合计时,需要注意二者的差异,作出正确选择。

按照「可视化焦点+引用背景」的分析维度,注意英文的原文最后是 in order date,看一下效果 图:

Screen Shot 2019-04-10 at 8.51.05 PM.png

  • 视图中必须存在的字段:订单年度 order date、顾客阵列、购买次数
  • 可视化焦点是不同阵列和购买次数的总额百分比,两个维度,两次表计算;
  • 视图中缺乏的背景信息是“至少购买1次、2次、3次以至于N次”的顾客数量,以及阵列信息。

在此基础上,我们也可以分为几步:

先完成一个视图: How many customers from each cohort (year of acquisition) purchased at 1, 2, 3, N times in <年(Order Date)>?

  • 计算顾客的获客年份和每年的顾客消费频次
  • 计算“在每个阵列中,购买1次、2次、3次、N次的顾客数量”

在上面视图基础上,修改为:

What percent of customers from each cohort (year of acquisition) purchased at least 1, 2, 3, N times in <年(Order Date)>?

  • 在上面基础上,增加“至少”,也就是“至少购买1次、2次……的顾客数量”,
  • 在上面基础上,计算“至少……顾客数量”的总额百分比。
  • 1 –

先说阵列,也就是每个顾客的获客年份,这个相对简单,参考案例2的阵列分析,相当于给每个顾客返回一个最早购买日期对应的年份。有两个结果一样的方法:

第一种,LOD获得每个顾客的首次购买日期,然后在使用时增加Year函数

  • 1st order date = {Fixed [customer ID]: MIN([order date]) }
  • Year( [1st order date] )

第二种方法,直接返回每个订单日期的年份,然后返回最小值

  • 获客年份 = {Fixed [customer ID]: MIN( Year ([order date]) )}

Screen Shot 2019-01-31 at 9.40.43 AM.png

  • 2 –

其次,我们需要计算“至少购买1次、2次、3次以至于N次”的顾客数量,先不说“至少”,其实就是计算不同购买次数的顾客数量。这个就是案例1 的原题,分析方法如下:
命题:客户的购买频率分布,即购买过1次、2次、3次……N次的顾客数量。

  • 可视化焦点:不同购买次数下的顾客数量。
    • 可视化维度:购买频次(1,2,3,N)
  • 需要引用的背景信息:每个顾客的购买次数
    • { fixed [customer ID] : countd(order ID) }

不过在这个题目中,我们还需要增加一个维度,消费的年份,结果就是:每个顾客在每个年份的购买次数。

Screen Shot 2019-01-31 at 11.55.17 AM.png

有了上面两个维度,我们就可以看一下每个订单年度中,来自不同会员矩阵(获客年份)的不同订单频次了。如下图:

Screen Shot 2019-04-10 at 9.46.57 PM.png

注意事项:

如果fixed 函数不添加第二个维度 (订单日期),有什么区别? 我们可以看下图,自行对比:

TC15 消费频次对比.png

-3 –

有了获客年份,和每个年份的顾客消费频次,我们就可以完成第二步了和第三步了,难点在于“至少购买1次、2次……”如何累计。

正常而言,我们使用running total会自动按照从前向后的顺序计算,不过这里我们需要倒过来,至少购买一次的顾客数量包含了后面的所有顾客数,至少购买三次的数量包含了后面购买四五六次的顾客数……,看下面的截图,这里需要指定表计算的sort order。不过这里需要说明,有多种选项都可以指向一个排序,要注意意义的正确性。

Screen Shot 2019-01-31 at 12.01.51 PM.png

【补充】表计算的计算都可以理解为窗口计算,上面的倒序running-total也可以使用window-sum来完成;考虑到计算的逻辑到“至少3次”,也就是要累计3次和后面所有的数据,转化为window-sum就是从当前位置到最后位置,因此可以使用下面的函数:

WINDOW_SUM( COUNTD([Customer ID]), 0, LAST())

相比较上面的方法,使用window-sum计算更好理解,也不需要调整计算的顺序,推荐!

-4 –

在上面绝对值的基础上,增加百分比分析,这里需要注意的是分子是什么?是每个获客年度的顾客总数。「补充」为什么要计算每个获客年度的顾客总数?关键是我们用什么方式提供这个数值。

思考:如何选择计算? 我们的详细级别是每个订单年份中,不同消费频次对应的顾客数量,简单的说,详细级别是:订单日期/年+消费频次+顾客。

而如果要加入获客年份的顾客数量,我们可以使用fixed LOD,指定每个每个获客年度,返回它对应的顾客计数,即 : {FIXED [哪一年的客户?]:COUNTD([Customer ID])}

如何选择计算类型 升级

注意我们的可视化视图中有一个筛选器,上面的fixed LOD不受筛选器影响,但是如果我们用total表计算作为因子,结果就不同了,筛选器会影响表计算的计算。

如果想让计算不受筛选器影响,不要用表计算,推荐使用fixed LOD

Screen Shot 2019-01-31 at 12.22.56 PM.png

5、本题终极总结

详见我的仪表板

LOD 15 四步骤简要步骤 blog.png

至此,案例15其实是前面多个用法的汇总和升级,其中有夹杂了表计算的灵活运用,可以说是熟练使用LOD的试金石。

下载我的LOD案例,部分有解读,特别第15个:

LOD系列

  1. 【Tableau】详细级别表达式LOD_详尽入门3.0(1)
  2. 【Tableau】详细级别表达式LOD_位置与语法(2)
  3. 【Tableau】详细级别表达式LOD_终极实践指南与资源大全(3)
  4. 【Tableau】详细级别表达式 LOD 核心要点回顾(4)
  5. 【Tableau】LOD15大详细级别表达式-深度解读
  6. 【Tableau】15大详细级别表达式-会员分析案例

Nov 26, 2018
Nov 27, 2018
Dec 11, 2018 update
Jan 31, 2019 update 案例15
Apr 15, 2019 update对话