All Drafts

I Knew How Many Orders I Sold. I Didn't Know Where Each One Got Stuck

A system that follows each order of a Chilean eCommerce business from sale to the customer's door: it times every leg in business hours, warns you when something stalls and calculates how much was really earned.

Daniel Flores Osses
Daniel Flores Osses
Oct 20, 2026 · 7 min read

Monday, eleven in the morning. A customer writes for the third time asking about their order. The store says "Processing." In the ERP, picking has been done since Friday. The warehouse says "that one already left." On the courier's website, no movement. Twenty minutes later, nobody still knows where the order got stuck or since when.

That scene kept repeating at a Chilean eCommerce business with a retail store and a wholesale one. The data existed, but each piece in its own system: the sale in the eCommerce CMS, the preparation in the ERP, the shipping on each courier's website. Nobody could answer how long do we take to prepare? or how much of our profit does shipping take?

So I built an order tracking system. It follows each order from the store's checkout to the customer's door and warns you when it stalls.

An order is a journey with legs

The system doesn't look at an order as a status, but as a sequence of milestones with date and time, pulled from the store, the ERP and the courier. Between one milestone and the next there is a leg, and each leg is timed separately.

1SaleThe customer buys in the store2Payment verifiedThe store confirms the payment3Reaches the pickerThe warehouse takes the order in the ERP4Ready for courierPicking is validated in the ERP5DeliveredReported by the courier or picked upPaymentQueuePickingDeliverylegs in business hours
Five milestones and four legs. Queue and picking together make up preparation, the leg that depends most on the warehouse.
  • Payment: how long it takes the customer (or the credit) to be paid.
  • Queue: the wait until the warehouse takes the order.
  • Picking: how long the warehouse takes to get it ready.
  • Delivery: from when it's ready until it reaches the customer.

Canceled and returned orders are stored, but don't count in the indicators.

Business hours, not clock hours

An order paid Friday at 11 p.m. and ready Monday at 10 a.m. has 59 clock hours. But the warehouse doesn't work on weekends: it took 2 business hours. Measuring in clock hours punishes the warehouse for the calendar and hides the real problems.

Paid Friday 11:00 p.m.Ready Monday 10:00 a.m.SaturdaySundayMon1 h1 hCalendar clock59 hWarehouse business hours2 hWeekends, off-shift nights and holidays don't add up.
The calendar clock never stops; business hours only add up within the shift.

That's why there are two configurable schedules (the warehouse's, with its two shifts, and the office's) and a holiday calendar per year.

The goal: deliver on time

Each order is compared against two OTD (on-time delivery) goals: by default, 8 business hours to prepare and 24 to deliver, with different goals per region. Times are shown as an average and with their p90, the time that 90% of orders don't exceed. A normal average with a high p90 warns you there are a few very bad cases someone should look at.

Stuck orders: the alarm that was missing

An order is stuck when it has spent more business hours than expected in its current leg. The counter turns red and is visible from any screen.

Order paid, in queueStuck10Warehouse business hours since payment0 hthreshold 4 h8 hStuck: no pickerIn progressThe warehouse took it: it leaves the list on its ownWarehouse and ERPNo pickerSlow pickingMissing productStuck in the ERPTransportNot picked up by courierLong transit
The queue passes the 4-business-hour threshold and the order enters Stuck. Someone marks it in progress and, when the warehouse takes it, it leaves on its own.
  • No picker or slow picking: the problem is in the warehouse.
  • Missing product: it shipped incomplete and a balance is pending.
  • Stuck in the ERP: the order moved forward outside, but the order is missing, needs confirming or picking needs validating.
  • Not picked up or long transit: the problem is with the courier.

Thresholds are adjustable and only recent bottlenecks are looked at, to avoid false alarms. Two columns separate ERP bottlenecks from transport ones, so you know right away who to call, and each order explains which leg stalled and against which threshold it was judged. With a stuck order you can:

  • Mark it "In progress" so it stops adding to the counter.
  • Wait for it to leave on its own when the system sees progress.
  • Mark it "Resolved" with a reason when the system can't see it, and undo it if needed.

The screens

Each store is viewed separately, with a single filter bar that stays put when you switch tabs or stores. Filters live in the link: copy it and the other person sees exactly the same thing.

  • Filters: dates, search and more than fifteen criteria (courier, region, warehouse, salesperson, compliance, VAT, coupon…), with two-level grouping.
  • Summary: eight indicators (orders, total time, compliance with both goals, shipping cost, profit, discounts and stuck orders) and their charts.
  • Orders: a table with a time bar per leg in compliance colors, cost and profit for each order, and CSV export.
  • Detail: timeline, order lines with gifts and redemptions, the money step by step and the change history.
  • Shipments: every dispatch leaving a warehouse, groupable by route to see how much it costs to ship to each region.
  • Totals: sales, margin, shipping and profit added up by courier, region, coupon, day, hour or the grouping you choose.

What each shipment really costs

The courier's invoice arrives at the end of the month, the quote arrives earlier and sometimes there's only a reference rate. The system uses the best data available and replaces it as soon as a better one appears.

The first cost that exists wins1. ManualEntered by someone on the team2. Courier invoiceThe actual charge from the monthly invoice3. Quoted by APIBased on origin, destination, weight and packages4. Rate tableEstimated by courier, region and weight5. PendingNo cost: not included in profitnonewinsnonewinsstays belowSomeone entered the cost by hand: it overrides everything
The system works down the list until it finds a cost. If someone enters one by hand, that one takes over.

Each cost carries a badge with its source, and what's pending is never added as zero. It's completed by importing the courier's billing CSV, maintaining a rate table by region and weight, or entering the cost by hand on an order.

The profit on each order, no tricks

Everything is worked in net amounts, excluding VAT. The system detects whether each sale was with or without VAT by looking at the ERP document and then the store.

List salePromotionsCouponsPoints redemptionNet saleShipping chargedCOGS and giftsCourier costPayment feeProfit
Each bar subtracts (or adds) from the previous one. What's left at the end is what the order earned.
  • Net sale: what was sold minus coupons, discounts and points redemption.
  • COGS (cost of goods): ERP cost plus import costs, including gifts, which are handed out at $0 but cost money.
  • Net shipping: the courier's cost minus what was charged to the customer.
  • Payment fee: the actual fee on each payment, or a configurable rate.
  • Profit: net sale plus shipping charged, minus COGS, courier and fee.

Read-only, and it updates itself

The system doesn't modify orders, stock or shipments: it only reads them. The only things written there are manual costs, management flags and settings. Nobody can break an order from an analytics dashboard. Every 15 minutes it pulls the stores, cross-checks with the ERP, queries the couriers and recalculates. There are three roles: administrator (everything, including schedules, goals, rate table and users), user (also enters costs and resolves bottlenecks) and reader.

What the business gained

  • Knowing which orders are stalled, why and who to call, before the customer writes.
  • Measuring the warehouse and couriers with fair numbers, in business hours and against a clear goal.
  • Knowing the real profit on each order, with shipping, gifts, coupons and fees.

If you're curious how the store talks to its customers when they ask about an order, I covered it in the AI CRM draft.

An order doesn't get lost overnight. It stalls on one leg, and someone has to catch it in time.
Back to Drafts Back to Home

Want a system like this for your business? Let's talk.

Contact me