Example BigQuery queries

6 min

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 configuredBigQuery is configured to retrieve your Optimizely Experimentation data and you have been granted the correct permissions, you can run queries on this data. See the BigQuery query syntax reference guide.

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 promo_click_banner, by variation.

SELECT variation_id, COUNT(distinct visitor_id)
FROM attributed_conversions
GROUP BY variation_id

Total conversions for an event, promo_click_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