Three-way reconciliation

Purpose

This workflow ensures complete transaction integrity and cash accuracy by:

• Matching every Shopify order to its corresponding Stripe charge at the transaction level
• Verifying that the net payout Stripe reports is consistent with what the bank received
• Flagging mismatches, missing transactions, and fee discrepancies before they affect reporting

Data sources

1. Shopify orders: Order-level export from Shopify for the period, including order ID, gross amount, payment status, and transaction date.
2. Stripe charges and payouts: Transaction-level charge report and payout summary from Stripe, including charge ID, gross amount, fees deducted, net payout amount, and payout date.
3. Bank statement: Bank transaction records for the period showing deposit amounts and value dates.

Key processing steps

1. Transaction normalization

Records from all three sources are standardized into a consistent format before any matching takes place. Date formats are unified, currency values are aligned to the same unit, and identifiers are normalized so that Shopify order IDs can be joined to Stripe charge metadata.

2. Shopify to Stripe transaction matching

• Each Shopify order is matched to its corresponding Stripe charge using order ID. For every pair, the gross order amount in Shopify is compared to the gross charge amount in Stripe.
• Transactions that appear in one system but not the other, and pairs where amounts diverge beyond the configured tolerance, are flagged as mismatches.

3. Bank to Stripe settlement matching

• Stripe batches multiple charges into a single payout. Each Stripe payout is matched to its corresponding bank deposit using the payout date and net amount after fees. Because one payout covers many transactions, this is an aggregated match: the sum of net charges in a payout batch is compared to the single bank deposit value.
• Payouts with no matching bank deposit and deposits where the amount does not reconcile to a known Stripe payout are flagged.

4. Exception classification

All flagged records are classified by exception type: missing transaction, amount mismatch, unmatched payout, or unmatched bank deposit.

Outputs

1. Reconciliation report: A full match summary for the period showing the status of every Shopify order and every Stripe payout. Includes a gross-to-net bridge per payout batch showing charges, fees, and the resulting bank deposit.
2. Exception log: All unmatched and mismatched records grouped by exception type, with the source data and expected values included so the finance team can investigate and resolve each item.