Firebase と BigQuery、Redash でコホート分析グラフを作成してみたよ

どうも、ファームノートでエンジニアをしている永沼です。この記事はFarmnote Advent Calendar 2018の16日目です。
最近 Firebase に入門中です。先日はFirebase と BigQuery の連携をするとおいくらかかりそうか雑に試算してみました。今回はじゃあ実際にひとつ連携させてやってみたよ、という話です。

TL;DR

・Firebase と BigQuery を連携させて Analytics データを集計できるよ
・イベントデータはデータ構造にちょっと癖があるよ
・さらに Redash とかと連携させるとグラフ化できて便利だよ

背景

前回の記事に書いたので詳しくは見てくださいませ。ざっくりこんな感じ。

・Firebase 向け Google Analytics はほとんどお仕着せのグラフしか使えない
・BigQuery と連携すると Analytics データを SQL でよしなにできる
・じゃあこれでよしなに指標を追っていけるのではー。ちょっとコホート分析グラフでも作ってみますか(前回書き忘れた)

グラフを作成する準備をする

実際に試してみましょう。Firebase 導入から Redash への連携までは以下のような手順になります。それぞれ手順通りにやればできるんですが、イチからやってみると結構長い・・・

1. アプリに Firebase を導入する
2. BigQuery の課金を有効にする & Blaze プランに変更する
3. Firebase と BigQuery を連携する
4. BigQuery と Redash を連携する

1. アプリに Firebase を導入する

公式のガイドを見て頑張りましょう。

Add Firebase to your iOS Project  |  Firebase

Add Firebase to your Android project

React Native での react-native-firebase などライブラリ経由で利用する場合は先に各ライブラリで用意されている手順を見たほうがよさそうです。

invertase/react-native-firebase

2. BigQuery の課金を有効にする & Blaze プランに変更

先に GCP の無料トライアルを申し込みました。300ドル相当分を無料で使わせてくれるらしい。太っ腹〜。BigQuery というか GCP 全体で課金が有効（無料トライアル含む）になっていればいいみたい

無料トライアルと無料枠 | Google Cloud

なにはともあれありがたく使わせてもらいました。なむなむ

Firebase を Blaze プランに変更します。Firebase Console の該当プロジェクトから変更します。

画面に従ってポチポチと進める...

シュッと完了。特にお支払い情報とか入れなかった。課金情報は GCP 側と統合されているっぽい。

3. Firebase と BigQuery を連携する

Firebase Console の該当プロジェクトから連携を有効にします。

Link BigQuery to Firebase - Firebase ヘルプ

手順に従ってポチポチやっていきましょう。

ポチポチ進めて「BigQueryにリンク」をクリックすると完了。

Analytics の連携を有効にします。あと、Clashlytics、Prediction、Cloud Messaging のデータも連携が可能なようですが、こちらは今回は試していませんので詳しくはわからず。

4. BigQuery と Redash を連携する

1日待つと GCP 側に Firebase 側と同名のプロジェクトが作成され（もしかすると連携した時点で作成されていたかもしれない...）イベントデータがエクスポートされます。

Redash と連携するにはこのプロジェクトでサービスアカウントを作成し、Redash 側にプロジェクト ID と生成された JSON を指定することで連携できます。

詳しくはRedash からガイドが出ていますのでこちらを参考に。ベータ版機能として標準SQLを利用するオプションがあるので、そちらをチェックすると便利です

BigQuery Setup

コホート分析グラフを作ってみる

さてさて、これで無事に Firebase の Analytics データをほげほげふがふがする用意ができました。

スキーマ

BigQuery Export のスキーマ - Firebase ヘルプ

Analytics データはこんなスキーマになっています。
前回の記事を書いた時点では日本語ページは古いスキーマのままだったのですが、更新されて新しいスキーマに置き換わっていました。これを信用して大丈夫です。

見ていただくとわかるんですが、いくつかキーがネストしている箇所があって「なんじゃこりゃー」となります。

これはどうやら RECORD 型というものらしく、SELECT をかけると1行あたり複数の値が返ってきます。詳しくはググってください。これは UNNEST を使ってバラバラにすることができます。

Redash でコホート分析グラフを作成してみる

お題としてはユーザー維持コホートを任意のイベントを利用してやりたいな、というものです。ユーザー登録を完了したユーザーの DAU を追うようなケースを考えます。

【必要な項目】
・ユーザー登録完了日
・↑の日にユーザー登録を完了した人数
・ユーザー登録完了日からの日数
・↑の日にログインした人数

ゴニョゴニョっとすると以下のようなクエリが出来上がりました。もうちょっとシュッと書けないものか、という気はしますがなにはともあれこれでできます。

WITH src AS
(
    SELECT
           DATE(TIMESTAMP(user_properties.value.string_value)) AS sign_up_date,
           DATE(TIMESTAMP_MILLIS(cast(event_timestamp/ 1000 AS int64))) AS event_date,
           DATE_DIFF(DATE(TIMESTAMP_MILLIS(cast(event_timestamp/ 1000 AS int64))), -- event_date
                     DATE(TIMESTAMP(user_properties.value.string_value)), -- sign_up_date
                     DAY) AS days_number,
           COUNT(DISTINCT user_id) AS login_count

        -- analytics はイベント日付ごとにパーティションされたテーブルになっている。
        -- events_* と _TABLE_SUFFIX でクエリをかける範囲を指定する。
      FROM `analytics_xxxxxxxx.events_*` t,

        -- user_properties は [{key: string, value: {string_value: string, int_value: int, ...}}] の形式になっている
        -- sign_up_timestamp を扱うために要素ごとにバラして結合している
           UNNEST(user_properties) AS user_properties

        -- 検索範囲は昨日から15日前まで
     WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), interval 15 DAY))
                           AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), interval  1 DAY))
       AND event_name = 'login'
       AND user_properties.key = 'sign_up_timestamp'
  GROUP BY sign_up_date,
           event_date,
           days_number
)

-- ログインしたユーザーが0だった日付も集計したいので昨日から15日前までの日付を生成し、
-- それに LEFT OUTER JOIN で集計データがあれば付加するということをしている。
SELECT
    -- ユーザー登録完了日
       dates as sign_up_date,
    -- ユーザー登録完了日からの日数
       COALESCE(login_days, 0) AS login_days,
    -- 該当の日のログインアカウント数
       COALESCE(login_count, 0) AS login_count,
    -- ユーザー登録完了日にユーザー登録した総アカウント数
       COALESCE(total, 0) AS total

    -- 検索範囲は昨日から15日間
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), interval 15 DAY),
                                  DATE_SUB(CURRENT_DATE(), interval  1 DAY))) AS dates
  LEFT OUTER JOIN
       (
         SELECT v.sign_up_date,
                v.days_number AS login_days,
                v.login_count AS login_count,
                total.login_count AS total
           FROM src AS total
             -- アカウント登録完了人数を取得する
             -- 0 日目のログイン人数 = アカウント登録完了人数としている
          INNER JOIN src AS v ON total.sign_up_date = v.sign_up_date
          WHERE total.days_number = 0
        ) AS point
     ON dates = point.sign_up_date

ゴニョゴニョ

どーん

ふぅ、思いがけず長い道のりだった...

まとめ

Redash と連携させることで Analytics のデータを利用して分析のために自由にグラフを作成したりすることができるようになります。最高ですね。

・Firebase と BigQuery を連携させて Analytics データを集計できるよ
・イベントデータはデータ構造にちょっと癖があるよ
・さらに Redash とかと連携させるとグラフ化できて便利だよ

いつものやつ

ファームノートではちょっくら世界の農業を変えてやるか、というエンジニアを募集しています。興味を持っていただけたみなさまのご応募をお待ちしております。