File: stage-2-11-stage-2-review.md
Stage 2 Review: Kewpie's results (approved)
Where we are: Stage 3 of 5 (Data Visualisation). Stage 1 approved 20-Oct-26, Stage 2 approved 21-Oct-26
ClearCompare (test case) · 20-Oct-26 · Sample data, cut-off 30-Sep-26. The full numbers are in the "KPI Calculation Pack" tab.
Stage 2 approved 21-Oct-26: your four decisions
1. This quarter's net revenue is shown as provisional
| Net revenue per lead, this quarter | |
|---|---|
| Before | "$52.42" |
| After | "$52.42 provisional (about $51.59 once clawbacks land)". Final on 29-Dec-26 |
2. The lead-mix target keeps 30%, with a later date
| Lead mix: target | |
|---|---|
| Before | Energy at or below 30% of leads by 31-Dec-26 · needs a fall of 4.3 points a month |
| After | Energy at or below 30% of leads by 31-Mar-27 · needs a fall of 2.2 points a month (energy is 43% now, rising 5.3 points a month) |
3. Cost per acquisition gets a counter-metric
| Cost per acquisition: counter-metric | |
|---|---|
| Before | Proposed only: "net revenue per marketing dollar" |
| After | Calculated: $5.71 this quarter, down from $6.37 across all channels. Paid search $3.49, down from $3.69. Paid search costs less per sale but earns less per dollar spent |
4. Complaints are judged on a rolling 12 months
| Complaints per 1,000 sales | |
|---|---|
| Before | Monthly rate. This quarter 4.5 (5 complaints) |
| After | 3.75 over October 2025 to September 2026 (15 complaints from 4,000 sales), against a target of 3.64 · 🟠 Amber |
Where these were applied:
- KPI Calculation Pack: summary and KPI details. The challenge notes are now marked "Accepted 21-Oct-26"
- Business Requirements: the lead-mix and complaints targets
- Business Requirements with Data Mapping: the lead-mix target, the complaints measure and the net revenue filters
- The spec: Stage 2 recorded as approved; 17 of 17 checks pass
Next: Stage 3, Data Visualisation (Viktor). Viktor isn't built yet; that's Phase 2 of the build.
↑ Back to top1. Stage 1 closed: your final edits
| Business Requirements, open questions | |
|---|---|
| Before | Two questions about timing: "What are the target dates for each objective?" and "When do we need to reach our KPI targets? Do targets need to be met within the next 6, 9 or 12 months?" |
| After | One question: "When do we need to reach our KPI targets? Do targets need to be met within the next 6, 9 or 12 months?" |
| Targets for speed to contact, conversion, cost per acquisition and the clawback rate | |
|---|---|
| Before | One date each (30-Jun-27, or 31-Mar-27 for speed to contact) |
| After | The same timing options as net revenue per lead: 3 months (31-Jan-27) · 6 months (30-Apr-27) · 9 months (31-Jul-27) · 1 quarter (31-Mar-27) · 2 quarters (30-Jun-27) |
Stage 1 is recorded as approved by Adam (acting subject matter expert), 20-Oct-26, in the Business Requirements, the change log and the spec.
↑ Back to top2. What the numbers say
| KPI | This quarter | Target | Status |
|---|---|---|---|
| Net revenue per lead | $52.42 | $57.41 by 30-Jun-27 | 🔴 Red: 8.7% below and still falling ($1.88 a month) |
| Lead-to-sale conversion | Health 9.5% | Health 11% | 🔴 Red: health is down from 11.0% last quarter |
| Cost per acquisition (paid search) | $93.04 | $106.70 | 🟢 Green, but see challenge 3 below |
| Speed to contact | 11 minutes | 10 minutes | 🟠 Amber: unchanged for two years. Social leads wait 16 minutes |
| 90-day clawback rate | 7.9% | 7.8% | 🟢 Green on complete quarters |
The answer to the CCO's question ("Marketing blames sales, sales blames lead quality, finance says it's clawbacks. I need to know which"):
| Cause | Effect on net revenue, last quarter to this quarter | Verdict |
|---|---|---|
| Lead mix: more energy leads, which earn about $95 a sale | −$29,658 | Main cause. Marketing's lead mix, not sales |
| Conversion, mostly health | −$12,186 | Second cause. A real sales problem in health |
| Clawbacks and revenue per sale | +$3,419 | Not the cause yet. Clawbacks are flat; this quarter's haven't fully landed |
| More leads | +$12,172 | Partly offsets the fall |
| Total | −$26,253 (−8.7% per lead) |
3. What Kewpie changed
Kewpie tested all 8 of Gary's draft measures on the data. 7 were confirmed and 1 was corrected: lead-to-sale conversion, which also changes health conversion.
| Lead-to-sale conversion: how it's measured | |
|---|---|
| Before | Leads that became a matched sale ÷ leads created. Sales with no lead (6.1% of all sales) were left out. Health this quarter: 9.2% |
| After | All sales in a product area ÷ leads created in that area, in the same month. Health this quarter: 9.5%, close to the CCO's quoted 9.4% |
Updated in Business Requirements with Data Mapping, marked "Corrected by Kewpie, 20-Oct-26".
↑ Back to top4. Five challenges (decided 21-Oct-26)
Kewpie doesn't change the KPI list or targets. These need a decision from you, as acting subject matter expert.
| Challenge | Finding | Kewpie suggests |
|---|---|---|
| 1. This quarter's clawbacks haven't landed yet | About $6,351 of clawbacks are still expected. Net revenue per lead is likely $51.59, not $52.42 | Show this quarter as "provisional" with the adjusted figure beside it |
| 2. The lead-mix target is a big reversal | Energy is 43% of leads and rising 5.3 points a month. Hitting 30% by 31-Dec-26 needs a fall of 4.3 points a month | Confirm the 30% with the Head of Marketing, or move the date to 31-Mar-27 |
| 3. Cost per acquisition looks better than it is | Paid search beats its target only because energy sales are cheap to win. Each one earns about $95, against about $600 for health | Add "net revenue per marketing dollar" as its counter-metric |
| 4. Complaint numbers are too small for a monthly rate | 32 complaints in 24 months; the quarterly rate swings from 0.0 to 7.6 | Judge it on a rolling 12 months |
| 5. The 9% headline | The measured fall is 8.7% | No change needed |
5. A fix made during this run
The first run showed paid search costing about $4,650 per sale. That was a bug in the sample data generator, not in Kewpie's calculation: marketing spend was scaled to full size while leads and sales were at 1-in-50 sample size. It's fixed, and the data was regenerated with the same seed. Every number on this page comes from the corrected data.
↑ Back to top6. Added 21-Oct-26: a description of the dataset
Where: KPI Calculation Pack, new section "2. The dataset" (open the "KPI Calculation Pack" tab)
| KPI Calculation Pack | |
|---|---|
| Before | Row counts and data checks only, at the end ("Data quality log") |
| After | A new section near the top covering: what each of the 5 tables holds, how they link, 6 things that stand out, what isn't in the data, and a column-by-column summary of every table (count, missing values, type, then mean, spread and quartiles for numbers, earliest and latest for dates, and the most common value for text) |
How the summary was made: pandas isn't installed on your PC, so a small tool (my-skills/kpi-creator/tools/describe_data.py) produces the same figures as pandas describe() using standard Python. It works on any folder of CSV files, so every future test case gets the same description.
7. Checks
- Rule checks: 17 of 17 pass at Stage 2, including the new ones: every draft measure confirmed or corrected, every KPI has a starting point and pace needed, every Top 5 KPI has a counter-metric
- Data checks: every key field is 100% populated. The known gaps are confirmed: 6.1% of sales unmatched, and Manila outcomes blank before March 2025