Blog · Board and management reporting
Conversion funnel optimization starts with comparable stage counts by source and cohort. Work through an export, calculate the losses, reconcile the totals, and decide which differences deserve investigation.
Conversion funnel optimization starts by comparing stage loss for groups with the same entry rules and time to convert. Calculate the differences from an export, investigate the records behind them, and prioritize a specific question for the process owner. A lower rate identifies where to look; it does not establish the cause or the return from a change.
Define one counting unit, such as a unique inquiry, and one ordered path: inquiry, qualified opportunity, proposal, won. Keep the original acquisition source and the month the inquiry entered. An inquiry can contribute once to each stage it reaches in order.
Give each inquiry the same observation window. In the example below, every entry receives thirty days from its entry date, and all windows have ended before the export is taken. A later win belongs in a separate longer-window measure. Do not compare a recent cohort with an older one that has had more opportunity to progress.
The denominator definitions belong in the report header. For the distinction between an entered cohort and completed decisions, use win rate, close rate, and conversion rate. This workflow uses stage entrants, including records still unresolved when their window closes.
A US business services company exports inquiry IDs, entry dates, original sources, and dated stage entries. It produces the following summary after removing duplicate IDs and checking stage order. Counts refer to unique inquiries, not the number of stage-change events.
Put cohort in column A, source in B, inquiries in C, qualified in D, proposals in E, and wins in F. The table starts at row 2.
| Cohort | Source | Inquiries | Qualified | Proposals | Won |
|---|---|---|---|---|---|
| June | Organic | 200 | 100 | 50 | 20 |
| June | Paid | 200 | 80 | 40 | 12 |
| July | Organic | 200 | 120 | 60 | 24 |
| July | Paid | 200 | 60 | 30 | 9 |
| Total | 800 | 360 | 180 | 65 |
If your file already contains non-overlapping source and cohort subtotals, SUMIFS can assemble the comparison. Microsoft documents SUMIFS as summing cells that meet multiple criteria. For July paid inquiries and qualified records:
=SUMIFS(C2:C5,A2:A5,"July",B2:B5,"Paid")
=SUMIFS(D2:D5,A2:A5,"July",B2:B5,"Paid")
These return 200 and 60. Never sum a source subtotal together with its underlying records. That would count the same inquiries twice.
If the supplied file has one row per inquiry instead, count rows meeting the cohort, source, and eligible-stage flags. Microsoft's COUNTIFS documentation explains that all specified criteria must be true. Deduplicate first; the function does not make identifiers unique.
Use the prior stage as the denominator and show the count that did not reach the next stage within the window.
Stage conversion = Next-stage entrants / Prior-stage entrants
Non-progression count = Prior-stage entrants − Next-stage entrants
For the first data row, inquiry-to-qualified conversion in G2 and non-progression in H2 are:
=IF(C2=0,"",D2/C2)
=C2-D2
Proposal conversion is =IF(D2=0,"",E2/D2), and proposal-to-win conversion is =IF(E2=0,"",F2/E2). Fill down and format rates as percentages. The following figures use one decimal place; calculations retain the underlying precision.
| Cohort and source | Inquiry to qualified | Qualified to proposal | Proposal to won | Inquiries not qualified |
|---|---|---|---|---|
| June organic | 50.0% | 50.0% | 40.0% | 100 |
| June paid | 40.0% | 50.0% | 30.0% | 120 |
| July organic | 60.0% | 50.0% | 40.0% | 80 |
| July paid | 30.0% | 50.0% | 30.0% | 140 |
The paid qualification rate fell from 40.0% to 30.0%, while its later conditional rates were unchanged. That makes qualification the first question for the paid-source owner. Organic qualification improved over the same cohorts. Combining the sources would hide both movements: each cohort qualified 180 of 400 inquiries, or 45.0%.
For shopping stages rather than inquiries and opportunities, use the separate ecommerce conversion funnel workflow.
The stage totals must decrease along this ordered path, and each difference must tie back to the prior count.
| Check | Calculation | Result |
|---|---|---|
| Inquiries not qualified | 800 − 360 | 440 |
| Qualified without proposal | 360 − 180 | 180 |
| Proposals without a win | 180 − 65 | 115 |
| Full reconciliation | 440 + 180 + 115 + 65 | 800 |
The final equality accounts for every inquiry exactly once by its furthest reached stage. It does not mean every non-progressing record was rejected. Some remain open at the end of the window. Add an outcome flag if the business needs to distinguish rejection, withdrawal, and unresolved records.
Also check the total rates from the summed counts. Inquiry-to-win conversion is =SUM(F2:F5)/SUM(C2:C5), or 65 / 800 = 8.1% rounded. Averaging segment percentages would depend on segment size and can mislead when those sizes differ.
At June's paid qualification rate, July's 200 paid inquiries would correspond arithmetically to 80 qualified inquiries. The observed count is 60, a difference of 20. That is a comparison under an unchanged-rate assumption, not twenty recoverable sales or evidence of a campaign effect.
Inspect disqualification reasons, campaign audience, handoff timing, and missing qualification dates. Ask the owner whether the definition changed. Review a consistent selection of both successful and unsuccessful records rather than reading only the losses.
Record the hypothesis, responsible person, proposed change, measurement window, and decision rule. Where appropriate, use a controlled test to separate the change from other influences. Rebuild the same table afterward. The funnel dashboard covers the recurring display and its reconciliation; the decision here is which process question deserves attention first.
Covirage is not affiliated with Microsoft.
Bring your inquiry export with source, cohort, and supplied stage dates or prepared stage counts. Covirage maps the columns, and its tools compute and check the comparisons; 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 insights for the supporting workflow.
Start with a stage whose loss affects a meaningful number of eligible records and has worsened within a comparable source and cohort. Check the underlying records and business impact before assigning an action. The lowest percentage alone does not establish the best opportunity.
Choose a window that fits the decision and the normal time between stages, then apply it equally to every cohort member. Keep later outcomes separate. If recent entrants have not completed the window, label them incomplete instead of comparing them with mature cohorts.
No. Audience mix, seasonality, qualification rules, or recording changes can move conversion without any effect from the proposed action. Use the comparison to form a hypothesis, then evaluate a controlled test or another explicitly justified method before claiming an effect.