How to get Shopify data into BigQuery (and what to model first)

Shopify’s built-in reports answer “what sold yesterday?” well. They struggle with the questions that drive budget: what a customer acquired in March is worth by September, which channel brings repeat buyers, how refunds change margin. Those need your Shopify data next to your GA4 and ad-spend data in a warehouse. BigQuery is a natural fit for brands already in the Google ecosystem. Here is how to get the data there and what to build first.

What are the options for moving Shopify data into BigQuery?

There are three families of approach. Most brands should use the first and add the third only for specific needs.

Approach	Examples	Best for	Watch out for
Managed connector	BigQuery Data Transfer Service for Shopify, Fivetran, Airbyte	Most brands; fastest to a working warehouse	Schema is the vendor’s, not yours; cost and refresh frequency depend on the vendor
Custom pipeline on Admin GraphQL bulk operations	Python or Cloud Run job writing JSONL to BigQuery	Exact control of fields, schedules and cost	You own retries, schema changes, backfills and monitoring
Event-driven	Webhooks to Pub/Sub, Shopify Flow “Send HTTP request”	Near-real-time triggers and alerts	Delivery and ordering are not guaranteed; not a source of truth

Managed connectors

Google’s BigQuery Data Transfer Service now has a Shopify connector. It is in Preview, requires a custom app in your Shopify store, covers GraphQL-based resources such as Orders, Products, Customers and Fulfillments, and — per Google’s docs — “with every transfer run, the Shopify connector transfers all available data from Shopify into BigQuery.” Google states there is no cost to transfer Shopify data while it is in Preview. That makes it a reasonable first try, with the usual caveat that Preview features can change.

Third-party connectors such as Fivetran and Airbyte are the mature alternatives. Airbyte’s Shopify source, for example, supports full refresh and incremental sync and uses GraphQL bulk operations for several high-volume streams. Compare connectors on three things: whether they include refunds and line-item detail, how they handle deletes and edits, and what they cost at your order volume.

Admin GraphQL API bulk operations

If you need control, write your own pipeline on bulk operations. You submit one GraphQL query, Shopify runs it asynchronously, and you download the result as JSONL, where “each line in the file is a node object returned in a connection.” Nested records such as line items appear on their own lines with a __parentId pointing to their order, which maps cleanly onto separate warehouse tables.

Facts to design around, from Shopify’s documentation:

• From API version 2026-01, a shop can run up to five concurrent bulk operations; earlier versions allow one of each type at a time.
• Download URLs expire after one week, and operations must complete within 10 days.
• A bulk query can contain at most five connections, nested at most two levels deep.
• Subscribe to the bulk_operations/finish webhook rather than polling.
• read_orders only covers orders from the last 60 days. Request read_all_orders for full history.

A minimal incremental orders extract looks like this:

mutation {
  bulkOperationRunQuery(
    query: """
    {
      orders(query: "updated_at:>='2026-09-01T00:00:00Z'") {
        edges { node {
          id name createdAt processedAt cancelledAt test sourceName
          customer { id }
          currentSubtotalPriceSet { shopMoney { amount currencyCode } }
          totalTaxSet { shopMoney { amount } }
          totalShippingPriceSet { shopMoney { amount } }
          refunds { id createdAt totalRefundedSet { shopMoney { amount } } }
          lineItems { edges { node {
            id sku quantity
            originalUnitPriceSet { shopMoney { amount } }
          } } }
        } }
      }
    }
    """
  ) {
    bulkOperation { id status }
    userErrors { field message }
  }
}

Filter on updated_at, not created_at, so edits, cancellations and refunds to older orders flow in. Then upsert into BigQuery on the order ID.

Webhooks and Shopify Flow

Webhooks deliver order and refund events within seconds, to HTTPS endpoints, Google Pub/Sub or Amazon EventBridge. Shopify is explicit that “webhook delivery isn’t always guaranteed” and that it “doesn’t guarantee ordering within a topic,” and recommends periodic reconciliation jobs. Use the X-Shopify-Webhook-Id header to drop duplicates.

Shopify Flow can send data out with its Send HTTP Request action, available on the Grow, Advanced and Plus plans. It is useful for alerts and simple workflows, not as a warehouse loader.

How do you get GA4 data into BigQuery?

Link the GA4 property to BigQuery in GA4 admin. Do this first, before anything else in this article, because the export is not retroactive.

What to know from Google’s documentation:

• Daily export writes events_YYYYMMDD tables; streaming export writes events_intraday_YYYYMMDD for near-real-time data.
• Standard properties can export up to 1 million events per day in the daily export.
• Daily tables follow the GA4 property’s time zone and can be updated for up to 2 calendar days plus today with late events.
• Changing the property time zone can cause discrepancies or a skipped daily export.
• Purchases carry ecommerce.transaction_id and ecommerce.purchase_revenue; event_timestamp is in UTC microseconds.

The transaction_id is your bridge between GA4 sessions and Shopify orders, which is why it matters that it is set reliably — see why GA4 and Shopify never show the same revenue.

For ad spend, BigQuery Data Transfer Service also has connectors for Google Ads (daily transfers with a configurable refresh window) and Facebook Ads. Other platforms usually come through a managed connector.

What should you model first?

Resist modeling everything. Five tables answer most early questions.

Table	Grain	Key columns
orders	One row per order	order_id, order_name, customer_id, created_at, source_name, currency, net_value, tax, shipping, cancelled_at, is_test
order_lines	One row per line item	line_id, order_id, sku, quantity, unit_price, discount
customers	One row per customer	customer_id, first_order_at, first_order_id, acquisition_channel
refunds	One row per refund	refund_id, order_id, refunded_at, amount
ad_spend_daily	Day × platform × campaign	date, platform, campaign_id, spend, currency

Rules that save pain later:

• Store money in the shop currency and keep presentment currency in a separate column for international stores.
• Convert timestamps to the store’s time zone once, in the staging layer, and use that everywhere.
• Exclude test orders and decide explicitly how cancelled orders are treated.
• Keep refunds as their own events. Finance usually wants revenue net of refunds on the refund date; marketing usually wants it attributed back to the order date. You need both.
• Derive first_order_at from orders, not from a customer-created field, so customers with accounts but no orders don’t distort cohorts.

What does a daily revenue table look like in SQL?

This produces gross and refund-adjusted revenue per day, with new versus returning customers.

CREATE OR REPLACE TABLE analytics.daily_revenue AS
WITH orders AS (
  SELECT
    o.order_id,
    DATE(o.created_at, 'America/New_York') AS order_date,
    DATE(c.first_order_at, 'America/New_York') AS first_order_date,
    o.net_value
  FROM shopify.orders AS o
  LEFT JOIN shopify.customers AS c USING (customer_id)
  WHERE NOT o.is_test AND o.cancelled_at IS NULL
),
order_days AS (
  SELECT
    order_date AS date,
    COUNT(*) AS orders,
    SUM(net_value) AS net_sales,
    SUM(IF(order_date = first_order_date, net_value, 0)) AS new_customer_sales,
    SUM(IF(order_date > first_order_date, net_value, 0)) AS returning_customer_sales
  FROM orders
  GROUP BY date
),
refund_days AS (
  SELECT
    DATE(refunded_at, 'America/New_York') AS date,
    SUM(amount) AS refunds_processed
  FROM shopify.refunds
  GROUP BY date
)
SELECT
  date,
  COALESCE(o.orders, 0) AS orders,
  COALESCE(o.net_sales, 0) AS net_sales,
  COALESCE(o.new_customer_sales, 0) AS new_customer_sales,
  COALESCE(o.returning_customer_sales, 0) AS returning_customer_sales,
  COALESCE(r.refunds_processed, 0) AS refunds_processed,
  COALESCE(o.net_sales, 0) - COALESCE(r.refunds_processed, 0) AS net_sales_after_refunds
FROM order_days AS o
FULL OUTER JOIN refund_days AS r USING (date)
ORDER BY date;

The full outer join keeps days that have refunds but no orders. Refunds are booked on the day they were processed, which matches how Shopify’s finance reports treat sales reversals. If your refund amounts include shipping and tax, separate them before subtracting from net sales.

What does a first-order cohort table look like?

Cohorts group customers by the month of their first order and track cumulative revenue per customer over time. This is the table behind LTV, payback and retention questions.

CREATE OR REPLACE TABLE analytics.first_order_cohorts AS
WITH first_orders AS (
  SELECT
    customer_id,
    DATE_TRUNC(DATE(MIN(created_at), 'America/New_York'), MONTH) AS cohort_month
  FROM shopify.orders
  WHERE NOT is_test AND cancelled_at IS NULL AND customer_id IS NOT NULL
  GROUP BY customer_id
),
activity AS (
  SELECT
    f.cohort_month,
    DATE_DIFF(DATE_TRUNC(DATE(o.created_at, 'America/New_York'), MONTH),
              f.cohort_month, MONTH) AS months_since_first,
    o.customer_id,
    o.net_value
  FROM shopify.orders AS o
  JOIN first_orders AS f USING (customer_id)
  WHERE NOT o.is_test AND o.cancelled_at IS NULL
),
cohort_size AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM first_orders
  GROUP BY cohort_month
)
SELECT
  a.cohort_month,
  a.months_since_first,
  s.customers AS cohort_customers,
  COUNT(DISTINCT a.customer_id) AS active_customers,
  SAFE_DIVIDE(COUNT(DISTINCT a.customer_id), s.customers) AS retention_rate,
  SUM(a.net_value) AS revenue,
  SAFE_DIVIDE(
    SUM(SUM(a.net_value)) OVER (PARTITION BY a.cohort_month ORDER BY a.months_since_first),
    s.customers
  ) AS cumulative_revenue_per_customer
FROM activity AS a
JOIN cohort_size AS s USING (cohort_month)
GROUP BY a.cohort_month, a.months_since_first, s.customers
ORDER BY a.cohort_month, a.months_since_first;

Join cohort_month to ad spend for the same month and you have a first payback view: cumulative revenue per customer against what it cost to acquire them. Add gross margin per line item when you have product costs, because payback on revenue flatters every cohort.

When is it worth bringing in help?

A working connector is a day’s job; a warehouse your finance and marketing teams both trust takes decisions about definitions, refunds, time zones and identity that are easy to get subtly wrong. If you want that built once and documented, Asabisoft’s Shopify to BigQuery pipeline work starts with exactly these five tables.