Why do people use Firebase? For most of its users, it is a rapid and easy way to get the backend up and running for their mobile or web apps. When your app is already in use, Firebase can provide you with analytical data to understand how your awesome app goes – for example, learn where your users are engaged, how often they are converted, and much more. All these insights are available on the dashboard.
However, the Firebase Analytics dashboard cannot answer every question you may have. In this case, it’s worth exporting your Firebase data to BigQuery for more advanced analytics. Why BigQuery? Read on and find it out.
Firebase archive to BigQuery – a good match
BigQuery is a part of the Google Cloud Platform and it can be seamlessly integrated with Firebase with no third-party tools involved. Once you link Firebase and BigQuery, your data will stream into a separate intraday table in real time.
BigQuery allows you to store and manipulate significant amounts of data with high performance. If required, you can easily export some of your queries from BigQuery to spreadsheets such as Google Sheets or Excel.
Firebase data to BigQuery – steps to complete
Firebase data will be automatically exported to BigQuery once you link these two tools together. For this, complete the following steps:
- In the Firebase console, click the gear wheel icon and select Project Settings.
- Go to the Integrations tab, then find BigQuery, and click “Link“.
- Read about linking Firebase to BigQuery and hit “Next“.
- On the next page, you need to configure your Firebase to Google BigQuery integration. Toggle on the data entities for export and configure export settings (for Google Analytics and Crashlytics). Check out this section about the data you can export from Firebase.
Click “Link to BigQuery“, and that’s it!
Load Firebase backup to BigQuery – how it works
Are you expecting an “Export” button or any other method to get your Firebase data to BigQuery? You don’t have any of this since everything is automated.
Once you link Firebase and BigQuery, a dataset in the associated BigQuery project is created. The dataset name corresponds to the data entity you chose to export. Here is an example of the dataset for Firebase Cloud Messaging logs:
In the datasets, the intraday tables will then be created and populated with the raw event data for all of your Firebase apps.
How often BigQuery and Firebase events are synchronized
Firebase sets up daily exports of your data to BigQuery in real-time. If you have recently linked Firebase and BigQuery, it may take at least one day to view the copy of your Firebase data in BigQuery.
How to add existing Firebase tables to BigQuery
We recommend that you link your Firebase project with BigQuery from the outset. The reason is that the historical data you had in Firebase (except for Performance Monitoring data) prior to connecting to BigQuery is not available for export to BigQuery.
If, for any reason, you need to get this done, here is a workaround for exporting analytics data:
- Download your Firebase Analytics report as a CSV file.
- Upload CSV to BigQuery.
Firebase from BigQuery
When linking Firebase to BigQuery, you may have noticed the Import from BigQuery option.
This lets you enable the import of segments that you can identify outside Firebase. If you toggle it on, Firebase will create a separate dataset in BigQuery.
Here is how it looks in BigQuery:
Firebase will periodically synchronize with this BigQuery dataset to update the segments that are available in the Firebase console for targeting. So, if you remove some records from BigQuery, they will be removed from Firebase as well.
How to load data to BigQuery
This can be done in many ways, both manual and automated. We blogged about this in our BigQuery Tutorial.
The best option, however, is to set up an integration between BigQuery and your data source for automated data synchronization. This can be done with Coupler.io, a solution that lets you export data from different sources (Xero, Pipedrive, Google Sheets, etc.) to different destinations, including BigQuery, on a custom schedule.
Basically, you need to complete three steps:
- Set up your source.
- Set up your destination.
- Set up your schedule.
Check out all the available BigQuery integrations.
What data is available for export from Firebase to BigQuery?
You can export the following project data from Firebase to BigQuery:
- Analytics
- Crashlytics
- Cloud Messaging
- Performance Monitoring
- Predictions
- A/B Testing
Firebase Analytics to BigQuery
Firebase Analytics rests on Google Analytics, which captures data from up to 500 predefined and custom events. You can see this data – for example, performance of your campaigns across organic and paid channels – in the dashboard of the Firebase console. For a custom analysis, you can export Firebase Analytics reports to BigQuery.
By the way, read our blog post to learn how you can export Google Analytics to BigQuery.
Firebase Crashlytics to BigQuery
Firebase Crashlytics includes different crash data, such as crashes by day, top crashing devices, users facing a particular crash issue, and other data. Once the Crashlytics data is exported to BigQuery, you’ll find it in a separate dataset named firebase_crashlytics that contains tables for each app in your project.
Firebase Cloud Messaging to BigQuery
Firebase Cloud Messaging contains message delivery data, including notification funnel, messaging labels, and so on. Based on the cloud messaging data exported to BigQuery, you can count notification messages sent, calculate the percentage of delivered messages, track all events for a given message, and much more.
Firebase Performance Monitoring to BigQuery
Firebase Performance Monitoring data includes duration and screen traces, trace metrics, network requests, and so on. The export of performance monitoring data to BigQuery is only available for mobile apps. With this data, you can check the ratio of frozen frames, view the average app start latency breakdown by country, and perform many other manipulations.
Firebase A/B Testing to BigQuery
This includes all analytics data you obtained when conducting Remote Config experiments in Firebase – for example, metric (the one you chose) per user, percentage difference from baseline, probability of beating the baseline, etc. Once you link Firebase to BigQuery, you’ll get all this data in a separate dataset.
Firebase Predictions to BigQuery
Firebase Predictions contain two predefined predictions: churn (the probability that a user churns from your app) and spend (the probability that a user will make payments), as well as custom predictions. You can export the raw prediction data, which includes the raw score for every user and the set of labeled holdout data from Firebase to BigQuery.
Bonus: How to export Firebase active users to BigQuery
Firebase Analytics raw data is exported in intraday BigQuery tables. To extract the required metrics, such as active users for a specific period, you need to query these tables.
Firebase deems the active users to be the users who have engaged with an app and logged a user_engagement event. So, you can obtain this metric from the intraday data that goes from Firebase to BigQuery using the following query:
SELECT
COUNT(*) AS active_users
FROM (
SELECT
COALESCE(user_dim.user_id, user_dim.app_info.app_instance_id) AS user_id
FROM TABLE_DATE_RANGE([XXXXX.app_events_], TIMESTAMP('2021-01-24'), TIMESTAMP('2021-01-29'))
WHERE
DATE(event_dim.timestamp_micros) = '2021-01-25'
AND event_dim.name ='user_engagement'
GROUP BY
user_id )
*Replace the dates with your values.
Firebase Realtime DB to BigQuery
The Firebase Realtime Database is a cloud-hosted database that stores data in JSON format. It accumulates data across all your clients in real time. You can only export data from Firebase Realtime Database natively as a JSON file. However, with Coupler.io and Firebase Database REST API, you can export this data right to BigQuery.
Warning: With this method, all the values from your database will be exported in one row, whereas the names of child components will be specified as Field names.
For this, you’ll need to choose JSON Client as a source and set up the connection according to your requirements. The base JSON URL is displayed in your database:
You need to add .json to the end to use any URL within the Firebase Realtime Database as a REST endpoint. For example, to export the entire database, the JSON URL will look like this:
https://test-project-310805-default-rtdb.firebaseio.com/.json
Note: In our example, we are using the Firebase Realtime Database in the test mode, so our requests are free of authentication headers. In real life, you’ll need to authenticate your requests.
In the destination settings, you’ll need to choose BigQuery as a destination and connect your BigQuery account using the .json key file.
After that, enter the name of the BigQuery dataset and the table to export the Firebase Realtime Database to.
Click “Save and Run” and welcome your Firebase data in Bigquery. Here is how it looks in our example:
Ready-to-use Firebase to BigQuery integration – do you need one?
As a final word, we’d like to ask you a question: Would you like to have a ready-to-use integration between Firebase and BigQuery? Fill out this form if you would, and you can also specify other sources you may be interested in, for example, PubSub to BigQuery or anything else. Good luck with your data!
