BigQueryにおけるGA4データの扱いづらさを解決する
背景 Google AnalyticsをBigQueryに連携してSQLで分析しようという時、event_params や user_properties が 1 2 3 4 5 6 7 8 9 10 11 ARRAY< STRUCT< key STRING, value STRUCT< string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64 > > > という大変扱い辛い型のため、以下のようにサブクエリを多用したSQLを度々書いていく必要があります。 1 2 3 4 5 6 7 8 9 SELECT TIMESTAMP_MICROS(event_timestamp) event_timestamp, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title, ... FROM `analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' このSQLの結果をデータマートにすれば解決かと思いきや、event_paramsにプロパティを追加する度にデータマートをメンテナンスする必要があり運用が非常に面倒です。...