👥 Step 52: 実践プロジェクト3 – 顧客管理データベース構築

顧客情報を一元管理して、マーケティングに活かそう！

📋 このステップで学ぶこと

顧客データベースの設計
顧客マスタの作成
購買履歴の記録と分析
RFM分析の実施
顧客セグメント分け
検索・抽出機能の実装
顧客レポートの自動生成

📝 1. プロジェクト概要

顧客管理データベースを構築し、顧客情報と購買履歴を一元管理します。

🔑 顧客管理の重要性

新規顧客獲得コスト vs 既存顧客維持コスト
新規：既存の5倍のコストがかかる！

パレートの法則（80:20の法則）
売上の80%は、上位20%の顧客から生まれる

顧客管理で実現すること：
✓ 優良顧客の特定と維持
✓ リピート率の向上
✓ LTV（顧客生涯価値）の最大化
✓ 効率的なマーケティング

📝 システム要件（※横スクロールできます）

【システム要件】

■ 目的
・顧客情報の一元管理
・購買履歴の記録と分析
・優良顧客の特定
・効果的なマーケティング施策

■ 機能
1. 顧客マスタ
   – 基本情報（氏名、連絡先）
   – 属性情報（年齢、性別、地域）
   – 登録日、最終購入日

2. 購買履歴
   – いつ、誰が、何を、いくら購入したか
   – 購入回数、購入金額の自動集計

3. RFM分析
   – Recency（最終購入日）
   – Frequency（購入頻度）
   – Monetary（購入金額）
   – スコアリングと顧客ランク付け

4. 顧客レポート
   – セグメント別顧客数
   – 購買動向分析
   – 優良顧客リスト

■ 活用シーン
・メルマガ配信リストの作成
・DMの送付先選定
・リピーター育成施策
・売上予測

🏗️ 2. システム設計

顧客管理システムは4つのシートで構成します。

👤

シート1：顧客マスタ

顧客の基本情報を管理
・顧客ID、氏名、連絡先
・年齢、性別、地域
・購入回数、累計金額（自動計算）
・顧客ランク（自動判定）

🛒

シート2：購買履歴

購入の詳細を記録
・取引ID、日付
・顧客ID、顧客名
・商品カテゴリ、購入金額
・備考

📊

シート3：RFM分析

顧客をスコアで評価
・Recency（最終購入日）
・Frequency（購入頻度）
・Monetary（購入金額）
・セグメント判定

📈

シート4：顧客レポート

分析結果をまとめる
・セグメント別顧客数
・優良顧客リスト
・休眠顧客リスト
・グラフによる可視化

📝 シート構成の詳細（※横スクロールできます）

【シート1：顧客マスタの列構成】

A: 顧客ID（自動採番）
B: 氏名
C: フリガナ
D: 性別
E: 生年月日
F: 年齢（自動計算）
G: 郵便番号
H: 住所
I: 電話番号
J: メールアドレス
K: 登録日
L: 最終購入日（自動取得）
M: 購入回数（自動計算）
N: 累計購入金額（自動計算）
O: 顧客ランク（自動判定）


【シート2：購買履歴の列構成】

A: 取引ID
B: 日付
C: 顧客ID
D: 顧客名（VLOOKUP）
E: 商品カテゴリ
F: 購入金額
G: 備考


【シート3：RFM分析の列構成】

A: 顧客ID
B: 氏名
C: 最終購入日（Recency）
D: 経過日数
E: Rスコア（1-5）
F: 購入回数（Frequency）
G: Fスコア（1-5）
H: 累計金額（Monetary）
I: Mスコア（1-5）
J: 総合スコア
K: セグメント

👤 3. 顧客マスタの作成

顧客情報を管理するマスタシートを作成します。

基本情報の設定

A列：顧客ID（自動採番）

="C"&TEXT(ROW()-1,"0000")

結果：C0001, C0002, C0003…（行番号から自動で連番作成）

F列：年齢（自動計算）

=IF(E2="","",DATEDIF(E2,TODAY(),"Y"))

生年月日から今日までの年数を計算。空白の場合は空白を返す。

📝 入力規則の設定（※横スクロールできます）

【入力規則の設定例】

■ D列：性別（ドロップダウン）
設定：データの入力規則 → リスト
リスト：男性,女性,その他,未回答

■ E列：生年月日（日付）
設定：データの入力規則 → 日付
条件：有効な日付

■ J列：メールアドレス
設定：データの入力規則 → ユーザー設定
数式：=ISNUMBER(FIND(“@”,J2))
メッセージ：@を含む形式で入力してください

購買情報の自動取得

L列：最終購入日（自動取得）

=MAXIFS(購買履歴!$B:$B,購買履歴!$C:$C,$A2)

購買履歴シートから、該当顧客の最新日付を取得。
Excel 2019以降 / Googleスプレッドシート対応

M列：購入回数（自動計算）

=COUNTIF(購買履歴!$C:$C,$A2)

購買履歴シートで、該当顧客IDの出現回数をカウント。

N列：累計購入金額（自動計算）

=SUMIF(購買履歴!$C:$C,$A2,購買履歴!$F:$F)

購買履歴シートから、該当顧客の購入金額を合計。

顧客ランクの自動判定

O列：顧客ランク

=IF(N2="","未購入",IF(N2>=1000000,"プラチナ",IF(N2>=500000,"ゴールド",IF(N2>=100000,"シルバー","ブロンズ"))))

ランク基準：
・プラチナ：100万円以上
・ゴールド：50万円以上
・シルバー：10万円以上
・ブロンズ：10万円未満
・未購入：購入なし

💡 条件付き書式でランクを色分け

・プラチナ：ゴールドの背景
・ゴールド：黄色の背景
・シルバー：グレーの背景
・ブロンズ：薄い青の背景
・未購入：薄い赤の背景

🛒 4. 購買履歴の記録

購入データを記録するシートを作成します。

A列：取引ID（自動採番）

="T"&TEXT(ROW()-1,"00000")

結果：T00001, T00002…（5桁の連番）

D列：顧客名（VLOOKUP）

=IF(C2="","",VLOOKUP(C2,顧客マスタ!$A:$B,2,FALSE))

顧客IDから顧客名を自動取得。入力の手間を省く。

📝 入力規則の設定（※横スクロールできます）

【購買履歴の入力規則】

■ B列：日付（日付型）
設定：データの入力規則 → 日付

■ C列：顧客ID（ドロップダウン）
設定：データの入力規則 → リスト
元の値：顧客マスタ!$A$2:$A$1000

■ E列：商品カテゴリ（ドロップダウン）
設定：データの入力規則 → リスト
リスト：食品,飲料,日用品,衣料品,家電,その他

■ F列：購入金額（整数）
設定：データの入力規則 → 整数
最小値：1

🛒 購買履歴のサンプルデータ

取引ID	日付	顧客ID	顧客名	カテゴリ	金額
T00001	2025-01-15	C0001	田中太郎	食品	¥5,000
T00002	2025-01-15	C0002	佐藤花子	飲料	¥3,000
T00003	2025-01-16	C0001	田中太郎	日用品	¥2,000
T00004	2025-01-17	C0003	鈴木一郎	食品	¥8,000
T00005	2025-01-20	C0001	田中太郎	食品	¥4,000

📊 5. RFM分析の実施

RFM分析で顧客を評価し、セグメント分けします。

Recency

最終購入日
最近買ってくれた人ほど良い顧客

Frequency

購入頻度
何回も買ってくれる人ほど良い顧客

Monetary

購入金額
たくさん買ってくれる人ほど良い顧客

Rスコアの計算

D列：経過日数

=IF(C2="","",TODAY()-C2)

最終購入日から今日までの日数を計算。

E列：Rスコア（1-5点）

=IF(D2="","",IF(D2<=30,5,IF(D2<=60,4,IF(D2<=90,3,IF(D2<=180,2,1)))))

スコア基準：
・5点：30日以内（最近購入）
・4点：31〜60日
・3点：61〜90日
・2点：91〜180日
・1点：181日以上（休眠客）

Fスコアの計算

G列：Fスコア（1-5点）

=IF(F2="","",IF(F2>=10,5,IF(F2>=5,4,IF(F2>=3,3,IF(F2>=2,2,1)))))

スコア基準：
・5点：10回以上
・4点：5〜9回
・3点：3〜4回
・2点：2回
・1点：1回

Mスコアの計算

I列：Mスコア（1-5点）

=IF(H2="","",IF(H2>=1000000,5,IF(H2>=500000,4,IF(H2>=100000,3,IF(H2>=50000,2,1)))))

スコア基準：
・5点：100万円以上
・4点：50〜100万円
・3点：10〜50万円
・2点：5〜10万円
・1点：5万円未満

総合スコアとセグメント

J列：総合スコア

=E2+G2+I2

範囲：3点（最低）〜 15点（最高）

K列：セグメント

=IF(J2="","",IF(J2>=13,"VIP",IF(J2>=10,"優良顧客",IF(J2>=7,"一般顧客",IF(J2>=5,"新規顧客","休眠顧客")))))

セグメント定義：
・VIP：13〜15点（最優良）
・優良顧客：10〜12点
・一般顧客：7〜9点
・新規顧客：5〜6点
・休眠顧客：3〜4点

📊 RFM分析の例

顧客名	経過	R	回数	F	金額	M	合計	セグメント
田中太郎	15日	5	12回	5	¥1,200,000	5	15	VIP
佐藤花子	45日	4	6回	4	¥350,000	3	11	優良顧客
高橋花子	75日	3	4回	3	¥80,000	2	8	一般顧客
鈴木一郎	200日	1	2回	2	¥30,000	1	4	休眠顧客

📈 6. 顧客レポートの作成

分析結果をまとめたレポートを作成します。

セグメント別顧客数

📝 Googleスプレッドシートの場合（※横スクロールできます）

【QUERY関数で集計】★Googleスプレッドシート専用

=QUERY(RFM分析!A:K, “SELECT K, COUNT(A) GROUP BY K ORDER BY COUNT(A) DESC LABEL K ‘セグメント’, COUNT(A) ‘顧客数'”)

結果：
セグメント    顧客数
優良顧客      45
一般顧客      120
VIP          15
新規顧客      80
休眠顧客      40

📝 Excelの場合（※横スクロールできます）

【COUNTIFで集計】

各セグメントの顧客数をカウント：

VIP：
=COUNTIF(RFM分析!$K:$K,”VIP”)

優良顧客：
=COUNTIF(RFM分析!$K:$K,”優良顧客”)

一般顧客：
=COUNTIF(RFM分析!$K:$K,”一般顧客”)

新規顧客：
=COUNTIF(RFM分析!$K:$K,”新規顧客”)

休眠顧客：
=COUNTIF(RFM分析!$K:$K,”休眠顧客”)

または、ピボットテーブルを使用：
1. RFM分析シートを選択
2. [挿入] → [ピボットテーブル]
3. 行：セグメント、値：顧客IDのカウント

優良顧客リスト

📝 優良顧客リスト（トップ20）（※横スクロールできます）

【Googleスプレッドシート】★専用

=QUERY(RFM分析!A:K, “SELECT A, B, J, K WHERE K IN (‘VIP’,’優良顧客’) ORDER BY J DESC LIMIT 20″)

結果：
顧客ID  氏名      総合スコア  セグメント
C0001  田中太郎    15        VIP
C0023  山田花子    14        VIP
C0045  佐藤次郎    13        VIP
…


【Excel】

1. RFM分析シートをコピー
2. セグメント列でフィルター → VIP、優良顧客を選択
3. 総合スコアで降順に並べ替え
4. 上位20件を表示

または、FILTER関数（Excel 365）：
=FILTER(RFM分析!A:K,(RFM分析!K:K=”VIP”)+(RFM分析!K:K=”優良顧客”))

休眠顧客リスト

📝 休眠顧客リスト（90日以上未購入）（※横スクロールできます）

【Googleスプレッドシート】★専用

=QUERY(RFM分析!A:K, “SELECT A, B, C, D WHERE D > 90 ORDER BY D DESC”)

結果：
顧客ID  氏名      最終購入日    経過日数
C0099  高橋一郎   2024-10-01   200日
C0088  伊藤美咲   2024-10-15   185日
…


【Excel】

=FILTER(RFM分析!A:K,RFM分析!D:D>90)

または、オートフィルターで経過日数 > 90 を抽出

グラフの作成

📊 作成するグラフ

1. セグメント別顧客数（円グラフ）
・セグメント別集計データを使用
・タイトル：「顧客セグメント構成」

2. 月別新規顧客数（折れ線グラフ）
・月別の登録者数を集計
・タイトル：「新規顧客獲得推移」

3. 購買金額分布（縦棒グラフ）
・累計購入金額の分布を集計
・タイトル：「顧客別購買金額分布」

🔍 7. 検索・抽出機能

条件に合った顧客を抽出する機能を実装します。

📝 条件別顧客抽出（※横スクロールできます）

【メール配信リストの作成】

条件：VIPと優良顧客、メールアドレス登録済み

Googleスプレッドシート（★専用）：
=QUERY(顧客マスタ!A:O, “SELECT A, B, J WHERE O IN (‘VIP’,’プラチナ’,’ゴールド’) AND J != ””)

Excel：
=FILTER(顧客マスタ!A:O,(顧客マスタ!O:O=”プラチナ”)+(顧客マスタ!O:O=”ゴールド”)*(顧客マスタ!J:J<>“”))

【地域別顧客リスト】

条件：東京都在住の顧客

Googleスプレッドシート（★専用）：
=QUERY(顧客マスタ!A:O, “SELECT A, B, H WHERE H LIKE ‘%東京都%'”)

Excel：
オートフィルターで住所列に「東京都」を含む条件を設定

【年齢層別リスト】

条件：30代の顧客

Googleスプレッドシート（★専用）：
=QUERY(顧客マスタ!A:O, “SELECT A, B, F WHERE F >= 30 AND F < 40")

Excel：
=FILTER(顧客マスタ!A:O,(顧客マスタ!F:F>=30)*(顧客マスタ!F:F<40))

📝 検索ボックスの作成（応用）（※横スクロールできます）

【検索機能の実装】

■ A1セル：検索キーワード入力欄
（ここに検索したい文字を入力）

■ A3セル以降：検索結果を表示

Googleスプレッドシート（★専用）：
=QUERY(顧客マスタ!A:O, “SELECT A, B, J WHERE B LIKE ‘%”&$A$1&”%’ OR J LIKE ‘%”&$A$1&”%'”)

説明：
・A1に入力されたキーワードで
・氏名またはメールアドレスを検索
・部分一致で抽出

使い方：
A1に「田中」と入力
→ 田中太郎、田中花子などが表示


【Excelの場合】

検索にはオートフィルターを使用するか、
FILTER関数とSEARCH関数を組み合わせる：

=FILTER(顧客マスタ!A:O,ISNUMBER(SEARCH($A$1,顧客マスタ!B:B)))

⚠️ QUERY関数はGoogleスプレッドシート専用

QUERY関数はExcelでは使用できません。

Excelでの代替方法：
・FILTER関数（Excel 365 / Excel 2021）
・オートフィルター機能
・ピボットテーブル
・高度なフィルター機能

📝 実践課題

実践課題

総合

顧客管理データベースを実際に構築してください

📝 要件（※横スクロールできます）

要件：

1. 4つのシートを作成
   – 顧客マスタ
   – 購買履歴
   – RFM分析
   – 顧客レポート

2. 顧客マスタに20名以上の顧客を登録

3. 購買履歴を50件以上入力

4. RFM分析でスコアリングとセグメント分け

5. 顧客レポートで各種分析結果を表示

6. グラフで可視化

構築手順：

【ステップ1】顧客マスタ作成（40分）
・ヘッダー設定
・顧客ID自動採番
・20〜30名のサンプルデータ入力
・年齢の自動計算
・入力規則の設定

【ステップ2】購買履歴作成（30分）
・ヘッダー設定
・取引ID自動採番
・ドロップダウン設定
・VLOOKUPで顧客名取得
・50〜100件のサンプルデータ入力

【ステップ3】顧客マスタの自動計算（30分）
・最終購入日の自動取得
・購入回数の自動計算
・累計購入金額の自動計算
・顧客ランクの自動判定
・条件付き書式の設定

【ステップ4】RFM分析シート作成（50分）
・顧客リストの作成
・Rスコアの計算
・Fスコアの計算
・Mスコアの計算
・総合スコアの計算
・セグメントの判定

【ステップ5】顧客レポート作成（50分）
・セグメント別集計
・優良顧客リスト
・新規顧客リスト
・休眠顧客リスト
・各種グラフの作成

【ステップ6】テストと調整（20分）
・新しい購買データを追加
・自動更新を確認
・見た目の調整

❓ よくある質問

Q1: 個人情報の取り扱いが心配です

個人情報保護のポイント：

1. 必要最小限の情報のみ：不要な情報は収集しない
2. アクセス制限：パスワード保護、権限管理
3. 暗号化：ファイル自体を暗号化
4. 定期的な削除：退会顧客の情報は削除
5. 同意の取得：利用目的を明示して同意を得る

Googleスプレッドシートなら権限管理が簡単です。

Q2: RFM分析のスコア基準をどう決めればいいですか？

スコア基準の決め方：

1. 自社の実態に合わせる：
まず全顧客のデータを集計し、平均値、中央値を確認

2. 業種による違い：
・飲食店：Rは短め（30日以内）
・不動産：Rは長め（1年以内）
・EC：Fは回数重視

3. 定期的に見直す：
半年〜1年ごとに基準を再検討

まずは一般的な基準で始めて、徐々に調整しましょう。

Q3: ExcelでQUERY関数の代わりになるものは？

Excelでの代替方法：

1. FILTER関数（Excel 365 / Excel 2021）
=FILTER(範囲, 条件)
条件に合うデータを抽出

2. ピボットテーブル
集計・グループ化・フィルタリングが可能
GUIで簡単に操作できる

3. オートフィルター
ヘッダー行でフィルター機能を使用
複数条件での絞り込みが可能

4. 高度なフィルター
[データ] → [詳細設定]
複雑な条件での抽出が可能

Q4: 売上管理システムと連携したい

連携方法：

同じファイル内：
・売上データシートがそのまま購買履歴に
・顧客情報も一元管理

別ファイル（Googleスプレッドシート）：
・IMPORTRANGEで売上データを取得
=IMPORTRANGE(“売上管理URL”, “売上データ!A:G”)

メリット：
✓ 二重入力不要
✓ リアルタイム分析
✓ データの一貫性

📝 Step 52 のまとめ

✅ このステップで学んだこと

👤 顧客DB設計

情報の一元管理と自動計算

🛒 購買履歴分析

購入パターンの把握と記録

📊 RFM分析

顧客の評価とセグメント分け

🔢 自動集計

購入回数・金額の自動計算

📈 レポート作成

セグメント別分析とグラフ化

🔍 検索機能

条件別の顧客抽出

🎯 実務での活用ポイント

1. 優良顧客の維持
VIP・優良顧客への優先的なサービス提供

2. 休眠顧客の掘り起こし
90日以上未購入の顧客へのリマインド

3. 新規顧客のフォロー
初回購入後のフォローアップで優良顧客へ育成

4. マーケティング効率化
セグメント別にターゲットを絞った施策

🎯 次のステップへ

顧客管理データベースを構築できました！次のStep 53では、実践プロジェクト4 – 予算管理・実績管理システムに挑戦します。予算と実績を比較・分析するシステムを作ります！

📝

学習メモ

Excel・Googleスプレッドシート完全マスター - Step 52

📋 過去のメモ一覧 ▼