Shopify to BigQuery: how to load store data your team can actually model

How do you connect Shopify to BigQuery?

You connect Shopify to BigQuery by reading the Admin API and writing the results into BigQuery tables, because there is no native BigQuery destination inside Shopify. Something has to sit in the middle: a managed connector, a scheduled script against the API, or a custom app. The managed route is the Shopify to BigQuery integration, which owns the pagination, the watermark, the staging table, the MERGE, and the retries.

The Admin API comes in two flavors, GraphQL and REST, and both paginate by cursor rather than by page number. You request a batch, get an opaque cursor back, and pass it to the next call. Throttling is cost based: Shopify runs a leaky bucket, so each query spends points from a bucket that refills at a steady rate, and a GraphQL query that asks for deep nested data costs more than a shallow one. That matters the moment you backfill several years of orders, because you are making thousands of sequential calls and each one can get throttled or time out.

Practically, the connection has three parts. First, an access token from a custom app with read scopes on orders, products, and customers. Second, a BigQuery landing spot: a project, a dataset, and a service account that can create tables and run MERGE. Third, the transfer itself, which pages through each object, lands raw or typed rows in a staging table, and merges into the target. If you also send store data to accounting, the pattern behind Shopify to QuickBooks is the same shape pointed at a different destination.

How do you load Shopify orders into BigQuery incrementally?

Load Shopify orders incrementally by keeping a watermark of the highest updated_at you have seen, requesting only orders changed since then, and MERGE-ing the result into BigQuery keyed on the order id. The updated_at filter is the important choice. Filtering on created_at feels simpler, but it only catches brand new orders and silently skips every order that was edited, refunded, or cancelled after the fact.

This is the trap that bites most first attempts. An order created on Monday can be edited on Tuesday, partially refunded on Wednesday, and cancelled on Friday. A naive nightly dump keyed on creation date grabs Monday's version once and never looks back, so your warehouse slowly drifts away from Shopify. Watching updated_at means every one of those changes bumps the order back into your next pull.

The load itself follows the same shape every time. Land the batch in a staging table, then collapse duplicates before you merge, because a single window can carry several versions of the same order:

Step	What runs	Why it matters
Dedupe the batch	ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC), keep rank 1	Stops a multi-version window from creating duplicate order rows
MERGE on id	MERGE INTO fct_order t USING staging s ON t.id = s.id	Makes retries idempotent, so a re-run after a timeout produces the same state
Advance the watermark	Store the max updated_at from the batch	Next run resumes exactly where this one stopped

Overlap the window by a few minutes on each run so an order committed out of clock order is not skipped. Because the MERGE is keyed on the id and idempotent, re-reading a handful of orders costs nothing and buys you correctness.

How do Shopify fields map to BigQuery types?

Map Shopify ids to STRING, money strings to NUMERIC, timestamps to TIMESTAMP in UTC, and nested blocks like the shipping address or tax lines to the JSON type so you can query keys without flattening first. Start with the object layout, which splits facts from dimensions, then the field types.

Shopify object	BigQuery table	Notes
orders	fct_order	One row per order. Partition by order date, cluster by customer and status
line_items	fct_order_line	Belongs to an order. Grain is one row per product per order
refunds	fct_refund	Separate objects, not a flag on the order. Store as negative money
customers	dim_customer	Dimension. Join key for margin, cohorts, and LTV
products	dim_product	Dimension. Attach COGS here to make margin possible

Shopify field / type	BigQuery type	Notes
total_price (string, shop currency)	NUMERIC	Cast the text to NUMERIC. Never store money as FLOAT64
presentment amount (string)	NUMERIC	What the buyer paid in their currency. Keep it in its own column, do not mix with shop currency
currency / presentment_currency	STRING	ISO codes. They tell a reader which amount column applies
created_at / updated_at	TIMESTAMP	Store as UTC. updated_at is your incremental watermark
id (order, customer, product)	STRING	The natural MERGE key. Keep it as text even though it looks numeric
shipping_address / tax_lines	JSON	Nested objects. Store raw and query keys without a reload
financial_status / fulfillment_status	STRING	New values appear over time, so validate downstream rather than constraining

The money field deserves a second look, because it is a real correctness bug and not a style preference. Shopify returns amounts as strings, and it returns them twice: once in your shop currency and once in the buyer's presentment currency. If you cast the presentment amount into the same column as the shop amount, a report that sums that column adds euros to dollars to yen and produces a number that means nothing. Pick one currency per column, cast to NUMERIC, and keep the other in its own labeled field.

How do you calculate true margin and LTV from Shopify in BigQuery?

Calculate true margin by blending order revenue with cost of goods sold, payment fees, and ad spend, then subtracting refunds to get net. Shopify tells you what a customer paid, but it does not know what the product cost you to buy, what your processor kept, or what you spent on ads to win the sale. Those three numbers live outside Shopify, and margin is meaningless until you join them in.

Attach COGS at the dim_product grain so every line item can look up its unit cost. Bring payment fees in from your processor: if your store runs on Stripe, the fee and net settle on the balance transaction, and the mapping is spelled out in Stripe to BigQuery, which lands the fee column right next to your order revenue. Ad spend joins by date and channel from your marketing platforms. Gross revenue minus COGS minus fees minus refunds, allocated back to the order, is contribution margin you can actually trust.

LTV is a cohort roll-up on top of that same model. Stamp each customer with their first order date, group customers into monthly cohorts, and sum net margin per cohort over the months that follow. Because refunds reduce net and margin already nets out costs, the LTV curve reflects money you kept rather than money that briefly touched the account. That is the number worth spending against.

How do you handle refunds and cancelled orders in Shopify data?

Handle refunds by loading them as their own rows in fct_refund with negative amounts, and handle cancellations by trusting the order status you already refreshed through the updated_at watermark. Refunds are separate objects in Shopify, not a single field on the order, so a refund can be partial, can arrive days after the sale, and can stack (two partial refunds against one order). Modeling them as facts lets you sum the negatives against gross revenue for a clean net.

Cancellations are quieter but just as important. When an order is cancelled its updated_at moves, so an incremental load keyed on that field pulls the cancelled version and your MERGE overwrites the stale row. Read cancelled_at and financial_status in your reporting layer so a cancelled or fully refunded order stops counting as revenue. This is also where store operations meet the books: if your bookkeeper needs those payouts in accounting, you can convert a transactions CSV into a QBO file for import so the refunds and cancellations reconcile on both sides.

How much does it cost to sync Shopify to BigQuery?

BigQuery bills on two axes: storage for the bytes you keep, and query cost, which on-demand pricing charges by the bytes each query scans (or by slots if you buy dedicated capacity). Shopify volumes are usually modest, so storage is close to a rounding error. The cost that grows is query scan, and it grows fastest when dashboards read whole fact tables.

The fix is layout. Partition fct_order and fct_order_line by order date so a query for last month reads one month of data instead of all history, and cluster by customer and status so filtered reads prune further. A dashboard that scans a single partition costs a tiny fraction of one that scans the table, for the same answer. If your query patterns are steady and heavy, flat-rate slots can beat on-demand, but most stores do fine on-demand once partitioning is in place.

On the pipeline side, our pricing is flat by plan (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a big sales month does not produce a surprise invoice. See pricing for the full breakdown. Between a flat pipeline bill and partitioned tables that keep scans small, the total cost of moving Shopify into BigQuery stays predictable even as your order volume climbs.