[GA4] Export experiment data to BigQuery

BigQuery is a cloud data warehouse that lets you run highly performant queries on large datasets. By setting up the integration between BigQuery Export and your linked Google Analytics 4 property, you can export your experiment data from Google Analytics to BigQuery and use SQL-like syntax to query the data.

Prerequisites

Before you can export Optimize experiment data to BigQuery, you must have:

• A Google Analytics 4 account and property
• An Optimize account with a container that's linked to your Analytics property
• A BigQuery account that's linked to your Analytics property

About the exported data

Google Analytics exports your Optimize experiment data to BigQuery as user properties. The experiment ID is stored in the user_properties.value.string_value field and is associated with the value belongs_to_optimize_experiment_id in the user_properties.key field.

Find your experiment and variant IDs

You can query for an experiment using its experiment ID, which is stored in the user_properties.value.string_value field. You can find the experiment ID under Google Analytics in the Measurement and objectives section of your experiment.

An experiment has one or more variants. Each variant has a variant ID, which is a combination of the experiment ID and an index starting at 0. For example, if a web page has 6 variants, the variant IDs will be in the same as the order of variants in Optimize, starting with 0 for the original.

You can get data about a variant by adding the experiment ID and variant ID to the query. For example, you could add the following to a query to get data about a variant:

"belongs_to_optimize_experiment_id:<experiment_id> <variant_id>"

Sample queries

Find the device category and event count for each Optimize experiment.

SELECT
  UserProperties.key, UserProperties.value.string_value, device.category, count(*)
FROM YOUR_TABLE A, A.user_properties AS UserProperties
WHERE CONTAINS_SUBSTR(UserProperties.key, "belongs_to_optimize_experiment_id")
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3
;

Find the total revenue and event count for a given Optimize experiment using the experiment ID.

SELECT 
  UserProperties.key as experiment_id, 
  UserProperties.value.string_value as variant_id, 
  sum(ecommerce.purchase_revenue) as total_revenue, 
  count(*) as event_count 
FROM YOUR_TABLE A, A.user_properties AS UserProperties
WHERE UserProperties.key = "belongs_to_optimize_experiment_id:<experiment_id>"
GROUP BY 1, 2
ORDER BY 1, 2
;