import csv, re, os from datetime import datetime, date from decimal import Decimal, ROUND_HALF_UP IN = "inputs"; OUT = "output" P0, P1 = date(2026, 10, 1), date(2026, 10, 31) PRODUCTS = {"workshop": "Workshop", "course": "Course"} def read(name): with open(os.path.join(IN, name), newline="", encoding="utf-8") as f: return list(csv.DictReader(f)) def pdate(s): s = (s 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"): try: return datetime.strptime(s, fmt).date() except ValueError: pass return None def pmoney(s): s = (s or "").strip() if not s: return None t = re.sub(r"(?i)cad|\$|,", "", s).strip() try: return Decimal(t).quantize(Decimal("0.01"), ROUND_HALF_UP) except Exception: return None def m(d): return f"{d:.2f}" def prod(s): return PRODUCTS.get((s or "").strip().lower()) exceptions = [] def etype(reason): r = reason.lower() if "duplicate" in r or "superseded" in r: return "excluded_duplicate_or_superseded" if "outside october" in r: return "excluded_out_of_period" if "status" in r: return "excluded_status" if "not a valid october paid order" in r: return "flagged_invalid_order_reference" if "amount missing" in r: return "flagged_missing_amount" return "excluded_other" def exc(src, row, key, issue, action, amount, et=None): exceptions.append(dict(source_file=src, row_id=row["row_id"], record_id=key, exception_type=et or etype(issue), issue=issue, action=action, row_amount_cad=amount)) # ---------- SALES ---------- sales = read("sales.csv") for r in sales: r["_date"] = pdate(r["date"]); r["_gross"] = pmoney(r["gross_cad"]) r["_prod"] = prod(r["product"]); r["_status"] = r["status"].strip().lower() r["_upd"] = datetime.fromisoformat(r["updated_at"].strip()) r["_reason"] = "" # duplicate / conflict resolution by order_id by_order = {} for r in sales: by_order.setdefault(r["order_id"].strip(), []).append(r) for oid, rows in by_order.items(): if len(rows) == 1: continue latest = max(rows, key=lambda x: x["_upd"]) seen = [] for r in rows: sig = tuple((k, v) for k, v in r.items() if k in ("order_id","date","product","status","gross_cad","updated_at")) if r is latest: seen.append(sig); continue if sig in seen or sig == tuple((k, v) for k, v in latest.items() if k in ("order_id","date","product","status","gross_cad","updated_at")): r["_reason"] = f"Exact duplicate of order {oid}; collapsed" else: r["_reason"] = f"Conflicting row for order {oid}; superseded by later updated_at {latest['updated_at']} ({latest['row_id']})" seen.append(sig) for r in sales: if r["_reason"]: continue if r["_date"] is None: r["_reason"] = "Invalid/missing date; unresolved" elif not (P0 <= r["_date"] <= P1): r["_reason"] = f"Order date {r['_date']} outside October 2026" elif r["_status"] != "paid": r["_reason"] = f"Status '{r['_status']}' is not revenue" elif r["_prod"] is None: r["_reason"] = "Unmapped product; flagged, not assigned" elif r["_gross"] is None: r["_reason"] = "Missing/invalid gross; unknown, not zero" paid_orders = {} with open(os.path.join(OUT, "cleaned-sales.csv"), "w", newline="", encoding="utf-8") as f: w = csv.writer(f) w.writerow(["row_id","order_id","date","product","status","gross_cad","updated_at","included_in_totals","exclusion_reason"]) for r in sales: inc = not r["_reason"] if inc: paid_orders[r["order_id"].strip()] = r w.writerow([r["row_id"], r["order_id"].strip(), r["_date"].isoformat() if r["_date"] else r["date"], r["_prod"] or r["product"], r["_status"], m(r["_gross"]) if r["_gross"] is not None else r["gross_cad"], r["updated_at"], "yes" if inc else "no", r["_reason"]]) if not inc: exc("sales.csv", r, r["order_id"], r["_reason"], "Excluded from paid orders and gross", m(r["_gross"]) if r["_gross"] is not None else "unknown") # ---------- REFUNDS ---------- refunds = read("refunds.csv") seen = {} for r in refunds: r["_date"] = pdate(r["settled_date"]); r["_amt"] = pmoney(r["amount_cad"]) r["_status"] = r["status"].strip().lower(); r["_reason"] = ""; r["_prod"] = "" _o = paid_orders.get(r["order_id"].strip()) if _o: r["_prod"] = _o["_prod"] rid = r["refund_id"].strip() sig = tuple(v for k, v in r.items() if k in ("refund_id","order_id","settled_date","status","amount_cad")) if rid in seen: r["_reason"] = (f"Exact duplicate of refund {rid} ({seen[rid][0]}); collapsed" if seen[rid][1] == sig else f"Conflicting duplicate refund ID {rid}; flagged") continue seen[rid] = (r["row_id"], sig) o = paid_orders.get(r["order_id"].strip()) if o: r["_prod"] = o["_prod"] if r["_status"] != "settled": r["_reason"] = f"Status '{r['_status']}'; pending refunds not subtracted" elif r["_date"] is None: r["_reason"] = "Invalid/missing settlement date" elif not (P0 <= r["_date"] <= P1): r["_reason"] = f"Settled {r['_date']} outside October 2026" elif o is None: r["_reason"] = f"Order {r['order_id']} is not a valid October paid order; flagged" elif r["_amt"] is None: r["_reason"] = "Settled refund amount missing (unknown, not zero); excluded from calculable total; net revenue provisional" applied_by_order = {} with open(os.path.join(OUT, "cleaned-refunds.csv"), "w", newline="", encoding="utf-8") as f: w = csv.writer(f) w.writerow(["row_id","refund_id","order_id","product","settled_date","status","amount_cad","included_in_totals","exclusion_reason"]) for r in refunds: inc = not r["_reason"] if inc: applied_by_order[r["order_id"]] = applied_by_order.get(r["order_id"], Decimal(0)) + r["_amt"] w.writerow([r["row_id"], r["refund_id"], r["order_id"], r["_prod"], r["_date"].isoformat() if r["_date"] else r["settled_date"], r["_status"], m(r["_amt"]) if r["_amt"] is not None else r["amount_cad"], "yes" if inc else "no", r["_reason"]]) if not inc: exc("refunds.csv", r, r["refund_id"], r["_reason"], "Not subtracted from net revenue", m(r["_amt"]) if r["_amt"] is not None else "unknown") for oid, a in applied_by_order.items(): assert a <= paid_orders[oid]["_gross"], f"refund exceeds gross on {oid}" # ---------- ADS ---------- ads = read("ads.csv") seen = {} for r in ads: r["_date"] = pdate(r["date"]); r["_amt"] = pmoney(r["spend_cad"]) r["_prod"] = prod(r["product"]); r["_chan"] = r["channel"].strip().lower(); r["_reason"] = "" aid = r["ad_id"].strip() sig = tuple(v for k, v in r.items() if k in ("ad_id","date","product","channel","spend_cad")) if aid in seen: r["_reason"] = (f"Exact duplicate of ad {aid} ({seen[aid][0]}); collapsed" if seen[aid][1] == sig else f"Conflicting duplicate ad ID {aid}; flagged") continue seen[aid] = (r["row_id"], sig) if r["_date"] is None: r["_reason"] = "Invalid/missing date" elif not (P0 <= r["_date"] <= P1): r["_reason"] = f"Ad date {r['_date']} outside October 2026" elif r["_prod"] is None: r["_reason"] = "Unmapped product; flagged, not assigned" elif r["_amt"] is None: r["_reason"] = "Missing spend; unknown, not zero" with open(os.path.join(OUT, "cleaned-ads.csv"), "w", newline="", encoding="utf-8") as f: w = csv.writer(f) w.writerow(["row_id","ad_id","date","product","channel","spend_cad","included_in_totals","exclusion_reason"]) for r in ads: inc = not r["_reason"] w.writerow([r["row_id"], r["ad_id"], r["_date"].isoformat() if r["_date"] else r["date"], r["_prod"] or r["product"], r["_chan"], m(r["_amt"]) if r["_amt"] is not None else r["spend_cad"], "yes" if inc else "no", r["_reason"]]) if not inc: exc("ads.csv", r, r["ad_id"], r["_reason"], "Excluded from ad spend", m(r["_amt"]) if r["_amt"] is not None else "unknown") for r in refunds: if "amount missing" in r["_reason"]: o = paid_orders[r["order_id"].strip()] exc("sales.csv", o, o["order_id"], f"Included paid order has settled refund {r['refund_id']} ({r['row_id']}) with unknown amount; net revenue provisional (refund bounded 0.00-{m(o['_gross'])} by gross)", "Kept in paid orders and gross; flagged only", m(o["_gross"]), "flagged_included_provisional") with open(os.path.join(OUT, "exceptions.csv"), "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=["source_file","row_id","record_id","exception_type","issue","action","row_amount_cad"]) w.writeheader(); w.writerows(exceptions) # ---------- METRICS ---------- res = {} for p in ("Workshop", "Course"): od = [r for r in paid_orders.values() if r["_prod"] == p] g = sum((r["_gross"] for r in od), Decimal(0)) rf = sum((r["_amt"] for r in refunds if not r["_reason"] and r["_prod"] == p), Decimal(0)) ad = sum((r["_amt"] for r in ads if not r["_reason"] and r["_prod"] == p), Decimal(0)) res[p] = dict(n=len(od), g=g, rf=rf, ad=ad) res["Combined"] = {k: res["Workshop"][k] + res["Course"][k] for k in ("n","g","rf","ad")} for v in res.values(): v["net"] = v["g"] - v["rf"]; v["contrib"] = v["net"] - v["ad"] v["per"] = (v["net"] / v["n"]).quantize(Decimal("0.01"), ROUND_HALF_UP) for k, v in res.items(): print(k, v["n"], m(v["g"]), m(v["rf"]), m(v["net"]), m(v["ad"]), m(v["contrib"]), m(v["per"])) print("sales rows", len(sales), "incl", sum(1 for r in sales if not r["_reason"])) print("refund rows", len(refunds), "incl", sum(1 for r in refunds if not r["_reason"])) print("ad rows", len(ads), "incl", sum(1 for r in ads if not r["_reason"])) print("exceptions", len(exceptions)) # Hand-calculated expectations exp = {"Workshop": (5, "1920.00", "240.00", "1680.00", "300.00", "1380.00", "336.00"), "Course": (4, "900.00", "60.00", "840.00", "150.00", "690.00", "210.00"), "Combined": (9, "2820.00", "300.00", "2520.00", "450.00", "2070.00", "280.00")} for k, e in exp.items(): v = res[k] assert (v["n"], m(v["g"]), m(v["rf"]), m(v["net"]), m(v["ad"]), m(v["contrib"]), m(v["per"])) == e, k def tot(rows, key): return sum((r[key] for r in rows if r[key] is not None), Decimal(0)) for rows, key, raw in ((sales, "_gross", "4140.00"), (refunds, "_amt", "960.00"), (ads, "_amt", "630.00")): inc = [r for r in rows if not r["_reason"]]; ex = [r for r in rows if r["_reason"]] assert m(tot(rows, key)) == raw and tot(inc, key) + tot(ex, key) == tot(rows, key) print("reconcile", raw, "=", m(tot(inc, key)), "+", m(tot(ex, key)), "| rows", len(rows), "=", len(inc), "+", len(ex)) assert sum(1 for r in refunds if r["_amt"] is None) == 1 excl_rows = [e for e in exceptions if e["exception_type"] != "flagged_included_provisional"] assert len(excl_rows) == sum(1 for t in (sales, refunds, ads) for r in t if r["_reason"]) print("ASSERTIONS PASSED")