> ## Documentation Index
> Fetch the complete documentation index at: https://docs.abconvert.io/llms.txt
> Use this file to discover all available pages before exploring further.

# BigQuery Export

> ABConvert copies your test data into a BigQuery dataset you own, once a day, under a stable schema, so your data team can join test results with the rest of your warehouse.

<Warning>
  **Pre-release.** This guide applies when the Integrations hub is enabled for your ABConvert account. Confirm the export is available for your store before granting dataset access.
</Warning>

<Note>
  Available on all ABConvert plans. See [Pricing](/configuration/pricing-plans) for the full matrix.
</Note>

ABConvert writes five tables into a BigQuery dataset in your own Google Cloud project. You own the rows, you pay for storage, and the data stays with you if you leave. The column names are a versioned contract: new columns and tables can be added, nothing is renamed or removed.

## When to use it

* You already report from BigQuery, Looker or a warehouse that reads BigQuery, and want test results next to the rest of the business.
* You want to measure a test against a metric ABConvert does not track, such as repeat purchase rate, margin after returns, or lifetime value.
* You want to keep the raw assignments and orders after a test ends, in a place you control.
* An agency or data team needs the numbers without an ABConvert login.

## When NOT to use it

* You want live results during a test. The export runs once a day. Use the [analytics page](/analytics/overview) instead.
* You want a one-off download of one test. Use the [order export](/api-reference/exports/create-an-export-job) instead, which produces a CSV.
* You want customer profiles or emails. The export carries no customer identity. Join `order_id` to your own Shopify order data for that.

## How it works

You create a dataset and grant one ABConvert service account write access to it. Each daily run reads new and updated rows for your store, loads an ABConvert-managed staging table in your dataset, and merges rows into the five destination tables on their full keys. Runs overlap for repair and catch-up, so the merge key prevents duplicate destination rows.

1. You create a dataset in your Google Cloud project. Any location works.
2. You grant the ABConvert export service account the role **BigQuery Data Editor** on that dataset.
3. You paste the project id and dataset id into ABConvert and click **Verify and connect**. ABConvert checks the grant and creates the five tables, empty.
4. ABConvert runs the export daily. It re-reads an overlap around the last successful watermark and can catch up after an interruption.
5. The first run queues a bounded history catch-up. Do not assume a dataset switch limits the catch-up to exactly 30 days; verify expected volume before replacing a destination.

## Use cases

* **Join test outcomes to warehouse metrics:** combine assigned visitors with your own refunds, margin, or repeat-purchase data.
* **Build a governed report:** give a data team one store-scoped schema for tests, assignments, exposures, orders, and order line items.
* **Audit attributed orders:** inspect attributed orders and line items without giving an agency access to your ABConvert admin.

## Setup

<Steps>
  <Step title="Create a dataset">
    In the Google Cloud console, open BigQuery and create a dataset in the project where you want the data. Choose any location. Note the project id and the dataset id.
  </Step>

  <Step title="Grant the ABConvert export account">
    Open the dataset, choose **Share** and then **Permissions**, and add a principal:

    ```
    abconvert-warehouse-export@abconvert-v1-prod.iam.gserviceaccount.com
    ```

    Give it the role **BigQuery Data Editor** on the dataset. Do not grant it at the project level; the dataset is all it needs.
  </Step>

  <Step title="Connect in ABConvert">
    In ABConvert, open **Integrations**, find **BigQuery** under Data Platforms, and click **Connect**. Paste the project id and dataset id, then click **Verify and connect**. The row reads **Waiting for first sync** until the first daily run lands, then **Connected** with the time of the last sync.
  </Step>
</Steps>

Nothing secret is stored in ABConvert. Removing the grant in your console disconnects the export; the row then reads **Action needed** with what to fix.

## The tables

All five tables carry `shop_domain` and `synced_at`. `test_id` is the numeric id you see in the ABConvert admin, as a string. `test_group_index` is the position of the test group in the test, starting at 0; names live in `abconvert_tests`, so renaming a test group never splits the history.

| Table                        | One row per                                   | Key                                                                      |
| ---------------------------- | --------------------------------------------- | ------------------------------------------------------------------------ |
| `abconvert_tests`            | test                                          | `shop_domain`, `test_id`                                                 |
| `abconvert_assignments`      | visitor and test, first assignment wins       | `shop_domain`, `visitor_id`, `test_id`                                   |
| `abconvert_exposures`        | session and test, first exposure wins         | `shop_domain`, `session_id`, `test_id`                                   |
| `abconvert_orders`           | order and test the order is attributed to     | `shop_domain`, `order_id`, `test_id`                                     |
| `abconvert_order_line_items` | order line, test, product and product variant | `shop_domain`, `order_id`, `test_id`, `product_id`, `product_variant_id` |

`abconvert_tests` holds the test name, type, status, start and end time, and a `test_groups` array with each test group's `index`, `name`, `is_control` flag and traffic `split`. It is refreshed in full every run, so a rename shows up the next day.

The assignments, orders and order line items tables carry the same audience columns: `device`, `country`, `market`, `visitor_type`, `traffic_platform`, `traffic_channel`, `referral_domain`, `landing_page`, the five `utm_*` fields, `gclid`, `fbclid`, `ttclid`, browser and OS name and version, `locale` and `timezone`.

Order money columns are `NUMERIC` in the order row's `currency`. Line-item rows do not carry a currency column, so join them to `abconvert_orders` on `shop_domain`, `order_id`, and `test_id` before comparing money. Cost and profit columns are null when the order has no cost data in ABConvert.

Inspect the destination schema before you hard-code field names. The public schema reference is still being finalized for this pre-release integration.

## Example queries

Conversion rate and revenue per visitor by test group. The query keeps every join store-scoped and reports revenue separately for each recorded currency:

```sql theme={null}
WITH assignments AS (
  SELECT shop_domain, test_id, test_group_index, visitor_id
  FROM `my-project.abconvert.abconvert_assignments`
  WHERE shop_domain = 'example.myshopify.com' AND test_id = '4021'
),
cohort_orders AS (
  SELECT a.shop_domain, a.test_id, a.test_group_index,
         o.visitor_id, o.order_id, o.currency, o.revenue
  FROM assignments a
  JOIN `my-project.abconvert.abconvert_orders` o
    ON o.shop_domain = a.shop_domain
   AND o.test_id = a.test_id
   AND o.visitor_id = a.visitor_id
),
visitors AS (
  SELECT shop_domain, test_id, test_group_index,
         COUNT(DISTINCT visitor_id) AS visitors
  FROM assignments
  GROUP BY 1, 2, 3
),
conversions AS (
  SELECT shop_domain, test_id, test_group_index,
         COUNT(DISTINCT visitor_id) AS purchasing_visitors,
         COUNT(DISTINCT order_id) AS orders
  FROM cohort_orders
  GROUP BY 1, 2, 3
),
revenue_by_currency AS (
  SELECT shop_domain, test_id, test_group_index, currency,
         SUM(revenue) AS revenue
  FROM cohort_orders
  GROUP BY 1, 2, 3, 4
)
SELECT v.shop_domain, v.test_id, t.test_name,
       v.test_group_index, g.name AS test_group,
       v.visitors, COALESCE(c.orders, 0) AS orders,
       SAFE_DIVIDE(COALESCE(c.purchasing_visitors, 0), v.visitors)
         AS visitor_conversion_rate,
       r.currency, r.revenue,
       SAFE_DIVIDE(r.revenue, v.visitors) AS revenue_per_visitor
FROM visitors v
JOIN `my-project.abconvert.abconvert_tests` t
  ON t.shop_domain = v.shop_domain AND t.test_id = v.test_id
JOIN UNNEST(t.test_groups) g ON g.index = v.test_group_index
LEFT JOIN conversions c
  ON c.shop_domain = v.shop_domain AND c.test_id = v.test_id
 AND c.test_group_index = v.test_group_index
LEFT JOIN revenue_by_currency r
  ON r.shop_domain = v.shop_domain AND r.test_id = v.test_id
 AND r.test_group_index = v.test_group_index
ORDER BY v.test_group_index, r.currency;
```

Join an ABConvert order to your own Shopify order table only when that table has a matching store boundary:

```sql theme={null}
SELECT o.shop_domain, o.test_id, o.test_group_index,
       o.order_id, o.currency, o.revenue, s.total_refunded
FROM `my-project.abconvert.abconvert_orders` o
JOIN `my-project.shopify.orders` s
  ON s.shop_domain = o.shop_domain
 AND CAST(s.id AS STRING) = o.order_id
WHERE o.shop_domain = 'example.myshopify.com' AND o.test_id = '4021';
```

## Common mistakes

* **Granting at the project level.** BigQuery Data Editor on the whole project gives the export account more than it needs. Grant it on the dataset only.
* **Treating `test_group_index` 0 as Control.** Control is the test group whose `is_control` is true in `abconvert_tests.test_groups`. It is usually index 0, but a merchant can reorder test groups.
* **Counting orders from the line items table.** `abconvert_order_line_items` has one row per product variant in an order. Count orders from `abconvert_orders`.
* **Expecting today's orders.** The export runs once a day with an overlap for repairs and catch-up. A new order usually appears after the next successful daily run. Source processing and catch-up can change the exact time.
* **Dropping ABConvert-managed staging tables.** ABConvert creates per-store staging tables and rewrites them every run. Leave them in place and do not depend on a fixed staging-table name.

## Things to know

* The export carries no email, no customer id and no IP address. Join `order_id` to your Shopify data when you need the customer.
* Each row keeps the currency it was recorded in. There is no currency conversion.
* Rows are merged, not appended. A re-run or a repair updates rows in place on the table key.
* Disconnecting stops the daily run. Your tables and rows stay in your dataset. Reconnecting the same dataset resumes from where it stopped.
* Changing to a different dataset starts a new destination catch-up. The current pre-release implementation can retain an earlier connection anchor, so confirm the expected history volume before you switch.

## FAQ

<Accordion title="Which BigQuery location should the dataset use?">
  Any location. The export loads data into your dataset and runs the merge in your dataset's own location, so EU and Asia datasets work the same as US ones.
</Accordion>

<Accordion title="How much does it cost me?">
  You pay BigQuery storage for the rows in your dataset and for any queries you run on them. ABConvert pays for reading its own warehouse and for the daily merge.
</Accordion>

<Accordion title="Can I get the data more often than daily?">
  Not yet. The export runs once a day. If you need results during a test, use the analytics page in ABConvert.
</Accordion>

<Accordion title="What happens if the grant is removed?">
  The next daily run fails for your store, the connection reads **Action needed** in ABConvert with the message to fix, and nothing else is written until you grant the account again and click **Verify and connect**.
</Accordion>

<Accordion title="Will a column ever be renamed or removed?">
  No. The schema is additive only: new columns and tables can appear, existing ones keep their names and types.
</Accordion>