Transforming a Flat BigQuery Table into a Nested GA4 Structure

[Taylor]

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.

[Leo]

In Google BigQuery, you’re on the right path to creating the nested structure you want for your data. The STRUCT function groups together the columns into a record, and ARRAY_AGG aggregates these records into an array. From reading your question, it seems that you want to give names to these fields inside the nested structure.

For this, you would use explicit field naming inside the STRUCT function. Here’s an updated version of your last SQL statement:

`sql
with test as (
select 123 cid, “sports” ec, “load” ea, “module” el union all
select 123,”marquee”,”load”,”home” union all
select 125,”bet_add”,”sports”,”football” union all
select 126, “bet_place”,”games”,”tennis”
)
select cid, array_agg(struct(ec as event_category,ea as event_action,el as event_label)) as events from test group by 1
`

Here, the fields inside each record in your array aren’t just ordered values anymore, they are named key-value pairs: {‘event_category’: ‘sports’, ‘event_action’: ‘load’, ‘event_label’: ‘module’}, and so forth. This should help get your data closer to the GA4 format you want. If there’s anything else you need, feel free to ask!