Marketing funnel analysis: find where prospects stop

Marketing funnel analysis shows where a defined group of prospects stops between first recorded contact and qualified lead. Compare stage counts by channel using the same entry period, qualification rule, and follow-up cutoff. A larger loss gives the team a place to investigate, while the underlying records show whether the issue is audience fit, follow-up, or missing data.

Start with the export and a measurable entry point

An awareness report may contain impressions, anonymous visits, and identifiable leads. Those are different counting units. Adding them into one funnel produces percentages that do not describe the progression of any consistent group.

For this analysis, use each prospect's first tracked landing visit as the observable awareness entry point. It is a proxy for recorded awareness, not a measure of everyone who encountered the brand. Include only prospects whose supplied identifiers let you connect the stages, and disclose that coverage limit beside the results.

Export campaign membership and lead history. Prepare a sheet named Prospects with one row per prospect and these columns:

The marketing operations owner should document the engagement and qualification rules with sales. A downloaded guide may qualify as engagement; it need not qualify as a sales-ready lead. Check that dates follow the defined sequence, and leave a missing stage blank rather than assigning a date.

This is a closed sequence: later stages must belong to entrants who completed the earlier stages. Google Analytics describes the distinction between open and closed funnels: a closed funnel requires entry at the first step. Your export analysis must make its own sequence rule equally explicit.

Build a comparable cohort

Consider a US business-services company tracking prospects who entered from September 1 through September 30, 2026. The report observes progression through October 15, 2026. Every channel uses those same dates; this compares a common entry period and cutoff, not an identical number of days for every prospect.

Filter the prepared file to that entry cohort before calculating the summary. Resolve duplicate IDs, channel blanks, and out-of-sequence dates first. The clean cohort occupies rows 2 through 3601. Do not include older leads that happened to qualify during September.

For comparisons requiring identical prospect age, use a fixed number of days after each entry date and wait for the cohort to mature. Cohort analysis in marketing covers that choice in more detail. Keep the choice consistent across the channels under review.

Count stages in Google Sheets

Create a summary with channel names in A2:A4. Count entrants in B2:

=COUNTIF(Prospects!B$2:B$3601,$A2)

Count engagement dates in C2, including dates through October 15:

=COUNTIFS(Prospects!B$2:B$3601,$A2,Prospects!D$2:D$3601,">0",Prospects!D$2:D$3601,"<"&DATE(2026,10,16))

For lead and qualified counts, use the same formula with the date range changed to column E and column F respectively. Fill the summary down for the other channels. Google's COUNTIFS documentation confirms that the function counts rows meeting multiple criteria; the date and channel tests must all apply to the same prepared rows.

Here are the resulting counts. The channel groups are mutually exclusive, so their totals can be added.

Calculate conversion and prove the losses

Stage conversion = Prospects reaching the next stage / Prospects reaching the preceding stage

Entry-to-qualified conversion = Qualified leads / Entrants

With the summary above in columns A:E, use these formulas and format the results as percentages:

=IF(B2=0,"",C2/B2)
=IF(C2=0,"",D2/C2)
=IF(D2=0,"",E2/D2)
=IF(B2=0,"",E2/B2)

Rates are rounded to one decimal place after calculation. The total uses summed counts: 201 / 3,600 = 5.6%. Do not average the displayed channel percentages.

Prove the total by separating the entrants into their furthest completed stage:

The identity must hold for every channel as well. Also tie the 438 lead IDs to the filtered lead export and the 201 qualified IDs to acceptance records. Equal totals alone do not prove that the right people were matched.

Turn the result into an investigation

Organic has the lowest entry-to-qualified rate, but it also supplies the largest entrant group. Review its engagement records and lead rejection reasons before changing spend. Partner conversion is stronger in this cohort; referral selection and audience intent may explain part of that difference.

Conversion does not contain cost. Use customer acquisition cost when connecting this report to acquisition economics. For a proposed change, marketing scenario analysis separates assumptions from observed results, while statistical analysis in marketing addresses uncertainty in the comparison.

Once a qualified opportunity exists, continue with sales funnel analysis. A shopping journey with carts and purchases belongs in ecommerce funnel analysis, which uses different stages and counting units.

Where it goes wrong

• Impressions treated as people. Exposure counts cannot prove person-level progression.
• Changing channel assignment. Moving a prospect between channels after qualification distorts the original cohort.
• Recent cohorts compared with mature ones. Different observation time can explain a conversion gap.
• Missing history treated as failure. An unrecorded engagement date is a data-quality question until checked.
• Channel differences treated as causal effects. Audience selection and follow-up differ; this report does not prove a proposed change will improve results.

Covirage is not affiliated with Google.

Analyze your marketing export in Covirage

Bring a sample of your own campaign and lead exports with identifiers, stage dates, and channel definitions. Covirage maps the columns; its tools compute and check the stage counts and comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.

See Covirage for sales insights for the supporting workflow.