GA4のRECORD型カラムを扱う時のポイント

この記事は何？

BigQuery Advent Calendar 2021 の 6日目の記事です。
BigQueryに転送したGoogleAnalytics4の生データを扱う際に、RECORD型の扱いに苦戦したので、その時の解決方法について書かせていただきます。

何に苦戦した？

GoogleAnalytics4では、イベントという単位でレコードが生成されており、イベント名(event_name)に対して、複数のパラメータ(event_params)が紐付く形式でレコードが保存されています。
具体的には、下記のようにpage_viewというイベント名に対して、RECORD型のevent_paramsがkey-value形式で値を持っています。

例えば、「page_titleに"SQL"という単語を含むページビューを集計したい」という場合は

SELECT 
  COUNT(event_name) AS count_page_view
FROM
  `project_id.dataset_id.table_id`
  , UNNEST(event_params)
WHERE
  event_name = 'page_view'
  AND key = 'page_title'
  AND value.string_value LIKE '%SQL%'

と書けば集計できます。

しかし、「page_titleに"SQL"という単語を含む、かつ流入元のsourceがgoogle、mediumがorganicのページビューを集計したい」という場合はどうでしょうか？

RECORD型は縦持ちのため、WHERE句の中でkeyやvalueに対して複数条件を書くことができず苦戦していました。

どう解決した？

結論から言うと、EXISTS関数を使うことで解決しました。

SELECT 
  COUNT(event_name) AS count_page_view
FROM
  `project_id.dataset_id.table_id`
WHERE
  event_name = 'page_view'
  AND EXISTS(SELECT * FROM UNNEST(event_params) WHERE key = 'page_title' AND value.string_value LIKE '%SQL%')
  AND EXISTS(SELECT * FROM UNNEST(event_params) WHERE key = 'source' AND value.string_value LIKE 'google')
  AND EXISTS(SELECT * FROM UNNEST(event_params) WHERE key = 'medium' AND value.string_value LIKE 'organic')

各EXISTS関数の中では、RECORD型であるevent_paramsをUNNEST関数によりフラット化した上で条件式を記述しています。

EXISTS関数の判定対象はあくまでFROM句で指定したテーブルのレコードのため、このように条件式を書くことでRECORD型のカラムの中身を対象に複数条件を記述することができました。