【デシル分析・RFM分析】 Excelでできる顧客分析入門

顧客分析を始めたいと思っても、「どのデータを使えばよいのか」「Excelでどこまで分析できるのか」「分析結果をどう施策に活かせばよいのか」で手が止まってしまうケースは少なくありません。

顧客分析というと、BIツールやCRMシステムを使った高度な分析をイメージするかもしれません。しかし、購買履歴データがあれば、Excelでも基本的な顧客分析を始めることができます。

本記事では、代表的な顧客分析手法である「デシル分析」と「RFM分析」について、Excelを使った進め方を解説します。必要なデータ項目、ピボットテーブルでの集計、ランク分け、分析結果の見方まで順を追って紹介します。

まずはExcelで自社の顧客データを整理し、優良顧客の把握、離反防止、リピート促進などの施策検討に役立てていきましょう。

記事内で紹介する分析を、自社の購買データで試せるテンプレートをご用意しています。

CRMにおける他の分析手法については下記のコラムで解説していますので、あわせてご覧ください。

顧客分析とは

顧客分析とは、顧客の購買履歴や行動データをもとに、顧客の特徴や傾向を把握する取り組みです。

たとえば、次のようなことを明らかにできます。

• 売上に大きく貢献している顧客は誰か
• 購入頻度が高い顧客はどのくらいいるか
• 最近購入していない顧客はどの層に多いか
• 新規顧客はリピーター化しているか
• 離反しそうな優良顧客はいないか

顧客分析を行うことで、すべての顧客に同じ施策を行うのではなく、顧客の状態に応じたマーケティング施策を検討しやすくなります。

たとえば、よく購入している顧客には継続利用を促す案内を行い、長期間購入がない顧客には再購入を促すキャンペーンを実施するといった使い分けが可能です。

本記事で紹介するデシル分析とRFM分析は、いずれも購買履歴データをもとに行う基本的な顧客分析です。特別なシステムを使わなくても、Excelで始められる点が大きな特徴です。

顧客分析に使う主なツール

顧客分析には、さまざまなツールが使われます。代表的なものとして、BIツール、CRMシステム、SFAツール、MAツール、データマイニングツールなどがあります。

・BI（ビジネスインテリジェンス）ツール
BIツールは、複数のデータを集計・可視化し、売上や顧客の傾向を把握するために使われます。ダッシュボードで数値を確認したり、部署ごと・商品ごと・期間ごとにデータを切り分けたりする際に有効です。

・CRM（顧客関係管理）システム
CRMシステムは、顧客情報を一元管理するためのツールです。顧客属性、購買履歴、問い合わせ履歴、営業接点などを管理し、顧客との関係性を深めるために活用されます。

・SFA（セールスフォースオートメーション）ツール
SFAツールは、営業活動を管理・効率化するためのツールです。商談状況や営業プロセスを可視化し、営業活動の改善に役立てます。

・MA（マーケティングオートメーション）ツール
MAツールは、メール配信やリード育成などのマーケティング活動を自動化するためのツールです。顧客の行動に応じてメールを出し分けたり、見込み顧客の興味度をスコア化したりできます。

・データマイニングツール
データマイニングツールは、大量のデータからパターンや傾向を見つけるために使われます。予測分析や高度なセグメント分析を行う際に活用されます。

このように、顧客分析に使えるツールは多くあります。ただし、顧客分析を初めて行う場合、いきなり高度なツールを導入する必要はありません。

まずはExcelで購買履歴を整理し、基本的な分析を行うだけでも、自社の顧客構造や課題が見えてきます。

Excelで顧客分析を始めるために準備するデータ

Excelでデシル分析やRFM分析を行うには、まず元になる購買データを準備します。

今回は例として、2020年1月1日から2020年12月31日までの1年間に購入履歴のある顧客を対象に、2021年1月15日に分析を行う設定で説明します。

必要なデータ項目は、主に次の4つです。

• 注文番号、購入番号、取引番号など、購入内容を識別する項目
• 顧客ID、会員番号など、顧客を識別する項目
• 注文日、購入日など、購入日を示す項目
• 注文金額、購入金額など、購入金額を示す項目

この4つがそろっていれば、デシル分析とRFM分析の基本的な集計は可能です。

データを準備する上での注意点

RFM分析を行う場合は、注文番号の重複に注意が必要です。

たとえば、1回の注文で複数の商品を購入している場合、明細データ上では同じ注文番号が複数行に分かれていることがあります。この状態のまま注文回数を数えると、実際には1回の注文であるにもかかわらず、複数回購入したように集計されてしまいます。

そのため、注文番号が重複している場合は、事前に注文番号単位でデータをまとめておく必要があります。

具体的には、次のような形に整理します。

• 注文番号
• 顧客ID
• 注文日
• 注文金額

1回の注文に複数商品が含まれている場合は、注文番号ごとに金額を合計し、1注文1行のデータに整えてから分析を行いましょう。

Excelを使ったデシル分析

デシル分析とは、顧客を購入金額の多い順に並べ、10のグループに分けて分析する方法です。

「デシル」とは10等分を意味します。購入金額の多い顧客から順に10分割することで、売上に大きく貢献している顧客層を把握できます。

たとえば、上位30％の顧客が売上全体の80％を占めている場合、自社の売上は一部の優良顧客に強く支えられていることが分かります。一方で、上位層と下位層の売上差が小さい場合は、特定の優良顧客が育っていない可能性もあります。

デシル分析では、主に次のようなことを確認できます。

• 売上に貢献している顧客層
• 上位顧客への売上依存度
• 販促対象にすべき顧客層
• 下位顧客の育成余地
• 顧客ランクごとの売上構成

Excelでデシル分析を行う流れは、次の4ステップです。

1. 顧客ごとに期間内の累積購入金額を計算する
2. 累積購入金額の降順で顧客を並べる
3. リストを10等分し、デシルランクを割り当てる
4. デシルランクごとの構成、傾向を分析する

ステップ1. 顧客ごとに期間内の累積購入金額を計算する

まず、ピボットテーブルを使って、顧客ごとの累積購入金額を集計します。

手順は次のとおりです。

1. 購買データが入力されているセル範囲を選択する
2. 「挿入」タブから「ピボットテーブル」をクリックする
3. 「ピボットテーブルの作成」で「新規ワークシート」を選択する
4. ピボットテーブルのフィールドリストで、「行」に顧客IDを設定する
5. 「値」に注文金額を設定する
6. 注文金額の集計方法が「合計」になっていることを確認する

これにより、顧客IDごとの注文金額合計を算出できます。

ステップ2. 累積購入金額の降順で並べる

次に、顧客ごとの累積購入金額を多い順に並べます。

ピボットテーブル上で注文金額の右上のセルを右クリックし、「並べ替え」から「降順」を選択します。これにより、購入金額が多い顧客から順に並びます。

デシル分析では、購入金額の多い順に顧客を10グループに分けるため、この並べ替えが重要です。 

ステップ3. リストを10等分し、デシルランクを割り当てる

購入金額の多い順に並べたら、顧客リストを10等分してデシルランクを割り当てます。
まず、新しいシートを作成し、次の項目を用意します。

• 順位
• 顧客ID
• 期間内購入金額
• デシルランク

ピボットテーブルで集計した顧客IDと購入金額をコピーし、新しいシート（デシルランク付与）に貼り付けます。その後、購入金額の多い顧客から順に、1、2、3……と順位を振ります。

今回のサンプルでは、対象顧客数を100名とします。100名を10等分するため、1グループあたり10名ずつに分けます。

顧客数が10で割り切れない場合は、余りを最下位ランクに含める方法が一般的です。下位ランクは購入金額が相対的に小さいため、分析全体への影響が比較的小さいためです。

Excel関数でデシルランクを割り当てる場合は、たとえば次のような考え方で設定します。

• 1位〜10位：デシル1
• 11位〜20位：デシル2
• 21位〜30位：デシル3
• 以降、同様に10分割

例：
=MIN(10,INT((A6-1)/$F$2)+1)

この式では、A列の順位をもとに、1〜10のデシルランクを自動で割り当てます。
F列に分割幅を入力しておくと楽です。

ステップ4. デシルランクごとの構成、傾向を分析する

デシルランクを割り当てたら、ランクごとに購入金額を合計し、売上全体に占める割合を確認します。
集計表には、次の項目を用意します。

• デシルランク
• 購入顧客数
• 購入金額合計
• 構成比
• 累計構成比

購入金額合計は、SUMIF関数を使うと集計できます。

例：
=SUMIF(デシルランク付与!$D$6:$D$105,A4,デシルランク付与!$C$6:$C$105)

この式では、D列のデシルランクがA4セルのランクと一致する顧客について、デシルランク付与シートC列の期間内購入金額を合計します。 

購入顧客数は、COUNTIF関数で集計できます。

例：
=COUNTIF(デシルランク付与!$D$6:$D$105,A4)

構成比は、各デシルランクの購入金額合計を、全体の購入金額合計で割って算出します。

累計構成比は、上位ランクから順に構成比を足し上げて算出します。

たとえば、デシル1〜3の顧客が売上全体の約80％を占めている場合、上位30％の顧客が売上の大部分を支えていることが分かります。

この結果から、次のような仮説を立てることができます。

• 上位顧客の離反が売上に大きな影響を与える可能性がある
• 上位顧客向けの維持施策を強化すべき
• 中位顧客を上位顧客へ育成する余地がある
• 下位顧客に同じ販促コストをかけるべきか見直す必要がある

デシル分析の活用例

デシル分析は、顧客を購入金額順に分類するシンプルな分析ですが、販促対象の選定や売上構造の把握に活用できます。

販促対象を選別する

DMやカタログ送付など、一定のコストがかかる施策を行う場合、すべての顧客に同じように送付すると費用対効果が悪化する可能性があります。

デシル分析を行えば、購入金額の高い顧客層を把握できます。そのため、まずはデシルランク上位の顧客に絞って施策を実施するなど、販促対象を検討しやすくなります。

たとえば、デシル1〜3の顧客が売上の大部分を占めている場合、上位顧客に向けて限定キャンペーンや優待案内を行うことで、売上維持やロイヤルティ向上につなげられる可能性があります。

売上構造と課題を把握する

デシル分析では、自社の売上がどの顧客層に支えられているかを把握できます。

もし上位ランクの顧客への売上依存度が非常に高い場合、その顧客層が離反すると売上への影響が大きくなります。この場合は、上位顧客の離反防止策が重要です。

一方で、デシルランク間の売上構成比に大きな差がない場合は、優良顧客が十分に育っていない可能性があります。この場合は、リピート促進や購入単価向上の施策を検討する必要があります。

デシル分析は、単に顧客をランク分けするための分析ではありません。売上を支えている顧客層を把握し、どの顧客にどのような施策を行うべきかを考えるための出発点になります。

Excelを使ったRFM分析

RFM分析とは、顧客を次の3つの指標で分類する分析手法です。

• R：Recency、最終購入日からの経過日数
• F：Frequency、購入頻度
• M：Monetary、累積購入金額

デシル分析では、主に購入金額をもとに顧客を分類します。一方、RFM分析では、購入金額に加えて「最近購入しているか」「どのくらい頻繁に購入しているか」も見ることができます。

そのため、顧客の状態をより細かく把握できます。

たとえば、累積購入金額が高い顧客でも、最近購入していない場合は離反リスクがあるかもしれません。反対に、購入金額はまだ高くなくても、最近購入し始めた顧客であれば、今後の育成対象として重要かもしれません。

RFM分析を行うことで、次のような顧客分類が可能になります。

最近購入しており、購入頻度も高い常連顧客

• 最近購入したばかりの新規顧客
• 過去はよく購入していたが、最近購入していない離反予備軍
• 購入金額は高いが、購入頻度は低い顧客
• 長期間購入がない離反顧客

ExcelでRFM分析を行う流れは、次の3ステップです。

1. 顧客ごとに「期間内の最終購入日」「購入件数」「累積購入金額」を集計する
2. 最終購入日、購入件数、累積購入金額によってR・F・Mのランクを割り当てる
3. R・F・Mの構成を分析する

ステップ1. 顧客ごとに最終購入日、購入件数、累積購入金額を集計する

まず、ピボットテーブルを使って、顧客ごとの最終購入日、購入件数、累積購入金額を集計します。

手順は次のとおりです。

1. 元データをすべて選択する
2. 「挿入」タブから「ピボットテーブル」をクリックする
3. 「行」に顧客IDを設定する
4. 「値」に注文日、注文番号、注文金額を設定する

それぞれの集計方法は、次のように設定します。

• 注文日：最大値
• 注文番号：データの個数
• 注文金額：合計

注文日の最大値を使うことで、顧客ごとの最終購入日を確認できます。注文番号の個数を数えることで購入件数を把握できます。注文金額の合計を使うことで累積購入金額を算出できます。

作成したピボットテーブルは、新しいシートにコピーし、項目名を次のように変更しておくと分かりやすくなります。

• 顧客ID
• 最終購入日
• 注文回数
• 合計注文金額

ステップ2. R・F・Mのランクを割り当てる

次に、顧客ごとにR・F・Mのランクを割り当てます。

R：最終購入日からの経過日数を計算する

まず、最終購入日から分析日までの経過日数を計算します。

今回の例では、分析日を2021年1月15日とします。

「最終購入日」の右隣に列を追加し、「最終購入日からの経過日数」という項目を作成します。そして、次のような式で経過日数を計算します。

例：
=分析日-最終購入日

たとえば、分析日をM2セルに入力している場合は、次のような式になります。
=$M$2-B2

この式により、最終購入日から何日経過しているかを算出できます。 

RFMランクの基準を決める

次に、R・F・Mそれぞれのランク基準を決めます。

RFM分析では、各項目を3段階または5段階で評価することが一般的です。ここでは、5段階で分類する例を紹介します。

Rは、最終購入日からの経過日数が短いほど高い評価にします。

例：

• 30日以内：R5
• 60日以内：R4
• 90日以内：R3
• 180日以内：R2
• 181日以上：R1

Fは、購入頻度が高いほど高い評価にします。

例：

• 10回以上：F5
• 5〜9回：F4
• 3〜4回：F3
• 2回：F2
• 1回：F1

Mは、累積購入金額が高いほど高い評価にします。

例：

• 100,000円以上：M5
• 50,000円以上：M4
• 30,000円以上：M3
• 10,000円以上：M2
• 10,000円未満：M1

ただし、この基準はあくまで一例です。実際には、自社の商品単価、購入頻度、購買サイクルに合わせて設定する必要があります。

たとえば、日用品のように購入頻度が高い商材と、家具や家電のように購入頻度が低い商材では、適切な基準が異なります。RFM分析では、データの分布を確認しながら、自社に合ったランク基準を設定することが重要です。

IF関数またはIFS関数でランクを割り当てる

ランク基準を決めたら、Excel関数を使って顧客ごとにR・F・Mランクを割り当てます。

Rのランク付けには、たとえば次のような式を使います。

=IFS(C5<=30,5,C5<=60,4,C5<=90,3,C5<=180,2,TRUE,1)

この式では、C5セルに入っている「最終購入日からの経過日数」をもとに、Rランクを1〜5で判定します。

Fのランク付けには、注文回数をもとにした式を使います。

=IFS(D5>=10,5,D5>=5,4,D5>=3,3,D5>=2,2,TRUE,1)

Mのランク付けには、合計注文金額をもとにした式を使います。

=IFS(E5>=100000,5,E5>=50000,4,E5>=30000,3,E5>=10000,2,TRUE,1)

このように、R・F・Mそれぞれのランクを付けることで、顧客の状態を多面的に把握できます。 

ステップ3. R・F・Mの構成を分析する

R・F・Mのランクを割り当てたら、ピボットテーブルを使って構成を分析します。

まずは、RとFを掛け合わせて顧客数を集計してみましょう。

手順は次のとおりです。

1. RFMランクを割り当てた顧客リスト全体を選択する
2. 「挿入」タブから「ピボットテーブル」をクリックする
3. 「行」にRランクを設定する
4. 「列」にFランクを設定する
5. 「値」に顧客IDを設定し、集計方法を「データの個数」にする

これにより、RとFの組み合わせごとに顧客数を確認できます。

また、詳細は割愛しますが関数でクロス集計を行うことも可能です。

たとえば、Rが高くFも高い顧客は、最近も購入しており、購入頻度も高い顧客です。常連顧客や優良顧客として、継続的な関係維持が重要になります。

一方で、Fは高いもののRが低い顧客は、過去には頻繁に購入していたものの、最近は購入していない顧客です。離反予備軍として、再購入を促す施策を検討する必要があります。

RとFの組み合わせから、顧客を次のように分類できます。

・常連顧客
購入頻度が高く、最近も購入している顧客です。売上への貢献度が高く、今後も継続購入が期待できます。
この層には、優待案内、限定キャンペーン、会員ランク施策など、継続利用を促す施策が有効です。

・新規顧客
最近初めて購入した顧客です。今後リピーターになってもらえるかどうかが重要です。
この層には、初回購入後のフォローメール、使い方の案内、関連商品の提案などが有効です。

・リピーター
複数回購入しており、最近も購入している顧客です。常連顧客へ育成する余地があります。
この層には、購入頻度を高めるキャンペーンや、次回購入を促すクーポンなどが考えられます。

・離反予備軍
過去には複数回購入していたものの、最近は購入していない顧客です。
この層は、放置すると離反顧客になる可能性があります。再購入を促すキャンペーン、休眠前のリマインド、過去購入商品に関連する案内などを検討しましょう。

・離反顧客
購入頻度が低く、長期間購入がない顧客です。
この層には、再購入の可能性を見極めたうえで、施策対象にするかどうかを判断する必要があります。すべての離反顧客にコストをかけるのではなく、購入金額や商品カテゴリなども組み合わせて優先順位を付けることが重要です。

RFM分析の注意点

RFM分析は、購買データをもとに顧客を定量的に分類できる便利な手法です。ただし、R・F・Mのスコアだけで顧客の状態を判断すると、誤った解釈につながることがあります。

ここでは、RFM分析を行う際に注意したいポイントを紹介します。

Recencyだけで判断しない

RFM分析では、最終購入日からの経過日数であるRecencyが重要な指標になります。最近購入している顧客ほど、再購入の可能性が高いと考えられるためです。

しかし、商材によっては購入サイクルが長い場合があります。

たとえば、毎年8月のお中元シーズンに購入している顧客がいるとします。4月にRFM分析を行った場合、その顧客の最終購入日からは約240日が経過しているため、Recencyの評価は低くなるかもしれません。

しかし、その顧客は毎年決まった時期に購入している優良顧客である可能性があります。Recencyだけを見ると、こうした顧客を離反顧客と誤って判断してしまうことがあります。

そのため、RFM分析は一度だけではなく、定期的に行うことが重要です。継続的に分析することで、季節性や購買サイクルを踏まえた顧客理解がしやすくなります。

FとMの組み合わせを見る

RFM分析では、累積購入金額であるMonetaryだけを見るのではなく、購入頻度であるFrequencyとの組み合わせも確認する必要があります。

たとえば、次の2人の顧客がいるとします。

1,000円の商品を毎月10回購入した顧客
10,000円の商品を1回だけ購入した顧客

どちらも累積購入金額は10,000円です。しかし、顧客の状態は大きく異なります。

前者は継続的に購入しているリピーターです。今後も定期的な購入が期待できる可能性があります。後者は一度だけ高額購入した顧客であり、再購入の可能性は別途確認する必要があります。

このように、Mだけでは見えない違いを、Fと組み合わせて見ることが重要です。

商品カテゴリや顧客属性も組み合わせる

RFM分析は、基本的に購買日、購入回数、購入金額をもとにした分析です。そのため、顧客が何を購入したのか、なぜ購入しなくなったのかまでは分かりません。

たとえば、ベビー用品を購入していた顧客の購入が途絶えた場合、単純な離反ではなく、子どもの成長によって必要な商品が変わった可能性があります。この場合、同じベビー用品を案内しても反応は期待しにくいでしょう。

しかし、子どもの成長に合わせた商品を提案できれば、再購入につながる可能性があります。

RFM分析の結果をより有効に活用するには、商品カテゴリ、購入チャネル、顧客属性、キャンペーン反応など、他のデータと組み合わせて考えることが重要です。

デシル分析・RFM分析を施策に活かすポイント

デシル分析やRFM分析は、Excelで顧客を分類できる便利な手法です。ただし、分析結果を眺めるだけでは売上改善にはつながりません。

重要なのは、分析結果から仮説を立て、具体的な施策に落とし込むことです。

たとえば、次のように考えます。

・上位顧客の売上構成比が高い
→ 上位顧客の離反防止施策を強化する

・中位顧客の人数が多い
→ 購入頻度や購入単価を上げる育成施策を検討する

・新規顧客は多いがリピート率が低い
→ 初回購入後のフォロー施策を見直す

・過去の優良顧客が休眠している
→ 離反理由を仮説立てし、再購入施策を行う

・下位顧客への販促コストが大きい
→ 施策対象を見直し、費用対効果を改善する

顧客分析は、分析表を作ることが目的ではありません。顧客ごとの状態を把握し、誰に、何を、どのタイミングで届けるべきかを考えるための手段です。

重要なのは分析をもとに戦略を設計することです。
自社に合ったマーケティング戦略の見つけ方は下記のコラムで解説していますので、あわせてご覧ください。

まずはExcelでできる顧客分析から始めよう

デシル分析とRFM分析は、購買履歴データがあればExcelでも始められる顧客分析です。

デシル分析では、購入金額をもとに顧客を10グループに分け、売上に貢献している顧客層を把握できます。RFM分析では、最終購入日、購入頻度、累積購入金額の3つの視点から、顧客の状態をより細かく分類できます。

これらの分析を行うことで、優良顧客、リピーター、新規顧客、離反予備軍、離反顧客などを把握しやすくなります。

まずはExcelで自社の購買データを整理し、顧客の傾向を確認してみましょう。分析を継続することで、顧客構造の変化や施策の改善ポイントも見えやすくなります。

Excel分析の次に考えたいこと

Excelでデシル分析やRFM分析を行えば、顧客の大まかな傾向は把握できます。

一方で、分析結果を実際のマーケティング施策に活かすには、さらに踏み込んだ検討が必要です。

たとえば、次のような点です。

• どのランクの顧客を優先すべきか
• 離反予備軍にどのような施策を行うべきか
• 上位顧客を維持するために何をすべきか
• 新規顧客をリピーター化するには何が必要か
• 分析結果をDM、メール、Web施策にどう反映するか
• 商品別、チャネル別、期間別にどこまで深掘りすべきか

Excelで分析表を作ることは、あくまでスタートです。そこから施策仮説を立て、実行し、結果を検証することで、顧客分析はマーケティング成果につながります。

フュージョン株式会社では、企業の購買データをもとに、売上分析・顧客分析・商品分析を行うCRM分析を支援しています。データの集計だけでなく、分析結果の読み解き、顧客セグメントの設計、施策への落とし込みまでサポートしています。

「Excelで分析してみたが、結果をどう解釈すればよいか分からない」
「顧客ランクごとにどのような施策を打つべきか整理したい」
「購買データを活用して、CRM施策やDM施策を改善したい」

このような課題がある方は、ぜひお気軽にお問い合わせください。