hamburger

主に日記

FirebaseAnalyticsのログデータを分解して扱いやすい形に変換する

FIrebaseAnalyticsのデータはコンソールのダッシュボード上確認できる。
ただし見れるデータは限られるため、詳細なデータを活用したい場合はBigQueryに保存されている生データを確認する必要がある。

2020/04/09現在、データは最新の日付のものが events_intraday_yyyyMMdd 、過去の日付のものが events_yyyyMMdd の形式で保存される仕様となっている(※いつからか日付ごとにパーテーションがきられるようになった)。

このテーブルの中に格納されているデータの形式が特殊で、1カラム内に配列で複数の値が入っているものもある(BigQueryで提供されている配列型)。

おそらくこういう形で格納されているはず。これで1レコード

event_date event_timestamp event_name event_params.key event_params.value.string_value event_params.value.int_value
20200408 1586311773017025 screen_view firebase_previous_id null 99999999
firebase_previous_class SampleActivity null
firebase_screen_class Sample2Activity null

これを以下のようにすれば通常の正規化されたテーブルの様に扱えるので、集計しやすくなる。

event_date event_timestamp event_name firebase_previous_id firebase_previous_class firebase_screen_class
20200408 1586311773017025 screen_view 99999999 SampleActivity Sample2Activity

配列型は unnest句を使えばよいという情報は多くヒットするのだが、ほとんどがfrom句でunnestせよという内容でその方法では別レコードに分かれてしまう。個別に利用するのならそれでも問題ないのかもしれないが、例えば複数のSQLから参照するための前処理として加工しようとしている場合はレコードが一緒の方が別カラムを組み合わせて検索条件を設定しやすく都合が良い。

前置きが長くなってしまったが、以下のようなクエリを書くことでフラットなレコードに変換できる。ちょっと冗長な気もするので別の良い方法が見つかったら再度記事を書きたい。

SELECT
  event_date,
  event_timestamp,
  event_name,
  (
  SELECT
    unnest_event_params.value.int_value
  FROM
    UNNEST(event_params) unnest_event_params
  WHERE
    unnest_event_params.key = "firebase_previous_id") AS firebase_previous_id,
  (
  SELECT
    unnest_event_params.value.string_value
  FROM
    UNNEST(event_params) unnest_event_params
  WHERE
    unnest_event_params.key = "firebase_previous_class") AS firebase_previous_class,
  (
  SELECT
    unnest_event_params.value.string_value
  FROM
    UNNEST(event_params) unnest_event_params
  WHERE
    unnest_event_params.key = "firebase_screen_class") AS firebase_screen_class,
FROM
  `テーブル名`;

ビッグデータ分析・活用のためのSQLレシピ

ビッグデータ分析・活用のためのSQLレシピ