Why this matters
Conversion funnel forecasting lets you predict how many users will move from awareness to purchase (or sign-up) and what revenue or pipeline they create. As a Marketing Analyst, you will:
- Estimate monthly sales from traffic and conversion rates.
- Size the impact of campaigns, channel shifts, or budget changes.
- Forecast pipeline for sales teams with realistic time lags.
- Run scenarios (best/base/worst) to support planning.
- Spot bottlenecks by stage and prioritize optimization.
Concept explained simply
A funnel is a sequence of stages. At each stage, a percentage of people continue. Forecasting multiplies the volume entering a stage by that stage's conversion rate to estimate how many advance. Repeat across stages to get the final outcome (purchases, sign-ups, deals).
Mental model
Imagine a leaky pipe. Water in equals site visits or leads. Each joint leaks (conversion loss). Tighten any joint (improve a stage), and more water reaches the end (purchases or won deals). If you pour in more water (traffic), more comes out, assuming the pipe can handle the flow (no capacity constraints).
Core formulas
- Stage output = Stage input × Stage conversion rate
- Cumulative conversion rate (start→end) = Product of all stage rates
- Revenue = Purchases (or Won deals) × Average order value (AOV) or ACV
- Traffic needed for target outcome = Target ÷ Cumulative conversion rate
Note on timing and cohorts
Some funnels have delays (e.g., trial-to-paid in 14–30 days, sales cycles in weeks or months). Apply stage outputs to later periods to reflect reality. For recurring revenue, apply retention by cohort over time.
Data you need
- Stage definitions (e.g., sessions → product view → add to cart → checkout → purchase).
- Historical conversion rates by stage (median or recent average).
- Top-of-funnel volume (traffic, impressions, or leads) and its expected change.
- Value metrics: AOV, ACV, price plans, expected discounts.
- Timing: sales cycle length, trial length, time-to-purchase, retention rates.
- Constraints and seasonality: capacity limits, promo periods, holidays.
Build a simple funnel forecast (step-by-step)
- List stages: Define clear start and end events. Keep stages mutually exclusive and collectively exhaustive.
- Pull rates: Use recent, stable conversion rates for each stage. If volatile, use medians or a smoothed average.
- Set inputs: Enter expected top-of-funnel volumes (traffic, impressions) and any planned changes.
- Apply timing: If stages complete in later periods, shift outputs accordingly (e.g., Month t leads → Month t+1 wins).
- Calculate: Multiply stage-by-stage to the end. Compute value (revenue or MRR) at the end.
- Scenario & sensitivity: Create base/best/worst with different rates or traffic; test which stage moves the outcome most.
- Check limits: Validate against capacity (inventory, sales seats) and seasonality.
Worked examples
Example 1 — Ecommerce purchases and revenue
- Stages: Sessions → Product view → Add to cart → Checkout start → Purchase
- Inputs: 100,000 sessions; PV 50%; ATC 10% of PV; Checkout start 60% of ATC; Purchase 80% of checkout; AOV $45
Calculations:
- Product views: 100,000 × 0.50 = 50,000
- Add to cart: 50,000 × 0.10 = 5,000
- Checkout start: 5,000 × 0.60 = 3,000
- Purchases: 3,000 × 0.80 = 2,400
- Revenue: 2,400 × $45 = $108,000
Sensitivity: +10% sessions → +10% purchases if rates hold (capacity permitting).
Example 2 — B2B lead-gen to revenue (with 2-month sales cycle)
- Stages: Impressions → Clicks → Leads → MQL → SQL → Won deal
- Inputs: 500,000 impressions; CTR 2%; LP CR 4%; MQL 40%; SQL 50%; Win 20%; ACV $8,000; Sales cycle ~ 2 months
Calculations:
- Clicks: 500,000 × 0.02 = 10,000
- Leads: 10,000 × 0.04 = 400
- MQL: 400 × 0.40 = 160
- SQL: 160 × 0.50 = 80
- Wins: 80 × 0.20 = 16 (realized ~2 months later)
- Revenue (Month t+2): 16 × $8,000 = $128,000
Example 3 — Subscription with trial and early retention
- Stages: Visits → Sign-up → Trial start → Trial-to-paid → Month-1 retained
- Inputs: 50,000 visits; Sign-up 6%; Trial start 80%; Trial→Paid 25%; Month-1 retention 85%; Price $20
Calculations:
- Sign-ups: 50,000 × 0.06 = 3,000
- Trial starts: 3,000 × 0.80 = 2,400
- Paid: 2,400 × 0.25 = 600
- Month-1 retained: 600 × 0.85 = 510
- MRR after first month: 510 × $20 = $10,200
Timing: If trial lasts 14 days, paid conversions may land in the next month depending on start date distribution.
Quality checks and common mistakes
Quality checks
- Do totals make sense vs. history? Compare forecasted cumulative conversion to recent ranges.
- Are rates within plausible bounds (e.g., not exceeding 100%)?
- Is timing applied? Sales cycle/trial delays should shift outputs forward.
- Capacity fit: forecasted orders or wins shouldn't exceed inventory or sales bandwidth.
- Seasonality: apply uplift/dip where relevant and revert to base afterward.
Common mistakes (and fixes)
- Double-counting stages: Ensure each user passes once through each stage definition.
- Mixing absolute and relative changes: A 10% relative improvement to 30% = 33%, not 40%.
- Rounding too early: Keep decimals through stages; round at the end.
- Ignoring time lags: Map wins or paid conversions to later months when cycles are not instant.
- Assuming linear scaling under constraints: Check limits like stock, checkout throughput, sales capacity.
Exercises
Practice these to get comfortable. Keep calculations exact; round results at the end.
Exercise 1 — Ecommerce funnel forecast
Inputs: 90,000 sessions; PV 55%; ATC 12% (of PV); Checkout start 60% (of ATC); Purchase 70% (of checkout); AOV $75.
- Task A: Estimate purchases and revenue.
- Task B: Recalculate with +10% sessions, same rates.
Exercise 2 — B2B pipeline next-month revenue
Inputs: 600,000 impressions; CTR 1.5%; LP CR 3%; MQL 35%; SQL 50%; Win 25%; ACV $12,000; sales cycle 1 month.
- Task: Estimate next-month wins and revenue. Do not round until the end.
Exercise checklist
- Stages clearly defined and in order.
- Rates applied to the correct base (e.g., ATC is % of PV).
- No early rounding; round at the end.
- Timing offsets applied where needed.
- Scenario noted (base vs. sensitivity).
Practical projects
- Build a 3-scenario funnel model (base/best/worst) for your site or a sample store; include a traffic input cell and stage rates.
- Add a timing sheet: map leads in Month t to wins in Month t+1 or t+2; compare to historical.
- Create a sensitivity table: identify which stage rate drives the biggest change in outcomes.
- Backtest: use last quarter inputs to predict outcomes, compare to actuals, and document variance drivers.
Who this is for and prerequisites
- Who: Marketing Analysts, Growth Marketers, and anyone building performance forecasts.
- Prerequisites: Comfort with percentages, basic spreadsheet formulas (multiplication, SUMPRODUCT), and understanding of your product's funnel stages.
Learning path
- Before: Basic analytics metrics (sessions, CTR, CR, AOV/ACV).
- Now: Conversion Funnel Forecasting (this lesson).
- Next: Seasonality and trend adjustments; Experiment impact sizing; Capacity-aware forecasting.
Next steps
- Turn one worked example into your own spreadsheet template.
- Add a separate tab for assumptions and a tab for checks (rates in range, capacity OK).
- Schedule a monthly forecast vs. actual review and capture learnings.
Mini challenge
Your ecommerce funnel is stable, but checkout completion drops from 80% to 68% during a promo weekend while traffic doubles and AOV falls 10%. Estimate the net effect on revenue directionally and list two hypotheses for the drop. Keep reasoning crisp and quantify the biggest driver.
Quick Test
This quick test is available to everyone; only logged-in users get saved progress.