How to Flatten a BigQuery Table with UNNEST

As mentioned in my post on Using BigQuery and Data Studio with GA4F, the Firebase Analytics data is stored as a JSON object in BigQuery.  Unfortunately this structure is not good for visualizing your data. 

The UNNEST operator allows you to run queries that flatten the data into the simple format required by your data visualization tools (such as Data Studio).

How Firebase Analytics Events Are Structured in BigQuery

A single event that you send to Google Analytics for Firebase will have multiple parameters, and each parameter is made up of a key-value pair (example: “content_author”: ”Jen”).  When you view this event in BigQuery, you’ll notice 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 example, let’s say that you have a news app, and your authors create content in three categories: “sports”, “politics”, or “fashion”.  Every time a user shares an article, you fire an event called “share” with the parameters “content_type” and “content_author”.  Here is a very simplified example of a single row in your BigQuery table:

How the UNNEST operator Works

UNNEST allows you to flatten the “event_params” column so that each item in the array creates a single row in the table with two new columns: “event_params.key” and “event_params.value”.  These results can then be CROSS JOINED with your table.  

So, this query…

SELECT * FROM `myTable` CROSS JOIN UNNEST(event_params) as authorTable

…will create two rows (one for each item in the event_params array).  Then when the unnested table CROSS JOINS with the existing table, the results look like this:

Now, if you would like to pull a count of “share” events by content_author, you simply write a query that uses a WHERE clause to identify the event name and parameter that we need.  Also, you can simplify this a bit by replacing “CROSS JOIN” with a comma.

SELECT 
  authorTable.value AS Author,
  count(1) AS eventCount
FROM `myTable`,
  UNNEST(event_params) as authorTable
WHERE event_name = 'share'
  AND authorTable.key='content_author' 
GROUP BY 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.

Leave a Reply

Your email address will not be published.