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

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

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

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

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

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

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

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

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

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

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

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

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

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

ポイント

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

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

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

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

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

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

<MIN(対象期間)

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

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

全会員-新規会員

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

使用データ

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

テーブル名:myData

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

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

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

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

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

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

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

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

にほんブログ村

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

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

「DAXを使い一発で新規顧客・既存顧客別に会員数と金額を集計する」への2件のフィードバック

  1. いつも精力的なブログ等を書かれていて、参考にさせていただいています。

    私は、パワークエリとOldピボットを愛用しています。
    これでできない場合は、Power Pivotを使います。
    今回のDAX関数は、私の理解を超えていて、
    困ってしまいました。

    サンプルのパワークエリで、最小日数のマスタを作成されていたので、もう一つ「クエリ」を追加してみました。

    最小日付をマージして、
    日付=最小日数なら「新規」、それ以外なら「既存」とすれば、DAX関数を使わなくても。クエリができます。
    このデータから、ピボットテーブルで、レポートができました。

    ご参考にしていただければ、幸いです。
    今後とも、よろしくお願いします。

    let
    ソース = myData,
    ピボット解除された他の列 = Table.UnpivotOtherColumns(ソース, {“日付”}, “属性”, “値”),
    マージされたクエリ数 = Table.NestedJoin(ピボット解除された他の列, {“属性”}, CustomerFirstSale, {“会員番号”}, “CustomerFirstSale”, JoinKind.LeftOuter),
    #”展開された CustomerFirstSale” = Table.ExpandTableColumn(マージされたクエリ数, “CustomerFirstSale”, {“最小”}, {“最小”}),
    追加された条件列 = Table.AddColumn(#”展開された CustomerFirstSale”, “新規”, each if [日付] = [最小] then “新規” else “既存”)
    in
    追加された条件列

    1. 返信ありがとうございます

      いつもご愛読頂き誠にありがとうございます

      返信頂いた内容は新規と既存ではなく、初回の話になると思います
      この点だけ付け加えさせてください

      新規は集計期間(例えば、4月1日~4月30日)内に初回があった会員が新規になります
      ですので、4月2日に初回の注文があった人は4月1日~4月30日の集計期間では新規になります

      一方、5月1日~5月31日を集計期間とした場合は4月2日に初回の人は既存になります

      Power Queryでも新規と既存の集計はできますが、フィルターやスライサーと連動して集計できないので、今、DAXでの分析に力を入れています

      何卒よろしくお願いします

コメントを残す

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