Dev GuideAPI Reference
Dev GuideAPI ReferenceUser GuideGitHubNuGetDev CommunitySubmit a ticketLog In
GitHubNuGetDev CommunitySubmit a ticket

Example BigQuery queries

Example queries to retrieve Optimizely Experimentation data from BigQuery.

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, MIN(timestamp) AS 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
    GROUP BY variation_id, visitor_id
  )
  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
  JOIN `<optimizely_account_id>.conversion` AS c
    ON fd.visitor_id = c.visitor_id AND c.timestamp >= fd.timestamp
  WHERE c.timestamp between '2023-06-20T15:43:54-04:00' AND '2024-02-12T09:54:32-05:00'
  AND c.event_name = 'click_promo_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, MIN(timestamp) AS 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
    GROUP BY variation_id, visitor_id
  )
  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
  JOIN `<optimizely_account_id>.conversion` AS c
    ON fd.visitor_id = c.visitor_id AND c.timestamp >= fd.timestamp
  WHERE 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