Sales & Revenue Performance Analytics Questions
Analyzing and reporting how the commercial engine is performing, from the descriptive-analytics seat. Covers defining and computing sales and revenue KPIs (MRR, ARR, bookings versus billings versus recognized revenue, NRR/GRR, logo and revenue churn, ACV, ARPA, DSO, run rate, magic number), including the SQL behind them; retention and cohort analysis of customers, leads and opportunities; sales funnel and pipeline analysis (stage conversion, win rate, deal velocity and sales cycle length, pipeline coverage, quota attainment, rep productivity, win/loss); executive and manager revenue dashboards and reporting design; and revenue data quality (reconciling CRM against billing, stage and closed-won definitions, overstated pipeline, multi-currency and revenue-recognition effects on reported metrics). Also covers explaining a worsening metric to stakeholders, resolving disputes over metric definitions, and spotting and handling metrics that incentives push people to game. Forecasting method, revenue-operations process and compensation design, CRM administration, marketing attribution, BI platform engineering, customer-success operations and unit-economics modelling are covered elsewhere.
How would you compute dollar-based net retention by cohort when customers also pay metered usage and add-ons, upsell several times, and sometimes churn partially? Say how you handle each of those in the numerator.
Sample Answer
Direct answer
Net revenue retention (NRR) for a cohort is the recurring revenue today from customers who were in the cohort at the start, divided by what those same customers paid at the start. I would measure it per customer at two fixed dates (start and 12 months later), put every revenue stream (subscription, add-ons, metered usage) on the same annualized basis at both dates, and net everything at the customer level. New customers never enter. Each of the cases in the question has a specific treatment in the numerator, below.
Definitions
- ARR: annual recurring revenue, the annualized value of a customer's recurring fees.
- Cohort: customers grouped by when they became customers (for example start quarter).
- Logo churn: losing a customer entirely, counted by customers rather than dollars. Partial churn is not logo churn.
- Add-on attach: the share of customers that have bought an add-on.
- Run-rate: a customer's current level of revenue stated as a full year.
- Reactivated customer: one who left and later came back.
- Bridge: a table that starts at the cohort's starting revenue and walks to its ending revenue by adding expansion and subtracting contraction and churn.
- Expansion / contraction / churn: a customer's dollars go up, go down but stay above zero, or go to zero.
- GRR (gross revenue retention): like NRR but each customer is capped at its starting dollars, so expansion cannot hide losses.
How each case is handled in the numerator
| Case | Treatment |
|---|---|
| Metered usage | Include, but put it on a smoothed basis at both ends (trailing 3-month average annualized), so one spike month at the end does not inflate NRR. Report NRR with and without usage ("committed" vs "total") so volatile revenue is visible. |
| Add-ons | Include in the same customer's total. Add-on purchases are expansion, add-on cancellations are contraction. Also track add-on attach separately as a product view, never as a separate cohort. |
| Several upsells | Use the end-of-window snapshot per customer, not the sum of upsell events. Events double count when a later downgrade follows an earlier upsell. The snapshot gives the net answer, and a bridge (expansion, contraction, churn) is derived per customer by comparing start and end. |
| Partial churn | The customer stays in the cohort with a lower end value. That is contraction, not logo churn. Only a customer whose end value is zero counts as churned. |
Further rules: roll sub-accounts up to the parent customer, convert currency at a constant rate, keep reactivated customers in their original cohort, and tag price increases so they are not mistaken for adoption.
Worked example (illustrative, $k annualized)
Six customers from one cohort. Subscription and add-ons are annualized $k. Metered usage is given as the last three months of usage ($k per month) at each date; the code smooths it into an annualized figure by taking the trailing 3-month average and multiplying by 12. Each tuple is (subscription, add-ons, usage) at month 0 and month 12.
# Last three months of metered usage, in $k per month, at each snapshot date.
def annualize(last3):
return sum(last3) / 3 * 12 # trailing 3-month average, times 12 months
# customer: (subscription, add-ons, usage months) at month 0, then at month 12.
# Subscription and add-ons are already annualized $k. Add-ons are the customer's total add-on ARR.
raw = {
"A": ((100, 0, (1.5, 1.5, 2.0)), (100, 30, (2.0, 2.25, 2.25))), # two add-on upsells (10 + 20) summed in the add-on column
"B": ((60, 10, (0.5, 0.5, 0.5)), (60, 0, (0.25, 0.25, 0.5))), # partial churn: add-on cancelled
"C": ((40, 0, (0, 0, 0)), (0, 0, (0, 0, 0))), # full churn
"D": ((80, 0, (0.75, 0.75, 1.0)), (120, 0, (1.0, 1.25, 1.25))), # seat expansion
"E": ((50, 5, (1.25, 1.25, 1.25)), (50, 5, (0.25, 0.25, 0.25))), # usage fell
"F": ((30, 0, (0, 0, 0)), (30, 0, (0, 0, 0))), # flat
}
cohort = {c: tuple((s, a, annualize(u)) for s, a, u in snaps) for c, snaps in raw.items()}
start = sum(sum(m0) for m0, _ in cohort.values())
end = sum(sum(m12) for _, m12 in cohort.values())
grr_num = sum(min(sum(m0), sum(m12)) for m0, m12 in cohort.values())
rec_start = sum(m0[0] + m0[1] for m0, _ in cohort.values())
rec_end = sum(m12[0] + m12[1] for _, m12 in cohort.values())
print("start", start, "end", end, "NRR", round(end / start * 100, 1))
print("GRR", round(grr_num / start * 100, 1))
print("contracted-only start", rec_start, "end", rec_end, "NRR", round(rec_end / rec_start * 100, 1))
usage_s = sum(m0[2] for m0, _ in cohort.values()); usage_e = sum(m12[2] for _, m12 in cohort.values())
print("usage", usage_s, usage_e)
# One-off migration in the final month: last three months are 1, 1, 7 ($k)
print("last month x 12:", 7 * 12, " trailing 3-month average x 12:", annualize((1, 1, 7)))
Output from running that code:
start 426.0 end 442.0 NRR 103.8
GRR 85.0
contracted-only start 375 end 395 NRR 105.3
usage 51.0 47.0
last month x 12: 84 trailing 3-month average x 12: 36.0
The one-off migration line at the end shows why smoothing matters: a customer whose last three months are 1, 1 and 7 ($k) would read 7 x 12 = 84 from the last month alone, but 36 on the trailing average, a much smaller distortion that the next month's data corrects.
GRR here is (120 + 64 + 0 + 90 + 58 + 30) / 426 = 362 / 426 = 85.0%: each customer counts at the smaller of start and end (A 120, B 64, C 0, D 90, E 58, F 30).
Bridge: start 426, expansion +80 (A +36, D +44), contraction -24 (B -12 from a cancelled add-on and lower usage, E -12 from lower usage), churn -40 (C), end 426 + 80 - 24 - 40 = 442. So NRR is 103.8% and GRR is 85.0%. The 18.8-point gap is the tell: expansion from two customers is carrying a cohort that lost 15% of its starting dollars. Excluding usage, the NRR is 105.3%, and usage itself fell from 51 to 47.
Trade-offs and pitfalls
- NRR with usage can swing on seasonality. Compare same-calendar windows or use trailing averages on both ends.
- Always report GRR beside NRR.
- Do not compute NRR from billing events by summing deltas: renewals, credits and timing shifts create double counts.
- Define the denominator date carefully: customers who churn before the end date must still be in the denominator.
From an opportunities table with created, stage-change and closed dates, how would you measure sales cycle length and deal velocity by rep and by segment? State what you do with lost and stalled deals.
Sample Answer
Direct answer
Cycle length is closed date minus created date for deals that have closed; deal velocity is expected won dollars per day, per rep and per segment. Compute them separately for won and lost deals, never mix open deals into an average of closed ones (they have no end date yet and would bias the number short), and report stalled open deals as their own group. Compare SMB (small and medium business) and enterprise as separate populations, because their deal sizes differ by about ten times and their cycles by about three to four times (35 to 42 days against 103 to 156 days in the example below), so any blended average describes neither.
What this velocity measures
This is historical throughput: of the deals that reached an outcome, how many dollars per day of selling time did they deliver? That is why the count is decided deals (won plus lost) and not open opportunities. The forward-looking version of the formula uses open opportunities and asks what the current pipeline is expected to deliver; use that one for a forecast and this one to compare reps and segments on what they have already shown. Both have the same shape (count x win rate x average deal size / cycle days), so state which count you used whenever you quote the number.
Definitions
- Sales cycle length (days): closed date minus created date, for won deals. Report lost-deal cycle length separately: it shows how long deals consume capacity before dying.
- Deal velocity ($ per day): decided deals x win rate x average won amount / won cycle days (decided means won or lost). Using decided deals only avoids the open-deal problem.
- Open and stalled deals: open deals have no closed date, so they cannot enter the averages. Show their age as of a fixed date and flag as stalled any open deal with no stage change for a set number of days (45 in the example; calibrate from the gaps on won deals).
- Censoring: averages over deals closed so far lean short, because long deals are still open. Fix by using only cohorts old enough to have mostly closed (for example, deals created more than twice the typical cycle ago) or by reporting the share of each creation cohort (the deals created in the same week or month) closed by day 30, 60 and 90. Numeric example: suppose enterprise cycles run anywhere from 20 to 180 days, so the true average is about 100 days and a typical long deal takes about 120 days. Looking at deals created only 60 days ago, the only ones closed are those with cycles of 60 days or less, and their average is about 40 days, far shorter than the truth. Waiting until the cohort is 240 days old (twice the typical 120-day long-deal cycle) lets nearly all of them close, and the average moves to about its true value.
Runnable example
Synthetic data (seeded), a table with created, last stage change and closed dates, and the three queries:
import sqlite3, random, datetime as dt
random.seed(11)
AS_OF = dt.date(2026, 9, 30)
con = sqlite3.connect(":memory:")
con.execute("""CREATE TABLE opportunities(
opp_id INTEGER PRIMARY KEY, rep TEXT, segment TEXT, amount REAL,
created_date TEXT, last_stage_change_date TEXT, closed_date TEXT, outcome TEXT)""")
reps = {"Ana": "SMB", "Ben": "SMB", "Chen": "Enterprise", "Dara": "Enterprise"}
# (win prob, mean cycle days, mean deal size) per segment
params = {"SMB": (0.30, 35, 9000), "Enterprise": (0.18, 120, 90000)}
oid = 0
for rep, seg in reps.items():
for _ in range(60):
oid += 1
created = dt.date(2026, 1, 1) + dt.timedelta(days=random.randint(0, 239))
pw, mc, ms = params[seg]
if rep in ("Ben", "Dara"): # these two reps are a little slower and lower-converting
pw, mc = pw * 0.8, mc * 1.25
cycle = max(5, int(random.gauss(mc, mc * 0.3)))
amount = round(random.gauss(ms, ms * 0.25), -2)
closes = created + dt.timedelta(days=cycle)
r = random.random()
if closes <= AS_OF and r < 0.85:
outcome = "won" if random.random() < pw / 0.85 else "lost"
con.execute("INSERT INTO opportunities VALUES(?,?,?,?,?,?,?,?)",
(oid, rep, seg, amount, created.isoformat(), closes.isoformat(), closes.isoformat(), outcome))
else:
age = (AS_OF - created).days
last = AS_OF - dt.timedelta(days=random.randint(0, min(age, 75)))
con.execute("INSERT INTO opportunities VALUES(?,?,?,?,?,?,NULL,'open')",
(oid, rep, seg, amount, created.isoformat(), last.isoformat()))
print("Won and lost cycle length (days, created to closed) by segment and rep")
q1 = """
SELECT segment, rep,
SUM(outcome='won') AS won, SUM(outcome='lost') AS lost,
ROUND(AVG(CASE WHEN outcome='won' THEN julianday(closed_date)-julianday(created_date) END),1) AS won_cycle_d,
ROUND(AVG(CASE WHEN outcome='lost' THEN julianday(closed_date)-julianday(created_date) END),1) AS lost_cycle_d,
ROUND(1.0*SUM(outcome='won')/COUNT(*),3) AS win_rate,
ROUND(AVG(CASE WHEN outcome='won' THEN amount END)) AS avg_won_amount
FROM opportunities WHERE outcome IN ('won','lost')
GROUP BY segment, rep ORDER BY segment, rep"""
for r in con.execute(q1): print(r)
print("\nDeal velocity per day (decided deals only): n * win_rate * avg_won_amount / won_cycle_days")
q2 = """
WITH s AS (
SELECT segment, rep, COUNT(*) AS n,
1.0*SUM(outcome='won')/COUNT(*) AS wr,
AVG(CASE WHEN outcome='won' THEN amount END) AS avg_amt,
AVG(CASE WHEN outcome='won' THEN julianday(closed_date)-julianday(created_date) END) AS cyc
FROM opportunities WHERE outcome IN ('won','lost') GROUP BY segment, rep)
SELECT segment, rep, n, ROUND(n*wr*avg_amt/cyc) AS velocity_per_day FROM s ORDER BY segment, rep"""
for r in con.execute(q2): print(r)
print("\nOpen deals as of 2026-09-30, split by stalled (no stage change for 45+ days) or moving")
q3 = f"""
SELECT segment,
CASE WHEN julianday('{AS_OF}')-julianday(last_stage_change_date) >= 45 THEN 'stalled' ELSE 'moving' END AS state,
COUNT(*) AS n, ROUND(SUM(amount)) AS open_value,
ROUND(AVG(julianday('{AS_OF}')-julianday(created_date))) AS avg_age_d
FROM opportunities WHERE outcome='open' GROUP BY 1,2 ORDER BY 1,2"""
for r in con.execute(q3): print(r)
Each output row is a tuple in the order of the SQL column aliases. First block: segment, rep, won, lost, won_cycle_d, lost_cycle_d, win_rate, avg_won_amount. Second block: segment, rep, n (decided deals), velocity_per_day. Third block: segment, state (stalled or moving), n (open deals), open_value, avg_age_d.
What the three queries do, in plain words: the first groups the decided deals by segment and rep and averages cycle length separately for wins and losses (the CASE inside AVG feeds the average only the rows of one outcome; julianday turns a date into a day number so subtracting two gives days). The second repeats the grouping inside a WITH clause (a named temporary result) so the velocity formula can use the win rate, average amount and cycle as ready-made columns. The third looks only at open deals and labels each moving or stalled by days since its last stage change, then counts and sums them.
Output:
Won and lost cycle length (days, created to closed) by segment and rep
('Enterprise', 'Chen', 10, 23, 103.0, 94.5, 0.303, 97810.0)
('Enterprise', 'Dara', 3, 18, 156.0, 145.3, 0.143, 118500.0)
('SMB', 'Ana', 16, 31, 35.1, 35.6, 0.34, 9675.0)
('SMB', 'Ben', 12, 31, 41.8, 44.7, 0.279, 9042.0)
Deal velocity per day (decided deals only): n * win_rate * avg_won_amount / won_cycle_days
('Enterprise', 'Chen', 33, 9496.0)
('Enterprise', 'Dara', 21, 2279.0)
('SMB', 'Ana', 47, 4415.0)
('SMB', 'Ben', 43, 2594.0)
Open deals as of 2026-09-30, split by stalled (no stage change for 45+ days) or moving
('Enterprise', 'moving', 42, 3736300.0, 97.0)
('Enterprise', 'stalled', 24, 2147100.0, 111.0)
('SMB', 'moving', 20, 182900.0, 138.0)
('SMB', 'stalled', 10, 99300.0, 206.0)
Reading the output
- Within each segment, compare the reps, and note sample size: Dara has only 3 won deals, so her 156-day cycle is a weak signal; do not rank her against Chen on that basis.
- Enterprise cycles (103 and 156 days) are several times SMB (35 and 42 days): never average across them.
- Win rate and velocity are per rep, so a rep with a long cycle can still have high velocity through deal size; read both.
- The open-deal table is a data-quality finding: the 10 stalled SMB deals average 206 days old, many times a typical SMB cycle of 35 to 42 days. They are almost certainly dead records, and should be reviewed and closed before they inflate pipeline and drag any average they are added to.
What to do with lost and stalled deals
- Lost: keep them in win rate and report their cycle separately. Dropping them makes win rate 100%.
- Stalled: report as a count and value by rep and segment, and send them to the owner for a decision (revive with a next step or close as lost). Do not silently include them in cycle averages, and do not silently delete them.
Pitfalls
- Mixing open deals into cycle averages; averaging across segments; small samples per rep; using a calendar window that over-represents deals that closed fast.
How would you run a cohort analysis on sales opportunities or leads to tell whether conversion is improving or deteriorating over time? Say how you would group them, what you would measure, and what could fool you.
Sample Answer
Direct answer
Group opportunities (or leads) by the month they were created, then follow each group forward and measure what share reaches each outcome by fixed ages: converted within 30 days, won within 90 days, and so on. Compare cohorts at the same age, never at the same calendar date. A cohort is a group of records that share a starting event and month. Win rate is the share of records that end as won; always say whether the denominator is all records created or only those already decided. In-flight means still open, with no outcome yet. Conversion is improving only if later cohorts beat earlier ones at equal age. The biggest ways to be fooled are in-flight deals that have not had time to resolve, changes in lead mix, and slicing by close date instead of creation date.
How to set it up
- Grouping. Cohort = creation month of the lead or opportunity. Add cuts that explain differences: source channel, segment, deal size band, region, and rep tenure.
- Measure. Per cohort, per age bucket: share reaching the next stage; share closed-won (won out of all created, and won out of decided); median days to close; average deal size. Use a "within N days" rule so cohorts of different ages are comparable. Pick N from the long-run sales cycle.
- Display. A triangle: rows are cohorts, columns are age (30, 60, 90 days). Cells beyond the data's edge stay blank rather than showing partial numbers.
- Decision. If the 90-day win rate for the last four mature cohorts is lower than for the four before, conversion is deteriorating, and the segment cuts then say where.
Illustrative example of the in-flight trap
Suppose a cohort of 100 opportunities will eventually win 20 and lose 80. Typical losses close around day 45 and typical wins around day 90. Checking at day 60, imagine 60 have been lost, 4 have been won, and 36 are still open (16 of them will eventually win). The naive "won out of created" is 4 / 100 = 4%, which looks like collapse versus an older cohort's final 20%. The "won out of decided" is 4 / (4 + 60) = 6.3%, which also looks like collapse, because the quick losses have closed and the slow wins have not. Both understate for the same reason: the open deals have not had time to resolve. (If wins closed faster than losses, the decided-only rate would overstate instead.) The fix is to compare to older cohorts at the same age (what share of the older cohort had won by day 60?) or to wait until the cohort matures.
The triangle below, as of the end of June, shows what that comparison looks like. Each cell is cumulative closed-won as a share of all records created (illustrative):
| Cohort | Age 30 days | Age 60 days | Age 90 days | Age 120 days |
|---|---|---|---|---|
| Jan | 1% | 4% | 14% | 20% |
| Feb | 1% | 4% | 13% | 19% |
| Mar | 1% | 5% | 15% | |
| Apr | 1% | 3% |
Read down a column, never across a row. At day 60 the April cohort is at 3% against 4% to 5% for earlier cohorts, a small gap worth watching but not a collapse; its day-90 cell does not exist yet, so it stays blank.
What can fool you
| Trap | What it does | Defence |
|---|---|---|
| In-flight deals in recent cohorts | Recent cohorts look worse (naive) or better (decided-only) | Compare at equal age, grey out immature cells |
| Close-date snapshots | Win rate by close month mixes deals created at different times, so a surge of old deals closing looks like improvement | Cohort by created date; use close-date snapshots only for revenue forecasting |
| Mix shift (the blend of lead sources changes) | A new channel with low buying intent (how ready the buyer is to purchase) drops the blended rate, with no change in any channel | Report by channel and compare like with like |
| Definition changes | Lead qualification rules or stage definitions (what qualifies a record to enter each stage) change | Version the definitions and annotate cohorts |
| Small cohorts | A 20-record cohort swings by 10 points on two deals | Show the number of records in each cell and a range of plausible values around each rate (an interval); pool months, meaning combine adjacent months into one cohort |
| Re-opened or re-created records | One buyer counted twice | Deduplicate to one buyer and one opportunity |
| Seasonality | Q4 budget flush | Compare the same month year over year |
Cohort by created date versus snapshot by close date
Created-date cohorts answer "is our process converting better?". Close-date snapshots answer "how much did we win this quarter?". They give different numbers for the same data and both are valid, so label which one a chart uses.
Trade-offs
Fixed-window rates are comparable but throw away deals that close later; survival curves use all the data but are harder to explain. A survival curve plots how the share of records still undecided falls with age, and it treats a still-open deal as "not decided yet, observed this long" instead of counting it as a loss; that treatment is called censoring.
Several regions use different definitions for pipeline stages, forecast categories and quota attainment. How would you lead the move to one reporting standard across them?
Sample Answer
Direct answer
I would run it as a change-management project with one sponsor and a small working group, not as a data clean-up. First inventory every regional definition and quantify how much each one moves the numbers. Then agree a short global core standard that leaves room for named local extensions, dual-run old and new for one close cycle, and cut over to a consolidated view within 90 days. The standard covers definitions (pipeline stages, forecast categories, quota attainment); it does not set the forecasting method itself, which is a separate decision.
Terms used here
- Pipeline stage: the step an open sales opportunity has reached.
- Forecast category: the label a deal carries in a forecast (for example Commit, Best Case, Pipeline) that says how likely the seller considers it.
- Quota attainment: closed bookings (the value of signed contracts) divided by the quota (target) for the period, as a percentage.
- Close cycle: the period-end routine in which numbers are finalised and reviewed.
- Restate: recalculate past periods under a new definition.
- Co-signatory: a second executive who must also approve, here Finance.
- Compensation plan: the document that sets how sellers are paid, including the quota and how attainment is measured.
- Dual-run: publishing the old and new numbers side by side for a cycle.
Approach
- Get a sponsor and decision rights. One executive (the head of Sales Operations or Revenue Operations, with Finance as co-signatory) who can say "this is the standard". Without it, each region negotiates forever.
- Inventory and quantify. For each region: stage names and exit criteria, what each forecast category means, and how attainment is computed. Then restate the last four quarters under a candidate common definition to show the impact per region. Arguments over principles become arguments over a number.
- Design a core plus extensions. A small mandatory core everyone reports (six stages, four forecast categories, one attainment formula), plus labelled local fields where a region has a real structural difference (for example a public-sector tender stage). Extensions map to the core.
- Separate reporting from compensation. Quota attainment used for pay is governed by the compensation plan. Changing the reporting definition must not secretly change anyone's pay, so state clearly which number is used for what.
- Dual-run, then cut over. Publish the old and new numbers side by side for one close cycle and review the differences with regional leads.
- Lock it in. Change control, a named steward, and retirement of the old reports with a published date.
The core standard, written out
Six stages (Closed lost is the other end state):
| Stage | Name | Exit criterion |
|---|---|---|
| 1 | Qualify | Buyer and problem identified, first meeting held |
| 2 | Discover | Need, budget and decision process confirmed |
| 3 | Validate | Demo or proof of fit delivered, internal champion named |
| 4 | Propose | Written pricing proposal sent |
| 5 | Negotiate | Terms under discussion with legal or procurement |
| 6 | Closed won | Contract signed by both parties |
Four forecast categories:
| Category | Meaning |
|---|---|
| Pipeline | Open deal not yet in the forecast (stages 1 to 3) |
| Best case | Could close this period if things go well (stage 4 or later) |
| Commit | The seller and manager will stand behind it (stage 5 or later) |
| Closed | Signed, counted in attainment |
The stage numbers in the table are minimum stages, not overlapping ranges. Each deal carries exactly one category at a time: Closed if signed; otherwise Commit if the seller and manager commit to it and it is at stage 5 or later; otherwise Best case if it is at stage 4 or later; otherwise Pipeline. A stage 5 deal that the manager does not yet commit to stays in Best case, so the four categories never double count a deal and the category totals add up to total open pipeline plus Closed.
One attainment formula: attainment = bookings signed in the period, at contract signature date and in the reporting currency, divided by the period quota. For example 1,900,000 / 2,000,000 = 95%.
How a local extension maps to the core: a region's "Technical win" stage maps to core stage 3 Validate, and a public-sector "Tender submitted" stage maps to core stage 4 Propose, with the tender reference kept in a labelled local field. Global reports use the core stage, the region keeps its own label.
90-day plan
| Days | Work | Output |
|---|---|---|
| 1 to 15 | Sponsor sign-off, working group named, definition inventory per region | Inventory of definitions |
| 16 to 30 | Draft core standard, restate four quarters under it, review impact | Draft standard plus impact table |
| 31 to 45 | Regional reviews, resolve objections, finalise extensions | Approved standard |
| 46 to 60 | Build mappings in the warehouse, publish old and new side by side | Dual-run reports |
| 61 to 75 | Close-cycle review of differences, train managers, fix mapping errors | Signed-off numbers |
| 76 to 90 | Cut over to the consolidated view, retire old reports on a published date | One live consolidated view |
The plan covers 90 days in six windows.
Worked example
Region A counts attainment on bookings at contract signature, Region B on bookings at first invoice, and both have a 2,000,000 USD quarterly quota. Under the common standard (signature date), Region B's 1,900,000 USD signed becomes 1,900,000 / 2,000,000 = 95%, where its own old method gave 1,700,000 / 2,000,000 = 85%. The restatement shows B was understated, not weak, and that is how you get B's lead to support the change. Where a standard lowers a region's number, show them that result first and in private.
Trade-offs and pitfalls
- Core plus extensions beats one rigid standard when regions truly differ, but too many extensions defeat the purpose; cap them and review annually.
- Standardising the metric is not standardising the forecast method. Stage and category definitions are in scope; how each region weights deals or models its forecast is not, and mixing the two stalls the project.
- Resistance is usually about consequences (pay, performance reviews), not definitions. Address consequences head on.
- Do not cut over before a dual-run. The first full cycle always exposes mapping errors.
You have win/loss data from closed opportunities with competitor, deal size, industry, stage durations and reason codes. How would you analyze it for actionable insight, and how would you avoid being misled by small samples, rep-entered reasons and survivorship?
Sample Answer
Direct answer
Start by defining the dataset honestly, then ask a sequence of comparisons, and for every difference ask how big the uncertainty is before acting. Win/loss analysis compares closed opportunities that were won with those that were lost across competitor, deal size, industry, stage durations and reason codes, to find where the company wins and why. The three traps in the question have specific defences: small samples get confidence intervals (a range that the true rate plausibly lies within) and pooling (combining several periods to get more deals), rep-entered reasons get cross-checked against behaviour and buyer interviews, and survivorship (only seeing deals that got far enough to close) gets handled by widening the dataset to deals that never reached a decision.
Step 1: Fix the dataset
- Include no-decision outcomes (the buyer chose to do nothing) and early disqualifications as their own category; a buyer who chose to do nothing is the most common competitor and is invisible if only won and lost are kept.
- Include opportunities from the same period regardless of outcome; do not analyse only the ones with complete reason codes.
- Define the denominators: win rate = won / (won + lost + no decision), with the decided-only variant shown alongside.
Step 2: Ordered analysis
| Order | Cut | What it tells you |
|---|---|---|
| 1 | Win rate by segment (industry, size band, source) | Where you win; guards against one big segment masking others |
| 2 | Win rate against each competitor | Which rivals are actually costing you deals |
| 3 | Stage duration of won vs lost deals | Slow movement in a stage can be an early warning, but is also affected by deal size |
| 4 | Discount depth and deal size, won vs lost | Whether price is a real driver |
| 5 | Lost-deal reason codes, ranked | Hypotheses, never conclusions |
| 6 | Multi-factor view: start with a stratified table (the win rate for each competitor within each size band, so one factor is held fixed while another varies); logistic regression, a statistical model that estimates each factor's effect while holding the others fixed, is the advanced version | Whether competitor, size and industry effects overlap |
Step 6 matters because factors correlate: if one competitor appears mostly in large deals, its "lower win rate" may just be deal size.
Small samples (worked example with real arithmetic)
Suppose competitor X appears in 12 deals and the company won 3 (25%), while across the other 300 deals it won 120 (40%). The 25% looks alarming. A bare 3/12 gives one number and hides how little data stands behind it. The Wilson score interval is a standard 95% confidence interval for a proportion that behaves well on small counts: z = 1.96 is the multiplier that covers 95% of a bell-shaped spread; centre is the win rate nudged slightly toward 50%, half is how far the interval reaches either side, and denom rescales both.
from math import sqrt
def wilson(wins, n, z=1.96):
"""95% Wilson score interval for a win rate."""
p = wins / n
denom = 1 + z * z / n
centre = p + z * z / (2 * n)
half = z * sqrt(p * (1 - p) / n + z * z / (4 * n * n))
return round((centre - half) / denom, 3), round((centre + half) / denom, 3)
print("Competitor X: 3 wins in 12 deals ->", wilson(3, 12))
print("All other deals: 120 wins in 300 ->", wilson(120, 300))
Output:
Competitor X: 3 wins in 12 deals -> (0.089, 0.532)
All other deals: 120 wins in 300 -> (0.346, 0.456)
Competitor X's 25% has a plausible range of 8.9% to 53.2%, which comfortably contains 40%, so with 12 deals you cannot conclude the company does worse against X. The action is to keep watching (pool quarters), not to rebuild the playbook. A rule I would apply: do not present a segment with fewer than about 30 deals as a finding; label it a "watch item". The reason is that at 12 deals one more win moves the rate by 8 points (1/12), at 30 deals by about 3 points, so small segments swing on single deals.
Rep-entered reasons
Reps pick reasons that are quick and blame things outside their control ("price", "no budget"). Defences:
- Compare the reason with observable data: if "price" is the reason, are the lost deals discounted less than won ones? Illustration: lost deals coded "price" averaged a 6% discount (discount depth, the percent taken off list price) against 11% on won deals, so the rep did not offer much room on the deals they blamed on price. If so, price is a convenient story.
- Collect the buyer's view separately (below), and measure agreement between rep and buyer reasons.
- Reduce the free choice: a short fixed list, required at close, with a "what would have changed the outcome" text field.
Survivorship
Survivorship (seeing only what made it through to the end of a process) matters here. Closed deals are the survivors of a longer process. Deals that never became opportunities, or aged out without being closed, are missing. Include no-decision, and check how many open opportunities are older than the typical cycle: they are quietly lost.
A continuing programme (not a one-off report)
- Buyer interviews by someone who is not the account owner, ideally a neutral party, within 30 to 60 days of the decision. Interview a stratified sample (chosen deliberately from each group, not at random): wins and losses, large and small, each major competitor.
- Sampling. Aim for enough interviews per stratum to hear repeated themes, and rotate to avoid only talking to friendly buyers.
- Feed-through. A quarterly review where findings are turned into owned actions (messaging, product gaps, qualification rules), each with a metric, and follow-up the next quarter on whether the metric moved.
Pitfalls
Treating reason-code counts as causes; acting on one quarter; ignoring the no-decision bucket; and interviewing only buyers who agreed to talk, who are not representative.
Unlock Full Question Bank
Get access to all Sales & Revenue Performance Analytics interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.