#!/usr/bin/env python3 """Task 1: Northline Workshops October 2026 reconciliation. Applies inputs/policy.md rules to inputs/sales.csv, inputs/refunds.csv, inputs/ads.csv. Writes output/cleaned-*.csv, output/exceptions.csv, output/summary.md. Standard library only. Raw inputs are never modified. """ import csv import os import re from datetime import date, datetime from decimal import Decimal IN = "inputs" OUT = "output" os.makedirs(OUT, exist_ok=True) PERIOD_START = date(2026, 10, 1) PERIOD_END = 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 norm_date(raw): """ISO, DD/MM/YYYY, or named English month (e.g. 'Oct 05 2026').""" 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"): try: return datetime.strptime(s, fmt).date() except ValueError: pass return None def norm_money(raw): s = (raw or "").strip() if not s: return None # unknown, never zero (policy.md) s = re.sub(r"(?i)^cad\s*", "", s) s = s.replace("$", "").replace(",", "").strip() try: return Decimal(s).quantize(Decimal("0.01")) except Exception: return None def money(d): return "" if d is None else f"{d:.2f}" def in_period(d): return d is not None and PERIOD_START <= d <= PERIOD_END def norm_product(raw): return PRODUCTS.get((raw or "").strip().lower()) # None => unmapped, flagged exceptions = [] # dicts: source_file,row_id,record_id,issue,action,detail def flag(src, row, rec, issue, action, detail): exceptions.append(dict(source_file=src, row_id=row, record_id=rec, issue=issue, action=action, detail=detail)) # ---------------- SALES ---------------- sales_raw = read("sales.csv") sales = [] for r in sales_raw: sales.append({ "row_id": r["row_id"], "order_id": r["order_id"].strip(), "date_raw": r["date"], "date": norm_date(r["date"]), "product": norm_product(r["product"]), "status": r["status"].strip().lower(), "gross": norm_money(r["gross_cad"]), "updated_at": r["updated_at"].strip(), }) # collapse exact duplicates (all fields except row_id) def sales_key(r): return (r["order_id"], r["date_raw"], r["product"], r["status"], r["gross"], r["updated_at"]) seen = {} deduped = [] for r in sales: k = sales_key(r) if k in seen: flag("sales.csv", r["row_id"], r["order_id"], "exact_duplicate", "collapsed", f"Identical to {seen[k]} on all fields except row_id; kept {seen[k]}.") else: seen[k] = r["row_id"] deduped.append(r) # conflicting rows, same order_id: keep latest updated_at by_order = {} for r in deduped: cur = by_order.get(r["order_id"]) if cur is None: by_order[r["order_id"]] = r else: keep, drop = (r, cur) if r["updated_at"] > cur["updated_at"] else (cur, r) by_order[r["order_id"]] = keep flag("sales.csv", drop["row_id"], drop["order_id"], "superseded_conflict", "excluded", f"Same order_id as {keep['row_id']}; kept latest updated_at " f"({keep['updated_at']}, gross {money(keep['gross'])}) per policy.md.") sales_clean = sorted(by_order.values(), key=lambda r: r["row_id"]) for r in sales_clean: reasons = [] if r["date"] is None: reasons.append("unparseable_date") elif not in_period(r["date"]): reasons.append("outside_october_2026") if r["status"] != "paid": reasons.append(f"status_{r['status']}_not_revenue") if r["product"] is None: reasons.append("unmapped_product") if r["gross"] is None: reasons.append("missing_or_unparseable_gross") r["included"] = not reasons r["note"] = ";".join(reasons) for reason in reasons: flag("sales.csv", r["row_id"], r["order_id"], reason, "excluded", f"date={r['date_raw']}, status={r['status']}, gross={money(r['gross']) or '(missing)'}; " "policy: only status paid within Oct 1-31 counts as revenue.") paid_orders = {r["order_id"]: r for r in sales_clean if r["included"]} # ---------------- REFUNDS ---------------- refunds_raw = read("refunds.csv") refunds = [] for r in refunds_raw: refunds.append({ "row_id": r["row_id"], "refund_id": r["refund_id"].strip(), "order_id": r["order_id"].strip(), "date_raw": r["settled_date"], "date": norm_date(r["settled_date"]), "status": r["status"].strip().lower(), "amount": norm_money(r["amount_cad"]), }) def refund_key(r): return (r["refund_id"], r["order_id"], r["date_raw"], r["status"], r["amount"]) seen = {} deduped = [] for r in refunds: k = refund_key(r) if k in seen: flag("refunds.csv", r["row_id"], r["refund_id"], "exact_duplicate", "collapsed", f"Identical to {seen[k]} on all fields except row_id; kept {seen[k]}.") else: seen[k] = r["row_id"] deduped.append(r) by_refund = {} for r in deduped: if r["refund_id"] in by_refund: flag("refunds.csv", r["row_id"], r["refund_id"], "conflicting_refund_id", "flagged_unresolved", "Duplicate refund_id with differing fields; no updated_at to resolve. Both kept out pending review.") by_refund[r["refund_id"]]["conflict"] = True r["conflict"] = True by_refund[r["refund_id"] + "_" + r["row_id"]] = r else: by_refund[r["refund_id"]] = r refunds_clean = sorted(by_refund.values(), key=lambda r: r["row_id"]) provisional = False for r in refunds_clean: reasons = [] if r.get("conflict"): reasons.append("conflicting_refund_id_unresolved") if r["status"] != "settled": reasons.append(f"status_{r['status']}_not_deducted") if r["date"] is None: reasons.append("unparseable_settled_date") elif not in_period(r["date"]): reasons.append("settlement_outside_october_2026") order = paid_orders.get(r["order_id"]) if order is None: reasons.append("no_valid_paid_october_order") if r["amount"] is None: reasons.append("missing_amount") if r["status"] == "settled": provisional = True r["included"] = not reasons r["note"] = ";".join(reasons) r["product"] = order["product"] if order else "" for reason in reasons: detail = (f"order_id={r['order_id']}, settled_date={r['date_raw']}, " f"status={r['status']}, amount={money(r['amount']) or '(missing)'}. ") if reason == "no_valid_paid_october_order": detail += "Order not found among included paid October orders; cannot validate or attribute." elif reason == "missing_amount": detail += "Amount unknown (never treated as zero); net revenue is provisional per policy.md." elif reason.startswith("status_"): detail += "Policy: do not subtract pending refunds." elif reason == "settlement_outside_october_2026": detail += "Policy: only refunds settled Oct 1-31 are deducted." flag("refunds.csv", r["row_id"], r["refund_id"], reason, "excluded", detail) # refunds cannot exceed paid gross (per order, applied refunds) applied_by_order = {} for r in refunds_clean: if r["included"]: applied_by_order[r["order_id"]] = applied_by_order.get(r["order_id"], Decimal("0")) + r["amount"] for oid, amt in applied_by_order.items(): if amt > paid_orders[oid]["gross"]: flag("refunds.csv", "", oid, "refunds_exceed_paid_gross", "flagged", f"Applied refunds {money(amt)} exceed paid gross {money(paid_orders[oid]['gross'])}.") # ---------------- ADS ---------------- ads_raw = read("ads.csv") ads = [] for r in ads_raw: ads.append({ "row_id": r["row_id"], "ad_id": r["ad_id"].strip(), "date_raw": r["date"], "date": norm_date(r["date"]), "product": norm_product(r["product"]), "channel": r["channel"].strip().lower(), "spend": norm_money(r["spend_cad"]), }) def ad_key(r): return (r["ad_id"], r["date_raw"], r["product"], r["channel"], r["spend"]) seen = {} deduped = [] for r in ads: k = ad_key(r) if k in seen: flag("ads.csv", r["row_id"], r["ad_id"], "exact_duplicate", "collapsed", f"Identical to {seen[k]} on all fields except row_id; kept {seen[k]}.") else: seen[k] = r["row_id"] deduped.append(r) by_ad = {} for r in deduped: if r["ad_id"] in by_ad: flag("ads.csv", r["row_id"], r["ad_id"], "conflicting_ad_id", "flagged_unresolved", "Duplicate ad_id with differing fields; no updated_at to resolve.") r["conflict"] = True by_ad[r["ad_id"] + "_" + r["row_id"]] = r else: by_ad[r["ad_id"]] = r ads_clean = sorted(by_ad.values(), key=lambda r: r["row_id"]) for r in ads_clean: reasons = [] if r.get("conflict"): reasons.append("conflicting_ad_id_unresolved") if r["date"] is None: reasons.append("unparseable_date") elif not in_period(r["date"]): reasons.append("outside_october_2026") if r["product"] is None: reasons.append("unmapped_product") if r["spend"] is None: reasons.append("missing_or_unparseable_spend") r["included"] = not reasons r["note"] = ";".join(reasons) for reason in reasons: flag("ads.csv", r["row_id"], r["ad_id"], reason, "excluded", f"date={r['date_raw']}, product={r['product']}, spend={money(r['spend']) or '(missing)'}.") # Cross-reference notes on kept rows so collapsed/superseded source row IDs # remain traceable from the cleaned files themselves (not only exceptions.csv). kept_notes = {} for e in exceptions: if e["issue"] == "exact_duplicate": m = re.search(r"kept (\w+)\.", e["detail"]) if m: kept_notes.setdefault((e["source_file"], m.group(1)), []).append( f"collapsed_exact_duplicate:{e['row_id']}") elif e["issue"] == "superseded_conflict": m = re.search(r"Same order_id as (\w+)", e["detail"]) if m: kept_notes.setdefault((e["source_file"], m.group(1)), []).append( f"supersedes:{e['row_id']}_latest_updated_at") for rows, fname in ((sales_clean, "sales.csv"), (refunds_clean, "refunds.csv"), (ads_clean, "ads.csv")): for r in rows: extra = kept_notes.get((fname, r["row_id"]), []) r["note"] = ";".join(([r["note"]] if r["note"] else []) + extra) # ---------------- WRITE CLEANED FILES ---------------- def iso(d): return d.isoformat() if d else "" 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", "note"]) for r in sales_clean: w.writerow([r["row_id"], r["order_id"], iso(r["date"]) or r["date_raw"], r["product"] or "", r["status"], money(r["gross"]), r["updated_at"], "yes" if r["included"] else "no", r["note"]]) 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", "settled_date", "status", "amount_cad", "product", "included", "note"]) for r in refunds_clean: w.writerow([r["row_id"], r["refund_id"], r["order_id"], iso(r["date"]) or r["date_raw"], r["status"], money(r["amount"]), r["product"], "yes" if r["included"] else "no", r["note"]]) 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", "note"]) for r in ads_clean: w.writerow([r["row_id"], r["ad_id"], iso(r["date"]) or r["date_raw"], r["product"] or "", r["channel"], money(r["spend"]), "yes" if r["included"] else "no", r["note"]]) 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", "issue", "action", "detail"]) w.writeheader() for e in exceptions: w.writerow(e) # ---------------- TOTALS ---------------- def totals(product=None): s = [r for r in sales_clean if r["included"] and (product is None or r["product"] == product)] rf = [r for r in refunds_clean if r["included"] and (product is None or r["product"] == product)] ad = [r for r in ads_clean if r["included"] and (product is None or r["product"] == product)] n = len(s) gross = sum((r["gross"] for r in s), Decimal("0")) ref = sum((r["amount"] for r in rf), Decimal("0")) spend = sum((r["spend"] for r in ad), Decimal("0")) net = gross - ref contrib = net - spend per = (net / n).quantize(Decimal("0.01")) if n else None return dict(orders=n, gross=gross, refunds=ref, net=net, spend=spend, contrib=contrib, per_order=per) T = {p: totals(p) for p in ("Workshop", "Course")} T["Combined"] = totals(None) def fmt_row(label, t): return (f"| {label} | {t['orders']} | {money(t['gross'])} | {money(t['refunds'])} | " f"{money(t['net'])} | {money(t['spend'])} | {money(t['contrib'])} | " f"{money(t['per_order'])} |") # reconciliation counts def recon(rows_raw, clean, src): included = sum(1 for r in clean if r["included"]) collapsed = sum(1 for e in exceptions if e["source_file"] == src and e["issue"] == "exact_duplicate") superseded = sum(1 for e in exceptions if e["source_file"] == src and e["issue"] == "superseded_conflict") excluded = sum(1 for r in clean if not r["included"]) assert len(rows_raw) == included + collapsed + superseded + excluded, src return len(rows_raw), included, collapsed, superseded, excluded rec_sales = recon(sales_raw, sales_clean, "sales.csv") rec_ref = recon(refunds_raw, refunds_clean, "refunds.csv") rec_ads = recon(ads_raw, ads_clean, "ads.csv") inc_sales_ids = ", ".join(r["row_id"] for r in sales_clean if r["included"]) inc_ref_ids = ", ".join(r["row_id"] for r in refunds_clean if r["included"]) inc_ad_ids = ", ".join(r["row_id"] for r in ads_clean if r["included"]) summary = f"""# Northline Workshops — October 2026 results (CAD) Reporting period: 2026-10-01 to 2026-10-31 inclusive. All amounts CAD, tax excluded. Rules applied from `inputs/policy.md` (accounting note, October 2026). Sources: `inputs/sales.csv`, `inputs/refunds.csv`, `inputs/ads.csv`. Generated by `reconcile.py` (standard-library Python); every figure below is computed from the cleaned files, which preserve source row IDs. **Status: PROVISIONAL.** Refund RF05 (row R06, order N1011) is settled in October but its amount is missing. It is excluded from the calculable total and never treated as zero, so Course and combined net revenue, contribution and per-order figures below are upper bounds pending that amount (Workshop figures are unaffected by RF05) (`inputs/policy.md`: "Missing refund amounts mean the resulting net revenue is provisional"). Refund RF04 (row R05) also references order N9999, which does not exist in the sales file — flagged, not guessed. ## Results | Metric basis | Paid orders | Gross sales | Applied refunds | Net revenue | Ad spend | Contribution after ads | Net revenue / paid order | |---|---|---|---|---|---|---|---| {fmt_row('Workshop', T['Workshop'])} {fmt_row('Course', T['Course'])} {fmt_row('Combined', T['Combined'])} Definitions (per `inputs/policy.md`): revenue = paid order gross; applied refunds = settled refunds with October settlement dates against valid paid October orders; contribution after advertising = net revenue − ad spend. **This is not net profit**: no COGS, payment fees, salaries or overhead were supplied. Included records: - Sales (paid, October): {inc_sales_ids}. - Refunds (settled, October, valid paid order, amount present): {inc_ref_ids} (RF01 → N1003 Workshop 240.00; RF02 → N1006 Course 60.00). - Ads (October, mapped product): {inc_ad_ids}. Checks performed on applied refunds: RF01 240.00 ≤ N1003 paid gross 480.00; RF02 60.00 ≤ N1006 paid gross 240.00. No refund exceeds paid gross. RF05's ceiling cannot be checked because its amount is missing (N1011 gross is 360.00). ## Reconciliation of source rows | File | Source rows | Included | Exact duplicates collapsed | Superseded (conflict) | Excluded after dedup | |---|---|---|---|---|---| | sales.csv | {rec_sales[0]} | {rec_sales[1]} | {rec_sales[2]} | {rec_sales[3]} | {rec_sales[4]} | | refunds.csv | {rec_ref[0]} | {rec_ref[1]} | {rec_ref[2]} | {rec_ref[3]} | {rec_ref[4]} | | ads.csv | {rec_ads[0]} | {rec_ads[1]} | {rec_ads[2]} | {rec_ads[3]} | {rec_ads[4]} | Every source row is accounted for: included + collapsed duplicate + superseded + excluded = source rows (asserted in `reconcile.py`). Row-level reasons are in `output/exceptions.csv`. Key items: - **S04** duplicates S03 (order N1003) exactly — collapsed, counted once (480.00). - **S09 vs S10** (order N1008): conflicting gross 120.00 vs 180.00; kept S10 (updated_at 2026-10-16T09:00:00, latest) per policy; S09 excluded. - **S05** pending and **S08** cancelled — not revenue per policy. - **S11** (2026-09-30) and **S15** (2026-11-01) — outside October. - **R03** duplicates R02 (refund RF02) exactly — collapsed, deducted once (60.00). - **R04** pending — not deducted per policy. - **R05** settled 120.00 but order N9999 is not a valid paid order — flagged and excluded. - **R06** settled, amount missing — excluded from calculable total; makes results provisional. - **R07** settled 2026-11-02 — November settlement, outside the October window. - **A04** duplicates A03 (ad AD03) exactly — collapsed, spent counted once (100.00). - **A07** dated 2026-11-01 — outside October. ## Normalization applied - Dates: slash dates read as DD/MM/YYYY (S02 03/10/2026 → 2026-10-03; S07 11/10/2026 → 2026-10-11; R02 14/10/2026 → 2026-10-14); named month S03/S04 "Oct 05 2026" → 2026-10-05; ISO kept. All valid dates written YYYY-MM-DD. - Money: "CAD 240.00", "$120.00", "$50.00" parsed to plain two-decimal values. The missing amount in R06 is preserved as empty, not zero. - Product/channel: trimmed and case-folded (" Workshop " → Workshop, "course" → Course, " Search "/"SOCIAL" → search/social). No unmapped products occurred. ## Recommendation Workshop produced {money(T['Workshop']['contrib'])} of the {money(T['Combined']['contrib'])} combined contribution ({T['Workshop']['contrib'] / T['Combined']['contrib'] * 100:.1f}%) on {money(T['Workshop']['spend'])} of ad spend, and its net revenue per paid order ({money(T['Workshop']['per_order'])}) is well above Course ({money(T['Course']['per_order'])}). Notably, contribution per ad dollar is identical for both lines (Workshop {T['Workshop']['contrib'] / T['Workshop']['spend']:.2f}, Course {T['Course']['contrib'] / T['Course']['spend']:.2f}), so October data alone does not justify shifting budget from Course to Workshop on efficiency grounds. Practical steps: 1. **Resolve the two refund exceptions before booking the month**: obtain RF05's amount (if the refund cannot exceed N1011's 360.00 paid gross, Course net revenue is bounded {money(T['Course']['net'] - paid_orders['N1011']['gross'])}–{money(T['Course']['net'])}, combined net revenue {money(T['Combined']['net'] - paid_orders['N1011']['gross'])}–{money(T['Combined']['net'])}, and combined contribution {money(T['Combined']['contrib'] - paid_orders['N1011']['gross'])}–{money(T['Combined']['contrib'])}) and investigate RF04's orphan order N9999 — it may indicate a missing sales record. 2. **Prioritize Workshop capacity/fill rate**: it carries most contribution and the higher per-order value; modest spend increases there are reasonable to test, but with equal ad efficiency, run a small controlled test rather than a large reallocation. 3. **Do not treat {money(T['Combined']['contrib'])} as profit.** Cost of delivery, payment fees, salaries and overhead are absent from the inputs; true profitability could be materially lower, especially for delivery-heavy workshops. Gather those costs before pricing or budget decisions. ## Assumptions and limitations - "Exact duplicate" = identical on all fields except row_id (policy allows collapsing). - Refund RF05 (R06) is attributed to Course via order N1011 for the provisional-range note only; it is excluded from the calculable totals and its unknown amount is not treated as zero. - Ads are attributed to the product named in each row (per policy); no allocation across products was needed. - No refunds exceeded paid gross among calculable records; the missing-amount refund could not be ceiling-checked. - Figures are provisional solely due to R06's missing amount; all other exclusions are policy-driven and final. Estimated human finish time (not a measured result): ~20–30 minutes to obtain the RF05 amount, investigate order N9999, and re-run `reconcile.py`. """ with open(os.path.join(OUT, "summary.md"), "w", encoding="utf-8") as f: f.write(summary) print("Totals:") for k, t in T.items(): print(k, {kk: (money(vv) if isinstance(vv, Decimal) else vv) for kk, vv in t.items()}) print("Recon (raw, included, dup-collapsed, superseded, excluded):", rec_sales, rec_ref, rec_ads) print("Provisional:", provisional, "| exceptions:", len(exceptions))