kenju's blog

About Programming, Mathematics, Security and Blockchain

redashでコホート分析を表示するSQL

redashでコホート分析を出力するSQLの書き方が少し癖のある書き方だったので、調べた内容をメモ。

結論

コホートグラフ描画に必要なのは、以下の4種類のカラム:

  • 基軸日
  • 基軸日からの経過日
  • 基軸日からの経過日におけるイベント数
  • 基軸日におけるイベント数のトータル(同じ基軸日にはデータが重複する)

カラム名は、それぞれ

  • cohort_date, date
  • day_number, day
  • value
  • total

とでもしておけばイメージがつきやすいのでは。

具体例

前提

「ユーザーの来訪頻度を把握する」ことを目的として、「1週間における全ユーザーのTOPページアクセス」をコホート分析するとする。

つまり、イベントは「TOPページアクセス」になる。

SQL出力結果イメージ

num cohort_date day_number value total
1 2017/9/1 1 800 1000
2 2017/9/1 2 600 1000
3 2017/9/1 3 550 1000
4 2017/9/1 4 300 1000
5 2017/9/1 5 150 1000
6 2017/9/1 6 200 1000
7 2017/9/1 7 10 1000
8 2017/9/2 1 2000 2500

たとえば「No.2」の行は、「2017/9/1日にTOPページにアクセスしたユーザー1000人の内、2日後の2017/9/2にアクセスした人は600人」ということだ。

最終的にこのようなテーブルを出力することをイメージすれば、コホートグラフが描画できる。

公式ドキュメント

redash.io