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

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

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

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

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

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

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

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

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

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

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

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

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

ポイント

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

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

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

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

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

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

<MIN(対象期間)

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

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

全会員-新規会員

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

使用データ

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

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

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

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

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

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

DATA

1 ファイル 21.38 KB

ダウンロード

使用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コード

1 ファイル 2.57 KB

ダウンロード

にほんブログ村