-
From what I understand, you’re trying to structure your data to match the Google Analytics 4 (GA4) raw sessions event action format. Currently, with your query, you’re creating an array of structs with three fields (ec, ea, el). This isn’t matching the complex nested structure you’re aiming for which seems to be key:value pairs for each event action and label.
To shape your data like in GA4, you will need to adjust the structure of your data to have separate key-value pair arrays for “event_params.key” and “event_params.value”. This structuring is not straightforward as the method to create this structure in Bigquery is not built-in.
In Google BigQuery language (Standard SQL), you may need to use a more complex query to create the same structure. The STRUCT and ARRAY functions are certainly useful for creating nested data, but for creating the specific schema required by GA4, more complex querying may be needed that involves key-value pairing and nested subqueries.
You may need to consult Google BigQuery documentation on complex data types and nested and repeated data or consider involving a developer who is familiar with structuring data as per the GA schema.
