Firebase Analytics is an incredibly powerful tool to understand how users use your mobile or web app. The past year I have been using it to draw insights and help prioritise and improve various functions in our team.

Firebase Analytics is mostly good for collecting data, to get any real insights, you need to ask the right questions. I do this using BigQuery and Metabase. I have written more about Metabase in a separate article.

This post is meant to be more of a living document, a book of recipes for how to do different things with BigQuery and Firebase analytics specifically. I hope it can serve you as it does me.

Types of queries

Events week-by-week

Getting week by week is really good for generating graphs and Week-over-Week measurements.

As an example, here is a graph comparing application users with users viewing items weekly:

Example of users and item view events week-over-week

In this script, we are converting the timestamp of the Google Analytics event to a datetime. The datetime is truncated using date_trunc and week() with a weekday to change which day the week starts with. This way we can define a week as something else than Mon-Sun which can be helpful as sometimes, it can take a day or two to get data from Firebase for example, into Google Analytics.

Specifically, we wanted to check the data on Mondays which meant that measuring data from Sun-Sat made sure the weekly data had all been logged properly before we drilled into it.

The data from this specific query can look something like this:

ANDROIDSaturday June 26th236
IOSSaturday June 26th103
IOSSaturday July 3rd303
ANDROIDSaturday July 3rd256
Weekly grouped data by truncating event_timestamp with WEEK(SATURDAY)

Events by user

Many times it’s worth calculating how many events each user does. Usually, this is a type of engagement measure. For a service like Netflix, it would be many many episodes or movies a user on average watches per given period.

It can help track trends in usage or, by applying segmentation, the easiest, as in this case, the platform. In the example below, I’m checking how many times on average a user does a search, using a custom event. This is useful and can be used to show, like in the graph below, how the activity is different between app types.

Example of showing the average number of search events per unique users

Comparing average order prices per platform

The Google Analytics view_item event comes with a whole load of different properties out-of-the-box and you can always add more to there if you want. Here I show how to get out the average value and how many unique views total we have for items.