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

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 paramFROM `firebase-public-project.analytics_153293282.events_20180915`,UNNEST(event_params) AS paramWHERE 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 board of M or L.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 pseudo_user_id (essentially, a unique ID assigned to each app instance) and event_timestamp 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_typeFROM ( 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_timestampAnd then we can analyze score by board type…SELECT ANY_VALUE(board_type) as board, AVG(score) as average_scoreFROM ( 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_typeBut 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!!). More details

Leave a Reply