Excelでの分析の位置づけ
RFM分析を始めるにあたり、最初から高額なBIツールやCRMシステムを導入する必要はありません。Excelは、「まずRFM分析を体験し、自社データで仮説を検証する」ための最良のスタートポイントです。
Excelでの分析が特に有効なのは以下のケースです。
- 顧客数が数千〜1万件程度の小〜中規模ECサイト
- RFM分析を社内に提案するために、まずプロトタイプを作りたい場合
- 分析チームのリソースが限られ、新しいツールの学習コストをかけられない場合
- 経営層に「こういう分析ができる」というデモを見せて予算を取りたい場合
逆に、顧客数が数万件を超える場合や、週次以上の頻度で更新したい場合は、最初から専用ツールを検討する方が効率的です。Excelはあくまで「第一歩」であり、「最終形」ではないことを認識しておきましょう。
必要なデータ項目と準備
Excelでの分析に必要な元データは、シンプルに「購買履歴データ」です。以下の項目が最低限必要です。
- 顧客ID：会員番号やメールアドレスなど、顧客を一意に識別できるキー
- 購入日：各注文の日付（yyyy/mm/dd形式）
- 購入金額：各注文の合計金額（税込・送料込みが望ましい）
ECプラットフォームの管理画面から「注文履歴CSV」をエクスポートすれば、通常これらのデータは揃います。ただし、以下の前処理が必要です。
- キャンセル・返品の除外：キャンセルされた注文や返品処理済みの注文は除外する。ステータスが「完了」の注文のみを対象にする。
- テスト注文の除外：開発チームのテスト注文や、0円の社内注文を除外する。
- 重複チェック：同一注文が複数行に分かれていないか確認する（明細行単位でエクスポートされている場合は、注文ID単位で集約する必要がある）。
前処理が済んだら、顧客IDごとに「最終購入日」「購入回数」「購入金額合計」の3列に集約します。これがRFM分析の入力データになります。
ステップ別の作成手順
集約済みのデータを元に、RFMスコアを付与していきます。
Step 1：Recencyの計算
最終購入日から今日までの経過日数を計算します。B列に最終購入日が入っている場合、以下の関数を使います。
=DATEDIF(B2, TODAY(), "d")
DATEDIF関数は「2つの日付の差」を求める関数で、第3引数の"d"は「日数」を意味します。結果が30なら「30日前に最後の購入」です。
Step 2：RFMスコアのランク付け
経過日数をもとに5段階のスコアを付与します。IFS関数（Excel 2019以降）を使います。
=IFS(D2<=30, 5, D2<=90, 4, D2<=180, 3, D2<=365, 2, TRUE, 1)
この例では、30日以内がR5（最も新しい）、90日以内がR4、180日以内がR3、365日以内がR2、それ以外がR1（最も古い）となります。
Frequency（購入回数）も同様にスコア化します。
=IFS(E2>=10, 5, E2>=6, 4, E2>=3, 3, E2>=2, 2, TRUE, 1)
Monetary（購入金額）も同様に処理します。
=IFS(F2>=100000, 5, F2>=50000, 4, F2>=20000, 3, F2>=5000, 2, TRUE, 1)
重要なポイント：ランクの閾値（30日、90日、10回、100,000円など）は業種・業態によって大きく異なります。自社の購買サイクルに合わせて調整してください。例えば、食品ECなら30日/60日/90日が適切ですが、家電ECなら90日/180日/365日が現実的です。
Step 3：セグメントラベルの付与
RFMスコアの組み合わせから、分かりやすいセグメント名を付けます。IFS関数を入れ子にする方法もありますが、複雑になるため、VLOOKUPで別シートのマッピング表を参照する方法が管理しやすいです。
マッピング表の例：R5F5→「ロイヤル」、R5F1→「新規有望」、R1F5→「要Winback」、R1F1→「完全休眠」。このマッピング表を「定義シート」として別シートに作成し、メインシートからVLOOKUPで参照する設計にすると、閾値やラベルの変更が容易になります。
ピボットテーブルでマトリクスを作る
各顧客にRFMスコアが付いたら、ピボットテーブルを使ってクロス集計表（RFMマトリクス）を作成します。
- データ範囲を選択し、「挿入」→「ピボットテーブル」を選択
- 行エリアに「Fスコア」をドラッグ（F5が上、F1が下になるよう並べ替え）
- 列エリアに「Rスコア」をドラッグ（R5が左、R1が右になるよう並べ替え）
- 値エリアに「顧客ID」をドラッグし、集計方法を「個数」に変更
これで、縦軸がFrequency、横軸がRecencyの「顧客数マトリクス」が完成します。右上（R5F5）がロイヤル、左下（R1F1）が完全休眠です。
さらに視覚的に分かりやすくするために、条件付き書式でヒートマップを適用しましょう。「ホーム」→「条件付き書式」→「カラースケール」で、顧客数が多いセルが濃い色になるように設定します。これにより、自社の顧客がどのゾーンに集中しているかが一目で分かります。
値を「顧客数」から「売上金額合計」に切り替えれば、「売上貢献度マトリクス」も作成できます。顧客数が多いゾーンと売上貢献度が高いゾーンのギャップを比較することで、「人数は多いが売上に貢献していないセグメント」と「少人数で売上を支えているセグメント」が可視化されます。
Excelの限界と次のステップ
Excelでの分析は素晴らしいスタートですが、以下の壁にぶつかったらツール導入を検討するタイミングです。
- データ量の限界：Excelの処理速度は数万行を超えると著しく低下します。VLOOKUP関数が数千個入ったシートは、再計算に数分かかることもあります。顧客数が1万人を超えたら、データベースベースのツールへの移行を検討しましょう。
- 更新の手間：毎週CSVをエクスポートし、データを貼り替え、関数を再計算し、ピボットテーブルを更新する。この手作業を毎週続けるのは現実的ではありません。2〜3回は頑張れても、4回目以降は「今週は忙しいからスキップ」が常態化します。
- 属人化リスク：Excelファイルの構造を理解している人が1人しかいない「属人化」状態は危険です。その人が異動や退職した時点で分析が止まります。複雑な数式やマクロが入ったExcelファイルは、引き継ぎが極めて困難です。
- 施策連動の不在：Excelで分析した結果を、メール配信ツールやLINE配信ツールに手動で反映する必要があります。「分析→リスト抽出→CSV出力→配信ツールにインポート→配信」という手順が増えるほど、ミスの可能性と時間コストが膨らみます。
- セグメント遷移が追えない：Excelは「今のスナップショット」は作れますが、「先月と今月の差分（遷移）」を自動で追跡する機能がありません。セグメント遷移こそがCRMの最重要KPIであるため、これが追えないのは致命的です。
分析精度を上げるTips
Excelでの分析中に精度を高めるための実践的なコツを紹介します。
閾値は自社データから決める
Recencyの閾値を「30日/90日/180日/365日」にするのは一般的な目安ですが、自社に最適とは限りません。まず顧客全体の「最終購入日からの経過日数」のヒストグラム（度数分布）を作り、自然な境界を見つけましょう。PERCENTILE関数を使って、上位20%/40%/60%/80%の境界値を算出する方法が客観的です。
=PERCENTILE(D:D, 0.2) で上位20%の閾値が分かります。
Monetaryは粗利ベースで
売上金額で集計するのが一般的ですが、商品カテゴリによって粗利率が大きく異なる場合は、粗利ベースでMonetaryを計算する方が実態に近い評価ができます。高額だが利益の薄い商品ばかり買う顧客と、安価だが高粗利の商品を繰り返し買う顧客では、後者の方がビジネスへの貢献度は高いからです。
定期的なバックアップ
分析ファイルは月次でバックアップを取り、「2024年1月版」「2024年2月版」のようにファイル名で管理しましょう。これにより、後から「3ヶ月前と比べてセグメント構成がどう変わったか」を手動で比較できます。ファイル名に日付を入れるルールを決めておくことで、属人化の軽減にもつながります。