過去2回にわたり、SaaSの概要、ダミーデータの共有、指標について紹介してきました。
今回は実際にDAX*1を使って指標を定義していきますが、前提として以下のことを押さえておいて下さい。ダミーデータ(pbix)がないと分かりにくいと思いますので、まずは下記よりダウンロードしておいてください。
Demo file Download
https://bit.ly/3507aj8
Tabular Editor 3を持っている方でとにかくメジャーだけ取得したいという方は一番下の「DAXスクリプト」へ飛んでコードを全てコピーしてTE3のDAX Scriptに張り付けて実行をしてください(詳細は「DAXスクリプト」をご参照)。
※TE2とTE3の違い
前提
以下、前提条件となります。
- Power BIの初心者でも分かるよう、ダミーデータを作っていますが、DAXの内容は中級~となっています
- DAX式は簡単なものから複雑なものまで入っていますが、簡単なDAXほど、クエリパフォーマンス(ビジュアルにした際の反応速度)が良いケースが殆ど
- Power BIを使ってメジャーを作りますが、Excel Power Pivotでも構築はできます。ただし、一部Power Pivotには対応していないメジャー(SELECTEDVALUEやTREATAS等は使用できません)
- Power BIのSaaS分析用のテンプレートとして、使える可能性はあります。ただし、取得データの形式が案件別に違うと思いますので、考え方のほうが重要かもしれません
- ダミーデータなので、データ量は少ないです。DAX Studioでパフォーマンスを測定しながら指標を作っていますが、顧客数が多い場合にはクエリパフォーマンスが著しく遅くなる可能性はあります
- 全ての切り口で期待する数字が算出されるとは限りません
- ロジック、結果等が間違っているケースもあると思いますので、その際はTwitter等にご連絡ください(@marshal_dabao)
- より最適なDAXの書き方があれば大歓迎です
上記前提条件をベースとして、以下各指標に関するDAXを記述していきます。なお、Power BIでメジャーを追加する場合のベスト・プラクティスの1つとして、
テーブル名を右クリック > 新しいメジャー
で追加するのが良いと思います(下図)。
DAXで定義する客数指標
重要!
基本的に下記DAX数式をデモファイルにコピペすれば使用できると思いますが、微妙な改行コードが入っていたりしてエラーが発生するかもしれませんので、その場合には下記DAX Formatterで一度”濾過”をしてから数式エディタに張り付けてみてください
例えば、貼り付けたら以下のようなエラーが発生した場合、上記サイトを開いてコードを張り付け、「Format」 > 「Copy」>「Power BIの数式エディタへ貼り付け」でエラーを回避することができます。
# Customers = DISTINCTCOUNT( Sales[CustomerID] )
//トライアル客を含む顧客数
Baseメジャーになりますので、一番最初に作ります。なお、//はコメント(注意書き用)ですので、一緒に数式エディタへコピペして構いません。メジャーを作ったら、下図のようにビジュアルへDrag & Dropすれば結果を確認出来ます。
上図は[# Customers]というメジャーですが、Salesテーブルの構造上、トライアル顧客(Standardプランの全ての機能を2ヵ月無料で使用できる顧客)まで含みますので、収益に貢献している有料顧客を集計する場合にはこれらを除外する必要があります。そこで、下記のメジャーを別途作ります。
- 有料サインアップ顧客数(# Customers_Signup)
# Customers_Signup =
CALCULATE (
[# Customers],
KEEPFILTERS ( 'dEventType'[EventType] = "Signed-up" )
)
//有料顧客数(通常の顧客数メジャー)。合計値は集計できない、かつ、最終月とも一致しない
下図の通り、21年2月と3月にトライアルを開始した顧客は除外され、同年4月では2月のトライアル顧客が有料顧客(以降「Signup客」)となり、5月では3月の2名が加わり、計3名のSignup客となりました。
上記DAXの算出結果は初回記事にて紹介した下記Pivotの結果(上のPivot)となります。
同様にトライアル客数も定義しておきます。
- トライアル顧客数(# Customers_Trial)
# Customers_Trial =
CALCULATE(
[# Customers],
KEEPFILTERS( 'dEventType'[EventType] = "Trial" )
)
//無料Trial顧客数。Trialは2ヵ月のため、同じ顧客が前後の月にて重複出現する
KEEPFILTERSはdEventTypeテーブルのEventyTypeでスライスした際に、結果がどちらか一方(Trial / Signed-up)によってオーバーライドされてしまうのを防ぐ(標準のフィルターを適用する)ために使用しています。
結果は上記の通りですが、先に進める前に1つ注意すべき点があります。それは、
顧客数をカウントする場合、合計値とは何か?
を認識しておくことです。単なる売上等のフロー指標とは異なり、DISTINCTCOUNTを使った計算は、
合計値とそれ以外が一致しないことが多い
ことを十分理解する必要があります。上表で言えば全てのメジャーの合計値がその上の各月の数字を集計した合計と一致しません。なぜなら、DISTINCTCOUNTはその時点のフィルターコンテキスト(各月 or 合計 = 21/02~21/03までの14ヵ月)において、ユニークな顧客数をカウントしているだけの計算になるからです。
これを前提として上記3つのメジャーについて吟味してみると、この段階で既にいろんなことが分かるようになります。
- 合計値がそれ以外の月の集計と一致しない
- [# Customers]はSignup顧客、Trial顧客の両方を含むため、3つのメジャーの中で最も数字が大きい
- 毎月のSignup顧客 + Trial顧客の合計が[# Customers]になるが、合計値同士(30 + 50)を合計しても一致しない
- 合計で見た場合、[# Customers]と[# Customers_Trial]は同じ50人。なぜなら、全ての顧客がSignup客になる前に、トライアルを申し込んでいるから
- Signupした顧客は最終的に30名であるが、各月の数字を見るといずれも30人未満。これは、計30人のSignup客のうち、何名かが解約したことを意味するが、上表だけではその人数をキャッチすることができない
- トライアル客の増加に比例して、Signup客も増加傾向であるが、12月以降から急激に落ち込んでいる(22年1月は多いように見えるが、実際には12月からトライアルを開始した人が2ヵ月目のトライアル期間に入っただけ)
- それでもSignup客が毎月増加傾向にあるのは、解約率が低い可能性が高い
3つのメジャーを作っただけで、これだけの情報と洞察が得られるのですが、Power BIでメジャーを作ったらまずは
- 探索的分析をやってみる
- 異なる切り口からいろいろ吟味する
の2つをやっておくことが非常に重要です。異なる切り口で言えば、例えば上記3つのメジャーを以下の切り口で見てみると更に面白くなります。この場合、CustomerIDやCountryといった切り口はdCustomerというテーブルの顧客情報であり、こちらは全て項目と合計の数字は一致します。
私はデータ分析をメインでやってきた人ですので、こうした部分で時間を使うようにしています。特にPower BIのようなセルフサービスBI(SSBI)はビジネスユーザー向けに簡単に分析できる環境を提供してくれますので、非常に便利な世の中になったと思います。
- 前月有料顧客数(# Customers_Signup.PM)
# Customers_Signup.PM =
CALCULATE(
[# Customers_Signup],
DATEADD( 'dCalendar'[Date], -1, MONTH )
)
//前月の有料顧客。合計値は集計できない、かつ、最終月とも一致しない
前月のSignup客数は重要な指標ですので、予め作っておきます。なお、DATEADDはTime Intelligence関数で日付テーブル(dCalendar)が必要で、良く使用されるDAX関数の1つです。この例では前月時点の日付テーブルでフィルターされた結果が算出されています(下図)。日付テーブルが連続した日付になっていないため、最初はTime Intelligence関数を使用できないかと思いましたが、今のところは大丈夫そうです。
ここで[# Customers_Signup.PM]の合計値が27となっているのに戸惑うかもしれません。単月で見た場合、赤枠内の数字は[# Customers_Signup]の前月の数字を反映していますが、27という数字はどこから出てきたのでしょうか?これを理解するためには、「合計」がどのような条件でフィルターされているかを理解する必要がありますが、Tabular Editor 3(以降「TE3」)のDAX debuggerという機能で簡単に知ることができます。
下図の通り、どうやら13ヵ月間におけるSignup客をカウントしていたようです。TE3は有料、TE2がオープンソースで無料ですが、この機能はTE3にしかありませんので、興味がある方は下記リンクより覗いてみてください。
エンタープライズBIの世界では伝説の1人であるDaniel Otykierさんの最高傑作です。
# New Customers =
//CallbackDataIDが発生するが、CustomerIDが少ないため、クエリパフォーマンスは良い
CALCULATE(
SUMX(
Sales,
var _current_date = Sales[Date]
var _first_signup_date =
CALCULATE(
MIN( Sales[Date] ),
ALLEXCEPT( Sales, dCustomer ),
dEventType[EventType] = "Signed-up"
)
var _result = INT( _current_date = _first_signup_date )
return _result
),
Sales[MonthlyPrice] > 0
)
# Lost Customers =
//解約した顧客数 = 先月顧客 + 新規顧客 - 今月顧客
//CallbackDataIDが発生するが、クエリパフォーマンスは良い
var _table1 =
CALCULATETABLE(
ADDCOLUMNS(
VALUES( dCalendar[Date] ),
"@CustomerPM", [# Customers_Signup.PM],
"@CustomerNew", [# New Customers],
"@CustomerCM", [# Customers_Signup]
),
Sales[MonthlyPrice] > 0
)
var _table2 =
ADDCOLUMNS(
_table1,
"@CustomerLost", [@CustomerPM] + [@CustomerNew] - [@CustomerCM]
)
var _result = SUMX( _table2, [@CustomerLost] )
return _result
- プレミアムプラン顧客数(# Customer_Premium)
# Customer_Premium =
CALCULATE(
[# Customers_Signup],
KEEPFILTERS( 'dPricing'[PriceType] = "Premium" )
)
//Premiumプランの客数
- プレミアム顧客割合(% Customer_Premium)
% Customer_Premium = DIVIDE( [# Customer_Premium], [# Customers_Signup] )
//Premiumプランの客数構成
- 維持顧客数(# Customers Retained)
# Customers Retained =
CALCULATE (
[# Customers_Signup] - [# New Customers],
LASTDATE ( dCalendar[Date] )
)
//顧客数 - 新規顧客にて算出
- 顧客リテンション率(Customer Retention)
Customer Retention =
IF(
NOT ISINSCOPE( dPricing[PriceType] ) &&
NOT ISINSCOPE( dEventType[EventType] ),
DIVIDE( [# Customers Retained], [# Customers_Signup.PM] )
)
//顧客維持率 = 維持顧客数(今月の顧客数 - 今月の新規顧客数) ÷ 前月の顧客数)
Customer Churn =
IF ( NOT ISBLANK ( [Customer Retention] ), 1 - [Customer Retention] )
//顧客数ベースのChurn
ここまで紹介した指標は全て顧客ベース(人数ベース)となります。中でも新規顧客数と解約顧客数のDAX式がやや複雑かと思いますが、データモデル(リレーションシップ)とDAX関数に対する理解が必要となります。最後のメジャーはコンバージョン率(CVR)ですが、こちらが一番ややこしいです。
CVRを算出するためには、追加でメジャーが2つ必要であり、まずはそちらを共有しておきます。1つずつ、上から順番に作っていく必要があります。
- 有料サインアップ顧客数_CVR用(# Customers_Signup_forCVR)
# Customers_Signup_forCVR =
VAR _signup_date =
TREATAS ( VALUES ( Sales[Date] ), dCustomer[StartDate] )
VAR _customer =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
KEEPFILTERS ( dEventType[EventType] = "Signed-up" ),
_signup_date
)
VAR _result =
COUNTROWS ( _customer )
RETURN
_result
//各月で有料サインアップした顧客数(同じ顧客が複数の月に出現することはない)
//CVR(Conversion Rate)計算用メジャー。合計値は各月の数字の集計と一致
- トライアル顧客数_CVR用(# Customers_Trial_forCVR)
# Customers_Trial_forCVR =
CALCULATE (
VAR _trial_date =
TREATAS ( VALUES ( Sales[Date] ), dCustomer[TrialDate] )
VAR _customer =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
KEEPFILTERS ( dEventType[EventType] = "Trial" ),
_trial_date
)
VAR _result =
COUNTROWS ( _customer )
RETURN
_result,
DATEADD ( dCalendar[Date], -2, MONTH )
)
//CVR(Conversion Rate)計算用メジャー
//各月でTrialに申し込んだ顧客数(同じ顧客が複数の月に出現することはない)
//dCustomerの全顧客数と一致し、合計値も各月の数字の集計と一致
//# Customer_Signup_forCVRと比較できるようにするため、意図的に同じ月にシフトさせている
- コンバージョン率(Conversion Rate)_正
Conversion Rate =
DIVIDE ( [# Customers_Signup_forCVR], [# Customers_Trial_forCVR] )
//各月に有料Signupした顧客(月別顧客ID重複なし) ÷ その2ヵ月前のトライアル顧客(同前)
- コンバージョン率(Conversion Rate)_誤
Conversion Rate.Wrong =
VAR _total_customers =
CALCULATE ( [# Customers], DATEADD ( dCalendar[Date], -2, MONTH ) )
VAR _current_customers = [# Customers_Signup]
VAR _result =
DIVIDE ( _current_customers, _total_customers )
RETURN
_result
//誤りのConvesion Rate。理由は_current_customersが複数の月に渡って登場しており、実態を反映したコンバージョン率を算出できていないため
最後のCVRについて、少し解説をします。この2つの指標をSignup客とTrial客も含め、表にすると以下のようになります。
Conversion Rateの解説
正しい結果のロジックは、毎月トライアルを開始した顧客だけを月別に抽出し、それと2ヶ月後に有料サインアップをした顧客と比較して算出しています。ポイントはApple-to-appleの比較になっていることであり、2021年2月のトライアル顧客は同年4月に1回のみ出現し、それ以降は出現していないことです。これにより、全てのトライアル顧客に対して、有料顧客へコンバートできたかどうかを把握できるようになります。
なお、冒頭で、[# Customers]や[#Customers_Signup]等のメジャーは月別の集計と合計が一致しないと言いましたが、この〇_forCVRの2つのメジャー(①と②)は重複CustomerIDを排除して算出されているため、合計と各月の集計が一致します(下図)。
一方、誤ったConversion Rateでは重複を全て加味しており、合計では同じ結果でもその他の月では間違った結果を算出しています。このロジックはデータモデルを変更することでも対応できると思いますが、余計なテーブルを作る必要がないDAXのほうが便利かもしれません。
DAXスクリプト
最後に、TE3を使っている人向けに、DAXスクリプトを残しておきます。
-------------------------
-- Measure: [# Customers]
-------------------------
MEASURE 'Sales'[# Customers] =
DISTINCTCOUNT ( Sales[CustomerID] )
//トライアル客を含む顧客数
DisplayFolder = "Customers"
FormatString = "#,0"
--------------------------------
-- Measure: [# Customers_Signup]
--------------------------------
MEASURE 'Sales'[# Customers_Signup] =
CALCULATE (
[# Customers],
KEEPFILTERS ( 'dEventType'[EventType] = "Signed-up" )
)
//有料顧客数(通常の顧客数メジャー)。合計値は集計できない、かつ、最終月とも一致しない
DisplayFolder = "Customers"
FormatString = "#,0"
-------------------------------
-- Measure: [# Customers_Trial]
-------------------------------
MEASURE 'Sales'[# Customers_Trial] =
CALCULATE ( [# Customers], KEEPFILTERS ( 'dEventType'[EventType] = "Trial" ) )
//無料Trial顧客数。Trialは2ヵ月のため、同じ顧客が前後の月にて重複出現する
DisplayFolder = "Customers"
FormatString = "#,0"
-----------------------------------
-- Measure: [# Customers_Signup.PM]
-----------------------------------
MEASURE 'Sales'[# Customers_Signup.PM] =
CALCULATE ( [# Customers_Signup], DATEADD ( 'dCalendar'[Date], -1, MONTH ) )
//前月の有料顧客。合計値は集計できない、かつ、最終月とも一致しない
DisplayFolder = "Customers"
FormatString = "0"
-----------------------------
-- Measure: [# New Customers]
-----------------------------
MEASURE 'Sales'[# New Customers] =
//CallbackDataIDが発生するが、CustomerIDが少ないため、クエリパフォーマンスは良い
CALCULATE (
SUMX (
Sales,
VAR _current_date = Sales[Date]
VAR _first_signup_date =
CALCULATE (
MIN ( Sales[Date] ),
ALLEXCEPT ( Sales, dCustomer ),
dEventType[EventType] = "Signed-up"
)
VAR _result =
IF ( _current_date = _first_signup_date, 1 ) // var _result = INT( _current_date = _first_signup_date )
RETURN
_result
),
Sales[MonthlyPrice] > 0
)
DisplayFolder = "Customers"
FormatString = "#,0"
------------------------------
-- Measure: [# Lost Customers]
------------------------------
MEASURE 'Sales'[# Lost Customers] =
//解約した顧客数 = 先月顧客 + 新規顧客 - 今月顧客(解約顧客含む)
//CallbackDataIDが発生するが、クエリパフォーマンスは良い
VAR _table1 =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( dCalendar[Date] ),
"@CustomerPM", [# Customers_Signup.PM],
"@CustomerNew", [# New Customers],
"@CustomerCM", [# Customers_Signup]
),
Sales[MonthlyPrice] > 0
)
VAR _table2 =
ADDCOLUMNS (
_table1,
"@CustomerLost",
[@CustomerPM] + [@CustomerNew] - [@CustomerCM]
)
VAR _result =
SUMX ( _table2, [@CustomerLost] )
RETURN
_result
//下記は合計値一致しないパターン
// VAR _previous_month_customers = [# Customers_Signup.PM]
// VAR _new_customers = [# New Customers]
// VAR _current_month_customers = [# Customers_Signup]
// VAR _result = _previous_month_customers + _new_customers - _current_month_customers
// RETURN
// _result
DisplayFolder = "Customers"
FormatString = "#,0"
--------------------------------
-- Measure: [# Customer_Premium]
--------------------------------
MEASURE 'Sales'[# Customer_Premium] =
CALCULATE (
[# Customers_Signup],
KEEPFILTERS ( 'dPricing'[PriceType] = "Premium" )
)
//Premiumプランの客数
DisplayFolder = "Customers"
FormatString = "#,0"
--------------------------------
-- Measure: [% Customer_Premium]
--------------------------------
MEASURE 'Sales'[% Customer_Premium] =
DIVIDE ( [# Customer_Premium], [# Customers_Signup] )
//Premiumプランの客数構成
DisplayFolder = "Customers"
FormatString = "0.0%;-0.0%;0.0%"
----------------------------------
-- Measure: [# Customers Retained]
----------------------------------
MEASURE 'Sales'[# Customers Retained] =
CALCULATE (
[# Customers_Signup] - [# New Customers],
LASTDATE ( dCalendar[Date] )
)
//顧客数 - 新規顧客にて算出
DisplayFolder = "Customers"
FormatString = "#,0"
--------------------------------
-- Measure: [Customer Retention]
--------------------------------
MEASURE 'Sales'[Customer Retention] =
IF (
NOT ISINSCOPE ( dPricing[PriceType] ) && NOT ISINSCOPE ( dEventType[EventType] ),
DIVIDE ( [# Customers Retained], [# Customers_Signup.PM] )
)
//顧客維持率 = 維持顧客数(今月の顧客数 - 今月の新規顧客数) ÷ 前月の顧客数)
DisplayFolder = "KPI"
FormatString = "0.0%;-0.0%;0.0%"
----------------------------
-- Measure: [Customer Churn]
----------------------------
MEASURE 'Sales'[Customer Churn] =
IF ( NOT ISBLANK ( [Customer Retention] ), 1 - [Customer Retention] )
//顧客数ベースのChurn(Cusomter Churn2と同じ結果)
DisplayFolder = "KPI"
FormatString = "0.0%;-0.0%;0.0%"
---------------------------------------
-- Measure: [# Customers_Signup_forCVR]
---------------------------------------
MEASURE 'Sales'[# Customers_Signup_forCVR] =
VAR _signup_date =
TREATAS ( VALUES ( Sales[Date] ), dCustomer[StartDate] )
VAR _customer =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
KEEPFILTERS ( dEventType[EventType] = "Signed-up" ),
_signup_date
)
VAR _result =
COUNTROWS ( _customer )
RETURN
_result
//各月で有料サインアップした顧客数(同じ顧客が複数の月に出現することはない)。CVR(Conversion Rate)計算用メジャー。合計値は各月の数字の集計と一致
DisplayFolder = "Customers"
FormatString = "#,0"
--------------------------------------
-- Measure: [# Customers_Trial_forCVR]
--------------------------------------
MEASURE 'Sales'[# Customers_Trial_forCVR] =
CALCULATE (
VAR _trial_date =
TREATAS ( VALUES ( Sales[Date] ), dCustomer[TrialDate] )
VAR _customer =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
KEEPFILTERS ( dEventType[EventType] = "Trial" ),
_trial_date
)
VAR _result =
COUNTROWS ( _customer )
RETURN
_result,
DATEADD ( dCalendar[Date], -2, MONTH )
)
//CVR(Conversion Rate)計算用メジャー
//各月でTrialに申し込んだ顧客数(同じ顧客が複数の月に出現することはない)。dCustomerの全顧客数と一致
//# Customer_Signup_forCVRと比較できるようにするため、意図的に同じ月にシフトさせている。合計値は各月の数字の集計と一致
DisplayFolder = "Customers"
FormatString = "0"
-----------------------------
-- Measure: [Conversion Rate]
-----------------------------
MEASURE 'Sales'[Conversion Rate] =
DIVIDE ( [# Customers_Signup_forCVR], [# Customers_Trial_forCVR] )
//各月に有料Signupした顧客(月別顧客ID重複なし) ÷ その2ヵ月前のトライアル顧客(同前)
DisplayFolder = "KPI"
FormatString = "0.0%;-0.0%;0.0%"
-----------------------------------
-- Measure: [Conversion Rate.Wrong]
-----------------------------------
MEASURE 'Sales'[Conversion Rate.Wrong] =
VAR _total_customers =
CALCULATE ( [# Customers], DATEADD ( dCalendar[Date], -2, MONTH ) )
VAR _current_customers = [# Customers_Signup]
VAR _result =
DIVIDE ( _current_customers, _total_customers )
RETURN
_result
//誤りのConvesion Rate。理由は_current_customersが複数の月に渡って登場しており、実態を反映したコンバージョン率を算出できていないため
DisplayFolder = "KPI"
FormatString = "#,0.0%;-#,0.0%;#,0.0%"
上記ソーコードを全てコピーし、以下のように実行します。
- SaaS_Analytics_dummy.pbixを開く
- Tabular Editor 3を立ち上げる
- New DAX Scriptのアイコンをクリック
- ソースコードを張り付け、Apply & Sync(実行)を押す
-
SaaS_Analytics_dummy.pbixに戻り、Salesテーブルをチェック
以上で、TE3を使ったメジャー構築は終了です。次回はDAXで定義するSaaSの収益指標について同じように見ていきたいと思います。
次回は下記より
marshal115.hatenablog.com