Skip to content

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.

10 min readUpdated By Baba HausmannOn the SEO lead path

SEO leads and practitioners Walk away with: a working MQL forecast spreadsheet calibrated to your own data
  • 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.
Search Demand Volume Click-Through CTR Sessions Traffic Odds Win × Ramp Conversion MQL % Scenarios MQLs
Each layer takes an input and applies a configurable assumption

You already know the chain. What follows is how to fill in each layer with real numbers instead of estimates.

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 StageTypical multiplierWhy
TOFU2.0x to 3.0xBroad informational queries attract many related terms
MOFU1.3x to 1.8xProblem-oriented queries have moderate variation
BOFU1.0x to 1.3xCommercial 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.

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:

  1. Export GSC data for the last 90 days: query, page, clicks, impressions, position
  2. 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.
  3. Round positions to integers (position 3.4 becomes position 3)
  4. Group by position, calculate average CTR per position
  5. 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.

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.

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:

  1. 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.
  2. 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.
  3. 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.
  4. Losses include queries that vanished. An attempt with no impressions now counts as a loss. Dropping it flatters the rate.
  5. 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 publishShare of steady-state traffic
15%
210%
325%
445%
570%
6 onwards100%

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.

0% 25% 50% 75% 100% M1M2M3M4M5M6M7M8 5% 10% 25% 45% 70% 100% New content Optimized pages (faster ramp)
New pages vs. optimized existing pages: different ramp speeds

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.

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 stageEstimated MQL rate
BOFU6% to 8%
MOFU3% to 5%
TOFU1% 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.

The three scenarios are three win rates, plus a conversion multiplier:

ScenarioWin rateConversionWhen to use
Conservativemeasured rate minus 20 points (never below 10%)0.8xLead with this in CFO conversations
Baselineyour measured rate1.0xYour own track record: the most likely outcome
Optimisticbaseline plus 20 points (never above 80%)1.2xUpside 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.

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.

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.

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
I've attached a Search Console export from the last 90 days with query, page, clicks, impressions and position. Build my own click-through curve. 1. Remove brand queries. Our brand terms are: [brand terms]. Show me the list of queries you removed. 2. Round each position to a whole number. 3. For positions 1 to 10, give the click-through rate (total clicks ÷ total impressions). 4. Mark any position with fewer than 100 impressions as "too little data" instead of giving a rate. Give me a table I can paste into a spreadsheet. Use only my data, no industry benchmarks.

Two exports and this prompt give you your win rate.

See the prompt
I've attached two Search Console exports with query, clicks, impressions and position, brand queries removed: the last three months, and the same three months a year earlier. Calculate my win rate. 1. Attempts: queries at position 5 to 10 a year ago with at least 50 impressions in that period. 2. Wins: attempts that are now at position [target, e.g. 4] or better. 3. Attempts with no impressions now count as losses. 4. Win rate = wins ÷ attempts. Give me the number of attempts, wins and the win rate, plus ten example wins and ten example losses. If there are fewer than 30 attempts, say so and tell me to use 25%, labelled "default, not measured".

Ask your CRM owner

You need: organic leads per landing page, to get your own conversion rates.

Hi [name], could you pull the leads that came from Google search over the last 12 months, with the first page each one landed on and whether it later became an SQL? A spreadsheet with lead, date, first page and stage is all I need. I want to use our own conversion rates in the forecast instead of industry averages. Thank you, this makes the model a lot more honest.

Get notified when new chapters publish

You're on the SEO lead path. Step 4 of 5.

0 of 5 read

  1. Next: Content Clusters
  2. Not read: Internal Linking
  3. Not read: The Content Quality Gate
  4. Reading now: The Forecast Spreadsheet
  5. Not read: Measuring by Segment

Switch path: CMO or Head of Growth