顧客統合履歴を管理するテーブルを作る
はじめに
RDB 上は異なるキーで登録されているが、実際は同じ顧客であることから集計の単位としたい場合があります。異なるキーで登録されている例としては、以下のような場合があげられます。
- 人為的ミスにより重複登録してしまった
- 単一ユーザーが複数アカウントを所持している
- 権限管掌上理由もしくはシステム設計上の都合により同一顧客に別 ID を振って個別に管理する必要がある
- セッション ID のように変更しうる値を顧客単位として扱う
このような場合に顧客追跡テーブルの設計として閉包テーブルを利用する方法を紹介します。なお、閉包テーブルが唯一の方法というわけではありません。
TL;DR
- 顧客の統合が行われるような系で顧客統合履歴を管理する方法として閉包テーブルがある
- 顧客の統合履歴追跡目的で閉包テーブルを構築する場合、挿入操作だけで良く、データウェアハウス（DWH）とも相性が良い
想定事例
以下の架空の事例を使います。
背景
スーパーマーケット X ではポイントカードを発行している。ポイントカードにはカードごとに ID が振られており購買履歴が管理されている。
さらに X はポイントアプリも運用している。ポイントアプリは以下の機能を有している。
- 新規発行: 仮想ポイントカードを発行することができる。
- カード・アプリ併用: 発行済みの仮想ポイントカードに対し、既存の物理ポイントカードを紐づけ、ポイントを共有できる。ただし、既に物理ポイントカードが紐づけられた仮想ポイントカードに対して別の物理ポイントカードを紐づけることはできず、紐づけられた物理ポイントカードは機種変更機能で移行した場合を除き別の仮想ポイントカードに紐づけることはできない。
- 機種変更: スマホ A にインストールされたポイントアプリ内の仮想ポイントカードに対し、スマホ B にインストールされたポイントアプリ内の仮想ポイントカードのポイントを全額移行できる。また、スマホ B のポイントカードに物理ポイントカードが紐づけられていた場合、その紐づけもスマホ A に移行する。移行元、移行先の双方に物理ポイントカードが紐づけられている場合は移行できない。移行後は、移行元のスマホ B のポイントカードは無効化され、再度新規発行して新しい仮想ポイントカードを発行するまで利用できない。
データモデル
テーブル一覧
| 論理テーブル名 | 物理テーブル名 | 概要 |
|---|---|---|
| 購買サマリー | purchase_summary | 購入ごとの集計 |
| ポイント | point | ポイント |
| ポイントカード | card | 物理ポイントカード |
| ポイントアプリ | app | 仮想ポイントカード |
| 併用履歴 | card_app_combine_history | 物理ポイントカードと仮想ポイントカードの紐づけ |
| アプリ移行履歴 | app_migrate_history | 機種変更による仮想ポイントカードの移行 |
ER 図
補足
機種変更時の移行先のポイント ID は、移行元のアプリに物理ポイントカードが紐づいている場合は移行元のポイント ID、いずれにも物理ポイントカードが紐づいていない場合は変更なし。
課題設定
分析課題
X は、購入額の多い顧客に対して定期的に優待券を配布したいと考えている。重複配布を可能な限り[1]避けるために、複数のポイントカードを利用している顧客については 1 顧客としてまとめて購入額を集計する必要がある。どのようにして購入額を集計すれば良いか。
データ例
以下の顧客を想定する。
- 顧客 1: 物理カード C1（ポイント ID P1）を持っており、これをアプリ A1（ポイント ID P2）に紐づけ、その後アプリ A2（ポイント ID P3）に移行した
- 顧客 2: 物理カード C2（ポイント ID P4）とアプリ A3（ポイント ID P5）を持っており、アプリを A4（ポイント ID P6）に移行した後物理カードと紐づけた
- 顧客 3: 物理カード C3（ポイント ID P7）とアプリ A5（ポイント ID P8）を持ち、紐づけは行っていない
- 顧客 4: アプリ A6（ポイント ID P9）とアプリ A7（ポイント ID P10）を持ち、アプリ A6 を A7 に移行した後、アプリ A6 の仮想カードを新規発行し（ポイント ID P11）、アプリ A6 をアプリ A8（ポイント ID P12）に機種変更した
point
| point_id | id_void |
|---|---|
| P1 | false |
| P2 | true |
| P3 | true |
| P4 | false |
| P5 | true |
| P6 | true |
| P7 | false |
| P8 | false |
| P9 | true |
| P10 | false |
| P11 | true |
| P12 | false |
card
| card_id | point_id |
|---|---|
| C1 | P1 |
| C2 | P4 |
| C3 | P7 |
app
| app_card_id | physical_card_id | point_id | is_void |
|---|---|---|---|
| A1 | C1 | P1 | true |
| A2 | C1 | P1 | false |
| A3 | P5 | true | |
| A4 | C2 | P6 | false |
| A5 | P8 | false | |
| A6 | P11 | true | |
| A7 | P10 | false | |
| A8 | P12 | false |
card_app_combine_history
| app_card_id | physical_card_id | point_id_old | point_id_new |
|---|---|---|---|
| A1 | C1 | P2 | P1 |
| A2 | C2 | P5 | P4 |
app_migrate_history
| app_card_id_from | app_card_id_to | point_id_to_old | point_id_to_new |
|---|---|---|---|
| A1 | A2 | P3 | P1 |
| A3 | A4 | P6 | P6 |
| A6 | A7 | P10 | P10 |
| A6 | A8 | P11 | P12 |
考え方
木構造とテーブル設計
以下のような有向木構造を考えます。
よく見ると顧客のポイントの統合履歴のように見えてきて、木構造を利用して顧客の統合履歴をまとめて取得できるような気がしてきます。
木構造は、テーブルデータ[2]の世界では、以下のような方法が利用できることが知られています。
- 隣接リスト構造 + 再帰クエリー
- 隣接リスト構造 + 外部アプリケーションによるループ処理
- 閉包テーブル
隣接リストは単純なデータ構造で、親子関係のみをレコードに持つものです。上の木構造は以下のように表現できます。
| 親（統合先） | 子（統合元） |
|---|---|
| A | |
| A | B |
| A | C |
| C | D |
| C | E |
| C | F |
直接の親子関係（A と B や C と F など）はすぐにわかりますが、それ以上離れている祖先・子孫関係（A と E など）は再帰クエリー等を利用する必要があります。件数が多くなって階層が深くなってくるとパフォーマンス上の問題が出てくる可能性があります。
閉包テーブルは木構造におけるすべての祖先・子孫関係を列挙する方法です。上の木構造は以下のように表現できます。
| 祖先（統合先） | 子孫（統合元） | 深さ |
|---|---|---|
| A | A | 0 |
| A | B | 1 |
| A | C | 1 |
| A | D | 2 |
| A | E | 2 |
| A | F | 2 |
| B | B | 0 |
| C | C | 0 |
| C | D | 1 |
| C | E | 1 |
| C | F | 1 |
| D | D | 0 |
| E | E | 0 |
| F | F | 0 |
閉包テーブルはすべての祖先・子孫関係が列挙されているため、直接の親子関係でない場合も容易に取得することができます。顧客の統合履歴を追うだけであれば、後述の通り顧客の新規登録および統合の時系列順に挿入していくだけで構築することができるため、既存データからの初期構築時以外は構築コストも高くありません。
再帰クエリーと閉包テーブルの違いは、階層構造に対する処理のタイミングを後でまとめてやるか、適宜小分けにデータに吐き出しておくかの違いです。どちらが良いかはケースによりますが、階層構造が深くならないことがあらかじめわかっている場合は、テーブルを増やさないためにも閉包テーブルを作らなくても良いかと思います。
想定事例の状況整理
想定事例のようなケースから、分析用の顧客統合履歴テーブルを作ることを考えます。
データ例において、顧客とポイントカード ID の関係はとシステムからわかるポイント ID の履歴以下の通りです。
| 顧客 | 有効な所有ポイント ID | 所有ポイント ID |
|---|---|---|
| 1 | P1 | P1, P2, P3 |
| 2 | P4 | P4, P5, P6 |
| 3 | P7, P8 | P7, P8 |
| 4 | P10, P12 | P9, P10, P11, P12 |
P7, P8 および P9, P11 は紐づけるための情報がポイントの履歴に存在しないため、同一顧客のものであるかは不明です[3]。それ以外のポイントカードについては各種機能の履歴を追うことで、同一顧客のポイントカードであることがわかります。
最終的には以下のような集計ができれば良いことになります。
| 集計単位 | 顧客 | 対象ポイントカード ID |
|---|---|---|
| 1 | 1 | P1, P2, P3 |
| 2 | 2 | P4, P5, P6 |
| 3-1 | 3 | P7 |
| 3-2 | 3 | P8 |
| 4-1 | 4 | P9, P10 |
| 4-2 | 4 | P11, P12 |
木構造の抽出
機種変更およびカード・アプリ併用機能は複数のポイント ID を統合する操作であるため、履歴を追うことによって閉包テーブルを作ることができます。
| 統合先 | 統合元 | 世代差 | 備考（顧客） | 備考（イベント） |
|---|---|---|---|---|
| P1 | P1 | 0 | 顧客 1 | 新規 |
| P2 | P2 | 0 | 顧客 1 | 新規 |
| P2 | P1 | 1 | 顧客 1 | カード・アプリ併用 |
| P3 | P3 | 0 | 顧客 1 | 新規 |
| P3 | P1 | 1 | 顧客 1 | 機種変更 |
| P4 | P4 | 0 | 顧客 2 | 新規 |
| P5 | P5 | 0 | 顧客 2 | 新規 |
| P6 | P6 | 0 | 顧客 2 | 新規 |
| P6 | P5 | 1 | 顧客 2 | 機種変更 |
| P6 | P4 | 2 | 顧客 2 | カード・アプリ併用 |
| P5 | P4 | 1 | 顧客 2 | カード・アプリ併用 |
| P7 | P7 | 0 | 顧客 3 | 新規 |
| P8 | P8 | 0 | 顧客 3 | 新規 |
| P9 | P9 | 0 | 顧客 4 | 新規 |
| P10 | P10 | 0 | 顧客 4 | 新規 |
| P10 | P9 | 0 | 顧客 4 | 機種変更 |
| P11 | P11 | 0 | 顧客 4 | 新規 |
| P12 | P12 | 0 | 顧客 4 | 新規 |
| P12 | P11 | 1 | 顧客 4 | 機種変更 |
これに対して全ポイント ID に紐づく最新のポイント ID は例えば以下のように取得できます。
SELECT
統合先,
統合元
FROM
統合履歴 h
WHERE
世代差 = (
SELECT
MAX(世代差)
FROM
統合履歴
WHERE
統合元 = h.統合元
)
| 統合先 | 統合元 | 備考 |
|---|---|---|
| P1 | P1 | 集計単位 1 |
| P1 | P2 | 集計単位 1 |
| P1 | P3 | 集計単位 1 |
| P6 | P4 | 集計単位 2 |
| P6 | P5 | 集計単位 2 |
| P6 | P6 | 集計単位 2 |
| P7 | P7 | 集計単位 3-1 |
| P8 | P8 | 集計単位 3-2 |
| P10 | P9 | 集計単位 4-1 |
| P10 | P10 | 集計単位 4-1 |
| P12 | P11 | 集計単位 4-2 |
| P12 | P12 | 集計単位 4-2 |
すべてのポイント ID に対して現行のポイント ID、すなわち集計単位が取得できていることがわかります。したがって統合元と購買履歴テーブルを JOIN し、統合先で GROUP BY すれば良いことになります。
統合履歴テーブルの作成
最後に顧客統合履歴を管理する閉包テーブルの作成について説明します。
運用上発生する操作は新規顧客の追加と顧客の統合の 2 つです。
テーブル
ここではデータ例に合わせてポイント ID = 顧客とみなして、ポイントの統合履歴テーブルを作成します。
追加
深さ 0 の行を追加するだけです。
時刻 T0 に P1, P2 が追加された場合は以下のようになります。
INSERT INTO point_merge_history
VALUES
('P1', 'P1', 0, T0),
('P2', 'P2', 0, T0);
point_merge_history
| point_id_to | point_id_from | depth | timestamp |
|---|---|---|---|
| P1 | P1 | 0 | T0 |
| P2 | P2 | 0 | T0 |
統合
統合元に紐づく統合履歴を抽出し、深さを +1 して統合先に紐づけます。
時刻 T1 に P1 を P2 に統合した場合は以下のようになります。
INSERT INTO point_merge_history
SELECT
'P2', -- 統合先
point_id_from,
depth + 1,
T1
FROM
point_merge_history
WHERE
point_id_to = 'P1' -- 統合元
point_merge_history
| point_id_to | point_id_from | depth | timestamp |
|---|---|---|---|
| P1 | P1 | 0 | T0 |
| P2 | P2 | 0 | T0 |
| P2 | P1 | 1 | T1 |
運用
日々の運用で顧客が統合されるたび（あるいは定期的に）追加、統合操作を行うことで完成します。
追加と統合の操作は順序依存があり、追加操作は任意のタイミングで挿入することができますが、統合には前後関係があり時系列に実行する必要があることに注意する必要があります。顧客の統合自体がそんなに頻繁に起こるものでもないので、単純にタイムスタンプ順に繰り返し行えば良いでしょう。ループをサポートしている RDB/DWH であれば SQL のみで完結します。ある程度まとめて処理をしたい場合は顧客統合履歴が有向非巡回グラフ（DAG）になることを利用してトポロジカルソートを行えば一定レベルで並列化ができます。トポロジカルソートも再帰クエリーを使えば SQL のみで実現できます[4]。
時系列順に構築できる性質のため、特定時点でのバックアップを持っていれば、それ以降の統合履歴テーブルを構築することも容易です。タイムスタンプが顧客統合履歴テーブルの最新時刻以降のデータを利用して追加・統合操作を行うだけで良いです。
閉包テーブルはデータ数がかなり多くなるため、場合によってはデータを削減したいことを考えるかもしれません。今回の課題設定のように、最終的な統合先だけがわかれば良い場合は、木構造の途中経路の部分は間引くことができます。ただし間引いた場合は、過去の特定時点での統合状態を把握することができなくなりますので、本当に間引かなければならない場合を除いては慎重になった方が良いでしょう。
-
紐づけが行われない限りは別の顧客として扱うのは致し方がないと割り切るが、紐づけが行われた実績がある場合は漏らさないように努力するということ。 ↩︎
-
テーブルデータとしたのは、一般的に顧客管理システム利用される RDB/DWH 扱うことができるためです。 ↩︎
-
実はアプリ移行履歴（app_migrate_history）を使えば P10, P11, P12 が同一顧客であることがわかるため顧客 4 についても集計単位を 1 つにできるのですが、これについては読者への課題とします。 ↩︎
-
再帰クエリーを避けるための閉包テーブルですが、運用時のデータ追加分に対するトポロジカルソートのための再帰クエリーは件数が多くないためパフォーマンス上の問題は起こりづらいです。初期構築時にやるとデータ量と階層の深さによっては現実的ではなくなります。 ↩︎
Discussion