#!/usr/bin/env python3 """Northline Workshops - October 2026 reconciliation (standard library only). Reads inputs/*.csv (never modified); writes cleaned files, exceptions.csv and summary.md to output/.""" import csv, os, sys from datetime import datetime, date from decimal import Decimal, ROUND_HALF_UP, InvalidOperation from collections import OrderedDict HERE = os.path.dirname(os.path.abspath(__file__)) BASE = next((c for c in (os.getcwd(), os.path.dirname(HERE), HERE) if os.path.exists(os.path.join(c, "inputs", "sales.csv"))), None) if BASE is None: sys.exit("inputs/sales.csv not found") INP, OUT = os.path.join(BASE, "inputs"), os.path.join(BASE, "output") os.makedirs(OUT, exist_ok=True) P0, P1 = date(2026, 10, 1), date(2026, 10, 31) PRODUCTS = {"workshop": "Workshop", "course": "Course"} POL = "inputs/policy.md" TWO = Decimal("0.01") Z = Decimal(0) def pdate(s): s = (s or "").strip() for f in ("%Y-%m-%d", "%d/%m/%Y", "%b %d %Y", "%B %d %Y", "%b %d, %Y", "%B %d, %Y", "%d %b %Y", "%d %B %Y"): try: return datetime.strptime(s, f).date() except ValueError: pass return None def pmoney(s): t = (s or "").strip().upper().replace("CAD", "").replace("$", "").replace(",", "").strip() if not t: return None try: return Decimal(t).quantize(TWO, rounding=ROUND_HALF_UP) except InvalidOperation: return None m = lambda d: "" if d is None else f"{d:.2f}" mc = lambda d: f"{d:,.2f}" iso = lambda d: "" if d is None else d.isoformat() inp = lambda d: d is not None and P0 <= d <= P1 prod_of = lambda s: PRODUCTS.get((s or "").strip().lower()) key = lambda r: tuple((v or "").strip() for k, v in r.items() if k != "row_id") def read(name): with open(os.path.join(INP, name), newline="", encoding="utf-8") as f: return list(csv.DictReader(f)) def groups(rows, col): g = OrderedDict() for r in rows: g.setdefault(r[col].strip(), []).append(r) return g EXC = [] def exc(src, r, rec, order, product, ndate, rawamt, etype, disp, effect, expl, pol, follow, kept=""): EXC.append(OrderedDict([ ("source_file", src), ("row_id", r["row_id"]), ("record_id", rec), ("related_order_id", order), ("product", product or ""), ("normalized_date", iso(ndate)), ("raw_amount", rawamt), ("amount_cad", m(pmoney(rawamt))), ("exception_type", etype), ("disposition", disp), ("retained_row_id", kept), ("effect_on_october_totals", effect), ("explanation", expl), ("policy_reference", pol), ("follow_up", follow), ("raw_source_row", "|".join(v or "" for v in r.values()))])) def dedupe(rows, src, rec, datecol, amtcol, ordercol, what): """Collapse exact duplicates (all fields except row_id identical). Returns (distinct rows, collapsed ids).""" distinct, collapsed = [], [] for r in rows: hit = next((d for d in distinct if key(d) == key(r)), None) if hit is None: distinct.append(r) continue collapsed.append(r["row_id"]) exc(src, r, rec, r[ordercol].strip() if ordercol else "", prod_of(r.get("product")), pdate(r[datecol]), r[amtcol], "exact_duplicate", "excluded (collapsed)", f"None - {what} counted once via retained row {hit['row_id']}", f"Exact duplicate of {hit['row_id']} (same ID {rec}, all fields identical); collapsed.", f"{POL} para 2: exact duplicate rows can be collapsed; order, refund and ad IDs are unique", "None", hit["row_id"]) return distinct, collapsed # ------------------------------------------------------------------ SALES S_RAW = read("sales.csv") CS = [] SRC = "inputs/sales.csv" for oid, rows in groups(S_RAW, "order_id").items(): distinct, rel = dedupe(rows, SRC, oid, "date", "gross_cad", "order_id", "order") distinct.sort(key=lambda r: datetime.fromisoformat(r["updated_at"].strip())) r = distinct[-1] for o in distinct[:-1]: rel.append(o["row_id"]) exc(SRC, o, oid, oid, prod_of(o["product"]), pdate(o["date"]), o["gross_cad"], "superseded_conflicting_row", "excluded (superseded)", f"None - order counted once via {r['row_id']} at gross {m(pmoney(r['gross_cad']))}", f"Conflicts with {r['row_id']} for order {oid}: this row gross {m(pmoney(o['gross_cad']))}, updated_at {o['updated_at'].strip()}; " f"retained row gross {m(pmoney(r['gross_cad']))}, updated_at {r['updated_at'].strip()} (latest).", f"{POL} para 2: retain the latest updated_at record; do not count both", "Optional: confirm in order system why gross changed", r["row_id"]) d, p, st, g = pdate(r["date"]), prod_of(r["product"]), r["status"].strip().lower(), pmoney(r["gross_cad"]) a = (SRC, r, oid, oid, p, d, r["gross_cad"]) if st != "paid": exc(*a, f"status_{st}", "excluded", "Not counted as a paid order or revenue", f"Order status is '{st}'; only paid orders are revenue.", f"{POL} para 2: only status paid is revenue; cancelled and pending are excluded", "None" if st == "cancelled" else "Recheck at next close; counts only if it becomes paid") elif d is None: exc(*a, "invalid_date", "excluded (flagged)", "Not counted", "Date not parseable; raw value preserved.", f"{POL} para 3", "Obtain date") elif not inp(d): exc(*a, "out_of_period", "excluded", "Not counted in October", f"Order date {iso(d)} is outside the reporting period 2026-10-01 to 2026-10-31.", f"{POL} para 1: reporting period October 1-31 inclusive", "Belongs to " + ("September" if d < P0 else "November") + " 2026 reporting") elif p is None: exc(*a, "unmapped_product", "excluded (flagged)", "Not counted", "Product is not Workshop or Course.", f"{POL} para 3: unmapped products must be flagged rather than assigned", "Map product") elif g is None: exc(*a, "missing_gross", "excluded (flagged)", "Not counted; totals provisional", "Gross empty or unparseable (unknown, not zero).", f"{POL} para 3", "Obtain amount") else: CS.append(OrderedDict([("row_id", r["row_id"]), ("duplicate_or_superseded_row_ids", ";".join(rel)), ("order_id", oid), ("date", iso(d)), ("product", p), ("status", st), ("gross_cad", m(g)), ("updated_at", r["updated_at"].strip())])) PAID = {s["order_id"]: s for s in CS} # ------------------------------------------------------------------ REFUNDS R_RAW = read("refunds.csv") CR = [] SRC = "inputs/refunds.csv" applied = {} for rid, rows in groups(R_RAW, "refund_id").items(): distinct, rel = dedupe(rows, SRC, rid, "settled_date", "amount_cad", "order_id", "refund") if len(distinct) > 1: for r in distinct: exc(SRC, r, rid, r["order_id"].strip(), "", pdate(r["settled_date"]), r["amount_cad"], "conflicting_rows", "excluded (flagged)", "Not subtracted", "Rows share a refund ID but differ; no updated_at to choose.", f"{POL} para 2", "Resolve") continue r = distinct[0] o, d, st, amt = r["order_id"].strip(), pdate(r["settled_date"]), r["status"].strip().lower(), pmoney(r["amount_cad"]) po = PAID.get(o) p = po["product"] if po else "" odesc = f"order {o} ({p}, paid gross {po['gross_cad']})" if po else f"order {o}" a = (SRC, r, rid, o, p, d, r["amount_cad"]) if st != "settled": exc(*a, f"refund_{st}", "excluded", "Not subtracted from October revenue", f"Refund status is '{st}', not settled; {odesc} stays at full gross in October.", f"{POL} para 1: do not subtract pending refunds", "Track; reduces net revenue in the month it settles") elif d is None: exc(*a, "invalid_date", "excluded (flagged)", "Not subtracted", "Settlement date not parseable.", f"{POL} para 3", "Obtain date") elif not inp(d): exc(*a, "settled_out_of_period", "excluded", "Not subtracted from October revenue", f"Settled {iso(d)}, outside October. It relates to October {odesc}, but only refunds settled in October reduce October revenue.", f"{POL} para 1: less settled refunds whose settlement date falls in October", "Subtract in November 2026 results") elif po is None: exc(*a, "no_valid_paid_order", "excluded (flagged)", "Not subtracted; no product can be assigned", f"Order {o} does not appear in inputs/sales.csv, so there is no valid paid order (or product) to apply this settled refund to.", f"{POL} para 2: a settled refund requires a valid paid order and a nonmissing amount; otherwise flag and exclude", "Investigate: wrong order ID, sale missing from export, or erroneous refund") elif amt is None: exc(*a, "missing_refund_amount", "excluded (flagged)", f"Not subtracted (unknown, not zero); {p} and combined net revenue are PROVISIONAL", f"Settled in October against valid paid {odesc}, but amount_cad is empty. Left blank, not estimated.", f"{POL} para 2 (nonmissing amount required) and para 3 (empty values are unknown, never zero; missing refund amounts make net revenue provisional)", "Obtain settled amount from payment records, then restate Course and combined figures") elif applied.get(o, Z) + amt > Decimal(po["gross_cad"]): exc(*a, "refund_exceeds_gross", "excluded (flagged)", "Not subtracted", f"Refunds on {odesc} would exceed paid gross.", f"{POL} para 3: refunds cannot exceed paid gross; flag if encountered", "Investigate") else: applied[o] = applied.get(o, Z) + amt CR.append(OrderedDict([("row_id", r["row_id"]), ("duplicate_row_ids", ";".join(rel)), ("refund_id", rid), ("order_id", o), ("product", p), ("settled_date", iso(d)), ("status", st), ("amount_cad", m(amt))])) for e in EXC: # refunds.csv has no product column: take it from the paid order where one exists if e["source_file"] == "inputs/refunds.csv" and not e["product"] and e["related_order_id"] in PAID: e["product"] = PAID[e["related_order_id"]]["product"] # ------------------------------------------------------------------ ADS A_RAW = read("ads.csv") CA = [] SRC = "inputs/ads.csv" for aid, rows in groups(A_RAW, "ad_id").items(): distinct, rel = dedupe(rows, SRC, aid, "date", "spend_cad", None, "ad") if len(distinct) > 1: for r in distinct: exc(SRC, r, aid, "", prod_of(r["product"]), pdate(r["date"]), r["spend_cad"], "conflicting_rows", "excluded (flagged)", "Not counted", "Rows share an ad ID but differ; no updated_at to choose.", f"{POL} para 2", "Resolve") continue r = distinct[0] d, p, sp = pdate(r["date"]), prod_of(r["product"]), pmoney(r["spend_cad"]) a = (SRC, r, aid, "", p, d, r["spend_cad"]) if d is None: exc(*a, "invalid_date", "excluded (flagged)", "Not counted", "Date not parseable.", f"{POL} para 3", "Obtain date") elif not inp(d): exc(*a, "out_of_period", "excluded", "Not counted in October ad spend", f"Ad date {iso(d)} is outside the reporting period 2026-10-01 to 2026-10-31.", f"{POL} para 1: reporting period October 1-31 inclusive", "Belongs to November 2026 reporting") elif p is None: exc(*a, "unmapped_product", "excluded (flagged)", "Not counted", "Product is not Workshop or Course.", f"{POL} para 3", "Map product") elif sp is None: exc(*a, "missing_spend", "excluded (flagged)", "Not counted; totals provisional", "Spend empty (unknown, not zero).", f"{POL} para 3", "Obtain amount") else: CA.append(OrderedDict([("row_id", r["row_id"]), ("duplicate_row_ids", ";".join(rel)), ("ad_id", aid), ("date", iso(d)), ("product", p), ("channel", r["channel"].strip().title()), ("spend_cad", m(sp))])) # ------------------------------------------------------------------ WRITE + RE-READ def write(name, rows): with open(os.path.join(OUT, name), "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=list(rows[0].keys())) w.writeheader() w.writerows(rows) def reread(name): with open(os.path.join(OUT, name), newline="", encoding="utf-8") as f: return list(csv.DictReader(f)) rank = {"inputs/sales.csv": 0, "inputs/refunds.csv": 1, "inputs/ads.csv": 2} EXC.sort(key=lambda e: (rank[e["source_file"]], e["row_id"])) write("cleaned-sales.csv", CS); write("cleaned-refunds.csv", CR); write("cleaned-ads.csv", CA); write("exceptions.csv", EXC) cs, cr, ca, ex = (reread(n) for n in ("cleaned-sales.csv", "cleaned-refunds.csv", "cleaned-ads.csv", "exceptions.csv")) # ------------------------------------------------------------------ METRICS (from the written files) M = {} for p in ("Workshop", "Course", "Combined"): sel = lambda x, p=p: p == "Combined" or x["product"] == p n = sum(1 for s in cs if sel(s)) gross = sum((Decimal(s["gross_cad"]) for s in cs if sel(s)), Z) ref = sum((Decimal(s["amount_cad"]) for s in cr if sel(s)), Z) ads = sum((Decimal(s["spend_cad"]) for s in ca if sel(s)), Z) M[p] = dict(n=n, gross=gross, ref=ref, net=gross - ref, ads=ads, contrib=gross - ref - ads, per=((gross - ref) / n).quantize(TWO, rounding=ROUND_HALF_UP)) W, C, T = M["Workshop"], M["Course"], M["Combined"] for k in ("n", "gross", "ref", "net", "ads", "contrib"): assert W[k] + C[k] == T[k], k def recon(raw, clean, src, col): exs = [e for e in ex if e["source_file"] == src] ids = sorted([c["row_id"] for c in clean] + [e["row_id"] for e in exs]) assert ids == sorted(r["row_id"] for r in raw) and len(set(ids)) == len(ids), src raw_amt = sum((pmoney(r[col]) or Z for r in raw), Z) exc_amt = sum((Decimal(e["amount_cad"]) for e in exs if e["amount_cad"]), Z) blanks = sum(1 for r in raw if pmoney(r[col]) is None) assert blanks == sum(1 for e in exs if not e["amount_cad"]), src return dict(raw_n=len(raw), inc_n=len(clean), exc_n=len(exs), raw=raw_amt, exc=exc_amt, inc=raw_amt - exc_amt, blanks=blanks, ids=", ".join(e["row_id"] for e in exs)) RS, RR, RA = recon(S_RAW, cs, "inputs/sales.csv", "gross_cad"), recon(R_RAW, cr, "inputs/refunds.csv", "amount_cad"), recon(A_RAW, ca, "inputs/ads.csv", "spend_cad") assert RS["inc"] == T["gross"] and RR["inc"] == T["ref"] and RA["inc"] == T["ads"] # independent expectations worked by hand from the raw files (guards against logic slips) EXPECT = {"Workshop": (5, "1920", "240", "1680", "300", "1380", "336.00"), "Course": (4, "900", "60", "840", "150", "690", "210.00"), "Combined": (9, "2820", "300", "2520", "450", "2070", "280.00")} for p, (n, g, rf, nt, ad, ct, per) in EXPECT.items(): x = M[p] assert (x["n"], x["gross"], x["ref"], x["net"], x["ads"], x["contrib"], x["per"]) == \ (n, Decimal(g), Decimal(rf), Decimal(nt), Decimal(ad), Decimal(ct), Decimal(per)), p byt = lambda t: [e for e in ex if e["exception_type"] == t] miss, pend, late, orphan = byt("missing_refund_amount"), byt("refund_pending"), byt("settled_out_of_period"), byt("no_valid_paid_order") assert len(miss) == 1 and len(pend) == 1 and len(late) == 1 and len(orphan) == 1 cap = Decimal(PAID[miss[0]["related_order_id"]]["gross_cad"]) pend_amt, late_amt, orphan_amt = Decimal(pend[0]["amount_cad"]), Decimal(late[0]["amount_cad"]), Decimal(orphan[0]["amount_cad"]) assert pend[0]["product"] == "Workshop" and late[0]["product"] == "Workshop" and miss[0]["product"] == "Course" chan = {} for x in ca: chan[(x["product"], x["channel"])] = chan.get((x["product"], x["channel"]), Z) + Decimal(x["spend_cad"]) ratio = lambda a, b: (a / b).quantize(TWO, rounding=ROUND_HALF_UP) pct = lambda a, b: f"{(a / b * 100).quantize(Decimal('0.1'), rounding=ROUND_HALF_UP)}%" def row(label, k, fmt=mc, prov=False): s = " ‡" if prov else "" return f"| {label} | {fmt(W[k])} | {fmt(C[k])}{s} | {fmt(T[k])}{s} |" L = [] A = L.append A("# Northline Workshops — October 2026 results (CAD, tax excluded)") A("") A("Period 2026-10-01 to 2026-10-31 inclusive. Rules: `inputs/policy.md`, \"Northline Workshops accounting note — October 2026\" " "(its only heading; paragraphs cited as para 1–3). Generated by `output/reconcile.py` from the three raw CSVs, which were read only.") A("") A("## 1. Bottom line") A("") A(f"- **{T['n']} paid orders; gross sales {mc(T['gross'])}; applied refunds {mc(T['ref'])}; net revenue {mc(T['net'])}; ad spend {mc(T['ads'])}; " f"contribution after advertising {mc(T['contrib'])}; net revenue per paid order {mc(T['per'])}.**") A("- **Course and combined net revenue, contribution and per-order figures are provisional.** Settled refund RF05 (row R06, order N1011, " "settled 2026-10-25) has no amount. Policy para 3 treats it as unknown, not zero, so it is left out of the calculable refund total. Workshop figures do not depend on it.") A("- Contribution after advertising is **not business profit**: no COGS, payment fees, salaries or overhead were supplied (policy para 1).") A("") A("## 2. Results") A("") A("| Measure | Workshop | Course | Combined |") A("|---|---:|---:|---:|") A(row("Paid order count", "n", fmt=str)) A(row("Gross sales", "gross")) A(row("Applied refunds (settled in October, calculable)", "ref", prov=True)) A(row("Net revenue = gross − applied refunds", "net", prov=True)) A(row("Advertising spend", "ads")) A(row("Contribution after advertising = net revenue − ad spend", "contrib", prov=True)) A(row("Net revenue per paid order", "per", prov=True)) A("") A("‡ Provisional: excludes the unknown amount of settled refund RF05 (R06).") A("") for p in ("Workshop", "Course", "Combined"): x = M[p] A(f"- {p}: {mc(x['gross'])} − {mc(x['ref'])} = {mc(x['net'])}; {mc(x['net'])} − {mc(x['ads'])} = {mc(x['contrib'])}; {mc(x['net'])} ÷ {x['n']} = {mc(x['per'])}.") A("") A(f"**Limit on the provisional figures (a bound, not an estimate).** Policy para 3 says refunds cannot exceed paid gross, and N1011's paid gross is {mc(cap)} (S13). " f"If RF05 is valid, the final figures therefore lie between these values: Course net revenue {mc(C['net'] - cap)} to {mc(C['net'])}; Course contribution " f"{mc(C['contrib'] - cap)} to {mc(C['contrib'])}; combined net revenue {mc(T['net'] - cap)} to {mc(T['net'])}; combined contribution " f"{mc(T['contrib'] - cap)} to {mc(T['contrib'])}. The lower end is a full refund of N1011. The actual amount is not in the inputs and has not been guessed.") A("") A("Included paid orders (`cleaned-sales.csv`):") A("") for p in ("Workshop", "Course"): A(f"- {p}: " + "; ".join(f"{s['order_id']} ({s['row_id']}) {s['gross_cad']}" for s in cs if s["product"] == p)) A("") A("Applied refunds (`cleaned-refunds.csv`): " + "; ".join( f"{r['refund_id']} ({r['row_id']}) {r['amount_cad']} on {r['order_id']}, {r['product']}, settled {r['settled_date']}" for r in cr) + ".") A("") A("Included ad spend (`cleaned-ads.csv`): " + "; ".join(f"{x['ad_id']} ({x['row_id']}) {x['product']} {x['channel']} {x['spend_cad']}" for x in ca) + ". By product and channel: " + "; ".join(f"{p} {c} {mc(v)}" for (p, c), v in sorted(chan.items(), reverse=True)) + ".") A("") A("## 3. Reconciliation of included and excluded records") A("") A("| Source file | Raw rows | Included (cleaned file) | Excluded (`exceptions.csv`) | Excluded row IDs |") A("|---|---:|---:|---:|---|") for nm, R in (("inputs/sales.csv", RS), ("inputs/refunds.csv", RR), ("inputs/ads.csv", RA)): A(f"| {nm} | {R['raw_n']} | {R['inc_n']} | {R['exc_n']} | {R['ids']} |") A(f"| Total | {RS['raw_n'] + RR['raw_n'] + RA['raw_n']} | {RS['inc_n'] + RR['inc_n'] + RA['inc_n']} | {RS['exc_n'] + RR['exc_n'] + RA['exc_n']} | |") A("") A("Each raw row ID appears exactly once, in a cleaned file or in `exceptions.csv`. Cleaned files hold only records that enter the totals; " "flagged-but-unresolved records (R05, R06) are kept in `exceptions.csv` with their raw values.") A("") A("| Amount tie-out (CAD) | All raw rows | Less excluded rows | = Included | Section 2 figure |") A("|---|---:|---:|---:|---|") A(f"| Sales gross | {mc(RS['raw'])} | {mc(RS['exc'])} | {mc(RS['inc'])} | Gross sales {mc(T['gross'])} |") A(f"| Refund amounts | {mc(RR['raw'])} + {RR['blanks']} blank | {mc(RR['exc'])} + {RR['blanks']} blank | {mc(RR['inc'])} | Applied refunds {mc(T['ref'])} |") A(f"| Ad spend | {mc(RA['raw'])} | {mc(RA['exc'])} | {mc(RA['inc'])} | Ad spend {mc(T['ads'])} |") A("") A("| Row | Record | Amount | Exception type | Effect on October totals |") A("|---|---|---:|---|---|") for e in ex: rec = e["record_id"] + (f" on {e['related_order_id']}" if e["source_file"].endswith("refunds.csv") else "") A(f"| {e['row_id']} | {rec} | {e['amount_cad'] or '(blank)'} | {e['exception_type']} | {e['effect_on_october_totals']} |") A("") A("`exceptions.csv` adds the explanation, policy reference, follow-up and the untouched raw row for each line above.") A("") A("Policy checks that found nothing: no unmapped products; no applied refund exceeds its order's paid gross " "(RF01 240.00 against 480.00 on N1003; RF02 60.00 against 240.00 on N1006); the blank RF05 amount is the only empty or unparseable value.") A("") A("## 4. Open items") A("") A(f"1. **RF05 (R06), Course order N1011: amount missing.** Course and combined figures stay provisional until the settled amount is obtained (limits in section 2).") A(f"2. **RF04 (R05): settled 2026-10-19 for {mc(orphan_amt)} against order N9999, which is not in `inputs/sales.csv`.** Flagged and excluded per policy para 2; " "no product can be assigned. If it proves to belong to a valid October paid order, October net revenue would fall by up to that amount; the inputs do not say which.") A(f"3. **RF03 (R04): pending {mc(pend_amt)} on Workshop order N1005.** Not subtracted (policy para 1). It will reduce net revenue in the month it settles.") A(f"4. **RF06 (R07): {mc(late_amt)} on Workshop order N1012, settled 2026-11-02.** The order (dated 2026-10-31) counts in October; the refund belongs to November.") A("5. **N1008: two conflicting rows.** S10 (180.00, updated 2026-10-16T09:00:00) was retained over S09 (120.00) under the latest-updated_at rule.") A("") A("## 5. Recommendation") A("") A(f"- **Do not shift ad budget between products on these numbers.** Both products returned the same calculable net revenue per ad dollar: Workshop " f"{mc(W['net'])} ÷ {mc(W['ads'])} = {ratio(W['net'], W['ads'])}; Course {mc(C['net'])} ÷ {mc(C['ads'])} = {ratio(C['net'], C['ads'])}. " f"The Course figure is provisional and could be as low as {ratio(C['net'] - cap, C['ads'])} if RF05 is a full refund. " f"The sample is {T['n']} orders in one month, and the inputs do not link individual orders to ads.") A(f"- **Close the RF05 and RF04 questions before treating October as final.** They are the two flagged refund records that leave October's figures open.") A(f"- **Look into Workshop refunds first.** Three of the five paid Workshop orders have a refund record (N1003 applied, N1005 pending, N1012 settled in November). " f"Applied plus pending plus November-settled Workshop refunds are {mc(W['ref'])} + {mc(pend_amt)} + {mc(late_amt)} = {mc(W['ref'] + pend_amt + late_amt)}, " f"or {pct(W['ref'] + pend_amt + late_amt, W['gross'])} of October Workshop gross, against {pct(W['ref'], W['gross'])} recognised in October. " f"Workshop's October net revenue of {mc(W['net'])} does not reflect the {mc(late_amt)} from RF06, which falls into November, or the {mc(pend_amt)} still pending on RF03. " "The inputs give no refund reasons, so the cause is not known.") A(f"- **Do not read {mc(T['contrib'])} as profit.** It is net revenue less advertising only. COGS, payment fees, salaries and overhead were not supplied, " "so whether October was profitable cannot be determined from these files.") A("") A("## 6. Method and assumptions") A("") A("- Dates: ISO, DD/MM/YYYY slash form and English month names accepted, output as YYYY-MM-DD (policy para 3). `updated_at` timestamps kept as supplied.") A("- Money: `CAD` and `$` stripped, two decimals; blanks kept blank. Product and channel trimmed and case-normalised (Workshop/Course; Search/Social).") A("- Order of tests per ID: collapse exact duplicates (first row kept), then latest `updated_at` for conflicts, then status, period, product and amount.") A("- Assumption: a refund's product is that of its order in `cleaned-sales.csv`; `inputs/refunds.csv` has no product column.") A("- Assumption: a refund that is settled but dated outside October is classed as out-of-period rather than tested further (applies to R07).") A("- Assumption: 'valid paid order' means an order retained in `cleaned-sales.csv`. N9999 fails because it is absent from the sales file entirely.") A("- Net revenue per ad dollar in section 5 is an added ratio for the recommendation, not a policy-defined measure.") A("") A("## 7. Checks performed by the script on this run") A("") A("- Totals recomputed from the written cleaned CSVs, not from in-memory values; Workshop + Course = Combined for every additive measure.") A("- Row-ID reconciliation: every raw row appears once in a cleaned file or `exceptions.csv`; amount tie-outs in section 3 match section 2.") A("- All 21 figures in the section 2 table matched values worked separately by hand from the raw files.") with open(os.path.join(OUT, "summary.md"), "w", encoding="utf-8") as f: f.write("\n".join(L) + "\n") # confirm raw inputs still parse to the same row counts (script opens them read-only) print("BASE", BASE) print("rows raw/included/excluded:", [(R["raw_n"], R["inc_n"], R["exc_n"]) for R in (RS, RR, RA)]) for p in M: print(p, {k: str(v) for k, v in M[p].items()}) print("ALL ASSERTIONS PASSED; files:", sorted(os.listdir(OUT)))