5 Ways Looker makes Firebase Analytics Data in BigQuery Useful

[LookerExperts]

This content, written by Ernesto Ongaro, was initially posted in Looker Blog on Nov 22, 2019. The content is subject to limited support.

The best apps today use a data-driven approach to make them successful. Knowing which segments of users stick around and get the most value (and in turn give you the most revenue) shouldn’t be a guessing game, it should be an exact science.

Millions of modern apps are written in Google’s backend application development platform, Firebase. Firebase contains over 18 tools to help application developers build, improve, and grow any app. If you’ve used apps from Venmo, The Economist, or The New York Times, then you’ve used Firebase.

There are a few moving pieces here, so let’s introduce them. Cloud Firestore is a database to store your application events, where they can then be analyzed in Firebase Analytics. Firebase Analytics offers some great out of the box functionality, but a lot of growing companies run out of runway with it, and their analytical questions get capped. That’s where BigQuery comes in. Anyone can export their Cloud Firestore application events to BigQuery with just a few clicks.

This means you have an event ingestion system built right into Firebase. Normally this would take months of engineering work, but if you’ve chosen the Google Firebase to BigQuery route, it happens much more easily. The downside is that querying your events data in BigQuery requires a lot of specialized SQL knowledge. Let’s explore how Looker can help simplify this process.

1) Nested data

Firebase data stored in BigQuery is highly nested. Every row contains an event name, like “item_equipped”, and then, in a set of key-value pairs called event_params, you’ll see properties for that event, like item=sword. This keeps the table from becoming infinitely wide, but at the same time, makes it challenging to query.

To understand how many swords people have equipped in a game, we’d have to write a query like:

SELECT event_name, param.value.string_value
FROM `firebase.events_20191215`,
UNNEST(event_params) AS param
WHERE event_name = "item_equipped"
AND param.key = “sword”

Luckily this is made easy when you let Looker’s modeling layer, LookML write the queries for you. There’s no need to write every query by hand — we have you covered.

What this means for you is that basic questions can be asked with a few clicks rather than after writing lines of code. This difference allows for deeper questions like “How many people equipped the sword and later went on to make a purchase?”

2) Partitioning

BigQuery is a scanning database, and you’re charged for the amount of data scanned. To help keep costs down (and speed up queries), every query of your events data will need to take Firebase’s partitioning scheme into account. Compounded with the baggage of UNNESTing, this means you’ll need to spend a lot of time setting up the boilerplate for your queries.

Without Looker, you have to think about partitioning with every query and will have to write out date ranges manually, like so:

FROM `firebase.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20191215' and '20191222'

But with Looker, simply use the built-in date selectors, and Looker magically translates them to the appropriate date ranges — no manual work required!

3) Common definitions

Anyone who writes SQL queries has a note file on their desktop with commonly used tasks, such as excluding test users, limiting to the last 30 days, calculating retention, or lifetime value. As your company scales, you might have several SQL proficient people with their own ways of getting answers. Eventually, this creates inefficiencies and chaos because definitions start to vary, and the data isn’t trusted.

Looker’s SQL-based language called LookML helps codify all those notes into something everyone can agree on. Common tasks like calculating D7 retention or ROAS (return on ad spend) can become codified with LookML, checked into a version-controlled (Git) system, and all of a sudden, anyone can query it with a guarantee that the definitions are the correct ones.

There are pesky parts of querying Firebase data like converting timestamps from unix_micros dealing with timezones that are made super easy with Looker.

4) Sessions

When someone opens your app, does a series of actions, and then closes the app on their phone, it allows us to ask specific questions about the intent of that user. If that person opens your app, did they achieve the task they were hoping to? How long do people play your game at a given time? These questions require sessionization of the data (grouping events that occur together for each user).

Unfortunately, sessions are not included in Firebase analytics data in BigQuery. However, with Looker, there is a solution. When you use Looker with Firebase, every row in the database will have a unique session ID tied to it. Problem solved!

5) Deeper questions for non-technical users

In addition to the simplification of queries described in the first part of this article, the additional value Looker provides is the ability for various users without technical experience to ask deeper data questions.

• User acquisition/marketing can ask what campaigns are bringing a positive ROAS across any dimension like source, geography, time, device, or even first session length.
• Product managers can ask what features/content is being used in the application and how it affects player retention and monetization.
• Management can get a single source of trusted truth about the application, not just behavioral data.

So if you’re stuck UNNESTing queries, dealing with data partitioning resulting in chaotic data, or thinking about launching an app with Firebase, give Looker a try and make use of our , which makes all the above actions easier to realize.