【分析】RFM分析の「M」を集計してまとめる

RFM分析は顧客を3つの指標で分類して、顧客別に施策を講じる手法です

Recency  いつ?、Frequency  頻度?、Monetary  いくら?

今回は、上のMonetaryを顧客別に集計する方法を解説させて頂きます

そしてMonetaryを抽出した後、前々回前回の分析内容もまとめて整理します

Monetaryとは?

 顧客が「どのくらいの金額を使ってくれているのか?」を分析することは当然、重要なことです

***【40代、50代歓迎】エクセルの学び直しを応援します!***

エクセルの進化を味方にして業務を効率化しましょう!

時間は30分/1,000円から!あなたの都合に合わせて相談になります!!平日の昼間も歓迎です! **********************************************************

今回は、データ内の顧客ID別に注文金額の総計を抽出します

使用するデータは前々回前回と同じく次のデータです

今回のポイント

 今回は、「前々回/Recency」と「前回/Frequency」に作成したクエリの内容を、「今回/Monetary」作成するクエリとともに、一つの「クエリ/まとめ」にまとめます

 今回の記事では、「まとめクエリ」を作成するにあたって2つのテクニックを活用します

1つ目は既に作成済みのクエリを参照・複製すること

2つ目はクエリの結合です

 Monetaryを抽出するにあたっては、シート上の元データから作業を始めるのでなく、Frequey/クエリを複製して活用します

 その後は、Recencyクエリを参照して他の「Frequency」と「Monetary」のクエリをまとめる「まとめクエリ」を作成します

 まとめクエリは「クエリの結合」の技術を使用して、「Frequeny」と「Monetary」の内容を抽出して紐づけます

Monetaryの抽出/クエリ作成

解説はPower Queryエディタ(以降エディタ)上からはじめます

まず、Frequencyのクエリを複製します

Frequencyクエリ上で右クリックして複製を選択します

複製されたクエリ上でF2キーを押し、名前を「Monetary」に変更します

複製されたクエリは適用したステップも複製されています(参照の場合は最終ステップが参照されているだけです)

上の画像の「グループ化された行」のステップを修正してMonetaryを抽出します

下の画像にて黄色にマークされた箇所をクリックします

開いたグループ化画面の各項目を修正します

新しい列名:頻度➡使用金額

操作:行数のカウント➡合計

:空欄➡注文金額

この際に、「並べ替えられた行」のステップは「頻度」という列がないためにエラーになりますので削除しておきましょう

エラーの原因のステップが削除されれば、下の画像のように「使用金額」が抽出されています

まとめクエリを作成

まず、前々回作成したRecencyクエリを参照します

Recencyクエリの参照を行ったら、Frequencyクエリと同じ様にF2キーにてクエリ名を「まとめ」に変更しましょう

 なお、Recencyについては、最終注文日でなく経過日(集計期間・最終日-最終注文日)をまとめる項目として使用します

 では、ここから2つのクエリを「クエリの結合」を通じて、「頻度」「使用金額」を紐づけます

 上の画像のように「顧客ID」を共通のキーにして紐づけ(結合)を行って行きます

<まとめ>

 今回は、RFM分析のMonetaryを抽出した上で前々回、前回のクエリ内容を紐づけました

Monetaryを抽出する上では、「複製」を使用しました

 そして複製したクエリの「グループ化」の内容を変更することによりMonetaryを抽出しました

そして最後は、顧客IDをキーにして「クエリの結合」をおこないました

 今回の解説ではRecency、Frequency、Monetaryをまとめただけですが、実際にまとめた内容を分析する上では、もう少し工夫が必要です

 RecencyやFrequency、Monetaryを更にグループ化して、グループ毎の違いを分析できるようにすべきです

この辺りはまた、別途、記事を書きたいと思います 

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


にほんブログ村

コメントを残す

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