DAXを使いコホート分析~顧客のリピート状況を把握~

【もしエクセルで行おうとしたら、非常に手間なリピート状況の把握をDAXを使用して一発で解決】

前回は、新規と既存をDAXで集計しました。今回は同じ条件で顧客を並べた上でリピート状況をDAXで把握できるようにします!

上の画像のマトリックス表では、縦に顧客を初回受注月で並べ、横は初回受注月の経過月(1~12)別にリピート率を並べています

下の上の画像のズームアップを見てください

例えば、初回の受注月が2019年4月だった会員がもし100名いたとしたら、その100名のうち次の月に受注があったのは33名ということになります

このマトリックス表を作成することで、単に金額の増減を把握するだけではわからない、具体的な増減の要因をつかめるようになります

使用データ

使用するデータには、会員別の受注データを使用します

OrderData

ポイント

*****Sponsered Link***** ************************

作成は3段階になります

1段階目で、新しい列を追加します

OrderData

会員毎の初回受注日を抽出する列を作成し、前述のマトリックス表の縦軸にします

2段階目で横軸にする連番をテーブルで作成します

3段階目でリピート率を計算するDAXを作成します

こちらのDAXは1段階目、2段階目で作成した縦横の軸を組み入れます

会員毎の初回受注日の抽出

CALCULATE関数とMIN関数を使用して、会員毎の初回受注日を抽出する列を作成します

CALCULATE関数の第二引数の条件にはFILTER関数を使用して、会員毎のテーブルを繰り返し作成し、会員毎の初回受注日を繰り返し抽出します

ただリピート率の計算は月単位で行うので、最小受注日は月末に揃えます

月末に揃える作業はEOMONTH関数を使用して行います

FirstOrderDate =

①VAR CurrentCustomer=’OrderData'[会員番号]
RETURN
②CALCULATE(

EOMONTH(MIN(‘OrderData'[受注日]),0),

FILTER(‘OrderData’,’OrderData'[会員番号]=①CurrentCustomer)

)

EOMONTHは第二引数に0を指定することで、第一引数の日程の月末日を抽出します

連番テーブルの作成

マトリックス表の横軸になる連番(0~12,増分は1)を作成します

こちらは新しいテーブルの作成となります

テーブルの作成には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[会員番号]) 

)

マトリックスの作成

実際にマトリックス作成の際には、2つの作業を事前に行います

日付の変更

個人の好みにもよりますが、そのままですと縦軸の初回の受注月はJan、Febなどの英語名になります

ですので、事前に日付を変換しておくことをお勧めします

仮メジャーの作成

マトリックスの「行」には上記の画像のFirstOrderDate、もしくは変換した内容、列には2段階目で作成した0~12の連番を指定します

値には普通にいけば、リピート率を配置します

ただ、このケースの場合には一旦、仮で作成したメジャーを一旦配置します

この仮メジャーを配置した後、リピート率を計算するDAXを配置します

<まとめ>

今回の記事では、初回受注月で会員を並べ、横軸に経過月毎にリピート率を並べるコホート分析のためのDAXの作成方法を解説しました

実際の作成はマトリックスの縦軸、横軸と値の3段階で行います

DAX関数は様々な関数を組み合わせて作成しますが、特にFILTER関数をどう使うかがポイントとなります

FILTER(‘OrderData’,’OrderData'[会員番号]=①CurrentCustomer)

FILTER(‘OrderData’,

EOMONTH(‘OrderData'[受注日],0)=EOMONTH(B: CurrentFirstMonth,A: CurrentMonthAfter) 

上記2つのFILTER関数はCALCULATE関数と組み合わせますが、この組み合わせは色々な場面で使用できるのでぜひ覚えておきましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

*****Sponsered Link***** ************************

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です