Case Study · Retail
Defacto NoSales: measuring stock that has not sold for 28 days, every week, by the same rule
A weekly system that checks, for every option × store pair with stock on Sunday, whether there was a sale in the last 28 days, and reports idle stock by store, product and region. Scheduled queries in BigQuery, a five-page Power BI dashboard, a trend since 2021.
- 28 days
- Rule
- Monday 05:30
- Run
- 5 pages
- Dashboard
- 6
- Metrics
Stock on Sunday, no sale for 28 days
4 scheduled queries, delete / fill
Last 8 weeks + since 2021
Total, No-Sales, LW, %, LW %, Diff %
Problem
A product that sits in a store without selling costs twice: it occupies the shelf and melts away in the season-end markdown. But the definition of “not selling” varies from store to store and person to person. Defacto needed a system that measures idle stock every week by the same rule, shows which store and which product is the problem from different angles, and tracks the effect of the action taken over time.
The rule
The definition is one sentence: an option with stock on Sunday that has not sold in that store for the last 28 days is NoSales. The setup runs only for active season, new season and NS products — out-of-season stock is the subject of a different process. The same rule is applied separately to e-commerce stock.
The rule’s simplicity is deliberate: when everyone understands the same number, the discussion at the dashboard moves from “is this product really not selling” to “what do we do”.
System
Data comes from the data lake on Google Cloud: calendar, daily store stock, hourly sales, season, store and product dimensions, e-commerce stock history. Four scheduled BigQuery queries run every Monday between 05:30 and 06:00:
| Query | Scope | Refresh |
|---|---|---|
| Store NoSales | All data | Last 8 weeks delete / fill |
| E-commerce NoSales | All data | Last 8 weeks delete / fill |
| Store · region | Last 9 weeks | Option × region |
| E-commerce · region | Last 9 weeks | Option × region |
The result tables feed five pages in Power BI: a summary of the last eight weeks, analysis by store and product, store–product analysis at option level, a store-based summary since 2021 and a region-based summary. The metrics shown: total stock, NoSales stock, stock that has been NoSales for two weeks, NoSales percentage, last week’s percentage and the difference.
Outcome
Because idle stock is measured by the same rule every week, the trend means something: if the ratio is falling in a store, the action taken is working. The NoSales page was also carried as-is into the JUMP-UP executive dashboard. Lumtify coded the project, moved it to BigQuery and maintained it.