如何用 Power BI DAX 計(jì)算年度留存客戶
留存客戶除了在互聯(lián)網(wǎng)公司的應(yīng)用，更是一個(gè)通用的問(wèn)題。
一年過(guò)去了，很多企業(yè)開(kāi)始計(jì)算，上一年度的客戶的留存。我們看這樣的問(wèn)題描述。
假設(shè)某公司在 2020 年 12 月有活躍客戶 100 人，而在此后的一年中只要有銷售額，就算該客戶在此后的一年為留存客戶。那么，對(duì)于試著給出對(duì)任何年月的活躍客戶計(jì)算其在未來(lái)一年的留存數(shù)以及留存率。
活躍客戶
活躍客戶在不同的業(yè)務(wù)中的定義也不同，這里我們姑且給出活躍客戶的精確定義。如下：
活躍客戶，又稱某日期區(qū)間內(nèi)的活躍客戶，指在給定的日期區(qū)間內(nèi)，有銷售的客戶，稱為活躍客戶。其數(shù)量為活躍客戶數(shù)。
數(shù)據(jù)模型
通常，客戶表（客戶維度）和訂單表（交易事實(shí)表）以及日期表（日期維度）之間，會(huì)構(gòu)建一個(gè)數(shù)據(jù)模型，大致如下：
請(qǐng)伙伴們記住：
永遠(yuǎn)使用維度表中的字段作為分組字段；而不要使用交易表中的同樣字段。
日期表也必須使用作為維度表的日期表；而不要使用交易表中的日期字段。
客戶是可能重名的，使用客戶 ID 作為唯一標(biāo)識(shí)。
下面開(kāi)始來(lái)對(duì)活躍客戶進(jìn)行計(jì)算。
活躍客戶的計(jì)算
其實(shí)，活躍客戶的概念不難理解，但在結(jié)合到表格的時(shí)候，卻不那么容易，我們需要考慮：
單個(gè)客戶是否活躍；
某類客戶有多少活躍。
現(xiàn)在考察單個(gè)客戶的情況，我們定義一個(gè)度量值如下：
Customer.活躍.標(biāo)識(shí) =
IF( COUNTROWS( 'Order' ) > 0 , 1 )該度量值的原理是，如果客戶維度對(duì)交易事實(shí)表有篩選，且結(jié)果不為空，說(shuō)明存在交易，則客戶是活躍的。這里使用?COUNTROWS?表的方式是一種技巧，且考慮了性能的優(yōu)化問(wèn)題。
請(qǐng)注意這里的用詞：單個(gè)客戶的情況。
因此，在構(gòu)造中，必須要求模型設(shè)計(jì)者將可以表征客戶唯一性的標(biāo)識(shí)列作為分組字段，如下：
可以看到這樣的特征，如下：
必須有年份和月份作為篩選環(huán)境，這是由活躍用戶在本場(chǎng)景下的定義決定的。
使用客戶維度的客戶 ID 作為分組字段，度量值顯示為 “活躍”，數(shù)值為 1，表示該客戶在當(dāng)月是活躍的。
但總計(jì)行的 1 并不能給出良好的語(yǔ)義，例如，總計(jì)行應(yīng)該給出總的客戶活躍數(shù)。
值得注意的是，在目前的模式下，如果使用額外的篩選器對(duì)客戶進(jìn)行篩選，其效果也是可用的，例如：
如果選定了某個(gè)行業(yè)，那么該度量值的計(jì)算依然有效。
現(xiàn)在的問(wèn)題是如何處理總計(jì)行的問(wèn)題。關(guān)于總計(jì)行的處理，我們此前有文章給出了終極方案，此處再做復(fù)習(xí)，給出考慮了總計(jì)行的度量值，如下：
Customer.活躍.數(shù)量 =
SUMX(
VALUES( Customer[CustomerID] ) ,
CALCULATE( IF( COUNTROWS( 'Order' ) > 0 , 1 ) )
)該度量值，不僅僅適用于單行客戶，還適用于沒(méi)有客戶的總計(jì)行。效果如下：
可以看出，此處的總計(jì)是正確的。
有了這個(gè)計(jì)算，我們還可以再提出一個(gè) KPI 單值卡片圖，如下：
接下來(lái)，要計(jì)算的是在所選日期區(qū)間未來(lái)一年的交易客戶情況。
日期維度的變換
這里是初學(xué)者晉級(jí)的一個(gè)重要思維切換點(diǎn)，我們此前對(duì)日期智能函數(shù)的計(jì)算也給出了終極思維模式，可以參考此前文章，這里復(fù)習(xí)如下：
沿著日期維度的計(jì)算，其本質(zhì)是對(duì)日期維度的變換。
什么叫日期維度的變換？定義如下：
篩選環(huán)境中給出了作為篩選的日期區(qū)間，稱：[D1,D2]。
計(jì)算中需要使用另一段日期區(qū)間，稱：[D1',D2']。
從 [D1,D2] 到 [D1',D2'] 的變換就是日期維度的變換。
在本案例中，已經(jīng)篩選了某個(gè)年月的區(qū)間，但在計(jì)算中需要考量的是未來(lái)一年的日期區(qū)間，有：
[D1,D2] 為某年月。
[D1',D2'] 為 [D1,D2] 隨后的一年。
即 D1' = D1 + 1，D2' = D1' + 一年。
注意：
一年和 365 日是不同的概念，一年是一個(gè)嚴(yán)格的概念，有的年份是 365 日，而有的年份是 366 日，因此，用一年這個(gè)概念更加嚴(yán)謹(jǐn)。很多初學(xué)者是不區(qū)分一年和 365 日這兩個(gè)概念的，即使其誤差從計(jì)算結(jié)果上可能可以忽略不記，但由于這個(gè)概念的不夠嚴(yán)謹(jǐn)，可能導(dǎo)致在其他的計(jì)算中出現(xiàn)嚴(yán)重問(wèn)題。
在本案例中，如果要計(jì)算某年月隨后一年的活躍客戶數(shù)，可使用以上思路建立度量值，如下：
Customer.活躍.數(shù)量.未來(lái)一年 =
CALCULATE(
SUMX(
VALUES( Customer[CustomerID] ) ,
CALCULATE( IF( COUNTROWS( 'Order' ) > 0 , 1 ) )
) ,
DATESINPERIOD( 'Calendar'[Date] , MAX( 'Calendar'[Date] ) + 1 , 1 , YEAR )
)其中，實(shí)現(xiàn)日期區(qū)間變換的是：
DATESINPERIOD( 'Calendar'[Date] , MAX( 'Calendar'[Date] ) + 1 , 1 , YEAR )該變換將清除當(dāng)前年月日期區(qū)間的篩選，同時(shí)施加一個(gè)未來(lái)一年的日期區(qū)間的篩選，得到所需效果。
結(jié)果如下：
不難看出，客戶的活躍有這樣的表現(xiàn)：
【1】在本月活躍，在未來(lái)一年不活躍。
【2】在本月不活躍，在未來(lái)一年活躍。
【3】在本月活躍，在未來(lái)一年也活躍。
我們需要進(jìn)一步來(lái)計(jì)算留存的客戶。
留存的客戶計(jì)算
基于以上的分析，留存的客戶，其計(jì)算特征如下：
在本月活躍，在未來(lái)一年也活躍。
這可以通過(guò)不同的 DAX 計(jì)算功能組合實(shí)現(xiàn)，這里給出常見(jiàn)的集合求交集的方法。如下：
Customer.活躍.未來(lái)一年留存數(shù)量 =
VAR vItemsInThisPeriod =
FILTER(
VALUES( Customer[CustomerID] ) ,
CALCULATE( IF( COUNTROWS( 'Order' ) > 0 , 1 ) )
)
VAR vItemsInOtherPeriod =
CALCULATETABLE(
FILTER(
VALUES( Customer[CustomerID] ) ,
CALCULATE( IF( COUNTROWS( 'Order' ) > 0 , 1 ) )
) ,
DATESINPERIOD( 'Calendar'[Date] , MAX( 'Calendar'[Date] ) + 1 , 1 , YEAR )
)
RETURN COUNTROWS( INTERSECT( vItemsInThisPeriod , vItemsInOtherPeriod ) )
以上計(jì)算分為三個(gè)部分：
先計(jì)算本期客戶列表，返回表。
再計(jì)算未來(lái)一年活躍客戶列表，返回表。
再求兩個(gè)表的交集的數(shù)目。
如果你想多學(xué)習(xí)一點(diǎn)，還可以用類似的實(shí)現(xiàn)如下：
Customer.活躍.未來(lái)一年留存數(shù)量.2 =
COUNTROWS(
FILTER(
VALUES( Customer[CustomerID] ) ,
CALCULATE( IF( COUNTROWS( 'Order' ) > 0 , 1 ) ) &&
CALCULATE( IF( COUNTROWS( 'Order' ) > 0 , 1 ) , DATESINPERIOD( 'Calendar'[Date] , MAX( 'Calendar'[Date] ) + 1 , 1 , YEAR ) )
)
)以上方法，針對(duì)單個(gè)客戶進(jìn)行判斷，方法如下：
滿足本期活躍
且同時(shí)滿足在未來(lái)一年活躍
篩選出所有同時(shí)符合上述條件的客戶。
這樣，整個(gè)效果如下：
可以看出兩種方法的計(jì)算結(jié)果完全一致，得到了檢驗(yàn)。
DAX 計(jì)算的檢驗(yàn)
DAX 的計(jì)算是在模型中進(jìn)行的，這對(duì)很多初學(xué)者造成困難，因?yàn)槟愀静恢滥阌?jì)算的正確還是錯(cuò)誤。
這里給出的檢驗(yàn)方式就是：
用兩種方法進(jìn)行計(jì)算，兩種方法使用不同的思路或者根本不同的 DAX 函數(shù)，來(lái)確保它們的邏輯結(jié)構(gòu)不同，如果結(jié)果相同，那么兩種同時(shí)正確，如果結(jié)果不同，那么，很可能出現(xiàn)了錯(cuò)誤，可以再做檢查。
留存率的計(jì)算
對(duì)上表進(jìn)行簡(jiǎn)化，如下：
其中留存率的計(jì)算非常簡(jiǎn)單，如下：
Customer.活躍.未來(lái)一年留存率% =
[Customer.活躍.未來(lái)一年留存數(shù)量] / [Customer.活躍.數(shù)量]這很容易理解。
計(jì)算的可擴(kuò)展性
好的度量值設(shè)計(jì)，是可以兼容不同場(chǎng)景的，例如本案例中的設(shè)計(jì)除了已經(jīng)滿足了這樣的要求外，還可以做到這樣的效果，如下：
這里沒(méi)有添加任何新的度量值，但對(duì)不同行業(yè)的活躍客戶和留存也給出了計(jì)算，請(qǐng)大家觀察它的正確性以及自己思考它為什么可以自適應(yīng)這樣的結(jié)果吧。
總結(jié)
DAX 用作數(shù)據(jù)建模以及計(jì)算有著重要的規(guī)律和最佳實(shí)踐，2022 年，我們將帶領(lǐng)大家一起從新的維度和視角學(xué)習(xí)這一套數(shù)據(jù)分析工具，讓你耳目一新。
在訂閱了BI佐羅講授的《BI真經(jīng)》之《BI進(jìn)行時(shí)》課程區(qū)，除了可以下載本文案例，還可以觀看視頻講解。
與精英一起討論 Power BI，驗(yàn)證碼：data2021
點(diǎn)擊“閱讀原文”進(jìn)入學(xué)習(xí)中心
↙