#!/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 and summary.md next to this script. Rules follow inputs/policy.md. """ import csv import os import re from datetime import datetime, date from decimal import Decimal, InvalidOperation HERE = os.path.dirname(os.path.abspath(__file__)) ROOT = os.path.dirname(HERE) INP = os.path.join(ROOT, "inputs") OUT = HERE PERIOD_START, PERIOD_END = date(2026, 10, 1), date(2026, 10, 31) PRODUCTS = {"workshop": "Workshop", "course": "Course"} ZERO = Decimal("0.00") def read(name): with open(os.path.join(INP, name), newline="", encoding="utf-8") as f: return list(csv.DictReader(f)) def parse_date(raw): """ISO, DD/MM/YYYY (policy: slash form is day-first) or named English month.""" s = (raw or "").strip() if not s: return None for fmt in ("%Y-%m-%d", "%d/%m/%Y", "%b %d %Y", "%B %d %Y", "%d %b %Y", "%d %B %Y", "%b %d, %Y", "%B %d, %Y"): try: return datetime.strptime(s, fmt).date() except ValueError: pass return None def parse_money(raw): """Empty -> None (unknown, never zero). Accepts CAD / $ decorations.""" 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 money(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 PERIOD_START <= d <= PERIOD_END def product(raw): return PRODUCTS.get((raw or "").strip().lower()) exceptions = [] def exc(src, row, rec_id, order_id, prod, category, raw_date, nd, raw_amt, na, reason, basis, effect, retained=""): exceptions.append({ "source_file": src, "row_id": row, "record_id": rec_id, "order_id": order_id, "product": prod or "", "category": category, "treatment": "excluded", "raw_date": raw_date, "normalized_date": iso(nd), "raw_amount": raw_amt, "normalized_amount_cad": money(na), "retained_row_id": retained, "reason": reason, "policy_basis": basis, "effect_on_totals": effect, }) def dedupe(rows, key, fields, order_field=None): """Collapse exact duplicates, then (if order_field) keep latest per key. Returns (kept_rows, dropped) where dropped = [(row, kind, kept_row)]. Exact duplicate = every raw field except row_id identical. """ groups = {} for r in rows: groups.setdefault(r[key].strip(), []).append(r) kept, dropped = [], [] for k, grp in groups.items(): uniq = [] for r in grp: sig = tuple(r[f] for f in fields) match = next((u for u in uniq if tuple(u[f] for f in fields) == sig), None) if match is not None: dropped.append((r, "exact_duplicate", match)) else: uniq.append(r) if len(uniq) > 1: if order_field is None: raise SystemExit(f"Conflicting rows for {k} with no tie-break rule") uniq.sort(key=lambda r: r[order_field]) winner = uniq[-1] if uniq[-2][order_field] == winner[order_field]: raise SystemExit(f"Tie on {order_field} for {k}; cannot resolve") for r in uniq[:-1]: dropped.append((r, "superseded", winner)) else: winner = uniq[0] kept.append(winner) kept.sort(key=lambda r: r["row_id"]) return kept, dropped # ---------------------------------------------------------------- SALES sales_raw = read("sales.csv") S_FIELDS = ["order_id", "date", "product", "status", "gross_cad", "updated_at"] s_kept, s_drop = dedupe(sales_raw, "order_id", S_FIELDS, "updated_at") related = {} for r, kind, w in s_drop: related.setdefault(w["row_id"], []).append(r["row_id"]) d, g, p = parse_date(r["date"]), parse_money(r["gross_cad"]), product(r["product"]) if kind == "exact_duplicate": exc("sales.csv", r["row_id"], r["order_id"], r["order_id"], p, "exact_duplicate", r["date"], d, r["gross_cad"], g, f"Exact duplicate of {w['row_id']} (same order ID and all fields); collapsed to one order.", "policy.md: 'Exact duplicate rows can be collapsed.'", f"Avoids double-counting {money(g)} gross and 1 order.", w["row_id"]) else: exc("sales.csv", r["row_id"], r["order_id"], r["order_id"], p, "superseded_by_later_update", r["date"], d, r["gross_cad"], g, f"Conflicts with {w['row_id']} for the same order ID; {w['row_id']} has later updated_at " f"({w['updated_at']} vs {r['updated_at']}) and gross {money(parse_money(w['gross_cad']))}, so it is retained.", "policy.md: 'retain the latest updated_at record; do not count both.'", f"{money(g)} not counted; order counted once at retained value.", w["row_id"]) clean_sales = [] for r in s_kept: d, g, p = parse_date(r["date"]), parse_money(r["gross_cad"]), product(r["product"]) st = r["status"].strip().lower() args = ("sales.csv", r["row_id"], r["order_id"], r["order_id"], p) tail = (r["date"], d, r["gross_cad"], g) if st != "paid": exc(*args, f"status_{st}", *tail, f"Order status is '{st}', not paid.", "policy.md: 'Only status paid is revenue; cancelled and pending are excluded.'", f"{money(g)} not counted as revenue; not a paid order.") elif d is None: exc(*args, "unparseable_date", *tail, "Order date missing or not in a valid format.", "policy.md: date formats paragraph", "Not counted; needs source check.") elif not in_period(d): exc(*args, "outside_reporting_period", *tail, f"Order date {iso(d)} is outside 2026-10-01 to 2026-10-31.", "policy.md: 'Reporting period: October 1-31 inclusive.'", f"{money(g)} belongs to another month's results.") elif p is None: exc(*args, "unmapped_product", *tail, f"Product '{r['product']}' is not Workshop or Course.", "policy.md: 'Unmapped products must be flagged rather than assigned.'", "Not counted.") elif g is None: exc(*args, "missing_amount", *tail, "Gross amount missing/unreadable.", "policy.md: 'Empty values are unknown, never zero.'", "Not counted; total would be provisional.") else: clean_sales.append({ "row_id": r["row_id"], "order_id": r["order_id"].strip(), "date": iso(d), "product": p, "status": st, "gross_cad": money(g), "updated_at": r["updated_at"].strip(), "collapsed_or_superseded_row_ids": ";".join(related.get(r["row_id"], [])), "_g": g, }) paid = {s["order_id"]: s for s in clean_sales} all_order_ids = {r["order_id"].strip() for r in sales_raw} # ---------------------------------------------------------------- REFUNDS ref_raw = read("refunds.csv") R_FIELDS = ["refund_id", "order_id", "settled_date", "status", "amount_cad"] r_kept, r_drop = dedupe(ref_raw, "refund_id", R_FIELDS) r_related = {} for r, kind, w in r_drop: r_related.setdefault(w["row_id"], []).append(r["row_id"]) a = parse_money(r["amount_cad"]) o = paid.get(r["order_id"].strip()) exc("refunds.csv", r["row_id"], r["refund_id"], r["order_id"], o["product"] if o else None, "exact_duplicate", r["settled_date"], parse_date(r["settled_date"]), r["amount_cad"], a, f"Exact duplicate of {w['row_id']} (same refund ID and all fields); collapsed to one refund.", "policy.md: 'Exact duplicate rows can be collapsed.' / 'Refund ID and ad ID are similarly unique.'", f"Avoids double-counting {money(a)} refund.", w["row_id"]) clean_ref, refunded_so_far, unresolved_missing = [], {}, [] for r in r_kept: d, a = parse_date(r["settled_date"]), parse_money(r["amount_cad"]) oid, st = r["order_id"].strip(), r["status"].strip().lower() o = paid.get(oid) prod = o["product"] if o else None args = ("refunds.csv", r["row_id"], r["refund_id"], oid, prod) tail = (r["settled_date"], d, r["amount_cad"], a) if st != "settled": exc(*args, f"refund_{st}", *tail, f"Refund status is '{st}', not settled.", "policy.md: 'Do not subtract pending refunds.'", f"{money(a)} not subtracted in October; possible later reduction if it settles.") elif d is None: exc(*args, "unparseable_date", *tail, "Settlement date missing or invalid.", "policy.md: date formats paragraph", "Not subtracted; needs source check.") elif not in_period(d): exc(*args, "settled_outside_reporting_period", *tail, f"Settlement date {iso(d)} is outside October 2026.", "policy.md: 'less settled refunds whose settlement date falls in October'", f"{money(a)} not subtracted in October; it falls in the month of settlement.") elif o is None: why = ("does not appear in sales.csv" if oid not in all_order_ids else "is not an included paid October order") exc(*args, "no_valid_paid_order", *tail, f"Order {oid} {why}, so the refund cannot be matched to a valid paid order or a product.", "policy.md: 'A settled refund requires a valid paid order and a nonmissing amount; otherwise flag it and exclude it'", f"{money(a)} not subtracted and not assigned to a product; needs investigation.") elif a is None: unresolved_missing.append((r, o)) exc(*args, "missing_refund_amount", *tail, f"Settled October refund against valid paid order {oid} ({prod}, gross {o['gross_cad']}) has an empty amount. " "Amount preserved as blank (unknown, not zero).", "policy.md: 'Empty values are unknown, never zero.' / 'Missing refund amounts mean the resulting net revenue is provisional'", f"Nothing subtracted; {prod} and combined net revenue, contribution and per-order figures are PROVISIONAL " f"(true refund is unknown, at most {o['gross_cad']} under the refund<=gross rule).") elif refunded_so_far.get(oid, ZERO) + a > o["_g"]: exc(*args, "refund_exceeds_paid_gross", *tail, f"Cumulative refunds would exceed paid gross {o['gross_cad']} for {oid}.", "policy.md: 'Refunds cannot exceed paid gross; flag if encountered.'", "Not subtracted; needs review.") else: refunded_so_far[oid] = refunded_so_far.get(oid, ZERO) + a clean_ref.append({ "row_id": r["row_id"], "refund_id": r["refund_id"].strip(), "order_id": oid, "order_product": prod, "settled_date": iso(d), "status": st, "amount_cad": money(a), "collapsed_duplicate_row_ids": ";".join(r_related.get(r["row_id"], [])), "_a": a, }) # ---------------------------------------------------------------- ADS ads_raw = read("ads.csv") A_FIELDS = ["ad_id", "date", "product", "channel", "spend_cad"] a_kept, a_drop = dedupe(ads_raw, "ad_id", A_FIELDS) a_related = {} for r, kind, w in a_drop: a_related.setdefault(w["row_id"], []).append(r["row_id"]) sp = parse_money(r["spend_cad"]) exc("ads.csv", r["row_id"], r["ad_id"], "", product(r["product"]), "exact_duplicate", r["date"], parse_date(r["date"]), r["spend_cad"], sp, f"Exact duplicate of {w['row_id']} (same ad ID and all fields); collapsed to one ad.", "policy.md: 'Exact duplicate rows can be collapsed.' / 'Refund ID and ad ID are similarly unique.'", f"Avoids double-counting {money(sp)} ad spend.", w["row_id"]) clean_ads = [] for r in a_kept: d, sp, p = parse_date(r["date"]), parse_money(r["spend_cad"]), product(r["product"]) args = ("ads.csv", r["row_id"], r["ad_id"], "", p) tail = (r["date"], d, r["spend_cad"], sp) if d is None: exc(*args, "unparseable_date", *tail, "Ad date missing or invalid.", "policy.md: date formats paragraph", "Not counted; needs source check.") elif not in_period(d): exc(*args, "outside_reporting_period", *tail, f"Ad date {iso(d)} is outside October 2026.", "policy.md: 'Reporting period: October 1-31 inclusive.' / 'October ads are all attributable...'", f"{money(sp)} belongs to another month's results.") elif p is None: exc(*args, "unmapped_product", *tail, f"Product '{r['product']}' is not Workshop or Course.", "policy.md: 'Unmapped products must be flagged rather than assigned.'", "Not counted.") elif sp is None: exc(*args, "missing_amount", *tail, "Spend missing/unreadable.", "policy.md: 'Empty values are unknown, never zero.'", "Not counted; total would be provisional.") else: clean_ads.append({ "row_id": r["row_id"], "ad_id": r["ad_id"].strip(), "date": iso(d), "product": p, "channel": r["channel"].strip().title(), "spend_cad": money(sp), "collapsed_duplicate_row_ids": ";".join(a_related.get(r["row_id"], [])), "_s": sp, }) # ---------------------------------------------------------------- WRITE CSVs def write(name, rows, cols): with open(os.path.join(OUT, name), "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=cols, extrasaction="ignore", lineterminator="\n") w.writeheader() w.writerows(rows) write("cleaned-sales.csv", clean_sales, ["row_id", "order_id", "date", "product", "status", "gross_cad", "updated_at", "collapsed_or_superseded_row_ids"]) write("cleaned-refunds.csv", clean_ref, ["row_id", "refund_id", "order_id", "order_product", "settled_date", "status", "amount_cad", "collapsed_duplicate_row_ids"]) write("cleaned-ads.csv", clean_ads, ["row_id", "ad_id", "date", "product", "channel", "spend_cad", "collapsed_duplicate_row_ids"]) file_order = {"sales.csv": 0, "refunds.csv": 1, "ads.csv": 2} exceptions.sort(key=lambda e: (file_order[e["source_file"]], e["row_id"])) for i, e in enumerate(exceptions, 1): e["exception_id"] = f"E{i:02d}" write("exceptions.csv", exceptions, ["exception_id", "source_file", "row_id", "record_id", "order_id", "product", "category", "treatment", "retained_row_id", "raw_date", "normalized_date", "raw_amount", "normalized_amount_cad", "reason", "policy_basis", "effect_on_totals"]) # ---------------------------------------------------------------- METRICS M = {} for p in ("Workshop", "Course", "Combined"): sel = lambda x, k="product": p == "Combined" or x[k] == p orders = [s for s in clean_sales if sel(s)] gross = sum((s["_g"] for s in orders), ZERO) refunds = sum((r["_a"] for r in clean_ref if sel(r, "order_product")), ZERO) ads = sum((a["_s"] for a in clean_ads if sel(a)), ZERO) net = gross - refunds M[p] = {"n": len(orders), "gross": gross, "ref": refunds, "net": net, "ads": ads, "contrib": net - ads, "per": (net / len(orders)).quantize(Decimal("0.01")), "ids": [s["order_id"] for s in orders]} # ---------------------------------------------------------------- CHECKS checks = [] def check(label, ok): checks.append((label, ok)) assert ok, label for name, raw, clean in (("sales.csv", sales_raw, clean_sales), ("refunds.csv", ref_raw, clean_ref), ("ads.csv", ads_raw, clean_ads)): ex_ids = [e["row_id"] for e in exceptions if e["source_file"] == name] cl_ids = [c["row_id"] for c in clean] check(f"{name}: every raw row ID appears exactly once in the cleaned file or exceptions.csv " f"({len(cl_ids)} + {len(ex_ids)} = {len(raw)})", sorted(ex_ids + cl_ids) == sorted(r["row_id"] for r in raw)) for p in ("gross", "ref", "ads", "net", "contrib"): check(f"Workshop + Course = Combined for {p}", M["Workshop"][p] + M["Course"][p] == M["Combined"][p]) check("Workshop + Course = Combined for order count", M["Workshop"]["n"] + M["Course"]["n"] == M["Combined"]["n"]) def raw_sum(rows, col): vals = [parse_money(r[col]) for r in rows] return sum((v for v in vals if v is not None), ZERO), sum(1 for v in vals if v is None) def exc_sum(name): vals = [e["normalized_amount_cad"] for e in exceptions if e["source_file"] == name] return sum((Decimal(v) for v in vals if v), ZERO), sum(1 for v in vals if not v) REC = {} for name, raw, col, inc in (("sales.csv", sales_raw, "gross_cad", M["Combined"]["gross"]), ("refunds.csv", ref_raw, "amount_cad", M["Combined"]["ref"]), ("ads.csv", ads_raw, "spend_cad", M["Combined"]["ads"])): rs, rblank = raw_sum(raw, col) es, eblank = exc_sum(name) check(f"{name}: raw stated amount {rs} = included {inc} + excluded {es} (blank amounts: {rblank})", rs == inc + es and rblank == eblank) REC[name] = {"rows": len(raw), "inc_rows": len(raw) - sum(1 for e in exceptions if e["source_file"] == name), "exc_rows": sum(1 for e in exceptions if e["source_file"] == name), "raw": rs, "inc": inc, "exc": es, "blank": rblank} check("No applied refund exceeds its order's paid gross", all(refunded_so_far[o] <= paid[o]["_g"] for o in refunded_so_far)) check("All cleaned dates fall in 2026-10-01..2026-10-31", all("2026-10-01" <= x <= "2026-10-31" for x in [s["date"] for s in clean_sales] + [r["settled_date"] for r in clean_ref] + [a["date"] for a in clean_ads])) check("All cleaned order/refund/ad IDs are unique", len({s["order_id"] for s in clean_sales}) == len(clean_sales) and len({r["refund_id"] for r in clean_ref}) == len(clean_ref) and len({a["ad_id"] for a in clean_ads}) == len(clean_ads)) # ---------------------------------------------------------------- SUMMARY def f(d): return f"{d:,.2f}" W, C, T = M["Workshop"], M["Course"], M["Combined"] miss_row, miss_order = unresolved_missing[0] assert len(unresolved_missing) == 1 and miss_order["product"] == "Course" cap = miss_order["_g"] exc_by = {e["row_id"]: e for e in exceptions} def ratio(a, b): return (a / b).quantize(Decimal("0.01")) def pct(a, b): return f"{(a / b * 100).quantize(Decimal('0.1'))}%" pending_w = Decimal(exc_by["R04"]["normalized_amount_cad"]) nov_w = Decimal(exc_by["R07"]["normalized_amount_cad"]) orphan = Decimal(exc_by["R05"]["normalized_amount_cad"]) assert exc_by["R04"]["product"] == "Workshop" and exc_by["R07"]["product"] == "Workshop" def cat_list(name): out = [] for e in exceptions: if e["source_file"] == name: amt = e["normalized_amount_cad"] or "blank" out.append(f"| {e['row_id']} | {e['record_id']} | {e['category']} | {amt} | {e['reason']} |") return "\n".join(out) def inc_list(rows, idcol, amtcol, extra): return "\n".join(f"| {r['row_id']} | {r[idcol]} | {extra(r)} | {r[amtcol]} |" for r in rows) summary = f"""# Northline Workshops — October 2026 results (CAD, tax excluded) **Status: PROVISIONAL.** One settled October refund (refunds.csv R06 / RF05, order N1011, Course) has no amount. Per inputs/policy.md ("Empty values are unknown, never zero"; "Missing refund amounts mean the resulting net revenue is provisional") nothing was subtracted for it, so every Course and Combined figure marked † below is the *calculable* value and can only go down once RF05 is known. Workshop figures are not affected by RF05. ## 1. Results | Measure | Workshop | Course | Combined | |---|---:|---:|---:| | Paid order count | {W['n']} | {C['n']} | {T['n']} | | Gross sales | {f(W['gross'])} | {f(C['gross'])} | {f(T['gross'])} | | Applied refunds (settled in October) | {f(W['ref'])} | {f(C['ref'])} † | {f(T['ref'])} † | | Net revenue | {f(W['net'])} | {f(C['net'])} † | {f(T['net'])} † | | Advertising spend | {f(W['ads'])} | {f(C['ads'])} | {f(T['ads'])} | | Contribution after advertising | {f(W['contrib'])} | {f(C['contrib'])} † | {f(T['contrib'])} † | | Net revenue per paid order | {f(W['per'])} | {f(C['per'])} † | {f(T['per'])} † | † Provisional: excludes the unknown amount of RF05. Formulas (inputs/policy.md): net revenue = paid gross − settled October refunds; contribution after advertising = net revenue − ad spend; net revenue per paid order = net revenue ÷ paid order count. - Workshop: {f(W['gross'])} − {f(W['ref'])} = {f(W['net'])}; {f(W['net'])} − {f(W['ads'])} = {f(W['contrib'])}; {f(W['net'])} ÷ {W['n']} = {f(W['per'])} - Course: {f(C['gross'])} − {f(C['ref'])} = {f(C['net'])}; {f(C['net'])} − {f(C['ads'])} = {f(C['contrib'])}; {f(C['net'])} ÷ {C['n']} = {f(C['per'])} - Combined: {f(T['gross'])} − {f(T['ref'])} = {f(T['net'])}; {f(T['net'])} − {f(T['ads'])} = {f(T['contrib'])}; {f(T['net'])} ÷ {T['n']} = {f(T['per'])} Paid orders — Workshop: {', '.join(W['ids'])}. Course: {', '.join(C['ids'])}. ### Range implied by the missing RF05 amount (a bound, not an estimate) Policy says refunds cannot exceed paid gross. N1011's paid gross is {f(cap)} (sales.csv S13), so if RF05 is a valid refund its amount lies between more than 0.00 and {f(cap)}. No value has been assumed. | Measure | Calculable (RF05 not subtracted) | If RF05 were the maximum {f(cap)} | |---|---:|---:| | Course net revenue | {f(C['net'])} | {f(C['net'] - cap)} | | Course contribution after advertising | {f(C['contrib'])} | {f(C['contrib'] - cap)} | | Course net revenue per paid order | {f(C['per'])} | {f(((C['net'] - cap) / C['n']).quantize(Decimal('0.01')))} | | Combined net revenue | {f(T['net'])} | {f(T['net'] - cap)} | | Combined contribution after advertising | {f(T['contrib'])} | {f(T['contrib'] - cap)} | | Combined net revenue per paid order | {f(T['per'])} | {f(((T['net'] - cap) / T['n']).quantize(Decimal('0.01')))} | ## 2. Reconciliation of included and excluded records | Source file | Raw rows | Included | Excluded | Raw stated amount | Included amount | Excluded stated amount | Blank amounts | |---|---:|---:|---:|---:|---:|---:|---:| | inputs/sales.csv (gross_cad) | {REC['sales.csv']['rows']} | {REC['sales.csv']['inc_rows']} | {REC['sales.csv']['exc_rows']} | {f(REC['sales.csv']['raw'])} | {f(REC['sales.csv']['inc'])} | {f(REC['sales.csv']['exc'])} | {REC['sales.csv']['blank']} | | inputs/refunds.csv (amount_cad) | {REC['refunds.csv']['rows']} | {REC['refunds.csv']['inc_rows']} | {REC['refunds.csv']['exc_rows']} | {f(REC['refunds.csv']['raw'])} | {f(REC['refunds.csv']['inc'])} | {f(REC['refunds.csv']['exc'])} | {REC['refunds.csv']['blank']} (R06) | | inputs/ads.csv (spend_cad) | {REC['ads.csv']['rows']} | {REC['ads.csv']['inc_rows']} | {REC['ads.csv']['exc_rows']} | {f(REC['ads.csv']['raw'])} | {f(REC['ads.csv']['inc'])} | {f(REC['ads.csv']['exc'])} | {REC['ads.csv']['blank']} | Raw stated amount = included + excluded in every file. Every raw row ID appears exactly once, either as `row_id` in a cleaned file or as a row in exceptions.csv (which carries the raw and normalized values). ### Sales — excluded rows (sales.csv) | Row | Order | Category | Gross | Reason | |---|---|---|---:|---| {cat_list('sales.csv')} ### Refunds — excluded rows (refunds.csv) | Row | Refund | Category | Amount | Reason | |---|---|---|---:|---| {cat_list('refunds.csv')} ### Ads — excluded rows (ads.csv) | Row | Ad | Category | Spend | Reason | |---|---|---|---:|---| {cat_list('ads.csv')} ### Included rows | Sales row | Order | Date / product | Gross | |---|---|---|---:| {inc_list(clean_sales, 'order_id', 'gross_cad', lambda r: r['date'] + ' ' + r['product'])} | Refund row | Refund | Order / product / settled | Amount | |---|---|---|---:| {inc_list(clean_ref, 'refund_id', 'amount_cad', lambda r: r['order_id'] + ' ' + r['order_product'] + ' ' + r['settled_date'])} | Ad row | Ad | Date / product / channel | Spend | |---|---|---|---:| {inc_list(clean_ads, 'ad_id', 'spend_cad', lambda r: r['date'] + ' ' + r['product'] + ' ' + r['channel'])} Policy flags checked and not encountered: no unmapped products (all rows map to Workshop or Course), and no applied refund exceeds its order's paid gross (RF01 240.00 vs N1003 480.00; RF02 60.00 vs N1006 240.00). ## 3. Recommendation 1. **Do not finalize October until RF05's amount is obtained** from the payment/refund system (refunds.csv R06). Each dollar of RF05 lowers Course and Combined net revenue and contribution one-for-one, by up to {f(cap)}. 2. **Do not move ad budget between products on this month's figures.** On the calculable numbers the two products are identical per ad dollar: net revenue ÷ ad spend is {ratio(W['net'], W['ads'])} for Workshop ({f(W['net'])} ÷ {f(W['ads'])}) and {ratio(C['net'], C['ads'])} for Course ({f(C['net'])} ÷ {f(C['ads'])}); contribution ÷ ad spend is {ratio(W['contrib'], W['ads'])} for both. RF05 can only lower the Course side. These ratios are my supplementary arithmetic, not policy-defined measures; they rest on {T['n']} orders and on the policy's product-level attribution of spend, which does not show that the ads caused the orders. 3. **Look into Workshop refunds before scaling Workshop spend.** Besides the {f(W['ref'])} applied in October ({pct(W['ref'], W['gross'])} of Workshop gross), two more refunds against October Workshop orders are outside this month's result: RF06, {f(nov_w)} on N1012, settled 2026-11-02 (it will reduce November's net revenue), and RF03, {f(pending_w)} on N1005, still pending. If RF03 also settles, {f(W['ref'] + nov_w + pending_w)} of {f(W['gross'])} October Workshop gross ({pct(W['ref'] + nov_w + pending_w, W['gross'])}) will have been refunded. 4. **Trace refund RF04** ({f(orphan)}, refunds.csv R05): its order N9999 is not in sales.csv. It is excluded as policy requires; if it turns out to belong to a valid paid October order missing from the export, October net revenue would be lower by that amount. 5. **Fix the exports at source**: one duplicated order row (S04), one duplicated refund row (R03), one duplicated ad row (A04) and one order with two conflicting gross values (N1008: S09 120.00 vs S10 180.00). **This is contribution after advertising, not profit.** inputs/policy.md states that no COGS, payment fees, salaries or overhead are supplied. The {f(T['contrib'])} (provisional) is what October sales left over after refunds and advertising to cover those unsupplied costs; whether the business made a profit cannot be determined from these inputs. ## 4. Assumptions and limitations - The sales `date` column is used for the October test (policy does not name the column). No retained order would change status under `updated_at` instead. - For exact duplicates the first row ID is retained (S03, R02, A03) and the later one excluded (S04, R03, A04); the choice does not affect any value. "Exact duplicate" means all raw fields other than row_id match. - Cleaned files contain only records that enter the totals. Excluded and unresolved rows, including R06 with its blank amount preserved, are in exceptions.csv with raw and normalized values. - `order_product` in cleaned-refunds.csv is derived from the matched order in cleaned-sales.csv; refunds.csv has no product column. RF04 (N9999) therefore has no product and is not assigned to one. - Ad channel is normalized to trimmed title case (Search, Social); product to Workshop/Course. - Deduplication and latest-`updated_at` selection are applied before status and period tests. - The RF05 range and the per-ad-dollar and refund-share figures are supplementary calculations. ## 5. Checks performed (by output/reconcile.py, all passed on this run) {chr(10).join('- ' + c[0] for c in checks)} """ with open(os.path.join(OUT, "summary.md"), "w", encoding="utf-8") as fh: fh.write(summary) # ---------------------------------------------------------------- CONSOLE for p in ("Workshop", "Course", "Combined"): m = M[p] print(p, m["n"], m["gross"], m["ref"], m["net"], m["ads"], m["contrib"], m["per"], m["ids"]) print(REC) for c in checks: print("PASS" if c[1] else "FAIL", c[0]) print(len(exceptions), "exceptions")