ReplAi-Now/shopify-skills / skills/operations/true-profit
true-profit
VerifiedReal profit per product and order after ads, shipping, COD fees, failed deliveries and returns. Use for "true profit", "am I making money" or "which products lose money".
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 10.9 KB |
| assets/ad-map-template.csv | other | 163 B |
| assets/product-costs-template.csv | other | 298 B |
| assets/setup.example.json | config | 1.4 KB |
| references/data-sources.md | markdown | 9.0 KB |
| references/method.md | markdown | 7.3 KB |
| scripts/orders.py | script | 16.6 KB |
| scripts/profit.py | script | 37.3 KB |
| scripts/render_dashboard.py | script | 10.9 KB |
| scripts/tabular.py | script | 6.2 KB |
scripts/profit.py
View on GitHub"""True profit per product, per order and per channel. Python 3 standard library only, no network.
Usage:
python3 profit.py --orders orders_export.csv --setup replai-setup/true-profit.json
[--costs product_costs.csv] # Shopify products export, cost sheet or invoice CSV
[--ads meta=meta.csv --ads tiktok=tiktok.csv --ads google=google.csv]
[--ad-map ad_product_map.csv] # campaign / ad set / ad name -> product
[--courier courier.csv::CourierName] [--courier-map status="State"]
[--returns returns.csv] # units sent back after delivery (Try & Buy, restock without refund)
[--out-dir results/]
Prints one JSON object: totals, products (units, revenue, COGS, ads, shipping, RTO cost,
returns, fees, contribution, margin, break-even ROAS), bestsellers that lose money,
orders summary, sales channels, ad platforms, assumptions and warnings.
Exit code 2 with {"error": "need_fx_rate", ...} when an ad or cost currency differs from the
store currency and setup has no rate for it. With --out-dir writes products.csv,
orders.csv, channels.csv, ad_platforms.csv.
"""
import argparse, csv, datetime, json, os, re, sys, tempfile, zipfile
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
import tabular # noqa: E402
from tabular import read_rows, pick, num, norm, write_csv # noqa: E402
from orders import load_orders, load_courier, CourierIndex, classify, is_cod, _iso_date, order_key # noqa: E402
HERE = os.path.dirname(os.path.abspath(__file__))
DEFAULT_SETUP = os.path.join(HERE, "..", "assets", "setup.example.json")
def r2(x):
return None if x is None else round(x + 0.0, 2)
def r4(x):
return None if x is None else round(x + 0.0, 4)
def die(obj, code=2):
print(json.dumps(obj, ensure_ascii=False, indent=1))
sys.exit(code)
def load_setup(path):
with open(DEFAULT_SETUP, encoding="utf-8") as fh:
base = json.load(fh)
if path:
with open(path, encoding="utf-8") as fh:
base.update(json.load(fh))
return base
def fx(currency, S, what):
cur = (currency or "").upper().strip()
store = S.get("currency", "").upper()
if not cur or cur == store:
return 1.0
rate = (S.get("fx_rates") or {}).get(cur)
if not rate:
die({"error": "need_fx_rate", "currency": cur, "store_currency": store, "for": what,
"fix": f"Ask the merchant: how many {store} is 1 {cur} for this period? Save it in setup fx_rates."})
return float(rate)
# ---------- product costs ----------
def load_costs(path, S):
"""Return (by_sku, by_title, titles). Accepts the Shopify products export (legacy headers Handle,
Title, Variant SKU, Cost per item, or the 2026 headers URL handle, Title, SKU, Cost per item),
a cost sheet, or the invoice template in assets/."""
rows = read_rows(path)
if not rows:
return {}, {}, []
cols = set(rows[0].keys())
cost_col = next((c for c in ("cost_per_item", "unit_cost", "cost", "cost_per_unit", "unit_price_cost",
"landed_cost", "unitcost") if c in cols), None)
if not cost_col:
die({"error": "missing column", "wanted": "Cost per item (or unit_cost)", "file": path,
"columns": sorted(cols)}, 1)
by_sku, titles_cost, last_title = {}, {}, {}
for r in rows:
handle = pick(r, "handle", "url_handle")
title = pick(r, "title", "product", "product_title", "name")
if handle:
if title:
last_title[handle] = title
else:
title = last_title.get(handle, "")
raw = r.get(cost_col, "")
if str(raw).strip() == "":
continue
rate = fx(pick(r, "currency"), S, f"product cost in {os.path.basename(path)}")
c = num(raw) * rate
sku = pick(r, "variant_sku", "sku")
if sku:
by_sku[sku.strip().lower()] = c
if title:
titles_cost.setdefault(title.strip(), []).append(c)
by_title = {t.lower(): sum(v) / len(v) for t, v in titles_cost.items()}
return by_sku, by_title, sorted(titles_cost, key=len, reverse=True)
def product_key(line_name, titles, group_by):
if group_by == "variant":
return line_name
low = line_name.lower()
for t in titles:
tl = t.lower()
if low == tl or low.startswith(tl + " - "):
return t
return line_name.rsplit(" - ", 1)[0] if " - " in line_name else line_name
# ---------- ads ----------
SPEND = re.compile(r"^(amount_spent|spend|cost|total_cost|spent|المبلغ_المنفق|المبلغ_الذي_تم_إنفاقه|الإنفاق|التكلفة)"
r"(_\(([a-z]{3})\))?$")
TITLE_HINT = ("campaign", "حملة", "الحملة")
SPEND_HINT = ("cost", "spen", "المنفق", "إنفاق", "الإنفاق", "التكلفة")
def _is_header(cells):
low = " ".join(str(c) for c in cells).lower()
return any(h in low for h in TITLE_HINT) and any(h in low for h in SPEND_HINT)
def _xlsx_grid(path):
"""First sheet of an XLSX as a list of rows (lists of text), title rows included."""
z = zipfile.ZipFile(path)
names = z.namelist()
ns = tabular._NS
shared = []
if "xl/sharedStrings.xml" in names:
for si in tabular.ET.fromstring(z.read("xl/sharedStrings.xml")).findall(ns + "si"):
shared.append("".join(t.text or "" for t in si.iter(ns + "t")))
sheets = sorted((n for n in names if re.match(r"xl/worksheets/sheet\d+\.xml$", n)),
key=lambda n: int(re.search(r"(\d+)", n.split("/")[-1]).group(1)))
grid = []
for row in (tabular.ET.fromstring(z.read(sheets[0])).iter(ns + "row") if sheets else []):
vals = {}
for c in row.findall(ns + "c"):
v, t = c.find(ns + "v"), c.get("t")
if t == "s" and v is not None:
val = shared[int(v.text)]
elif t == "inlineStr":
val = "".join(x.text or "" for x in c.iter(ns + "t"))
else:
val = v.text if v is not None else ""
vals[tabular._cell_index(c.get("r"))] = val or ""
if vals:
grid.append([vals.get(i, "") for i in range(max(vals) + 1)])
return grid
def read_ads_table(path):
if path.lower().endswith((".xlsx", ".xlsm")):
# Google Ads XLSX starts with a report title and a date line, like its CSV.
grid = _xlsx_grid(path)
start = next((i for i, r in enumerate(grid[:15]) if _is_header(r)), 0)
if start:
tmp = tempfile.NamedTemporaryFile("w", suffix=".csv", delete=False, encoding="utf-8", newline="")
csv.writer(tmp).writerows(grid[start:])
tmp.close()
try:
return read_rows(tmp.name)
finally:
os.unlink(tmp.name)
return read_rows(path)
if path.lower().endswith((".csv", ".tsv", ".txt")):
with open(path, "rb") as fh:
raw = fh.read()
text, enc = None, None
for enc in ("utf-8-sig", "utf-16", "cp1256", "latin-1"):
try:
text = raw.decode(enc)
break
except UnicodeDecodeError:
continue
lines = text.splitlines()
start = 0
for i, ln in enumerate(lines[:15]):
if _is_header([ln]):
start = i
break
if start or enc == "utf-16": # title lines to skip, or a UTF-16 file read_rows cannot decode
tmp = tempfile.NamedTemporaryFile("w", suffix=".csv", delete=False, encoding="utf-8")
tmp.write("\n".join(lines[start:]))
tmp.close()
try:
return read_rows(tmp.name)
finally:
os.unlink(tmp.name)
return read_rows(path)
def load_ads(platform, path, S):
rows = read_ads_table(path)
if not rows:
return []
cols = list(rows[0].keys())
spend_col, cur_from_header = None, None
for c in cols:
m = SPEND.match(c)
if m:
spend_col, cur_from_header = c, (m.group(3) or "").upper() or None
break
if not spend_col:
die({"error": "missing column", "wanted": "spend (Amount spent / Cost / Spend)", "file": path,
"columns": cols}, 1)
def col(*names):
return next((n for n in names if n in cols), None)
# English headers, then the Arabic ones Meta / TikTok / Google write in an Arabic interface.
camp = col("campaign_name", "campaign", "اسم_الحملة", "الحملة")
adset = col("ad_set_name", "adset_name", "ad_group_name", "ad_group", "ad_set",
"اسم_مجموعة_الإعلانات", "مجموعة_الإعلانات", "اسم_المجموعة_الإعلانية")
ad = col("ad_name", "ad", "اسم_الإعلان", "الإعلان")
purch = col("purchases", "website_purchases", "conversions", "total_complete_payment",
"complete_payment", "purchase", "orders", "عمليات_الشراء", "المشتريات", "التحويلات", "الإحالات_الناجحة")
value = col("purchases_conversion_value", "purchase_conversion_value", "conversion_value",
"conv._value", "total_complete_payment_value", "total_purchase_value", "purchase_value",
"قيمة_تحويل_عمليات_الشراء", "قيمة_التحويل", "قيمة_الإحالات_الناجحة")
date = col("day", "date", "reporting_starts", "by_day", "stat_time_day", "start_date",
"التاريخ", "اليوم", "بداية_التقارير", "بدء_إعداد_التقارير")
curc = col("currency", "currency_code", "العملة", "رمز_العملة")
names = [c for c in (camp, adset, ad) if c]
out = []
for r in rows:
cname = r.get(camp, "") if camp else ""
low = cname.strip().lower()
# Total rows: Google "Total: ...", TikTok "Total of N ...", "--". A campaign named "Total Look" is kept.
if low in ("total", "totals", "--", "الإجمالي", "المجموع") or \
low.startswith(("total:", "total of ", "totals:", "الإجمالي:", "الإجمالي ")):
continue
sp = num(r.get(spend_col, ""))
# Meta "Include summary row": every name column blank. Real rows always carry a name.
if names and not any(str(r.get(c, "")).strip() for c in names):
continue
if not cname and sp == 0:
continue
cur = cur_from_header or (r.get(curc, "") if curc else "") or \
(S.get("ad_currency") or {}).get(platform) or S.get("currency", "")
rate = fx(cur, S, f"{platform} ad spend")
out.append({"platform": platform, "campaign": cname, "adset": r.get(adset, "") if adset else "",
"ad": r.get(ad, "") if ad else "", "spend": sp * rate, "spend_original": sp,
"currency": cur.upper(), "purchases": num(r.get(purch, "")) if purch else 0.0,
"value": num(r.get(value, "")) * rate if value else 0.0,
"date": _iso_date(r.get(date, ""), False) if date else ""}) # XLSX gives Excel serials
return out
def load_ad_map(path):
rules = []
for i, r in enumerate(read_rows(path)):
contains = pick(r, "contains", "pattern", "match", "name_contains").strip().lower()
prod = pick(r, "product", "products", "product_title").strip()
if not contains or not prod:
continue
level = (pick(r, "level", "match_on") or "campaign").strip().lower().replace(" ", "")
level = {"adgroup": "adset", "ad_set": "adset", "ad_group": "adset"}.get(level, level)
rules.append({"platform": pick(r, "platform").strip().lower(), "level": level,
"contains": contains, "product": prod, "order": i})
prio = {"ad": 0, "adset": 1, "campaign": 2}
rules.sort(key=lambda x: (prio.get(x["level"], 3), -len(x["contains"]), x["order"]))
return rules
def match_rule(row, rules):
for ru in rules:
if ru["platform"] and ru["platform"] != row["platform"]:
continue
if ru["contains"] in str(row.get(ru["level"], "")).lower():
return ru
return None
# ---------- returns after delivery ----------
def load_returns(path):
"""Units sent back after delivery, by order key: {key: [(sku, name, qty)]}. Accepts a returns CSV
with an order column (order / order name / order number), a SKU or product column and a quantity
column; with a `restock_type` column only RETURN rows count (the connector's refundLineItems)."""
rows = read_rows(path)
if not rows:
return {}
cols = set(rows[0].keys())
if not cols & {"order", "order_name", "order_number", "name", "order_id"}:
die({"error": "missing column", "wanted": "order (order name, e.g. #1001)", "file": path,
"columns": sorted(cols)}, 1)
out = {}
for r in rows:
rt = pick(r, "restock_type", "restocktype").lower()
if rt and "return" not in rt:
continue
q = abs(num(pick(r, "quantity", "returned_quantity", "qty", "units"), 1.0))
k = order_key(pick(r, "order", "order_name", "order_number", "name", "order_id"))
if q and k:
out.setdefault(k, []).append((pick(r, "sku", "lineitem_sku", "variant_sku").strip().lower(),
pick(r, "product", "lineitem_name", "line_item", "title").strip().lower(), q))
return out
def returned_units(o, returns):
"""Returned quantity per order line (index -> qty), from the orders column and/or the returns file."""
got = {i: ln.get("returned", 0.0) for i, ln in enumerate(o["lines"])}
left = [list(x) for x in returns.get(o["key"], [])]
for i, ln in enumerate(o["lines"]):
sku, name = ln["sku"].strip().lower(), ln["name"].lower()
q = 0.0
for x in left:
if x[2] > 0 and ((sku and x[0] == sku) or (not x[0] and x[1] and (name == x[1] or name.startswith(x[1] + " - ")))):
take = min(x[2], ln["qty"] - q)
q += take
x[2] -= take
got[i] = min(ln["qty"], max(got[i], q))
return got
# ---------- main ----------
def main(argv=None):
ap = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
ap.add_argument("--orders", required=True)
ap.add_argument("--setup")
ap.add_argument("--costs")
ap.add_argument("--ads", action="append", default=[])
ap.add_argument("--ad-map")
ap.add_argument("--courier", action="append", default=[])
ap.add_argument("--courier-map", action="append", default=[])
ap.add_argument("--returns")
ap.add_argument("--out-dir")
a = ap.parse_args(argv)
S = load_setup(a.setup)
cur = S.get("currency", "")
warnings, assumptions = [], []
orders = load_orders(a.orders)
by_sku, by_title, titles = load_costs(a.costs, S) if a.costs else ({}, {}, [])
if not a.costs:
warnings.append("No product cost file given: every product shows cost missing and no profit. Give a costs file (see references/data-sources.md).")
overrides = {} # meaning -> [headers]; repeat --courier-map for files with different headers
for x in a.courier_map:
k, _, v = x.partition("=")
overrides.setdefault(k.strip(), []).append(v.strip())
crow = []
for spec in a.courier:
path, _, cname = spec.partition("::")
rows, _ = load_courier(path, cname, overrides)
crow += rows
cidx = CourierIndex(crow, S)
returns = load_returns(a.returns) if a.returns else {}
has_returned_col = any(ln.get("returned") for o in orders for ln in o["lines"])
def default(key, label):
v = S.get(key)
if v is None:
warnings.append(f"No {label} in setup and no courier value: counted as 0, so profit is overstated.")
S[key] = 0.0
return 0.0
return float(v)
d_ship = default("shipping_cost_per_order", "shipping cost per order")
d_ret = default("return_shipping_per_order", "return shipping cost per failed order")
pack = float(S.get("packaging_cost_per_order") or 0)
g_pct, g_fix = float(S.get("gateway_fee_pct") or 0), float(S.get("gateway_fee_fixed") or 0)
c_pct, c_fix = float(S.get("cod_fee_pct") or 0), float(S.get("cod_fee_fixed") or 0)
group_by = S.get("group_by", "product")
incl_tax = bool(S.get("prices_include_tax", False))
restock = bool(S.get("refund_restock", True))
P = {} # product -> metrics
def prod(k):
if k not in P:
P[k] = {"product": k, "orders": set(), "units_sold": 0.0, "units_rto": 0.0, "gross_sales": 0.0,
"discounts": 0.0, "refunds": 0.0, "tax_removed": 0.0, "shipping_income": 0.0,
"net_revenue": 0.0, "cogs": 0.0, "cost_missing": False, "shipping_cost": 0.0,
"payment_fees": 0.0, "packaging": 0.0, "rto_cost": 0.0, "rto_gross": 0.0, "ads": 0.0,
"units_returned": 0.0, "returns_not_refunded": 0.0}
return P[k]
order_rows, counts, missing_cost_lines, zero_cost = [], {}, {}, set()
gift_cod_unknown, try_buy_tagged, returned_total = 0, 0, 0.0
for o in orders:
c = cidx.match(o)
# Same rules as cod-rto-analysis: shipped but neither paid nor failed is open (still with the
# courier, or not updated) and is left out of profit, not counted as delivered.
outcome = classify(o, S, c)
counts[outcome] = counts.get(outcome, 0) + 1
if outcome not in ("delivered", "failed") or not o["lines"]:
continue
cod = is_cod(o, S.get("cod_payment_keywords", []))
G = sum(ln["price"] * ln["qty"] for ln in o["lines"])
Q = sum(ln["qty"] for ln in o["lines"]) or 1.0
lines = []
for ln in o["lines"]:
g = ln["price"] * ln["qty"]
share = g / G if G else ln["qty"] / Q
unit = by_sku.get(ln["sku"].strip().lower()) if ln["sku"] else None
key = product_key(ln["name"], titles, group_by)
if unit is None:
unit = by_title.get(ln["name"].lower(), by_title.get(key.lower()))
lines.append((ln, g, share, key, unit))
orow = {"order": o["name"], "date": o["date"], "outcome": outcome, "cod": cod,
"channel": o["source"] or "unknown", "net_revenue": 0.0, "cogs": 0.0, "costs": 0.0,
"ads": 0.0, "cost_missing": False, "lines": []}
if outcome == "failed":
out_fee = c["shipping_fee"] if c and c["shipping_fee"] is not None else d_ship
ret_fee = c["return_fee"] if c and c["return_fee"] is not None else d_ret
cod_fee = (c["cod_fee"] if c and c["cod_fee"] is not None else c_fix) if cod else 0.0
rto = out_fee + ret_fee + cod_fee + pack
for ln, g, share, key, unit in lines:
p = prod(key)
if unit is None: # a product without a cost is unknown whatever happened to its orders
p["cost_missing"] = True
missing_cost_lines[key] = missing_cost_lines.get(key, 0) + ln["qty"]
p["units_rto"] += ln["qty"]
p["rto_gross"] += g
p["rto_cost"] += rto * share
orow.update({"costs": rto, "rto_cost": rto})
order_rows.append(orow)
continue
ship = c["shipping_fee"] if c and c["shipping_fee"] is not None else d_ship
if cod:
# A COD % fee applies to the cash collected: total minus any gift card part.
cash = max(0.0, o["total"] - o["gift_card"])
if not o["gift_card"] and "gift" in str(o["payment"]).lower() and c_pct and not (c and c["cod_fee"] is not None):
gift_cod_unknown += 1
fee = c["cod_fee"] if c and c["cod_fee"] is not None else cash * c_pct / 100 + c_fix
else:
fee = o["total"] * g_pct / 100 + g_fix
if any("try" in t and "buy" in t for t in o["tags"]):
try_buy_tagged += 1
goods = G - o["discount"]
frac = min(1.0, o["refunded"] / goods) if (restock and goods > 0 and o["refunded"] > 0) else 0.0
# Units sent back after delivery (Try & Buy, restock without refund): their value, net of
# discount, comes off revenue where the refund does not already cover it.
ret = returned_units(o, returns)
dfac = (1 - o["discount"] / G) if G else 1.0
ret_val = {i: ln["price"] * ret[i] * dfac for i, ln in enumerate(o["lines"])}
tot_ret = sum(ret_val.values())
unrefunded = max(0.0, tot_ret - o["refunded"])
returned_total += unrefunded
for idx, (ln, g, share, key, unit) in enumerate(lines):
p = prod(key)
p["orders"].add(o["name"])
disc, si, rf = o["discount"] * share, o["shipping"] * share, o["refunded"] * share
tx = o["taxes"] * share if incl_tax else 0.0
back = unrefunded * ret_val[idx] / tot_ret if tot_ret else 0.0
net = g - disc + si - tx - rf - back
p["units_sold"] += ln["qty"] - ret[idx]
p["units_returned"] += ret[idx]
p["returns_not_refunded"] += back
p["gross_sales"] += g
p["discounts"] += disc
p["shipping_income"] += si
p["refunds"] += rf
p["tax_removed"] += tx
p["net_revenue"] += net
if unit is None:
p["cost_missing"] = True
cogs = None
orow["cost_missing"] = True
missing_cost_lines[key] = missing_cost_lines.get(key, 0) + ln["qty"]
else:
if unit == 0:
zero_cost.add(key)
back_frac = ret[idx] / ln["qty"] if ln["qty"] else 0.0 # returned units are back in stock
cogs = unit * ln["qty"] * (1 - max(frac, back_frac))
p["cogs"] += cogs
orow["cogs"] += cogs
p["shipping_cost"] += ship * share
p["payment_fees"] += fee * share
p["packaging"] += pack * share
orow["net_revenue"] += net
orow["lines"].append((key, net))
orow["costs"] = ship + fee + pack
order_rows.append(orow)
# ---- ads ----
ad_rows = []
for spec in a.ads:
platform, _, path = spec.partition("=")
if not path:
platform, path = os.path.splitext(os.path.basename(spec))[0], spec
ad_rows += load_ads(platform.strip().lower(), path, S)
rules = load_ad_map(a.ad_map) if (a.ad_map and S.get("ad_allocation", "mapping") == "mapping") else []
base = {k: max(0.0, p["net_revenue"]) for k, p in P.items()}
total_base = sum(base.values())
lookup = {k.lower(): k for k in P}
unknown_products, mapped_spend, shared_spend = set(), 0.0, 0.0
def spread(amount, keys):
b = sum(base.get(k, 0) for k in keys)
for k in keys:
P[k]["ads"] += amount * (base.get(k, 0) / b if b else 1.0 / len(keys))
for row in ad_rows:
ru = match_rule(row, rules) if rules else None
targets = []
if ru and ru["product"].upper() != "SHARED":
for name in ru["product"].split("|"):
k = lookup.get(name.strip().lower())
if k:
targets.append(k)
else:
unknown_products.add(name.strip())
row["allocated_to"] = "|".join(targets) if targets else "shared"
if targets:
spread(row["spend"], targets)
mapped_spend += row["spend"]
else:
shared_spend += row["spend"]
if total_base:
spread(row["spend"], [k for k in P if base.get(k, 0) > 0])
if unknown_products:
warnings.append("Ad map names products not found in the orders (spend shared by revenue instead): " + ", ".join(sorted(unknown_products)))
total_ads = sum(r["spend"] for r in ad_rows)
# ---- product results ----
products = []
for k, p in P.items():
known = not p["cost_missing"]
cba = (p["net_revenue"] - p["cogs"] - p["shipping_cost"] - p["payment_fees"] - p["packaging"] - p["rto_cost"]) if known else None
contrib = cba - p["ads"] if known else None
row = {kk: (r2(v) if isinstance(v, float) else v) for kk, v in p.items() if kk != "orders"}
row["orders"] = len(p["orders"])
row["units_sold"] = p["units_sold"]
row["units_rto"] = p["units_rto"]
row["units_returned"] = p["units_returned"]
row["cogs"] = r2(p["cogs"]) if known else None
row["contribution_before_ads"] = r2(cba)
row["contribution"] = r2(contrib)
row["margin"] = r4(contrib / p["net_revenue"]) if known and p["net_revenue"] else None
row["break_even_roas"] = r2(p["net_revenue"] / cba) if known and cba and cba > 0 else None
row["break_even_platform_roas"] = r2((p["gross_sales"] + p["rto_gross"]) / cba) if known and cba and cba > 0 else None
row["actual_roas"] = r2(p["net_revenue"] / p["ads"]) if p["ads"] else None
row["never_breaks_even"] = bool(known and cba is not None and cba <= 0)
products.append(row)
products.sort(key=lambda x: (-(x["net_revenue"] or 0), x["product"]))
topn = int(S.get("bestseller_top_n", 10))
best = sorted(products, key=lambda x: (-x["units_sold"], -(x["net_revenue"] or 0)))[:topn]
losers = []
for x in best:
if x["contribution"] is not None and x["contribution"] < 0:
loss = -x["contribution"]
losers.append({"product": x["product"], "units_sold": x["units_sold"], "net_revenue": x["net_revenue"],
"contribution": x["contribution"], "ads": x["ads"],
"contribution_before_ads": x["contribution_before_ads"],
"price_increase_per_unit_to_break_even": r2(loss / x["units_sold"]) if x["units_sold"] else None,
"ad_cut_to_break_even": r2(loss) if x["ads"] and x["ads"] >= loss else None,
"problem": "ads" if (x["contribution_before_ads"] or 0) > 0 else "price_or_cost"})
# ---- orders ----
ads_per_rev = {k: (P[k]["ads"] / base[k]) if base.get(k) else 0.0 for k in P}
out_orders, attached = [], 0.0
for orow in order_rows:
ads = sum(max(0.0, net) * ads_per_rev.get(k, 0) for k, net in orow["lines"])
attached += ads
if orow["outcome"] == "failed":
contrib = -orow["costs"]
elif orow["cost_missing"]:
contrib = None
else:
contrib = orow["net_revenue"] - orow["cogs"] - orow["costs"] - ads
out_orders.append({"order": orow["order"], "date": orow["date"], "outcome": orow["outcome"],
"channel": orow["channel"], "cod": orow["cod"], "net_revenue": r2(orow["net_revenue"]),
"cogs": None if orow["cost_missing"] else r2(orow["cogs"]),
"shipping_fees_packaging_or_rto": r2(orow["costs"]), "ads": r2(ads),
"contribution": r2(contrib)})
delivered = [x for x in out_orders if x["outcome"] == "delivered"]
failed = [x for x in out_orders if x["outcome"] == "failed"]
known_del = [x for x in delivered if x["contribution"] is not None]
orders_summary = {
"delivered": len(delivered), "failed_rto": len(failed),
"delivery_rate": r4(len(delivered) / (len(delivered) + len(failed))) if (delivered or failed) else None,
"avg_net_revenue_per_delivered_order": r2(sum(x["net_revenue"] for x in delivered) / len(delivered)) if delivered else None,
"avg_contribution_per_delivered_order": r2(sum(x["contribution"] for x in known_del) / len(known_del)) if known_del else None,
"avg_cost_per_rto_order": r2(sum(-x["contribution"] for x in failed) / len(failed)) if failed else None,
"delivered_orders_losing_money": sum(1 for x in known_del if x["contribution"] < 0),
"orders_with_missing_cost": sum(1 for x in delivered if x["contribution"] is None),
"worst_orders": sorted([x for x in out_orders if x["contribution"] is not None], key=lambda x: x["contribution"])[:10],
}
# ---- channels ----
ch = {}
for x in out_orders:
c = ch.setdefault(x["channel"], {"channel": x["channel"], "delivered": 0, "failed_rto": 0, "net_revenue": 0.0,
"contribution_known": 0.0, "orders_missing_cost": 0, "delivered_with_cost": 0})
c["delivered" if x["outcome"] == "delivered" else "failed_rto"] += 1
c["net_revenue"] += x["net_revenue"] or 0
if x["contribution"] is None:
c["orders_missing_cost"] += 1
else:
c["contribution_known"] += x["contribution"]
if x["outcome"] == "delivered":
c["delivered_with_cost"] += 1
for c in ch.values():
# contribution: n/a as soon as one delivered order lacks a cost. contribution_known: the part
# with costs, n/a when no delivered order has one (failed-delivery costs alone are not a profit).
c["contribution"] = None if c["orders_missing_cost"] else c["contribution_known"]
if c["delivered"] and not c["delivered_with_cost"]:
c["contribution_known"] = None
channels = [{k: r2(v) if isinstance(v, float) else v for k, v in c.items()} for c in
sorted(ch.values(), key=lambda c: -c["net_revenue"])]
known_products = [x for x in products if x["contribution"] is not None]
# Break-even cost per delivered order: contribution before ads of the delivered orders that have
# costs, minus failed-delivery costs, divided by those delivered orders only.
cba_orders = sum(x["contribution"] + x["ads"] for x in known_del) + sum(x["contribution"] for x in failed)
be_cpa = cba_orders / len(known_del) if known_del else None
drate = orders_summary["delivery_rate"] or 0
plat = {}
for row in ad_rows:
q = plat.setdefault(row["platform"], {"platform": row["platform"], "spend": 0.0, "purchases": 0.0,
"purchase_value": 0.0, "mapped_spend": 0.0, "currency_in_file": set()})
q["spend"] += row["spend"]
q["purchases"] += row["purchases"]
q["purchase_value"] += row["value"]
q["currency_in_file"].add(row["currency"])
if row["allocated_to"] != "shared":
q["mapped_spend"] += row["spend"]
platforms = []
for q in plat.values():
dp = q["purchases"] * drate
platforms.append({"platform": q["platform"], "spend": r2(q["spend"]), "mapped_spend": r2(q["mapped_spend"]),
"currency_in_file": sorted(q["currency_in_file"]),
"platform_purchases": q["purchases"],
"platform_roas": r2(q["purchase_value"] / q["spend"]) if q["spend"] and q["purchase_value"] else None,
"cost_per_platform_purchase": r2(q["spend"] / q["purchases"]) if q["purchases"] else None,
"delivered_purchases_estimate": r2(dp),
"true_cpa_estimate": r2(q["spend"] / dp) if dp else None,
"break_even_cpa": r2(be_cpa)})
# ---- totals ----
dates = sorted(o["date"] for o in orders if o["date"])
days = 0
if dates:
try:
days = (datetime.date.fromisoformat(dates[-1]) - datetime.date.fromisoformat(dates[0])).days + 1
except ValueError:
days = 0
ad_dates = sorted(r["date"] for r in ad_rows if r["date"])
if ad_dates and dates and (ad_dates[0] < dates[0] or ad_dates[-1] > dates[-1]):
warnings.append(f"Ad data runs {ad_dates[0]} to {ad_dates[-1]} but orders run {dates[0]} to {dates[-1]}. Export both for the same dates.")
fixed = float(S.get("fixed_monthly_costs") or 0) * days / 30.0 if days else 0.0
sumk = lambda key: r2(sum((x[key] or 0) for x in products)) # noqa: E731
profit_known = bool(known_products)
contrib_known = sum(x["contribution"] for x in known_products) if profit_known else None
totals = {
"net_revenue": sumk("net_revenue"), "gross_sales": sumk("gross_sales"), "discounts": sumk("discounts"),
"refunds": sumk("refunds"), "cogs_known": sumk("cogs"), "shipping_cost": sumk("shipping_cost"),
"payment_fees": sumk("payment_fees"), "packaging": sumk("packaging"), "rto_cost": sumk("rto_cost"),
"ads_total": r2(total_ads), "ads_mapped": r2(mapped_spend), "ads_shared_by_revenue": r2(shared_spend),
"ads_not_attached_to_orders": r2(total_ads - attached),
"contribution_known_products": r2(contrib_known),
"returns_not_refunded": r2(returned_total),
"net_revenue_with_missing_cost": r2(sum(x["net_revenue"] for x in products if x["contribution"] is None)),
"fixed_costs_for_period": r2(fixed) if fixed else None,
"net_profit_known": r2(contrib_known - fixed) if (fixed and profit_known) else None,
}
totals["margin_known"] = r4(contrib_known / sum(x["net_revenue"] for x in known_products)) if known_products and sum(x["net_revenue"] for x in known_products) else None
if counts.get("open"):
warnings.append(f"{counts['open']} shipped orders are still open (not marked paid or failed: with the courier, or not updated). They are left out of profit; a courier file or updated statuses would settle them.")
if not profit_known:
warnings.insert(0, "No product has a unit cost, so profit is unknown: no profit, margin or money-losing bestseller can be shown. Give product costs (Shopify products export with Cost per item, or the cost template) and rerun.")
if gift_cod_unknown:
warnings.append(f"{gift_cod_unknown} COD orders were partly paid by gift card but the gift card amount is not in the file: the COD % fee was charged on the full total, so fees are slightly overstated.")
if returns or has_returned_col:
assumptions.append("Units sent back after delivery (returns file or returned quantity column) are back in stock; their value comes off revenue where no refund covers it.")
else:
assumptions.append("No returned-units data: items a customer sends back without a refund (Try & Buy, restock without refund) are not visible in the export, so revenue can be overstated. The connector's refunds (restockType RETURN) fill this gap; see references/data-sources.md.")
if try_buy_tagged:
warnings.append(f"{try_buy_tagged} delivered orders are tagged Try & Buy but no returned-units data was given: items handed back at the door are counted as sold. Give a returns file (--returns) to correct revenue.")
if missing_cost_lines:
lost_ads = sum(P[k]["ads"] for k in P if P[k]["cost_missing"])
warnings.append("Products with no cost (left out of profit totals): " + ", ".join(sorted(missing_cost_lines))
+ (f". Their share of ad spend ({lost_ads:,.0f} {cur}) is left out of contribution too." if lost_ads else ""))
if zero_cost:
warnings.append("Products with a unit cost of 0 (check it is not a blank cost saved as 0): " + ", ".join(sorted(zero_cost)))
assumptions += [
"Revenue = item price x quantity, minus discounts and refunds" + (", minus taxes included in prices" if incl_tax else "") + ", plus shipping charged to the customer. Order-level amounts are split across lines by line value.",
"Failed (RTO) orders earn nothing and cost outbound + return shipping + COD fee + packaging; their goods are assumed back in stock unharmed.",
("Refunded goods are assumed back in stock (their cost is reversed in proportion to the refund)." if restock else "Refunded goods are assumed lost (cost not reversed)."),
f"Payment fees: COD {c_pct:g}% + {c_fix:g}, card/wallet {g_pct:g}% + {g_fix:g} per order, unless the courier file gives the COD fee.",
"Ad spend: " + ("campaigns matched in the ad map go to their products; the rest is shared by net revenue." if rules else "all spend shared across products by net revenue (no confirmed ad map)."),
"Break-even ROAS = net revenue / contribution before ads. Platform break-even ROAS adds failed orders' value, because ad platforms count purchases before delivery.",
]
rates = {k: v for k, v in (S.get("fx_rates") or {}).items() if v}
used = sorted({r["currency"] for r in ad_rows if r["currency"] and r["currency"] != cur.upper()})
if used:
assumptions.append("Currency conversion: " + ", ".join(f"1 {c} = {rates[c]:g} {cur}" for c in used if c in rates) + " (rate given by the merchant).")
if shared_spend and total_base == 0:
warnings.append("Shared ad spend could not be spread: no delivered revenue.")
result = {"currency": cur,
"period": {"from": dates[0] if dates else None, "to": dates[-1] if dates else None, "days": days},
"order_outcomes": counts, "profit_known": profit_known, "totals": totals, "products": products,
"bestsellers": [x["product"] for x in best],
"bestsellers_losing_money": losers, "orders": orders_summary,
"sales_channels": channels, "ad_platforms": platforms,
"courier": {"rows": len(crow), "matched": len(cidx.used)},
"assumptions": assumptions, "warnings": warnings}
if a.out_dir:
os.makedirs(a.out_dir, exist_ok=True)
write_csv(os.path.join(a.out_dir, "products.csv"), products)
write_csv(os.path.join(a.out_dir, "orders.csv"), out_orders)
write_csv(os.path.join(a.out_dir, "channels.csv"), channels)
write_csv(os.path.join(a.out_dir, "ad_platforms.csv"), platforms)
print(json.dumps(result, ensure_ascii=False, indent=1))
return result
if __name__ == "__main__":
main()
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 |