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

Reporting period: 2026-10-01 to 2026-10-31 inclusive, per `inputs/policy.md` ("Northline
Workshops accounting note — October 2026"). Sources: `inputs/sales.csv` (rows S01–S15),
`inputs/refunds.csv` (R01–R07), `inputs/ads.csv` (A01–A07). All figures are CAD.

**Status: net revenue and contribution are PROVISIONAL.** Settled refund RF05 (row R06,
order N1011) has a missing amount. Per policy, empty means unknown, never zero, so it is
excluded from the calculable total; the true net revenue is lower than shown by that
unknown amount (bounded above by the order's paid gross of CAD 360.00, since refunds
cannot exceed paid gross).

## Results

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

Definitions per policy: revenue = paid order gross less settled refunds whose settlement
date falls in October; contribution after advertising = net revenue − ad spend. Pending
refunds are not subtracted. This is **not net profit** (see Recommendation).

### Included records (see cleaned CSVs for full detail)

- **Paid orders (9):** Workshop — N1001 (S01, 240.00), N1003 (S03+S04 collapsed, 480.00),
  N1005 (S06, 240.00), N1010 (S12, 720.00), N1012 (S14, 240.00).
  Course — N1002 (S02, 120.00), N1006 (S07, 240.00), N1008 (S10, 180.00),
  N1011 (S13, 360.00).
- **Applied refunds (2):** RF01 (R01, N1003, settled 2026-10-09, 240.00, Workshop);
  RF02 (R02+R03 collapsed, N1006, settled 14/10/2026 → 2026-10-14, 60.00, Course).
  Both are ≤ the related order's paid gross (240 ≤ 480; 60 ≤ 240), so no over-refund flags.
  Refund product is inherited from the paid order (policy: every refund relates to an
  October order).
- **October ads (5):** AD01 (A01, Workshop, search, 150.00), AD02 (A02, Course, social,
  90.00), AD03 (A03+A04 collapsed, Workshop, social, 100.00), AD04 (A05, Course, search,
  60.00), AD05 (A06, Workshop, search, 50.00). Spend attributed to the product named in
  each row, per policy.

## Record reconciliation

Every raw row ID appears exactly once across cleaned provenance (`row_ids`) or an
exclusion in `exceptions.csv`; collapsed duplicates also get an explanatory exception row.
Verified programmatically (assertion in `output/reconcile.py`, run locally).

| File | Raw rows | Kept records | Duplicate rows collapsed | Superseded (conflict) | Other exclusions |
|---|---:|---:|---:|---:|---:|
| sales.csv | 15 | 9 (covering 10 row IDs) | 1 (S04) | 1 (S09) | 4 (S05, S08, S11, S15) |
| refunds.csv | 7 | 2 (covering 3 row IDs) | 1 (R03) | 0 | 4 (R04, R05, R06, R07) |
| ads.csv | 7 | 5 (covering 6 row IDs) | 1 (A04) | 0 | 1 (A07) |

### Exclusions and flags (detail in `output/exceptions.csv`)

**Sales**
- S04 (N1003): exact duplicate of S03 — collapsed, counted once.
- S09 vs S10 (N1008): conflicting gross (120 vs 180) with different `updated_at`; kept
  S10 (2026-10-16T09:00:00 is latest), per policy "retain the latest updated_at record".
- S05 (N1004): status `pending` — not revenue.
- S08 (N1007): status `cancelled` — not revenue.
- S11 (N1009): paid but dated 2026-09-30 — outside the period.
- S15 (N1013): paid but dated 2026-11-01 — outside the period.

**Refunds**
- R03 (RF02): exact duplicate of R02 — collapsed.
- R04 (RF03, N1005, 240.00): `pending` — policy says do not subtract pending refunds.
  If it later settles, Workshop net revenue would fall by 240.00.
- R05 (RF04, order N9999, 120.00, settled 2026-10-19): no valid paid order N9999 exists
  in the supplied sales data — flagged and excluded; needs follow-up with the source
  system (possible missing order record or typo).
- R06 (RF05, N1011, settled 2026-10-25): amount missing — flagged, excluded from the
  calculable total; makes net revenue **provisional**. Missing value preserved as empty
  in the exception detail, not guessed.
- R07 (RF06, N1012, 240.00): settled 2026-11-02 — outside October; will reduce November
  results, not October's.

**Ads**
- A04 (AD03): exact duplicate of A03 — collapsed.
- A07 (AD06, Course, 80.00): dated 2026-11-01 — outside October.

## Method and normalization

- Dates normalized to YYYY-MM-DD. Slash dates read as DD/MM/YYYY per policy
  (S02 `03/10/2026` → 2026-10-03; S07 `11/10/2026` → 2026-10-11; R02 `14/10/2026` →
  2026-10-14). Named-month date S03/S04 `Oct 05 2026` → 2026-10-05. ISO dates kept as-is.
- Money normalized to two decimals; currency strings stripped (`CAD 240.00` in S01,
  `$120.00` in S02, `$50.00` in A06). No unparseable money values occurred; the only
  missing money value (R06 amount) is preserved as unknown.
- Product and channel trimmed and case-normalized (` Workshop ` → Workshop, `course` →
  Course, ` Search `/`SOCIAL` → search/social). All products mapped to Workshop or
  Course; no unmapped products occurred.
- Deduplication: exact duplicates collapsed by unique ID (order/refund/ad). For ads,
  duplicate comparison is done after normalization, per the policy's case/space rule.
- `row_ids` in cleaned files preserves all source row IDs folded into each record
  (semicolon-separated), so provenance back to the raw CSVs is exact.

### Assumptions (inputs did not resolve these; none affected the totals)

1. Refund product attribution: taken from the linked paid order's product, since
   refunds.csv has no product column and policy ties each refund to an October order.
2. No conflicting refund/ad IDs occurred, so no tiebreaker beyond exact-duplicate
   collapse was needed; if one had, it would have been flagged rather than guessed.
3. Channel labels are lowercased for consistency (policy requires case-insensitivity;
   it does not mandate a display case).

## Recommendation

1. **Resolve the provisional items first.** Chase the missing amount for settled refund
   RF05 (R06) and the orphan refund RF04 referencing order N9999 (R05) with the payments
   system. Combined net revenue could fall by up to 360.00 (RF05 cap) plus 120.00
   (RF04, if a valid October paid order is found), i.e., from 2,520.00 to as low as
   2,040.00 before any other corrections. Watch pending RF03 (240.00 on Workshop) and
   the November-settled RF06 (240.00) in the next period.
2. **Lean toward Workshop for marginal ad budget, cautiously.** On October data,
   Workshop returned 1,680.00 net revenue on 300.00 ad spend (5.6× net-revenue-to-spend;
   contribution 1,380.00) vs Course 840.00 on 150.00 (also 5.6×; contribution 690.00).
   Ratios are equal, but Workshop's net revenue per paid order is higher (336.00 vs
   210.00) and it carries 66% of combined contribution. With only 9 orders and no
   per-channel conversion data, treat this as a small nudge (e.g., a controlled test
   shifting a modest slice of Course search budget to Workshop), not a proven causal
   result — the policy attributes spend to products, not to the orders it generated.
3. **Do not read 2,070.00 as profit.** Contribution after advertising here is only net
   revenue minus ad spend. Policy states no COGS (materials, venue, instructor time for
   workshops), payment processing fees, salaries or overhead are supplied, and taxes are
   excluded. Actual business profit is materially lower and cannot be computed from
   these inputs. Before pricing or budget decisions, add those cost lines.

## Limitations

- Net revenue, contribution, and per-order figures are provisional (RF05 amount unknown).
- RF04 cannot be validated (order N9999 absent); it is excluded, not assumed invalid
  business-wise.
- Ad effectiveness cannot be causally measured from these files (no click/conversion
  or attribution data beyond product naming).
- Only Task 1 inputs were supplied; no site/ directory exists in this workspace.

## Checks actually performed

- Ran `output/reconcile.py` (Python 3, standard library only) against the raw CSVs;
  it produced all four CSV deliverables and printed the totals above.
- Assertion check passed: every raw row ID is accounted for exactly once across cleaned
  provenance and exclusions, with duplicate IDs additionally explained in exceptions.csv
  (15 sales + 7 refunds + 7 ads = 29 raw rows fully reconciled).
- Manually cross-checked totals against hand calculation (9 paid orders; 2,820.00 gross;
  300.00 refunds; 450.00 ads) — matched.
- Verified refund-≤-gross condition for both applied refunds.
- Inspected all four output CSVs line by line.

Estimated human time to finish (estimate, not measured): ~20–30 minutes to review the
exception dispositions, confirm RF05/RF04 with the source system, and sign off.
