RFM分析のやり方が今さら聞けないなら、スプレッドシートとAIで済ませる

RFM分析は、直近購入日・購入頻度・購入金額の3指標で顧客をランク分けする手法だ。専用ツールは不要で、スプレッドシート関数5本・作業7工程・元データの列3つだけで再現できる。AIに任せるのは、集計が終わったあとのスコアの解釈と施策の壁打ちだけでいい。

• 必要なのは顧客ID・注文日・注文金額の3列だけ
• 集計はスプレッドシート関数で完結し、AIの役割は解釈・言語化に限定される
• ツール導入の稟議を待たず、その日のうちに顧客ランクが出せる

RFM分析とは何か、何が分かるのか？

RFM分析は、Recency(直近購入からの経過日数)・Frequency(購入回数)・Monetary(累計購入金額)の3指標で顧客を評価する行動セグメンテーション手法だ。顧客を「優良」「安定」「離反懸念」「休眠」のような塊に分け、誰に何を送るべきかを判断するための土台になる。

この手法自体は目新しいものではない。ダイレクトマーケティングの領域で1990年代から使われてきた古典的なフレームワークで、機械学習のような予測モデルではなく、あくまで「今持っているデータを並べ替えて塊にする」だけの整理手法だ。だからこそ、専用のBIツールがなくても組める。

RFM分析が解決するのは、実は集計そのものより前の問題であることが多い。社内で「優良顧客」という言葉を使うとき、売上上位者を優良と見る人、直近の購入者を優良と見る人、リピート回数で見る人が同じ会議に混在し、施策の的が絞れなくなる場面はよくある。RFMはR・F・Mを最初から別々の軸として分離するため、この「誰にとっての優良か」がずれている状態そのものを可視化できる。分析の効能は、精緻なスコアを出すことよりも、まずこの共通言語を作ることにある。

ここで最初に断っておきたいのは、この記事のサンプルはすべて後述する架空の購入履歴データであり、実在するECサイトの顧客データではないという点だ。手順とスプレッドシートの組み方を確認するための再現用データとして扱ってほしい。

RFM分析はどんな商材で機能し、どこでは機能しないのか？

RFM分析が機能するのは、購入頻度(F)が意味を持つ業態だ。通販・D2C・消耗品ECのように、同じ顧客が年に複数回購入する可能性がある商材であれば、F軸に十分なばらつきが出て、ランク分けが成立する。

逆に機能しにくい、あるいは重み付けの見直しが要るケースが3つある。1つ目は住宅設備や高額家電、BtoBの大口取引のような超高単価・低頻度購入だ。この場合Fは「1〜2回」に集中し、指標としての差がほとんど出ない。2つ目はサブスクリプション型のビジネスだ。「購入」が自動更新である以上、Recencyは顧客本人の能動的な意思を反映しない。ログイン日や利用頻度など、購買以外の行動指標に置き換える調整が必要になる。3つ目はBtoBだ。発注者・利用者・意思決定者が別人であることが多く、RFMは購買行動しか見ないため、関係の複雑さそのものは捉えられない。

具体的なイメージで言うと、超高単価・低頻度購入の典型は住宅リフォームや太陽光発電設備のような、数年に一度あるかないかの高額商材だ。この場合、大半の顧客のFは1で揃ってしまい、F軸で顧客を分ける意味がほとんどなくなる。サブスクリプション型の典型は、月額制の動画配信サービスや会員制のEC定期便のような、契約が続く限り自動で「購入」が発生し続けるビジネスだ。ここでのRecencyは決済日を追っても顧客の熱量を反映せず、ログイン頻度や利用回数のような行動データに置き換えないと、離反の兆候を見落とす。BtoBの典型は、法人向けの資材卸やOEM部品の取引のように、発注担当者と実際に商品を使う現場、契約を決裁する管理職が別人であるような取引だ。RFMは注文という行為しか見ないため、この「誰が何を決めているか」という関係の構造までは捉えられない。

この記事で組む手順は、あくまで「購入という行動が繰り返し発生する」ことを前提にしている。自社の商材がこの前提から外れる場合は、R・Fの定義を購入以外の行動(ログイン、資料請求、問い合わせなど)に読み替えてから同じ関数構成を当てはめるとよい。

なぜ専用ツールなしでスプレッドシートだけで再現できるのか？

RFM分析の中身は、四則演算とランク付けの組み合わせでしかない。直近購入日と基準日の差を出す引き算、購入回数を数える条件付きカウント、購入金額を合計する条件付き集計、そしてその3つの数値を段階に振り分ける並べ替え。この4つの操作はすべてスプレッドシートの標準関数で完結する。専用のBIツールやCRMが提供している「顧客分析機能」の中身も、突き詰めればこの組み合わせの延長線上にあることが多い。

専用ツールが優れているのは、複数チャネルの顧客データを自動で名寄せし、日次で再計算し、ダッシュボードとして常時表示し続ける「運用の自動化」の部分だ。逆に言えば、月に1回、手元のCSVから顧客のランクを出したいだけであれば、その運用機能は過剰装備になる。稟議を通して予算を確保し、導入設定を行い、社内のデータ連携を組む手間のほうが、分析そのものより重くなることは珍しくない。

なお、楽天RMS・Amazonセラーセントラル・Shopify管理画面など、チャネルをまたいで顧客を同一人物として突き合わせる「名寄せ」の作業は、RFM分析そのものとは別の課題だ。この記事で扱うのは単一チャネル内での集計に限定し、チャネル横断の名寄せには踏み込まない。

分析に必要なデータは何列か？

必要なのは顧客ID・注文日・注文金額の3列だけだ。この3列さえ元データから抜き出せれば、RFM分析は組める。逆に言えば、この3列が揃わない状態で分析ツールの導入を検討しても、結局は同じ3列をどこかから作り出す作業が発生する。

モールやカートシステムごとに、実際のCSVの列名や仕様は異なる。楽天RMSの受注データ、Amazonセラーセントラルの注文レポート、Shopify管理画面の注文エクスポートは、それぞれ画面の名称も列の並びも別物だ。この記事ではどのチャネルの列名がどれに対応するかという断定は避ける。理由は単純で、管理画面にログインしないと正確な列名は確認できず、誤った列名を書けば実務ではむしろ迷わせてしまうからだ。大事なのは、どのチャネルであっても「顧客を一意に識別できる列」「注文が発生した日付の列」「その注文の金額の列」の3つに相当する項目さえ手元に用意できれば、それ以降の手順は共通だという点だ。

この3列以外は今回の集計には使わない。商品名、カテゴリ、配送先、クーポン利用の有無といった列は、RFMのスコアリング自体には不要だ。列を増やすほど作業は重くなるので、まずはこの3列だけを別シートに抜き出すところから始めるとよい。

スプレッドシートで実際にどう組むか？

作業は7工程に分解できる。関数を使う工程は5つで、残り2つはデータ整形の下ごしらえだ。

工程	内容	使う関数
1	元データを顧客ID・注文日・注文金額の3列に整理する	–
2	分析基準日を1つのセルに固定する	–
3	顧客ごとの最終購入日を求め、基準日との差でRecencyを出す	MAXIFS
4	顧客ごとの注文回数を数えてFrequencyを出す	COUNTIFS
5	顧客ごとの累計購入金額を合計してMonetaryを出す	SUMIFS
6	R・F・Mそれぞれを3段階のスコアに変換する	RANK.EQ
7	3つのスコアの組み合わせをセグメント名に変換する	IFS

手順の要は工程3から5だ。顧客IDが重複して並ぶ注文明細に対して、MAXIFSで「その顧客IDに一致する行の中で最も新しい注文日」を、COUNTIFSで「その顧客IDに一致する行数」を、SUMIFSで「その顧客IDに一致する行の注文金額の合計」を、それぞれ1行にまとめる。ここまでで顧客ごとにR・F・Mの生の数値が並んだ表ができる。

次にRANK.EQ(またはパーセンタイル関数)で、R・F・Mそれぞれを母集団内での順位に変換し、3段階(高・中・低)のスコアに振り分ける。最後にIFSで、R・F・Mのスコアの組み合わせを「優良」「安定」「離反懸念」「休眠」「新規・見込み」のような読みやすいセグメント名に変換する。この時点で最終的な出力は、顧客ID・R・F・M・セグメント名の5列に収まる。工程を追うたびに列が増えていくのではなく、最終的には5列だけが残る設計にしておくと、あとでAIに渡すときも扱いやすい。

工程1と2は関数を使わない下ごしらえだが、ここを丁寧にやるかどうかで後の工程の正確さが変わる。工程1では、注文明細から顧客ID・注文日・注文金額の3列だけを別シートに抜き出し、日付列が文字列ではなく日付形式になっているか、金額列に単位(円マークやカンマ)が文字として混ざっていないかを確認する。ここが崩れていると、後続のMAXIFSやSUMIFSが正しく計算されない。工程2では、分析基準日を「今日の日付」を都度参照する関数ではなく、1つのセルに固定した日付として置く。基準日を固定しておくことで、後日同じシートを開いて見返したときにも、過去に出したRecencyの数値が変わらずに残る。

5本の関数がそれぞれ何を1行に出しているかを整理すると次のようになる。MAXIFSは「その顧客IDの注文の中で最も新しい注文日」を1つの日付として返す。COUNTIFSは「その顧客IDの注文が明細の中に何行あるか」を1つの整数として返す。SUMIFSは「その顧客IDの注文金額をすべて足した合計」を1つの金額として返す。RANK.EQは、34人分のR・F・Mそれぞれの数値を母集団内で順位づけし、その順位を3段階のスコアに変換する。IFSは、R・F・Mの3つのスコアの組み合わせを見て、あらかじめ決めておいたセグメント名のどれに当てはまるかを判定する。ここまでの5本を積み上げると、顧客ID1件につき1行、R・F・M・セグメント名が横に並んだ表が出来上がる。

組み終えたら検算をしておくと安全だ。COUNTIFSで出したFrequencyの合計行数が、元の注文明細の総件数と一致するか、SUMIFSで出したMonetaryの合計金額が、元データ全体をSUM関数で合計した金額と一致するかを確認する。この2点が一致していれば、顧客ごとの集計にもれや重複がないことが確認できる。

AIに何を任せ、何を任せないべきか？

集計そのものはAIに任せない。R・F・Mの計算は前段の関数で機械的に再現でき、AIに投げる理由がない。むしろAIに集計を丸投げすると、対象データ範囲の取り違えのような誤りが混入しても検証しづらくなる。AIに渡すのは、集計が終わったあとの5列(顧客ID・R・F・M・セグメント名)だけでいい。

AIに任せてよいのは2つだ。1つはセグメントの特徴を言語化すること。もう1つは、そのセグメントに対する施策のアイデア出しの壁打ち相手にすることだ。どちらも「数字を出す」作業ではなく「数字を読んで言葉にする」作業であり、これはスプレッドシートの関数ではできない領域になる。

プロンプトの例を2本挙げる。1本目は特徴の要約用だ。「以下は顧客ID・R(直近購入からの経過日数)・F(購入回数)・M(累計購入金額)・セグメント名の5列のデータだ。セグメントごとにR・F・Mの傾向をまとめ、各セグメントの特徴を3行以内で説明してほしい」というように、渡す列の意味を先に定義してから投げると、的外れな解釈が減る。

2本目は休眠顧客向けの施策アイデア出し用だ。「セグメント名が『休眠』の顧客は、直近購入から日数が経っているが過去には複数回購入している層だ。この層に対して、値引き以外でもう一度接点を作る施策のアイデアを5つ挙げてほしい」というように、セグメントの定義と制約条件(値引き以外、など)をセットで渡すと、汎用的すぎる回答を避けやすい。

ここで挙げたプロンプトは一例であり、特定の生成AI製品の機能や有料プランの仕様を前提にしたものではない。手元でよく使っているチャットに、集計済みの5列を貼り付けて試すだけで再現できる。

サンプルデータ100件を実際に流すと、何が起きるのか？

ここまでの手順を、架空の購入履歴データ100件(実店舗の顧客データではなく、この記事のために作成した再現用サンプル)に当てて実際に集計した。100件の注文明細は、34人のユニーク顧客に集約された。1人が複数回注文しているケースがあるため、明細の件数と顧客数は一致しない。

指標	最小	中央値	最大
R(経過日数)	3日	83日	333日
F(購入回数)	1回	2回	6回
M(累計購入金額)	3,400円	21,800円	61,400円

ここからが、この記事でいちばん書いておきたい数字だ。R・F・Mをそれぞれ3段階に分けると、理論上は3×3×3で27通りの組み合わせができる。RFM分析を紹介する記事の多くは、ここで「27のセグメントに分類できます」と締めくくる。だが今回の34人にRANK.EQでスコアを振り、実際に組み合わせを数えると、出現したのは27通りのうち17通りだけだった。残り10通りの組み合わせには、1人も該当者がいなかった。

27通りの組み合わせが均等に埋まらない理由は、R・F・Mが互いに独立な変数ではないからだ。3つの指標を別々の軸として扱ってはいるが、実際の購買行動では、頻繁に購入している顧客(Fが高い)は、必然的に直近の購入日も新しくなりやすく(Rも良くなりやすい)、累計購入金額も積み上がりやすい(Mも高くなりやすい)。逆に、直近の購入は古い(Rが悪い)のに購入頻度は高い(Fが高い)、というように軸同士が矛盾する組み合わせは、そもそも起こりにくい。3×3×3のマス目のうち、軸同士が矛盾する隅のマスほど空になりやすいのは、この相関構造のためだ。

これは意図的に作ったデータの偏りではない。34人という規模で3×3×3のマス目を作れば、統計的に見て空白のマスが出るのは当然の帰結だ。理論上のセグメント数と、実際に人が入るセグメント数は別物であり、その差はデータの母数で決まる。母数が数百人、数千人と増えれば27通りに近づいていくが、100件・34人のような規模では、多くのマスが空になる。

この「27通りのうち17通り」という結果を、IFS関数で5つの名前(優良・安定・離反懸念・休眠・新規・見込み)に集約したのが次の内訳だ。

セグメント	人数	割合
安定	11人	32.4%
優良	7人	20.6%
離反懸念	6人	17.6%
休眠	5人	14.7%
新規・見込み	5人	14.7%

たとえば実測で最も人数の多かった「安定」セグメント(11人)は、R・F・Mの3スコアのうち多くが中位に集まる組み合わせに対応していた。一方「優良」セグメント(7人)は、R・F・Mのすべてで上位スコアが揃う組み合わせに対応する。IFSの条件式は、このように「どのスコアの組み合わせをどの名前に当てはめるか」を先に決めておく必要があり、この対応表を作る作業自体は関数ではなく人間の判断で行う部分だ。

細かい27通りのまま社内に共有しても、1人か2人しか入らないセグメントに個別の施策を組むことはできない。IFSで5段階に集約したのは、粒度を細かくしすぎると1箱あたりの人数が減り、施策が打てなくなるからだ。セグメントの箱の数は、理論上いくつ作れるかではなく、手元のデータ量でいくつまでなら意味を持たせられるかで決めるべきだという結論になる。

箱の細かさと1箱あたりの人数の関係も、今回の数字で確認できる。34人を実際に出現した17通りの組み合わせにそのまま割り当てると、1つの組み合わせあたり平均は34÷17でおよそ2人にしかならない。1人か2人しかいないセグメントに対して個別の施策を組んでも、効果を検証するにはサンプルが少なすぎる。一方、IFSで5つの名前に集約した後は、1セグメントあたり平均34÷5で6.8人、実際の内訳でも最少の休眠・新規見込みで5人、最多の安定で11人と、少なくとも傾向を見て何かを試せる規模は確保できている。

では100件・34人規模のデータなら、何段階に分けるのが適切か。今回の実測から言えるのは、27通りの生の組み合わせのままでは細かすぎ、1つの名前にすべてをまとめてしまっては粗すぎる、ということだけだ。5段階という区切りは、このサンプルにおいて「1セグメントあたり5人以上を確保する」という条件から逆算して決めたものであり、データ量が数百人、数千人規模になれば、もっと細かい段階数でも1箱あたりの人数を確保できるようになる。段階数を先に決めてからデータを当てはめるのではなく、手元のデータ量を見てから何段階に集約できるかを決める、という順序が実務では逆になりやすい。

他のRFM解説記事の多くが「27のセグメントに分けられます」とだけ書いて終わるのは、理論上のマス目の数を数えているだけで、実際のデータを一度も流していないからだ。今回のように手元のデータで実際に計算してみて初めて、理論値と現実の差が見える。

なお、この34人分の計算処理そのものにかかった時間は37ミリ秒だった。これは表計算ソフト上での再計算時間ではなく、同じロジックをプログラムで動かした際の計算時間であり、桁で言えば体感できないほど一瞬だ。この記事で時間がかかるとすれば、それは計算ではなく、3列を揃えて基準日を決めるまでの段取りの方にある。

出したランクを、次にどう使うのか？

顧客ランクを出して終わりにすると、分析はただの作業実績になる。EC担当者の社内提案が通らない理由｜数値分析を4項目に翻訳する型で書いたように、社内で数字が通らないのは分析の精度ではなく、数字を相手の意思決定に翻訳できていないことが原因であることが多い。RFMで出た5列も同じで、そのまま「セグメント分けしました」と共有しても、次の一手には繋がらない。

使い道は大きく2つに分かれる。1つは、既存の販促スケジュールに接続することだ。「離反懸念」セグメントに対しては、通常のメルマガとは別枠で接点を作る、「休眠」セグメントに対しては、値引き以外の切り口で再訪を促す、というように、セグメント名をそのまま施策の宛先として使う。もう1つは、社内提案の材料として使うことだ。「全顧客に一律で送っているクーポンを、優良セグメントとそれ以外で出し分けたら、原価をどれだけ抑えられるか」というように、既存の運用コストを削る根拠として提示すると、稟議の通りやすさが変わる。

どちらの使い道でも共通しているのは、RFMの結果を「分析したこと」自体の報告にしないという姿勢だ。セグメントの人数、割合、そしてそこから何を変えるつもりなのかまでをセットで持っていくと、聞き手は数字の解釈に付き合わされずに済む。ツールを買わずにスプレッドシートで完結させたこと自体も、稟議を待たずにその日のうちに動ける材料になったという意味で、次に話す相手への説明材料になる。

この先、AI時代のEC実務者に求められるスキルがどう変わっていくかについては、EC担当者がAI時代に学ぶべきスキルは、業務を3つに分けると決まるで扱っている。今回のような「集計は関数に任せ、解釈と言語化だけをAIに任せる」という役割分担の考え方は、RFM分析に限らず他の業務にも応用できる。

よくある質問

RFM分析はExcelでもGoogleスプレッドシートでもできるか？

この記事で使っているMAXIFS・COUNTIFS・SUMIFS・RANK.EQ・IFSはいずれも一般的な表計算関数であり、特定のソフトの独自機能ではない。関数名や引数の細部はソフトによって差があるため、実際に組む際は自分が使っているソフトの関数の挙動を確認しながら進めるとよい。

セグメントは必ず5段階にしないといけないか？

そうではない。今回のサンプルではIFSで5つの名前に集約したが、これはデータ量(34人)に合わせた一例だ。データ量が少なければセグメント数をさらに絞り、多ければ増やすといった調整が必要になる。この記事の核は「理論上の組み合わせ数と、実際に人が入る組み合わせ数は別物」という点であり、5段階という数字自体を固定のルールとして扱うべきではない。

顧客データが少ない場合でもRFM分析は意味があるか？

意味はあるが、セグメントの粒度には注意が要る。今回の実測のように、100件程度・34人規模のデータでは、理論上27通りある組み合わせのうち17通りしか埋まらなかった。データが少ないほど空白のセグメントが増えるため、粒度を細かくしすぎず、少ない段階数で集約する方が施策に落としやすい。

AIにデータをそのまま渡してRFM分析自体をやらせてもよいか？

この記事で扱った範囲では推奨していない。R・F・Mの集計はスプレッドシートの関数で機械的かつ検証可能な形で再現できるため、AIに投げる必然性が薄い。AIに渡すのは、集計が終わったあとのセグメントの解釈や施策アイデアの壁打ちに限定した方が、対象データ範囲の取り違えのような検証しづらい誤りを避けやすい。

分析の基準日はどう決めればいいか？

分析を実行する日、あるいは月初や月末のようにあらかじめ決めた区切りの日を、1つのセルに固定して使うとよい。基準日を固定せず「今日の日付」を都度参照する関数のままにしておくと、シートを開くたびにRecencyの数値が変わってしまい、過去に共有した資料と数字が合わなくなる。RFM分析を月次のように繰り返し使う場合は、基準日を変えて再計算するたびに、古いシートのコピーを残しておくと、セグメントの変化を後から追える。

セグメント名は何を基準に決めればいいか？

IFSでどの組み合わせをどの名前に当てはめるかは、業務側で先に決めておく必要がある。今回のサンプルでは、R・F・Mすべてが上位のスコアを「優良」、直近購入が古くF・Mが中位以上の顧客を「離反懸念」というように、社内で使われている言葉に寄せて名付けた。関数が自動でセグメントに名前を付けてくれるわけではなく、名前と条件の対応表そのものは分析者が設計する部分だ。