Re:dashで、FirebaseAnalyticsのBigQueryからコホートを表示する方法

FirebaseAnalytics使っていますか？無料でここまでやってくれるのはかなりありがたいですね。
私は特にRetensionページをよく見るのですが、ここではフィルターはできず、新規ユーザーに関するコホートしか見れません。
新規ユーザーだけでなく、ある時点で特定の状態だった人の継続率をみたいケースがあったので、手始めに新規ユーザーに関するコホートをRe:dashで再現することにしました。

1. クエリを作る

コホートを表示するには以下の4つの数字が必要です。（参考文献)

1. date（獲得日）
2. past_days（経過日数）
3. user_count (date, past_daysごとのユーザー数）
4. total_count（獲得日の人数（母数））

実際のクエリはこちらです。期間は昨日から28日間を取得しています

WITH first_counts AS
  (SELECT DATE(TIMESTAMP_MICROS(user_first_touch_timestamp), 'Asia/Tokyo') AS first_open_date,
          count(DISTINCT user_pseudo_id) AS total_count
   FROM `ninarubaby.analytics_164116869.events_*`
   WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
     AND device.operating_system = "IOS"
   GROUP BY first_open_date),
     cohorts AS
  (SELECT DATE(TIMESTAMP_MICROS(user_first_touch_timestamp), 'Asia/Tokyo') AS date,
          DATE_DIFF(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), DATE(TIMESTAMP_MICROS(user_first_touch_timestamp), 'Asia/Tokyo'),DAY) AS past_days,
          count(DISTINCT user_pseudo_id) AS user_count
   FROM `ninarubaby.analytics_164116869.events_*`
   WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
     AND device.operating_system = "IOS"
     AND DATE(TIMESTAMP_MICROS(user_first_touch_timestamp), 'Asia/Tokyo') >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
   GROUP BY past_days, date )
SELECT date, past_days,
             user_count,
             total_count
FROM first_counts
INNER JOIN cohorts ON first_counts.first_open_date = cohorts.date
WHERE past_days > 0

date, past_days, user_countは一気にとってしまって、別でとったtotal_countをinner joinしています。
firebase analyticsの設定によりますが、弊社では日本時間で設定していたのでtimestamp型からdate型に変換する際にtimezone（Asia/Tokyo）を指定しています。
past_days > 0 を表示しないと一部表示がおかしくなったので一応つけていますが、手元で確認して問題なければ削除しても良いと思います。

2. コホートを表示する

それでは、実際に可視化して見ましょう

1. Re:dash上で New Visualization をクリック
2. 以下のように設定
   • Visualization Type: Cohort
   • Date: date
   • Strage: past_days
   • Bucket Popuration Size: total_count
   • Stage Value: user_count
3. Saveをクリック

すると、下記のような画面が作成できます（実際のデータは見せられないのでイメージ図ですw）

最後に

BigQueryにexportしてくれてれば、あとでなんとでもなるというのは楽ですね。
また、BigQueryなので、データが大きくても耐えられる時間内で帰ってきてくれるのも嬉しいところです。
初期のアプリであればBigQueryにexportしてもタダみたいな値段で使えるのでとりあえずexportしておくことをオススメします。
では、よいデータライフを