Export Predictions data to BigQuery

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

  1. Go to the Integrations page in the Firebase console.

  2. In the BigQuery card, click Link.

  3. 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 bq extract
  • 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

Pricing

There is no charge for exporting data from Predictions, and BigQuery provides generous free usage limits. For detailed information, refer to BigQuery pricing or the BigQuery sandbox.

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.