How to automate inventory reorder alerts

What this automation does

Every morning the buyer gets one short list of SKUs that need ordering, how many to order, and why. No one has to scan a spreadsheet of thousands of rows.

1. Snapshot. Each night the workflow reads on-hand, committed and incoming quantities for every active SKU from Shopify, WooCommerce, QuickBooks Online or your warehouse system and saves a dated copy.
2. Calculate velocity. It computes average daily units sold over a trailing window, commonly 28 days, and leaves out days the item was out of stock so a stockout does not drag the average down.
3. Set the reorder point. Reorder point equals average daily demand times supplier lead time in days, plus safety stock. Inventory position equals on hand plus on order minus committed.
4. Alert. When inventory position drops to or below the reorder point, the SKU goes into the digest with days of cover remaining and a suggested quantity, rounded up to the case pack and the supplier’s minimum order.
5. Explain. When something looks unusual, such as velocity doubling against the prior period or a supplier running late on recent orders, a model writes a one-line note so the buyer does not blindly trust the number.
6. Draft the order. The buyer approves lines in Slack. The workflow groups approved lines by supplier and drafts a purchase order or a supplier email for review.

When it is worth building

This pays off once you have more SKUs than one person can eyeball, several suppliers with different lead times, and a history of either stockouts on best sellers or cash tied up in slow movers. A few hundred active SKUs is roughly where spreadsheets start to fail.

It is not worth building if your ERP already has reorder planning you have not configured. NetSuite and similar systems include reorder features, and turning those on is cheaper than a parallel system. For Shopify stores with straightforward catalogs, inventory planning apps in the Shopify App Store may cover the need for a monthly fee. Build when your data spans systems (store plus warehouse plus accounting), when you need custom rules per supplier, or when you want alerts and approvals inside the tools your team already uses.

Architecture

The choice between a spreadsheet and a database matters more here than in most automations, because snapshot history grows every day. Our Airtable vs Postgres comparison covers where each one stops scaling.

How to build it

1. Clean the SKU list. Archive discontinued items, standardize variant SKUs, and list every bundle or kit with its component SKUs so bundle sales draw down the right parts.
2. Build the supplier table. For each SKU, record supplier, quoted lead time, minimum order, case pack and cost. Add a column for actual lead time, filled from receipt dates later.
3. Start snapshots before alerts. Sales history can usually be rebuilt from past orders, but past stock levels often cannot. Turn on nightly snapshots in week one.
4. Implement the formulas. Begin with a simple safety stock, such as a fixed number of days of demand per supplier. Move high-volume SKUs to a statistical safety stock based on demand variability once you have enough history.
5. Design the digest. Group by supplier, sort by days of cover, and show the math for each line. Buyers trust numbers they can check.
6. Add AI notes carefully. Pass the model only computed figures and flags. It explains, it never changes a quantity.
7. Connect approval to ordering. Approved lines become a purchase order in QuickBooks Online that the buyer reviews before sending (purchase orders require the Plus or Advanced plan), or a supplier email the buyer sends.
8. Track actual lead times. When stock arrives, record the real lead time and use a rolling average in the formula instead of the supplier’s quote. If you already match purchase orders to receipts and invoices, the receipt date is already captured there.

The downloadable starter template below gives you the nightly snapshot, reorder point calculation and Slack digest as a starting point.

What breaks in production

• The stockout spiral. If out-of-stock days count as zero sales, velocity falls, the reorder point falls, and you under-order again. Exclude zero-stock days from the average.
• Bundles and kits. Selling a gift set that contains three products must reduce three component SKUs. Missing bundle mappings is the most common source of wrong suggestions.
• Multiple locations. Total stock can look healthy while one warehouse is empty. Calculate per location when you fulfill from more than one.
• Lead time drift. A supplier who quoted 14 days now takes 30. Without actual receipt dates feeding back in, alerts arrive too late.
• Promotions and seasonality. A trailing average lags behind a promotion or a seasonal ramp. Let marketing flag planned promotions so affected SKUs get a temporary uplift.
• Negative inventory. Pre-orders and backorders can push on-hand below zero in some systems. Handle negatives explicitly or the math produces nonsense.

Timeline and cost

A single-location setup reading from one system typically takes two to three weeks. Lumeor builds this in the range of $4,000 to $10,000, depending on the number of locations, how clean the SKU and bundle data is, and whether approved lines create purchase orders directly. Running costs are small, mostly workflow hosting, because the model is only called for flagged SKUs and email drafts.

How to measure success

• Stockout days per month on your top 50 SKUs
• Days of inventory on hand, overall and for slow movers
• Buyer hours per week spent on reorder decisions
• Share of suggested quantities approved without edits
• Expedited or rush orders placed to cover gaps
• Value of stock with no sales in 90 days