MQL Forecasting: The Spreadsheet
- 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 The MQL Prediction Model The business case: why forecasting changes the budget conversation. Worked examples and the CFO pitch. Written for CMOs. Other perspective MQL Forecasting for Segment Prioritization Which segment to build first based on the forecast. The monthly operating rhythm. Written for Heads of Growth.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 Google Trends data at the category level) 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.
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.1x to 1.4x | 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
- 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: 30% to 50% 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.
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: Maturity ramp by content type
Section titled “Layer 4: Maturity ramp by content type”Not all content matures at the same rate. A purely informational TOFU page on a low-competition keyword might reach steady state in 4 months. A BOFU comparison page in a competitive space might take 8 to 10 months.
Configure different ramps by content type:
| Content type | Month 1 | Month 3 | Month 6 | Steady state |
|---|---|---|---|---|
| New TOFU (low competition) | 25% | 60% | 100% | Month 5-6 |
| New MOFU | 20% | 50% | 90% | Month 6-7 |
| New BOFU (competitive) | 15% | 40% | 80% | Month 8-10 |
| Optimized existing page | 50% | 80% | 100% | Month 3-4 |
| Refreshed/rewritten page | 35% | 65% | 95% | Month 5-6 |
These are starting points. After your first cluster matures, replace them with your actual ramp data. Pull GSC click data by month for each page since publication and plot the curve. Your observed ramp is your best predictor for the next cluster.
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”Run every layer at three assumption levels. For each configurable input (CTR, target position, conversion rate, maturity speed), define a conservative, baseline, and optimistic value.
The spread should be honest:
- Conservative: assumes you rank 1 to 2 positions worse than target, CTR is 15% below your curve, conversion is at the low end of your range
- Baseline: your best current estimates
- Optimistic: assumes you hit target position, CTR matches your curve, conversion is at the high end
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.”
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.
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, 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
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”The strategic case for forecasting (why this changes the budget conversation) is in the CMO version. The prioritization framework (which segment to build first based on the forecast) is in the Head of Growth version. If you need to justify the model to leadership, start there. If you need to decide build order, start there. This page assumes you’ve already decided to build the model and need to know how.
Get notified when new chapters publish
Summarize with AI