#!/usr/bin/env python3 """Northline Workshops - October 2026 reconciliation (standard library only). Reads inputs/sales.csv, inputs/refunds.csv, inputs/ads.csv (never modified) and writes cleaned-sales.csv, cleaned-refunds.csv, cleaned-ads.csv, exceptions.csv, summary.md into output/. Rules come from inputs/policy.md. """ import csv import os import re from collections import OrderedDict, defaultdict from datetime import date, datetime from decimal import Decimal, InvalidOperation HERE = os.path.dirname(os.path.abspath(__file__)) BASE = os.path.dirname(HERE) IN = os.path.join(BASE, "inputs") OUT = HERE P_START, P_END = date(2026, 10, 1), date(2026, 10, 31) PRODUCTS = {"workshop": "Workshop", "course": "Course"} ZERO = Decimal("0.00") def parse_date(raw): s = (raw or "").strip() if not s: return None fmts = ["%Y-%m-%d", "%d/%m/%Y", "%b %d %Y", "%B %d %Y", "%b %d, %Y", "%B %d, %Y", "%d %b %Y", "%d %B %Y"] for f in fmts: try: return datetime.strptime(s, f).date() except ValueError: pass return None def parse_money(raw): """Empty -> None (unknown, never zero). Strips CAD / $ / commas.""" s = (raw or "").strip() if not s: return None s = re.sub(r"(?i)cad", "", s).replace("$", "").replace(",", "").strip() try: return Decimal(s).quantize(Decimal("0.01")) except InvalidOperation: return None def m(d): return "" if d is None else f"{d:.2f}" def iso(d): return "" if d is None else d.isoformat() def in_period(d): return d is not None and P_START <= d <= P_END def load(name): with open(os.path.join(IN, name), newline="", encoding="utf-8") as fh: return list(csv.DictReader(fh)) exceptions = [] def exc(source, row, rec_id, etype, disposition, raw_date, norm_date, raw_amt, norm_amt, product, status, explanation, policy, related="", follow_up=""): exceptions.append(OrderedDict([ ("exception_id", ""), ("source_file", source), ("row_id", row), ("record_id", rec_id), ("exception_type", etype), ("disposition", disposition), ("raw_date", raw_date), ("normalized_date", iso(norm_date)), ("raw_amount", raw_amt), ("amount_cad", m(norm_amt)), ("product", product), ("status", status), ("related_row_or_record", related), ("explanation", explanation), ("policy_basis", policy), ("follow_up", follow_up), ])) # ---------------------------------------------------------------- SALES sales_raw = load("sales.csv") sales = [] for r in sales_raw: sales.append({ "row_id": r["row_id"].strip(), "order_id": r["order_id"].strip(), "raw_date": r["date"], "date": parse_date(r["date"]), "raw_product": r["product"], "product": PRODUCTS.get(r["product"].strip().lower()), "status": r["status"].strip().lower(), "raw_gross": r["gross_cad"], "gross": parse_money(r["gross_cad"]), "updated_at": r["updated_at"].strip(), }) by_order = OrderedDict() for s in sales: by_order.setdefault(s["order_id"], []).append(s) clean_sales = [] for oid, rows in by_order.items(): # collapse exact duplicates (all normalised fields equal), keep first row id uniq = [] for s in rows: key = (s["date"], s["product"], s["status"], s["gross"], s["updated_at"]) hit = next((u for u in uniq if u["key"] == key), None) if hit: hit["collapsed"].append(s["row_id"]) exc("sales.csv", s["row_id"], oid, "exact_duplicate", "excluded", s["raw_date"], s["date"], s["raw_gross"], s["gross"], s["product"] or "", s["status"], f"Exact duplicate of {hit['row']['row_id']} (same order ID and all fields); collapsed so the order counts once.", "policy.md: 'Exact duplicate rows can be collapsed.'", related=hit["row"]["row_id"]) else: uniq.append({"key": key, "row": s, "collapsed": []}) # conflicting versions: keep latest updated_at uniq.sort(key=lambda u: datetime.fromisoformat(u["row"]["updated_at"])) keep = uniq[-1] for u in uniq[:-1]: s = u["row"] keep["collapsed"].append(s["row_id"]) exc("sales.csv", s["row_id"], oid, "superseded_conflicting_version", "excluded", s["raw_date"], s["date"], s["raw_gross"], s["gross"], s["product"] or "", s["status"], f"Conflicts with {keep['row']['row_id']} for the same order ID (gross {m(s['gross'])} vs {m(keep['row']['gross'])}); " f"{keep['row']['row_id']} has the later updated_at ({keep['row']['updated_at']} vs {s['updated_at']}) and is retained.", "policy.md: 'retain the latest updated_at record; do not count both.'", related=keep["row"]["row_id"]) s = keep["row"] if s["status"] != "paid": exc("sales.csv", s["row_id"], oid, f"status_{s['status']}", "excluded", s["raw_date"], s["date"], s["raw_gross"], s["gross"], s["product"] or "", s["status"], f"Order status is '{s['status']}', not paid; not revenue.", "policy.md: 'Only status paid is revenue; cancelled and pending are excluded.'") continue if s["date"] is None: exc("sales.csv", s["row_id"], oid, "invalid_date", "excluded_unresolved", s["raw_date"], None, s["raw_gross"], s["gross"], s["product"] or "", s["status"], "Date could not be parsed; value preserved.", "policy.md: date formats", follow_up="Confirm order date.") continue if not in_period(s["date"]): exc("sales.csv", s["row_id"], oid, "outside_reporting_period", "excluded", s["raw_date"], s["date"], s["raw_gross"], s["gross"], s["product"] or "", s["status"], f"Order date {iso(s['date'])} is outside 2026-10-01..2026-10-31.", "policy.md: 'Reporting period: October 1-31 inclusive.'", follow_up="Belongs to " + ("September" if s["date"] < P_START else "November") + " reporting.") continue if s["product"] is None: exc("sales.csv", s["row_id"], oid, "unmapped_product", "excluded_unresolved", s["raw_date"], s["date"], s["raw_gross"], s["gross"], s["raw_product"], s["status"], "Product is not Workshop or Course; not assigned.", "policy.md: 'Unmapped products must be flagged rather than assigned.'", follow_up="Confirm product.") continue if s["gross"] is None: exc("sales.csv", s["row_id"], oid, "missing_amount", "excluded_unresolved", s["raw_date"], s["date"], s["raw_gross"], None, s["product"], s["status"], "Gross amount missing/unparseable; unknown, not zero.", "policy.md: 'Empty values are unknown, never zero.'", follow_up="Obtain amount.") continue s["collapsed"] = keep["collapsed"] clean_sales.append(s) paid_orders = {s["order_id"]: s for s in clean_sales} all_paid_any_period = {s["order_id"] for s in sales if s["status"] == "paid"} # ---------------------------------------------------------------- REFUNDS refunds_raw = load("refunds.csv") refunds = [] for r in refunds_raw: refunds.append({ "row_id": r["row_id"].strip(), "refund_id": r["refund_id"].strip(), "order_id": r["order_id"].strip(), "raw_date": r["settled_date"], "date": parse_date(r["settled_date"]), "status": r["status"].strip().lower(), "raw_amount": r["amount_cad"], "amount": parse_money(r["amount_cad"]), }) by_ref = OrderedDict() for r in refunds: by_ref.setdefault(r["refund_id"], []).append(r) clean_refunds = [] applied_by_order = defaultdict(lambda: ZERO) for rid, rows in by_ref.items(): first = rows[0] collapsed = [] conflict = False for r in rows[1:]: same = all(r[k] == first[k] for k in ("order_id", "date", "status", "amount")) if same: collapsed.append(r["row_id"]) exc("refunds.csv", r["row_id"], rid, "exact_duplicate", "excluded", r["raw_date"], r["date"], r["raw_amount"], r["amount"], paid_orders.get(r["order_id"], {}).get("product", ""), r["status"], f"Exact duplicate of {first['row_id']} (same refund ID and all fields); collapsed so the refund is subtracted once.", "policy.md: 'Exact duplicate rows can be collapsed.' / 'Refund ID and ad ID are similarly unique.'", related=first["row_id"]) else: conflict = True exc("refunds.csv", r["row_id"], rid, "conflicting_refund_id", "excluded_unresolved", r["raw_date"], r["date"], r["raw_amount"], r["amount"], "", r["status"], f"Same refund ID as {first['row_id']} with different values and no updated_at to choose between them.", "policy.md: 'Refund ID and ad ID are similarly unique.'", related=first["row_id"], follow_up="Confirm which version is correct.") r = first order = paid_orders.get(r["order_id"]) prod = order["product"] if order else "" if conflict: exc("refunds.csv", r["row_id"], rid, "conflicting_refund_id", "excluded_unresolved", r["raw_date"], r["date"], r["raw_amount"], r["amount"], prod, r["status"], "Conflicting versions of this refund ID.", "policy.md: uniqueness", follow_up="Confirm which version is correct.") continue if r["status"] != "settled": exc("refunds.csv", r["row_id"], rid, f"refund_{r['status']}", "excluded", r["raw_date"], r["date"], r["raw_amount"], r["amount"], prod, r["status"], f"Refund status is '{r['status']}', not settled; not subtracted from October revenue (order {r['order_id']}).", "policy.md: 'Do not subtract pending refunds.'", follow_up="If it settles later it reduces revenue in the settlement month.") continue if r["date"] is None or not in_period(r["date"]): exc("refunds.csv", r["row_id"], rid, "settled_outside_reporting_period", "excluded", r["raw_date"], r["date"], r["raw_amount"], r["amount"], prod, r["status"], f"Settlement date {iso(r['date']) or r['raw_date']} is outside October 2026, although order {r['order_id']} is an October order.", "policy.md: 'less settled refunds whose settlement date falls in October'", follow_up="Apply in November reporting.") continue if order is None: why = ("no row for this order ID exists in sales.csv" if r["order_id"] not in {s["order_id"] for s in sales} else "the order is not an included paid October order") exc("refunds.csv", r["row_id"], rid, "no_valid_paid_order", "flagged_excluded", r["raw_date"], r["date"], r["raw_amount"], r["amount"], "", r["status"], f"Settled refund references order {r['order_id']} but {why}; cannot be tied to a product or to paid revenue.", "policy.md: 'A settled refund requires a valid paid order and a nonmissing amount; otherwise flag it and exclude it'", follow_up="Trace the order ID (typo? missing sales row?) before deciding whether October revenue should fall by this amount.") continue if r["amount"] is None: exc("refunds.csv", r["row_id"], rid, "missing_refund_amount", "flagged_excluded_unresolved", r["raw_date"], r["date"], r["raw_amount"], None, prod, r["status"], f"Settled in October against valid paid order {r['order_id']} ({prod}, gross {m(order['gross'])}) but amount is empty. " "Unknown, not zero; left blank and excluded from the calculable refund total. Net revenue is therefore provisional.", "policy.md: 'Empty values are unknown, never zero.' / 'Missing refund amounts mean the resulting net revenue is provisional'", follow_up="Obtain the settled amount from the payment processor; it then reduces Course and combined net revenue.") continue if applied_by_order[r["order_id"]] + r["amount"] > order["gross"]: exc("refunds.csv", r["row_id"], rid, "refund_exceeds_paid_gross", "flagged_excluded_unresolved", r["raw_date"], r["date"], r["raw_amount"], r["amount"], prod, r["status"], f"Refund(s) on {r['order_id']} would exceed paid gross {m(order['gross'])}.", "policy.md: 'Refunds cannot exceed paid gross; flag if encountered.'", follow_up="Verify amount.") continue applied_by_order[r["order_id"]] += r["amount"] r["product"] = prod r["collapsed"] = collapsed clean_refunds.append(r) # ---------------------------------------------------------------- ADS ads_raw = load("ads.csv") ads = [] for r in ads_raw: ads.append({ "row_id": r["row_id"].strip(), "ad_id": r["ad_id"].strip(), "raw_date": r["date"], "date": parse_date(r["date"]), "raw_product": r["product"], "product": PRODUCTS.get(r["product"].strip().lower()), "channel": r["channel"].strip().lower(), "raw_spend": r["spend_cad"], "spend": parse_money(r["spend_cad"]), }) by_ad = OrderedDict() for a in ads: by_ad.setdefault(a["ad_id"], []).append(a) clean_ads = [] for aid, rows in by_ad.items(): first = rows[0] collapsed = [] conflict = False for a in rows[1:]: same = all(a[k] == first[k] for k in ("date", "product", "channel", "spend")) if same: collapsed.append(a["row_id"]) exc("ads.csv", a["row_id"], aid, "exact_duplicate", "excluded", a["raw_date"], a["date"], a["raw_spend"], a["spend"], a["product"] or "", "", f"Exact duplicate of {first['row_id']} (same ad ID and all fields); collapsed so the spend counts once.", "policy.md: 'Exact duplicate rows can be collapsed.' / 'Refund ID and ad ID are similarly unique.'", related=first["row_id"]) else: conflict = True exc("ads.csv", a["row_id"], aid, "conflicting_ad_id", "excluded_unresolved", a["raw_date"], a["date"], a["raw_spend"], a["spend"], a["product"] or "", "", f"Conflicts with {first['row_id']}.", "policy.md: uniqueness", related=first["row_id"], follow_up="Confirm correct version.") a = first if conflict: exc("ads.csv", a["row_id"], aid, "conflicting_ad_id", "excluded_unresolved", a["raw_date"], a["date"], a["raw_spend"], a["spend"], a["product"] or "", "", "Conflicting versions of this ad ID.", "policy.md: uniqueness", follow_up="Confirm correct version.") continue if a["date"] is None or not in_period(a["date"]): exc("ads.csv", a["row_id"], aid, "outside_reporting_period", "excluded", a["raw_date"], a["date"], a["raw_spend"], a["spend"], a["product"] or "", "", f"Ad date {iso(a['date']) or a['raw_date']} is outside 2026-10-01..2026-10-31.", "policy.md: 'Reporting period: October 1-31 inclusive.'", follow_up="Belongs to November reporting.") continue if a["product"] is None: exc("ads.csv", a["row_id"], aid, "unmapped_product", "excluded_unresolved", a["raw_date"], a["date"], a["raw_spend"], a["spend"], a["raw_product"], "", "Product is not Workshop or Course; not assigned.", "policy.md: 'Unmapped products must be flagged rather than assigned.'", follow_up="Confirm product.") continue if a["spend"] is None: exc("ads.csv", a["row_id"], aid, "missing_amount", "excluded_unresolved", a["raw_date"], a["date"], a["raw_spend"], None, a["product"], "", "Spend missing; unknown, not zero.", "policy.md: 'Empty values are unknown, never zero.'", follow_up="Obtain spend.") continue a["collapsed"] = collapsed clean_ads.append(a) # ---------------------------------------------------------------- WRITE CSVs def write_csv(name, header, rows): with open(os.path.join(OUT, name), "w", newline="", encoding="utf-8") as fh: w = csv.writer(fh, lineterminator="\n") w.writerow(header) w.writerows(rows) write_csv("cleaned-sales.csv", ["row_id", "order_id", "date", "product", "status", "gross_cad", "updated_at", "collapsed_row_ids"], [[s["row_id"], s["order_id"], iso(s["date"]), s["product"], s["status"], m(s["gross"]), s["updated_at"], ";".join(s["collapsed"])] for s in clean_sales]) write_csv("cleaned-refunds.csv", ["row_id", "refund_id", "order_id", "product", "settled_date", "status", "amount_cad", "collapsed_row_ids"], [[r["row_id"], r["refund_id"], r["order_id"], r["product"], iso(r["date"]), r["status"], m(r["amount"]), ";".join(r["collapsed"])] for r in clean_refunds]) write_csv("cleaned-ads.csv", ["row_id", "ad_id", "date", "product", "channel", "spend_cad", "collapsed_row_ids"], [[a["row_id"], a["ad_id"], iso(a["date"]), a["product"], a["channel"], m(a["spend"]), ";".join(a["collapsed"])] for a in clean_ads]) order_key = {"sales.csv": 0, "refunds.csv": 1, "ads.csv": 2} exceptions.sort(key=lambda e: (order_key[e["source_file"]], e["row_id"])) for i, e in enumerate(exceptions, 1): e["exception_id"] = f"E{i:02d}" write_csv("exceptions.csv", list(exceptions[0].keys()), [list(e.values()) for e in exceptions]) # ---------------------------------------------------------------- METRICS metrics = OrderedDict() for p in ["Workshop", "Course", "Combined"]: sel = (lambda x: True) if p == "Combined" else (lambda x, p=p: x["product"] == p) orders = [s for s in clean_sales if sel(s)] gross = sum((s["gross"] for s in orders), ZERO) ref = sum((r["amount"] for r in clean_refunds if sel(r)), ZERO) spend = sum((a["spend"] for a in clean_ads if sel(a)), ZERO) net = gross - ref metrics[p] = { "orders": len(orders), "gross": gross, "refunds": ref, "net": net, "ads": spend, "contrib": net - spend, "per_order": (net / len(orders)).quantize(Decimal("0.01")) if orders else None, "net_per_ad": (net / spend).quantize(Decimal("0.01")) if spend else None, } for k in ("orders", "gross", "refunds", "net", "ads", "contrib"): assert metrics["Workshop"][k] + metrics["Course"][k] == metrics["Combined"][k], k # ---------------------------------------------------------------- RECONCILIATION def recon(source, all_rows, amt_key, clean_rows): n_all = len(all_rows) n_inc = len(clean_rows) ex = [e for e in exceptions if e["source_file"] == source] n_exc = len(ex) tot_all = sum((r[amt_key] for r in all_rows if r[amt_key] is not None), ZERO) tot_inc = sum((r[amt_key] for r in clean_rows), ZERO) tot_exc = sum((Decimal(e["amount_cad"]) for e in ex if e["amount_cad"]), ZERO) unknown = sum(1 for r in all_rows if r[amt_key] is None) inc_ids = {r["row_id"] for r in clean_rows} exc_ids = {e["row_id"] for e in ex} assert n_all == n_inc + n_exc, source assert inc_ids.isdisjoint(exc_ids) and inc_ids | exc_ids == {r["row_id"] for r in all_rows}, source assert tot_all == tot_inc + tot_exc, source return dict(n_all=n_all, n_inc=n_inc, n_exc=n_exc, tot_all=tot_all, tot_inc=tot_inc, tot_exc=tot_exc, unknown=unknown, ex=ex) rec = OrderedDict([ ("sales.csv", recon("sales.csv", sales, "gross", clean_sales)), ("refunds.csv", recon("refunds.csv", refunds, "amount", clean_refunds)), ("ads.csv", recon("ads.csv", ads, "spend", clean_ads)), ]) # sanity check on expected source row counts (inputs are only ever opened for reading) assert len(sales_raw) == 15 and len(refunds_raw) == 7 and len(ads_raw) == 7 # ---------------------------------------------------------------- SUMMARY W, C, T = metrics["Workshop"], metrics["Course"], metrics["Combined"] missing = [e for e in exceptions if e["exception_type"] == "missing_refund_amount"] orphan = [e for e in exceptions if e["exception_type"] == "no_valid_paid_order"] pending = [e for e in exceptions if e["exception_type"] == "refund_pending"] late = [e for e in exceptions if e["exception_type"] == "settled_outside_reporting_period"] L = [] A = L.append A("# Northline Workshops — October 2026 results (CAD, tax excluded)") A("") A("Period: 2026-10-01 to 2026-10-31 inclusive (inputs/policy.md, line 2). Sources: inputs/sales.csv, " "inputs/refunds.csv, inputs/ads.csv. All figures below are produced by `output/reconcile.py` from those files. Revision 2; changes from the first " "submission are listed in output/CHANGELOG.md and the first submission is kept in output/original-submission/.") A("") A("## 1. Headline") A("") if missing: e = missing[0] o = paid_orders[[r for r in refunds if r["row_id"] == e["row_id"]][0]["order_id"]] low_c = C["net"] - o["gross"] A(f"**Course and combined net revenue, contribution and net revenue per order are PROVISIONAL.** Refund " f"{e['record_id']} (refunds.csv {e['row_id']}) settled on {e['normalized_date']} against paid order " f"{o['order_id']} (sales.csv {o['row_id']}, Course, {m(o['gross'])}) but its amount is blank. Per policy.md it is " f"treated as unknown, not zero, and is left out of the calculable refund total. Workshop figures are not affected.") A("") A("| Measure | Workshop | Course (provisional) | Combined (provisional) |") A("|---|---:|---:|---:|") A(f"| Paid orders | {W['orders']} | {C['orders']} | {T['orders']} |") A(f"| Gross sales | {m(W['gross'])} | {m(C['gross'])} | {m(T['gross'])} |") A(f"| Applied refunds (settled in October, calculable) | {m(W['refunds'])} | {m(C['refunds'])} | {m(T['refunds'])} |") A(f"| Net revenue | {m(W['net'])} | {m(C['net'])} | {m(T['net'])} |") A(f"| Advertising spend | {m(W['ads'])} | {m(C['ads'])} | {m(T['ads'])} |") A(f"| Contribution after advertising | {m(W['contrib'])} | {m(C['contrib'])} | {m(T['contrib'])} |") A(f"| Net revenue per paid order | {m(W['per_order'])} | {m(C['per_order'])} | {m(T['per_order'])} |") A("") A("Calculations: net revenue = gross sales − applied refunds; contribution after advertising = net revenue − ad spend " "(policy.md); net revenue per paid order = net revenue ÷ paid orders.") A("") A(f"- Workshop: {m(W['gross'])} − {m(W['refunds'])} = {m(W['net'])}; − {m(W['ads'])} = {m(W['contrib'])}; " f"{m(W['net'])} ÷ {W['orders']} = {m(W['per_order'])}.") A(f"- Course: {m(C['gross'])} − {m(C['refunds'])} = {m(C['net'])}; − {m(C['ads'])} = {m(C['contrib'])}; " f"{m(C['net'])} ÷ {C['orders']} = {m(C['per_order'])}.") A(f"- Combined: {m(T['gross'])} − {m(T['refunds'])} = {m(T['net'])}; − {m(T['ads'])} = {m(T['contrib'])}; " f"{m(T['net'])} ÷ {T['orders']} = {m(T['per_order'])}.") A("") if missing: A(f"**Bound on the provisional figures (not an estimate).** Policy says refunds cannot exceed paid gross, so " f"{e['record_id']} can be at most {m(o['gross'])}. If it were the full {m(o['gross'])}: Course net " f"revenue {m(low_c)}, Course contribution {m(low_c - C['ads'])}, Course net revenue per order " f"{m((low_c / C['orders']).quantize(Decimal('0.01')))}; combined net revenue {m(T['net'] - o['gross'])}, combined " f"contribution {m(T['contrib'] - o['gross'])}, combined net revenue per order " f"{m(((T['net'] - o['gross']) / T['orders']).quantize(Decimal('0.01')))}. The table above is the upper limit of " f"that range; the true figures are lower by whatever {e['record_id']} turns out to be.") A("") A("## 2. What went into each figure") A("") A("**Paid orders / gross sales** (output/cleaned-sales.csv):") A("") A("| Product | Order (source row) | Date | Gross |") A("|---|---|---|---:|") for p in ["Workshop", "Course"]: for s in clean_sales: if s["product"] == p: note = f" (replaces/collapses {', '.join(s['collapsed'])})" if s["collapsed"] else "" A(f"| {p} | {s['order_id']} ({s['row_id']}){note} | {iso(s['date'])} | {m(s['gross'])} |") A("") A("**Applied refunds** (output/cleaned-refunds.csv):") A("") A("| Product | Refund (source row) | Order | Settled | Amount |") A("|---|---|---|---|---:|") for r in clean_refunds: note = f" (collapses {', '.join(r['collapsed'])})" if r["collapsed"] else "" A(f"| {r['product']} | {r['refund_id']} ({r['row_id']}){note} | {r['order_id']} | {iso(r['date'])} | {m(r['amount'])} |") A("") A("Neither applied refund exceeds the paid gross of its order (240.00 vs 480.00 on N1003; 60.00 vs 240.00 on N1006).") A("") A("**Advertising spend** (output/cleaned-ads.csv):") A("") A("| Product | Ad (source row) | Date | Channel | Spend |") A("|---|---|---|---|---:|") for p in ["Workshop", "Course"]: for a in clean_ads: if a["product"] == p: note = f" (collapses {', '.join(a['collapsed'])})" if a["collapsed"] else "" A(f"| {p} | {a['ad_id']} ({a['row_id']}){note} | {iso(a['date'])} | {a['channel']} | {m(a['spend'])} |") A("") ch = defaultdict(lambda: ZERO) for a in clean_ads: ch[a["channel"]] += a["spend"] A("Spend by channel: " + "; ".join(f"{k} {m(v)}" for k, v in sorted(ch.items())) + ". sales.csv has no channel field, so revenue cannot be tied to a channel.") A("") A("## 3. Reconciliation of included and excluded records") A("") A("Every source row appears exactly once: either in a cleaned file or in output/exceptions.csv (checked by the script).") A("") A("| Source file | Source rows | Included | Excluded | Sum of all stated amounts | Included amount | Excluded amount | Rows with unknown amount |") A("|---|---:|---:|---:|---:|---:|---:|---:|") for k, v in rec.items(): A(f"| {k} | {v['n_all']} | {v['n_inc']} | {v['n_exc']} | {m(v['tot_all'])} | {m(v['tot_inc'])} | {m(v['tot_exc'])} | {v['unknown']} |") A("") A("Excluded rows (full explanations and policy basis in output/exceptions.csv):") A("") A("| ID | Source row | Record | Reason | Amount |") A("|---|---|---|---|---:|") for e in exceptions: amt = e["amount_cad"] if e["amount_cad"] else "unknown (blank)" A(f"| {e['exception_id']} | {e['source_file']} {e['row_id']} | {e['record_id']} | {e['exception_type']} | {amt} |") A("") for k, v in rec.items(): parts = " + ".join(f"{e['amount_cad']} ({e['row_id']})" for e in v["ex"] if e["amount_cad"]) unk = [e["row_id"] for e in v["ex"] if not e["amount_cad"]] A(f"- {k}: {m(v['tot_inc'])} included + [{parts}] = {m(v['tot_inc'])} + {m(v['tot_exc'])} = {m(v['tot_all'])}" + (f"; plus {', '.join(unk)} with no stated amount." if unk else ".")) A("") A("## 4. Open items that could change the numbers") A("") n = 1 for e in missing: A(f"{n}. **{e['record_id']} (refunds.csv {e['row_id']}) — amount missing.** Makes Course and combined net revenue " f"provisional (section 1). Needs the settled amount from the payment record.") n += 1 for e in orphan: A(f"{n}. **{e['record_id']} (refunds.csv {e['row_id']}) — {e['amount_cad']} settled {e['normalized_date']} against an " f"order ID that is not in sales.csv.** Flagged and excluded as policy requires. If it turns out to belong to an " f"October paid order, net revenue falls by {e['amount_cad']}; the inputs do not say which product. " "Source conflict: policy.md says 'every refund here relates to an October order', but this refund cannot be " "matched to any order in sales.csv. The conflict is not resolved here; the explicit flag-and-exclude rule in " "policy.md was followed.") n += 1 for e in pending: A(f"{n}. **{e['record_id']} (refunds.csv {e['row_id']}) — {e['amount_cad']} pending** on a {e['product']} order. " f"Correctly not subtracted in October; it will reduce revenue in the month it settles.") n += 1 for e in late: A(f"{n}. **{e['record_id']} (refunds.csv {e['row_id']}) — {e['amount_cad']} settled {e['normalized_date']}** on a " f"{e['product']} order placed in October. Falls into November under the settlement-date rule.") n += 1 later_w = sum((Decimal(e["amount_cad"]) for e in pending + late if e["product"] == "Workshop" and e["amount_cad"]), ZERO) A("") A(f"Items {n-2 if pending and late else n-1}–{n-1} do not change October's reported result, but they mean {m(later_w)} " f"of October Workshop gross is already refunded or awaiting refund outside the period (the pending one only if it " f"settles). Together with the {m(W['refunds'])} applied, that is {m(later_w + W['refunds'])} of {m(W['gross'])} " f"Workshop gross ({(later_w + W['refunds']) / W['gross'] * 100:.1f}%).") A("") A("## 5. Recommendation") A("") A(f"1. **Do not move advertising budget between products on this month's data.** On the calculable figures both " f"products return the same net revenue per ad dollar: Workshop {m(W['net'])} ÷ {m(W['ads'])} = {W['net_per_ad']}, " f"Course {m(C['net'])} ÷ {m(C['ads'])} = {C['net_per_ad']}. The Course figure is the upper limit; it can only fall " f"once RF05 is known (to {((C['net'] - o['gross']) / C['ads']).quantize(Decimal('0.01'))} if RF05 is a full refund). RF04 (120.00, order not found) could also lower either product's figure." if missing else "1. Review ad allocation using the table in section 1.") A("2. **Close the two refund gaps before circulating October as final:** get the RF05 amount and trace order N9999 " "for RF04. Until then, label Course and combined results provisional.") c_max = C["refunds"] + o["gross"] A(f"3. **Ask why refunds are happening, on both products.** Workshop has the larger known refund amount: " f"{m(later_w + W['refunds'])} of {m(W['gross'])} gross ({(later_w + W['refunds']) / W['gross'] * 100:.1f}%) once " f"pending RF03 and November-settled RF06 are counted with the {m(W['refunds'])} applied, on three of its five " f"October orders (N1003, N1005, N1012). Course shows {m(C['refunds'])} known plus RF05, which is unknown; at the " f"{m(o['gross'])} ceiling Course would be {m(c_max)} of {m(C['gross'])} ({c_max / C['gross'] * 100:.1f}%). So which " f"product has the higher refund rate cannot be determined until RF05 is known. The inputs give no refund reasons.") A("4. **Fix the exports.** Each file contained a repeated row for an ID that should be unique (S04, R03, A04), and " "order N1008 was exported in two versions (S09, S10). Uncorrected, these would have overstated gross sales by " "600.00, refunds by 60.00 and ad spend by 100.00.") A("") A("**This is not profit.** Contribution after advertising is net revenue less ad spend only. No cost of delivery " "(COGS), payment fees, salaries or overhead were supplied (policy.md), so the " f"{m(T['contrib'])} combined figure says how much is left to cover those costs, not what the business earned. " "Ad spend is assigned to the product named on each ad row, as policy.md instructs; the inputs do not show which " "sales, if any, the ads produced.") A("") A("## 6. Method and assumptions") A("") A("- Cleaned files contain only the records counted in the totals, one row per order/refund/ad, keyed by the source " "`row_id` that was kept; `collapsed_row_ids` lists duplicate or superseded source rows folded into it. All other " "source rows are in exceptions.csv with raw date and raw amount preserved.") A("- Dates: ISO, DD/MM/YYYY and named-month forms normalised to YYYY-MM-DD (e.g. S02 `03/10/2026` → 2026-10-03, " "S03 `Oct 05 2026` → 2026-10-05, R02 `14/10/2026` → 2026-10-14). Money: `CAD`/`$` stripped, two decimals " "(S01, S02, A06). Product and channel: trimmed, case-insensitive (S01, S02, A01, A03). No unmapped products were found.") A("- Order of tests for sales: collapse exact duplicates → keep latest `updated_at` among conflicting rows → status " "must be paid → order date in October. An order's month is taken from the `date` column, not `updated_at` " "(assumption; they agree on month for every included order).") A("- Assumption: when exact duplicates are collapsed, the first row ID is kept (S03, R02, A03). The choice does not " "affect any total.") A("- Assumption: a refund's product is the product of its order in cleaned-sales.csv; refunds.csv has no product column.") A("- R06 (blank amount) is kept blank in exceptions.csv rather than placed in cleaned-refunds.csv, because policy.md " "says to exclude it from the calculable total.") A("- Limitation: results depend only on the three CSVs and policy.md; nothing was verified against a bank, payment " "processor or ad platform.") A("") with open(os.path.join(OUT, "summary.md"), "w", encoding="utf-8") as fh: fh.write("\n".join(L)) # ---------------------------------------------------------------- CONSOLE print("METRICS") for p, v in metrics.items(): print(p, {k: (str(x) if x is not None else None) for k, x in v.items()}) print("RECON") for k, v in rec.items(): print(k, {x: str(y) for x, y in v.items() if x != "ex"}, [e["row_id"] + ":" + e["exception_type"] for e in v["ex"]]) print("clean sales rows", [s["row_id"] for s in clean_sales]) print("clean refund rows", [r["row_id"] for r in clean_refunds]) print("clean ad rows", [a["row_id"] for a in clean_ads]) print("all assertions passed")