Disclaimer: This website requires Please enable JavaScript in your browser settings for the best experience.

The availability of features may depend on your plan type. Contact your Customer Success Manager if you have any questions.

Dev GuideAPI Reference
Dev GuideAPI ReferenceUser GuideLegal TermsGitHubDev CommunityOptimizely AcademySubmit a ticketLog In
Dev Guide

Example BigQuery queries

Example queries to retrieve Optimizely Experimentation data from BigQuery.

📘

Note

This is a third-party integration and is not an Optimizely subprocessor. 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