Blog · Board and management reporting
Improve sales conversion rate by finding where comparable sales cohorts lose momentum. Use CRM exports, spreadsheet formulas, source comparisons, and aging checks to choose a measurable follow-up change.
To improve sales conversion rate, find the stage and cohort where comparable opportunities stop progressing, then test a change aimed at that cause. Start with a dated CRM export, keep the observation window fixed, and separate changes in buyer mix from changes in selling performance.
Choose a business question before calculating. Here it is: what share of newly created opportunities becomes closed won within a fixed follow-up window? That differs from wins divided by closed decisions; the conversion, close-rate, and win-rate definitions explain those denominators.
Write down which opportunities qualify, when the clock starts, and which outcome ends it. Keep renewals separate from new business if their selling processes differ. Exclude test records and duplicate opportunities using rules chosen before looking at results.
Use stage entry dates rather than today's stage alone. For example, HubSpot's lifecycle documentation distinguishes stage entry dates from the current lifecycle value. Your export needs enough history to reconstruct the events being measured.
Use one deduplicated row per opportunity on a sheet named Deals:
| Column | Export field |
|---|---|
| A | Opportunity ID |
| B | Created date |
| C | Original source |
| D | First qualified date |
| E | First proposal date |
| F | First terminal outcome date |
| G | First terminal outcome: Won, Lost, or blank |
| H | Current stage at export; terminal labels standardized to Won or Lost |
| I | Date entered current stage |
The example uses a required qualified-to-proposal-to-win path. Review reopened opportunities separately rather than confusing a later outcome with the first decision.
On Settings, set B1 to 45, the follow-up days, and B2 to =DATE(2026,5,31), the export date. Dates must be spreadsheet dates. In K2, flag opportunities with a complete observation window:
=--AND(ISNUMBER(B2),Settings!$B$2>=B2+Settings!$B$1)
L2 flags a win inside that window:
=--AND(K2=1,G2="Won",ISNUMBER(F2),F2>=B2,F2<=B2+Settings!$B$1)
M2 flags a loss inside the window; N2 and O2 flag the earlier stages:
=--AND(K2=1,G2="Lost",ISNUMBER(F2),F2>=B2,F2<=B2+Settings!$B$1)
=--AND(K2=1,ISNUMBER(D2),D2>=B2,D2<=B2+Settings!$B$1)
=--AND(K2=1,ISNUMBER(E2),E2>=B2,E2<=B2+Settings!$B$1)
Fill the formulas down. On a summary sheet, put the cohort start in B1, the next month's start in C1, and a source name in A4. Count eligible starts in B4:
=COUNTIFS(Deals!$B$2:$B$1000,">="&$B$1,Deals!$B$2:$B$1000,"<"&$C$1,Deals!$C$2:$C$1000,$A4,Deals!$K$2:$K$1000,1)
Count timely wins in C4, then calculate conversion in D4:
=SUMIFS(Deals!$L$2:$L$1000,Deals!$B$2:$B$1000,">="&$B$1,Deals!$B$2:$B$1000,"<"&$C$1,Deals!$C$2:$C$1000,$A4)
=IF(B4=0,"",C4/B4)
Microsoft's COUNTIFS documentation confirms that all specified criteria must match and the criteria ranges must have the same dimensions. Covirage is not affiliated with HubSpot or Microsoft.
Consider an illustrative US business-services company. The table summarizes its January and February opportunity cohorts at the same age. Every count is inside the follow-up window; open means no terminal outcome by that deadline.
| Cohort | Source | Starts | Qualified | Proposal | Won | Lost | Open | Won / starts |
|---|---|---|---|---|---|---|---|---|
| January | Referral | 40 | 28 | 20 | 12 | 20 | 8 | 30.0% |
| January | Paid search | 60 | 30 | 18 | 9 | 36 | 15 | 15.0% |
| January total | 100 | 58 | 38 | 21 | 56 | 23 | 21.0% | |
| February | Referral | 20 | 14 | 10 | 6 | 10 | 4 | 30.0% |
| February | Paid search | 80 | 40 | 24 | 12 | 48 | 20 | 15.0% |
| February total | 100 | 54 | 34 | 18 | 58 | 24 | 18.0% |
Rates are rounded to one decimal place. The total fell by 3.0 percentage points, yet each source retained its conversion rate. February simply contained fewer referrals.
Hold January's source counts fixed and apply February's source rates: (40 × 6/20 + 60 × 12/80) / 100 = 21.0%. That comparison removes the source-mix change. The same principle underlies win rate by segment, source, and deal size.
The reconciliation is equally useful: January's 21 + 56 + 23 = 100; February's 18 + 58 + 24 = 100. Missing outcomes must stay visible. Stage counts are cumulative milestones and must never be added together.
Within paid search, inspect the underlying opportunities that did not reach qualification, then those that reached a proposal without winning. A stage conversion rate locates the transition; loss reasons and dated activity explain what to investigate.
Use current aging to choose today's work, keeping it separate from the historical cohort outcome. For open opportunities, P2 returns days in the current stage:
=IF(OR(H2="Won",H2="Lost",I2=""),"",Settings!$B$2-I2)
Check that current-stage dates do not exceed the export date. Review the oldest relevant opportunities for missing buyer commitments, unanswered questions, and overdue next steps. Agree on a specific intervention, such as confirming the buyer's decision process before sending a proposal.
Keep a comparable group on the existing approach where practical. Measure both groups after equal follow-up, retaining lost and unresolved opportunities. If the bottleneck precedes opportunity creation, use the separate meeting-to-opportunity comparison.
Bring your dated CRM export or Google Sheet, including opportunity IDs, sources, stage dates, and outcomes. Covirage maps the columns; its tools compute and check the cohort comparisons, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See sales insights for the supporting sales reporting context.
Check whether the starting population, observation window, and source mix changed. Then compare conversion within the same source and deal segment. An overall decline can happen even when every source converts at its previous rate.
Yes, for a cohort measure with a fixed follow-up window. Open deals remain eligible starts but are not wins within that window. Report their count separately so managers can distinguish unresolved pipeline from confirmed losses.
No. Faster follow-up is a testable process change, and its effect depends on the buyers and the reason deals stalled. Compare a changed group with a comparable unchanged group, and check both conversion and opportunity quality.
Revenue operations should maintain the population, stage definitions, and reconciliation. Sales managers should review the underlying opportunities and own the process change. Marketing should join when source quality or qualification criteria explain the difference.