How to Flatten a BigQuery Table with UNNEST

As mentioned in my mail on Using BigQuery and Data Studio with GA4, the Google Analytics data is stored as a JSON object in BigQuery ( the same is true for Firebase Analytics data collected on a native app ). unfortunately this structure is not good for visualizing your data .
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 ) .


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 :

BigQuery sample

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 :
BigQuery sample #2
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 

BigQuery results

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 :

source :
Category : News


Related articles

Biggest Social Media Platforms as Per User Base

The web is the sacred lifeline of industrial development...

AniMixPlay Review – Is AniMixPlay Safe?

AniMixPlay is a website where you can watch anime...

TweakVip and Offroad Outlaws

There are several applications that make your life more...

The Benefits of Green Buildings

The term green building can be used to describe...

Pacman 30th Anniversary: New Google Doodle

A modified version of the Google doodle honoring Pacman...