❤️‍🔥
Firebase+BigQueryでユーザプロパティの最新の値だけを抽出する

2025/12/14に公開
FirebaseのデータはBigQueryに接続可能であり、クエリによりWebコンソール以上に詳細な集計・調査ができる。
データはアプリ起動や画面表示、その他ユーザ定義のものなどのイベント単位でデータベースに保存され、各イベントにはユーザプロパティの情報も含まれる。
ユーザプロパティは上図の通りKey-Vaule型の値としてネストされており、かつすべてのイベントに付与される点が特徴。

そのため、例えば 「プロパティfoo_countが10件以上」の条件で抽出する場合、単に foo_count >= 10 としても、同じユーザが複数回ヒットする、最新のデータに基づかないといった問題が生じる。
本記事では、上記の問題への対応方法を紹介する。

 さっそくクエリFirebase Analyticsのイベントデータから、各ユーザーの最新のアクセス情報とユーザープロパティ foo_count を取得する例は以下の通り。
WITH latest_events AS (
  SELECT
    t.user_id,
    -- 1. ユーザーごとに最新のイベント1件を取得
    ARRAY_AGG(
      STRUCT(
        t.user_id AS user_id,
        -- 2. ユーザープロパティを抽出
        (
          SELECT
            COALESCE(up.value.int_value, 0)
          FROM
            UNNEST(t.user_properties) as up
          WHERE up.key = 'foo_count'
        ) AS foo_count,
        -- 3. イベントタイムスタンプをISO8601形式に変換
        FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%S%Ez', TIMESTAMP_MICROS(t.event_timestamp)) AS event_timestamp
      )
      ORDER BY TIMESTAMP_MICROS(t.event_timestamp) DESC
      LIMIT 1
    )[OFFSET(0)] AS latest_event
  -- 4.取得対象イベントの絞り込み
  FROM
    `[project].analytics_[id].events_202511*` AS t
  WHERE
    t.event_name = 'screen_view'
  GROUP BY
    t.user_id
)

SELECT
  latest_event.user_id,
  latest_event.foo_count,
  latest_event.event_timestamp AS last_access
FROM
  latest_events
ORDER BY
  latest_event.foo_count DESC
)

 全体の構造クエリは2つの部分で構成されている。

ARRAY_AGG()を使用してユーザーごとに最新のイベント（1件）を取得
1で取得した構造体から各フィールドを展開して結果を出力

 ポイント
 1. ユーザーごとに最新のイベント1件を取得集計分析関数 ARRAY_AGG() を使用し、 STRUCT() 内に必要な値を抽出して格納する。

timestampを使用した ORDER BY と LIMIT 1 の指定により、最新のイベント1件の情報のみ取得できる。

また、 ARRAY_AGG() の戻り値は配列型なので、必要な構造体=テーブルだけを取り出すために、 [OFFSET(0)] の指定が必要。
SELECT
  t.user_id,
  ARRAY_AGG(
    STRUCT(
    …
    )
    ORDER BY TIMESTAMP_MICROS(t.event_timestamp) DESC
    LIMIT 1
  )[OFFSET(0)] AS latest_event
…
GROUP BY
  t.user_id

 2. ユーザープロパティを抽出冒頭に記したように、ユーザープロパティは1つのイベントデータにKey-Vaule型の値としてネストされている。

そのためいったん UNNEST() で user_properties を展開し、WHERE句でKeyを指定して抽出する。

また、ユーザープロパティは型に応じて int_value, string_value, float_value, double_value に格納されるため、SELECT句で適当な箇所を参照する必要がある。

今回の例では整数型を int_value で抽出し、かつNULLの場合は0で代替している。
(SELECT
  COALESCE(up.value.int_value, 0)
  FROM
    UNNEST(t.user_properties) as up
  WHERE up.key = 'foo_count'
) AS foo_count

 3. イベントタイムスタンプをISO8601形式に変換イベントのタイムスタンプはUnix timeで表されている。

必須ではないが、後続の処理用にここでまとめて変換しておく。
FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%S%Ez', TIMESTAMP_MICROS(t.event_timestamp)) AS event_timestamp

 4.取得対象イベントの絞り込みBigQueryに取り込んだFirebaseのデータは、参照範囲を適切に設定しないとコスト（時間・費用の両方）が容易に増えていく。

今回の例では2025年11月のみを対象に以下の通り抽出する。（_TABLE_SUFFIX を使う方法もある）
また、WHERE句の指定により、画面遷移イベント(event_name = 'screen_view')のみ抽出している。必須ではないが、Firebaseから取り込んだテーブルはすべてのイベントが記録されるため、この指定によりパフォーマンスの向上が図れる。
FROM
  `[project].analytics_[id].events_202511*` AS t
WHERE
  t.event_name = 'screen_view'

 参考資料BigQuery で ROW_NUMBER(), RANK() を使うな！
BigQuery Best Practices: Performance Compute
Firebase AnalyticsをBigQueryで分析したいときに役立つテクニック
さっそくクエリ

全体の構造

ポイント

1. ユーザーごとに最新のイベント1件を取得

2. ユーザープロパティを抽出

3. イベントタイムスタンプをISO8601形式に変換

4.取得対象イベントの絞り込み

参考資料

Discussion
