• Google Analytics 4

  Transforming a Flat BigQuery Table into a Nested GA4 Structure

  Posted by Mathew on 31 May 2022 at 6:46 am

  I managed to nest event category values, but I hit a snag when attempting to create key-value pairs for event action and event label. It’s like trying to match the array structure in GA4 raw sessions, but I just can’t quite get there.

  You can see how far I’ve got: https://prnt.sc/3FhZgd7IOmVV
  Ultimately, I’m aiming to make the event action label look like this: https://prnt.sc/pT5AOhL7q9S8

  Here’s an example: I’ve created a flat table, but how do I give it the shape I’m after?

  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,ea,el)) as events from test group by 1
  

  Take another look: I’ve built another flat table just like the last one. But how do I reshape it into the format I want?

  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,ea,el)) as events from test group by 1
  

  Leo replied 1 year, 4 months ago 3 Members · 2 Replies
• 2 Replies

• 

  Taylor

  Member

  14 February 2023 at 2:26 am

  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

  Member

  9 May 2023 at 2:01 am

  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!

Log In to Reply