Broken size run sheet for clothing and shoe shops: which sizes each style has sold out of, from the shop's own product data

Updated 24 Oct 2026 · Disclosure: the product data scraper linked below is an Unadhi product on the Apify Store. This guide is free, and the idea also works with the free exports from your own Shopify admin. Current Apify pricing is only on the Store page. This page has no affiliate links and nobody paid to be mentioned.

The finished sheet

sizerun tab (example rows are made up; core sizes in settings: S, M, L)

The cotton tee row shows why the sheet works per variant, not per product: the product is still "in stock" because five of six variants are, but a customer looking for a white medium finds nothing.

Who this is for

Owners and merchandisers of small clothing, footwear and lingerie shops that sell online, and buyers at multi-brand boutiques who want to see which sizes a brand's own web shop has run out of. A style that has sold out of its middle sizes but still has XS and XXL looks "in stock" on a product list, yet it no longer sells. This sheet puts those styles at the top.

Step 1: get the products into a tab named scrape

With the product data scraper

Open the E-commerce Product Data Scraper (Shopify, WooCommerce, any shop) on the Apify Store and use this input with the shop's address:

{ "startUrls": [{ "url": "https://your-shop.example" }],
  "maxProductsPerShop": 2000, "includeDescription": false }

• maxProductsPerShop: set it above the shop's number of products (the default is 100). You pay only for products delivered.
• includeDescription: this sheet does not need descriptions, so switch it off to keep the file small.
• You can also give a single collection or category page, for example only the dresses collection.

The scraper follows robots.txt and reads the shop's terms page. If the terms forbid automated access (Shopify's default terms do), it skips the shop and charges nothing. Check the run's Storage → Key-value store → SHOPS_SUMMARY: the shop should show ok. If it shows blocked-by-terms, use the free admin route below for your own shop, and leave other shops alone.

In the run, export the dataset as CSV. In your Google Sheet use File → Import → Upload, "Insert new sheet", and name the tab scrape. Do not rename the headers.

What the variant columns contain

• Shopify shops (read from the shop's public product JSON): one set of columns per variant, variants/0/title, variants/0/available, variants/1/title and so on. The variant title is the option values joined with a slash, for example White / M. available is TRUE or FALSE. It says whether the variant can be bought, not how many are left.
• Shops that publish schema.org product data with variants: the same columns, with the variant name the shop publishes. Some shops write names like Tee - M; see the note on core sizes below.
• WooCommerce shops (read from the WooCommerce Store API): one row per product with inStock, but no variant columns. These rows show "In stock, no size data" or "Sold out".
• A Shopify product with only one variant has the variant title Default Title.

Free, from your own Shopify admin

Products → Export gives one row per variant with the option values (Option1 Value, Option2 Value and so on), and the inventory export gives stock per variant and location. With those you can count sold-out sizes per product in a pivot table instead of using the formulas below. Your admin also shows quantities, which public data does not.

Step 2: the settings tab

Add a tab named settings. In B1 type your core sizes, separated by commas, exactly as they appear in variant titles: for example S, M, L, or 8, 10, 12, 14, or UK 6, UK 7, UK 8. A style missing any of these counts as a broken run.

Step 3: build the sizerun tab

Add a tab named sizerun. Row 1 holds the headers below; row 2 holds the formulas, which you copy down one row per row of scrape.

Notes on the formulas:

• Sold out pairs each variants/N/title with its variants/N/available. Every variant has both columns, so the two lists line up.
• Core sizes missing wraps the sold-out list in slashes and looks for / M /, so L does not match XL and White / M counts as a missing M. SEARCH ignores upper and lower case. If a shop names variants like Tee - M, change the slashes in the formula to the shop's separator, or check column F by eye.
• A missing core size in one colour counts as a broken run for the whole product, because column F tells you which colour it is.
• Status uses the product's own inStock first, so a fully sold-out product shows "Sold out" even when it has no variant columns.

Step 4: the Monday routine

1. Run the scraper again (or re-export from your admin) and re-import the scrape tab with "Replace current sheet". The sizerun formulas and your Next step notes stay, but check that row order still matches if products were added; sorting scrape by title before importing keeps it stable.
2. Add a filter on sizerun and show only "Broken run". These are the styles to reorder first.
3. Styles that stay "Broken run" for several weeks and that you will not reorder are markdown candidates: the remaining sizes are what customers can still buy.
4. For a brand you stock, a broken run on the brand's own web shop can mean the brand itself is short of those sizes. Ask your rep before you plan a reorder around it.

Check before you act on it

• SHOPS_SUMMARY shows ok for the shop, or you used your own admin export.
• settings!B1 sizes are spelled exactly as in the variant titles.
• Rows marked "In stock, no size data" are WooCommerce or similar shops: check sizes on the product page.
• Public data says whether a size can be bought, not how many are left. For your own shop, confirm quantities in your admin before you reorder.

Partner programme opening soon

Partner programme opening soon: join the free waitlist. Planned terms are 50% of each referred sale (75% once you have 5+ paying customers), single level, paid through Gumroad only from revenue received.

Sources

• Product data scraper inputs and outputs (startUrls, maxProductsPerShop default 100, includeDescription; Shopify variants with title and available from the public product JSON; schema.org hasVariant names and availability; WooCommerce Store API rows with inStock and no variants; SHOPS_SUMMARY statuses; skipped shops not charged): Apify Store page (Actor input schema, README and source read 24 Oct 2026)
• Google Sheets FILTER, REGEXMATCH, TEXTJOIN, SEARCH, SPLIT and LET functions: Google Docs Editors Help.

Related

• Competitor store: out of stock and new products, weekly
• Wholesale line sheet from your own Shopify or WooCommerce store
• Brand stockist price and stock check sheet

← All guides

Unadhi Guides are free, independent explainers. We correct errors as soon as we find them: write to mininglife7@gmail.com.