FirebaseAnalytics/BigQueryを使う上で覚えておきたい関数

はじめに

この記事は
Diverse Advent Calendar 2019の23日目の記事になります！

前日の記事は[@tacksman]さんのFlutterとFirestoreを使い始めて一ヶ月がたったというタイトルの記事でした！

僕自身はiOS(Swift)とUnity(C#)でFirestoreを使った経験はあるのですが、
話題のFlutterでFirestoreを使ったことがなく
使い始めて系の記事はこの組み合わせでは未経験の僕には入りやすかったです！

日本語系の記事が少ないようなのでDiverseからどんどん日本語での記事が増えていくといいですね

前日の記事についてはここまでにさせていただき
今回はFirebase Analytics関連のお話でBigQueryについてになります。

MySQLなどのDB操作の経験はあったのですが、
見慣れないテーブル構造に戸惑った結果FirebaseAnalyticsと連携するBigQueryのテーブル構造を分解してデータを抽出していくにはある関数を知る必要があるよって話になります。

Firebase Analyticsについては以下記事で触れてるのでよかったらぜひ！
Event, UserPropertiesを送っておくと戦略的に幅が広がる話

BigQuery

BigQueryは、
Google が提供するサーバーレスでスケーラビリティに優れた企業向けデータウェアハウスです。

主に行動ログを貯めるために使用している企業が多いのではないでしょうか？

データを貯めてBIツールで欲しいデータを整形しダッシュボードに表示をして
施策の良し悪しの判断や次にやる施策の方針決めに使ってる企業もあると思います。

ちなみにDiverseではLookerというBIツールを使用しており今年の弊社アドカレでも触れられているのでこちらもよかったらご覧になってください！
BIツールが定着していない会社でLookerを導入して1年後の世界

次にこのBigQueryがFirebaseとどのように絡んでくるのかみてみましょう

Firebase * BigQuery

FirebaseプロジェクトとBigQueryを連携すると
以下6つのFirebaseが提供するサービスのデータをBigQueryに流すことができます。

Google Analytics
Crashlytics
Predictions
Cloud Messaging
Performance Monitoring

Firestore
今年のFirebaseSummit2019で発表されたExtensions(githubはこちら)を使用することで
FirestoreのCollection単位でBigQueryにデータを流すことが可能です。

Firebaseが提供するサービスの全てがBigQueryと連携しているわけではないですが、
連携すると良いサービスについてはプロダクトチームの判断で連携することが可能だよって感じですかね！

Analyticsのレコードを覗いてみる

MySQLを使用している人には馴染みの表でデータが表現されています。
1レコードはアプリから送信した一つのEventを表現しており
複数のカラムで1つのEventがログとして保存されてます。

1レコードに含まれる情報量多いですよね

詳細が気になる方はこちらをみてみると良いかもしれません。

小ネタになるのですが、
以下スクショのようにEventごとにUserPropertiesでフィルターをかけることが可能です！

これが実現できるのもEventのレコードにUserPropertiesの情報が含まれているからなんですかね！

データ構造

前提としてマッチングのメッセージの例です。
チャットルームにてメッセージを送信したというEventを送る際に
EventのParametersとしてroomIDと送信者のIDを送るというケースです。

senderIDがxxxのUserをBigQueryから抽出することを本記事では目標にしてみましょう

Analytics.logEvent(
    "sent_message",
    parameters: [
        "roomID": "1",
        "senderID: "xxx"
    ]
)

BigQuery上での構造は以下の内容です。
正直初めてこの構造をみた時、senderIDがxxxの人を抽出するQueryを書くのに悩みました
MySQLは多少触れたことがあったのですがこのような構造に遭遇してこなかったため未知との遭遇

未知との遭遇のためそいつの正体を知る必要があります
BigQuery上のデータ構造を少し分解した図が以下になります。

どうやら1カラムにMapで値が入っているように見えますね！
ではどうやってネストされた構造からsenderIDがxxxの人を抽出することができるのでしょうか？

UNNEST関数

FirebaseAnalyticsのBigQueryのデータ構造を分解していく上で重要なのがこのUNNEST関数です
UNNEST関数の役割としてはネストされた構造を一組の行に変換することです。

Queryの良し悪しは出ちゃいますが今回はこんな感じで抽出してみました。

UNNESTで欲しい箇所のネストを行に変換したQueryをSubQueryとして今回やってみました。
(Query力低いのでこれもできるとかあればぜひ)

WITH
senderInfo AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'roomID') as roomID,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'senderID') as senderID
  FROM
    `{your_project}.analytics_216055106.events_*`
  WHERE
    event_name = "sent_message"
  GROUP BY roomID, senderID
)
SELECT * FROM senderInfo WHERE senderInfo.senderID = "xxx"

Queryを実行する前にどれくらいの容量がかかるのかを確認できるのでConsoleから直で実行する際には確認しておくと良さそうです。
仕組みとしてはDry Runでおおよその容量を目安として表示しているのかなと思います。

実際の金額いくらなの？っていうのを知りたい場合は、
Google Cloud 料金計算ツールで確認してみるといいでしょう。

さいごに

まだ使い始めて一年も立ってないのでペーペーですが
、今後知見が溜まり次第その知りたい情報はBigQueryを使うべきか？FirebaseConsoleで完結するかの線引き基準などそういう少し掘り下げた記事を書いてみようと思います