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.
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
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:
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 ;