Table of Contents
The UNNEST hustler allows you to run queries that flatten the datum into the simpleton format required by your data visual image tools ( such as Data Studio ) .
Contents
How Google Analytics 4 Events Are Structured in BigQuery
A one event that you send to Google Analytics 4 will have multiple parameters, and each parameter is made up of a key-value copulate ( example : “ content_author ” : ” Jen ” ). When you view this event in BigQuery, you ’ ll detect a column called “ event_params ” that contains an array of all the parameters set with this event ( BigQuery calls this a “ repeated record ”, but I like to use the word “ array ” ) .
As an exemplar, let ’ s say that you have a news app, and your authors create message in three categories : “ sports ”, “ politics ”, or “ fashion ”. Every time a exploiter shares an article, you fire an event called “ contribution ” with the parameters “ content_type ” and “ content_author ”. here is a identical simplify case of a single rowing in your BigQuery postpone :
How the UNNEST operator Works
UNNEST allows you to flatten the “ event_params ” column so that each item in the array creates a individual row in the board with two new columns : “ event_params.key ” and “ event_params.value ”. These results can then be CROSS JOINED with your mesa .
thus, this query…
1 blue-ribbon * FROM ` myTable ` hybrid join UNNEST ( event_params ) as authorTable …will create two rows ( one for each item in the event_params array ). then when the unnested mesa CROSS JOINS with the existing table, the results look like this :
now, if you would like to pull a consider of “ partake ” events by content_author, you simply write a question that uses a WHERE clause to identify the event list and parameter that we need. besides, you can simplify this a moment by replacing “ CROSS JOIN ” with a comma .
1 choose 2 authorTable. value AS writer, 3 count ( 1 ) AS eventCount 4 FROM ` myTable `, 5 UNNEST ( event_params ) as authorTable 6 WHERE event_name = 'share ' 7 AND authorTable. key = 'content_author ' 8 group aside 1 
Want to See It In Action?
Check out my posts on how UNNEST allows you to view multiple parameters on the same event, or how UNNEST allows you to view a parameter across multiple events .
Other Resources for BigQuery and GA4
here are a few early posts that I ’ ve created for using BigQuery with data from Google Analytics 4 :
