FUSION directmarketing agency

JP / EN

MENU

TOPICS MARKETING COLUMN マーケティングコラム

MARKETING
COLUMN
マーケティングコラム

Excelでできる顧客分析入門(デシル分析、RFM分析)

顧客分析には様々な手法があります。
今回は、Excelでもできる基本的な顧客分析としてデシル分析とRFM分析を取り上げ、その具体的な手順をご紹介します。

分析にあたっては、分析対象期間の以下のような元データが必要となります。
売上明細データなどが活用できるでしょう。
今回の例では、架空の売上明細から分析を行います。2014年1月1日~12月31日に購入履歴のある顧客を対象とし、2015年1月15日に分析を行うという想定です。

注文明細イメージ

【必要な項目】
・注文を識別できる項目:注文番号など
・顧客を識別できる項目:顧客IDなど
・注文日
・注文金額

※以降で説明する手順でRFM分析を行う場合には、注文番号に該当する項目に重複がないよう注意してください。
注文明細がたとえば以下のような形になっている場合には、前処理として、注文番号単位で注文日・顧客ID・注文金額をまとめたデータを作成してください。

order_list02.bmp

元データが準備できたら、さっそく分析してみましょう。



■デシル分析
デシル分析とは、顧客を購入金額の多い順に並べ、上から順番に10等分して、各グループの購買傾向を分析する手法です。
売上に貢献している顧客を見つけることで販促活動の費用対効果を改善したり、売上構造分析として活用することで自社の課題を発見するのに役立ちます。

それでは、Excelでデシル分析を行う具体的な手順を見てみましょう。
おおまかな流れは以下のようになります。

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


1. 累積購入金額の計算
元データをすべて選択範囲とし、Excelのメニューから「挿入>ピボットテーブル」を選択します。
decile01.bmp



ピボットテーブルで「行ラベル」に「顧客ID」を、「値」に「注文金額」をドラッグします。
「注文金額」の集計方法が「合計」になっていることを確認してください。
これで、顧客IDごとの注文金額が集計されます。

decile02.bmp

2. 累積購入金額で並べ替え
できあがったピボットテーブルの注文金額の列の一番上の数値が入ったセルで右クリックし、
「並べ替え>降順」をクリックします。
これで、注文金額の合計が多い順にデータが並びます。

decile03.bmp

3. デシルランクを割り当てる
2でできたピボットテーブルの顧客IDと累積購入金額の入ったセルをすべて選択してコピーし、
新しいシートにペーストしたら、上から順番に、1、2、3...と順位を振ります。

decile04.bmp

順位を振ったら、デシルランクを割り振ります。
今回のサンプルでは、リストに含まれる顧客数は1,610名でしたので、161人ずつデシルランクを振ることになります。
顧客数が10で割り切れない場合には、一番下のランクに余りをすべて含めてしまうのが一般的です。
デシルランク下位の方が購入金額が少ないので、まとめても影響が少ないためです。
たとえば、デシルランク1が100名、ランク2も100名...、最後のランク10だけ105名というようになります。

デシルランクの割り振りは、手で入力してもいいですが、関数を利用すると便利です。
この例では、IF関数を利用して、順位と分割幅からデシルランクを割り振っています。

decile05.bmp

4. デシルランクごとの構成、傾向を分析する
デシルランクごとに購入金額を合計し、売上全体に占める割合を算出します。この例ではSUMIF関数を利用しました。
今回のサンプルでは、上位30%の顧客が、売上の約80%を占めていることが分かります。

decile06.bmp

デシル分析は以下のように活用できます。

・販促対象の選別
DMなどコストがかかる施策を行う際、購買力があり、売上への影響が大きいデシルランク上位の顧客のみを対象とすることで、費用対効果の向上が見込めます。

・自社の売上構造および課題の把握
デシルランク上位の顧客の売上構成比があまりに大きい場合、その少数の顧客が離反するだけで売上が激減してしまいます。上位顧客の離反防止策を講じるとともに、下位顧客の育成を検討した方がよいかもしれません。
一方、デシルランク間の売上構成比に大きな差がない場合、自社のファンと呼べる顧客が育っていない可能性があります。
売上がどのような顧客に支えられているのか把握することで、課題に気付くことができます。





■RFM分析
RFM分析とは、特定の期間における
・R(Recency)=最終購入日からの経過日数
・F(Frequency)=購入頻度
・M(Monetary)=累積購入金額
の切り口で顧客を分類する分析手法のことです。

デシル分析では購入金額のみで顧客を分類しましたが、2つの切り口を追加することで、顧客を多面的に分類し、よりきめ細かな施策を検討できるようになります。

それでは、ExcelでRFM分析を行う具体的な手順を見てみましょう。
おおまかな流れは以下のようになります。

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


1. 最終購入日、購入件数、累積購入金額を集計
元データをすべて選択範囲とし、Excelのメニューから「挿入>ピボットテーブル」を選択します。

ピボットテーブルで「行ラベル」に「顧客ID」を、
「値」に「注文日」、「注文番号」、「注文金額」をドラッグします。

それぞれの「値の集計方法」は以下のようにします。
・注文日:最大値 (表示形式は「日付」)
・注文番号:データの個数
・注文金額:合計

RFM01.bmp

できあがったピボットテーブルの顧客IDからと累積購入金額までの入ったセルをすべて選択してコピーし、
新しいシートにペーストします。



2. R、F、Mランクの割り当て
2-1) R(最終購入日からの経過日数)を計算する
最終購入日の右隣に1列挿入し「最終購入日からの経過日数」というラベルを付けます。
「R(最終購入日からの経過日数)」を計算するために、計算の起点日を決めます。
今回の例では、集計を実施した「2015/1/15」を起点日としました。
「最終購入日からの経過日数」の列に「=起点日-最終購入日」の計算式を記入し、列内のセルにコピーします。
これで経過日数が計算されます。

RFM02.bmp

2-2) RFMランクを定義する
RFMランクを定義します。データの分布などを見ながら、項目ごとに3つまたは5つのランクに分割するのが一般的です。今回の例では、以下の条件で5つのランクに分けることにしました。

RFM_list.bmp

※各定義は数値で入力し、セルの書式設定から「○日以内」などの見た目上の表示を追加すると、次項以降の計算がしやすくなります。当該セルで右クリックし「セルの書式設定>ユーザー定義」を選択した後、「分類」の入力ボックスの中に「0"日以内"」などと入力してOKすると、セル内の数値に「" "」でくくった文字列が、見た目の上でだけ追加されます(各定義は、厳密には「○以上△以下」となりますが、計算で利用するため、上記表のように記述しています)

RFM03.bmp

2-3) RFMランクの割り振り
2-1で作ったリストの右側に3列挿入し、それぞれR、F、Mのラベルを付けます。
IF関数で条件分岐させ、ランクを割り振ります。

RFM04.bmp

3. R・F・Mの構成を分析する
2までできれば、どの顧客が最近も購入してくれているのか/最近購入がないのか、
どのくらいの頻度で購入してくれているのかなどが分かります。

さらに全体の傾向を見るために、R(最終購入日からの経過日数)とF(購入頻度)でクロス集計してみましょう。
2でできあがったリスト全体を選択し、ピボットテーブルを作成します。
行ラベルに「R」を、列ラベルに「F」を、値に「顧客ID」(集計方法は「データの個数」)をドラッグすると、
以下のように顧客数が集計されます。

RFM05.bmp

RとFの掛け合わせにより、たとえば以下のように顧客を分類することができます。
・常連:購入頻度が高く、最近も購入している顧客
・新規:最近初めて購入した顧客
・リピーター:複数回購入しており、最近も購入している顧客
・離反予備軍:購入頻度が高かったのに、直近の購入履歴がない顧客
・離反優良顧客:購入頻度が高かったのに、ずっと購入のない顧客
・離反客:購入頻度が低く、ずっと購入のない顧客

RFM06.bmp

新規からリピーター、常連へと顧客を育成し定着させる(図の青の矢印)ことが望ましい姿です。
一方で、常連が何らかの理由で離反してしまうこと(図の赤の矢印)は避けたい事態です。
顧客がどのランクに属しているか分かれば、それぞれに適した施策を検討することができます。
また、分類ごとの顧客の割合が分かれば、自社の課題も発見しやすくなります。

※施策の具体例は「売上アップの方程式」もご参照ください。



最後に
デシル分析・RFM分析では、顧客のデモグラフィックな属性や、何を購入したかといった情報は取り扱いません。
したがって、これらだけでは顧客分析としては不十分な場面も考えられます。

一方で、この2つの分析は、特別なシステムがなくても簡単に集計できるという特長があります。そのため、顧客分析の第一歩として行ったり、継続して行ったりするのに適しています。同じ分析を定期的に行うことで、優良客が離反していないか、新規客がリピーターになっているかといった変化も分析できます。

また、デシル分析・RFM分析を起点として、さらに深掘りすべきポイントが見つかる場合もあります。
以前のコラムでも取り上げたように、効率的なデータ分析を行うには仮説が欠かせません。デシル分析・RFM分析から得られた気づきを仮説構築のベースとすることで、より高度な分析も効果的に行っていくことができます。

「顧客分析を行ったことがない」「継続的に分析を行っていきたい」という方は、手軽なデシル分析・RFM分析からスタートしてみてはいかがでしょうか。



フュージョンでは、これらの分析を含む顧客分析パッケージ『CRM ANALYZER』をご提供しています。分析の際にランクをどう切り分けるか、結果をどのように解釈するか、さらに分析をどのように具体的な施策に反映していくか、というのは分析のもっとも重要なポイントです。自社での分析に課題をお持ちであれば、ぜひマーケティングのプロであるフュージョンにご相談ください。

▼CRM ANALYZER
https://www.fusion.co.jp/crm/



※フュージョンでは無料メールマガジンを配信しています。
この記事のようなマーケティングに関わる話題を月2回ほど配信していますので、ぜひご登録ください。

▼フュージョンメールマガジンの配信登録はこちら
https://www.fusion.co.jp/contact/mailmagazine.html

メールマガジンのご登録はこちら

現場の最前線活躍する社員が執筆するマーケティングコラムをメールマガジンとして配信しています。
お陰様で多くの企業の購読者様よりご好評頂いているメールマガジンです。
実務に役立つセミナー情報も随時お届けしています。

FUSION(フュージョン)は戦略策定から購買データ分析、クリエイティブ制作、効果測定まで、
ダイレクトマーケティングに関わる業務全てを担う体制でサポートをご提案します。
お気軽にお問い合わせください。

数々の受賞実績のノウハウを詰め込んだ
フュージョンのマーケティングサービス

ダイレクトマーケティングのすべてを、eラーニングで学ぶ 無料資料をダウンロード

RECENT POSTS

ARCHIVES

CATEGORIES

数々の受賞実績のノウハウを詰め込んだ
フュージョンのマーケティングサービス