How to Join GA4 BigQuery Export Data to Shopify Orders

TL;DR

Most joins that return zero rows fail on identifier shape, not on setup. GA4 writes the numeric order id and never the #1001 order name, and the first-party Shopify connector for BigQuery hands you a GraphQL global id, so one REGEXP_EXTRACT fixes the whole query [4]. After that, two documented timing rules decide whether the numbers hold still: no backfill of history from before you linked the export [3], and daily tables that keep changing for three days [1]. Purchases the browser never saw join on the order id but not on the session, which quietly undercounts exactly the orders you most wanted explained.

Key Takeaways

• Join on ecommerce.transaction_id from the GA4 export against the numeric part of the Shopify order id. The order name is not in the GA4 row at all, so joining on #1001 returns nothing.
• Shopify's own route into BigQuery is the BigQuery Data Transfer Service Shopify connector, which "supports the data transfer of GraphQL-based resources, such as Collections and Orders" [4]. GraphQL ids are prefixed, so cast or strip before comparing.
• That connector is in Preview, and the no-cost clause is scoped to Preview: "There is no cost to transfer Shopify data into BigQuery while this feature is in Preview" [4]. Price it as a thing that will eventually cost something.
• Google documents no backfill for a GA4 to BigQuery link. Data "should start flowing to your BigQuery project within 24 hours" of linking [3], and nothing arrives for the period before it.
• Analytics updates daily tables with late events "for up to three days after the dates of the events" [1], so a reconciliation query run against yesterday needs a three-day stability window before you treat its output as final.
• Refunds arrive as separate refund events sharing the purchase's transaction id, so a purchase-only join understates returns, and summing purchase and refund values without sign handling double-counts.

The join that returns zero rows

Start with the query that fails, because it fails the same way in nearly every store.

Someone pulls the GA4 purchase rows for a day, pulls the Shopify orders for the same day, joins them on what looks like the obvious pair, and gets an empty result set. Then comes the checklist: export, timezone, event name, property id. None of it is the problem.

Shopify orders have two identifiers and GA4 only receives one of them. The order name is the human-readable string with the prefix, #1001. The order id is the long numeric value that the Admin API uses. A server-side purchase from WeltPixel Conversion Tracking arrives in GA4 with the numeric order id as its transaction id. The purchase is delivered once per order from the order webhook, and the same numeric value doubles as the event's deduplication key against the client-side event. Nothing in that row contains the order name.

So the join is available, and it is available on a stable key. What breaks it is the shape of the identifier on the Shopify side, which depends entirely on how you got the orders into BigQuery.

If you have not decided whether you need the raw export at all, when raw GA4 data beats dashboards covers the setup and the decision. This article starts after that.

How do Shopify orders get into BigQuery?

Through Google Cloud, not through Shopify. There is no export-to-BigQuery button in the Shopify admin.

Google Cloud documents a first-party path: "You can load data from Shopify to BigQuery using the BigQuery Data Transfer Service for Shopify connector. With the BigQuery Data Transfer Service, you can schedule recurring transfer jobs that add your latest data from Shopify to BigQuery" [4]. The prerequisites are specific. You need a custom Shopify app for your store, access to the required scopes, and roles/bigquery.admin on the BigQuery side. The console flow asks for your shop name, a client ID and a client secret; the bq command-line flow takes an Admin API access token and an asset list such as {"assets": ["Orders"]}, with a repeat frequency that includes an on-demand option [4].

Two caveats before you build on it. It is in Preview, and the pricing line is scoped to Preview: "There is no cost to transfer Shopify data into BigQuery while this feature is in Preview" [4]. And several resources carry conditions: GiftCards requires a Shopify Plus subscription, the app-subscription resources require a sales-channel app, and the discount-code resources require a discount function [4]. Orders carries none of those.

The consequence for your SQL is the one the connector's own description gives away. It transfers GraphQL-based resources [4], and GraphQL ids are global ids, so an order id arrives looking like gid://shopify/Order/1234567890 while GA4 carries 1234567890.

Which keys exist on both sides?

Three that matter, and one that people expect and will not find.

The transaction id is the reliable one. The GA4 purchase row also carries user_pseudo_id, described in Google's schema as "The pseudonymous id (e.g., app instance ID) for the user" [1], the session parameter inside event_params, revenue in ecommerce.purchase_revenue_in_usd, currency, the items array, and the campaign values captured at the landing page. Those enrich a joined row; none is a join key on its own.

What people expect and will not find is a customer email or customer id. GA4 does not receive one.

Here is the shape of the query. Confirm the Shopify-side column names against your own transferred table before you run it. The connector documentation lists resources, not schemas.

-- GA4 purchases joined to Shopify orders for one day
WITH ga4_purchases AS (
  SELECT
    ecommerce.transaction_id           AS order_id,
    ecommerce.purchase_revenue_in_usd  AS ga4_revenue,
    user_pseudo_id
  FROM `your_project.analytics_123456789.events_20260914`
  WHERE event_name = 'purchase'
),
-- column names in this CTE are placeholders: confirm them against your own transferred table
shopify_orders AS (
  SELECT
    REGEXP_EXTRACT(id, r'(\d+)$') AS order_id,
    total_price,
    created_at
  FROM `your_project.shopify_transfer.Orders`
)
SELECT
  o.order_id,
  o.total_price,
  g.ga4_revenue,
  g.user_pseudo_id
FROM shopify_orders AS o
LEFT JOIN ga4_purchases AS g USING (order_id);

The LEFT JOIN direction is deliberate. Orders are the population you care about, and the rows where ga4_revenue comes back null are the finding, not an error.

One caveat on the revenue columns. ecommerce.purchase_revenue_in_usd is the USD-converted value in Google's schema [1], while the Shopify order total is in the store's own currency, so a non-USD store has to convert one side first.

Every channel in WeltPixel Conversion Tracking sends the order's total price, tax and shipping included, as the purchase value. If your Shopify side uses a subtotal, the revenue columns differ by a consistent margin and you spend an afternoon hunting a tracking bug that is a column choice. The wider version of that mismatch is in why Shopify and GA4 revenue never match.

How stable is a join you ran this morning?

Less stable than it looks for three days, and permanently blind to anything before the link date.

Google's export documentation states that "Analytics will update the daily tables (events_YYYYMMDD) with events for those dates for up to three days after the dates of the events" [1]. A count you take on Tuesday for Monday can legitimately grow on Wednesday and Thursday. If your reconciliation dashboard alerts on a gap, it needs to ignore the trailing three days or it will alert every single morning.

For a reconciliation join, the daily table is the one to work against, because the streaming table carries no completeness guarantee [2]. Choosing between the two exports is part of the setup decision covered in when raw GA4 data beats dashboards.

The history question has a short answer. Google's setup documentation says only that "Once the linkage is complete, data should start flowing to your BigQuery project within 24 hours" [3], and documents no import of prior history. So a store that links the export today cannot query last quarter, ever.

Volume caps close the section. Standard properties export up to 1 million events per day and Analytics 360 up to 20 billion [2]. Google's setup documentation states that if a property consistently exceeds the export limit, the daily BigQuery export is paused and previous days' exports are not reprocessed [3], which makes the cap a planning input for high-traffic stores. Retention is the other reason people build this pipeline, covered in the 14-month retention limit.

The rows that behave differently

Two categories, and one of them is the most useful thing in this article.

Purchases the browser never saw. With admin-placed orders, blocked pixels and consent declines, no browser session bridges to the order, and the server-side purchase still lands in GA4 with a real transaction id and a real value. Its visitor and session identity is derived from the order record instead, so those identifiers match no session rows in the export. A transaction-id join keeps every one of those orders. A session-level join, the kind that attaches landing page, device and campaign, silently drops them. The orders you most want explained fall out of the enrichment step and nothing in the query says so. Count them at the session level: the number to watch is matched orders whose GA4 row carries no session that exists in the event tables, which is the count step 5 below asks you to take. Background in why a Shopify purchase starts a new GA4 session and client id versus user id.

Refunds. A refund arrives as its own refund event carrying the purchase's transaction id, with its own event id and a timestamp taken from the refund date. Refunds join fine; the arithmetic is what goes wrong. A purchase-only query understates returns, and adding purchase and refund values without handling sign inflates revenue. The GA4 schema exposes refund_value_in_usd alongside purchase revenue for that reason [1]. Mechanics in server-side GA4 refund tracking.

Where ShopifyQL fits, and where it does not

Someone will suggest it instead of the join, so know what it is. Shopify's developer documentation describes ShopifyQL as the query layer behind the admin's reports, queryable through the GraphQL Admin API, with analytics-queryable metafields, app events, embeddable metric cards and annotations [5]. That is an app-developer surface over Shopify's own data. It does not query GA4 event data. For a question that lives entirely inside Shopify, it may be shorter than BigQuery; for "which GA4 session preceded this order", the join is the only route.

Run it once and read the gap

1. Confirm your GA4 export exists and note the date it started. Anything before that date is not recoverable [3].
2. Set up the Shopify transfer with {"assets": ["Orders"]} and run it on demand once [4]. Inspect the resulting table and write down the actual name and shape of the id column.
3. Run the joined query above for a single day that is at least four days old, so the daily table has settled [1].
4. Count three numbers: orders in Shopify, purchase rows in GA4, and rows that matched. A GA4 count well below the order count is a delivery problem rather than a join problem, and the Measurement Protocol primer is where to start.
5. Attempt the session-level enrichment on matched rows and count how many orders lose their session. That is your ceiling on any campaign analysis built from this pipeline.
6. Schedule the transfer and the query daily, excluding the three-day trailing window from any alert.

FAQ

Why does my join on the Shopify order name return no rows?

Because the order name is never sent to GA4. The purchase event carries the numeric order id as its transaction id, so #1001 matches nothing. Join on the numeric id and strip the gid://shopify/Order/ prefix from the connector's output first [4].

Can I backfill GA4 data from before I set up the BigQuery export?

No. Google documents only that data starts flowing within 24 hours of linking [3], with no historical import.

How long should I wait before treating a day's numbers as final?

Three days after the event date, because Analytics keeps updating daily tables with late events for that long [1]. Streaming tables are useful within minutes but carry no completeness guarantee [2].

Is the Shopify BigQuery connector safe to build a production pipeline on?

It works, and it is in Preview, which means the interface can change and the free-transfer clause is tied to that status [4]. Re-check the schema on a schedule instead of hard-coding it once.

Do refunds break the join?

No. They arrive as separate refund events sharing the purchase transaction id, with their own timestamps. Handle them as a signed adjustment rather than adding their values to purchase revenue [1].

If your GA4 purchase rows are missing to begin with, no join will find them. WeltPixel Conversion Tracking [6] sends the purchase from the Shopify order webhook with the numeric order id as both the transaction id and the deduplication key, which is what makes a one-to-one join to your order table possible in the first place.

Before you build the dashboard, run the query for one settled day and count the orders whose session is missing. Every campaign number you produce later inherits that gap.

Sources

1. Google Analytics Help, "[GA4] BigQuery Export schema" (daily events_YYYYMMDD and events_intraday_YYYYMMDD tables; user_pseudo_id definition; ecommerce fields including transaction_id, purchase_revenue_in_usd and refund_value_in_usd; daily tables updated with late events for up to three days after the event dates), support.google.com/analytics/answer/7029846, accessed September 10, 2026
2. Google Analytics Help, "[GA4] BigQuery Export" (daily export of raw unsampled data from the previous day; streaming export within minutes with no completeness guarantee; standard properties up to 1 million events per day, Analytics 360 up to 20 billion), support.google.com/analytics/answer/9358801, accessed September 10, 2026
3. Google Analytics Help, "[GA4] Set up BigQuery Export" (data should start flowing within 24 hours of linkage; no documented historical import; a property that consistently exceeds the export limit has its daily BigQuery export paused, and previous days' exports are not reprocessed), support.google.com/analytics/answer/9823238, accessed September 10, 2026
4. Google Cloud documentation, "Shopify transfers" (BigQuery Data Transfer Service Shopify connector; transfer of GraphQL-based resources such as Collections and Orders; custom Shopify app, access scopes and roles/bigquery.admin prerequisites; console and bq flows with asset lists and on-demand repeat frequency; resource limitations for GiftCards, app-subscription and discount-code resources; no cost to transfer while in Preview), docs.cloud.google.com/bigquery/docs/shopify-transfer, accessed September 10, 2026
5. Shopify.dev, "Build analytics for apps" (ShopifyQL as the query layer behind admin reports, queryable through the GraphQL Admin API; analytics-queryable metafields, app events, embedded metric cards and annotations), shopify.dev/docs/apps/build/analytics, accessed September 10, 2026
6. WeltPixel Conversion Tracking, Shopify App Store listing, apps.shopify.com/weltpixel-conversion-tracking, accessed September 10, 2026