データビジュアライズのための基本的なSQLサンプル（LTV編）

このシリーズは、データビジュアライゼーションを担当する人にとってのSQL の代表例を解説するシリーズです。前回記事はこちらです。

・データビジュアライズのための基本的なSQLサンプル（時系列集計編）

・データビジュアライズのための基本的なSQLサンプル（複数の軸を同時に集計する場合）

今回は、これまたインターネットサービスを取り巻く上でよく出てきがちなLTVの代表的な出し方について解説しましょう。

LTV = Life Time Value

獲得顧客１人あたりの価値を表す代表的な指標です。
ある期間の安価で顧客がサービスに貢献してくれた価値を測る指標として用いられます。
例えば、e-commerce サービスだと、サービス参加後90日間の顧客一人当たりの総決済額などが用いられます。
これを、SQLで出してみます。

データ構造を観察し、メインテーブルを決める

多くのサービスでは、

・users（顧客テーブル）
・order（注文テーブル）
・order_details（注文詳細テーブル）

といったような構成になっており、order テーブルに総決済額が記録されている構成になっているでしょうから、この構成を前提とします。

基本となるSQL

まずは、90日という縛りのない、登録後のユーザーの総決済額をユーザー登録日グループごとに出してみます。

今回、参加したユーザー（新規登録したユーザー）全てを対象にしたいので、メインテーブルは users とします。

このメインテーブル選びは、最終的な結果に対する影響は少ないですが、思考の段階での整理のしやすさが抜群に変わってきます。
この思考段階が勝負なところもあるデータビジュアライゼーションにとっては、結構大きなファクターです。

SELECT
  DATE_FORMAT(users.created_at, "%Y年%m月%d日") AS "ユーザー登録日",
  SUM(order.total_price) / COUNT(DISTINCT users.id)
    AS "ユーザー１人当たりの平均総決済額"
FROM users
  LEFT JOIN order ON users.id = order.user_id
GROUP BY `ユーザー登録日`
ORDER BY `ユーザー登録日`

・order / users のテーブル結合を行なっている
　・usersが欠損しないよう、LEFT JOIN
・SUM / COUNT で平均総決済額を算出している
　・COUNTは、users.id の重複を考慮してDISTINCTを指定

というあたりが新しいトピックになりますが、それ以外の期間集計などはこれまでの延長線上です。

ここでの大きなコツは、やはり users をメインテーブルにしていることです。
order をメインテーブルにした場合、「まだ一度も買っていない顧客」のことを気にしなくてはなりません。こうした考えることを減らすために、取ろうとしているデータの構造はどうなっているのか、に目を向けることはとても大切なことです。
たった１文のSQL であっても、実はオンメモリでデータレイキングをしているわけですから、ここの構造構築力が可視化力の決定的な差になってきます。

これに、登録後90日以内縛りをつけてみます。

WHERE order.created_at <= (users.created_at + INTERVAL 90 DAYS)

です。これで、users.created_at を使ってorder の集計範囲を制御しています。
これを最初のSQLに組み入れ、結果はこうなります。

SELECT
  DATE_FORMAT(users.created_at, "%Y年%m月%d日") AS "ユーザー登録日",
  SUM(order.total_price) / COUNT(DISTINCT users.id)
    AS "ユーザー１人当たりの平均総決済額"
FROM users
  LEFT JOIN order ON users.id = order.user_id
WHERE order.created_at <= (users.created_at + INTERVAL 90 DAYS)
GROUP BY `ユーザー登録日`
ORDER BY `ユーザー登録日`

これでユーザー登録後90日以内のLTVが算出できました。

初回注文日を起点にして90日以内のLTVを出したい場合

さぁ、ちょっとずつ複雑になっていきます。

ここで困るのは「初回注文日」というカラムがテーブルのどこにも存在しないことです。
僕らは無意識に users.initial_ordered_at というカラムを欲しがっています。

ここで今がサービスの初期なのであれば上記のカラムを構造上作ってしまうというのは十分検討に値します。サービスは成長していけば必ずこのLTV という指標は気になるからです。

ここではこれを後付けでやる方法を紹介します。

下準備①「初回注文日」の意味を考える

上記のusers.initial_ordered_atことを考える前に「初回注文日」という言葉の意味を考えます。
注文しているユーザーのみを集計の対象とする
という文脈を読み取れるでしょう。

これに対するアプローチは、メインテーブルを逆に考えることです。orderに含まれている限りは必ず注文を行なっているわけですから、orderをメインテーブルとして考えた方が、あとあと考えることが減ります。

FROM users
  LEFT JOIN order ON users.id = order.user_id

を

FROM order
  LEFT JOIN users ON order.user_id = users.id

とします。
この場合欠損を考慮する必要がないため、LEFT JOIN は INNER JOIN に変えることも可能です。（その方がパフォーマンスがいいですね）

FROM order
  INNER JOIN users ON order.user_id = users.id

こうしておきましょう。

下準備②初回注文日を含んだテーブルを「作る」

初回注文日を含んだQueryはこんな感じで書きます。

SELECT
  users.id AS "id",
  MIN(order.created_at) AS "initial_ordered_at"
FROM order
  INNER JOIN users ON order.user_id = users.id
GROUP BY users.id

これで、idとinitial_ordered_at（初回注文日）だけを持つ結果が返ってきます。
クエリの結果はサブクエリとして書くと仮想テーブルとして取り扱えます。

２つの下準備をマージして結果を求める

２つの下準備を参考に、ベースSQLの中のFROMセクションを入れ替えてやります。

INNERSELECT
  DATE_FORMAT(extended_users.initial_ordered_at, "%Y年%m月%d日") AS "初回注文日",
  SUM(order.total_price) / COUNT(DISTINCT users.id)
    AS "ユーザー１人当たりの平均総決済額"
FROM order
  INNER JOIN (SELECT
      users.id AS "id",
      MIN(order.created_at) AS "initial_ordered_at"
    FROM order
      INNER JOIN users ON order.user_id = users.id
    GROUP BY users.id) extended_users ON order.user_id = extended_users.id
WHERE order.created_at <= (extended_users.initial_ordered_at + INTERVAL 90 DAYS)
GROUP BY `初回注文日`
ORDER BY `初回注文日`

・下準備した二つの要素を用いてFROMセクションの中身を変えました。
・サブテーブルにはextended_users という名前をつけ、拡張ユーザーテーブルだとわかるようにします。
・小さく効果大なコツ：サブテーブルをFROMセクションに用いることにより、不要なクエリ実行の機会を最小限に留めています
・あとは「そういうテーブルがあるもんだと思って各所の値を変える」だけ。

文脈から欲しいデータ構造を読み解く力

このように、同じLTVでも「ユーザー登録時」が起点になるのと「初回注文日」が起点になるのとでは、実のところ求めるデータ構造が全く違うのが実感できたと思います。

そして、求めるデータ構造に構想が近づけば近づくほどそのあと考えることはどんどん減っていきます。

データの可視化という文脈はそういうデータのモデリングを訓練するにはうってつけの材料なので、ぜひ目の前にそういった課題が転がっている方がいたら、ただオペレーティブに出すばかりではなくて、応用力を目指した抽象化にもチャレンジしてみてください。