💘

顧客統合履歴を管理するテーブルを作る

2022/10/06に公開

はじめに

RDB 上は異なるキーで登録されているが、実際は同じ顧客であることから集計の単位としたい場合があります。異なるキーで登録されている例としては、以下のような場合があげられます。

  • 人為的ミスにより重複登録してしまった
  • 単一ユーザーが複数アカウントを所持している
  • 権限管掌上理由もしくはシステム設計上の都合により同一顧客に別 ID を振って個別に管理する必要がある
  • セッション ID のように変更しうる値を顧客単位として扱う

このような場合に顧客追跡テーブルの設計として閉包テーブルを利用する方法を紹介します。なお、閉包テーブルが唯一の方法というわけではありません。

TL;DR

  • 顧客の統合が行われるような系で顧客統合履歴を管理する方法として閉包テーブルがある
  • 顧客の統合履歴追跡目的で閉包テーブルを構築する場合、挿入操作だけで良く、データウェアハウス(DWH)とも相性が良い

想定事例

以下の架空の事例を使います。

背景

スーパーマーケット X ではポイントカードを発行している。ポイントカードにはカードごとに ID が振られており購買履歴が管理されている。

さらに X はポイントアプリも運用している。ポイントアプリは以下の機能を有している。

  1. 新規発行: 仮想ポイントカードを発行することができる。
  2. カード・アプリ併用: 発行済みの仮想ポイントカードに対し、既存の物理ポイントカードを紐づけ、ポイントを共有できる。ただし、既に物理ポイントカードが紐づけられた仮想ポイントカードに対して別の物理ポイントカードを紐づけることはできず、紐づけられた物理ポイントカードは機種変更機能で移行した場合を除き別の仮想ポイントカードに紐づけることはできない。
  3. 機種変更: スマホ A にインストールされたポイントアプリ内の仮想ポイントカードに対し、スマホ B にインストールされたポイントアプリ内の仮想ポイントカードのポイントを全額移行できる。また、スマホ B のポイントカードに物理ポイントカードが紐づけられていた場合、その紐づけもスマホ A に移行する。移行元、移行先の双方に物理ポイントカードが紐づけられている場合は移行できない。移行後は、移行元のスマホ B のポイントカードは無効化され、再度新規発行して新しい仮想ポイントカードを発行するまで利用できない。

データモデル

テーブル一覧

論理テーブル名 物理テーブル名 概要
購買サマリー purchase_summary 購入ごとの集計
ポイント point ポイント
ポイントカード card 物理ポイントカード
ポイントアプリ app 仮想ポイントカード
併用履歴 card_app_combine_history 物理ポイントカードと仮想ポイントカードの紐づけ
アプリ移行履歴 app_migrate_history 機種変更による仮想ポイントカードの移行

ER 図

補足

機種変更時の移行先のポイント ID は、移行元のアプリに物理ポイントカードが紐づいている場合は移行元のポイント ID、いずれにも物理ポイントカードが紐づいていない場合は変更なし。

課題設定

分析課題

X は、購入額の多い顧客に対して定期的に優待券を配布したいと考えている。重複配布を可能な限り[1]避けるために、複数のポイントカードを利用している顧客については 1 顧客としてまとめて購入額を集計する必要がある。どのようにして購入額を集計すれば良いか。

データ例

以下の顧客を想定する。

  1. 顧客 1: 物理カード C1(ポイント ID P1)を持っており、これをアプリ A1(ポイント ID P2)に紐づけ、その後アプリ A2(ポイント ID P3)に移行した
  2. 顧客 2: 物理カード C2(ポイント ID P4)とアプリ A3(ポイント ID P5)を持っており、アプリを A4(ポイント ID P6)に移行した後物理カードと紐づけた
  3. 顧客 3: 物理カード C3(ポイント ID P7)とアプリ A5(ポイント ID P8)を持ち、紐づけは行っていない
  4. 顧客 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]の世界では、以下のような方法が利用できることが知られています。

  1. 隣接リスト構造 + 再帰クエリー
  2. 隣接リスト構造 + 外部アプリケーションによるループ処理
  3. 閉包テーブル

隣接リストは単純なデータ構造で、親子関係のみをレコードに持つものです。上の木構造は以下のように表現できます。

親(統合先) 子(統合元)
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]

時系列順に構築できる性質のため、特定時点でのバックアップを持っていれば、それ以降の統合履歴テーブルを構築することも容易です。タイムスタンプが顧客統合履歴テーブルの最新時刻以降のデータを利用して追加・統合操作を行うだけで良いです。

閉包テーブルはデータ数がかなり多くなるため、場合によってはデータを削減したいことを考えるかもしれません。今回の課題設定のように、最終的な統合先だけがわかれば良い場合は、木構造の途中経路の部分は間引くことができます。ただし間引いた場合は、過去の特定時点での統合状態を把握することができなくなりますので、本当に間引かなければならない場合を除いては慎重になった方が良いでしょう。

脚注
  1. 紐づけが行われない限りは別の顧客として扱うのは致し方がないと割り切るが、紐づけが行われた実績がある場合は漏らさないように努力するということ。 ↩︎

  2. テーブルデータとしたのは、一般的に顧客管理システム利用される RDB/DWH 扱うことができるためです。 ↩︎

  3. 実はアプリ移行履歴(app_migrate_history)を使えば P10, P11, P12 が同一顧客であることがわかるため顧客 4 についても集計単位を 1 つにできるのですが、これについては読者への課題とします。 ↩︎

  4. 再帰クエリーを避けるための閉包テーブルですが、運用時のデータ追加分に対するトポロジカルソートのための再帰クエリーは件数が多くないためパフォーマンス上の問題は起こりづらいです。初期構築時にやるとデータ量と階層の深さによっては現実的ではなくなります。 ↩︎

Discussion