Edited at

Firebase Analyticsのカスタムイベントのデータを今月,先月で絞ってredashで見る

Firebaseでイベントをログに記録したものをRedashで表示したくなり、一応できたのでメモ。

Firebase の BigQuery Exportの機能を使い、アナリティクスのデータとアプリのデータの統合を行うことができます。

詳しいFirebaseのデータをBigQueryに入れる方法はこちらのBigQuery を Firebase にリンクするから確認してください。

RedashでBigQueryをデータソースに追加するにはこちらのBigQuery Setup

をご確認ください。

Firebaseのデータソースが選択できるようになると、下記のような日付ごとに作成されたイベントデータが確認できると思います。

詳しくは下記を参考にしてください。

BigQuery Export のスキーマ

それに対してクエリを書いていきますが、如何せんBigQueryも書いたことがなかったため、日付ごとのテーブルとは、unionしまくるのか?

と思ったけど、BigQueryにはワイルドカード テーブルという機能がありそれを用いれば今月、先月のデータが出せるということがわかりました。

(ワイルドカード テーブルを使用した複数テーブルに対するクエリ)

例えば、今月のhogeイベントのカスタムパラメータを取得したい場合は、

-- 今月のPVデータ

SELECT JSON_EXTRACT(TO_JSON_STRING(event_params, TRUE), '$[3].value.string_value') AS hoge,
count(*) AS PV
FROM `analytics_xxxxxxxx.events_*`
WHERE
_TABLE_SUFFIX > CONCAT(FORMAT_DATE("%Y%m",(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH))), "01")
AND event_name='my_custom_event'
GROUP BY JSON_EXTRACT(TO_JSON_STRING(event_params, TRUE), '$[3].value.string_value')

このようにすれば今月の1日以降のテーブルを対象に、イベントを取得することができます。

カスタムイベントのパラメータはevent_paramsカラムにJSONで入っているのでJSON_EXTRACTにて取得することができます。

先月も同じ要領です。

-- 先月のPVデータ

SELECT JSON_EXTRACT(TO_JSON_STRING(event_params, TRUE), '$[3].value.string_value') AS hoge,
count(*) AS PV
FROM `analytics_xxxxxxxx.events_*`
WHERE _TABLE_SUFFIX > CONCAT(FORMAT_DATE("%Y%m",DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY)), "01")
AND _TABLE_SUFFIX < CONCAT(FORMAT_DATE("%Y%m",(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH))), "01")
AND event_name='my_custom_event'
GROUP BY JSON_EXTRACT(TO_JSON_STRING(event_params, TRUE), '$[3].value.string_value')

ワイルドカード テーブルには一定ルール的なのがあるのですが、こちらのドキュメントを読むと理解できます。

あとはRedashのクエリリザルトを使って、アプリのデータと結びつけることでFirebaseのイベントデータと、アプリのデータを紐づけることができ、

具体的なデータの分析を行うことができます。

めでたしめでたし。