automations.help
Book a teardown

Inventory & suppliers

Flag slow-moving inventory for clearance

Score every SKU by how many days of stock you're holding at its current sell rate, rank the slow movers by the cash they've tied up, and drop a ranked clearance list in a Google Sheet every week. You decide the lever — markdown, bundle, ad push, or discontinue — instead of discovering the problem at year-end inventory count.

Difficulty
Intermediate
Time to build
3-6 hrs
Updated
  • Shopify
  • Google Sheets

The manual way today

Slow stock doesn't announce itself. Once a quarter — or worse, once a year at physical count — someone exports a Shopify inventory CSV, eyeballs which products 'feel' stale, maybe cross-references a sales report in another tab to confirm, and pulls a handful onto a clearance shelf. The SKUs that quietly sell two units a month never make the list because nothing flags them; they just sit.

Every unit of dead stock is cash you already spent, sitting on a shelf earning nothing, often while you're reordering the products that actually move. It's storage you're paying for, working capital you can't redeploy, and — for anything perishable, seasonal, or trend-driven — a markdown that gets deeper the longer you wait. None of it shows up as a line item. It shows up as a cash-flow squeeze nobody can quite explain.

What running looks like

Every week the system reads each SKU's trailing sales and current on-hand from Shopify, computes days-of-supply and the cash tied up in it, and writes a ranked clearance-candidate list to a Google Sheet — slowest, most cash-heavy movers at the top, with last-sale date and a suggested action beside each. New arrivals, seasonal lines, and anything you've tagged to leave alone are filtered out automatically. A person reviews the short list and picks the lever; nothing gets marked down on its own.

01The build

How to build it

  1. Define what 'slow-moving' means for your store

    Needs a human

    Pin the policy before automating it, because 'slow' is a judgment call, not a constant. Set a trailing window to measure sell rate over (90 days is a sane default; use 180 for low-frequency or high-ticket lines). Set the days-of-supply line that makes a SKU a candidate — e.g. more than 120 days of cover at the current rate. Set a minimum cash-tied-up floor so a $40 pile of stock doesn't clutter the list next to a $4,000 one. And list the exclusions: a 'seasonal' tag, an 'evergreen' or 'always-stock' tag for staples you'll never clearance, and a rule to skip any SKU younger than the trailing window.

  2. Set up the Google Sheet as the workbench

    Needs a human

    Two tabs. A 'SKU settings' tab the merchandising owner controls: one row per SKU with any per-product overrides — an exclude flag, a custom threshold, a note like 'discontinuing, let it run out'. A 'Clearance candidates' tab the automation writes to: SKU, product title, on-hand units, units sold in the window, days-of-supply, unit cost, cash tied up, last-sale date, and a suggested action. Apps Script reads the settings tab and owns the candidates tab, so a human's overrides never get overwritten by a refresh.

  3. Pull trailing sales per SKU from Shopify on a schedule

    Runs itself

    In the Sheet's Apps Script editor, a time-driven trigger fires weekly. Query Shopify's Admin GraphQL orders for the trailing window (orders(query: "created_at:>=YYYY-MM-DD status:any")), page through them, and sum lineItems.quantity per SKU — keying on SKU, not product title, which gets edited and isn't unique. On a large catalog this is what Shopify's bulkOperationRunQuery is for: one async export instead of thousands of paged calls. Net result: one trailing-units number per SKU.

  4. Pull current on-hand, unit cost, and product age

    Runs itself

    Walk productVariants for sku, inventoryQuantity, the product's createdAt and tags, and inventoryItem.unitCost.amount — Shopify already stores your cost per item if you've filled that field in, so the cash-tied-up math comes straight from the source. createdAt is your proxy for how long the SKU has been sellable, which is how the next step keeps a three-week-old product off the list. No cost on file? Fall back to retail value and label the column so nobody mistakes it for true cash at risk.

  5. Compute days-of-supply and cash tied up

    Runs itself

    For each SKU: daily rate = units sold in window ÷ window length; days-of-supply = on-hand ÷ daily rate; cash tied up = on-hand × unit cost. Handle the two divide-by-zero cases honestly — a SKU with on-hand but zero sales in the window is dead stock (flag it as 'no sales in {window}', not 'infinity days'), and a SKU with zero on-hand isn't slow-moving at all, it's just empty, so drop it. Carry last-sale date through too; 'sold its last unit 140 days ago' is the line that makes a candidate undeniable.

  6. Filter out the false positives

    Runs itself

    A raw days-of-supply list is full of things you shouldn't clearance. Drop any SKU younger than the trailing window — it hasn't had a fair chance to sell. Drop anything carrying a 'seasonal', 'evergreen', or 'always-stock' tag, or flagged exclude on the settings tab. Watch the stockout blind spot: a SKU that was out of stock for most of the window and just got restocked will look slow because it had nothing to sell — the API won't hand you exact in-stock days, so treat a recently-created-or-restocked SKU as low-confidence and lean on the tag and override columns to suppress it.

  7. Write the ranked candidate list to the Sheet

    Runs itself

    Take what survives the filter, sort by cash tied up descending — the point is to free working capital, so the $4,000 slow mover ranks above the $400 one even if the smaller one is technically slower — and write it to the 'Clearance candidates' tab. Add a plain-English suggested action per row from simple rules: deep days-of-supply plus high cost → 'markdown'; small quantity of a discontinued line → 'bundle or liquidate'; healthy margin but no visibility → 'feature in ads/email before discounting'. Clear and rewrite the tab each run so it always reflects today's position.

  8. Put the short list in front of a person to decide the lever

    Needs a human

    This is the gate, and it stays human on purpose. The Sheet shows ten or fifteen ranked candidates, not a thousand SKUs — small enough to actually act on. A person picks the move per row, because the right lever depends on things no report knows: a restock already on the water, a wholesale order about to clear it, a brand rule against ever discounting a hero product. The automation surfaces and ranks; it never decides to mark your margin down.

  9. Tag approved SKUs back in Shopify so clearance runs itself

    Runs itself

    Once a row is approved (a checkbox on the candidates tab), Apps Script writes a 'clearance' tag to that product via the Shopify Admin API. Point an automated collection at that tag and your clearance page fills itself; pair the tag with an automatic discount or a Shopify price-list rule if you want the markdown applied too. When the SKU sells through or you pull it from clearance, the tag comes off on the next run. The flag becomes the switch — set once, and the storefront follows.

02The stack

What it’s built on

Shopify
System of record for everything the score needs: trailing orders for sell rate, live on-hand per SKU, unit cost from the inventory item's cost-per-item field, and the product tag that drives a clearance collection on the storefront.
Google Sheets
The workbench and the glue. One tab holds your thresholds and per-SKU overrides; another holds the ranked candidate list. Apps Script's time-driven trigger runs the Shopify pulls, the days-of-supply and cash math, and writes the output — no separate automation tool needed.Free with any Google account, and Apps Script is included at no cost. It's the honest pick when you want to read and tune the logic yourself. On a very large catalog (10k+ SKUs) you can hit Apps Script's runtime limits — that's the point to batch with Shopify bulk operations or move the math to a real database.

03Questions

Before you build

What actually counts as 'slow-moving'?

Days-of-supply over a line you set — not just 'hasn't sold lately'. The build divides current on-hand by the daily sell rate over your trailing window, so a SKU with 400 units selling two a day is carrying ~200 days of stock and gets flagged, while one with 20 units selling two a day (10 days of cover) doesn't. Pure 'no sales' SKUs are caught separately as dead stock. You set the threshold, the window, and a minimum cash floor, so 'slow' means what it means for your margins.

Won't this flag new products that just haven't had time to sell?

No — that's an explicit filter. Any SKU younger than your trailing window is dropped, because measuring a 90-day sell rate on a three-week-old product is noise. The build reads each product's created date from Shopify and excludes the new ones automatically, so a launch you're still ramping never lands on the clearance list.

How is this different from a low-stock alert?

It's the opposite problem. A low-stock alert tells you a fast seller is about to run out so you can reorder. This tells you a slow seller has far more stock than its sell rate justifies, so you can free the cash. Same inputs — sales and on-hand — read from the other end. Many stores run both; this one's job is the overstock side nobody watches until count.

Do I need to know my product costs for this to work?

It works better with them and it's worth the setup. Shopify already has a 'cost per item' field on each variant; fill it in and the build ranks candidates by real cash tied up, so you clear what frees the most working capital first. No costs on file? It falls back to retail value to rank by — still useful for spotting slow movers, just less precise about the dollars at stake, and the column is labeled so you don't confuse the two.

Will it mark products down or change prices on its own?

No. It scores, ranks, and suggests — a person approves each row before anything happens. Even the optional last step only adds a 'clearance' tag once you've checked the box; whether that tag triggers a markdown is a Shopify rule you control. The markdown decision is exactly where local knowledge belongs, so it's never automatic.

Can I build this myself, or should you build it?

Every threshold, query, and edge case is written out above on purpose — if you're comfortable in Apps Script and Shopify's API and have an afternoon, build it. If you'd rather not own the days-of-supply math, the stockout blind spot, and the cost-basis ranking, we'll build it on the Shopify and Google accounts you already use, document it, and hand you the keys. You own the workflow outright — no monthly retainer, no platform lock-in. It starts with a free 20-minute teardown where we map your dead stock and busywork, not a contract.