如何在BigQuery內呈現類似舊版GA的事件表格

如何在BigQuery內呈現類似舊版GA(UA/GA3)的事件表格

Raw data：

Code：

SELECT DISTINCT
event_date,
event_timestamp,
user_pseudo_id,
MAX (CASE WHEN params_new.key = 'screen_name' THEN params_new.value.string_value ELSE NULL END) OVER (PARTITION BY event_timestamp, user_pseudo_id) AS `screen_name`,
MAX (CASE WHEN params_new.key = 'type' THEN params_new.value.string_value ELSE NULL END) OVER (PARTITION BY event_timestamp, user_pseudo_id) AS `type`,
MAX (CASE WHEN params_new.key = 'item' THEN params_new.value.string_value ELSE NULL END) OVER (PARTITION BY event_timestamp, user_pseudo_id) AS `item`,
traffic_source.source,

FROM 
`my-project-12345.analytics_9876543.events_*`  AS original
LEFT JOIN UNNEST (event_params) AS params_new
WHERE REGEXP_CONTAINS(key, 'screen|type|item')

Processed Data：

參考資料：

Marketing Watch House - GA4 Big Query Tutorials 播放清單

創建時間：2023.02.24