Firebase×Bigqueryでフルリテンションを集計する

動作環境

FirebaseとBigQueryを統合し、FirebaseAnalyticsのデータをBigQueryにインポートしている環境です。

やりたいこと

初回起動(first_open)の日から2日間連続で起動しているUU割合、7日間連続で起動しているUU割合（フルリテンション）を初回起動日別に取得し、ダッシュボード化したい。
リテンションの定義はアプリのリテンションをSQLで出して可視化するを参考にしました。

考え方

WITH句の中のstartで初回起動日とuser_pseudo_idの一覧を作成。
usedateで初回起動に限らない起動日とuser_pseudo_idの一覧を作成。
その後のSELECT文でstartに対して日付の条件を1日ずつずらしてusedateを見たい日数分LEFT OUTER JOINすることで、user_pseudo_idの件数＝連続起動しているUU数となる状態になっています。

クエリ

WITH start AS(
  SELECT
    user_pseudo_id AS start_id,
    EXTRACT(DATE FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE "Asia/Tokyo") AS start_date
  FROM
    `hogehoge.analytics_xxxxxxxx.events_*` ---BigQueryの集計対象となるテーブル名
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 30 DAY)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY))
    AND event_name = "first_open"
  GROUP BY
    start_id,
    start_date
),
usedate AS(
  SELECT
      user_pseudo_id AS use_id,
      EXTRACT(DATE FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE "Asia/Tokyo") AS use_date
  FROM
    `hogehoge.analytics_xxxxxxxx.events_*` ---BigQueryの集計対象となるテーブル名
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 30 DAY)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY))
    AND event_name IN ("session_start", "screen_view", "user_engagement")
  GROUP BY
    use_id,
    use_date
)
SELECT
   start.start_date,
   COUNT(start_id) AS startuu,
   COUNT(day2.use_id) AS secondday_uu,
   COUNT(day7.use_id) AS seventhday_uu
FROM
   start
   LEFT OUTER JOIN usedate day2 ON start.start_id = day2.use_id AND DATE_ADD(start.start_date, INTERVAL 1 DAY) = day2.use_date
   LEFT OUTER JOIN usedate day3 ON day2.use_id = day3.use_id AND DATE_ADD(day2.use_date, INTERVAL 1 DAY) = day3.use_date
   LEFT OUTER JOIN usedate day4 ON day3.use_id = day4.use_id AND DATE_ADD(day3.use_date, INTERVAL 1 DAY) = day4.use_date
   LEFT OUTER JOIN usedate day5 ON day4.use_id = day5.use_id AND DATE_ADD(day4.use_date, INTERVAL 1 DAY) = day5.use_date
   LEFT OUTER JOIN usedate day6 ON day5.use_id = day6.use_id AND DATE_ADD(day5.use_date, INTERVAL 1 DAY) = day6.use_date
   LEFT OUTER JOIN usedate day7 ON day6.use_id = day7.use_id AND DATE_ADD(day6.use_date, INTERVAL 1 DAY) = day7.use_date
GROUP BY
   start.start_date

アウトプット

ダッシュボードツールのRe:dashを使うとこんな形で可視化することができます。