Redashでコホート分析のはまりどころ

はじめに

Redashでコホート分析ができるですが、いくつかハマったのでメモを残しておきます。

コホートとは？

詳細は他のサイトで調べて頂きたいですが、例えば、ある月に100人が予約して、その翌月にその100人のうち何人が予約したか？その翌々月は？というの表した図です。継続率の分析やリピートするタイミングの分析などに使うことが多いと思います。

こんな感じの図です

サンプルデータ

• 以下のようなシンプルなテーブルを例とします

• Ordersテーブル：予約データ

• orders.user_id：予約者

• orders.created_at：予約作成日

SQL

• クエリはこんな感じです。mysql5系で試したので、MySQL8.0とかでしたら、with句を使ったほうが見やすいかと思います。

-- ユーザーごとの予約の初回の月
CREATE TEMPORARY TABLE tmp_first_date_by_user AS (
 SELECT
  DATE_FORMAT(MIN(orders.created_at), '%Y%m') AS first_date,
  user_id
 FROM orders
 WHERE orders.created_at IS NOT NULL
 GROUP BY user_id
);

-- 初回の月ごとの予約の件数
CREATE TEMPORARY TABLE tmp_user_count_by_first_date AS (
 SELECT
  first_date,
  COUNT(user_id) AS total
 FROM tmp_first_date_by_user
 GROUP BY first_date
);

-- それぞれのorderの月ごとの件数を求める(同月の予約は1とする)
CREATE TEMPORARY TABLE tmp_user_count_by_date AS (
 SELECT
  DATE_FORMAT(orders.created_at, '%Y%m') AS cohort_date,
  tmp_first_date_by_user.first_date AS first_date,
  COUNT(DISTINCT orders.user_id) AS user_count
 FROM orders
 INNER JOIN tmp_first_date_by_user ON tmp_first_date_by_user.user_id = orders.user_id
 WHERE orders.created_at IS NOT NULL
 GROUP BY 1, 2 
);


SELECT
 date_format(CONCAT(tmp_user_count_by_first_date.first_date, '01'),'%Y-%m-%d') AS date,
 PERIOD_DIFF(tmp_user_count_by_date.cohort_date, tmp_user_count_by_first_date.first_date) AS month_number,
 CAST(SUM(tmp_user_count_by_date.user_count) AS SIGNED) AS value,
 tmp_user_count_by_first_date.total AS total
FROM tmp_user_count_by_first_date
LEFT OUTER JOIN tmp_user_count_by_date ON tmp_user_count_by_date.first_date = tmp_user_count_by_first_date.first_date
GROUP BY 1, 2, 4
ORDER BY 1, 2, 4

Redashの設定

• RedashのVisualizationの設定1

• RedashのVisualizationの設定2

はまりポイント

• なにかがおかしいと、RedashでCohortにVisualization Typeを選べない（何が原因かわからないのでハマる）
• データが多すぎるとブラウザが固まるっぽいので、最初はWHERE句で数行だけにして、設定がうまくいってから、データ量を調整して増やしていく
• Redash では、月、週、日の単位でコホート分析するだけの機能なので、年や四半期などはできない
  • Redash設定画面の Time Interval
• 結果は、次の4つのカラムをかならず出力する。
  • 日付：必ずYYYY-MM-DDの形式にする
    • SQLの date
    • Redash設定画面の Date(Bucket)
  • 期間：最初の日付からの経過時間
    • SQLの month_number
    • Redash設定画面の Stage
  • その期間のデータ：その経過時間のデータ
    • SQLの value
    • Redash設定画面の Stage Value
  • 最初の日付のデータ：初回のデータ
    • SQLの total
    • Redash設定画面の Bucket Population Size
• 日付は、必ずYYYY-MM-DDの形式にする。月のコホートでもYYYY-MMだとブラウザが固まる。YYYYMMDDでもだめ。
• 結果として表示するカラム名は、全部小文字にする。大文字と小文字が混じっているとブラウザが固まる

参考