How to reconcile shipping carrier invoices with your orders
By the Analistable team · Updated · 6 min read
Join the carrier's invoice detail (one row per parcel) to your orders on the tracking number. Then compare what you charged the customer for shipping with what the carrier billed, flag weight or size adjustments, list surcharges (fuel, remote area, residential), and catch parcels billed twice or billed for orders you never shipped.
Part of our guide: How to reconcile data in Excel
Prepare the two files
- From your shop (WooCommerce, Shopify…): order number, tracking number, shipping charged to the customer, declared weight.
- From the carrier: invoice line per parcel with tracking number, billed weight, base charge and each surcharge.
- Normalise tracking numbers: remove spaces and make them upper case in both files.
Formulas
Billed =SUMIFS(Carrier[Total], Carrier[Tracking], [@Tracking])
Times billed =COUNTIFS(Carrier[Tracking], [@Tracking])
Weight diff =XLOOKUP([@Tracking], Carrier[Tracking], Carrier[Billed kg]) - [@[Declared kg]]
Margin =[@[Shipping charged]] - [@Billed]On the carrier side, =COUNTIFS(Orders[Tracking], [@Tracking])=0 finds parcels billed that aren't in your orders.
Worked example
| Order | Declared kg | Billed kg | Charged customer | Carrier total | Times billed | Flag |
|---|---|---|---|---|---|---|
| #2101 | 1 | 1 | 4.95 | 4.2 | 1 | OK |
| #2102 | 2 | 5 | 6.95 | 11.6 | 1 | Weight adjustment |
| #2103 | 1 | 1 | 4.95 | 8.4 | 2 | Billed twice |
| (none) | — | 1 | — | 4.2 | 1 | Not in orders — return label? |
#2102 was billed at 5 kg: either the product weight in the shop is wrong, or the carrier measured volumetric weight. #2103 should be disputed. The unknown parcel may be a return label you issued — check before disputing it.
Turn it into a monthly check
Build it once in Power Query: merge orders and carrier lines on tracking number (full outer join), add the flags, and refresh with each invoice. Dispute duplicates and unexplained weight adjustments within the carrier's claim window.
The same join technique applies to any two systems that share a reference — see joining two tables in Excel.
Surcharges
Carriers add fuel, remote-area, residential or out-of-hours surcharges as separate columns or lines. Total them per parcel, then per month: a rising surcharge share often justifies renegotiating rates or changing service levels for remote postcodes.
Checking volumetric weight yourself
When the carrier bills a higher weight, work out what the volumetric weight of your box should be. Carriers divide length × width × height (in cm) by a divisor set in their terms; 5,000 is common for international express services, but yours may differ, so take the figure from your contract.
Volumetric kg =[@[Length cm]] * [@[Width cm]] * [@[Height cm]] / 5000
Chargeable kg =MAX([@[Actual kg]], [@[Volumetric kg]])
Explained =IF(ABS([@[Billed kg]] - [@[Chargeable kg]]) <= 0.5, "Explained", "Query")| Order | Box (cm) | Actual kg | Volumetric kg | Chargeable kg | Billed kg | Result |
|---|---|---|---|---|---|---|
| #2210 | 40 × 30 × 20 | 3.1 | 4.8 | 4.8 | 5 | Explained |
| #2211 | 30 × 20 × 15 | 3.1 | 1.8 | 3.1 | 3 | Explained |
| #2212 | 30 × 20 × 15 | 3.1 | 1.8 | 3.1 | 6 | Query |
#2210 went out in a larger box than needed, so the extra weight is real: 40 × 30 × 20 = 24,000 ÷ 5,000 = 4.8 kg. #2212 used the small box and was still billed at 6 kg, which is worth disputing with the parcel's dimensions and a photo if you have one.
The join in SQL
A full outer join keeps parcels from both sides, so orders with no carrier line and carrier lines with no order appear in one result. DuckDB supports it directly:
SELECT COALESCE(o.tracking, c.tracking) AS tracking,
o.order_no,
o.shipping_charged,
SUM(c.total) AS carrier_total,
COUNT(c.tracking) AS times_billed
FROM orders o
FULL OUTER JOIN carrier c ON c.tracking = o.tracking
GROUP BY 1, 2, 3
HAVING COUNT(c.tracking) <> 1 OR o.order_no IS NULL
ORDER BY 1;The HAVING clause keeps only the exceptions: parcels billed twice, parcels never billed (times_billed = 0) and carrier lines without an order. For more on join types, see SQL joins across multiple tables.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Most parcels unmatched | Tracking stored with spaces, or as a number that lost leading zeros | Import tracking as text and remove spaces in both files |
| Order has two tracking numbers | Split shipment | Store one row per parcel, not per order |
| Carrier line with no order | Return label, replacement or manual shipment | Check your label-printing history before disputing |
| Charge appears months later | Late adjustment on a new invoice | Keep all invoices stacked so the parcel's full cost is visible |
| Margin negative on every order | Shipping prices in the shop not updated after a rate change | Compare average billed cost by service with the shop's rates |
Which approach fits
- A single carrier and a few hundred parcels: the lookup formulas on one sheet are enough.
- Several carriers: stack invoices with a Carrier column so you can compare cost per kilo and surcharge share across them.
- Disputes to track: add a Claim status column and a date raised, and keep the sheet open until each claim is credited on a later invoice.
To check the month is complete, add up billed amounts per invoice and compare each total with the invoice header. A missing page in a PDF-to-CSV conversion is a surprisingly common cause of a reconciliation that looks clean but isn't.
Getting the exports
In WooCommerce, the standard order export doesn't always include tracking numbers; they are often stored by a shipping or tracking plugin as order meta, so you may need that plugin's export or a custom export with the meta field added. Shopify's order export includes fulfilment details, though the exact columns depend on how labels were created. Carriers usually provide invoice detail as CSV or Excel from their business account portal; ask your account manager if only a PDF is available, since converting PDFs often splits or drops lines.
Import tracking numbers as text in both files. Long numeric tracking numbers opened directly in Excel can be turned into scientific notation and lose their last digits, which makes them impossible to match. Use Data → From Text/CSV and set the column type to Text.
Monthly checklist
- Download the invoice detail as soon as each carrier invoice arrives, while the dispute window is still open.
- Refresh the join and review the four flags: duplicate, weight adjustment, unknown parcel and negative margin.
- Raise disputes for duplicates and unexplained adjustments, and log each claim with its date and amount.
- Correct product weights or box rules in the shop for any SKU that is regularly re-weighed.
- Tick off credits from earlier claims on the new invoice and close them in the log.
Shipping margin by service
Once every parcel has a billed cost, pivot by shipping method (as the customer chose it in the checkout) and sum Shipping charged and Carrier total. If next-day delivery charges customers £6.95 on average but costs £8.40 to send, every next-day order loses £1.45 on shipping before any surcharge. That is a pricing decision, not a reconciliation error, but this is usually the only report that shows it.
Frequently asked questions
- How do I check a shipping invoice?
- Join the carrier's parcel-level invoice to your orders by tracking number, then compare weights, charges and the number of times each parcel was billed.
- Why was I charged a weight adjustment?
- The carrier measured a higher actual or volumetric weight than declared. Check the product weight and box size in your shop settings.
- How do I find duplicate carrier charges?
- Count each tracking number on the invoice with COUNTIFS; anything above 1 is billed more than once.
- What is volumetric weight?
- A weight calculated from parcel dimensions (length × width × height ÷ a carrier-specific divisor). Carriers charge the higher of actual and volumetric weight.
- How long do I have to dispute a carrier charge?
- Claim windows are set by each carrier's terms, often a few weeks after the invoice. Check yours and run this reconciliation as soon as invoices arrive.