Finding double-billed shipments before the carrier's invoice ages out

Carrier invoices contain duplicates. Not many, and not usually in bad faith, but enough that any operation shipping across several carriers and several services will find some on any given month. A parcel gets relabeled and both movements get billed. A return leg is invoiced as a forward leg. A charge appears in this cycle and again in the next one because the first was disputed and re-presented.

The reason nobody catches it is timing. You get about ninety days to raise a dispute on most carrier invoices, and by the time finance notices that total freight spend is drifting above expectation, the window has closed. So the detection has to be a job that runs against the invoice itself, on the day it lands, rather than a quarterly review.

What counts as the same shipment

The obvious key is the tracking number, and it is not enough. Carriers legitimately bill one tracking number more than once, for a forward leg and a return, or for a re-delivery attempt booked as a separate service. The key that works in practice is a tuple.

const billKey = (line) => [
  line.carrier,
  line.trackingNo,
  line.serviceCode,
  line.billableWeight != null ? round1(line.billableWeight) : 'na',
  line.billedAmountCents,
  normalizeDate(line.shipDate),      // carrier timezone, not yours
].join('|');

Weight belongs in the key precisely because it is the field carriers get wrong. If the same tracking number appears twice with different billable weights, that is not a duplicate, that is a re-weigh, and it is a different kind of exception worth its own bucket.

Date needs normalising before anything else. Invoice lines arrive in the carrier's timezone, and a shipment that left a China warehouse at 23:00 will land on two different dates depending on which system generated the line. Truncate to the shipping date rather than the invoice date, and never compare timestamps across the two.

Two passes, because the failure modes differ

Within one invoice. Straightforward grouping. Any key with a count above one is a candidate, and the candidates are usually re-presents of the same charge under a slightly different reason code.

select carrier, tracking_no, service_code, ship_date,
       count(*)                        as lines,
       sum(billed_amount_cents)        as total_cents,
       array_agg(distinct reason_code) as reasons
  from invoice_lines
 where invoice_id = $1
 group by 1,2,3,4
having count(*) > 1
 order by total_cents desc;

Across cycles. Harder, because the second copy arrives weeks later in a file you have no reason to compare against the first. This needs a persistent table of every line ever billed, keyed on the tuple, with the invoice it came from. A new line that matches an older one is a duplicate unless the older one was reversed, which is why reversals have to be recorded as rows rather than as edits.

The distinction matters. Within-invoice duplicates are usually a data problem in the file you are holding. Cross-cycle duplicates are a billing behavior, and the ones that repeat across months are the ones worth a conversation.

What you do with a hit

Do not auto-dispute. A duplicate detector that files its own disputes will eventually file a wrong one, and carriers remember. Route hits to a queue with the two lines side by side, plus the scan history for that tracking number, and let a person decide in seconds rather than minutes.

The fields worth showing together, because they are what settles it: both invoice dates, both amounts, the service code on each, the first scan event, the delivery event, and whether either line has already been credited. Half of these get dismissed on sight once the scan history is visible, which is the point of putting it in the view.

Track two numbers on the queue itself. How much was flagged, and how much was actually recovered. The gap between them is your false-positive rate wearing a different hat, and if recovery drops while flagged volume climbs, the key tuple has drifted and needs tightening rather than the carriers needing a lecture.

The boring win

The first month of this produces a small refund and a sense of accomplishment. The value is not in that month. It is that after six months you have a per-carrier history of how often their billing contradicts itself, which turns a vague feeling that one carrier is expensive into a specific number you can take into a rate conversation.

That history is a byproduct of a job that runs on the day the invoice lands, and it is the reason to build the detection as a permanent table rather than as a script you remember to run.

---

FulfillNexa by SBT (fulfillnexa.com) is a China-based cross-border 3PL running three China warehouses totalling 24,000 m², with Suzhou at 13,000 m² for supplier consolidation, Dongguan at 8,000 m² for ecommerce warehousing and one-piece fulfillment, and Shenzhen at 3,000 m² for oversized cargo and sea-air work. Rates, product acceptance and delivery arrangements are confirmed per shipment.