Chapter 11 · Stage 04
MQL Forecasting: The Spreadsheet
A forecast only holds if it runs on your own data. Build the spreadsheet from your GSC and DataForSEO inputs and recalibrate it every quarter.
- Pull the inputs from GSC and DataForSEO, not from generic benchmarks
- Calibrate the CTR curve against your actual click-through rates by position
- Set maturity ramps by content type, not one flat curve for everything
- Recalibrate quarterly by comparing forecast to actuals and adjusting the assumptions that drifted
This page is about building the thing. How to pull the data, set the assumptions, wire the calculations, and keep the model accurate over time.
Other perspective Forecasting Pipeline What the forecast tells you and what to do with it: the business case, a worked example, and which segment to build first.The six layers, operationally
Section titled “The six layers, operationally”You already know the chain. What follows is how to fill in each layer with real numbers instead of estimates.
Layer 1: Search demand inputs
Section titled “Layer 1: Search demand inputs”Start with your keyword map for the cluster you’re modeling. Each page has a primary keyword. You need monthly search volume for each one.
Where to pull volume data:
- DataForSEO keyword data API gives you raw Google Ads volume at the location and language level. This is the same data Google Keyword Planner shows, without the rounding and ranges. Pull volume for every primary keyword in your cluster, filtered to your target country.
- GSC Performance report gives you impression data for queries you already rank for. This is useful for existing pages being optimized, less useful for new pages targeting keywords you don’t yet rank for.
For new pages, use DataForSEO volume. For existing pages being optimized, use GSC impressions as a cross-check: if DataForSEO says 480 monthly searches but GSC shows 600 impressions for that query, your actual demand is higher than the keyword tool suggests.
Seasonality adjustment. Pull 12 months of DataForSEO volume history (or GSC’s 16 months) and calculate a monthly index. A keyword with 480 average volume might be 600 in Q1 and 350 in Q3. The forecast should reflect this, especially in the first 6 months when the maturity curve and seasonal patterns interact.
If you have less than 12 months of keyword data, build the index from 24 to 36 months of GA4 organic sessions for the whole site instead. It’s a coarser shape than a per-keyword index, but it’s a shape rather than none. Check it before you use it: if the history includes a one-off campaign or a tracking outage, trim that window out. Flat, static volume is the last resort, not the first fallback.
Secondary keyword multiplier. A well-built page ranks for more than its primary keyword. The multiplier varies by funnel stage:
| Funnel Stage | Typical multiplier | Why |
|---|---|---|
| TOFU | 2.0x to 3.0x | Broad informational queries attract many related terms |
| MOFU | 1.3x to 1.8x | Problem-oriented queries have moderate variation |
| BOFU | 1.0x to 1.3x | Commercial queries are tightly focused |
Calibrate these against your own GSC data. For existing pages, divide total organic clicks by the clicks from the primary keyword alone. That ratio is your actual secondary multiplier for that page type.
Layer 2: CTR curve calibration
Section titled “Layer 2: CTR curve calibration”Generic CTR curves (the “position 1 gets 28%, position 2 gets 15%” tables from industry studies) are averages across all query types, all industries, all SERP layouts. They’re a starting point. Your actual CTR curve is what matters.
Building your CTR curve from GSC:
- Export GSC data for the last 90 days: query, page, clicks, impressions, position
- Remove branded queries first. Branded clicks sit far above non-brand clicks at the same position, so a mixed curve inflates the non-brand baseline the whole forecast runs on, and most for the companies with the strongest brands.
- Round positions to integers (position 3.4 becomes position 3)
- Group by position, calculate average CTR per position
- You need at least 100 impressions per position bucket for the number to be meaningful
This gives you a CTR curve calibrated to your domain, your SERP features, and your title tag quality. It will differ from published benchmarks, sometimes significantly.
SERP feature discounts. For keywords where AI Overviews, featured snippets, or heavy ad placement appear above organic results, apply a CTR discount. I typically use:
- AI Overview present: 20% to 40% CTR reduction depending on query type
- Featured snippet (not yours): 15% to 25% reduction
- 4+ ads above organic: 10% to 20% reduction
Check the actual SERPs for your target keywords. DataForSEO SERP API can tell you which features are present for each keyword at scale, so you don’t have to check manually.
Per-page CTR override. Some pages will have CTR characteristics that differ from your curve. A page with a particularly compelling title tag might outperform its position. A page competing against a strong brand in position 1 might underperform. Allow per-page overrides in your spreadsheet, but default to the calibrated curve.
Layer 3: Sessions calculation
Section titled “Layer 3: Sessions calculation”This is arithmetic. For each page:
Projected sessions = Volume x Secondary multiplier x CTR at target position
The target position is your informed estimate of where the page will rank at steady state. “Informed” means: you’ve looked at the competing pages, assessed their authority (DR/UR, backlink profiles), evaluated content quality, and made a judgment about where your page can realistically land.
Pages that already exist: forecast only the extra traffic. Take today’s sessions, add the gap between today and the target position, and apply the win rate (layer 4) to that gap only. Discounting the clicks a page already gets would forecast it below what it delivers now.
Do not target position 1 for every page. If the top 3 results are DR 80+ domains with thousands of backlinks, targeting position 6 to 8 is honest forecasting. Position 4 in a less competitive SERP produces more MQLs than position 8 in a crowded one.
Layer 4: Odds (win rate × maturity ramp)
Section titled “Layer 4: Odds (win rate × maturity ramp)”The target position is a planning target, not a promise. Most models quietly assume every page reaches it. It doesn’t. The win rate says whether a page gets there; the maturity ramp says when. This layer is adapted from Dagan Price’s talk “The SEO ROI Formula” at BrightonSEO, October 2026.
Measure the win rate from your own Search Console:
- Export non-brand queries for the last three months and for the same three months a year earlier. Same months both years, so seasonality cancels out.
- Attempts: queries that sat at position 5 to 10 a year ago, with at least 50 impressions in that window. Fewer impressions and the position is noise.
- Wins: attempts that now sit at the target position your forecast uses (top 4 if the target is 4). The win line always equals the target line.
- Losses include queries that vanished. An attempt with no impressions now counts as a loss. Dropping it flatters the rate.
- Win rate = wins ÷ attempts.
You need at least 30 attempts. Below that, use 25% and label it “default, not measured” in the forecast.
New pages: the measured rate comes from existing rankings, so it overstates pages that start from nothing. If you have the record, use your own: of the pages you published 6 to 18 months ago, the share now at the target position. Without that record, use the conservative scenario’s rate for new pages.
The maturity ramp spreads the year over months. New pages:
| Month after publish | Share of steady-state traffic |
|---|---|
| 1 | 5% |
| 2 | 10% |
| 3 | 25% |
| 4 | 45% |
| 5 | 70% |
| 6 onwards | 100% |
Existing pages you optimize ramp faster, because the URL already has history, links and indexation: 25% in month 1, 60% in month 2, 100% from month 3.
Win rate and ramp don’t overlap. The win rate is measured over twelve months, so it answers “does the page reach the target within a year”. The ramp answers “in which months of that year”. Never apply a second ramp average on top.
The ramp is not passive aging. It’s the internal linking, routing and optimization work that lifts a page over those months. After your first cluster matures, replace these numbers with your own: pull GSC clicks by month for each page since publication and plot the curve.
Layer 5: Conversion rates by funnel stage
Section titled “Layer 5: Conversion rates by funnel stage”You need MQL session rates: what percentage of organic sessions on a given content type become marketing-qualified leads.
If you have CRM attribution data: Pull lead records tagged as organic source, segment by the page that drove the conversion (or the landing page of the session), group by funnel stage, and calculate the rate. This is your ground truth.
If you don’t have per-page attribution: Use aggregate organic conversion rate from your analytics, then weight by funnel stage. BOFU content converts at 3x to 5x the rate of TOFU content. A reasonable starting split if your overall organic MQL rate is 3%:
| Funnel stage | Estimated MQL rate |
|---|---|
| BOFU | 6% to 8% |
| MOFU | 3% to 5% |
| TOFU | 1% to 2% |
These are the assumptions that matter most. A 1% shift in BOFU conversion rate changes the forecast more than a 20% shift in TOFU search volume. Flag these prominently in your spreadsheet and mark them for quarterly recalibration.
Layer 6: Scenario ranges
Section titled “Layer 6: Scenario ranges”The three scenarios are three win rates, plus a conversion multiplier:
| Scenario | Win rate | Conversion | When to use |
|---|---|---|---|
| Conservative | measured rate minus 20 points (never below 10%) | 0.8x | Lead with this in CFO conversations |
| Baseline | your measured rate | 1.0x | Your own track record: the most likely outcome |
| Optimistic | baseline plus 20 points (never above 80%) | 1.2x | Upside planning, not commitments |
Don’t also lower the target position or the CTR in the conservative case. The win rate already measures that uncertainty; adding both counts it twice.
Once you have pages you’ve worked on for a while, split the rate: the site-wide rate as conservative (it’s mostly pages nobody touches), the worked-page rate as baseline, capped at 45% until it rests on 30 attempts.
Put the cost of the work next to each scenario, and the return per euro, so the CFO reads it like any other investment. No probability labels (“80% chance we beat this”) unless you calculated them. A stated probability without a calculation behind it is a made-up number.
The output is three MQL projections per month per segment. Present the range, not the midpoint. A range of 12 to 22 MQLs per month is more credible than “17 MQLs per month.” If only the optimistic case justifies the investment, the business case is weak.
Quarterly recalibration
Section titled “Quarterly recalibration”The model degrades if you don’t update it. Every quarter:
1. Compare forecast to actuals per page. Pull actual GSC clicks and actual MQLs from CRM. Calculate the ratio of actual to forecast for each page and each layer.
2. Identify which layer drifted. If traffic is close to forecast but MQLs are below, your conversion assumption drifted. If traffic is below but conversion rates are accurate, your ranking or CTR assumptions drifted. Diagnosing which layer is off tells you what to fix in the model and what to fix in the content.
3. Update the assumptions. Replace estimates with observed values. Your CTR curve after one quarter of data is better than any published benchmark. Your conversion rates after one quarter are better than any starting estimate. Swap them in.
Re-measure the win rate on the same three-months-against-a-year-ago window. Once you’ve worked on pages for a year, measure their win rate separately from the rest of the site: that number is the honest read of what the work adds.
4. Re-forecast the next quarter. With updated assumptions, run the model forward. The new forecast is more accurate because it’s built on observed data, not estimates.
After two to three calibration cycles, the model becomes genuinely predictive. The gap between forecast and actuals narrows to 10% to 15%, which is accurate enough for budget conversations and prioritization decisions.
The spreadsheet structure
Section titled “The spreadsheet structure”Set up one tab per segment cluster. Each tab has:
- Rows: One per page in the cluster
- Columns: Primary keyword, volume, seasonality index, secondary multiplier, target position, CTR, projected sessions, win rate, maturity percentage per month (months 1 through 12), MQL rate, projected MQLs per month
- Three scenario columns per output metric (conservative/baseline/optimistic)
- An actuals row per page that gets filled in monthly from GSC and CRM (Measuring by Segment covers where those numbers come from)
A summary tab rolls up all segments into a single view: total projected MQLs per month per segment per scenario, with actuals alongside for comparison.
Keep the assumptions in a separate “Config” tab: your CTR curve, your maturity ramps by content type, your conversion rates by funnel stage. When you recalibrate, you update the Config tab and the forecasts recalculate automatically.
What this page doesn’t cover
Section titled “What this page doesn’t cover”Why a forecast changes the budget conversation, and which segment to build first, are in Forecasting Pipeline. This page assumes you’ve decided to build the model and need to know how.
Take this away: 3 asks for your AI and your CRM owner
Your AI
Give your AI the Search Console export and this.
See the prompt
Two exports and this prompt give you your win rate.
See the prompt
Ask your CRM owner
You need: organic leads per landing page, to get your own conversion rates.
Get notified when new chapters publish
You're on the SEO lead path. Step 4 of 5.
0 of 5 read
- Next: Content Clusters
- Not read: Internal Linking
- Not read: The Content Quality Gate
- Reading now: The Forecast Spreadsheet
- Not read: Measuring by Segment
Switch path: CMO or Head of Growth