The Product Sold Well and Still Expired in the Warehouse. I Built a System to See It in Time

A product can sell well and still expire in the warehouse. It happens all the time in a supplement eCommerce business: a second lot arrives before the first one runs out, each with its own expiration date, and the sales pace that was enough for one isn't enough for both.

In the Chilean eCommerce business I built this system for (a retail store and a wholesale one, with two warehouses of their own), nobody looked at the two things that matter together: how fast each product sells and when each of its lots expires. The problem was discovered late, when all that was left was to mark down or throw out.

---

The core idea: look lot by lot

The system splits each product's stock into its lots and sorts them with the FEFO (First Expired, First Out: what expires first sells first) rule. Then it projects, day by day and using actual sales, when each lot runs out and whether it can sell through before expiring.

Each lot has two dates:

• Sell-by deadline: the expiration minus a safety margin (90 days by default). It's an early warning and turns amber.
• Actual expiration: if the lot reaches that date with units left, that is the surplus and the product is flagged as "Heads up".

The margin is adjusted by catalog, product type, category or SKU. Lots in clearance use a zero margin: they're already being marked down and sell right up to the day of expiration. The difference between the two dates matters: passing the deadline is a warning, not a loss. If the lot still manages to sell through before expiring, it stays amber and isn't flagged as surplus.

---

How the surplus is calculated

It's a simple calculation anyone can check: what has to be sold (the lot plus those expiring before it) minus what can sell at the current daily pace in the days remaining.

Next to the surplus appears the required pace, which tells you right away whether a promotion is enough or you need to think of something else. A few rules prevent pointless alarms:

• Less than one unit doesn't count: you can't sell half a unit.
• A product with 30 days of stock and zero sales has all its stock at risk; if it arrived more recently, it has no history yet and isn't flagged.
• Each warehouse is calculated with its own lots and its own sales; the combined view tells you which warehouse the surplus comes from.

---

An average sales figure that doesn't fool you

If the sales pace is wrong, everything else is wrong. Monthly sales is the six-month average excluding the months the product was out of stock: a month without stock isn't slow sales, it's a month with nothing to sell.

Something similar happens with new products: the average starts at their first sale, not the day they arrived at the warehouse, and the card flags it as "New product". So it doesn't inflate one that sold two units and nothing more, it requires at least two days with sales and fourteen since the first one. From that pace come the months to sell out and the DSI (days sales of inventory: how many days of sales today's stock covers). With no sales, those figures stay blank, not zero.

---

The dashboard and the record

The dashboard shows the catalog as cards: stock per warehouse, turnover, DSI, the lot that expires first and the worst traffic light among its lots (expired with stock, sell fast, attention, no expiration or in order).

• Indicators on top that work as filters: "Heads up," lots in red, expired, pesos at risk.
• Filters by warehouse, traffic light, turnover, category, brand and product type, which AI classifies.
• Views you can share by link and export to Excel.

If a product meets the signal to request stock from the wholesale store, its whole card is framed in cyan so it stands out at first glance.

Tapping a card opens the record: the alert with its figures, a chart of projected stock lot by lot, 24 months of sales by channel, a lot timeline and a button to refresh that product from the ERP in seconds.

---

When the problem is in the storefront

Sometimes a product doesn't turn over because it isn't visible on the website. The system reads the online store (read-only) and, if a product turns over slowly, flags when it isn't published, is hidden, shows as sold out with stock in the warehouse or has no photo.

Whoever manages the store marks it as "I fixed it" without waiting for the sync, with a record of who and when.

---

The retail and wholesale stores, side by side

Everything is calculated using only the retail store's stock and sales. The wholesale store appears separately, as a reference, and triggers three signals:

• Request stock from wholesale: the retail store has five units or fewer and wholesale has some.
• Wholesale has stock: the retail store has less than a month of sales.
• Send stock to wholesale: the retail store has a slow product that wholesale is missing.

They're options, not orders: a person makes the decision.

---

Alerts and three brains

Alerts group the lots at risk by urgency. Each one describes its situation with a rule: expires with stock, the other warehouse sells faster, low turnover, no expiration loaded, excess stock or low stock.

• They're filtered by status, warehouse and type, and marked as handled without closing them.
• They close on their own when the problem goes away: the lot sold, was transferred or had its date corrected.
• Every morning an email arrives with the urgent items, the sold-out products wholesale has, and the website problems.

1. The algorithm does the math and always has the final word.
2. A machine learning model gives a second opinion on urgency and suggests packs; if it doesn't match, "Review" appears.
3. A language model writes it in plain words, without recommending or inventing figures.

If the AI runs out of budget, the system keeps working just the same with the algorithm.

---

Where the data comes from

Everything comes from the ERP and the system only reads. It uses available stock (net of what's reserved), syncs every hour and, on high-sales days like a Cyber event, can pull stock every 20 minutes. One button refreshes the whole catalog in seconds and another refreshes a single product. If an expiration date is wrong, it's fixed in the ERP and the next sync brings it in correctly. Margins, thresholds, channels and email recipients are changed in settings, and saving recalculates the entire catalog.

---

What the business gains

The waste that doesn't happen. A lot detected months ahead can still be moved with a promotion, a pack or a transfer; the same lot discovered a week before expiring is already a loss. And the system doesn't tell you what to do: it shows where to look and leaves the decision to the team. The series continues with the purchasing dashboard and the imports dashboard.