---
name: purchase-reconciliation
description: Reconciles analytics purchases with the order or billing system order by order, never total against total. Joins GA4 transaction IDs to orders, classifies every order as matched, missing, duplicate, value mismatch or late, traces each class to its cause (consent, blockers, payment redirects, reloads, legacy checkout scripts, currency, tax and shipping, time zones) and decides between a browser fix and server-confirmed sending. Ships BigQuery and portable SQL, gap thresholds and a report format. Use when GA4 purchases or revenue do not match Shopify, Stripe, WooCommerce, the order database or billing system, "GA4 purchase discrepancy", "missing transactions", "GA4 conversions lower than orders", "GA4 shows fewer orders", "purchases double counted", "transaction_id does not match", "revenue is off", when checking the GA4 BigQuery export against orders, before moving purchases to server side or Measurement Protocol or a backfill, after a checkout or tag change, or when asked what gap is acceptable.
license: CC-BY-4.0
metadata:
  tier: free
  category: tracking-troubleshooting
  version: "0.2.0"
  last_reviewed: "2026-11-06"
  status: reviewed
---

# Purchase reconciliation

Compares analytics purchases with the system that records orders or payments, one order at a time. The output is a classified order list, a cause for each class, a decision on where to fix, and a short report that states the gap against agreed thresholds. Equal totals are never the deliverable: missing and duplicated orders cancel each other out.

## When to use

Use this skill when:

- GA4 shows fewer or more purchases, or different revenue, than Shopify, Stripe, WooCommerce, the order database or the billing system.
- Someone proposes moving purchase tracking to the server, adding Measurement Protocol, or backfilling missing orders.
- A checkout, payment, consent or tag change shipped (including Shopify's Thank you and Order status upgrade) and needs proof that purchase collection still works.
- Someone asks what gap is acceptable, or wants a recurring coverage number.

Use something else when:

- Ad platforms disagree with GA4 or the CRM: platform-number-reconciliation, or the ad platform's own conversion diagnostics when that skill is not installed. Run this skill first when the ad platform imports GA4 purchases.
- The question is which channel gets credit, not whether the order was measured: attribution-model-selector, after collection is reconciled.
- The tag must be built or changed: ga4-ecommerce-and-refunds, gtm-change-workflow, stripe-conversion-tracking or ga4-measurement-protocol when installed, otherwise Google's ecommerce and Measurement Protocol guides with GTM Preview mode.
- Consent behaviour itself needs testing: consent-debugging.

## Inputs

Read these first if they exist:

- `martech-context/stack.md`: the numeric GA4 property ID and stream measurement IDs, the BigQuery export project, dataset and location, every purchase sender (platform app, theme code, GTM, custom pixel, server container, Measurement Protocol), the order and billing systems, payment providers and their capture setting.
- `martech-context/metrics.md`: order and revenue definitions, and any agreed reconciliation thresholds.
- `martech-context/tracking-plan/`: the purchase event contract (trigger, `transaction_id` source, value rule).
- `martech-context/decisions.md`: consent model (basic or advanced), dates of tracking and checkout releases.

Then gather what is missing:

- Read-only access to the GA4 BigQuery export, or to the GA4 Data API when there is no export.
- Read-only access to orders: a warehouse table, an order export or the billing API.
- The property's reporting time zone and currency (Admin API property fields `timeZone` and `currencyCode`, or `get_property_details` in Google's GA4 MCP server).
- The payment methods and checkout flows, including those that leave the site (3-D Secure, wallets, bank redirects) or settle later (bank transfer, direct debit), and whether payment is captured at checkout or later.
- Consent evidence for the period: CMP logs or consent rates by region. Without it, consent cannot be named as a cause.

Never ask for, print or store API secrets, keys or customer personal data. The worksheet carries order IDs, amounts and flags only.

## Procedure

### 1. Write the comparison contract

Fill [assets/comparison-contract.yaml](assets/comparison-contract.yaml) before any query. It fixes:

- The outcome `purchase` is meant to count: placed, paid, or paid and not cancelled. Most mismatches start here.
- For paid, the paid definition: `captured`, or `authorized` when the business counts revenue at authorization (manual capture at fulfilment, such as Shopify `AUTHORIZED` orders or Stripe `requires_capture`). Under `captured`, authorized orders awaiting capture are excluded as `authorized_not_captured`, not reported as unpaid. Mapping: [references/platform-notes.md](references/platform-notes.md).
- The eligible population: channels, statuses, test-order rule, stores. Count every exclusion by reason.
- The join key: the order field the integration sends as `transaction_id`, plus any second key.
- The value rule. GA4 defines `value` as the sum of price times quantity of the items, without shipping or tax (checked 2026-10-17). Compare items after discounts, without tax and shipping, per currency.
- One business timestamp, one reporting time zone, complete days only.

If the intended outcome cannot be written in one sentence, stop and agree it with the owner. Never edit the contract later to bring the numbers closer.

### 2. Freeze the period and prove both sides are complete

- GA4 updates a daily export table for up to 2 calendar days plus today beyond its date, and occasionally later (checked 2026-10-17). The scan window runs 3 days past the period, so run only when the last scan-window day ended at least 3 days ago: 6 days after the period ended. A period ending 2026-09-13 runs on 2026-09-20 or later.
- Run the worksheet's schema check and Query 0: every field path in every daily table, one table per day, no empty days, no intraday tables. A missing day is an export failure, not a tracking gap.
- List what removes purchases before the export: unlinked streams, export event exclusions, active data filters and the standard property's daily export limit of 1 million events.
- On the order side, record the extract time and the latest timestamp present. Warehouse copies often lag their source by one to two days.

### 3. Prove the transaction ID format on real orders

- Take five ordinary orders and five problem orders (redirect payment, upsell, refunded) and find their raw `transaction_id` in the export. Compare with every identifier the order has: internal ID, display name such as `#1042`, confirmation number, checkout token, invoice number.
- Write a normalisation rule only after it holds for all ten. Blind character stripping creates collisions.
- If the browser sends a provisional number (cart, checkout or quote) and billing later issues a final number, map both keys to the order.
- When several stores or streams share a property, join on store and key. Order number sequences collide across stores.

### 4. Pull the analytics side

Use [assets/ga4-bigquery-worksheet.sql](assets/ga4-bigquery-worksheet.sql). Field notes, run limits and the Data API fallback: [references/bigquery-worksheet-notes.md](references/bigquery-worksheet-notes.md).

- Stay read-only: run through the BigQuery MCP tool `execute_sql_readonly`, or with a role that cannot write. That tool returns at most 3,000 rows and stops after 3 minutes, so pull summaries there and page long lists.
- Wildcard queries are never cached: dry-run each new query and read the bytes estimate.
- Without an export, use the Data API (`transactionId`, `ecommercePurchases`, `grossPurchaseRevenue`) and read the response metadata: `dataLossFromOtherRow` or `subjectToThresholding` true means the list may be incomplete; `samplingMetadatas` means sampled; `schemaRestrictionResponse` means revenue hidden by the user's role.

### 5. Pull the order side

Fill [assets/order-side-template.sql](assets/order-side-template.sql). It returns one row per order with eligibility, exclusion reason, both keys, amounts, currency, tolerance and segment fields. It extracts orders paid in the period or placed in the scan window; a placed-date filter alone drops orders placed earlier and paid in the period. Run its extract check: eligible orders placed before `scan_window_start` must be zero, or widen the scan window to the earliest one and re-run Query 0.

### 6. Classify every order

Join on store and key over the scan window. Where to run it, in order of preference: a saved order table next to the export (approval needed), the order rows inlined as a literal, or Query 3 exported to the order warehouse and [assets/classify-on-order-side.sql](assets/classify-on-order-side.sql) (portable SQL). Each eligible order gets separate flags, then one primary class in this order of precedence:

- `missing_in_analytics`: no purchase row in the window.
- `duplicate`: more than one purchase row, or two raw IDs mapped to the order. Split identical transport rows, same ID and different IDs. Google says reports deduplicate a repeated ID from the same user on web streams and is silent on the export; check one known reloaded order before relying on either.
- `late`: the first row falls outside the period on the order's clock, arrives more than 60 minutes after the business timestamp, or arrives more than 5 minutes before it (sub-flag `fired_before_business_ts`).
- `value_mismatch`: currency differs, value is missing, or value differs by more than one minor unit per item line.
- `matched`: exactly one row, on time, with value and currency inside tolerance.

Then list analytics-only transactions (no eligible order) with the order side's reason: test, unpaid, authorized not captured, cancelled, other channel, not found. A join that starts from orders alone hides them.

### 7. Find the cause of each class

Causes, detection and evidence per class: [references/classes-and-causes.md](references/classes-and-causes.md).

- 7a. Segments. Group each class by payment method, checkout flow, device, country, consent state and sender (Query 7). Compare rates, never counts, and compare each segment with the control: the in-page checkout flow with at least 100 eligible orders and the lowest miss rate. A segment is a suspect when it has at least 20 eligible orders and a miss rate at least twice the control's or at least 5 percentage points above it. Never compare with the overall rate: it already contains the defect.
- 7b. Blockers. Join a first-party record of confirmation-page loads (server access log or own beacon) to orders with no GA4 row, or, once checkout captures `client_id`, use the share of consented checkouts without one. Measured blocker loss counts as explained; unmeasured, it stays unexplained.
- 7c. Name causes from the reference. The common missing causes: consent under basic mode, blockers, redirects that never return to a page that pushes the paid event, legacy Shopify checkout scripts stopped by the Thank you and Order status upgrade, a hosted checkout without a sender, a hidden trigger filter, an empty payload.

Name consent as a cause only with consent evidence. A browser event that never arrived cannot report its own consent state.

### 8. Check senders and attribution

Run Queries 1 and 2 (rows in the period only). Per sender, count purchase rows, distinct `user_pseudo_id`, rows without a session ID and distinct session source and medium.

- A sender whose rows share one user, have no sessions or show one source and medium carries no attribution, even when the columns are not null. Check cardinality, never null rates.
- Orders seen by two senders are double counted unless both send the same `transaction_id` for the same user and reports prove the deduplication.
- Before removing a sender, count the orders only it captures (`browser_only`, `server_only`); those are lost with it.

### 9. Decide where to fix

Detail and the hold-until-paid design: [references/browser-vs-server.md](references/browser-vs-server.md).

- Compute `recoverable_share` (Query 7): for each redirect and delayed-payment flow, its miss rate minus the control's, floored at zero, times its orders; summed and divided by eligible orders.
- Fix in the browser when recoverable share is at most 2%, nothing fires before payment under a paid contract, and the defect is a trigger, payload, ID or reload problem. Browser events keep their session.
- Confirm on the server (hold until paid, then send) when recoverable share is above 2%, or the contract counts paid orders and any purchase fires before payment. Redirect share alone never decides it. Capture `client_id` and `session_id` with the Google tag `get` command at checkout, store them on the order, and send `purchase` once payment is confirmed. One sender per order.
- When no `client_id` was captured (blocked or not loaded before a fast redirect), the contract decides: send with a generated ID marked `data_source` = `server_no_client`, which recovers counts but never attribution, or do not send.
- Measurement Protocol events take the session's attributes only with `session_id`, within 24 hours of session start, and with any overridden timestamp inside that session. Backdating stops at 72 hours. A backfill after a frozen period misses all of this, lands in Unassigned or Direct, and cannot be recalled.
- A return URL flag such as `?status=success` is not proof of payment. Confirm on the server.

### 10. Apply thresholds and report

Defaults, to be replaced by thresholds agreed in `metrics.md` (reasoning in [references/thresholds-and-reporting.md](references/thresholds-and-reporting.md)):

- Measure the gap against the expected ceiling: eligible orders minus orders explained by documented consent, measured blockers or policy.
- Unexplained gap up to 2% of eligible orders and of eligible value: pass. Above 2% and up to 5%: investigate the top suspect. Above 5%, or any suspect whose excess over the control consent does not explain: defect.
- Above 5% with no suspect segment and blockers not measured: measure blockers before calling a defect.
- Duplicate flag rate above 0.5% or value flag rate above 1% of observed orders, taken from the flags, not the primary classes: defect. Any analytics-only purchase from an order that never reached the contract's paid state: defect.
- Under 200 eligible orders, extend the period before quoting rates.
- Name the sender architecture (browser only, server confirmed, mixed) beside the verdict.

Write the report with [assets/reconciliation-report-template.md](assets/reconciliation-report-template.md).

### 11. Re-run after the fix

Record the release time, then re-run the same contract on a frozen period after it. A total that moved is not evidence; the class counts must move.

## Worked example

Acme Outdoor, a fictional store, reconciles 2026-09-07 to 2026-09-13 in New York time, run on 2026-09-20. Full report: [assets/example-acme-report.md](assets/example-acme-report.md); sample rows: [assets/example-acme-classified-orders.csv](assets/example-acme-classified-orders.csv).

- 1,000 eligible paid web orders; 905 purchase rows in the week read as a 9.5% gap. The extract check finds 17 bank transfer orders placed before the default scan window, so it is widened to 2026-08-31.
- Order by order: 724 matched, 153 missing, 31 duplicate, 24 value mismatch and 68 late (63 sent before payment), plus 53 analytics-only, 29 of them unpaid.
- The control, in-page card, misses 7.7%, in line with 8% consent denials. 3-D Secure redirects miss 35.0% (4.6 times the control) and wallet redirects (`redirect_wallet`) 29.3% (3.8 times); against the overall 15.3% the wallet flow would not be flagged. Unexplained gap 7.3%: defect.
- Recoverable share 8.2% and 92 placement fires decide server-confirmed sending. The re-run on 2026-09-28 to 2026-10-04 passes at 1.4% unexplained.

## Acceptance checks

- Contract: outcome, paid definition, population, key, value rule, time zone, period and scan window are filled before any query. Pass: no template text remains and the exclusion counts sum to the orders in scope.
- Freeze and completeness: the last scan-window day ended at least 3 days before the run, every field path exists in every daily table, and every scan day has a table. Pass: the schema check, Query 0 and the extract notes are in the report.
- Extract: orders paid in the period are present whatever their placement date, and no eligible order was placed before `scan_window_start`. Pass: the extract check returns zero rows for the window used.
- Key proof: ten named orders were traced to their raw `transaction_id`, and Query 4 returns zero shared keys. Pass: the normalisation rule is written in the contract.
- Classification: every eligible order has exactly one primary class. Pass: the class counts sum to the eligible count, shown beside the flag rates.
- Analytics-only: distinct transaction IDs in the period joined to an eligible order (any class except missing) plus analytics-only transactions equals distinct transaction IDs in the period. Pass: the three numbers are shown.
- Causes: each suspect segment against the control has evidence (query output, a reproduced journey, consent or blocker logs) or is marked unexplained. Pass: no cause is named without evidence.
- Senders: per-sender cardinality and overlap are reported for the period. Pass: a sender without attribution is labelled as such.
- Thresholds: the verdict cites the threshold source and the sender architecture. Pass: unexplained gap, suspects, duplicate and value flag rates and unpaid analytics-only counts each have a verdict.
- Fix verification: a re-run on a frozen post-release period with the same contract. Pass: the targeted class counts fell and no other class rose.

## Boundaries

- Read-only. The skill does not edit tags, change GA4 settings, publish containers, send Measurement Protocol events or write tables. Saving the order-side output or the Query 3 export as a table, a backfill and any tag or property change are drafts that stop for human approval.
- A backfill is permanent: events sent to GA4 cannot be recalled by the sender. Get written approval with the order list, the parameters and the expected reporting effect first.
- Consent policy belongs to the privacy owner. Never raise coverage by changing consent behaviour.
- Revenue truth for finance stays in the order or billing system. GA4 is reconciled to it, not the reverse.
- Hand-offs and their read-only modes:
  - BigQuery: Google's remote MCP server; use `execute_sql_readonly` and ask an admin for an IAM deny policy on `execute_sql` and `cancel_job`.
  - GA4: Google's GA4 MCP server (`analytics.readonly` scope, no `--allow-write`) for `run_report` and property details.
  - Stripe: Stripe's MCP server; call only `stripe_api_read`, with an OAuth grant or agent key limited to read permissions.
  - Shopify: the Dev MCP server serves docs and schemas, not orders. The AI Toolkit's `shopify store execute` reads orders only with read scopes and without `--allow-mutations`, after human approval; otherwise use an approved export, connector or Admin API app.
  - Tags and server senders: gtm-change-workflow, stripe-conversion-tracking, ga4-measurement-protocol or server-side-tracking-debug when installed; otherwise GTM Preview mode, server container Preview and Google's Measurement Protocol validation endpoint.

## Live docs

Platform settings, limits and menu paths change. Before stating any parameter name, limit, quota or UI path:

- Fetch the current page from the vendor docs MCP server or llms.txt listed in `sources.yaml`, or open the canonical URL.
- If the live page disagrees with this skill, follow the live page and tell the user what changed.
- Cite the URL you used.

The entries that matter most here: the GA4 export schema and behaviour pages, the transaction ID deduplication article, the Measurement Protocol sending and use-case pages, the Data API response metadata, Shopify's financial status and checkout upgrade pages, and Stripe's manual capture and webhook pages. Every fact in this skill was checked on 2026-10-17.
