Example BigQuery queries
Example queries to retrieve Optimizely Experimentation data from BigQuery.
📘
NoteThis is a third-party integration and is not an Optimizely subprocessor. See Optimizely'sThird-Party Add-Ons & Platform Integration Terms.
AfterBigQuery 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 theBigQuery query syntax reference guide.
🚧
ImportantQueries can only be run from the United States.
Conversions
WITHfirst_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 eventclick_promo_banner, by variation.
SELECT variation_id, COUNT(distinct visitor_id)FROM attributed_conversionsGROUP BY variation_idTotal conversions for an event,click_promo_banner, by variation.
SELECT variation_id, COUNT(visitor_id)FROM attributed_conversionsGROUP BY variation_idVisitors and revenue
WITHfirst_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_conversionsGROUP BY variation_idTotal revenue in the entire experiment.
SELECT variation_id, SUM(revenue)FROM attributed_conversionsGROUP BY variation_idUpdated 2 months ago