顧客管理データCRM＋RFM+SFA

libe-irevo@master 2024年1月2日

ここではCRM（顧客管理）、RFM分析（顧客購買分析）、SFA（営業支援）について説明します。

エクセルを使った顧客管理データベースの作り方

まずはエクセルを使った顧客管理データベースの作成方法や手順を、以下の4つに分けて解説します。

【前準備】管理項目の選定
【1】管理項目の設定
【2】顧客情報の入力
【3】テーブル機能でデータベース化

それぞれ詳しく見ていきましょう。

【前準備】管理項目の選定

顧客管理データベース作成の前準備として、管理項目を選定しましょう。盛り込む情報の例は、下表の通りです。

基本情報	・企業名・所在地・電話番号・メールアドレス・代表者名　など
担当者情報	・担当者名・担当部署・決裁権の有無　など
購買関連の情報	・購入製品名/サービス名・購入時期・購入金額　など
営業関連の情報	・購入のきっかけ・商談内容・受注確度・最終訪問日・次回の訪問予定日　など
その他の情報	・顧客管理番号・関係性・セミナー/展示会への参加歴　　など

上記のような項目から、目的に合わせて管理する内容を選定します。例えば売上管理の場合は、主に次の5つを抽出すると良いでしょう。

企業名
担当者名
購入製品
購入金額
購入のきっかけ　など

また案件管理の場合は、主に次の5つがおすすめです。

企業名
担当者名
商談内容
次回の訪問予定日
受注確度　など

はじめは項目を少なくし、必要に応じて増やしていくと管理しやすいでしょう。

【1】管理項目の設定

管理項目を選定できたら、実際にエクセルで設定します。具体的な手順は、以下の通りです。

データのタイトルをA1セルに入力
A1セルから1行空け、3行目に管理項目を横並びに入力

タイトルと管理項目の間に1行空ける理由は、のちの操作でデータベースとして認識させるためです。また管理項目を横並びにすることで、フィルター機能などエクセルの便利な機能を使えるようになります。

より使いやすい顧客管理シートを完成させるためにも、上記の操作は必ず行いましょう。

【2】顧客情報の入力

次に各管理項目に合わせて、情報を縦並びに入力します。顧客番号はのちのちも管理しやすいよう、次のように重複しない数字を割り振ると良いでしょう。

A社→001
B社→002
C社→003　など

ここでは顧客情報の入力時に活用したい機能として、書式設定と入力規則の設定について解説します。

書式設定

データの表示方法を統一したいときは、書式設定を利用しましょう。具体的な操作方法は、次の通りです。

列を選択し右クリック
書式設定を選択
「表示形式」最下部のユーザー定義を選択
近いコードを選択し、必要に応じて書き換え

例えば日付の書式設定は、デフォルトの「yyyy/m/d」を種類バーで「yyyy/mm/dd」へ書き換えると日付の桁がそろい見やすくなります。

また顧客番号は「000」あるいは「000000」、金額は「#,###」（3桁区切り）といったコードを利用すると便利です。保有する顧客数や自社製品・サービスの価格に合わせて、分かりやすい表示方法を設定していきましょう。

入力規則の設定

受注確度や購入製品など入力内容が固定している場所は、入力規則を設定するのがおすすめです。ドロップダウンリストを利用できるため、入力ミスや表記揺れが減り、より精度の高い顧客管理データベースを作成できます。具体的な操作方法は、次の2パターンです。

【方法1】

別シートにリストのデータを作成
本シートの入力セルを範囲選択
上部バーにある「データの入力規則」をクリック
リスト形式を選択
「元の値」にリストデータの範囲を指定

【方法2】

本シートの入力セルを範囲選択
上部バーにある「データの入力規則」をクリック
リスト形式を選択
元の値にリストデータを直接打ち込む

ドロップダウンリストは入力の手間を大幅に削減できるため、業務効率化という意味でも積極的に取り入れたいところです。

【3】テーブル機能でデータベース化

最後にテーブル機能を使って、エクセルのシートをデータベース化します。具体的な操作方法は、次の通りです。

管理項目と顧客情報を範囲選択
上部バーの挿入タブ内にある「テーブル」を選択
「先頭行をテーブルの見出しとして使用する」をチェックした上でOKをクリック

設定が完了すると管理項目にフィルターが設定され、背景色やフォントも変わります。

デザインを変更したい場合はいずれかのセルを選択し、上部バーの「テーブルデザイン」から選択しましょう。

なおテーブル機能でデータベース化すると、行を削除してもデザインのパターンが崩れません。

顧客情報の追加や修正も簡単にできるため、エクセルで顧客管理する場合はデータベース化の操作を忘れないようにしましょう。

顧客管理で使いたいエクセルの便利な機能5選

エクセルで作成した顧客管理データベースを、より使いやすくしたい場合は次に挙げる5つの機能がおすすめです。

ウィンドウ枠の固定機能
フィルター機能
スライサー機能
重複チェック機能
ピボットテーブル機能

それぞれ詳しく見ていきましょう。

ウィンドウ枠の固定機能

ウィンドウ枠の固定機能を活用すると、下方へスクロールしても管理項目を見られる状態にできます。具体的な操作は、次の通りです。

固定したい行の下、かつ列の右にあたるセルを選択（A3セルを固定したい場合はB4セル）
上部バーから「表示」をクリック
「ウィンドウ枠の固定」を選択

特に顧客情報の増加にともない、スクロールする必要性が出てきた際に重宝する機能です。

フィルター機能

フィルター機能では、顧客情報の並び替えや抽出ができます。例えば購入製品という管理項目から、製品Aを購入した顧客を抽出し一覧化することも可能です。具体的な操作は、次のようになります。

管理項目のセルにある「▼」ボタンをクリック
絞り込みたいデータを選択

なおテキストフィルターでは自由記述によるデータの指定や、複数条件による絞り込みもできます。また特定のセルと同じ値のデータを抽出したい場合の操作は、次の通りです。

特定のセルを選択した上で右クリック
「フィルター」を選択
「選択したセルの値でフィルター」をクリック

特定の顧客データを検索したいときや、受注傾向などを分析したいときに活用してみてください。

スライサー機能

スライサー機能では、フィルター機能よりもさらに的を絞ったデータの抽出が可能です。具体的な操作は、次のようになります。

「テーブルデザイン」をクリック
「スライサーを挿入」を選択
絞り込みたい項目を選択し、OKボタンをクリック

クリック後は、選択した項目ごとに値のダイアログが表示されます。

複数の項目もワンクリックで抽出できるため、何度もフィルターをかけたいときはスライサー機能を使うとより効率的です。

重複チェック機能

重複チェック機能では、重複したデータを色付けしてくれます。具体的な操作方法は、次の通りです。

上部バーから「ホーム」を選択
「条件付き書式」をクリック
「セルの強調表示ルール」内にある「重複する値」を選択
値を「重複」、書式を「任意」に設定しOKボタンをクリック

なお重複したデータは、ctrl+Fのショートカットキーでも検索できます。入力内容を置換したい場合は、ホームにある「検索と選択」でまとめて修正しましょう。

ピボットテーブル機能

ピボットテーブル機能では、抽出したデータを表・グラフ化できます。具体的な操作方法は、次の通りです。

上部バーの「挿入」をクリック
「ピボットテーブル」を選択
ダイアログが出たら、OKボタンをクリック
「ピボットテーブルのフィールド」で分析したい項目を選択
行や値のフィールドへ項目をドラッグ＆ドロップ

また作成されたピボットテーブルは、グラフ化もできます。具体的な操作方法は、次の通りです。

ピボットテーブルを範囲選択し、右クリック
クイック分析を選択
ダイアログ上部にある「グラフ」をクリック

特に受注傾向などの分析を進める際に役立つため、顧客管理の担当者は操作に慣れておくと良いでしょう。

エクセルの顧客管理に使えるテンプレート3選【無料】

エクセルの顧客管理に使えるテンプレートサイトは、主に次の3つです。

Officeテンプレート
Excel Form 顧客管理
［文書］テンプレートの無料ダウンロード

それぞれ詳しく見ていきましょう。

Officeテンプレート

Officeテンプレートの顧客管理表は、所在地や電話番号など企業の属性情報をまとめられるテンプレートです。基本的な顧客リストの作成に役立つため、初めて顧客管理を行う担当者でも使いやすいでしょう。

また同サイト内ではテンプレートの使い方も公開されており、エクセルの実践的な知識がない方でも分かりやすくなっています。

Excel Form 顧客管理

Excel Form 顧客管理は、フォームに入力するだけで顧客情報や売上情報を一覧化できるテンプレートサイトです。VBAマクロが公開された有料の正規版もありますが、特にカスタマイズが必要ない場合は無料版でも十分活用できます。

はがきや封筒のラベルも作成できるため、郵送DMの送付時にも役立つでしょう。

［文書］テンプレートの無料ダウンロード

［文書］テンプレートの無料ダウンロードにある顧客管理表は、担当者やメールアドレスなど一般的な管理項目がデフォルトで設定されているテンプレートです。年賀状やお中元などの項目もありますが、必要に応じて簡単に変更・削除できます。

ダウンロード後はテーブル機能などを利用してデザインの変更や機能を追加するとより使いやすくなるでしょう。

エクセルによる顧客データの分析方法

エクセルではRFM分析やデシル分析といった、顧客データ分析も実行できます。最低限必要な項目は、次の4つです。

顧客番号
受注の識別項目（注文番号など）
購入日
購入金額

ここではエクセルによるRFM分析や、デシル分析のやり方を見ていきましょう。なお顧客データ分析について詳しく知りたい方は、こちらの記事もあわせてチェックしてみてください。

RFM分析

RFM分析とは購買行動をもとに、優良顧客や維持顧客などを見極める方法です。分析項目は、次の3つになります。

最新の購入日（Recency）
購入の頻度（Frequency）
購入総額（Monetary）

エクセルでRFM分析を進める手順は、以下の通りです。

1. ピボットテーブルでRFMを集計
2. 完成した表を新規シートへコピー＆ペースト
3. RFMを定義
　　a. 「最終注文日からの経過日数」という管理項目を追加
　　b. 基準日を決定
　　c. 基準日セルの値-最終注文日の値で経過日数を算出
　　d. データをもとに定義を決定
　　e. 各定義のセルに書式設定（0日以内/0回以上/0円以上など）
4. IF関数を使ってRFMランクを振り分け
　　a. Rのランク分け「=IF(○>=□,’’1’’,IF(○>=□,’’2’’,IF(○>=□,’’3’’)))」※1
　　b. F・Mも同様にランク分け
　　c. 行の最後まで計算式をコピー

※1○：経過日数のセル、□：日数、1～3：ランク

なお算出されたRFMは、再度ピボットテーブルを活用したクロス集計を実行することで、より深い分析ができます。例えばRとFのランクがどちらも3の場合は最優良顧客、両方とも1の場合は離反顧客と判断しやすくなるでしょう。

デシル分析

デシル分析とは購入金額をもとに、顧客をグループ化して分析する方法です。エクセルでの操作方法は、次のようになります。

1. ピボットテーブルで購入金額の総額を算出
2. 一番上のセルを右クリック
3. 「並べ替え」内の「降順」を選択
4. 完成した表を新規シートへコピー&ペースト
5. 順位の振り分け
6. IF関数を利用し、デシルランクを割り振り
　　a. 10等分した際の分割幅を決定（100人なら分割幅は10）
　　b. デシルランク項目の一番上にあるセルにIF関数を挿入※2
　　c. 行の最後まで計算式をコピー

※2：挿入するIF関数
=IF(○<=□,’’D1’’, IF(○<=□*2,’’D2’’, IF(○<=□*3,’’D3’’, IF(○<=□*4,’’D4’’, IF(○<=□*5,’’D5’’, IF(○<=□*6,’’D6’’, IF(○<=□*7,’’D7’’, IF(○<=□*8,’’D8’’, IF(○<=□*9,’’D9’’, IF(○<=□*10,’’D10’’))))))))))

○：デシルランクのセル、□：分割幅のセル、D1～D10：デシルランク