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

顧客分析は、自社の業績をアップさせるために必要な取り組みです。しかし、具体的にどのように分析に取り組めばよいかと問われると、戸惑ってしまう方がいるのも事実ではないでしょうか。
このコラムでは、手軽に使えるExcelを活用し、顧客分析の基本をわかりやすく解説します。代表的な手法であるデシル分析とRFM分析を取り上げ、Excelを用いた身近で実践的なアプローチを紹介します。

顧客分析とは

顧客分析は、顧客の行動と傾向を分析し、より効果的なマーケティング戦略を形成するプロセスです。デシル分析では、顧客を収益性に応じて10のグループに分け、各グループの特徴を把握します。RFM分析は、顧客の最終購入日、購入頻度、購入金額を基に評価し、重要な顧客を特定します。これらの方法を使うことで、企業は顧客データを活用し、効果的なマーケティング戦略を立てることができます。

顧客分析を行うツール

顧客分析ツールには、BIツール、CRMシステム、SFAツール、MAツール、データマイニングツールなどなど、多種多様なものがあります。これらのツールは、それぞれ独自の用途と特徴を持ち、詳細な顧客データの分析と活用が可能です。
ただ、顧客分析を始めるにあたっては、Excelのような身近で基本的なツールから手を付けることも有効です。Excelは、データの整理や基本的な分析、グラフ作成など、顧客分析の最初の一歩には充分なツールと言えます。

顧客分析ツールの例

1. BI（ビジネスインテリジェンス）ツール
   ○    用途：複雑なデータセットの分析、視覚化
   ○    特徴：詳細な洞察提供、高度なデータ処理能力
2. CRM（顧客関係管理）システム
   ○    用途：顧客情報の一元管理、マーケティング活動の分析
   ○    特徴：顧客データの統合、セールスとマーケティングの連携強化
3. SFA（セールスフォースオートメーション）ツール
   ○    用途：営業プロセスの自動化、顧客管理の効率化
   ○    特徴：営業活動の効率向上、顧客データの活用促進
4. MA（マーケティングオートメーション）ツール
   ○    用途：マーケティング活動の自動化、リード生成と育成
   ○    特徴：効果的なマーケティングキャンペーンの実施、顧客エンゲージメントの向上
5. データマイニングツール
   ○    用途：データパターンの発見、予測分析
   ○    特徴：複雑なデータから有用な情報抽出、予測モデルの構築

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

Excelで顧客分析を始めるために、まずは売上明細など分析に使うための元データを準備しましょう。今回は、2020年1月1日～12月31日の一年間に購入履歴のある顧客を対象とし、2021年1月15日に分析を行う設定にします。

データ内に必要な項目

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

※ RFM分析を行う場合の注意点
注文番号が重複している際は注意が必要です。例えば、一回の注文で複数商品を購入している場合など、注文番号が重複しているケースがあります。
データの中に注文番号が重複している場合は、前処理として以下のように注文番号単位で注文日・顧客ID・注文金額をまとめたデータを準備しましょう。

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

Excelを使ったデシル分析

デシル分析は、顧客を購入金額の多い順に10のグループに分け、各グループの購入傾向を分析する方法です。この分析を通じて、売上に大きく貢献する顧客グループを特定でき、販促活動の効果を高めることに役立ちます。また、売上構造の分析を通して、自社の課題を発見することも可能です。
それでは、Excelでデシル分析を行う際の手順を見てみましょう。
おおまかな流れは以下の4ステップです。

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

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

ピボットテーブルを使用して顧客ごとの累計購入金額を求めましょう。

• データが入力されているセルの範囲を選択します。
• 「挿入」タブから「ピボットテーブル」をクリックします。
• 「ピボットテーブルの作成」の中の「ピボットテーブルレポートを配置する場所を選択してください。」で「新規のワークシート」を選択して「OK」をクリックします。

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

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

ランク分けを行うために累計購入金額が大きい順に並び替えましょう。

• 注文金額が入力されている列の一番上の数値を右クリックし、「並べ替え＞降順」をクリックします。これにより、注文金額の合計が多い順にデータが並びます。

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

ランクを割り当てる前にリストを10等分しましょう。

• 新しいシートを作成しA1、B1、C1にそれぞれ「順位」「顧客ID」「期間内購入金額」の項目を作成します。
• 注文金額を降順で並び替えたデータの顧客IDと注文金額をすべてコピーし、「顧客ID」「期間内購入金額」の下にペーストします。
• 「順位」の列に上から1、2、3...と番号を振ります。

次に、デシルランクを割り当てましょう。
今回のサンプルでは、リストに含まれる顧客数は1,610名でしたので、10で割って161人ずつデシルランクを割り当てます。
顧客数が10で割り切れない場合には、一番下のランクに余りをすべて含めてしまうのが一般的です。下位の方が購入金額が小さく、影響が少ないためです。
デシルランクの割り当てには、関数を利用します。
IF関数を利用して、順位と分割幅からデシルランクを割り振っていきましょう。

• F1に「分割幅」の項目を作り、F2にデータ数を10で割った数値を入力します。
• D1に「デシルランク」の項目を作り、D2以降に画像内のIF関数を入力します。

※「分割幅」の数字ずつD1～D9まで入力され、残りはD10が入力される関数を書いています

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

デシルランクごとに購入金額を合計し、売上全体に占める割合(累積構成比)を算出しましょう。
この例ではSUMIF関数を利用して割合を算出しています。

• H列からL列に画像のとおり表を作りましょう。
• 「購入顧客数」にはランクごとの購入者数を入力します。
• 「購入金額合計」のJ2に画像のSUMIF関数を入力し、J11までコピーします。
• 「構成比」は各ランクの購入金額合計/合計金額×100です。
• 「累計構成比」は上位ランクを含めた「構成比」の合計です。

今回のサンプルでは、デシルランク1～3の上位30％の顧客が、売上の約80％を占めていることが分かります。

デシル分析のデータ活用例

デシル分析の活用例を2つ紹介します。

• 販促対象の選別
  
  • DMなどのコストがかかる施策を行う際、購買力が高く、売上への影響が大きいデシルランク上位の顧客のみを対象とすることで、費用対効果の向上が見込めます。
• 自社の売上構造および課題の把握
  • デシルランク上位の顧客の売上構成比があまりに大きい場合、その少数の顧客が離反するだけで売上が激減してしまいます。上位顧客の離反防止策を講じるとともに、下位顧客の育成も検討した方が良いでしょう。
  • 一方、デシルランク間の売上構成比に大きな差がない場合、優良顧客が育っていない可能性があります。売上がどのような顧客に支えられているのか把握することで、自社の課題に気付くことができます。

Excelを使ったRFM分析

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

デシル分析では購入金額のみで顧客を分類しましたが、２つの切り口を追加することで、顧客を多面的に分類し、より細やかな施策を検討できるようになります。
それでは、ExcelでRFM分析を行う具体的な手順を見てみましょう。
おおまかな流れは以下の3ステップです。

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

ステップ1.　顧客ごとに「期間内の最終購入日」「購入件数」「累積購入金額」を集計する

• 元データをすべて選択し、「挿入」タブから「ピボットテーブル」をクリックします。
• 「行ラベル」に「顧客ID」をドラッグします。
• 「値」に「注文日」、「注文番号」、「注文金額」をドラッグします。
  それぞれの「値の集計方法」は以下のようにします。
  • 注文日：最大値　（表示形式は「日付」）
  • 注文番号：データの個数
  • 注文金額：合計

• 作成されたピボットテーブルの「行ラベル」から「合計/注文金額」までをすべてコピーし、新規シートにペーストします。
• 次のステップの画像のとおり、項目名を「顧客ID」「最終購入日」「注文回数」「合計注文金額」にそれぞれ変更します。

ステップ2.　最終購入日、購入件数、累積購入金額によって、R・F・Mのランクを割り当てる

• R（最終購入日からの経過日数）を計算する
  • 「最終購入日」の列の右隣に1列挿入し「最終購入日からの経過日数」という項目を作ります
  • 「R（最終購入日からの経過日数）」を計算するために、計算の起点日を決めます。
  • 今回の例では、集計を実施した「2021/1/15」を起点日としました。（J列）
  • 「最終購入日からの経過日数」の列に「=起点日-最終購入日」の計算式を記入し、列内のセルにコピーします。これにより、経過日数が計算されます。

• RFMランクを定義する

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

※各定義は数値で入力し、セルの書式設定から「○日以内」などの見た目上の表示を追加すると、次項以降の計算がしやすくなります。
下記の方法で見た目上の表示を追加することができます。

• 対象セルで右クリックし「セルの書式設定＞ユーザー定義」を選択
• 「種類」の入力ボックスの中には表示させたい文字列を入力(例では、「0"日以内"」と入力)

この設定により、セル内の数値に「" "」でくくった文字列が追加されます。
（各定義は、厳密には「○以上△以下」となりますが、計算で利用するため表のように記述しています）

• RFMランクを割り振る
  • 作成したリストの右隣に3列挿入し、それぞれR、F、Mの項目を作ります。
  • R、F、M別にIF関数で条件分岐させ、ランクを割り振ります。

ここまで作成できれば、直近の購入の有無や、どのくらいの頻度で購入してくれているのかなどが分かります。

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

さらに全体の傾向を見るために、R（最終購入日からの経過日数）とF（購入頻度）でクロス集計してみましょう。

• 作成したリスト全体を選択し、ピボットテーブルを作成します。
• フィールドリストの行ラベルに「R」をドラッグします。
• 列ラベルに「F」をドラッグします
• 値に「顧客ID」（集計方法は「データの個数」）をドラッグします。そうするとRとFを掛け合わせた顧客数が集計されます。

RとFを掛け合わせることで、以下のように顧客を分類することができます。

• 常連：購入頻度が高く、最近も購入している顧客
• 新規：最近初めて購入した顧客
• リピーター：複数回購入しており、最近も購入している顧客
• 離反予備軍：購入頻度が高かったが、直近の購入履歴がない顧客
• 離反優良顧客：購入頻度が高かったが、長い間購入のない顧客
• 離反客：購入頻度が低く、長い間購入のない顧客

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

※施策の具体例は「売上を上げる6つの方法とマーケティング課題の設定・解決策検討プロセス」もご参照ください。

RFM分析の注意点 

RFM分析は、業界や商材に関わらず基本的な購買データのみを用いて、定量的なデータを計測できる利点がありますが、一方で注意点もあります。

注意点① 「Recency」（最終購入日からの経過日数）への注目

例えば、毎年4月にRFM分析を行っているとします。この場合、お中元の時期（8月）に毎年購入してくれる顧客の「Recency」は240日となり、「優良顧客」として認識することはできないでしょう。
しかし、このような顧客に離反されてしまうと、お得意様を失うだけでなく、その商品を受け取った人が新規顧客になる機会も失ってしまいます。
このような顧客を失わないためには、RFM分析を定期的かつ継続的に実施し、顧客ごとに状況を把握することが必要です。定期的に行うことで、ある程度長いスパンでリピートしている顧客を見つけられるでしょう。

注意点② 指標と顧客属性の関係

例えば、1千円の定番商品を毎月購入している顧客と、1万円の限定商品を一度だけ購入した顧客の最終購入が同日だったとします。その場合、R(最終購入日からの経過日数)とM(累積購入金額)の指標で見ると同一のセグメントに属することになるでしょう。
しかし、F（購入頻度）とM（累積購入金額）の組み合わせで見ると、累積購入金額が同じでも繰り返し購入してくれている顧客と、一度だけ高額購入した顧客であることがわかります。このふたりの属性が異なることは明らかで、それぞれに実施すべき施策も当然異なってくるでしょう。

また、RFM分析を他の分析と併用することも有効です。
例えば、おもちゃ屋での購入が途絶えた顧客の購入履歴を分析し、主にベビー用品を購入していたとわかれば、子どもの成長に伴うニーズの変化が起こったと推測できます。それにあわせて適切な年齢層の商品を訴求すれば、「離反客」が再度店舗を訪れる可能性を高められるでしょう。

まずはExcelでできる顧客分析から

デシル分析・RFM分析では、顧客のデモグラフィック属性（年齢や性別、年収、学歴など、個人の社会的な属性）や、具体的な購入品の情報は取り扱いません。そのため、特別なシステムを使用しなくても Excelで簡単に集計できるという特長があります。
顧客分析の第一歩としてはもちろん、継続して行うことにも適しています。定期的に分析することで、優良客が離反していないか、新規客がリピーターになっているかといった変化を見ることができます。

そして、効率的なデータ分析を行うには仮説が欠かせません。デシル分析・RFM分析から得られた気づきを仮説構築のベースとすることで、より高度な分析を行っていくことにつながります。

フュージョン株式会社では、顧客基礎分析レポートサービス「CRM ANALYZER」を提供しています。企業の顧客購買データから、売上分析・顧客分析・商品分析の3つの軸で基礎分析を行います。データの集計にとどまらず、どのようにランクを切り分けるか、分析結果をどのように解釈するか、さらに具体的な施策に反映していくかを網羅しています。マーケティングの戦略策定から施策実行までの各フェーズで幅広く活用できる内容になっています。ご興味のある方は、ぜひサービス資料をご覧ください。

顧客基礎分析レポートサービス「CRM ANALYZER」資料ダウンロードはこちら

フュージョン株式会社では、マーケティングに役立つ情報を定期的にメールにて発信しています。ぜひ以下のフォームからご登録ください。
メールマガジンの登録はこちらから