Excelで顧客管理・CRM/SFA分析について

libe-irevo@master 2024年1月2日

エクセルで顧客管理に役立つツールをCRM/SFA分析についてまとめてみました。

CRM分析

CRM（Customer Relationship Management）は、顧客情報を管理・分析し、顧客との関係性を強化できるツールです。顧客管理システムという名前のとおり、顧客情報や購買履歴、行動履歴など、顧客に関する幅広い情報を一元管理できます。

CRMには以下のような機能が搭載されています。

顧客情報管理
メール配信機能
問い合わせ管理機能
分析・レポート機能
外部サービスとの連携機能

顧客情報の管理に特化しており、フォーマットを用意することなく顧客情報をまとめられるのが特徴です。また、顧客情報の追加や更新も簡単に行えるため、効率よく情報を管理できるでしょう。

SFA分析

SFA（Sales Force Automation）は、営業活動を支援してくれるツールのことです。顧客情報や商談内容、案件の進捗など、営業部門が扱う情報を蓄積・分析できます。

SFAには以下のような機能が搭載されています。

顧客情報管理機能
案件管理機能
商談管理機能
営業担当者の行動管理機能
予実管理機能

CRMは顧客との関係性構築を目的としている一方、SFAは営業活動の強化を目的としているのがポイントです。

ただし、どちらも顧客情報を効率よく管理できるツールであり、SFAとCRM双方の機能を持つツールや、両者を連携して使えるツールも複数存在します。

分析例2-1 販売状況の分析計画 vs 実績

最初の分析は、販売状況を計画対実績で比較する分析です。特定月における部署毎の売上実績を把握しています。

[計画売上金額]はこの月の目標値、[売上金額]はこれに対する実績値です。この対比、つまり[売上金額]/[計画売上金額]を[月度達成率]で表しています。また年度計画に対する達成度を同様に[年度達成率]にて表しています。更にそれぞれの部署毎の[粗利金額]と[粗利率]を表示し、部署毎の収益性も合わせて把握できるようにしています。
当然ながら売上が思わしくない部署に対してはプッシュが必要ですが、合わせて追加のテリトリー配分、必要なトレーニング、販売プランや戦略の見直しなども検討する必要があるでしょう。
一方で全ての営業部が目標を達成できるとは限らず、パレート最適の理論でよく言われるように特定の営業部が売上の大部分を構成するということも有り得ます。ここでは既に年度達成率が100%以上、つまり年度目標を達成している営業部も有りますが、この部署がクールダウンをしないように、新たに結果を上積みできるようなインセンティブを提供することも必要となるでしょう。このレポートは部署毎に見ていますが、同様のことは営業部内の営業担当者個人にも当てはまります。

分析例2-2 プロダクトライン別販売状況の分析

2つ目の分析では、商品ライン毎の分析をしていきます。

縦軸には商品ラインをリストし、[計画売上金額]、[売上金額]、[粗利金額]を表示しています。また、収益性と成長性を表す指標として[粗利率]と[前年成長率]を置いています。この2つの指標から、各商品ラインの力の入れ具合やバランスを理解していくことが可能となります。また、[平均販売日数]、[売上数量]、そして[売上金額]/[売上数量]で導き出した[平均販売価格]を算出しています。この3つの指標は販売プロセスや売上目標を達成する上で検討しなければならない指標です。例えば、[平均販売価格]が高い商品は、一見すると販売効率は良いかもしれませんが、お客様側の商品理解や意思決定に長い期間を要し、結果的に販売プロセスが長くなる可能性も有ります。一方で販売数量が多く、販売日数もかからない代わりに、販売価格が大きくない場合もありえるでしょう。毎月の売上を形作るという観点からは、このバランスも重要なコントロールの指標となることでしょう。

分析例2-3 営業生産性の分析

3つ目の分析は、営業生産性に関する分析です。

縦軸に営業担当者をリストし、横軸には[担当顧客数]、[売上金額]、顧客あたりの単価である[売上金額/顧客]、[訪問回数]、[売上金額/訪問]、[訪問数/顧客]を置いています。[売上金額/顧客]は[売上金額]/[担当顧客数]にて、[売上金額/訪問]は[売上金額]/[訪問回数]にて、[訪問数/顧客数]は[訪問回数]/[担当顧客数]にてそれぞれ算出しています。
今日の販売活動では一般的に如何にきちんとしたプロセスを踏み、目的を持った訪問活動を行い、販売に結びつけるかが重要になるため、訪問回数あたりの売上金額の規模を大きくすることが求められる姿になります。一方で顧客との関係をきちんと維持するためには最低限の訪問も必要であるため、顧客あたりの訪問回数も重要な指標の1つとなることでしょう。
これらの分析を進めていく中で、標準的に必要となる訪問数等を理解し、テリトリー配置の増減を行うことによって営業担当者のワークロードを平準化することが必要です。当然ながら販売プロセス下にある商談でテリトリー変更をすることは無用なプロセスの妨げになるため、これらが実施されるべきタイミングや顧客毎の状況は考慮されるべきです。また合わせて生産性の低い営業担当者に対するコーチングやサポートも必要となることでしょう。

分析例2-4 販売プロセス分析

4つ目の分析では、販売プロセスに関する分析を行います。ここでは縦軸に営業部をリストして、それぞれの部署毎の販売プロセスの進行状況を理解することが可能となります。

[顧客数]の下にある[全顧客数]はそれぞれの部署が担当している顧客総数となります。そして[製品紹介]、[製品デモ]、[役員面接]、[見積提示]、[契約済み]はそれぞれシーケンシャルな販売プロセスを意味し、[全顧客数]はこのうちのどれかに分類されます。当然ながらこのようなプロセスは扱う商品やサービスによって異なるため、企業毎に定義される必要が有り、このプロセス完了毎にSFAに営業担当者が入力する必要が有ります。この分析の場合は、ここから契約間近の顧客数がどの程度あるか、リード生成が不十分な営業部はどこかを理解することが可能となります。さらにここでは、[営業担当者数]と、[稼動顧客数/営業担当]という指標値を利用して、リード生成の状態を把握することが可能となっています。[稼動顧客数/営業担当]は([製品デモ]+[役員面談]+[見積提示])/[営業担当者数]にて算出され、商談が進んでいる、つまり稼動状態にある顧客数が、営業担当者あたりどの程度存在しているかを把握することが可能な指標値です。また担当している全顧客数にたいして、どの程度が契約まで完了したのかを示す指標として[契約転換率]を示しています。言い換えればこれは的中率やクローズレシオと言われるものと同義です。
ここから、営業サポート人員の配置を稼動顧客数の状況に合わせて最適化する、経過的にプロセスを把握することにより、止まりがちなプロセスを理解し、必要な施策を実施するということが必要となります。

分析例2-5 スループット分析

続いての分析は、個人別の販売プロセス分析です。2-4の販売プロセス分析ではプロセス毎の顧客数を理解しましたが、ここではプロセス毎にそれぞれの営業担当者が利用している時間を把握します。

縦軸に営業担当者とそれぞれの階級を示し、プロセス毎、そして合計の[平均経過日数]を表示させています。絞込み条件として同じ商品ラインを販売しているため、このプロセスにかかる日数が営業担当者毎に違う場合、そのプロセスをうまくこなせる営業担当者と、逆に時間がかかってしまう営業担当者がいることが理解できます。当然ながら営業担当者の資質によって得意なプロセスと不得意なプロセスがありますが、そのプロセスをうまくこなす営業担当者の成功事例を共有することによって、スタンダートレベルを上げることが可能となりますし、それぞれの営業担当者にとっての苦手なプロセスを上司や周りが理解し、トレーニング機会の提供等の必要なサポートを行うことも必要となります。

分析例2-6 ボトルネック分析

6つ目の分析はボトルネックの分析です。

2-4及び2-5と酷似したレポートフォーマットを利用しますが、販売プロセスの指標値として利用されているのは、[減耗率]という指標値です。これは前のプロセスから次のプロセスに移すことができなかった顧客が、前のプロセスの顧客に対してどの程度存在していたのかを算出しています。例えば製品デモ後の減耗率が高い営業担当者は、顧客に対して製品のデモンストレーション時に製品価値のアピールがきちんとできず、結果的に次のプロセスへ顧客を持っていくことができないケースが多かったことが想定できます。また、最初の製品紹介の部分は、全顧客数からの減耗率を表しています。この場合、製品紹介に問題があったか、初回訪問からの関係を築くことができなかった可能性が考えられます。またそもそもの全顧客数としてターゲティングした顧客リストが、適切なターゲットであったのかも検討しなければなりません。このように減耗率が高いプロセスを理解し、営業担当者-プロセスの組合せ毎に改善のための施策を行うことが必要となり、この分析はそれらのベースとなる指標値を理解することを可能としています。
また、減耗率だけでは実際の契約ボリュームを理解することは難しいため、合わせて[契約顧客数]及び[契約転換率]を表示しています。

分析例2-7 クロスセル分析

最後の分析はクロスセリングの分析です。ここでは、関連性の高い商品ラインの組合せを理解し、この組合せで商品ラインを購入していない顧客を見つけ出し、販売活動のターゲティングに活かす事が目的となります。

絞込条件として商品ラインAを購入した顧客だけを対象として、それぞれ共通の属性を持つ顧客毎にセグメントとしてグルーピングしています。そして横軸には併買の商品ラインを表示し、指標値としては、[併買率]を表示させています。これは当該セグメントで商品ラインAを購入した顧客のうち、どの程度の顧客が併買商品を購入しているかを割合で表しています。50%以上の併買率を示している場合は、ピンクで色分けしています。これはそのまま関連性が高いことを意味しますが、一方で100%でないということは、残りの顧客はこの商品の組合せを購入していないため、販売機会が存在することを示しています。例えば、顧客セグメントE、商品ラインBのグリッドは93%と表示されていますが、残り7%は商品Bを購入していません。もちろん個別の顧客によっては必要ないために購入しない可能性もあるかもしれませんが、93%もの顧客がこの併買を実施したのには何かしらの理由があるはずです。この理由を理解し、残りの7%の顧客に対して販売活動をすることは合理的な販売活動と言えるでしょう。ここでは7%に該当する顧客をリストアップし、合わせて営業担当者と営業担当者の電話番号を表示しています。ここにリストされた営業担当者はこの販売機会を知るべきでしょうし、表示されているように、特定の営業担当者が何らかの理由で(販売スキルがない等)アプローチをしていないのがその7%の理由かもしれません。いずれにしても、共通の属性を持つ顧客が同一の活動をするであろうという前提に立ち、クロスセリングの機会を発見するための基礎情報として役立てることが可能となります。

CRMやSFAで顧客管理を行うメリット

CRMやSFAで顧客管理を行うメリットは以下のとおりです。

顧客情報をリアルタイムで管理・共有できる
顧客情報を効率よく分析できる
外部サービスと連携できる

ここでは、それぞれのメリットについて解説します。

顧客情報をリアルタイムで管理・共有できる

CRMやSFAは、顧客情報をリアルタイムで管理・共有できるのがメリットです。

登録した情報は、リアルタイムでシステムに反映されます。そのため、最新版ではないファイルを参照してしまうリスクを防げるでしょう。

また、IDとパスワードがあれば同じシステムにアクセスできるため、データ共有も簡単です。他部署への共有もスムーズに行えるため、部門をまたいで顧客情報を活用できます。チームでの同時編集が可能なツールも多く、エクセルのようにわざわざ共有用のファイルを作る必要がありません。

クラウド型の場合は、インターネット環境があれば社外からアクセスできるのもメリットです。スマートフォンやタブレットからツールを操作できるため、出先で商談情報の記録や新規顧客情報の入力なども行えます。

顧客情報を効率よく分析できる

CRMやSFAは、顧客情報を効率よく分析できるのも魅力です。

CRMやSFAの中には、データを集計して自動で分析してくれる機能を持つものが多く存在します。分析結果から表やグラフを自動で作成してくれるものもあり、統計の知識がない方でも簡単に顧客情報を分析できます。

CRMやSFAを活用することで、収集した顧客情報を効率よく分析し、結果をマーケティング戦略の策定や営業活動の改善などに活かせるでしょう。

外部サービスと連携できる

CRMやSFAの中には、外部サービスと連携して使えるものも多いです。

CTIやMAツール、メール配信ツール、ERPなどの外部サービスと連携することで、さらに活用の幅が広がります。

たとえば、コンピューターと電話をつなぐCTIと連携させることで、顧客情報を確認しながら顧客からの問い合わせに対応できるようになります。コールセンター業務の効率化と、顧客満足度向上につながるでしょう。

また、MAツールやメール配信ツールと連携させることで、顧客の状況に合わせた適切なコンテンツを配信できるようになります。

外部サービスと連携できるツールを選び、導入効果を高めましょう。

データドリブンな営業マンになるなら最低限おさえておきたい4つの基本指標

営業またはセールスの仕事をされている方たちがデータドリブンになるために、ビジネスの指標をモニターしたいが、どこから始めていいか分からず最初の一歩を踏み出せない、といった相談をよく受けます。

そこで、今回は一般的な売切り型のビジネス（サブスク/SaaS型のビジネスでない）の分野においてデータドリブンな営業の方たちが確実に押さえている基本的な4つのトップライン指標を紹介したいと思います。

なお、ビジネスモデルが異なるサブスク/SaaS型のビジネスで見ておきたい指標は売切り型のビジネスと少し異なりますが、そちらは関してはこちらの「サブスク型ビジネスデータ分析」ページで紹介しておりますので、ぜひご覧ください。

1. 売上を増やす2つのエンジン – 新規顧客と既存顧客

当たり前ですが、営業にとって最も重要なことは売上を増やすことです。

そこで売り上げを増やすために何をしなければいけないかを考えるとき、売上を「既存顧客からの売上」と「新規顧客からの売上」に分解して考えることが重要になります。

というのも、この2つのタイプの売上を上げるためのアクションは異なることが多いからです。

例えば、「既存顧客からの売上」を増やすには1人あたりの売上を増やすための施策や商品を購入してもらう回数を増やすための施策が重要となります。

逆に、「新規顧客からの売上」を増やすには、例えば広告や口コミなどを使って自分たちのビジネスをより多くの人達に知ってもらうための施策などが重要となります。

そこで、「既存顧客からの売上」と「新規顧客からの売上」を切り分けたうえで、それぞれのパフォーマンスをモニターしていくことが重要になります。

なお、1行が1人の顧客の1購買を表すような購買データがあるときに、新規と既存に分けて売上を見るには、初回購入かどうかのラベルをデータに追加することで、そのラベルをもとに、それぞれの売上を可視化できるようになります。

2. 顧客あたりの売上

上記でも述べたように、「既存顧客からの売上」を増やすためには「顧客あたりの売上」を増やすための活動を行う必要があります。

そこで、「顧客あたりの売上」をモニターし現在の施策、活動がうまくいっているのかどうかを、注意して見ていく必要があります。

なお、「顧客あたりの売上」は任意の期間（例：年、月など）における売上の合計を顧客数で割ることで、計算できます。

なお、顧客あたりの売上をセグメントに分けて比べることで、顧客あたりの売上の大きい、優良な顧客セグメントを探索し、ターゲットに据えるべきセグメントのヒントを得ることもできます。

3. 顧客獲得コスト

売上を増やすには「新規顧客からの売上」を増やすことが重要で、そのためにはより多くの新規顧客を獲得する必要があります。そのため、自社の商品やサービスをより多くの人たちに知ってもらうためのマーケティングやプロモーションの活動を行っていくことになります。

その際、どれだけのコストを掛けることができるのかを把握しておくことが重要で、そのための指標が「1人あたり顧客獲得コスト」です。

もちろんこの「顧客獲得コスト」が前出の「顧客あたりの売上」よりも低くなければ利益が出ません。そして、この2つの指標を正確に把握することで、どれだけ顧客獲得にコストをかけることができるのか、データを元に計画していくことができるようになります。

「顧客獲得コスト」は任意の期間（例：年、月など）の「新規顧客の獲得に費やした総コスト」を「新規顧客数」で割ることで計算できます。

多くの場合、「新規顧客の獲得に費やした総コスト」は別のシステムやデータベースから取得する必要があったりするため、「顧客獲得コスト」の計算の前にコストのデータと新規顧客数のデータを結合する必要があります。

顧客獲得コストをモニターすることで、新規顧客の獲得にかけているコストが現在のビジネスの状況に対して、適正かを確認できます。

また、顧客獲得コストをセグメントに分けて比べることで、獲得効率の良いまたは悪い、顧客セグメント・広告媒体・流入経路を理解して、マーケティングや営業活動の注力先を最適化できます。

4. 営業利益率

新規顧客を増やすための施策にも、そして既存顧客からの売上を伸ばすための施策にもマーケティング費や営業コストがかかるものですが、販促活動を活発に行なった結果、売上は伸びたが利益は減ったというのでは困ります。

そもそもある程度の利益を保つことができなければ、継続的な販促活動が行えなくなるため、長期的には売上も伸びないでしょう。そこで売上に対して利益がどれだけあるかという「利益率」を見ておく必要があります。

なお、利益にはいくつかのタイプのものがあるのですが、営業が注目すべき利益は「営業利益」と呼ばれるものです。これは、売上高から商品やサービスを「生み出すためにかかった費用」である売上原価（原材料費、労務費、設備費）を引いた売上総利益から、さらに商品やサービスを「売るためにかかった費用」である販管費を引いたものです。