カテゴリー別アーカイブ: 分析

DAXを使用して注文数毎に会員を分類する~会員のセグメント~

【IF式をわざわざ重ねなくても、DAXを使用すれば注文数で会員をセグメントできます】

下の画像のようなデータから会員/Customer_IDを注文数でセグメントする場合はIF式やピボットテーブルを組み合わせる必要がありました

OrderData1
アイコン

Order-Segment 133.91 KB 1 downloads

...

DAXとパラメーターを使用すれば、簡単にセグメントが行えます

ポイント

今回は注文データからセグメントを行いますので、一旦、SUMMARIZE関数を使用して会員番号毎に注文数を紐づけるテーブルを作成するのがポイントです

上記のテーブルからFILTER関数を使い、注文数毎に会員数を集計します

パラメーターの作成

まずはFILTER関数内で、注文数のフィルターに使用するパラメーターを作成します

次の画像で、フィルターに設定する数値範囲を設定します

上記の数値範囲を設定すると2つのものが作成されます

1つは数値範囲を設定したテーブルです

もう一つはメジャーです

こちらのメジャーをFILTER関数内で使用します

セグメントをするメジャーの作成

顧客別注文数 =

①VAR CustomerOrders=   

SUMMARIZE(

‘OrderData1’,

OrderData1[Customer ID], 

“注文数”, 

COUNT(OrderData1[Order ID])

)

RETURN

②COUNTROWS(   

FILTER(

CustomerOrders,

[注文数]=③[パラメーター 値]

)

)

③は前述のパラメーターを作成した時のメジャーになります

画像に alt 属性が指定されていません。ファイル名: image-33-644x63.png

パラメーターで設定した値(1~10)毎(行毎に)に会員を抽出する形になります

FILTER(CustomerOrders,[注文数]=③[パラメーター 値])

会員を抽出したら②の「COUNTROWS関数」でセグメント毎の会員数を集計します

マトリックスの作成

最後にマトリックスを作成します

行には下の画像のパラメーターを使用します

画像に alt 属性が指定されていません。ファイル名: image-32.png

<まとめ>

今回は注文データから、注文数で会員をセグメントしたデータを作成します

エクセルのワークシートで行おうとすればIF関数やピボットテーブルを組み合わせる必要があります

DAXを使用する場合は、FILTER関数をうまく使えば簡単に行えます

アイコン

DAX 0.31 KB downloads

...

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

にほんブログ村

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

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

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

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

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

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

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

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

使用データ

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

OrderData
アイコン

OrderData 104.62 KB 2 downloads

...

ポイント

作成は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関数と組み合わせますが、この組み合わせは色々な場面で使用できるのでぜひ覚えておきましょう!

アイコン

DAX 0.65 KB 1 downloads

...

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

にほんブログ村

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

DAXを使い一発で新規顧客・既存顧客別に会員数と金額を集計する

【売上金額が単に増えた減ったとは別に新規と既存の切り口があると、金額の増減をストーリーで語れます】

数字の増減の理由を聞かれた時に、返答に困ったことはありませんか?

なかなか数字の増減の理由をつかめないのは、原因がずっと前に起こっていることもあるからです

例えば、ある企業の売上金額の80%が僅か顧客の20%から生まれていたとします

この優良顧客の離脱率は完全に0にはできません

しかも獲得した顧客もすぐに優良になるわけではありません。優良顧客になるまでに時間はかかるものです

優良顧客が離脱するペース以上に新規顧客を獲得して成長させないと売上は維持できません

ですので、新規顧客の獲得の減少が後々売上に響くこともあります

逆に新規顧客と既存顧客に分けた切り口で集計ができると、中長期期間での分析がしやすくなります

ところが、この新規既存の集計はエクセルのワークシートで行おうとすると猛烈に時間がかかります

今回の記事では、DAX一発で新規既存の数字を集計できるようにします

なお、新規と既存の切り口は売上金額の分析でなくても、在庫分析などでも活用できます

ポイント

新規と既存の違いとは何か?この定義がとても重要です

初回購入とはまた違いますので、その点は明確に意識しておきましょう!

次の図を見てみてください

対象期間より前に購入があった会員は新規と既存、どちらに区分されるでしょうか?

これは既存に区分されます

この「対象期間より前」の定義を数式で表すと次のようになります

<MIN(対象期間)

この数式を活用して既存会員をDAXで表現します

逆に新規はどう表現するか?

全会員-新規会員

上記で表現できますので、既存会員を式で表すことができれば難しい式を使う必要はありません

使用データ

使用するデータは次のデータになります

テーブル名:myData

横の軸は会員番号別の軸になります

4月1日から4日まで新規会員を1会員づつ獲得しており、新規会員からの金額は1日100となっています

上記のテーブルはPower BI内に取り込んだ後、ピボット解除で変換した上で使用します

尚、上記は売上データから新規・既存を集計しますが、会員データを別途作成しておくともう少しDAX式が簡単になります

会員データと売上データの間にリレーションを作成したうえで、DAXを使用する方法も少し紹介します

アイコン

DATA 21.38 KB 6 downloads

...

使用DAX(抜粋)

VALUES関数

今回のDAXではVAR/変数を使って複数のDAX関数を組み合わせます

その中の一つがVALUES関数です

VALUES関数はテーブル操作関数なので「新しいテーブル」を作成できます

上の画像では、全会員をテーブル化しています

CALCULATETABLE関数

こちらの関数はテーブル式をフィルター評価する関数です

CALCULATETABLE_NewTable = CALCULATETABLE(VALUES(myData[会員番号]),’myData'[日付]=MIN(‘myData'[日付]))

上のVALUES関数に加えて、フィルターを加えています

今回の記事では、この関数を使用して既存会員をテーブル化します

新規会員数

ポイントで前述したように、全会員から既存会員を引いたのが新規会員となります

NewCustomers =  
VAR CurrentCustomers= Values(myData[会員番号]) 

VAR CurrentDate=Min(myData[日付]) 

VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate) 

VAR NewCustomers=EXCEPT(①CurrentCustomers,③OldCustomers)

⑤RETURN COUNTROWS(④NewCustomers)

解説

①新規・既存会員を含めて全会員をリスト化します

②対象期間の最小日付、つまり新規と既存の境目となる日付を算出します

③②で算出した最小日付を基にして既存会員をテーブル化します

④①の全会員から③の既存会員をEXCEPT関数により差し引いて新規会員をテーブル化します

⑤COUNTROWS関数により、NewCustomersテーブルから新規会員数を出力します

既存会員数

上記の新規会員数を算出するDAX関数の一部を省略するだけです

OldCustomers =

① VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate)

④RETURN COUNTROWS(③OldCustomers)

新規会員金額

新規会員数を算出するDAXの最後のRETURNを変更します

 SUMX(NewCustomers,CALCULATE(SUM(myData[金額])))

SUMX関数を使用するのですが、第二引数でCALCULATE関数を使用することでNewCustomersテーブルの1行1行*を計算させるのがポイントです

行評価

NewCustomersAmount =

①VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<CurrentDate) 

④VAR NewCustomers=EXCEPT(CurrentCustomers,OldCustomers)

⑤RETURN     SUMX(NewCustomers,CALCULATE(SUM(myData[金額])))

既存会員金額

OldCustomersAmount =  

①VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate)

④RETURN SUMX(③OldCustomers,CALCULATE(SUM(myData[金額])))

会員データを使用するパターン

使用する会員データは次の通りとなります

新規会員数

NewCustomers =  

①VAR CurrentDates=VALUES(myData[日付])               

②RETURN COUNTROWS(FILTER(‘CustomerFirstSale1’,’CustomerFirstSale1′[最小] in ①CurrentDates))

既存会員数

OldCustomers =

①VAR CurrentDates=VALUES(myData[日付])

②VAR CurrentCustomers=COUNTROWS(VALUES(myData[会員番号]))

③VAR NewCustomers=COUNTROWS(FILTER(‘CustomerFirstSale1’, ‘CustomerFirstSale1′[最小] in ①CurrentDates))    

④RETURN ②CurrentCustomers-③NewCustomers

新規会員金額

NewCustomersAmount =

①VAR CurrentDates=VALUES(myData[日付]) 

②RETURN SUMX(FILTER(‘CustomerFirstSale1’,’CustomerFirstSale1′[最小] in CurrentDates),’MeasureTable (2)'[SalesAmount]*)

*事前にSUM関数で合計を計算するメジャーを作成しておきますSalesAmount = SUM(myData[金額])

既存会員金額

OldCustomersAmount =  

①VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate)

④RETURN     SUMX(③OldCustomers,CALCULATE(SUM(myData[金額])))

<まとめ>

今回の記事ではDAX関数を使用して、新規と既存を区分して集計する方法を解説しました

複数の関数を組み合わせて集計しますが、一番重要なのはMIN(対象期間)により全期間から新規と既存の境目となる「時」を算出することです

DAX関数を使用することで、エクセルで複数シートをまたいで処理するのとは違い、即時に新規と既存を集計できます

今回は新規と既存の切り口での集計を紹介しましたが、後日、新規と既存の切り口以上に重要なリピートについても解説を行います

アイコン

DAXコード 2.57 KB 4 downloads

...

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

にほんブログ村

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

超高速!パレード図を1つのメジャー一発で作成する方法

【分析を語る上で一番よく出てくるのがパレード図だと思います。実は実務上ではかなり作成が面倒なのですが、DAXを使用すれば一発で作成できます】
パレード図の便利さの一つは、分析の優先順位を付けれる点です

物理的には全ての客先、全ての商品を同じように対策を打つことはできません

パレード図では横棒グラフの並びと、累計の割合を示す折れ線グラフとの組み合わせにより、優先的に対策を打つべき箇所が分かります

ところが、エクセルのワークシートでパレード図を作ろうとするととても面倒です

特に累計の構成比を算出するのが面倒です

データの並び替え⇒構成比の算出⇒累計の構成比の算出などが「累計の構成比」の算出には必要です

仮に、データの追加があった場合には最初からやり直しになります

この累計の構成比メジャーで作成します

今回は以下の売上データから、客先別のパレード図をPower BIにて前述のDAX関数を組み合わせて作成します

ポイント

累計の構成を計算するので、数式は以下のようになります

・分子⇒累計金額

・分母⇒全合計金額

分母は問題になりませんが、分子が厄介です

エクセルワークシートであれば、下の図のように、機械的に上のセルを足して累計金額を計算します

メジャーで計算する場合にはポイントが2つあり、1つ目は個別金額のグループ化、2つ目は不等式を使う点です

仮に、金額が右のようになっているとしたら、A>B>C>D>E

Dの箇所の累計は>=Dのもの、つまりA+B+C+Dのグループを合計します

一番大きいAであれば、Aだけ、一番小さいEの場合には全てをグループ化して合計します

不等式を使うためには、AからEまで各々の合計額(複数)を事前に計算してグループ(複数)を準備しておくことが必要です

全体金額の計算

累計の構成比はmyParetoとして計算します

まずは、分母の金額を計算します

myPareto = VAR TotalRevenue=CALCULATE(SUM(‘売上データ3′[売上金額]),ALLSELECTED(‘売上データ3’))

単にSUM関数で計算するのではなく、スライサーなどによるフィルター処理にも備えて、CALCULATE関数とALLSELECTED関数の組み合わせで計算します

*VARとRETURNの使い方はこちらから

*ALL関数とALLSELECTED関数の使い分けはこちらから

都度金額の計算

後で不等式の中で比較のために使う都度金額/CurrentRevenueを計算しておきます

こちらは前述の画像の>=の右側の部分(D)になります

VAR CurrentRevenue= SUM(‘売上データ3′[売上金額])

個別金額のグループを計算

個別金額のグループは、SUMMARIZE関数を使用して作成します

*SUMMARIZE関数の使い方はこちらから

該当するのは前述の画像の「A~E」の部分です

話を分かりやすくするために、SUMMARIZE関数を使用して新たなテーブルを作成してみます

mySummarize = SUMMARIZE(‘売上データ3’,’売上データ3′[客先],”Revenue”,SUM(‘売上データ3′[売上金額]))

この関数により客先を重複なく並べた列を作成した上で、新たに「Revenue」という列で客先別の合計金額を並べています

こちらのSUMMARIZE関数を前述の式の中で使うと次のようになります

不等式を使った累計の計算

こちらはSUMX関数を使用します

*SUMX関数の使い方はこちらから

前述のように、累計は不等式を使ってグループを作成し、グループを合計して算出します

SUMX関数は2つ引数を使用しますが、1つ目の引数はグループを計算するFILTER関数になります

 FILTER(SummarizeTable, [Revenue]>=CurrentRevenue)

上記の式の[Revenue]は、前述のSUMMARIZE関数で算出したグループの各々の金額と考えてください

このFILTER関数により各々の金額のCurrentRevenue以上の金額がグループ化されます

そして、SUMX関数の第二引数とてして、合計する列[Revenue]を指定します

これでメジャー内の変数の指定が終わったので、Returnを指定します

Returnは「CumulativeSum/TotalRevenue」となります

グラフの作成

これで問題だった累計構成比が計算できましたので、最後にグラフを作成します

グラフは横棒グラフと折れ線グラフの組み合わせを選択します

X軸は客先、列のY軸は売上金額の合計を選択します

線のY軸には、先ほど作成したメジャーを設定します

作成したメジャー内では、分母にALLではなくALLSELECTED関数を使用しています

ですので、スライサーでフィルターしても、折れ線は最終的に100%になります

<まとめ>

今回はDAX関数を使用して、Power BIにてパレード図を作成する方法を解説しました

作成するDAX関数は複数の関数の組み合わせになり、複雑な面もありますが、一度作成してしまえば使いまわしていけます

エクセルワークシートで同じことを使用としたら、行数が可変になるので、相当面倒な作業になります

最後に、今回使用したDAX関数をメモ帳にて添付します

アイコン

累計構成比 0.96 KB 1 downloads

...

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