# Northline Workshops — October 2026 results (CAD, tax excluded) — PROVISIONAL

Basis: inputs/policy.md. Figures were produced by `output/reconcile.py` (Python standard library only) from inputs/sales.csv, inputs/refunds.csv and inputs/ads.csv. `output/verify.py` then recomputed them independently from the cleaned CSVs. The row-level details are in cleaned-sales.csv, cleaned-refunds.csv, cleaned-ads.csv and exceptions.csv.

**Why these results are provisional:** refund RF05 (refunds.csv row R06, order N1011, Course) is marked settled on 2026-10-25, but its amount is blank. Policy says a missing value is unknown, not zero. The refund is therefore excluded from the calculable total, and net revenue is provisional (policy.md).

## Results (calculable totals)

| Metric | Workshop | Course | Combined |
|---|---:|---:|---:|
| Paid order count | 5 | 4 | 9 |
| Gross sales | 1,920.00 | 900.00 | 2,820.00 |
| Applied refunds (settled in October) | 240.00 | 60.00 | 300.00 |
| Net revenue (provisional) | 1,680.00 | 840.00 | 2,520.00 |
| Advertising spend | 300.00 | 150.00 | 450.00 |
| Contribution after advertising (provisional) | 1,380.00 | 690.00 | 2,070.00 |
| Net revenue per paid order (provisional) | 336.00 | 210.00 | 280.00 |

Calculations:
- **Net revenue** = gross sales − applied refunds.
- **Contribution** = net revenue − ad spend.
- **Per order** = net revenue ÷ paid order count. For example, 2,520.00 ÷ 9 = 280.00.

**Range for the missing RF05 amount (illustration, not an estimate):** the refund applies to order N1011, whose gross is 360.00 (sales.csv row S13). Policy says refunds cannot exceed paid gross, so RF05 should be between 0 and 360.00. That gives these ranges:
- Course net revenue: 480.00–840.00
- Course contribution: 330.00–690.00
- Course net revenue per order: 120.00–210.00
- Combined net revenue: 2,160.00–2,520.00
- Combined contribution: 1,710.00–2,070.00
- Combined net revenue per order: 240.00–280.00

## Included records
- **Sales (9 paid October orders):**
  - Workshop: N1001 (S01) 240, N1003 (S03) 480, N1005 (S06) 240, N1010 (S12) 720, N1012 (S14) 240
  - Course: N1002 (S02) 120, N1006 (S07) 240, N1008 (S10) 180, N1011 (S13) 360
- **Refunds applied:**
  - RF01 (R01): N1003, Workshop, 240.00
  - RF02 (R02): N1006, Course, 60.00
- **Ads included:**
  - Workshop: A01 150, A03 100, A06 50
  - Course: A02 90, A05 60

## Reconciliation of included and excluded rows

| Source | Raw rows | Included | Excluded/flagged | Check |
|---|---:|---:|---:|---|
| sales.csv | 15 | 9 | 6 (S04, S05, S08, S09, S11, S15) | 9 + 6 = 15 ✔ |
| refunds.csv | 7 | 2 | 5 (R03, R04, R05, R06, R07) | 2 + 5 = 7 ✔ |
| ads.csv | 7 | 5 | 2 (A04, A07) | 5 + 2 = 7 ✔ |

### Excluded and flagged rows (amounts in CAD; full reasons in exceptions.csv)
- **Sales:**
  - S04: exact duplicate of S03 (N1003, 480)
  - S05: pending (120)
  - S08: cancelled (240)
  - S09: superseded by S10 for N1008, which has a later updated_at (120 replaced by 180)
  - S11: dated 2026-09-30, outside the period (240)
  - S15: dated 2026-11-01, outside the period (120)
- **Refunds:**
  - R03: exact duplicate of RF02 (60)
  - R04: pending (240); policy says not to subtract pending refunds
  - R05: order N9999 is not a valid paid order, so it is flagged (120)
  - R06: amount missing, so it is flagged and makes the results provisional
  - R07: settled 2026-11-02, outside the period (240)
- **Ads:**
  - A04: exact duplicate of AD03 (100)
  - A07: dated 2026-11-01, outside the period (80)

Only two of the 13 exception rows need follow-up: R05/RF04 and R06/RF05. In exceptions.csv they have `category = flagged_unresolved` and `needs_follow_up = yes`. The other 11 rows are routine policy exclusions (`excluded_by_policy`).

## How to read the output files
- **Cleaned files:** each cleaned CSV keeps every source row and its original `row_id`.
  - Rows counted in the totals are marked `included = yes` (sales and ads) or `applied = yes` (refunds).
  - The `note` column says why any other row was left out.
  - To get the counted records only, filter on that column.
- **Dates and money:** valid dates are in YYYY-MM-DD format and money has two decimal places. Unresolved values are left as they were (RF05's amount is still blank).
- **exceptions.csv `raw_value`:** this column shows the amount exactly as it appears in the source file.

### Normalization applied
- **Dates:**
  - Slash dates were read as DD/MM/YYYY (03/10/2026 became 2026-10-03; 11/10/2026 became 2026-10-11; 14/10/2026 became 2026-10-14).
  - "Oct 05 2026" became 2026-10-05.
- **Money:** "CAD", "$" and spaces were removed (for example, "CAD 240.00" became 240.00 and "$50.00" became 50.00), and all amounts were set to 2 decimal places.
- **Product and channel names:** surrounding spaces were trimmed and letter case was standardized (" Workshop " became Workshop; "course" became Course; " Search " became Search; "social"/"SOCIAL" became Social).
- No products were left unmapped, and no applied refund exceeded its order's gross.

## Recommendation
1. **Before closing October, get the RF05 amount** for order N1011 from the payment processor. Also confirm RF04 (order N9999, 120.00): it may be a mis-keyed order ID. Do not treat either as zero, and do not assign RF04 to a product, until it is confirmed.
2. **Commercially, both products earn back their advertising at the calculable level.** Workshop gave 1,380.00 contribution on 300.00 of ads. Course gave 690.00 on 150.00, or 330.00 if RF05 turns out to be a full refund. Workshop's higher net revenue per order (336.00 vs 210.00) comes largely from one 720.00 order (N1010). With only one month and nine orders, this does not justify reallocating budget yet. Review the per-product results again after November.
3. **Contribution after advertising is not profit.** The 2,070.00 is only net revenue minus ad spend. It excludes COGS, venue or delivery costs, payment fees, salaries, overhead and tax. None of these were supplied (policy.md), so do not report this figure as net profit.

## Assumptions and limitations
- **Refund product:** each refund is assigned to the product of the order it refunds.
- **Refund exceeding gross:** this check compares each refund with the retained gross of its own order. RF01 is 240 against a 480 order, and RF02 is 60 against a 240 order.
- **Refund on an unknown order:** RF04 references N9999, which does not appear in sales.csv, so it is flagged and excluded. Policy says every refund here relates to an October order, so this is likely a data error rather than a refund on an earlier order.
- **N1012:** the order is counted in October (dated 2026-10-31), but its refund RF06 settled 2026-11-02 and belongs in November reporting.
- **Order date:** inclusion uses the sales `date` field, not `updated_at`.
- **Limitation:** no external records were available, so the missing RF05 amount and the unknown order N9999 could not be resolved.

## Verification performed
- `reconcile.py` checks that included + excluded rows equal the raw rows for each file, and that combined contribution = gross − refunds − ads. These checks passed.
- `verify.py` recomputes every table figure from the cleaned CSVs alone, and all figures matched. It also checks that:
  - included order IDs are unique, and so are applied refund IDs and included ad IDs;
  - all 13 non-included rows appear in exceptions.csv;
  - each applied refund is no more than its order's gross.

  All of these checks passed.
