---
title: Example BigQuery queries
slug: experimentation-strategy/data-docs/example-bigquery-queries
createdAt: 2026-08-05T20:34:49.000Z
---

:::hint{type="info"}
This is a third-party integration and is not an Optimizely subprocessor. See Optimizely's [Third-Party Add-Ons & Platform Integration Terms](https://www.optimizely.com/legal/third-party-add-ons-platform-integration-terms/).
:::

After [BigQuery is configured](docId\:pi0IvP-yJ9IGwBv_k4MXP) 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](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax).

:::hint{type="warning"}
**Important**

Queries can only be run from the United States.
:::

## Conversions

```sql
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.

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

Total conversions for an event, `promo_click_banner`, by variation.

```sql
SELECT variation_id, COUNT(visitor_id)
FROM attributed_conversions
GROUP BY variation_id
```

## Visitors and revenue

```sql
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.

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

Total revenue in the entire experiment.

```sql
SELECT variation_id, SUM(revenue)
FROM attributed_conversions
GROUP BY variation_id
```