Daily reorder brief for ShipHero SKUs about to sell out

Every weekday at 7am, build me a prioritized reorder brief from ShipHero so I can see which SKUs are about to stock out while there is still time to do something about it.

Start with current stock. Use the ShipHero "List warehouse products" operation to pull every SKU's on-hand quantity, reorder level and reorder amount. Stock in ShipHero lives on the warehouse product record rather than on the product itself, so this has to be scoped to a warehouse. Run it per warehouse and say which warehouse each section of the brief covers. Page through the results with an explicit page size instead of assuming a single call returns everything.

Then work out how fast each SKU is actually moving. Use the ShipHero "List shipments" operation for the last 30 days and add up the units shipped per SKU from the shipment line items. Shipments are the best available signal for real outbound demand. Daily velocity is total units shipped in the window divided by 30. Days of cover is on-hand quantity divided by daily velocity.

Only include SKUs with fewer than 21 days of cover. If a SKU had no shipments at all in the last 30 days, treat its cover as unlimited and leave it out of the ranking, unless its on-hand quantity is already at or below its reorder level. In that case list it in a short separate section labelled as no recent movement but below reorder level, so I can decide what to do without it crowding out the genuinely urgent items.

For the recommended reorder quantity, work out what it takes to get the SKU back to roughly 45 days of cover: daily velocity multiplied by 45, minus current on-hand, rounded up to a sensible whole number. If ShipHero has a reorder amount set for that SKU and it is larger than your calculation, use the reorder amount instead. Never recommend less than the configured reorder amount.

Group the results by vendor, using the ShipHero "List vendors" operation to resolve the vendor on each product, so that one purchase order can cover several SKUs at once. Order the vendor groups by whichever contains the soonest stockout, and within each group rank SKUs by days of cover ascending.

Post the brief to Slack with the "Send a Message" operation. Open with a one line summary: how many SKUs are at risk, across how many vendors, and the single soonest stockout. Then one section per vendor, and one row per SKU showing SKU, product name, on-hand, daily velocity, days of cover and recommended reorder quantity. Keep it scannable in a Slack channel rather than writing prose, and close by telling me I can reply approving a vendor by name.

Append the same rows to a Google Sheet with the "Append Values" operation, one row per SKU per run, with columns for run date, warehouse, vendor, SKU, product name, on-hand, daily velocity, days of cover and recommended quantity. This is how we build a restock history over time, so keep the column order stable between runs.

Do not create any purchase order automatically. Wait for me to reply. When I reply approving a specific vendor's list, use the ShipHero "Create purchase order" operation against that vendor with the approved SKUs and their recommended quantities. If I approve with changes, such as different quantities or dropping a SKU, use my numbers rather than yours. Once the purchase order exists, confirm back in the same Slack thread with the purchase order number and the vendor it went to. If I never reply, do nothing and let the next run pick it up.

One practical note: the ShipHero API is credit based and will throttle heavy runs. Request sensible page sizes, and if a throttling error comes back carrying a wait time, wait that long and retry rather than failing the whole brief.