How to use the UNNEST function in BigQuery to analyze event parameters in Analytics

Using BigQuery is a great way to generate some custom in-depth analysis of your Google Analytics data, but to really unlock that data, it helps to know a few tricks. Today, let’s talk about one of the most important ones: Using the function to analyze event parameters and user properties that you receive along with your Analytics data.

One of the trickier parts of working with Firebase data in BigQuery — and this applies not just to Analytics data but to Crashlytics data, too — is that your data is not organized in nice little rows and columns like this:

How you’re probably imagining your BigQuery data

Instead, your data consists of rows that maybe look a little more like JSON objects, where columns could contain a single bit of data, but they could also contain arrays, structs, or even arrays of structs. Maybe something a little more like this:

What your BigQuery data is really like

Working with this data can be weird at first, but once you learn a couple of tricks, it’s not so bad.

Let’s start by looking at our public sample data for our game of Flood-it. Feel free to follow along; this data is available for anybody to play with.

In particular, I’m interested in the event. This is the event that we record when the user successfully completes a quickplay level in the game. Here, let’s take a look at a few:

SELECT *
FROM `firebase-public-project.analytics_153293282.events_20180915`
WHERE event_name = "level_complete_quickplay"
LIMIT 10

If you run this, you’ll see a number of level complete events, along with a couple of other event parameters that seem interesting. We’ve got a parameter, which is recording the number of moves it took for a player to complete the level, and a parameter, which records the board size (, , or )

You might also notice that each of these parameters doesn’t have a single value column, but several: , , and .

Basically, you can think of this as kind of a struct that we use to store your parameter values, and in practice most of them end up being nil. But it’s a nice way of making sure we can take a number of different parameters with a bunch of different formats and store them in a consistent way.

So, let’s figure out the average user score just by looking at event parameters where is equal to . Seems pretty simple, right?

SELECT *
FROM `firebase-public-project.analytics_153293282.events_20180915`
WHERE event_name = "level_complete_quickplay"
AND event_params.key = "value"
LIMIT 10

Oh, dear. That’s… a really weird error message.

The problem here is that is essentially an array (actually in BigQuery parlance it’s a “repeated record”, but you can think of it as an array). So while it might contain several rows that themselves have a field, it doesn’t have one itself.

This is where the function comes in. It basically lets you take elements in an array and expand each one of these individual elements. You can then join your original row against each unnested element to add them to your table.

To use a simpler example, let’s imagine we have a table full of spaceships. And is an array, much like was in our analytics tables.

Right now, if I wanted to find spaceships that contain a crew member named Zoe, I couldn’t do that with a line like this.

SELECT * FROM `spaceships` WHERE crew = "Zoe"

But if I were to write something like this…

SELECT * FROM `spaceships` CROSS JOIN UNNEST(crew) as crew_member

What BigQuery will do is take every individual member in my crew array, and add it on as a new column in my row called It will repeat my original row as needed to accompany each new value. Kinda like in this nifty animation:

Once I’ve done that, it’s easy to select spaceships with crew members named Zoe.

SELECT * FROM `spaceships` 
CROSS JOIN UNNEST(crew) as crew_member
WHERE crew_member = "Zoe"

You’ll find that in practice, though, most BigQuery developers will replace the with a comma, like so:

SELECT * FROM `spaceships`,
UNNEST(crew) as crew_member
WHERE crew_member = "Zoe"

It still does the same thing, it just looks cooler. (BigQuery developers are all about looking cool.)

So going back to our example, we essentially have an array (or repeated record) of . So if I were to say:

SELECT * FROM `my_analtyics_table`,
UNNEST(event_params) as param

…what BigQuery will do is take each individual event parameter and expand them out into a new column called , and repeat the original row as needed. Like this:

You’ll notice that itself is still a nested object. But it’s a single record, not a repeating one. (Again, kinda like a struct.) So we can query for things like or . Specifically, we can now look just for rows where is equal to .

Since, in practice, it’s a little weird to have the full repeated object alongside each unnested parameter, I usually drop it and just look at the param object instead.

So with all that in mind, let’s go back to the task of analyzing the average score for users who complete a quickplay level. I can now grab just those events along with the unnested parameter.

SELECT event_name, param
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"

And then, it’s really easy to grab the actual value of the value parameter by looking at .

SELECT event_name, param.value.int_value AS score
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"

From there, I can perform interesting tasks like calculating the mean, getting some quantiles, and figuring out the standard deviation…

SELECT AVG(param.value.int_value) AS average, 
APPROX_QUANTILES(param.value.int_value, 2) AS quantiles,
STDDEV(param.value.int_value) AS stddev
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"

Hey, maybe while I’m at it, let’s make a quick histogram of all the values!

SELECT param.value.int_value AS score, COUNT(1) AS count
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"
GROUP BY 1
ORDER BY 1

So… this is interesting. There’s a pretty big peak around 21 or 22 moves… but if you keep looking, it seems like there’s also a couple of smaller peaks around 29 moves and in the 34–36 range.

The reason for that is the other parameter I was talking about — the parameter. While the vast majority of games are being played on a small board, there’s a number of games being played on medium and large boards too, and those probably account for those smaller peaks.

In fact, we can kinda see that by looking at both the board and the value parameters for our event.

SELECT param
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "board")

Sure enough, it seems like the “value” parameter is higher when it follows a of or .

But how can we actually prove this out in BigQuery? Right now, making this analysis is kinda difficult because we have different values in different rows.

Now, there is a way we can bunch these rows together. If we add back the (essentially, a unique ID assigned to each app instance) and to each of our columns, we can then group together parameters for the same event based on those two values.

SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
FROM (
SELECT user_pseudo_id, event_timestamp, param
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "board")
)
GROUP BY user_pseudo_id, event_timestamp

And then we can analyze score by board type…

SELECT ANY_VALUE(board_type) as board, AVG(score) as average_score
FROM (
SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
FROM (
SELECT user_pseudo_id, event_timestamp, param
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "board")
)
GROUP BY user_pseudo_id, event_timestamp
)
GROUP BY board_type

But this seems like a pretty awkward way of doing things. Wouldn’t it be better if we could find a way of more easily getting multiple values in the same row? Well it turns out you can, and we’ll find out how… in my next post! (Woo! Cliffhanger ending!!)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Todd Kerpelman

Former Game Designer. Current Developer Advocate at Google. Dad. Often sleep deprived.