BigQuery の UNNEST 関数を使って GoogleAnalytics のデータを取得する方法

BigQuery で分析するとき、配列データ・ネスト型の扱いに悩んだことはありませんか？

Google Analytics 4 (GA4) のデータなどでみられるように、BigQuery では配列データが格納できます。

配列データの中身を取り出すには UNNEST 関数を使う必要があり、意図したデータを取得するのに少しコツがいります。

本記事では GA4 のデータを例に、配列データを行と列で展開する方法についてご紹介します。

GA4 のデータ構造

GA4 のデータで UNNEST 関数を使用する必要があるのは、 event_params と user_properties のデータを取得するときです。

両者の key それぞれに対して、value が入ります。

イベントの対象となった URL などを取得する場合に、key と value を指定して取得します。

行に展開 ～FROM 句で UNNEST～

行に展開するときは、FROM 句で UNNEST 関数を使います。

ひとつのイベントに対して key の数だけ行に展開されるため、イベントは重複します。

データを集計する際は考慮する必要です。

SELECT
  event_date,
  event_name,
  ep.*,
FROM
  `{project_id}.analytics_xxxxxx.events_yyyyMMdd`,
  UNNEST(event_params) AS ep

列に展開 ～SELECT 句で UNNEST～

横に展開するときは、SELECT 文で UNNEST 関数を使います。

ひとつのイベントに対して 指定した key の value がカラムに追加されるので、イベントは重複しません。

SELECT
  event_date,
  event_name,
  (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key='page_title') AS page_title,
  (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key='page_location') AS page_location
FROM
  `{project_id}.analytics_xxxxxx.events_yyyyMMdd`

まとめ

GA4 を例に、配列データの展開方法についてご紹介しました。

分析時のお役にたてば幸いです。