The IDs do not match because Shopify and Stripe are not exporting the same thing.
Shopify order CSVs describe orders. Stripe transaction CSVs describe payments, captures, refunds, or balance activity. If you try to join #1042 from Shopify to ch_3P8x... from Stripe, nothing is wrong with the files. You are comparing an order number to a payment record.
For matching shopify order IDs stripe transaction CSV, the real job is to find the shared key first. Only then should you start matching rows.
Which values can actually match
Before you clean columns or write formulas, identify which pair of fields you are looking at.
| What you see on screen | Shopify stores it as | Stripe stores it as | What happens when matched |
|---|---|---|---|
#1042 | Name in the Shopify order export | id such as ch_3P8x... | No match. The first is an order number. The second is a charge ID. |
6721948832513 | Id in the Shopify order export or Order in transaction history | id such as ch_3P8x... | No match. Internal Shopify order ID is not the same object as a Stripe transaction ID. |
c1027508764856.2 | One entry inside Payment References | The same provider reference in a Stripe-linked export or metadata field | Direct match, if that exact reference exists on both sides. |
1042 | Cleaned Shopify order number from Name after removing # | metadata[order_number] or similar Stripe metadata field | Match works if metadata was written and exported. |
$84.20 on the same day | Total plus Paid at | Amount plus Created | Unsafe. Duplicate orders, retries, refunds, and timezone shifts create false matches. |
That table is the whole problem in one view.
Most bad reconciliations happen because someone uses the first row in that table and expects it to behave like the third or fourth.
Shopify gives you more than one order reference
Shopify exports multiple identifiers, and they do different jobs.
| Shopify field | What it represents | When to use it |
|---|---|---|
Name | The order number visible in Shopify, often with a # prefix | Use when Stripe metadata stores the order number, or when you need a human-readable reference |
Id | Shopify's internal order ID | Use only if that same internal ID was passed into Stripe metadata or another bridge field |
Payment ID | A payment-side ID sent by Shopify for successful or pending payments | Use when your Stripe-side export includes the same payment-side reference |
Payment References | All payment IDs linked to the order, including failed payments, refunds, and captures | Use when one order has multiple payment events and you need the full history, not only the final success |
This is why two people can export the same order from Shopify and still get different matching results.
One export is order-shaped. Another export is transaction-shaped. The transaction history export gives you one row per captured payment event, not one row per order. It also does not give you every possible gateway state. If you are comparing against authorizations, retries, or other payment events that are not present in that export, the Shopify file you pulled may not be operating at the same level.
So the first decision is not "Which formula should I use?"
It is "Am I matching order rows to order rows, or payment rows to payment rows?"
If you skip that question, you end up doing the same work described in why reconciling Shopify orders against Stripe payouts is slow, where one file is grouped by order and the other is grouped by settlement activity.
Stripe usually exports the payment object, not the Shopify order
Stripe CSVs are usually built around Stripe's own identifiers:
- charge IDs
- payment intent IDs
- refund IDs
- balance transaction IDs
- payout-related records
Those are valid IDs. They are not Shopify order IDs.
That distinction matters because one Shopify order can create several Stripe records:
- a failed payment attempt
- a successful charge
- a capture
- a refund
- a dispute or adjustment
If your Shopify file has one order row and your Stripe file has three payment rows tied to that order, a row-for-row match will fail even when the underlying data is correct.
This is where metadata becomes important. If Stripe has a metadata field like shopify_order_id or order_number, that field can act as the bridge back to Shopify. But some Stripe exports do not surface metadata by default, so the value can exist on the Stripe object and still be missing from the CSV you downloaded.
That leads to a common false conclusion:
"Stripe does not have the Shopify reference."
Sometimes Stripe does have it. Your export just does not include it.
Build the bridge table before you match anything
A reliable join needs one stable shared key. If you do not have one, you are not matching records. You are guessing based on amount and date.
The clean workflow is to build a small bridge table first.
| Working column | Comes from | Why it matters |
|---|---|---|
shopify_order_number_clean | Shopify Name with the # removed | Lets you compare against Stripe metadata that stores 1042 instead of #1042 |
shopify_internal_order_id | Shopify Id or transaction-history Order | Useful only when the same internal ID exists on the Stripe side |
shopify_payment_reference_single | One value split out from Payment References | Prevents one Shopify cell with multiple references from hiding several payment attempts |
stripe_transaction_id | Stripe id | The native Stripe record ID |
stripe_metadata_order_number | Stripe metadata export, if present | Often the cleanest route back to Shopify Name |
stripe_metadata_shopify_order_id | Stripe metadata export, if present | Clean route back to Shopify internal order ID |
amount and timestamp | Both files | Review fields only, not your primary join key |
Three rules make this work.
1. Split `Payment References` into one reference per row.
If Shopify gives you c1027508764856.1 + c1027508764856.2, that is not one reference. It is two. One may represent a failed attempt and the other the successful payment. If you keep them in one cell, you lose the ability to match each event correctly.
2. Keep the human order number separate from payment-side IDs.
#1042 is useful for review. It is not the same class of identifier as ch_3P8x.... Do not overwrite one with the other in a "cleaned" column. Keep both.
3. Normalize formatting without collapsing meaning.
Remove the # from Shopify order numbers if Stripe metadata stores plain numerals. Trim spaces. Preserve leading characters in payment references. A value that starts with c or ch_ is telling you what system generated it. Do not strip that away.
The matching order that works in practice
Once the bridge table exists, match in this order.
1. Match payment-side reference to payment-side reference
If Shopify Payment ID or a single Payment References value appears on the Stripe side, use that first. It is the closest thing to a true cross-system transaction key.
This is the best-case scenario because it connects the payment event directly and avoids guessing from totals.
2. Match Shopify internal order ID to Stripe metadata
If Stripe metadata contains the Shopify internal order ID, match Shopify Id or transaction-history Order to that metadata field.
This is strong because internal IDs are stable. The weakness is that the field must already have been written into Stripe and included in the export.
3. Match Shopify order number to Stripe metadata
If Stripe metadata stores the order number instead of the internal order ID, clean Shopify Name so that #1042 and 1042 do not fail on formatting alone.
This route works well for review and client-facing reporting because the order number is readable. It is weaker than an internal ID when different systems format order names differently.
4. Use amount and timestamp only to review leftovers
This is where many spreadsheets go off the rails.
Amount and date are not safe primary keys in Shopify and Stripe files because:
- the same amount can occur more than once in a day
- one order can have multiple payment attempts
- refunds can happen long after the original sale
- the order timestamp and payment timestamp are often not the same event
- timezone handling can shift the date without changing the payment
If dates are part of your review pass, treat them as supporting evidence. Do not promote them into the main join key, especially if you are already seeing the pattern behind Stripe UTC timestamps mismatch bank statement CSV dates.
Why good records still look unmatched
When a workbook says the rows do not align, one of these conditions is usually true.
You matched order-level data to payment-level data.
One Shopify order can map to several Stripe records. The file is not wrong. Your grain is wrong.
The Stripe export does not include the bridge field.
The metadata may exist in Stripe, but the CSV you exported may omit it. If the bridge key is missing from the file, the join will fail even though the underlying payment object contains it.
`Payment References` contains more than one value.
A single Shopify order can carry several payment IDs. If you treat the whole cell as one key, none of the individual payment events will match cleanly.
You are using `Name` because it looks familiar.
That is the most common trap. #1042 is easy to read, so people try to force everything to that number. Stripe transaction files are not built around what is easiest to read. They are built around payment objects.
Refunds are being matched to the original sale row.
Refunds need their own row logic. They are not negative copies of the original order export line.
The timestamps differ even when the payment is the same.
Shopify order creation, payment capture, and Stripe transaction creation can land at different times. If you force an exact date match, good records can look broken.
What a reliable finished output looks like
When the matching is correct, your working file should be able to answer these questions without manual digging:
| Question | The file should answer with |
|---|---|
| Which Shopify order does this Stripe transaction belong to? | Order number and internal order ID |
| Which Stripe payment event belongs to this Shopify order? | Transaction ID or payment-side reference |
| Is this row a sale, retry, refund, or capture? | Event type separated clearly |
| Was the record matched by direct key or by review logic? | Match method column |
| Which rows are still unresolved? | A short unmatched list with the reason |
If your sheet cannot answer those five questions, it is not finished. It may have most of the rows lined up, but it is not yet reliable enough to reuse next month.
At small volume, you can maintain that bridge table by hand.
At recurring volume, the real work stops being reconciliation judgment and turns into file maintenance:
- split multi-value payment references
- normalize order numbers
- re-export Stripe with the right fields
- rebuild the same joins
- review the same unmatched edge cases again
That is the point where the problem is no longer "What does this ID mean?" It is "Why am I still doing object mapping in a spreadsheet every month?"
