You can export your Firebase Predictions data into BigQuery for further analysis. BigQuery allows you to analyze the data using BigQuery SQL, export it to another cloud provider, and even use the data for your custom ML models.
Enable BigQuery export
Go to the Integrations page in the Firebase console.
In the BigQuery card, click Link.
Follow the on-screen instructions to enable BigQuery.
When you link your project to BiqQuery:
Firebase exports a copy of your existing data to BigQuery.
Firebase sets up regular syncs of your data from your Firebase project to BigQuery.
By default, all apps in your project are linked to BigQuery and any apps that you later add to the project are automatically linked to BigQuery. You can manage which apps send data.
To deactivate BigQuery export, unlink your project in the Firebase console.
What data is exported to BigQuery?
For each app in the project, a data export creates a table that includes the Predictions data for each user. If the Predictions record was used as part of the evaluation set, the evaluation label and score are also exported. The evaluation label can be either 0 if the prediction was negative or 1 if the prediction was positive.
What is holdout/evaluation data?
Not all of the data is used directly for training. As is typical for supervised learning tasks, Firebase Predictions sets aside 20% of the data as holdout data and uses only the remaining 80% of the data to train the model. Then, to evaluate the model's performance, predictions are generated for the users in the holdout set, based on the data in the training window, and compared to the actual outcomes for each user, based on the labels generated from the label window.
What can you do with the exported data?
BigQuery export contains the raw prediction data at every risk profile along with the score and labeled holdout data.
Access raw Predictions data
In addition to the computed prediction result at every risk profile, you can also get the raw score for every user as well as the set of labeled holdout data. You can use this data to evaluate the performance of your own Predictions or to create user groups beyond the three risk tolerance profiles have defined in the UI.
Transfer data into BigQuery
Using BigQuery Data Transfer services, you can automatically transfer data from Google Analytics for Firebase, Crashlytics, Google Marketing Platform, Google Ads, and YouTube into BigQuery on a scheduled and fully managed basis. You can then use the Predictions data to perform sophisticated analysis like seeing which acquisition channel is resulting in the largest number of users predicted to spend.
Predictions export contains the instance_id field that identifies a unique instance of the app install. You can use this value with other data.
Take your Predictions everywhere
Since Predictions integrates with Firebase Remote Config, Firebase Cloud Messaging, and Firebase A/B Testing, you might want to access your Predictions results server-side or push them to another third-party solution. There is currently no charge for exporting data.
You can export your data by:
- Using the BigQuery web UI
- Running the CLI command
- Submitting an extract job via the API or client libraries.
Get all the prediction data for CSV export
The following sample query flattens the Predictions data which can be exported as CSV or directly into Google Sheets.
SELECT FORMAT_TIMESTAMP("%Y%m%d",u.prediction_time) AS prediction_date, u.instance_id, u.app_id, u.project_id, u.prediction_time, p.prediction_name, p.score, p.id, p.evaluation_info.label, p.evaluation_info.score AS label_score, r.risk_profile_name, r.prediction_result FROM Table_Name u, u.predictions p, p.risk_profiles r ORDER BY u.prediction_time ASC LIMIT 10
Get all the prediction details used for model evaluation (holdout)
SELECT FORMAT_TIMESTAMP("%Y%m%d",u.prediction_time) AS prediction_date, u.instance_id, u.app_id, u.project_id, u.prediction_time, p.prediction_name, p.score, p.id, p.evaluation_info.label, p.evaluation_info.score AS label_score, r.risk_profile_name, r.prediction_result FROM Table_Name u, u.predictions p, p.risk_profiles r WHERE p.evaluation_info.label is not null ORDER BY u.prediction_time ASC LIMIT 10
Combine Predictions data with Analytics data for more powerful analytics
You can enable BigQuery export for Google Analytics for Firebase, then join Analytics events data with Predictions for even more powerful analysis.
Analyze which country has the highest predicted churn
SELECT predictions.prediction_name, risk_profiles.risk_profile_name, events.geo.country, COUNT(1) AS count FROM Analytics_Table_Name events, Predictions_Table_Name predictions_data, predictions_data.predictions predictions, predictions.risk_profiles risk_profiles WHERE predictions.prediction_name = 'churn' AND risk_profiles.prediction_result = 'POSITIVE' AND predictions_data.user_id = events.user_id GROUP BY predictions.prediction_name, risk_profiles.risk_profile_name, events.geo.country ORDER BY risk_profiles.risk_profile_name
Use the acquisition channel data in Analytics to see which channels had the most users predicted to spend
SELECT predictions.prediction_name, risk_profiles.prediction_result, risk_profiles.risk_profile_name, events.traffic_source.source, COUNT(1) as user_count FROM Predictions_Table_Name predictions_data, predictions_data.predictions predictions, predictions_data.predictions predictions, predictions.risk_profiles risk_profiles WHERE predictions.prediction_name = 'spend' AND risk_profiles.prediction_result = 'POSITIVE' AND predictions_data.user_id = events.user_id GROUP BY predictions.prediction_name, risk_profiles.prediction_result, risk_profiles.risk_profile_name, events.traffic_source.source ORDER BY user_count desc, risk_profiles.risk_profile_name