- Why Order-to-Shipping Reconciliation Fails
- The Orders vs Shipping Problem
- Step 1: Standardise Customer Names
- Step 2: Fuzzy-Match Orders to Shipments
- Step 3: Separate Fulfilled from Unfulfilled
- Step 4: Investigate the Unmatched Orders
- Run It as a One-Click Template
- Frequently Asked Questions
- You Might Also Like
Every e-commerce team faces the same question at month end: which orders never shipped? The answer hides in two spreadsheets that almost never agree. This guide shows how to match orders against your shipping log in Google Sheets and flag the gaps with confidence scores.
Why Order-to-Shipping Reconciliation Fails
Your orders export comes from the storefront; your shipping log comes from the warehouse or courier system. They are generated by different tools, at different times and with different conventions. The order
#1042
in the store may appear as
1042
or
ORD-1042
in the shipping logand the customer who typed
J. Smith
at checkout may have been entered as
John Smith
at pickup.
A simple VLOOKUP on the order ID returns "not found" for perfectly shipped orders and a lookup on customer names misses every spelling variant. That produces a false list of unfulfilled orders and a support queue full of angry customers whose package actually arrived.
The Orders vs Shipping Problem
Reconciliation compares two views of the same event:
- The order exists in the store system (it was paid for).
- The shipment exists in the logistics system (it was sent).
An order is unfulfilled when no believable shipment matches it. The hard part is deciding what counts as a believable match when identifiers drift. Fuzzy matching on standardised customer names plus the order ID gives you a score you can use to make that call consistently.
Step 1: Standardise Customer Names
Normalise the name column in both the orders export and the shipping log. Lowercasing, removing punctuation and collapsing whitespace turns
J. Smith
and
John Smith
into comparable values.
The Standardize Data function in Flookup Data Wrangler applies the same cleaning rules to both columns in one pass, so your matching step compares apples to apples.
Step 2: Fuzzy-Match Orders to Shipments
Use
Fuzzy Match Data
to find the best shipping log record for every order. Ask it to return the matched shipping order ID and its similarity score. A threshold around
0.85
is a good starting point for customer names.
Treat the score as your decision signal: high scores confirm the shipment, borderline scores need a human glanceand no match at all means genuinely unfulfilled.
Step 3: Separate Fulfilled from Unfulfilled
Now bucket every order:
- Matched and shipped - the order has a shipment with a high confidence score.
- Borderline - a possible shipment at medium confidence; verify the order ID manually.
- Unmatched - no shipment found; these are your candidates for investigation.
Write the three buckets back to your sheet with the match score alongside, so the next audit can reproduce your reasoning.
Step 4: Investigate the Unmatched Orders
For every unmatched order, check whether the shipment exists under a different identifier or was cancelled and refunded. Fix the underlying system issue when you can: add order ID synonyms to the shipping log or enforce a canonical ID format at checkout so next month's reconciliation is cleaner.
Run It as a One-Click Template
The Order-to-Shipping Reconciliation workflow template in Flookup Data Wrangler automates the whole pipeline. Give it your orders list range and shipping log range and it standardises the customer names, fuzzy-matches each order to the shipping log and writes an Order Ship Recon sheet with the order ID, customer, matched shipping order ID, match score, status and confidence for every order.
The template is included with the Data Nova plan and it never modifies your original orders or shipping data.
Frequently Asked Questions
How do I match orders to shipments in Google Sheets?
Match orders to shipments by joining the two lists on a unique key such as an order ID. When the keys do not line up, standardise the customer name columns and fuzzy-match them instead, then use the resulting confidence score to separate order-to-shipping matches from genuinely unfulfilled orders.
Why do some orders never match a shipment with a lookup formula?
Lookup formulas such as VLOOKUP match exactly. If an order ID is missing, truncated or entered as text where the shipping log stores it as a numberor if the customer name differs by a space or a spelling variant, the lookup returns not found even though the shipment exists. Fuzzy matching tolerates these variations.
How can I flag unfulfilled orders automatically?
Use the Order-to-Shipping Reconciliation workflow template in Flookup Data Wrangler. It standardises customer names, fuzzy-matches each order to the shipping log and writes a review sheet with the matched shipping order, match score, status and confidence. Orders with no match are flagged so you can investigate them.
What causes shipping logs and orders exports to disagree?
Common causes are different ordering platforms or stores, localised date formats, customer names typed differently at checkout versus at pickup, refunded or split ordersand exports taken at different times. Each of these shows up as a missing or low-confidence match that a reconciliation review sheet makes easy to spot.