Firebase + BigQuery + Data Studio

Using BigQuery and Data Studio with Google Analytics App + Web

The most difficult part of migrating from Universal Analytics to Google Analytics App + Web properties is the reporting.  While App + Web properties are still in beta, they do not yet have the same built-in reports that Marketers and Analysts have come to expect from previous versions of Google Analytics, and it can be surprisingly difficult to replicate them. 

However, with a little practice, you can create just about any data visualization you want by using a combination of Firebase Analytics + BigQuery + Data Studio.  The goal of this post is to share tips and best practices for visualizing your Firebase Analytics data in BigQuery and Data Studio, and give you a few samples to help get started.

Setting Up the Data Flows

Before we begin you will need to enable the BigQuery integration to export your raw data from Firebase Analytics into a BigQuery table daily.  Once this is done you will be able to use BigQuery to run SQL queries on your raw data, or connect Data Studio to BigQuery (Google’s free data visualization tool) to create reports and dashboards.  Your data flow will work like this:

Getting Familiar with App + Web Data in BigQuery

The Google Analytics for Firebase data stored in BigQuery is not completely raw (some processing has been done on it), but it’s helpful to think of it as a raw and unsampled list of events.  Each day Google generates a table with data from the prior day, and each row in the table represents an event.

Now, if you’ve used BigQuery with GA360 you know what’s coming next: each event (or row) in your table is in a JSON format (this is true of Crashlytics data too).  Here is a simplified example of a single screen_view event in BigQuery:

event_dateevent_nameevent_params.keyevent_params.value.string_value
20200101screen_viewfirebase_screen_classproduct_detail_page_12345
firebase_previous_classsearch_results_page

You’ll notice that there is no “screen name” column, but there is a parameter called “firebase_screen_class” that stores the screen name.  There’s also another parameter called “firebase_previous_class” that stores the prior screen name.  These parameters and their values are stored in the columns “event_params.key” and “event_params.value.string_value”.  Here’s what the data actually looks like when you export it from BigQuery.

{
    "event_date": "20200101",
    "event_name": "screen_view",
    "event_params": [{
        "key": "firebase_previous_class",
        "value": {
            "string_value": "search_results_page"
        }
    }, {
        "key": "firebase_screen_class",
        "value": {
            "string_value": "product_detail_page_12345"
        }
    }]
}

We have multiple values for the “event_params…” columns because the data is actually an array.  If this concept is new to you then you will definitely need to read my post on How to Flatten a BigQuery Table with UNNEST.

Getting Familiar with App + Web Data in Data Studio

It was simple and intuitive to building dashboards in Data Studio with the old Google Analytics API because you had a pre-built list of metrics and dimensions, such as: Page, Page Title and Page Views.  With Google Analytics for Firebase your data is more raw, and you’ll have to transition to using events, parameter names and parameter values.  This can be frustrating at first, but when you get used to it you will see why this schema is so flexible.

As an example, here is how you would pull a list of pageviews by URL from your web stream data stored in Google Analytics for Firebase:

  • Dimension: “Event Param Value (string)”
  • Metric: “Event Count”
  • Filter: Event Name = “page_view”
  • Filter: Event Param Name = “page_location”

The resulting chart will look like the image below:


Resource Library

Data Studio Samples

I’ve compiled a series of reporting examples in a public Data Studio report that anyone can view and copy.

It can be difficult to explore the events that are being recorded and their parameters in the App + Web UI.  This may change with future updates, but for now the best way to do this is with Data Studio.

If you are new to using Data Studio with Firebase data, start with The Event & Parameter Explorer.

Any time I build a Data Studio dashboard using App + Web, I start by copying this report to help me drill down into each event to view the parameters and their values.  This helps you visualize how your data is organized, and also make sure that you spell event and parameter names correctly in your filters.

BigQuery + Data Studio Posts

I’ve compiled a list of posts below for reporting on App + Web data in BigQuery and Data Studio.  This list will grow over time. 

Like always, please contact me if you find any problems with these resources or if you would like to contribute new items for me to add.

Leave a Reply

Your email address will not be published.