FUSION LOGO
    close

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

    2015-10-30

    目標達成の道しるべ「KPI」を設定しよう

    マーケティング分析の一つに顧客分析があります。

    顧客分析は、顧客数の推移や、購入金額・購入頻度、最終購入日からの経過日数といった切り口で分析を行うことで、顧客の購買行動の状況を把握することができます。売上アップや商品・サービスの改善にも深く関連する有効性の高い手法です。顧客中心のマーケティングには顧客分析は必須、とわかってはいても、アナリストでもない限り社内の膨大なデータを取り扱うのはハードルが高く感じるものです。また社外の専門企業に分析を依頼するのは、セキュリティ面など懸念から禁止としている企業も少なくありません。

    そこで今回のコラムでは、顧客分析の基本手法であるデシル分析とRFM分析について、Excelでできる手順と活用方法についてご紹介します。

    なお、下記の関連コラムで顧客分析に重要な2つのポイントについて解説しています。
    こちらもあわせてご一読ください。

    ▼関連コラム「一目置かれる、データに基づく分析資料とは?」

    ――――――――――――――――――――――

    1. 顧客分析を始める前に準備するもの

    ――――――――――――――――――――――

    顧客分析を始める際は、売上明細など分析に使うための元データが必要となります。
    例として、
    2020年1月1日~12月31日に購入履歴のある顧客を対象とし、2021年1月15日に分析を行うという想定です。※架空のデータです

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

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

    ▼関連コラム「データをマーケティングに生かすために~データの前処理とは?」

    目標達成の道しるべ「KPI」を設定しよう

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

    ――――――――――――――――――――――

    2. Excelを使ったデシル分析

    ――――――――――――――――――――――

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

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

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

    ①顧客ごとに期間内の累積購入金額を計算する
    元データが入力されているセル範囲を選択し、メニューから「挿入>ピボットテーブル」を選択します。今回は新規シートにピボットテーブルを作成します。

    目標達成の道しるべ「KPI」を設定しよう

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

    目標達成の道しるべ「KPI」を設定しよう

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

    目標達成の道しるべ「KPI」を設定しよう

    ③ ②のリストを10等分し、デシルランクを割り当てる
    ②が反映されたピボットテーブルの顧客IDと累積購入金額の入ったセルをすべてコピーします。
    新規シートにペースト後、上から順番に、1、2、3...と順位を振ります。

    目標達成の道しるべ「KPI」を設定しよう

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

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

    目標達成の道しるべ「KPI」を設定しよう

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

    目標達成の道しるべ「KPI」を設定しよう

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

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

    ――――――――――――――――――――――

    3. Excelを使ったRFM分析

    ――――――――――――――――――――――

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

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

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

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

    ① 顧客ごとに期間内の最終購入日、購入件数、累積購入金額を集計する
    元データをすべて選択範囲とし、Excelのメニューから「挿入>ピボットテーブル」を選択します。
    ピボットテーブルで「行ラベル」に「顧客ID」を、「値」に「注文日」、「注文番号」、「注文金額」をドラッグします。
    それぞれの「値の集計方法」は以下のようにします。
    ・注文日:最大値 (表示形式は「日付」)
    ・注文番号:データの個数
    ・注文金額:合計

    目標達成の道しるべ「KPI」を設定しよう

    作成されたピボットテーブルの顧客IDから累積購入金額(注文金額)までの入ったセルをすべてコピーし、新規シートにペーストします。

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

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

    目標達成の道しるべ「KPI」を設定しよう

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

    目標達成の道しるべ「KPI」を設定しよう

    ※各定義は数値で入力し、セルの書式設定から「○日以内」などの見た目上の表示を追加すると、次項以降の計算がしやすくなります。
    下記の方法で見た目上の表示を追加することができます。
    1.対象セルで右クリックし「セルの書式設定>ユーザー定義」を選択
    2. 「種類」の入力ボックスの中には表示させたい文字列を入力(例では、「0"日以内"」と入力)

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

    目標達成の道しるべ「KPI」を設定しよう

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

    目標達成の道しるべ「KPI」を設定しよう

    ここまで作成できれば、どの顧客が最近も購入してくれているのか/最近購入がないのか、どのくらいの頻度で購入してくれているのかなどが分かります。

    ③R・F・Mの構成を分析する
    さらに全体の傾向を見るために、R(最終購入日からの経過日数)とF(購入頻度)でクロス集計してみましょう。
    ②で作成したリスト全体を選択し、ピボットテーブルを作成します。
    フィールドリストの行ラベルに「R」を、列ラベルに「F」を、値に「顧客ID」(集計方法は「データの個数」)をドラッグすると、RとFを掛け合わせた顧客数が集計されます。

    目標達成の道しるべ「KPI」を設定しよう

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

    例えば、新規顧客の場合、リピーター、常連へと顧客を育成し定着させることが望ましい姿です(図の青の矢印)。
    一方で、常連が何らかの理由で離反してしまうことは避けたい事態です(図の赤の矢印)。

    顧客がどのランクに属しているか分かれば、それぞれに適した施策を検討することができます。
    また、分類ごとの顧客の割合が分かれば、自社の課題も発見しやすくなります。

    目標達成の道しるべ「KPI」を設定しよう

    ※施策の具体例は「【鉄則】売上を上げるための6つの方法」もご参照ください。

    RFM分析では、基本的な購買データで業界や商材に関わらず、定量的なデータを計測できる利点がありますが、将来の購買行動を予測することなどはできません。RFM分析の注意点や限界については、関連コラム「RFM分析の限界とその対処法」の中でも解説していますので、あわせてご一読ください。

    ――――――――――――――――――――――

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

    ――――――――――――――――――――――

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

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

    「顧客分析を行ったことがない」「継続的に分析を行っていきたい」という方は、普段の業務でもよく使うExcelでもできるデシル分析・RFM分析からスタートしてみてはいかがでしょうか。

    フュージョンでは、これらの分析を含む顧客分析パッケージ『CRM ANALYZER』をご提供しています。
    『CRM ANALYZER』は、企業の顧客購買データをお預かりし、売上分析・顧客分析・商品分析の3つの軸で行う基礎分析レポートです。データの集計にとどまらず、分析の際にランクをどう切り分けるか、結果をどのように解釈するか、さらに分析をどのように具体的な施策に反映していくか、までを網羅していますので、マーケティングの戦略策定~実行まで幅広く活用いただける内容になっています。
    下記のサービス資料では、レポートのイメージサンプルもご紹介しております。
    社内でデータ分析をしているが自信が持てない、データからわかる示唆が欲しい、という方、ぜひご参考になさってください。

    CRM ANALYZERのサービス詳細はこちら

    • facebook
    • mail

    関連する記事

    マーケティングに役立つ資料ノウハウを公開中!

    カテゴリー

    メルマガ登録

    役立つマーケティング情報やセミナー情報、弊社のサービスのご案内等をお届けします。