【もしエクセルで行おうとしたら、非常に手間なリピート状況の把握をDAXを使用して一発で解決】
前回は、新規と既存をDAXで集計しました。今回は同じ条件で顧客を並べた上でリピート状況をDAXで把握できるようにします！
上の画像のマトリックス表では、縦に顧客を初回受注月で並べ、横は初回受注月の経過月（１～１２）別にリピート率を並べています
下の上の画像のズームアップを見てください
例えば、初回の受注月が２０１９年４月だった会員がもし１００名いたとしたら、その１００名のうち次の月に受注があったのは３３名ということになります
このマトリックス表を作成することで、単に金額の増減を把握するだけではわからない、具体的な増減の要因をつかめるようになります
目次
使用データ
使用するデータには、会員別の受注データを使用します
ポイント
作成は３段階になります
１段階目で、新しい列を追加します
会員毎の初回受注日を抽出する列を作成し、前述のマトリックス表の縦軸にします
２段階目で横軸にする連番をテーブルで作成します
３段階目でリピート率を計算するDAXを作成します
こちらのDAXは１段階目、２段階目で作成した縦横の軸を組み入れます
会員毎の初回受注日の抽出
CALCULATE関数とMIN関数を使用して、会員毎の初回受注日を抽出する列を作成します
CALCULATE関数の第二引数の条件にはFILTER関数を使用して、会員毎のテーブルを繰り返し作成し、会員毎の初回受注日を繰り返し抽出します
ただリピート率の計算は月単位で行うので、最小受注日は月末に揃えます
月末に揃える作業はEOMONTH関数を使用して行います
FirstOrderDate =
①VAR CurrentCustomer=’OrderData'[会員番号]
RETURN
②CALCULATE(
EOMONTH(MIN(‘OrderData'[受注日]),0),
FILTER(‘OrderData’,’OrderData'[会員番号]=①CurrentCustomer)
)
EOMONTHは第二引数に０を指定することで、第一引数の日程の月末日を抽出します
連番テーブルの作成
マトリックス表の横軸になる連番（０～１２，増分は１）を作成します
こちらは新しいテーブルの作成となります
テーブルの作成にはGENERATESERIES関数を使用します
MonthAfter = GENERATESERIES(0,12,1)
リピート率を計算するDAXの作成
変数の作成
まず縦横の位置を変数で指定します
A: VAR CurrentMonthAfter=SELECTEDVALUE(MonthAfter[Value])
B: VAR CurrentFirstMonth=SELECTEDVALUE(OrderData[FirstOrderDate])
マトリックスの位置の行を抽出
テーブル：OrderDataから前述のAとBを組み合わせた位置に該当する行を、FILTER関数を使用して抽出します
この時に、EOMONTH関数を使用して月末日に揃えて行を抽出するのもポイントです
FILTER(‘OrderData’, EOMONTH(‘OrderData'[受注日],0)=EOMONTH(B: CurrentFirstMonth,A: CurrentMonthAfter))
CALCULATE関数による会員数抽出
上記のFILTER関数により抽出した行から、CALCULATE関数とDISTINCTCOUNT関数を使用して該当する会員数を抽出します
CALCULATE(
DISTINCTCOUNT(‘OrderData'[会員番号]),
FILTER(‘OrderData’,
EOMONTH(‘OrderData'[受注日],0)=EOMONTH(CurrentFirstMonth,CurrentMonthAfter)
) ),
DIVIDE関数による率の計算
DIVIDE関数によって、分子：マトリックスの位置の会員数と分母：全体の会員数（実際には行でフィルターされる）で割ります
CustomerRetension% =
A: VAR CurrentMonthAfter=SELECTEDVALUE(MonthAfter[Value])
B: VAR CurrentFirstMonth=SELECTEDVALUE(OrderData[FirstOrderDate])
RETURN
DIVIDE(
CALCULATE(
DISTINCTCOUNT(‘OrderData'[会員番号]),
FILTER(‘OrderData’,
EOMONTH(‘OrderData'[受注日],0)=EOMONTH(B: CurrentFirstMonth,A: CurrentMonthAfter)
)
),
DISTINCTCOUNT(OrderData[会員番号])
)
マトリックスの作成
実際にマトリックス作成の際には、２つの作業を事前に行います
日付の変更
個人の好みにもよりますが、そのままですと縦軸の初回の受注月はJan、Febなどの英語名になります
ですので、事前に日付を変換しておくことをお勧めします
仮メジャーの作成
マトリックスの「行」には上記の画像のFirstOrderDate、もしくは変換した内容、列には２段階目で作成した０～１２の連番を指定します
値には普通にいけば、リピート率を配置します
ただ、このケースの場合には一旦、仮で作成したメジャーを一旦配置します
この仮メジャーを配置した後、リピート率を計算するDAXを配置します
＜まとめ＞
今回の記事では、初回受注月で会員を並べ、横軸に経過月毎にリピート率を並べるコホート分析のためのDAXの作成方法を解説しました
実際の作成はマトリックスの縦軸、横軸と値の３段階で行います
DAX関数は様々な関数を組み合わせて作成しますが、特にFILTER関数をどう使うかがポイントとなります
FILTER(‘OrderData’,’OrderData'[会員番号]=①CurrentCustomer)
FILTER(‘OrderData’,
EOMONTH(‘OrderData'[受注日],0)=EOMONTH(B: CurrentFirstMonth,A: CurrentMonthAfter)
)
上記２つのFILTER関数はCALCULATE関数と組み合わせますが、この組み合わせは色々な場面で使用できるのでぜひ覚えておきましょう！
コメントを残す