File: stage-1-04-business-requirements-with-data-mapping.md
ClearCompare: Business Requirements with Data Mapping
Where we are: Stage 2 of 5 (KPI Development). Stage 1 approved 20-Oct-26
Stage 1
Business Requirements
Gary
Problem, objectives, Top 5 KPIs, Project Plan
Stage 2
KPI Development
Kewpie
Calculations tested on the data
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
Stage 1 output for review · Business Requirements v1.0 · 20-Oct-26 · Test case (sample data)
The format follows Stage 1 of your process plan:
- What do we want to know?
- Why?
- Actions
- How is it measured?
- Data mapping and calculations from data sources
- Conditions and filters applied to the data
Each KPI is listed under the business objective it connects to.
Evidence key:
- Stakeholder answers are quoted
- Everything else is Gary's proposal (Working Hypothesis) for you to sign off as SME
- The last 3 fields (measure, data mapping, filters) are a draft. Kewpie runs and confirms them on the data in Stage 2
Problem Statement
Net revenue per lead fell about 9% last quarter, and nobody can say whether the mix of leads, sales conversion or cancelled policies caused it. Without one trusted answer, teams argue about the numbers instead of fixing the problem.↑ Back to top
Business objectives
Each objective helps solve the Problem Statement.
| Business objective | Sub-objectives |
|---|---|
| Grow net commission revenue profitably | Cut the cost of winning a sale through paid search · Reduce commission clawed back within 90 days |
| Convert leads more efficiently | Lift health insurance conversion back to 11% (now 9.4%) |
| Explain performance changes within a day, not a week | none |
↑ Back to top
Objective: Grow net commission revenue profitably
Net revenue per lead · Top 5 · Must Have · Lagging
Serves: Grow net commission revenue profitably; Explain performance changes within a day
| Field | Detail |
|---|---|
| What do we want to know? | How much net commission we earn from each lead, after clawbacks |
| Why? | It's the headline the CCO is asked about ("Revenue per lead dropped 9%"). Net, not gross, shows whether growth is profitable |
| Actions | If below threshold 2 weeks running, use the revenue bridge to find the driver. The CCO then assigns the fix: marketing for mix or volume, the vertical head for conversion or clawbacks. Owner: CCO |
| How is it measured? | Net commission ÷ leads created, weekly and quarterly, by vertical |
| Data mapping and calculations | sales.gross_commission, sales.clawback_amount, sales.sale_date, sales.vertical · leads.lead_id, leads.created_ts, leads.verticalCalc: SUM(gross_commission − clawback_amount) / COUNT(lead_id) |
| Conditions and filters | Revenue by sale_date, leads by created_ts. The 6% of unmatched sales are included (they carry a vertical). Clawbacks land up to 90 days after a sale, so the latest quarter is flagged as incomplete (open question: should the current quarter's clawbacks be estimated?) |
| Target (SMART) | Back to the April to June 2026 level by 30-Jun-27 Timing to confirm with the CCO: within 3, 6 or 9 months, or 1 or 2 quarters · 3 months: by 31-Jan-27 · 6 months: by 30-Apr-27 · 9 months: by 31-Jul-27 · 1 quarter: by 31-Mar-27 (end of the January to March quarter) · 2 quarters: by 30-Jun-27 (end of the April to June quarter) |
| Thresholds | Green: at or above target · Amber: up to 3% below · Red: more than 3% below |
Lead mix by product area · Sub-KPI under Net revenue per lead · Must Have · Leading
Serves: Grow net commission revenue profitably; Explain performance changes within a day
| Field | Detail |
|---|---|
| What do we want to know? | What share of leads comes from each vertical |
| Why? | Verticals pay very different commissions, so a shift in mix moves revenue per lead without anyone selling worse |
| Actions | If a low-commission vertical's share rises more than 3 points in a month, marketing reviews campaign targeting. Owner: Head of Marketing |
| How is it measured? | Leads by vertical ÷ all leads, monthly |
| Data mapping and calculations | leads.vertical, leads.created_tsCalc: COUNT(lead_id WHERE vertical = v) / COUNT(lead_id) |
| Conditions and filters | Calendar month by created_ts. Test leads excluded (none in the sample data) |
| Target (SMART) | Energy leads at or below 30% of all leads each month, by 31-Dec-26. Energy was about 26% of leads until June 2026, then about 40% in July to September 2026 |
| Thresholds | Green: 30% or less · Amber: over 30% up to 33% · Red: over 33% |
Cost per acquisition, by channel · Top 5 · Must Have · Lagging
Serves: Grow net commission revenue profitably (cut the cost of winning a sale through paid search)
| Field | Detail |
|---|---|
| What do we want to know? | How much marketing spend it takes to win each net sale, by channel |
| Why? | Growth only helps if it isn't bought with rising acquisition cost. The CCO named paid search |
| Actions | If a channel is above threshold for 2 weeks, marketing moves budget to the cheaper channels. Owner: Head of Marketing |
| How is it measured? | Marketing spend ÷ net sales, monthly, by channel |
| Data mapping and calculations | marketing_spend.cost, marketing_spend.channel, marketing_spend.date · sales.lead_id → leads.channel, sales.clawback_amountCalc: SUM(cost) / COUNT(sales WHERE no clawback) per channel |
| Conditions and filters | Net sales = sales not clawed back. Unmatched sales (6%) can't be tied to a channel, so they're excluded and counted. Spend and sales are matched by month |
| Target (SMART) | Paid search 10% below its July 2025 to June 2026 average by 30-Jun-27; other channels hold. The size of the cut is Gary's proposal Timing to confirm with the CCO: within 3, 6 or 9 months, or 1 or 2 quarters · 3 months: by 31-Jan-27 · 6 months: by 30-Apr-27 · 9 months: by 31-Jul-27 · 1 quarter: by 31-Mar-27 (end of the January to March quarter) · 2 quarters: by 30-Jun-27 (end of the April to June quarter) |
| Thresholds | Green: at or below target · Amber: up to 10% above · Red: more than 10% above |
90-day clawback rate · Top 5 · Must Have · Lagging
Serves: Grow net commission revenue profitably (reduce commission clawed back within 90 days)
| Field | Detail |
|---|---|
| What do we want to know? | What share of health and life sales are clawed back within 90 days |
| Why? | Finance's explanation of the fall: "Partners claw back commission if a health or life policy is cancelled within 90 days." Clawbacks also point to poor-fit sales |
| Actions | If a vertical is above threshold for a month, its vertical head and compliance review the sales mix and call quality. Owner: Vertical heads |
| How is it measured? | Clawed-back sales ÷ all sales, health and life, by the month of sale |
| Data mapping and calculations | sales.clawback_date, sales.sale_date, sales.verticalCalc: COUNT(clawback_date − sale_date <= 90 days) / COUNT(sales) |
| Conditions and filters | Health and life only (the other verticals have no clawbacks). Only months whose sales are at least 90 days old count as final; newer months are shown as provisional |
| Target (SMART) | Back to the July 2025 to June 2026 average by 30-Jun-27 Timing to confirm with the CCO: within 3, 6 or 9 months, or 1 or 2 quarters · 3 months: by 31-Jan-27 · 6 months: by 30-Apr-27 · 9 months: by 31-Jul-27 · 1 quarter: by 31-Mar-27 (end of the January to March quarter) · 2 quarters: by 30-Jun-27 (end of the April to June quarter) |
| Thresholds | Green: at or below target · Amber: up to 1 point above · Red: more than 1 point above |
↑ Back to top
Objective: Convert leads more efficiently
Lead-to-sale conversion, by product area · Top 5 · Must Have · Leading
Serves: Convert leads more efficiently
| Field | Detail |
|---|---|
| What do we want to know? | What share of leads become a sale, in each vertical |
| Why? | Conversion moves before revenue does, so it gives an early warning |
| Actions | If a vertical is below threshold for a week, its vertical head reviews contact speed, scripts and team coaching. Owner: Vertical heads |
| How is it measured? | Leads that became a sale ÷ leads created, weekly, by vertical |
| Data mapping and calculations | leads.lead_id, leads.vertical, leads.created_ts · sales.lead_idCalc: COUNT(DISTINCT sales.lead_id) / COUNT(leads.lead_id) |
| Conditions and filters | Grouped by lead created_ts. Leads under 7 days old are excluded, since they're still open. Unmatched sales (6%) are excluded, which understates conversion by about 6%; this is noted on the chart |
| Target (SMART) | Health 11% (stated by the CCO). Other verticals hold their July 2025 to June 2026 average, by 30-Jun-27 Timing to confirm with the CCO: within 3, 6 or 9 months, or 1 or 2 quarters · 3 months: by 31-Jan-27 · 6 months: by 30-Apr-27 · 9 months: by 31-Jul-27 · 1 quarter: by 31-Mar-27 (end of the January to March quarter) · 2 quarters: by 30-Jun-27 (end of the April to June quarter) |
| Thresholds | Green: at or above target · Amber: up to 1 point below · Red: more than 1 point below |
Health insurance conversion · Sub-KPI under Lead-to-sale conversion · Must Have · Lagging
Serves: Convert leads more efficiently (lift health insurance conversion back to 11%)
| Field | Detail |
|---|---|
| What do we want to know? | Health insurance lead-to-sale conversion |
| Why? | The CCO's stated target: "Lift health insurance conversion back to 11% (it's 9.4% now)" |
| Actions | The health vertical head runs a weekly conversion review until it's back above 10.5%. Owner: Head of Health |
| How is it measured? | As lead-to-sale conversion, for health only |
| Data mapping and calculations | As lead-to-sale conversion, filtered to leads.vertical = 'health' |
| Conditions and filters | As lead-to-sale conversion |
| Target (SMART) | 11% by 30-Jun-27 (stated by the CCO) |
| Thresholds | Green: at least 11% · Amber: 10% to 11% · Red: under 10% |
Complaints per 1,000 sales · Sub-KPI under Lead-to-sale conversion · Must Have · Lagging
Serves: Convert leads more efficiently
| Field | Detail |
|---|---|
| What do we want to know? | How many complaints we get for every 1,000 sales, by vertical |
| Why? | "We're financial services: complaints and compliance breaches must stay visible." Conversion must not be bought with mis-selling |
| Actions | Any month above threshold triggers a compliance review of that vertical's sales calls. Owner: Head of Compliance |
| How is it measured? | Complaints ÷ sales × 1,000, monthly, by vertical |
| Data mapping and calculations | complaints.complaint_id, complaints.vertical, complaints.date · sales.sale_id, sales.sale_dateCalc: COUNT(complaints) / COUNT(sales) × 1000 |
| Conditions and filters | Complaints by complaint date, sales by sale_date, same month. Low volumes, so a 3-month rolling rate is shown alongside |
| Target (SMART) | At or below the July 2025 to June 2026 rate by 30-Jun-27 |
| Thresholds | Green: at or below the July 2025 to June 2026 rate · Amber: up to 25% above · Red: more than 25% above |
Speed to contact · Top 5 · Must Have · Leading
Serves: Convert leads more efficiently
| Field | Detail |
|---|---|
| What do we want to know? | How many minutes it typically takes from a lead arriving to the first call |
| Why? | Leads go cold fast. It's the quickest lever the sales team controls |
| Actions | If the median goes over threshold on any day, sales operations adjusts rostering across Sydney, Melbourne and Manila. Owner: Head of Sales Operations |
| How is it measured? | Median minutes from lead created to first contact, daily, by team |
| Data mapping and calculations | leads.created_ts · contacts.first_contact_ts, contacts.agent_teamCalc: MEDIAN(first_contact_ts − created_ts) in minutes |
| Conditions and filters | Leads never contacted are excluded from the median and counted separately. Manila outcomes are blank before Mar-25, but contact times are still usable |
| Target (SMART) | Median of 10 minutes or less by 31-Mar-27 Timing to confirm with the CCO: within 3, 6 or 9 months, or 1 or 2 quarters · 3 months: by 31-Jan-27 · 6 months: by 30-Apr-27 · 9 months: by 31-Jul-27 · 1 quarter: by 31-Mar-27 (end of the January to March quarter) · 2 quarters: by 30-Jun-27 (end of the April to June quarter) |
| Thresholds | Green: 10 min or less · Amber: 10–15 min · Red: over 15 min |
↑ Back to top
Objective: Explain performance changes within a day
This objective is met by net revenue per lead together with a revenue bridge on the dashboard. It isn't a separate KPI.
| Field | Detail |
|---|---|
| What do we want to know? | Why net revenue moved between last quarter and this quarter |
| Why? | "When a number moves, the dashboard tells me why before anyone asks." "Show me a bridge from last quarter to this quarter." |
| Actions | The CCO uses the bridge in the Monday exec meeting to assign the fix to the right owner |
| How is it measured? | The change in net revenue split into 4 effects: lead volume, vertical mix, conversion and clawbacks. The 4 effects add up to the total change |
| Data mapping and calculations | The same fields as net revenue per lead, lead mix, lead-to-sale conversion and the 90-day clawback rate. Each effect is calculated by changing one driver at a time, holding the others at last quarter's values |
| Conditions and filters | As net revenue per lead. The latest quarter's clawback effect is provisional until clawbacks have had 90 days to land |
↑ Back to top
Nice to Have (backlog: not developed this time)
| What do we want to know? | Why | Why it's Nice to Have |
|---|---|---|
| Total leads | Volume context | No action attached. Shown inside the bridge as the volume effect |
| Revenue by partner | Good to know | No decision or owner named |
| Agent-level conversion | Coaching | The constraint says team leaders only, not the exec view |
| Website visits | Top-of-funnel interest | No data source and no action |
↑ Back to top
For your review
- Actions and owners: all are Gary's proposals. The stakeholder said "I don't know" when asked who would act. Approve them or change them.
- Targets and dates: only "health conversion back to 11%" came from the stakeholder. The date 30-Jun-27 is Gary's proposal.
- Lead-mix target: changed from "Agreed mix range per vertical (to be set)" to "Energy leads at or below 30% of all leads each month, by 31-Dec-26".