【BigQuery】Flutter Firebase Analytics/Crashlyticsで送ったログをBigQueryで調べる

Firease Analytics, Crashlyticsは配列でネストされているので少し分析しづらいです。
個人的によく使うクエリを少しメモしておきます。
今回はFlutterからログを送信しています。FlutterではSDK側でよしなにやってはくれないので event_name などの部分は独自で定義した名前になります。
例) page_view, screen_name

Flutter Code

Future<void> sendEvent(
  Action action,
  String screenName, {
  Map<String, dynamic>? params,
}) async {
  final events = <String, dynamic>{
    'screen_name': screenName,
  };
  if (params != null) {
    events.addAll(params);
  }

  await FirebaseAnalytics.instance.logEvent(
    name: action._logName,
    parameters: events,
  );
}

Tableを分解していく

SELECT
  *
FROM
  `service_name.analytics_100000.events_20220630`

特徴的なのは event_params です。
event_parmasはArrayになっているので単純なクエリでは取り出せないので注意する必要があります。

key	value
screen_name	{ “string_value”: “home”, “int_value”: null, “float_value”: null, “double_value”: null }

BigQueryではこのように配列がネストしている時はunnest を使ってフラットにしてあげます。

SELECT
  event_name,
  param,
FROM
  `service_name.analytics_100000.events_20220630`
  unnest(event_params) as param
WHERE
  param.key = "screen_name"

そうするとこのように event_params.key で絞り込めます。ただ、このままだと event_params.values はまだArrayになっているためWHERE句では絞り込めません。絞り込むにはこの部分をサブクエリにする必要があります。

SELECT
    (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = 'screen_name') AS screen_name,      
  FROM
    `service_name.analytics_100000.events_20220630`
  WHERE
    event_name = "page_view"

screen_nameの取得部分をサブクエリに分けるとこうなります。 event_nameが page_view 以外だと screen_name.string_value がnullになるので event_nameでも絞ります。

screen_nameだけで絞れるようになりました。こんな感じで、BigQueryではunnestとサブクエリを使えば目的の値を取り出せると思います。

今回の例ではあまり長くないのでわかりやすいですが複雑なサブクエリを書くととても見づらくなります。そこで使えるのがWITHです。

1つ前のクエリをWITHで書き換えるとこのようになります。

WITH
  screen_name AS (
  SELECT
    value.string_value
  FROM
    `service_name.analytics_100000.events_20220630`
    UNNEST(event_params)
  WHERE
    key = 'screen_name'
    AND event_name = "page_view" )

SELECT
  *
FROM
  screen_name

まだWHEREの条件が少ないので恩恵はあまりありませんが複数条件が加わった時にはWITHを使ったほうが見やすくなるかなと思います。

よく使うクエリ

日付範囲指定

Analyticsのテーブルは日付毎に分かれているのですが、期間内で集計する場合にテーブルを跨ぎたい場合がほとんどだと思います。
その場合は _TABLE_SUFFIX を使って日付を YYYYMMDD で指定すると取得できます

2022/06/01-30 の集計

FROM
  `service_name.analytics_100000.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20220601'
  AND '20220630'

特定のPageViewを取る

今までのを組み合わせると2022/06/01-30のhome画面のPVはこのクエリで取得できます。

WITH
  screen_name AS (
  SELECT
    value.string_value
  FROM
    `service_name.analytics_100000.events_*`,
    UNNEST(event_params)
  WHERE
    _TABLE_SUFFIX BETWEEN '20220601' AND '20220630'
    AND key = 'screen_name'
    AND event_name = "page_view" )
SELECT
  count(*)
FROM
  screen_name
WHERE
  string_value = "home"

ユニークユーザにする

本来はevent_paramsの ga_session_id で判定する方が良いのですが、Flutter側でUserIdを設定していたらこちらの方がシンプルなクエリを書けます。

FirebaseAnalytics.instance.setUserId(id: userId);

あとは先程のクエリを DISTINCT user_id で絞るだけでユニークのPVが出せます。

WITH
  screen_name AS (
  SELECT
    DISTINCT user_id,
    value.string_value
  FROM
    `service_name.analytics_100000.events_*`,
    UNNEST(event_params)
  WHERE
    _TABLE_SUFFIX BETWEEN '20220601' AND '20220630'
    AND key = 'screen_name'
    AND event_name = "page_view" )
SELECT
  count(*)
FROM
  screen_name
WHERE
  string_value = "home"

まとめ

分析は大変な作業ですが、せっかくアプリを作ったらサボらずにエンジニアもログを集計して良いUI/UXを作れると良いですね。

June 30, 2022