BigQuery Export

​

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 instead.
• You want a one-off download of one test. Use the order export 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

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

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

​

The tables

​

Example queries

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;

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