顧客に関するデータが複数のシステムに分かれて存在しているケースがあります。

• CRMには「氏名」「会社名」「商談ステータス」
• ECサイトには「購入履歴」「会員ステータス」

それぞれのシステム単体では顧客情報を確認できても、システムをまたいで「同じ人」のデータを追いかけようとすると大変です。

この記事では、TROCCOを使って複数システムの顧客データをメールアドレスをキーに自動で紐付ける方法を解説します。

1. なぜメールアドレスで紐付けるのか

複数のシステムにまたがるデータを統合するには、すべてのシステムに共通して存在する「キー」が必要です。その代表格がメールアドレスです。
CRMやECサイトなどは、会員登録やフォーム入力の際にメールアドレスを取得していることがほとんどです。

ただし、いくつか注意しておきたい点もあります。

• 表記ゆれ：大文字・小文字の違いや、全角・半角の混在により、本来同じメールアドレスでも一致しない場合がある
• 1人が複数のメールアドレスを使い分けている場合：その人物を完全に一意に特定することはできない

これらに対しては完璧な解決策はない、ということを理解した上で、実際にTROCCOでどう紐付けていくかを見ていきます。

2. 全体の流れ

具体的な手順に入る前に、フローを確認しましょう。流れは大きく2段階です。

1. CRM・ECサイトなど、各システムのデータをTROCCOの転送設定でDWH（BigQuery、Snowflake、Redshift、Databricksなど）に集約する
2. TROCCOのデータマート定義で、メールアドレスをキーにテーブル同士をJOINする

各システムからのデータがまず一箇所（DWH）に集まり、そこで初めて「同じ人」のデータとして紐付けられます。では手順を具体的に見ていきます。

手順1：各システムのデータをDWHに集約する

まず、各システムに散らばっているデータを転送設定を使ってDWHに集めます。

ここで重要なのは、各システムのメールアドレスのカラムがどれかを確認しておくことです。後の手順でJOINキーとして使うため、カラム名や形式（例：大文字小文字、空白の有無）をあらかじめ把握しておきましょう。

設定のポイントとしては「複数システムのデータを、同じDWH内に集約する」ことです。これが完了すれば、次はいよいよデータマート定義でのJOIN作業に進みます。

手順2：データマート定義でJOINクエリを組む

各システムのデータがDWHに集まったら、いよいよTROCCOのデータマート定義を使ってメールアドレスをキーにJOINします。

データマート定義では、SQLクエリを入力するだけで、複数テーブルを組み合わせた新しいテーブル（データマート）を作成できます。基本となるクエリは次のような形です。

SELECT
  a.*,
  b.status
FROM table_crm a
LEFT JOIN table_ec b
  ON a.email = b.email_address

ここでのポイントを2つ解説します。

なぜLEFT JOINなのか

table_crm（CRM側のデータ）を主軸として、そこにtable_ec（ECサイトのデータ）の情報を付加する形にしています。LEFT JOINを使うことで、ECサイト経由ではない（ECサイト側にデータが存在しない）顧客もCRM側の情報はそのまま残り、データが欠落しません。

表記ゆれへの対応

手順1で触れた通り、メールアドレスは大文字・小文字や空白の有無で表記がゆれることがあります。これをJOIN条件にそのまま使うと、本来同じはずのメールアドレスが一致せず、正しく紐付けられません。そこで、JOIN条件の中であらかじめ正規化しておきます。

SELECT
  a.*,
  b.status
FROM table_crm a
LEFT JOIN table_ec b
  ON LOWER(TRIM(a.email)) = LOWER(TRIM(b.email_address))

TRIMで前後の空白を除去し、LOWERで小文字に統一することで、表記ゆれによる紐付け漏れを防げます。

3. できあがったデータの活用イメージ

ここまでの手順で、CRM・ECサイトなどに散らばっていた顧客データが、メールアドレスをキーに1つのテーブルへとまとまりました。

たとえば、このデータをBIツールに接続すれば、次のような分析がすぐに行えるようになります。

• 流入元のECサイトごとに、その後の購入率や顧客単価を比較する
• 広告経由で獲得した顧客が、その後どのステータス（商談中・成約・解約など）に至っているかを可視化する

4. つまずきやすいポイント

最後に、実際にメールアドレスでの紐付けを運用する中でよく出てくるつまずきポイントをまとめておきます。

メールアドレスがNULL・空の場合

会員登録時にメールアドレス入力が必須でないシステムでは、メールアドレスが空のレコードが存在することがあります。この場合、JOIN条件に一致せず、その顧客のデータは紐付けられません。集計結果の母数が想定より少ない場合は、まずこの可能性を疑ってみてください。

同じメールアドレスで複数レコードがある場合

退会後に再登録した、フォームを複数回送信した、といった理由で、同一システム内に同じメールアドレスのレコードが複数存在することがあります。この状態でJOINすると、行が想定以上に増えてしまう（レコードが重複する）ことがあるため、必要に応じてJOIN前に重複排除の処理を挟むことを検討してください。

表記ゆれが完全には防げない場合

LOWERやTRIMで多くの表記ゆれには対応できますが、たとえば「Gmailのドットを無視する仕様」のような、メールサービス固有の仕様まではSQLだけでは吸収しきれません。完璧な名寄せを目指すというより、「実用上十分な精度で紐付ける」という前提で運用するのが現実的です。

5. まとめ

CRM・ECサイトなど複数のシステムに散らばった顧客データを、TROCCOを使ってメールアドレスをキーに紐付ける方法を紹介しました。

ポイントを振り返ると次の通りです。

• 各システムのデータは、TROCCOの転送設定でDWHに集約する
• データマート定義を使いメールアドレスをキーにJOINする
  • JOINの際はLOWERやTRIMで表記ゆれを吸収しておく
  • メールアドレスでの紐付けは万能ではなく、NULLや重複、表記ゆれの限界があることも理解した上で運用する

一度この仕組みを作ってしまえば、システムをまたいだ顧客データの突き合わせを手作業で行う必要がなくなり、常に最新の統合データを分析や施策に活用できるようになります。