Sales funnel analysis: diagnose conversion and deal loss

Sales funnel analysis compares how qualified opportunities progress, how much deal value converts, and where open deals are aging. Start with a CRM export whose dates and amounts have clear definitions, then separate won, lost, and still-open opportunities. Team comparisons become useful only when their cohorts and deal mix are understood.

Begin where qualification ends

The sales funnel here starts when an opportunity meets the company's qualification criteria. Marketing funnel analysis covers the earlier movement from audience to qualified lead. Keeping that boundary explicit prevents a lead-generation change from appearing to be a change in sales execution.

Ask for one opportunity ID, qualification date, team at qualification, dated stage entries, outcome, close date, and amount. Preserve historical ownership for this analysis; otherwise a reassignment can move old performance between teams. The guide to preparing a sales export covers identifier, date, and control-total checks.

Define the amount field. This example uses opportunity value recorded at qualification, in USD, and keeps that amount fixed through the reconciliation. It measures the initial value attached to opportunities that later win, rather than accounting revenue or a current forecast. If values change, keep those changes in a separate bridge.

Work one qualified cohort through the export

A US equipment service company qualified these opportunities on September 1, 2026. The cutoff is September 30, 2026. Every opportunity has had the same twenty-nine days to progress, and no deal has reopened.

Use A for ID, B for team, C for qualification date, D for proposal date, E for outcome, F for qualification value, and G for outcome date. A dash represents a blank spreadsheet cell.

Check that each ID appears once in this prepared cohort table. The proposal entrants are E01, E02, E03, and W01; W02 closed lost without a proposal. Do not assign a proposal date to that deal just to make the stages look orderly.

Calculate conversion with named denominators

Microsoft's COUNTIFS documentation describes counting rows when all specified conditions match. With East in J2, put qualified opportunities in K2, proposal entrants in L2, wins in M2, and losses in N2 using these formulas:

=COUNTIFS($B$2:$B$7,J2,$A$2:$A$7,"<>")
=COUNTIFS($B$2:$B$7,J2,$D$2:$D$7,">0")
=COUNTIFS($B$2:$B$7,J2,$E$2:$E$7,"Won")
=COUNTIFS($B$2:$B$7,J2,$E$2:$E$7,"Lost")

The table is already restricted to the qualification cohort and cutoff. Apply the same restrictions to a larger export before using these formulas. Round the displayed percentages to one decimal place.

For each summary row, calculate open opportunities in O2, proposal conversion in P2, and cohort win conversion in Q2:

=K2-M2-N2
=IF(K2=0,"",L2/K2)
=IF(K2=0,"",M2/K2)

Fill down for West. The rate formulas display blank when there are no eligible records, rather than a fabricated zero.

Closed-deal win rate is 2 / (2 + 2) = 50.0%. Cohort conversion is 2 / 6 = 33.3%. The difference is the two unresolved opportunities. For the spreadsheet method behind these measures, see win rate and pipeline coverage in Excel.

Reconcile count and deal value

The count check is simple: six qualified equals two won plus two lost plus two open. Now apply the same mutually exclusive statuses to the fixed qualification amounts.

Microsoft documents SUMIFS as adding cells that meet multiple criteria. East's won qualification value is:

=SUMIFS($F$2:$F$7,$B$2:$B$7,J2,$E$2:$E$7,"Won")

Change the status criterion to Lost or Open for the other columns. Sum all three statuses for the team's original qualified value.

The value check is 90,000 + 110,000 + 300,000 = 500,000. The total value conversion is 90,000 / 500,000 = 18.0%, and average won opportunity value is 90,000 / 2 = $45,000.

Both teams won the same share of qualified opportunities, yet their value conversion differs. West's large open opportunity contributes heavily to its denominator. That is a reason to inspect the open deal and its fit, not evidence that the team needs a different commission plan.

Inspect aging and assign the next question

Put the cutoff date in S1. For an open opportunity, current-stage age starts at the recorded proposal date if it exists, otherwise the qualification date:

=IF(E2<>"Open","",$S$1-IF(D2="",C2,D2))

This works for the simple two-stage path shown. A richer sales process needs the actual latest valid entry to the current stage, including reentries; do not use the first-ever proposal date as current-stage age after a return.

The East manager should review the proposal's next decision. The West manager should investigate why the larger opportunity has not reached a proposal and whether its qualification remains valid. Compare with the company's own similar deals and review policy; neither age is automatically overdue without that context.

A recurring funnel dashboard can keep these counts and timing checks visible. The sales manager still owns the follow-up decision and the explanation recorded against each opportunity.

Where it goes wrong

• Different cohort maturity. A newly qualified team cohort has had less opportunity to close.
• Current owner substituted for historical owner. Transfers can rewrite the apparent origin of performance.
• Amounts changing inside a fixed-value reconciliation. Track revisions separately or the status totals will no longer explain the original cohort.
• Open deals treated as losses. That understates unresolved business and changes the meaning of conversion.
• Small team differences treated as causes. Deal size, product, territory, and customer mix need review before assigning a performance explanation.

Covirage is not affiliated with Microsoft.

Do this on your own file in Covirage

Bring your opportunity export with qualification cohorts, team ownership, amounts, stage dates, and outcomes. Covirage maps the columns, and its tools compute and check conversion, value reconciliations, and stage aging; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See Covirage for sales teams for the supporting workflow.