グロースハックのためのSQL – コーホート分析

コホート分析とは

定義

ユーザーを一定の条件や属性によってグループに分け、時間の経過に伴って、グループごとにどんな行動の変化があるかを分析するものです
Udemy メディアグロースハックとは？事例から成功手法まで５分で理解できる記事 ( https://udemy.benesse.co.jp/marketing/growth-hack.html#i-4)

よくある図と読み方

たいてい、Google検索で「cohort analysis」と検索すると上のような表が出てくる。
Google Analyticsを使ったことがある人は、自動生成される表を見たことがあるかもしれない。

赤枠で囲った部分から得られる示唆は下記になる
・ 1/25に登録したユーザーは、2日目には23.5%再訪している
・2/2に登録したユーザーは、2目には18.5%再訪している
-> ユーザーのサービス定着率が下がっている（23.5% -> 18.5% ）

SQLでコーホート表(のようなもの)を作る

テーブル構成

シンプルなテスト用のテーブルを用意した。

user_idが0の人は、2019-05-01に非アクティブ（＝ログインしてない / 買い物してない / 動画を再生していないなど意味合いはなんでもよし）というレコードが入っている

【自分で試したい人用】SQLダンプはこちら

各ユーザーのactiveの推移を見るSQL

SELECT
  ul1.user_id,
  ul1.date AS first_access_day,
  ul1.login AS day01,
  ul2.login AS day02,
  ul3.login AS day03
FROM
  user_log ul1
  -- 同じユーザー・翌日の日付で自己結合
  LEFT JOIN user_log ul2 ON ul1.user_id = ul2.user_id
  AND ul1.date = DATE_SUB(ul2.date, INTERVAL 1 DAY)
  -- 同じユーザー・翌々日の日付で自己結合
  LEFT JOIN user_log ul3 ON ul1.user_id = ul3.user_id
  AND ul1.date = DATE_SUB(ul3.date, INTERVAL 2 DAY)

全ユーザーのactive率の推移を見るSQL & 7日間に拡張

１週間に期間を広げ、登録日でGroup by すると一気にコーホート表感が出る。

SELECT
  ul1.date AS first_access_day,
  SUM(ul1.active) / COUNT(*) AS day01,
  SUM(ul2.active) / COUNT(*) AS day02,
  SUM(ul3.active) / COUNT(*) AS day03,
  SUM(ul4.active) / COUNT(*) AS day04,
  SUM(ul5.active) / COUNT(*) AS day05,
  SUM(ul6.active) / COUNT(*) AS day07,
  SUM(ul7.active) / COUNT(*) AS day07
FROM
  user_log ul1
  LEFT JOIN user_log ul2 ON ul1.user_id = ul2.user_id
  AND ul1.date = DATE_SUB(ul2.date, INTERVAL 1 DAY)
  LEFT JOIN user_log ul3 ON ul1.user_id = ul3.user_id
  AND ul1.date = DATE_SUB(ul3.date, INTERVAL 2 DAY)
  LEFT JOIN user_log ul4 ON ul1.user_id = ul4.user_id
  AND ul1.date = DATE_SUB(ul4.date, INTERVAL 3 DAY)
  LEFT JOIN user_log ul5 ON ul1.user_id = ul5.user_id
  AND ul1.date = DATE_SUB(ul5.date, INTERVAL 4 DAY)
  LEFT JOIN user_log ul6 ON ul1.user_id = ul6.user_id
  AND ul1.date = DATE_SUB(ul6.date, INTERVAL 5 DAY)
  LEFT JOIN user_log ul7 ON ul1.user_id = ul7.user_id
  AND ul1.date = DATE_SUB(ul7.date, INTERVAL 6 DAY)
GROUP BY
  ul1.date
ORDER BY
  ul1.date

キレイにスタイリングして上司に褒められる

SQLの取得結果でも充分示唆は得られるが、スプレッドシートに出力して条件付き書式をつけると見やすくなる。
※今回は乱数でデータを作っているので規則的なグラデーションにはなっていない