🙆‍♀️

『CDPの作り方』を読んだ - 「なぜCDPを作るのか」から顧客統合処理のデータ処理まで

に公開
book
idea

『CDPの作り方』を読んだ。現在データエンジニアリングに取り組んでいるのだが、要件定義やデータ処理の勘所がわかっていないため、CDPの作り方に入門した。
技術の話だけではなく、ビジネスに対してどうやって価値を提供するか、どういう風にプロジェクト自体を進めていけば良いかが書いてある。
要件定義や運用については理想的に書かれているという印象はありますが、具体的なデータ設計書やシステム間連携図などの記載がありプロジェクトのイメージがつきやすいのでオススメしたい。

https://tatsu-zine.com/books/cdp-ga4-bigquery

以下、章ごとに学んだことを残しておきます。

はじめに

CDPを導入してどういったことをするのか、そもそもなぜCDPが注目されているのかが書いてあり一番良かった。

  • 少子高齢化が進み、顧客候補が減り続ける中で、既存顧客との関係維持はますます重要。
    • 「未来についてわかっていることは、人口動態のように、"今すでに起こっている未来"だけである」 - ピーター・F・ドラッガー
    • 企業は確実に起きるであろう未来を見据え、LTV(LifeTimeValue:顧客生涯価値)向上を掲げる
      • より良い体験の提供。顧客の興味関心に合わせたコンテンツの提供

1. データの活用領域と現在位置

GA4の導入、イベントデータを分析を簡単に紹介されている。GA4自体は利用していないのとすでにBigQueryは設定済みのため斜め読み。「Google Analytics 4の導入状況」はLookerのサンプルとして良かった。

  • 「Google Analytics 4 ガイド」

https://www.ga4.guide/

  • SEM Technology「Google Analytics 4の導入状況」

https://sem-technology.info/blog/posts/00194/

2. CDPを自前で作るための基礎知識

CDPプロジェクトの導入目的、ライフサイクル、成果物などの記載が有り良かった。
ただ興味があるのはデータエンジニアリングの箇所のため、MA施策なども紹介されているが流し読みしました。

  • 「なんのためにCDPを作るのか?」
    • CDPを通じて「顧客にどのような価値を提供したいのか」、そして「ビジネスをどのように成長させたいのか」という明確な目的をもつこと
  • CDP導入の目的
    • 顧客一人ひとりに最適化されたOne to Oneマーケティングを実現し、顧客エンゲージメントを高める
    • 複数のチャネルに分散している顧客データを統合し、顧客の全体像を把握することで、より効果的なマーケティング施策を立案する
    • データに基づいた意思決定を迅速に行い、変化の激しい市場環境に柔軟に対応する

3. CDPの技術要件と基礎知識 / 4. データ基盤のインフラ / 5. CDP構築の要件

CDPのコンポーネントとOSSや商用製品を含めたツール紹介、および選定ポイントの記載があります。
知らないツールも多々有るのと直近の流行り廃りはわからないので、2025年8月時点として参考になります。

コンポーネント 役割 技術選定のポイント
DWH データの蓄積・分析・統合顧客マスタの格納 BigQueryなどのクラウドPaaSが主流。SQLでの解析能力、データ保護機能が重要。
ETLツール データの抽出・変換・ロード（入出力） OSSのAirbyteや、Pythonのdltなど、WebUI型かコーディング型かを検討。
ワークフローエンジン ETL処理やデータ処理の実行管理・エラー管理 Apache Airflow、Dagsterなど。プログラム実行有無、クエリ管理の有無（SQLワークフローの管理のみ場合dbtなど）で選定。
データ転送 データ連携の仕組み CDC（Change Data Capture）やストリーミング（Webhook/Pub/Sub）など、データの鮮度・システム負荷を考慮して選択。
データカタログ データガバナンス GCPの場合はDataplexなど

6. データ処理

CDPを作るに当たって具体的に発生するデータ処理についての記載があります。システムごとに差分はあるものの、顧客データ統合処理のクエリのIDマップを作るという考えは知らなかったので、非常に参考になりました。

差分レコード処理

CDP内の直近データと取り込んだ差分データに基づいて最新のレコードを生成。直近データと差分エータをunion allで連結し、customer_idごとにupdated_atを新しい順に並べて最初のレコードを取得。

duckdbで検証した。

CREATE TABLE m_customers (
    customer_id VARCHAR,
    name VARCHAR,
    age INTEGER,
    gender VARCHAR,
    email VARCHAR,
    phone VARCHAR,
    address VARCHAR,
    updated_at DATE
);

INSERT INTO m_customers VALUES
('001', '山田太郎', 35, '男性', 'taro.yamada@example.com', '090-1234-5678', '東京都新宿区1-1-1', '2024-12-12'),
('002', '佐藤花子', 28, '女性', 'hanako.sato@example.com', '080-8765-4321', '大阪府大阪市2-2-2', '2024-12-11'),
('003', '鈴木次郎', 41, '男性', 'jiro.suzuki@example.com', '070-1111-2222', '神奈川県横浜市3-3-3', '2024-12-10'),
('004', '田中美香', 30, '女性', 'mika.tanaka@example.com', '090-3333-4444', '愛知県名古屋市4-4-4', '2024-12-09'),
('005', '高橋一郎', 50, '男性', 'ichiro.takahashi@example.com', '080-5555-6666', '福岡県福岡市5-5-5', '2024-12-08');


CREATE TABLE diff_customers_20241215 (
    customer_id VARCHAR,
    name VARCHAR,
    age INTEGER,
    gender VARCHAR,
    email VARCHAR,
    phone VARCHAR,
    address VARCHAR,
    updated_at DATE
);
INSERT INTO diff_customers_20241215 VALUES
('003', '鈴木次郎', 41, '男性', 'jiro.suzuki_new@example.com', '070-1111-2222', '神奈川県横浜市3-3-3', '2024-12-15'),
('004', '田中美香', 30, '女性', 'mika.tanaka@example.com', '090-3333-4444', '東京都渋谷区6-6-6', '2024-12-15');

with t1 as (
  select * from m_customers
  union all
  select * from diff_customers_20241215
)
select * from t1
qualify row_number() over (partition by customer_id order by updated_at desc) = 1;

┌─────────────┬──────────┬───────┬─────────┬──────────────────────────────┬───────────────┬─────────────────────┬────────────┐
│ customer_id │   name   │  age  │ gender  │            email             │     phone     │       address       │ updated_at │
│   varchar   │ varchar  │ int32 │ varchar │           varchar            │    varchar    │       varchar       │    date    │
├─────────────┼──────────┼───────┼─────────┼──────────────────────────────┼───────────────┼─────────────────────┼────────────┤
│ 005         │ 高橋一郎 │    50 │ 男性    │ ichiro.takahashi@example.com │ 080-5555-6666 │ 福岡県福岡市5-5-5   │ 2024-12-08 │
│ 002         │ 佐藤花子 │    28 │ 女性    │ hanako.sato@example.com      │ 080-8765-4321 │ 大阪府大阪市2-2-2   │ 2024-12-11 │
│ 004         │ 田中美香 │    30 │ 女性    │ mika.tanaka@example.com      │ 090-3333-4444 │ 東京都渋谷区6-6-6   │ 2024-12-15 │
│ 003         │ 鈴木次郎 │    41 │ 男性    │ jiro.suzuki_new@example.com  │ 070-1111-2222 │ 神奈川県横浜市3-3-3 │ 2024-12-15 │
│ 001         │ 山田太郎 │    35 │ 男性    │ taro.yamada@example.com      │ 090-1234-5678 │ 東京都新宿区1-1-1   │ 2024-12-12 │
└─────────────┴──────────┴───────┴─────────┴──────────────────────────────┴───────────────┴─────────────────────┴────────────┘

統合顧客マスタの作成

  • 何を持って同一顧客とみなすのか
    • システム上は①が最も望ましい、堅牢な形で、②、③にしたがって脆くなる。
      • ① システム間で統合可能なIDを保持している
        • どちらのテーブルにも顧客の識別子として同じ体系の「顧客コード」という変数が入っている
      • ② 顧客のメールアドレスや電話番号などの個人を特定できる(と見なせる)変数が含まれる
        • iCloud+でのワンタイムEメールアドレスが使えるようになったことから、複数のサービス間で同じメールアドレスを登録することが減少している
        • 携帯電話番号であれば無限に発行できないため、比較的個人を特定しやすい識別子として扱われる
      • ③ ①と②のどちらでもなく、複数の変数を総合的に見て類似度の極めて高いレコードを同一顧客とみなす
  1. 複数のシステムの顧客マスタ間のIDの対応関係をまとめる
  2. IDマップ
  3. 同一人物を表すレコード同じ情報を持つカラムの集約
  4. 統合顧客ID(プライマリキー)の採番
  5. 統合顧客マスタ
  • IDマップの生成
create or replace table system_a_master as
  select * from (
    values
      ('a001', 'user1@example.com', 'm1001', '山田 太郎'),
      ('a002', 'user2@example.com', 'm1002', '佐藤 花子'),
      ('a003', 'user3@example.com', 'm1003', '鈴木 健太'),
      ('a004', 'user4@example.com', 'm1004', '高橋 由美')
  ) as t (id, email, member_id, name);

create or replace table system_b_master as
select * from (
  values
    ('b001', 'b_user1@example.com', ''),
    ('b002', 'user2@example.com', ''),
    ('b003', 'user2@example.com', 'ad003'),
    ('b004', 'b_user2@example.com', 'ad004'),
    ('b005', 'user3@example.com', 'ad005')
) as t (id, email, ad_id);

create or replace table system_c_master as
select * from (
  values
    ('c001', 'm1003'),
    ('c005', 'm1004')
) as t (id, member_id);

create or replace table system_d_master as
select * from (
  values
    ('d001', 'ad003'),
    ('d003', 'ad003'),
    ('d005', 'ad004'),
    ('d006', 'ad004'),
    ('d007', 'ad005')
) as t (id, ad_id);

with joined_data as (
  select distinct
    a.id a_id,
    b.id b_id
  from system_a_master a
  left join system_b_master b on a.email = b.email
)
select 
  a_id,
  b_id
from joined_data
order by a_id
;

┌─────────┬─────────┐
│  a_id   │  b_id   │
│ varchar │ varchar │
├─────────┼─────────┤
│ a001    │ NULL    │
│ a002    │ b003    │
│ a002    │ b002    │
│ a003    │ b005    │
│ a004    │ NULL    │
└─────────┴─────────┘

with joined_data as (
  select distinct
    a.id a_id,
    c.id c_id
  from system_a_master a
  left join system_c_master c on a.member_id = c.member_id
)
select 
  a_id,
  c_id
from joined_data
order by a_id
;

─────────┬─────────┐
│  a_id   │  c_id   │
│ varchar │ varchar │
├─────────┼─────────┤
│ a001    │ NULL    │
│ a002    │ NULL    │
│ a003    │ c001    │
│ a004    │ c005    │
└─────────┴─────────┘

with joined_data as (
  select distinct
    b.id b_id,
    d.id d_id
  from system_b_master b
  left join system_d_master d on b.ad_id = d.ad_id
)
select 
  b_id,
  d_id
from joined_data
order by b_id, d_id
;

┌─────────┬─────────┐
│  b_id   │  d_id   │
│ varchar │ varchar │
├─────────┼─────────┤
│ b001    │ NULL    │
│ b002    │ NULL    │
│ b003    │ d001    │
│ b003    │ d003    │
│ b004    │ d005    │
│ b004    │ d006    │
│ b005    │ d007    │
└─────────┴─────────┘
  • Aを主として他のシステムのIDを紐づける
create or replace temp table id_map_a as
SELECT
  a.id,
  (
    SELECT
      ARRAY_AGG(b.id)
    FROM
      system_b_master b
    WHERE
      a.email = b.email
  ) AS b_id,
  (
    SELECT
      ARRAY_AGG(c.id)
    FROM
      system_c_master c
    WHERE
      a.member_id = c.member_id
  ) AS c_id
FROM
  system_a_master a
ORDER BY
  a.id;
select * from id_map_a;
┌─────────┬──────────────┬──────────────┐
│   id    │     b_id     │     c_id     │
│ varchar │  varchar[]   │  varchar[]   │
├─────────┼──────────────┼──────────────┤
│ A001    │ [NULL]       │ [NULL]       │
│ A002    │ [B003, B002] │ [NULL, NULL] │
│ A003    │ [B005]       │ [C001]       │
│ A004    │ [NULL]       │ [C005]       │
└─────────┴──────────────┴──────────────┘
  • inner joinを使って顧客マスタにそのまま列を追加
select * from system_a_master innner join id_map_a using(id)  ;
┌─────────┬───────────────────┬───────────┬───────────┬──────────────┬───────────┐
│   id    │       email       │ member_id │   name    │     b_id     │   c_id    │
│ varchar │      varchar      │  varchar  │  varchar  │  varchar[]   │ varchar[] │
├─────────┼───────────────────┼───────────┼───────────┼──────────────┼───────────┤
│ a001    │ user1@example.com │ m1001     │ 山田 太郎 │ NULL         │ NULL      │
│ a002    │ user2@example.com │ m1002     │ 佐藤 花子 │ [b002, b003] │ NULL      │
│ a003    │ user3@example.com │ m1003     │ 鈴木 健太 │ [b005]       │ [c001]    │
│ a004    │ user4@example.com │ m1004     │ 高橋 由美 │ NULL         │ [c005]    │
└─────────┴───────────────────┴───────────┴───────────┴──────────────┴───────────┘

  • ディメンションと指標

    • 売上高、利益額、訪問者数などの数値そのものに着目。ざっくりと単一の数値を見る
      • この数値が指標(metic)
    • 「商品ごとの売上額」「営業ブロック別の利益額」といったように、数値を見る切り口が加わる
      • この切り口がディメンション(dimension)

  • ディメンションナルモデリング

    • スタースキーマ：データをファクトテーブルとディメンションテーブルに分け、データ容量は小さくなるが、分析にはJOINが必要となり計算負荷が増大する。
    • ワイドテーブル：あらゆる情報を1つのテーブルに集約し、人間が認識可能な形式で保持する。列数は多くなるが、JOINが不要で、ビジネスユーザーが直感的に利用しやすい。
      • CDPは、顧客のLTV評価やマーケティング施策の対象者を決めるためのデータという位置づけであるため、データの中身が一目で分かり、JOINの少ないシンプルなクエリで利用できる非正規化されたワイドテーブルが都合が良い

7. GA4のログ処理

GA4は利用想定がないので斜め読み。
GA4のイベントベースでユーザーのセグメント取得するためのSQLの例が豊富。

8. CDP構築後の活用方法

CDP構築後の運用/MAについて記載がある。ビジネスレイヤーのためここも斜め読み。

おわりに

CDPプロジェクトを進めるに当たっての作成すべき成果物やデータ処理の例が豊富です。特に顧客統合の処理の「名寄せ」データ処理は、アプリケーションでも応用できるので知っておきたい。
CDPとはなにか、なぜ作るのか、どうやって作るのかが例とともに書かれており分かりやすいのでCDPを始めて作ろうとする方にオススメします。

Discussion