【分析】RFM分析の「R」を集計する

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

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

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

 このRecencyとは、会員IDが含まれる注文データ内に出現する「顧客ID別の最終注文日」のことです

 まず何故、このRecency/最終注文日を分析するかという話しをしたいと思います

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

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

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

 例として最終注文日が1日前の顧客グループと、1年前の顧客グループを比較して考えて見ましょう

 最終注文日が1日前の顧客グループであれば、集計日段階では「稼働」の状態であり、こちらからアクションをしなくても再度リピートしてもらえる可能性が高いです

 一方、1年前の顧客グループに対しては「休眠」している可能性が高く、何らかのアクションをしないと、再度リピートしてもらえる可能性が低いです

 このように、顧客の最終日がいつか?によりアクションをすべき内容が違ってくるのです

今回はこの「最終注文日」をPower Queryでサクッと集計する方法を解説します

 こちらの「最終注文日」を集計する処理は、ピボットテーブルでも集計は可能ですが、Power Queryで行うと大きなメリットがあります

 Power Queryでは集計内容をテーブルに直接読込を行うことができるため、直接データを編集することができます

 一方、ピボットテーブルだとシートの別な場所にコピーしないとデータの編集が行えないのです

今回の使用データと行いたい事

今回の解説で使用するデータは、次の画像の注文データです

注文データは、注文日が2021年1月から3月までの期間で集計されています

 ですので、注文データ内では1つの顧客IDに対して、上の画像のように複数の注文日が存在したりしています

 これらの「1顧客IDに対して複数存在する注文日」の中から「最終注文日」を顧客IDごとに集計することが今回行いたい事です

 例えば、上の画像であれば「顧客ID / CO6324」の最終注文日「2021/03/12」を集計します

最終注文日の集計

では、実際に使用データから最終注文日を顧客ID毎に集計します

解説は、エディタ上から始めます

画像に alt 属性が指定されていません。ファイル名: エディタ-2.jpg

 こちらの集計処理は、過去の記事でも紹介したグループ化により一瞬で終了します

まずは「ホーム」タブの「グループ化」をクリックします

するとグループ化・画面が開くので、次の画像のように各項目を設定します

グループ化項目:顧客IDの列

新しい列名:最終日

操作:最大

列:注文日

この上記の設定により、顧客ID別に最大/最終の注文日が集計されます

 例えば、3つ注文履歴がある顧客ID:C06234であれば下のGIF画像のように「2021/03/12」のみが集計されます

<まとめ>

 今回は、グループ化機能により注文データから顧客ID毎に最終注文日を集計しました

 グループ化・画面にて集計方法(操作)を「最大」で指定すれば、瞬時に最終注文日を集計できます

尚、「最終注文日」を集計した後は「最終注文日からの経過日数」を集計します

こちらについては、起算日を指定する必要があると思います

起算日の指定方法は主に2つあります

本日の日付を指定するM関数を使用する ➡ DateTime.LocalNow

・カスタム列・作成画面で日付を指定する ➡ #date(年,月,日)

今回は、#dateで日付を指定してみます

 上のGIF画像ではカスタム列・作成画面にて[最終注文日]から2021年5月14日の日付を差し引いています

 ちなみに、新たに作成された列は小数点が付いているので、後で整数に直す必要があります

  

 顧客ID別に「最終注文日からの日数」を集計し、過去の記事で紹介したABC分析の内容と顧客IDと紐づければ、顧客ランクとRecencyの関係性を分析することができます!

ぜひ実践してみてください!

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


にほんブログ村

コメントを残す

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