In addition to viewing A/B Testing experiment data in the
Firebase console, you can inspect and analyze experiment data in
BigQuery. While A/B Testing does not have a separate
BigQuery table, experiment and variant memberships are stored on every
Google Analytics event within the Analytics event tables.
The user properties that contain experiment information are of the form
userProperty.key like "firebase_exp_%"
or
userProperty.key =
"firebase_exp_01"
where
01
is the experiment ID, and
userProperty.value.string_value
contains the (zero-based) index of the
experiment variant.
You can use these experiment user properties to extract experiment data.
This gives you the power to slice your experiment results in many different
ways and independently verify the results of A/B Testing.
To get started, complete the following as described in this guide:
- Enable BigQuery export for Google Analytics in the Firebase
console
- Access A/B Testing data using BigQuery
- Explore example queries
Enable BigQuery export for Google Analytics in the Firebase console
If you're on the Spark plan, you can use the
BigQuery sandbox
to
access BigQuery at no cost, subject to
Sandbox limits
.
See
Pricing and the BigQuery sandbox
for more information.
First, make sure that you're exporting your Analytics data to
BigQuery:
- Open the
Integrations
tab,
which you can access using
settings
>
Project settings
in the
Firebase console
.
- If you're already using BigQuery with other Firebase services,
click
Manage
. Otherwise, click
Link
.
- Review
About Linking Firebase to BigQuery
, then click
Next
.
- In the
Configure integration
section, enable the
Google Analytics
toggle.
Select a region and choose export settings.
Click
Link to BigQuery
.
Depending on how you chose to export data, it may take up to a day for the
tables to become available. For more information about exporting project data to
BigQuery, see
Export project data to BigQuery
.
Access A/B Testing data in BigQuery
Before querying for data for a specific experiment, you'll want to obtain some
or all of the following to use in your query:
- Experiment ID:
You can obtain this from the URL of the
Experiment overview
page. For example, if your URL looks like
https://console.firebase.google.com/project/my_firebase_project/config/experiment/results/25
,
the experiment ID is
25
.
- Google Analytics property ID
: This is your 9-digit
Google Analytics property ID. You can find this within
Google Analytics; it also appears in BigQuery when you expand
your project name to show the name of your Google Analytics event
table (
project_name.analytics_000000000.events
).
- Experiment date:
To compose a faster and more efficient query, it's
good practice to limit your queries to the Google Analytics daily
event table partitions that contain your experiment data—tables
identified with a
YYYYMMDD
suffix. So, if your experiment ran from
February 2, 2024 through May 2, 2024, you'd specify a
_TABLE_SUFFIX between
'20240202' AND '20240502'
. For an example, see
Select a specific experiment's values
.
- Event names:
Typically, these correspond with your
goal metrics
that you configured in the experiment. For example,
in_app_purchase
events,
ad_impression
, or
user_retention
events.
After you gather the information you need to generate your query:
- Open
BigQuery
in the Google Cloud console.
- Select your project, then select
Create SQL query
.
- Add your query. For example queries to run, see
Explore example queries
.
- Click
Run
.
Query experiment data using the Firebase console's auto-generated query
If you're using the Blaze plan, the
Experiment overview
page provides a
sample query that returns the experiment name, variants, event names, and the
number of events for the experiment you're viewing.
To obtain and run the auto-generated query:
- From the Firebase console, open
A/B Testing
and select the A/B Testing experiment you want to query to open the
Experiment overview
.
- From the Options menu, beneath
BigQuery integration
, select
Query experiment data
. This opens your project in BigQuery
within the Google Cloud console console and provides a basic query you can
use to query your experiment data.
The following example shows a generated query for an experiment with
three variants (including the baseline) named "Winter welcome experiment."
It returns the active experiment name, variant name, unique event, and
event count for each event. Note that the query builder doesn't specify
your project name in the table name, as it opens directly within your project.
/*
This query is auto-generated by Firebase A/B Testing for your
experiment "Winter welcome experiment".
It demonstrates how you can get event counts for all Analytics
events logged by each variant of this experiment's population.
*/
SELECT
'Winter welcome experiment' AS experimentName,
CASE userProperty.value.string_value
WHEN '0' THEN 'Baseline'
WHEN '1' THEN 'Welcome message (1)'
WHEN '2' THEN 'Welcome message (2)'
END AS experimentVariant,
event_name AS eventName,
COUNT(*) AS count
FROM
`analytics_000000000.events_*`,
UNNEST(user_properties) AS userProperty
WHERE
(_TABLE_SUFFIX BETWEEN '20240202' AND '20240502')
AND userProperty.key = 'firebase_exp_25'
GROUP BY
experimentVariant, eventName
For additional query examples, proceed to
Explore example queries
.
Explore example queries
The following sections provide examples of queries you can use to extract
A/B Testing experiment data from Google Analytics event tables.
Extract purchase and experiment standard deviation values from all experiments
You can use experiment results data to independently verify
Firebase A/B Testing results. The following BigQuery SQL statement
extracts experiment
variants, the number of unique users in each variant, and sums total revenue
from
in_app_purchase
and
ecommerce_purchase
events, and standard deviations
for all experiments within the time range specified as the
_TABLE_SUFFIX
begin
and end dates. You can use the data you obtain from this query with a
statistical significance generator for one-tailed t-tests to verify that the
results Firebase provides match your own analysis.
For more information about how A/B Testing calculates inference, see
Interpret test results
.
/*
This query returns all experiment variants, number of unique users,
the average USD spent per user, and the standard deviation for all
experiments within the date range specified for _TABLE_SUFFIX.
*/
SELECT
experimentNumber,
experimentVariant,
COUNT(*) AS unique_users,
AVG(usd_value) AS usd_value_per_user,
STDDEV(usd_value) AS std_dev
FROM
(
SELECT
userProperty.key AS experimentNumber,
userProperty.value.string_value AS experimentVariant,
user_pseudo_id,
SUM(
CASE
WHEN event_name IN ('in_app_purchase', 'ecommerce_purchase')
THEN event_value_in_usd
ELSE 0
END) AS usd_value
FROM `
PROJECT_NAME
.analytics_
ANALYTICS_ID
.events_*`
CROSS JOIN UNNEST(user_properties) AS userProperty
WHERE
userProperty.key LIKE 'firebase_exp_%'
AND event_name IN ('in_app_purchase', 'ecommerce_purchase')
AND (_TABLE_SUFFIX BETWEEN '
YYYYMMDD
' AND '
YYYMMDD
')
GROUP BY 1, 2, 3
)
GROUP BY 1, 2
ORDER BY 1, 2;
Select a specific experiment's values
The following example query illustrates how to obtain data for a specific
experiment in BigQuery. This sample query returns the experiment name,
variant names (including Baseline), event names, and event counts.
SELECT
'
EXPERIMENT_NAME
' AS experimentName,
CASE userProperty.value.string_value
WHEN '0' THEN 'Baseline'
WHEN '1' THEN '
VARIANT_1_NAME
'
WHEN '2' THEN '
VARIANT_2_NAME
'
END AS experimentVariant,
event_name AS eventName,
COUNT(*) AS count
FROM
`analytics_
ANALYTICS_PROPERTY
.events_*`,
UNNEST(user_properties) AS userProperty
WHERE
(_TABLE_SUFFIX BETWEEN '
YYYMMDD
' AND '
YYYMMDD
')
AND userProperty.key = 'firebase_exp_
EXPERIMENT_NUMBER
'
GROUP BY
experimentVariant, eventName