ReplAi-Now/shopify-skills / skills/operations/dead-stock-clearance
dead-stock-clearance
VerifiedFind slow and dead stock, the cash stuck in it, and a clearance plan you approve before anything changes. Use for "dead stock", "slow movers" or "what should I put on sale".
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 11.0 KB |
| assets/first-stocked.example.csv | other | 63 B |
| assets/setup.example.json | config | 1.1 KB |
| references/data-sources.md | markdown | 5.2 KB |
| references/method.md | markdown | 7.7 KB |
| references/shopify-apply.md | markdown | 5.7 KB |
| scripts/build_dashboard.py | script | 14.9 KB |
| scripts/dead_stock.py | script | 31.1 KB |
| scripts/store_data.py | script | 12.0 KB |
| scripts/tabular.py | script | 6.2 KB |
scripts/store_data.py
View on GitHub"""Load Shopify exports (orders, products, inventory) into simple per-variant records.
Imported by the other scripts in this folder (Python 3 standard library only):
from store_data import load_orders, load_variants, parse_date
Columns are matched after tabular.norm() (lowercase, spaces become "_").
Shopify writes order level fields (Created at, Cancelled at, Subtotal) only on the
first row of each order and product level fields (Title, Type, Tags, Vendor) only on
the first row of each product, so both are carried forward here.
Both the 2026 Shopify export headers and the older ones are read: URL handle / Handle, SKU /
Variant SKU, Price / Variant Price, Compare-at price / Variant Compare At Price, Inventory quantity /
Variant Inventory Qty, Option1 value / Option1 Value / Option 1 Value (inventory export), and
Canceled at / Cancelled at (orders export).
Stock is returned as exported: negative numbers (oversold) are kept so callers can flag them.
Variants are keyed by SKU (upper case). A variant without a SKU is keyed by
"NAME:<product title> - <option values>", which is how Shopify writes "Lineitem name".
A SKU used by several products is kept once (the first product's price and cost, stock
added up) and the other handles are listed in "duplicate_handles".
Refunds: an order whose Financial Status is "refunded" or "voided" gets "refunded": True and
callers drop it like a cancelled one (use counts_as_sale). For "partially_refunded" orders each
line's "qty" is the ordered quantity minus the refunded quantity when the file has a refunded
quantity column (Lineitem refunded quantity); without one the line is kept whole and the order
gets "partial_refund_unknown": True so the caller can say so.
"""
import datetime as dt
import re
from tabular import read_rows, num, norm
class DataError(ValueError):
"""A required column or file is missing. The message says which one."""
def col(row, *names):
"""First non-empty value for any of the names. Also matches Shopify headers
with a suffix in brackets, e.g. 'available' matches 'available_(not_editable)'."""
for n in names:
n = norm(n)
v = row.get(n)
if v not in (None, ""):
return v
for k, v in row.items():
if k.startswith(n + "_(") and v not in (None, ""):
return v
return ""
def has_col(rows, *names):
keys = set()
for r in rows[:50]:
keys.update(r.keys())
for n in names:
n = norm(n)
if n in keys or any(k.startswith(n + "_(") for k in keys):
return True
return False
def parse_date(value, dayfirst=True):
"""ISO dates (2026-10-28, '2026-10-28 14:05:00 +0200'), DD/MM/YYYY (or MM/DD/YYYY with
dayfirst=False, as Excel writes them on a US locale), or an Excel serial. None if invalid."""
s = str(value or "").strip()
if not s:
return None
try:
m = re.match(r"(\d{4})-(\d{1,2})-(\d{1,2})", s)
if m:
return dt.date(int(m.group(1)), int(m.group(2)), int(m.group(3)))
m = re.match(r"(\d{1,2})/(\d{1,2})/(\d{4})", s)
if m:
a, b, y = int(m.group(1)), int(m.group(2)), int(m.group(3))
day, month = (a, b) if dayfirst else (b, a)
if month > 12 >= day: # cannot be read the chosen way: swap
day, month = month, day
return dt.date(y, month, day)
if re.fullmatch(r"\d{5}(\.\d+)?", s):
return dt.date(1899, 12, 30) + dt.timedelta(days=int(float(s)))
except ValueError:
return None
return None
def slash_dayfirst(values):
"""Decide whether slash dates in one column are DD/MM (True) or MM/DD (False).
MM/DD when some value has a second part above 12 and none has a first part above 12."""
first_big = second_big = False
for v in values:
m = re.match(r"\s*(\d{1,2})/(\d{1,2})/\d{4}", str(v or ""))
if m:
first_big = first_big or int(m.group(1)) > 12
second_big = second_big or int(m.group(2)) > 12
return not (second_big and not first_big)
def name_key(title, variant_title=""):
t = (title or "").strip().lower()
v = (variant_title or "").strip().lower()
return "NAME:" + (t + " - " + v if v and v != "default title" else t)
def variant_key(sku, title="", variant_title=""):
sku = (sku or "").strip()
return sku.upper() if sku else name_key(title, variant_title)
REFUND_QTY_COLS = ("lineitem_refunded_quantity", "lineitem_refunded_qty", "refunded_quantity", "refunded_qty")
FULL_REFUND_STATUSES = ("refunded", "voided")
def counts_as_sale(order):
"""False for cancelled orders and fully refunded or voided orders."""
return not order["cancelled"] and not order["refunded"]
def load_orders(path):
"""Return a list of orders: {name, date, cancelled, refunded, financial_status, partial_refund_unknown,
subtotal, lines: [{key, sku, name, qty, ordered_qty, price}]}. "qty" is net of refunded units."""
rows = read_rows(path)
if not rows:
raise DataError(f"{path}: no rows found")
for need, alts in (("Name", ("name", "order", "order_name")),
("Created at", ("created_at", "date", "order_date")),
("Lineitem quantity", ("lineitem_quantity", "quantity", "qty"))):
if not has_col(rows, *alts):
raise DataError(f"orders file is missing the column '{need}'")
if not has_col(rows, "lineitem_sku", "sku", "lineitem_name"):
raise DataError("orders file needs 'Lineitem sku' or 'Lineitem name'")
dayfirst = slash_dayfirst(col(r, "created_at", "date", "order_date") for r in rows)
has_refund_qty = has_col(rows, *REFUND_QTY_COLS)
orders, by_name = [], {}
for r in rows:
name = col(r, "name", "order", "order_name")
if not name:
continue
o = by_name.get(name)
if o is None:
o = {"name": name, "date": None, "cancelled": False, "refunded": False, "financial_status": "",
"partial_refund_unknown": False, "subtotal": None, "lines": []}
by_name[name] = o
orders.append(o)
if o["date"] is None:
o["date"] = parse_date(col(r, "created_at", "date", "order_date"), dayfirst)
if col(r, "cancelled_at", "canceled_at"):
o["cancelled"] = True
fin = col(r, "financial_status").strip().lower().replace(" ", "_")
if fin and not o["financial_status"]:
o["financial_status"] = fin
o["refunded"] = fin in FULL_REFUND_STATUSES
o["partial_refund_unknown"] = fin == "partially_refunded" and not has_refund_qty
sub = col(r, "subtotal")
if sub and o["subtotal"] is None:
o["subtotal"] = num(sub)
qty = num(col(r, "lineitem_quantity", "quantity", "qty"))
sku = col(r, "lineitem_sku", "sku")
lname = col(r, "lineitem_name")
if qty <= 0 or not (sku or lname):
continue
key = sku.strip().upper() if sku.strip() else "NAME:" + lname.strip().lower()
net = qty - num(col(r, *REFUND_QTY_COLS)) if has_refund_qty else qty
if net <= 0:
continue # every unit of this line was refunded
o["lines"].append({"key": key, "sku": sku.strip(), "name": lname, "qty": net, "ordered_qty": qty,
"price": num(col(r, "lineitem_price", "price"))})
return [o for o in orders if o["date"] is not None]
HANDLE_COLS = ("handle", "url_handle")
def _options(r):
vals = [col(r, f"option{i}_value", f"option_{i}_value") for i in (1, 2, 3)]
vals = [v for v in vals if v and v.lower() != "default title"]
return " / ".join(vals)
def _blank(key, sku, handle, title, vtitle):
return {"key": key, "sku": sku, "handle": handle, "title": title, "variant_title": vtitle,
"type": "", "vendor": "", "tags": "", "price": None, "compare_at": None, "cost": None,
"available": None, "on_hand": None, "incoming": None, "status": "",
"variant_id": "", "product_id": "", "duplicate_handles": []}
def load_variants(products=None, inventory=None):
"""Merge Products > Export and Products > Inventory > Export into {key: record}.
Inventory numbers (summed over locations) win over 'Variant Inventory Qty'."""
recs = {}
if products:
rows = read_rows(products)
if not rows:
raise DataError(f"{products}: no rows found")
if not has_col(rows, *HANDLE_COLS):
raise DataError("products file is missing the column 'URL handle' (older exports: 'Handle')")
carry = {}
for r in rows:
h = col(r, *HANDLE_COLS)
if not h:
continue
c = carry.setdefault(h, {"title": "", "type": "", "vendor": "", "tags": "", "status": ""})
for k, names in (("title", ("title",)), ("type", ("type", "product_type", "product_category")),
("vendor", ("vendor",)), ("tags", ("tags",)), ("status", ("status",))):
v = col(r, *names)
if v and not c[k]:
c[k] = v
sku = col(r, "variant_sku", "sku")
price = col(r, "variant_price", "price")
if not (sku or price or col(r, "option1_value", "option_1_value")):
continue # image-only row
vt = _options(r)
key = variant_key(sku, c["title"], vt)
if key in recs and recs[key]["handle"] != h:
# Same SKU on another product: orders cannot tell them apart. Keep the first
# product's price and cost, add up the stock, and list the other handle.
dup = recs[key]
if h not in dup["duplicate_handles"]:
dup["duplicate_handles"].append(h)
q = col(r, "variant_inventory_qty", "inventory_quantity", "inventory")
if q != "" and dup["available"] is not None:
dup["available"] += num(q)
dup["on_hand"] += num(q)
continue
rec = recs.get(key) or _blank(key, sku, h, c["title"], vt)
rec.update({"type": c["type"], "vendor": c["vendor"], "tags": c["tags"], "status": c["status"]})
rec["price"] = num(price) if price else None
ca = col(r, "variant_compare_at_price", "compare_at_price")
rec["compare_at"] = num(ca) if ca else None
cost = col(r, "cost_per_item", "unit_cost", "cost")
rec["cost"] = num(cost) if cost else None
q = col(r, "variant_inventory_qty", "inventory_quantity", "inventory")
if q != "":
rec["available"] = num(q)
rec["on_hand"] = num(q)
rec["variant_id"] = col(r, "variant_id")
rec["product_id"] = col(r, "product_id", "id")
recs[key] = rec
if inventory:
rows = read_rows(inventory)
if not rows:
raise DataError(f"{inventory}: no rows found")
if not has_col(rows, "available", "on_hand"):
raise DataError("inventory file is missing the column 'Available' (or 'On hand')")
titles, seen = {}, set()
for r in rows:
h = col(r, *HANDLE_COLS)
t = col(r, "title")
if h and t:
titles[h] = t
title = t or titles.get(h, "")
sku = col(r, "sku", "variant_sku")
vt = _options(r)
key = variant_key(sku, title, vt)
rec = recs.get(key)
if rec is None:
rec = _blank(key, sku, h, title, vt)
recs[key] = rec
if h and rec["handle"] and h != rec["handle"] and h not in rec["duplicate_handles"]:
rec["duplicate_handles"].append(h)
if key not in seen:
rec["available"], rec["on_hand"], rec["incoming"] = 0.0, 0.0, 0.0
seen.add(key)
avail = col(r, "available")
onhand = col(r, "on_hand")
rec["available"] += num(avail if avail != "" else onhand)
rec["on_hand"] += num(onhand if onhand != "" else avail)
rec["incoming"] += num(col(r, "incoming"))
return recs
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack." | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |