BigQuery and Data Studio

BigQuery and Data Studio

Data — the new oil…

Your app has generated significant amount of data, which needs attention now…How to do so ?….

PART ONE (Firebase events to BigQuery)…

This post focuses on how to analyze the Firebase events generated by your app. We assume that your app has Firebase analytics events.

  1. Go to Firebase console and Select Events
Firebase console Events.
Firebase console Events.

Note: These events are from a demo app, production app will have ample amount of events.

2. Click on Link To BigQuery. (bottom in the above screenshot)

Pricing for BigQuery is 

BigQuery Pricing….
BigQuery Pricing….

Any point of time, you want to unlink, we can still do that by

. Go to your Firebase project ->Project Overview and Project Settings

Project Overview
Project Overview

. Click on Integrations Tab and Select BigQuery.

. You will see an option of Unlink from BigQuery (click that for unlinking).

Unlink from BigQuery….
Unlink from BigQuery….

3. Go to BigQuery console to verify whether the datasets have been created or not. 

BigQuery Console..
BigQuery Console..

In the LHS, you can see analytics_xxxxxxxxx (which is the dataset created from Firebase events)..

Under that, there are 2 tables, 

events_ : Which is the permanent table. Having all the data from the day you linked to BigQuery.

events_intraday_ : Which is the temporary table. This captures today’s events and at the end of the day it transfers to the events_ table.

Note : In case you dont see these two tables, meaning that you have linked to BigQuery today itself. You need to wait for at least 1 day, for the data to appear.


4. No need to define schema for the tables, as BigQuery takes care of that.

You can view the schema as : 

Schema in BigQuery
Schema in BigQuery

For preview of the data, you can select Preview.


5. You can run specific queries over your analytics data, by writing them in Query Editor (see step3 screenshot).

There are 2 types of Queries supported in BigQuery :

Legacy SQL and Standard SQL. By default Standard is checked.

For switching between two, Go to More and Select Query Settings

Switching between SQL formats…
Switching between SQL formats…

Under SQL Dialect : 

Standard VS Legacy..
Standard VS Legacy..

Tip : For writing queries in BigQuery, I would highly recommend watching this :

BigQuery Tips…

PART TWO (BigQuery to DataStudio)…

There are some tools already supporting BigQuery, for visualizing data in an interactive manner, 

  1. Tableau
  2. QlikView
  3. Google’s Data Studio

We will go with DataStudio….

There are various templates available inside DataStudio such as :

Data Studio…
Data Studio…

On the Left Hand Side, you can switch between Featured / Marketing templates / Community for exploring different templates…

You can even create your own report.

For the demonstration, 

  1. Go to Data Studio and select All templates from the Right hand side.
Google Data Studio.
Google Data Studio.

2. Click on Firebase Events Report.

This report is made by Product Managers at Google Analytics and Data Studio

3. This is the sample report looks like 

Firebase Events Report.
Firebase Events Report.

NOTE : The data inside this template is not your app data

4. Linking our data, click on USE TEMPLATE (above screenshot top right).

This will open a pop-up like below…

Link to our data source…(BigQuery and Data Studio)
Link to our data source…(BigQuery and Data Studio)

5. Click on the dropdown of New Data Source andselect Create New Data Source 

New Data Source…(BigQuery and Data Studio)
New Data Source…(BigQuery and Data Studio)

6. Choose BigQuery

BigQuery and Data Studio…
BigQuery and Data Studio…

7. Choose your project -> DataSet -> Table -> Configuration

Connect to data source…
Connect to data source…

On the top right corner, you see the Connect button, click it

8. Click on Add to Report

Add to Report…
Add to Report…

9. Finally, you will see the pop-up again and Click Copy Report..

Copy Report…
Copy Report…

Congrats, your data is linked…..(analyze the data visually now)

For editing this report, click on the Edit option…

Editing report in Data Studio…