BDAstyle

Business Data Analysis & Visualization with Excel

RFM分析［顧客購買履歴(利用実績)］ with Excel 1/3

Step 0シチュエーションの設定

ヘアサロンXは，200人のポイント会員のここ1年の利用記録を集計しました。具体的な集計項目は利用回数および利用金額の2つで，あわせて直近の利用日も抽出すると下の表(一部のみ)のようになりました。

DL

Xはこの結果をもとにRFM分析をおこなうため，RFM分析表を作成します。このとき，R, F, M各クラス分けの基準については，事前にPOSデータからパレートの法則に従って統計的に作成しておいた以下の基準を用いることとします。

  • 【クラスの数】 3つ(上位クラス:「3」～下位クラス:「1」)
  • R基準(R:Recency, 直近利用日からの経過日数)
    • R3クラス ―R≦45日
    • R2クラス ―45日＜R≦100日
    • R1クラス ―100日＜R
  • F基準(F:Frequency, 利用回数(累積))
    • F3クラス ―F≧9回
    • F2クラス ―9回＞F≧3回
    • F1クラス ―3回＞F
  • M基準(M:Monetary, 利用金額(累積))
    • M3クラス ―M≧80,000円
    • M2クラス ―80,000円＞M≧20,000円
    • M1クラス ―20,000円＞M

RFM分析表の作成

工程

Step 1見出しの作成

列見出し「期間最終日」および「経過日数」を作ります。

ここでいう「期間最終日」とは，分析対象期間の中で最もあたらしい日付のことを指しています(つまり当該期間における最後の日付のことです)。また「経過日数」とは「直近利用日」より「期間最終日」時点で何日が経過しているかを示すものです。

Step 2期間最終日の入力

セルF2に，このケースでの期間最終日である「2013/12/23」を入力します。

Step 3経過日数の計算

最初の顧客の「経過日数」をDATEDIF関数を使って求めます。DATEDIF関数は「関数の挿入ウイザード」では入力できませんので，該当するセルに直接計算式を入力していきます。引数は (開始日, 終了日, 単位) となっており，「単位」は"d"として日数で求めるよう指定します。また，この例では後に式をコピーすることを考慮して，終了日を絶対参照にしておきます。

セルF4 =DATEDIF(B4, $F$2, "d")

入力後，計算式を左表の最終行までコピーします。

【参考】日数のカウント

期間の計算は総日数か営業日かどちらを基準とするか，という選択があります。冒頭のようなクラス基準は通常どちらかを想定して作られます。したがって計算方法も指定されていることと思います。

このケースでは単純に総日数で計算するものとしますが，もし，営業日ベースでとなると，どうでしょうか。

たとえば，土日休みの環境ではNETWORKDAYS関数が役に立つと思います。その他の環境では，やはり，別途カレンダーを用意して営業日をExcelにカウントさせるといった方法がbetterかと思います(営業日数をExcelで計算する)。

Step 4RFM分析表・見出しの作成

セルH3～K3にあたらしく見出しを作成します。順に「ID」「R」「F」「M」と入力します。

Step 5RFM分析表・顧客名の転記

「ID」列のセルH4に，下のようにID(顧客名)を転記する式を入力します。

セルH4 =A4

つづいてこれを左表の最終行までコピーします。

Step 6RFM分析表・R得点の計算

「R」列のセルI4に，最初のIDのR得点を求めます。頁頭・シチュエーション設定の「R基準」より，計算式は

セルI4 =IF(F4<=45, 3, IF(F4<=100, 2, 1))

となります※。


※x=経過日数として，下図に「分岐の流れ」を併記しています。この例ではクラス3からIF関数を入れ子にして判別をおこなっています。

Step 7RFM分析表・R得点計算式のコピー

セルI4の計算式を表の最終行までコピーします。

Step 8RFM分析表・F得点の計算

「F」列のセルJ4に，最初のIDのF得点を求めます。頁頭・シチュエーション設定の「F基準」より，計算式は

セルJ4 =IF(C4>=9, 3, IF(C4>=3, 2, 1))

となります※。


※x=利用回数として，下図に「分岐の流れ」を併記しています。この例ではクラス3からIF関数を入れ子にして判別をおこなっています。

Step 9RFM分析表・F得点計算式のコピー

セルJ4の計算式を表の最終行までコピーします。

Step 10RFM分析表・M得点の計算

「K」列のセルK4に，最初のIDのM得点を求めます。頁頭・シチュエーション設定の「M基準」より，計算式は

セルK4 =IF(D4>=80000, 3, IF(D4>=20000, 2, 1))

となります※。


※x=利用金額として，下図に「分岐の流れ」を併記しています。この例ではクラス3からIF関数を入れ子にして判別をおこなっています。

Step 11RFM分析表・M得点計算式のコピー

セルK4の計算式を表の最終行までコピーします。

Step 12RFM分析表の完成

RFM分析表の完成です(下表はその一部です。設定に従えば，実際には200人分のデータが存在します)。

Next

次は，セグメント別に顧客データを振り分ける2つの方法です。

その他の参照