Example BigQuery queries
Example queries to retrieve Optimizely Experimentation data from BigQuery.
Note
See Optimizely's Third-Party Add-Ons & Platform Integration Terms.
After BigQuery is configured to retrieve your Optimizely Experimentation data and you were given the correct permissions, you can run queries on this data in BigQuery. See the BigQuery query syntax reference guide.
Important
Queries can only be run from the United States.
Conversions
WITH
first_decisions AS (
SELECT *
FROM (
SELECT variation_id, visitor_id, timestamp
FROM `<optimizely_account_id>.decision`
WHERE experiment_id_hash = ABS(MOD(FARM_FINGERPRINT('12345678'), 4000))
AND experiment_id = '12345678'
AND timestamp BETWEEN '2023-06-20T15:43:54-04:00' AND '2024-02-12T09:54:32-05:00'
AND is_holdback = FALSE
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY visitor_id ORDER BY timestamp) = 1
),
attributed_conversions AS (
SELECT fd.variation_id, fd.visitor_id, c.revenue
FROM first_decisions AS fd
LEFT JOIN `<optimizely_account_id>.conversion` AS c
ON fd.visitor_id = c.visitor_id
AND c.timestamp >= fd.timestamp
AND c.timestamp BETWEEN '2023-06-20T15:43:54-04:00' AND '2024-02-12T09:54:32-05:00'
WHERE c.event_name = 'promo_click_banner'
)
Examples
Unique conversions per visitor for an event click_promo_banner, by variation
SELECT variation_id, COUNT(distinct visitor_id)
FROM attributed_conversions
GROUP BY variation_id
Total conversions for an event, click_promo_banner, by variation:
SELECT variation_id, COUNT(visitor_id)
FROM attributed_conversions
GROUP BY variation_id
Visitors and Revenue
WITH
first_decisions AS (
SELECT *
FROM (
SELECT variation_id, visitor_id, timestamp
FROM `<optimizely_account_id>.decision`
WHERE experiment_id_hash = ABS(MOD(FARM_FINGERPRINT('12345678'), 4000))
AND experiment_id = '12345678'
AND timestamp BETWEEN '2023-06-20T15:43:54-04:00' AND '2024-02-12T09:54:32-05:00'
AND is_holdback = FALSE
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY visitor_id ORDER BY timestamp) = 1
),
attributed_conversions AS (
SELECT fd.variation_id, fd.visitor_id, revenue
FROM first_decisions AS fd
LEFT JOIN `<optimizely_account_id>.conversion` AS c
ON fd.visitor_id = c.visitor_id
AND c.timestamp >= fd.timestamp
AND c.timestamp BETWEEN '2023-06-20T15:43:54-04:00' AND '2024-02-12T09:54:32-05:00'
)
Examples
Total visitors in the entire experiment
SELECT variation_id, COUNT(distinct visitor_id)
FROM attributed_conversions
GROUP BY variation_id
Total revenue in the entire experiment
SELECT variation_id, SUM(revenue)
FROM attributed_conversions
GROUP BY variation_id
Updated 2 months ago