File: stage-2-09-kpi-calculation-pack.md
ClearCompare: KPI Calculation Pack
Where we are: Stage 3 of 5 (Data Visualisation). Stage 1 approved 20-Oct-26, Stage 2 approved 21-Oct-26
Stage 1
Business Requirements
Gary
Approved 20-Oct-26
Stage 2
KPI Development
Kewpie
Approved 21-Oct-26
Stage 3
Data Visualisation
Viktor
Standard dashboard and options
Stage 4
Dashboard Creator
Jennifer
Standards checked, first draft
Stage 5
Stakeholder Review and Handover
CCO and Adam
Walk-through, sign-off, go-live
Sample data. Every number here comes from seeded sample data (synthetic), with a data cut-off of 30-Sep-26. "This quarter" means July to September 2026, "last quarter" April to June 2026, and "the July 2025 to June 2026 average" is the last full financial year. Calculations:03 - Working Files/kewpie_calculations.py; results:kpi_results.json.
1. Summary: the Top 5 executive KPIs
| KPI | This quarter | Target | Status | Pace needed each month | Current trend each month |
|---|---|---|---|---|---|
| Net revenue per lead | $52.42 provisional (about $51.59 once clawbacks land) | $57.41 (the April to June 2026 level) by 30-Jun-27 | 🔴 Red: 8.7% below | +$0.56 | −$1.88 (falling) |
| Lead-to-sale conversion (worst product area: health) | Health 9.5% | Health 11%; others hold their average | 🔴 Red (health) | Health +0.17 points | Health −0.76 points (falling) |
| Cost per acquisition (paid search) | $93.04 | $106.70 (10% below the July 2025 to June 2026 average of $118.56) | 🟢 Green: already below target | None needed | Falling (good) |
| Speed to contact (median) | 11 minutes | 10 minutes or less by 31-Mar-27 | 🟠 Amber | −0.17 minutes | Flat |
| 90-day clawback rate (health and life) | 7.9% (April to June 2026 sales, the latest complete quarter) | 7.8% (the July 2025 to June 2026 average) | 🟢 Green: at target | None needed | Flat |
Statuses use the thresholds in the Business Requirements. Each status has a text label, so it doesn't rely on colour alone.
What the numbers say:
- Lead mix explains most of the fall. Net revenue fell $26,253 between last quarter and this quarter. The shift into energy leads cost $29,658. Lower conversion cost another $12,186. More leads added $12,172 (see the revenue bridge below).
- Clawbacks aren't the cause yet. The clawback rate on complete quarters is flat at about 7.9%. This quarter's clawbacks haven't fully landed, so the true picture is still to come (see the challenge note "This quarter's clawbacks haven't landed yet").
- Health conversion is the second problem. It's 9.5%, down from 11.0% last quarter, and still falling.
↑ Back to top
2. The dataset
In short: 5 tables of seeded sample data covering 24 months, from 1-Oct-24 to 30-Sep-26, built at 1-in-50 scale of ClearCompare's real volumes.
How the tables link
- leads: one row per enquiry (58,509), with its product area, marketing channel, campaign, state and time
- contacts: the first call for each lead (one per lead, 58,509), with the team that called and the outcome
- sales: one row per sale (7,647), with commission and any clawback. 6.1% have no lead attached
- complaints: one row per complaint (32), linked to a lead
- marketing_spend: daily spend by channel and product area (13,115 rows). It links to leads by channel and product area, not lead by lead
What stands out
| Observation | Why it matters |
|---|---|
| Health is the biggest source of leads (40.9%), but energy is the biggest source of sales (50.9%) | Energy converts well (about 24%) but earns little per sale. This is the lead-mix effect behind the fall |
| Commission per sale: median $104, mean $459, maximum $2,200 | Very lopsided: many small energy sales and a few large home loans. Averages need splitting by product area |
| 245 sales (3.2%) were clawed back, always for the full commission | Clawbacks are rare overall but concentrated in health and life |
| 8.0% of call outcomes are blank, all Manila before March 2025 | A known gap. Contact times are still complete |
| Only 32 complaints, 81% about energy | Too few for monthly rates. Energy's share of complaints is worth watching as energy grows |
| Spend per day and channel: median $19.69, maximum $366.09 | Paid search and social are the expensive channels |
Not in the data: customer identities, the reason a policy was cancelled, agent names, or staff costs. Cost per acquisition covers marketing spend only.
Column by column (the same summary as pandas describe(), produced by my-skills/kpi-creator/tools/describe_data.py):
complaints
32 rows · 5 columns
| Column | Type | Count | Missing | Summary |
|---|---|---|---|---|
complaint_id | id | 32 | 0 (0.0%) | identifier · 32 unique |
lead_id | id | 32 | 0 (0.0%) | identifier · 32 unique |
vertical | text | 32 | 0 (0.0%) | 4 unique · most common "energy" (26, 81.2%) |
category | text | 32 | 0 (0.0%) | 4 unique · most common "billing" (9, 28.1%) |
date | date | 32 | 0 (0.0%) | earliest 2024-11-06 · latest 2026-09-23 |
contacts
58,509 rows · 4 columns
| Column | Type | Count | Missing | Summary |
|---|---|---|---|---|
lead_id | id | 58,509 | 0 (0.0%) | identifier · 58,509 unique |
agent_team | text | 58,509 | 0 (0.0%) | 3 unique · most common "Manila" (23,386, 40.0%) |
first_contact_ts | date | 58,509 | 0 (0.0%) | earliest 2024-10-01 07:24 · latest 2026-09-30 23:18 |
outcome | text | 53,821 | 4,688 (8.0%) | 5 unique · most common "no_answer" (11,764, 21.9%) |
leads
58,509 rows · 6 columns
| Column | Type | Count | Missing | Summary |
|---|---|---|---|---|
lead_id | id | 58,509 | 0 (0.0%) | identifier · 58,509 unique |
vertical | text | 58,509 | 0 (0.0%) | 4 unique · most common "health" (23,906, 40.9%) |
channel | text | 58,509 | 0 (0.0%) | 5 unique · most common "paid_search" (22,100, 37.8%) |
campaign | text | 58,509 | 0 (0.0%) | 480 unique · most common "paid_search-energy-2609" (442, 0.8%) |
created_ts | date | 58,509 | 0 (0.0%) | earliest 2024-10-01 07:07 · latest 2026-09-30 21:37 |
state | text | 58,509 | 0 (0.0%) | 5 unique · most common "SA" (11,785, 20.1%) |
marketing_spend
13,115 rows · 4 columns
| Column | Type | Count | Missing | Summary |
|---|---|---|---|---|
date | date | 13,115 | 0 (0.0%) | earliest 2024-10-01 · latest 2026-09-30 |
channel | text | 13,115 | 0 (0.0%) | 5 unique · most common "paid_search" (2,901, 22.1%) |
vertical | text | 13,115 | 0 (0.0%) | 4 unique · most common "health" (3,569, 27.2%) |
cost | number | 13,115 | 0 (0.0%) | mean 37.97 · std 49.25 · min 0.4000 · 25% 6.83 · 50% 19.69 · 75% 45.59 · max 366.09 |
sales
7,647 rows · 8 columns
| Column | Type | Count | Missing | Summary |
|---|---|---|---|---|
sale_id | id | 7,647 | 0 (0.0%) | identifier · 7,647 unique |
lead_id | id | 7,183 | 464 (6.1%) | identifier · 7,183 unique |
vertical | text | 7,647 | 0 (0.0%) | 4 unique · most common "energy" (3,893, 50.9%) |
partner | text | 7,647 | 0 (0.0%) | 4 unique · most common "Partner D" (1,951, 25.5%) |
gross_commission | number | 7,647 | 0 (0.0%) | mean 458.67 · std 502.36 · min 85.51 · 25% 94.88 · 50% 104.22 · 75% 624.62 · max 2,199.81 |
sale_date | date | 7,647 | 0 (0.0%) | earliest 2024-10-02 · latest 2026-09-30 |
clawback_date | date | 245 | 7,402 (96.8%) | earliest 2024-10-15 · latest 2026-09-26 |
clawback_amount | number | 7,647 | 0 (0.0%) | mean 20.58 · std 114.31 · min 0.00 · 25% 0.00 · 50% 0.00 · 75% 0.00 · max 900.31 |
↑ Back to top
3. Revenue bridge: last quarter to this quarter
Net revenue moved from $429,119 (April to June 2026) to $402,866 (July to September 2026), a fall of $26,253. Each effect is worked out by changing one driver at a time, in this order: leads, mix, conversion, revenue per sale. The four effects add up to the total.
| Effect | Change in net revenue | What it means |
|---|---|---|
| Lead volume | +$12,172 | 212 more leads (7,474 to 7,686) |
| Lead mix | −$29,658 | Energy rose from 27.0% to 39.5% of leads; health fell from 41.4% to 33.9%, life from 18.6% to 13.6% |
| Conversion | −$12,186 | Mostly health: 11.0% down to 9.5% |
| Revenue per sale, after clawbacks | +$3,419 | Mostly timing: this quarter's clawbacks haven't all landed |
| Total | −$26,253 | Net revenue per lead: $57.41 down to $52.42 (−8.7%) |
↑ Back to top
4. Each KPI in detail
Net revenue per lead · Top 5
| Field | Detail |
|---|---|
| Draft measure | Confirmed as Gary drafted it |
| How measured | Net commission (gross minus clawbacks) ÷ leads created |
| Calculation | SUM(sales.gross_commission − sales.clawback_amount) / COUNT(leads.lead_id) |
| Data checks | Every field is present and fully populated. Unmatched sales (6.1%) are included, because each sale carries its product area |
| By quarter | Oct–Dec 24 $56.69 · Jan–Mar 25 $57.33 · Apr–Jun 25 $58.65 · Jul–Sep 25 $58.85 · Oct–Dec 25 $57.54 · Jan–Mar 26 $59.40 · Apr–Jun 26 $57.41 · Jul–Sep 26 $52.42 |
| Last 6 months | Apr $57.77 · May $58.56 · Jun $55.94 · Jul $52.36 · Aug $54.56 · Sep $50.30 |
| By product area, this quarter | Home loans $141.87 · Health $53.64 · Life $51.35 · Energy $22.31 |
| Starting point | $52.42, provisional until 29-Dec-26, when this quarter's clawbacks will all have landed (Observed Fact, sample data). Allowing for the clawbacks still expected: about $51.59 (Strong Inference). Both figures are shown side by side |
| Target | $57.41 by 30-Jun-27 (the April to June 2026 level) |
| Pace needed | +$0.56 a month (+$0.65 using the clawback-adjusted figure) |
| Current trend | −$1.88 a month (June to September). Off pace |
| Counter-metric | Total net revenue. Revenue per lead can be pushed up by cutting low-value leads, which shrinks the business |
Lead mix by product area · Sub-KPI under net revenue per lead
| Field | Detail |
|---|---|
| Draft measure | Confirmed |
| How measured | Leads in each product area ÷ all leads, monthly |
| Energy share, last 6 months | Apr 27.0% · May 27.0% · Jun 27.0% · Jul 36.0% · Aug 39.5% · Sep 43.0% |
| Starting point | 43.0% (September 2026) |
| Target | 30% or less by 31-Mar-27 (moved from 31-Dec-26 at Stage 2) |
| Pace needed | −2.2 points a month (was −4.3 with the 31-Dec-26 date) |
| Current trend | +5.3 points a month. Off pace, and moving the wrong way |
| Status | 🔴 Red (over 33%) |
Lead-to-sale conversion, by product area · Top 5
| Field | Detail |
|---|---|
| Draft measure | Corrected (see the challenge note "Conversion was understated") |
| Before | Leads that became a matched sale ÷ leads created, excluding leads under 7 days old. 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% |
| Calculation | COUNT(sales WHERE vertical = v) / COUNT(leads WHERE vertical = v), by month |
| This quarter vs target | Health 9.5% vs 11.0% 🔴 · Home loans 7.1% vs 7.1% 🟢 · Energy 23.5% vs 24.1% 🟠 · Life 6.6% vs 6.0% 🟢 |
| Last quarter | Health 11.0% · Home loans 6.9% · Energy 24.1% · Life 5.7% |
| Counter-metrics | Complaints per 1,000 sales and the 90-day clawback rate, so conversion isn't bought with pushy selling |
Health insurance conversion · Sub-KPI under lead-to-sale conversion
| Field | Detail |
|---|---|
| Draft measure | Corrected (as above) |
| Starting point | 9.5% this quarter (the CCO quoted 9.4%) |
| Target | 11% by 30-Jun-27 (stated by the CCO) |
| Pace needed | +0.17 points a month |
| Current trend | −0.76 points a month. Off pace |
| Status | 🔴 Red (under 10%) |
Complaints per 1,000 sales · Sub-KPI under lead-to-sale conversion
| Field | Detail |
|---|---|
| Draft measure | Confirmed, now judged on a rolling 12 months (agreed at Stage 2) |
| Last 12 months (October 2025 to September 2026) | 3.75 per 1,000 sales (15 complaints from 4,000 sales) |
| This quarter, for context | 4.5 per 1,000 sales (5 complaints) |
| July 2025 to June 2026 average | 3.6 per 1,000 sales, which is the target |
| By quarter | 4.5 · 2.2 · 7.6 · 4.2 · 4.2 · 0.0 · 6.2 · 4.5 |
| Status | 🟠 Amber: 3% above the target of 3.64 |
Cost per acquisition, by channel · Top 5
| Field | Detail |
|---|---|
| Draft measure | Confirmed |
| How measured | Marketing spend ÷ net sales (sales not clawed back), by channel |
| This quarter (vs July 2025 to June 2026 average) | Paid search $93.04 ($118.56) · Social $91.03 ($99.79) · Partner referral $48.32 ($54.15) · SEO $25.89 ($24.02) · Direct $4.18 ($4.42) |
| Target | Paid search $106.70 by 30-Jun-27. Already met |
| Data note | 6.1% of sales can't be tied to a channel, so cost per acquisition is overstated by about 6% |
| Counter-metric | Net revenue per marketing dollar (added at Stage 2). All channels: $5.71 this quarter, down from $6.37 last quarter. Paid search: $3.49, down from $3.69 (July 2025 to June 2026 average: $3.85). Social $4.90 · Partner referral $8.90 · SEO $14.53 · Direct $101.51. So paid search costs less per sale but earns less per dollar spent |
Speed to contact · Top 5
| Field | Detail |
|---|---|
| Draft measure | Confirmed |
| This quarter | Median 11 minutes. 35.7% of leads waited over 15 minutes |
| By team | Sydney 11 · Melbourne 12 · Manila 11 minutes |
| By channel | Social 16 · Paid search 11 · SEO 11 · Direct 10 · Partner referral 10 minutes |
| Trend | 11 minutes in every quarter since October 2024 |
| Target | 10 minutes or less by 31-Mar-27 |
| Status | 🟠 Amber |
| Counter-metric | Lead-to-sale conversion, so calls aren't rushed just to hit the time |
90-day clawback rate · Top 5
| Field | Detail |
|---|---|
| Draft measure | Confirmed |
| How measured | Health and life sales clawed back within 90 days ÷ all health and life sales, by quarter of sale. Only quarters whose sales are all at least 90 days old count as final |
| By quarter (final) | 7.9% · 8.3% · 7.7% · 8.2% · 7.3% · 8.0% · 7.9% (April to June 2026) |
| By product area, April to June 2026 | Health 8.2% · Life 6.3% |
| This quarter | 5.1% provisional. Not final until 29-Dec-26 |
| Target | 7.8% (the July 2025 to June 2026 average) by 30-Jun-27 |
| Status | 🟢 Green |
| Counter-metric | Lead-to-sale conversion, so clawbacks aren't cut by turning away good customers |
↑ Back to top
5. Challenge notes (for Gary and Adam)
Kewpie doesn't change the KPI list. These notes go back for a decision.
| Challenge | What Kewpie found | Suggested fix | Status |
|---|---|---|---|
| Conversion was understated | Gary's draft only counted sales matched to a lead. 6.1% of sales have no lead, so health conversion read 9.2% instead of 9.5%. The CCO's "9.4%" matches the corrected figure | Count all sales in a product area ÷ leads in that area (applied) | Corrected |
| This quarter's clawbacks haven't landed yet | About $6,351 of clawbacks are still expected on recent health and life sales. That makes this quarter's net revenue per lead $52.42, not the likely $51.59 | Show this quarter's net revenue as "provisional" with the adjusted figure beside it | Accepted 21-Oct-26 |
| The lead-mix target is a big reversal | Energy share needs to fall 4.3 points a month to reach 30% by 31-Dec-26. It's rising 5.3 points a month | Keep 30%, move the date to 31-Mar-27 (2.2 points a month) | Accepted 21-Oct-26 |
| Cost per acquisition looks better than it is | Paid search cost per sale fell to $93 because energy sales are cheap to win. But an energy sale earns about $95, against about $600 for health | Add "net revenue per marketing dollar" as its counter-metric | Accepted 21-Oct-26 |
| Complaint numbers are too small for a monthly rate | Only 32 complaints in 24 months. The quarterly rate swings from 0.0 to 7.6 | Judge it on a rolling 12 months, and show the monthly count for context | Accepted 21-Oct-26 |
| The 9% headline | Measured fall in net revenue per lead: 8.7%. The CCO said "about 9%" | No change. Matches the open question on where the 9% came from | Noted |
↑ Back to top
6. KPI tree
- Net revenue per lead: $52.42 provisional · 🔴 Red
- Lead mix (energy share): 43.0% · 🔴 Red
- Lead-to-sale conversion: health 9.5% · 🔴 Red
- Health insurance conversion: 9.5% · 🔴 Red
- Complaints per 1,000 sales (12 months): 3.75 · 🟠 Amber
- Cost per acquisition (paid search): $93.04 · 🟢 Green
- Speed to contact: 11 minutes · 🟠 Amber
- 90-day clawback rate: 7.9% · 🟢 Green
↑ Back to top
7. Data quality log
| Check | Result |
|---|---|
| Rows | 58,509 leads · 58,509 contacts · 7,647 sales · 13,115 spend rows · 32 complaints |
| Key fields populated | All 100% (product area, channel, commission, contact time) |
| Sales not matched to a lead | 464 (6.1%) |
| Manila call outcomes before March 2025 | 4,688 of 4,688 blank (as expected). Contact times are still present |
| Leads never contacted | 0 |
| Clawbacks only on health and life | Yes |
| Clawback equals the full commission | Yes, on every clawback |
| Sales after the 30-Sep-26 cut-off | 0 |