We are exporting GA4 (Google Analytics 4) data from Big Query via a built-in Microsoft connector. Everything is coming through, but the event_params is in a very unusual JSON format. I have unpivoted numerous other arrays from different sources with no issue using the OPENJSON function and CROSS APPLY but this one is beating me so far.
I have searched around the internet and so far I have only found documentation on how to do it in Big Query, which doesn't work for me with the built in connectors.
My goal is to pivot each of the params and values with non-null values into their own columns, such as below, although there are about 10 custom columns I will have in the end.
| event_date | event_timestamp | page_location | ga_session number | etc |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| First | row | | | |
| Second | row | | | |
Below is a sample of my data
| event_date | event_timestamp | event_params |
| ------------ | ------------ | ------------ |
| 20221022 | 1666490454564810 | {"v":[{"v":{"f":[{"v":"page_location"},{"v":{"f":[{"v":"https://welcome.mypage.com/"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_number"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_title"},{"v":{"f":[{"v":"Welcome Hub"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"engaged_session_event"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_id"},{"v":{"f":[{"v":null},{"v":"1666490454"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ignore_referrer"},{"v":{"f":[{"v":"true"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_referrer"},{"v":{"f":[{"v":"https://login.mypage.com/callback?code=rgE7OZ3rLlJb8NcqfzOF370PhKojttNbKOddEKxg3p2aL&state=RDlGdC1vQWRlR0VHTHQ3M1p3TlVvS0hnVTVSbXA4UVM0NGtNR2ljbkl1Vg%3D%3D"},{"v":null},{"v":null},{"v":null}]}}]}}]} |
| 20221022 | 1666490454564810 | {"v":[{"v":{"f":[{"v":"engaged_session_event"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_referrer"},{"v":{"f":[{"v":"https://login.mypage.com/callback?code=rgE7OZ3rLlJb8NcqfzOF370PhKojttNbKOddEKxg3p2aL&state=RDlGdC1vQWRlR0VHTHQ3M1p3TlVvS0hnVTVSbXA4UVM0NGtNR2ljbkl1Vg%3D%3D"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ignore_referrer"},{"v":{"f":[{"v":"true"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_title"},{"v":{"f":[{"v":"Welcome Hub"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_number"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_id"},{"v":{"f":[{"v":null},{"v":"1666490454"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"session_engaged"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_location"},{"v":{"f":[{"v":"https://welcome.mypage.com/"},{"v":null},{"v":null},{"v":null}]}}]}}]} |