いまさら聞けないCRMの基礎知識②～売上分析とは？Excelでできる３つの分析手法を解説～

前回のコラム「いまさら聞けないCRMの基礎知識」では、CRMは「ニーズ・顧客行動の多様化」「新規顧客獲得 ＜ 既存顧客維持」「デジタル化の加速」を背景に、収益化を最大化させるために欠かせない仕組みであることをお話しました。
そして、CRMを進めていくための取り組みとして

1. データ分析による現状把握
2. 検証環境の構築
3. 施策の実行

をご紹介しました。
今回は、「１．データ分析による現状把握」をより詳しくご説明します。

まず一歩を踏み出す

CRMへの取り組みを阻む主な理由は、

　課題１　データを扱い切れない

　課題２　顧客の動きを把握できない

　課題３　業務過多で手が回らない

　課題４　PDCAを回せない（人手不足・ノウハウの欠如）

が挙げられますが（「いまさら聞けないCRMの基礎知識」参照）、どれも中長期的視点で取り組むことが大切です。これは短期的に成果を求められるマーケティング担当者にとっては、何とも悩ましい問題です。

ここで重要なことは、物事をシンプルに捉えることです。
完璧を目指して動かないよりも、まず一歩を踏み出してみましょう。どんなことであっても踏み出せば必ず結果が出ますし、その結果をどう活かすかの方がビジネスへの貢献ははるかに大きいです。

CRMが”実施できている状態”にするための取り組み（踏み出す一歩）は、

1. データ分析による現状把握
2. 検証環境の構築
3. 施策の実行

の３つです。
どれも簡単ではありませんが、難しく考える必要もありません。
ここからは「データ分析による現状把握」をご説明します。

データ分析による現状把握

「データ分析ができていないのだが、どうしたらいいのか分からない」というご相談をいただきますが、その場合のほとんどが「何のためにデータ分析をするのか？」が定まっていません。加えて、分析に必要とされるデータさえ存在していないことも少なくありません。 どんな料理を食べたいのかがなければメニューは決まりませんし、材料がなければ料理はできません。

そこで、まず『データ分析による現状把握』を行いましょう。
繰り返しになりますが、重要なことは「シンプルに捉える」ことです。データが足りない、分析ツールがないなど、すぐに解決できない問題は気にせず、今あるデータやリソースを使ってビジネスの現状を可能な範囲で把握しましょう。
分析結果から顧客理解を深めることで、顧客満足度を上げたり、購買率アップなどに役立てたり、最終的には自社の売上・利益アップにつなげることもできます。

３つの分析手法

現状を把握するための代表的な分析方法は次の３つです。

• 新規既存売上分析
• デシル分析
• RFM分析

新規既存売上分析

新規既存分析は、新規顧客、既存顧客の売上分布を確認するための分析です。
現在に至るまでの売上がどのように構成されてきたかを客観視できます。CRMの観点だと「既存顧客の売上が増えている＝CRMが貢献している」となります。

以下の図を使ってご説明します。
「売上 ＝ 新規顧客の売上 ＋ 既存顧客の売上」を表したグラフです。

売上は徐々に伸びていますが、4月からは既存顧客の売上が増え、割合にも変化が生まれていることが分かります。つまり、売上の伸長は既存顧客によって形成されていることとなり、リピートを原則とするビジネスであれば、順調と言って良いでしょう。
いたってシンプルな新規既存売上分析ですが、事実を知らずに要因分析に進んでしまい、迷走してしまうことは多々見受けられます。
この例の場合、4月以降の新規顧客の売上はそれまでに比べると減っています。「4月以降は広告出稿を大幅に減らしていた」という事実を把握していれば、なぜだろう？と頭を悩ますことはありませんし、割合が逆転したのは当然の結果と言うこともできます。

また、「何をもって既存顧客と呼ぶのか？」などの定義も曖昧になりがちです。
リピート購入を前提とする商材では２回目購入までを新規顧客、３回以上の購入を既存顧客と定義することもありますし、長期間取引のない顧客を新規顧客と定義することもあります。定義は自社の考え方次第になりますので、この際、関係者と前提条件や約束事を改めて確認しておきましょう。

デシル分析

デシル分析は、全顧客を累計購入金額の高い順に10等分して、各グループ（デシル1～10）の購入比率や売上高構成比を算出する分析方法のことです。

1,000名の顧客を抱えるECサイト運営者のケースでご説明します。

1. 1,000名の顧客を累計購入金額の高い順に並び替えます。
   （ただし、購入額が0円の顧客はあらかじめ除いておきます）
2. 1,000名の顧客を10等分します。つまり、100名ずつ10のグループに分けます。
3. 先頭のグループをデシル1、最後方のグループをデシル10と命名します。
4. 各グループの合計購入金額を算出します。このとき、デシル1の合計購入金額が最も高くなります。
5. 全体の購入金額に対する各グループの購入金額の割合を計算します。
6. 各グループの購入金額を構成する人数である100で割り、ひとりあたりの購入金額を算出します。
7. データがあれば、累計購入回数でも同様の計算を行います。

このケースでは、デシル1（上位100人）で過半数の売上を上げており、80％の売上をデシル4まで（上位400人）で上げていることが分かります。「デシル４までの顧客は優良顧客として、これからも通い続けてもらえるようなコミュニケーションをしよう」といった施策を検討できるようになります。
デシル分析は、特に優良顧客向けの施策を考える時に活用されることが主流ですが、BtoCやD2Cビジネスではそれぞれのグループの特性に合わせた施策の実行まで視野を広げ、全体最適化の指針に用いられることもあります。

初歩的な顧客分析方法であるデシル分析ですが、このようにある程度は有用となる示唆を得ることができます。Excelで手軽にできますので、実際のデータを使って分析してみましょう。

RFM分析

デシル分析と同じく、顧客分析の代表的なものとしてRFM分析が挙げられます。
デシル分析は購入金額のみで顧客をグループ化するのに対し、RFM分析は、

• Recency：最新購入日
• Frequency：購入頻度
• Monetary：累計購入金額

の３つの切り口でグループ化する手法です。

デシル分析では、過去に一度だけ高額商品を購入した顧客と最近少額だが大量購入した顧客が同じグループに分類される可能性がありますが、「直近ではいつ？」という概念が入っているRFM分析では、異なる購買行動として明確に分けて分析することができます。

【実践】RFM分析をExcelでやってみる

「顧客分析をしたことがない」「継続的に分析をしていきたい」という方は、日常業務で慣れ親しんでいるExcelでもできるデシル分析、RFM分析からスタートしてみてはいかがでしょうか。
ここからは、RFM分析をExcelで行う手順をご紹介します。

ステップ1．分析に必要なデータを準備する

RFM分析を始める上で必要となる元データは、

• 顧客ID（顧客を識別できる項目）
• 最新受注日
• 受注金額

です。

受注データなどを使って、前処理として、イメージのように顧客ID単位で取りまとめておきます。このコラムでは、これを元データと呼びます。

＜元データのイメージ＞

ステップ2．R（最終購入日からの経過日数）を計算する

元データの累計購入金額の列の右隣のセルに「経過日数」「集計日」と入力します。
今回の例では、集計を実施した2022年12月31日を集計期間の最終日としますので、集計日の隣りのセル（G1）に「2022/12/31」と入力してください。

経過日数を計算するには、Excelの【DATEDIF】という関数を使います。
顧客ID１の経過日数を表示するE1に『=DATEDIF(B2,$G$1,”d”)』と入力し、以下の行には式をそのままコピペしてください。自動的に経過日数が表示されます。

E2には「90」という結果が表示されていますので、顧客ID１は最終購入日から90日が経過していることになります。
顧客ごとの累計購入回数、累積購入金額、最終購入日からの経過日数が集計できたことで、RFM分析の準備が整いました。

ステップ3．RFMランクを定義する

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

1. 対象セルで右クリックし「セルの書式設定＞ユーザー定義」を選択
2. 「種類」に表示させたい文字列を入力（以下の図は「0″日以内”」の場合）

＜完成した元データ＞

ステップ4.　RFMランクを割り振る

完成した元データのF列から3列を挿入し、それぞれR、F、Mとタイトルを付けます。
それぞれIF関数を用いてランクを割り振ります。
Rの場合には、
「=IF(E2<=$M$2,$L$2,IF(E2<=M$3,L$3,IF(E2<=$M$4,$L$4,IF(E2<=$M$5,$L$5,$L$6))))」
という関数式になります。
こちらを参考にF、MもIF関数をF列、G列、H列に入力します。
少し難しいですが、参照するセルと大小だけを変更すれば良いので、頑張ってみましょう。

これでRFM分析の集計データが完成しました。

ステップ5．RFM分析を実行して、データに意味を持たせる

最終購入日、購入件数、累積購入金額を顧客ごとに集計
RFMそれぞれのランクに割り当て
を経て完成した集計データを、さらに意味あるものにするには、数値の解釈が重要になります。最後にデータの読み解き方をご紹介します。

（１）合計値

顧客IDごとにRFM３つの数値を合算し、その値が高ければ高いほど優良な顧客であると解釈します。合算するだけなので、こちらもExcelで簡単にできます。
合計値はとても分かりやすく簡単ですが、その分どうしても粗くなってしまいます。例えば合計値が10だとしても、R4, F3, M3（4+3+3）のケースもあれば、R1, F4, M5（1+4+5）のケースもあります。前者は「バランスの取れた顧客」、後者は「購入頻度も購入金額も高いが、最近は購入していない顧客」と言えるため、施策への落とし込みには注意を要します。

（２）クロス集計

RFMの３項目から２項目を選択し、クロス集計をかけます。
例えば、RとFを掛け合わせることで、以下のように顧客を分類することができます。

・常連：購入頻度が高く、最近も購入している顧客

・新規：最近初めて購入した顧客

・リピーター：複数回購入しており、最近も購入している顧客

・離反兆候：購入頻度は高かったが、直近の購入がない顧客

・離反元優良：購入頻度は高かったが、長らく購入がない顧客

・離反：購入頻度が低く、直近ではまったく購入のない顧客

それぞれにあった施策の立案をはじめ、どのランクの顧客が多いかなどを可視化することで、自社の課題の洗い出しや対策の優先順位づけもできるようになります。
ここではRとFのクロス集計をご紹介しましたが、FとMでクロス集計を行うことも有用です。

いかがでしょうか。
CRMが実施できている状態にするための取り組み「データ分析による現状把握」をご紹介しました。
分析と聞くと躊躇してしまうかもしれませんが、今回ご紹介したデシル分析とRFM分析は顧客の年齢などの属性情報や何を購入したかといった購買履歴は用いないので、Excelで簡単に集計できるという特長があります。顧客分析の第一歩に留まらず、定期観察にも適しています。

次回は、デシル分析やRFM分析から得られた気づきをいかに活かすかという観点を交えながら、

２．検証環境の構築

３．施策の実行

についてお話します。
キーワードは『共通言語化』です。

クロス・コミュニケーションは、顧客と企業の良好な関係を築き、深めるCRM戦略を「戦略立案」から「実務支援」までをワンストップでご提供します。
CRMについてのお悩みをお持ちでしたら、お気軽にお問い合わせください。