SEO Forecast Template

SEO Business & Delivery Toolkit, template 1 of 10

SEO Forecast Template

Project added clicks and contribution per keyword cluster with CTR, attainment and seasonality you can defend.

How much additional traffic and monthly contribution a portfolio of keyword clusters could add, with every assumption visible.

  • Excel workbook (.xlsx), opens in Google Sheets
  • PDF field guide, 9 to 11 pages
  • Blank and worked CSV templates
  • DataForSEO import script

Free, no email required. Version 3.0, updated November 2026.

SEO Forecast Template.xlsx
N13fx=IF(K13="","",IF(K13<>"Ready","n.a.",M13*H13*I13))

Start HereDashboardWorkspaceDelivery QAField Guide

The formula above is the real one from cell N13. Scroll it sideways to read the whole rule.

Connected to the DataForSEO API

This template is designed around one DataForSEO endpoint. Pull the data, convert it with one command, and the workbook does the rest.

Setup steps

DataForSEO Labs: Keyword OverviewSearch volume, monthly history, difficulty and intent for a list of keywordsdataforseo_labs/google/keyword_overview/live

Save the JSON responseThe full API response, kept as your source of truth.response.json

Run the import scriptOffline. No API calls, no credentials.dataforseo_import.py import --template 31

Paste at A13Dashboard, checks and action list update instantly.SEO Forecast Template.xlsx

Column map for this template:Filled by the APIAudit trail (automatic)You add

Cluster IDKeywordMonthly searchesBaseline CTRTarget CTRAttainment factorSeasonal multiplierConversion rateValue per conversionSource reference
3 of 10 input columns come straight from DataForSEO

What the SEO forecast template decides

SEO forecasts fail in two ways: they're so optimistic nobody believes them, or so vague nobody can act on them. A good forecast states its assumptions plainly so stakeholders can argue with the inputs instead of the conclusion.

This SEO forecast template models each keyword cluster (one row per cluster, deduplicated so traffic isn't counted twice) from search volume, baseline CTR, target CTR, an attainment factor, seasonality, conversion rate and value per conversion. It calculates baseline modeled clicks, added clicks and added monthly contribution, and ranks clusters by the value they add.

Who it's for

  • SEO leads building a business case or budget request
  • Agencies scoping retainers and setting expectations
  • Finance and leadership teams evaluating SEO investment
  • In-house teams setting quarterly targets

Where it fits in your workflow

The SEO forecast template answers one question. These three templates answer the questions around it:

  • SEO ROI Calculator: whether SEO is paying back, by month and cumulatively, after real costs, and when it broke even.
  • Keyword Prioritization Template: which keywords deserve your next hours of work, ranked by the monthly profit each could add per hour invested, with every assumption visible and editable.
  • Search Seasonality Planner: when demand for each keyword peaks, how much to scale traffic expectations month by month, and when to start production so content is ranking before the peak.

What's inside the download

One ZIP with the workbook, a 10-column input template (blank and filled), the PDF field guide and an offline script that converts saved DataForSEO responses into the input CSV. The workbook has five tabs.

Workspace. The working table. 10 input columns, 4 calculated columns, room for 1,000 rows. Dropdowns, Yes/No fields and range checks catch bad inputs as you paste.
Start Here. What it decides, a four-step setup and the colour key.
Dashboard. KPI cards, charts, a ranked Top 10 and an automatic action list.
Delivery QA. Pass, fail or exclude each validation step with evidence, owner and date.
Field Guide. Every field explained, plus each formula in plain English.

How the SEO forecasting calculation works

Baseline clicks = volume × baseline CTR × seasonality. Added clicks = volume × (target CTR − baseline CTR, min 0) × seasonality × attainment. Added contribution = added clicks × conversion rate × value per conversion.

The key formula, exactly as it appears in the workbook

=IF(K13="","",IF(K13<>"Ready","n.a.",M13*H13*I13))
Result column Plain-English formula What it tells you
Baseline modeled clicks Monthly searches × Baseline CTR × Seasonal multiplier Modeled baseline for the selected month.
Added modeled clicks Monthly searches × MAX(0,Target CTR-Baseline CTR) × Seasonal multiplier × Attainment factor Incremental opportunity after seasonal and attainment factors.
Added monthly contribution Added modeled clicks × Conversion rate × Value per conversion Forecast contribution based on the input conversion economics.

How to use the SEO forecast template

  1. Deduplicate into clustersOne row per cluster, using the primary keyword's volume or the cluster's combined volume, never both.
  2. Set baseline and target CTRBaseline from Search Console; target for the position you can credibly reach.
  3. Apply attainment and seasonalityAttainment discounts the gain (0.5 to 0.7 is sensible). Seasonality scales a specific month (1.0 = average).
  4. Present ranges, not pointsCopy the sheet for conservative and stretch scenarios by changing attainment and target CTR.

The worked example in the Workspace tab. Clear the lime input cells, paste your CSV at A13 (values only) and every result recalculates.

Fill the SEO forecast template with DataForSEO

Every template in this library is built to run on DataForSEO data. This one uses DataForSEO Labs: Keyword Overview: search volume, monthly history, difficulty and intent for a list of keywords.

Most of the data columns fill automatically. Add the business inputs listed below, then paste.

  1. Request the dataSend this request with your DataForSEO API login (HTTP Basic auth).
    POST https://api.dataforseo.com/v3/dataforseo_labs/google/keyword_overview/live [ { "keywords": [ "your keyword 1", "your keyword 2" ], "location_code": 2840, "language_code": "en" } ]
  2. Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
  3. Convert it with the included scriptIt runs offline, makes no API calls and never sees your credentials.
    python3 Automation/dataforseo_import.py import --template 31 --input response.json --output my-inputs.csv
  4. Add what the API can't knowCluster ID, Baseline CTR, Target CTR, Attainment factor, Seasonal multiplier, Conversion rate, Value per conversion
  5. Paste at A13Clear the demo rows, paste the CSV values at Workspace cell A13 and the dashboard updates.
Fills automatically from DataForSEO You add
Keyword, Monthly searches Cluster ID, Baseline CTR, Target CTR, Attainment factor, Seasonal multiplier, Conversion rate, Value per conversion

Source reference fills automatically with the task ID, endpoint, location, language, device and snapshot time, so every row is traceable. Before a big pull, estimate the cost with the DataForSEO pricing calculator. The same steps, plus an example request file, are in the download's Automation folder.

Worked example: Northbeam Outdoor

Every template ships filled with data from Northbeam Outdoor, a fictional outdoor-gear retailer that appears across all 40 workbooks, so you can see what useful output looks like before you add your own. This workbook has 18 example rows.

18Records
0Rows with input issues
$12,796Added contribution / month
10,367Added modeled clicks

The example portfolio adds roughly 10,400 modeled clicks and $12,800 in monthly contribution, led by the men's down jacket, trail running shoe and women's rain jacket clusters.

Top 10 by added monthly contribution

# Cluster ID Added monthly contribution
1 CL-08 $1,649
2 CL-09 $1,633
3 CL-17 $1,602
4 CL-14 $1,121
5 CL-18 $1,058

Input fields

Keep the columns in this order when you prepare a CSV. A missing observation stays blank; use 0 only for a measured zero or a verified No. Fields marked DataForSEO can be filled automatically by the included script.

CSV field Column Meaning and rule Source
cluster_id Cluster ID Unique nonoverlapping demand cluster. Do not repeat the same intent family. Your records
keyword Keyword Exact query in the selected country and language. DataForSEO: keyword
search_volume Monthly searches Provider monthly search-volume estimate. Use one market, language and source date. DataForSEO: search_volume
baseline_ctr Baseline CTR Assumed organic click-through rate as a decimal. Calibrate by device, brand and SERP layout. Decimal: 0.08 = 8%. Your records
target_ctr Target CTR Assumed organic click-through rate as a decimal. Calibrate by device, brand and SERP layout. Decimal: 0.08 = 8%. Your records
attainment Attainment factor Planning haircut for partial target attainment. Decimal: 0.08 = 8%. Your records
seasonality Seasonal multiplier Demand relative to an average month. 1 means average. Your records
conversion_rate Conversion rate Conversions divided by eligible organic sessions for the same segment. This is an assumption. Decimal: 0.08 = 8%. Your records
value_per_conversion Value per conversion USD contribution profit per conversion or expected lead profit. Use one value basis. Your records
source_ref Source reference Task ID, export filename and observation scope. Retain the untouched source response separately. Optional. DataForSEO: source_ref

Reading the result honestly

A forecast is conditional on the entered assumptions. Use it to discuss a range by changing and saving a clearly labeled planning case.

Limits. Do not add secondary keywords from the same cluster as independent demand. This is a one-month steady-state opportunity, not a timing model.

SEO forecasting mistakes to avoid

Forecasting from rankings aloneRankings don't pay bills. Carry the forecast through to conversions and contribution.
Double counting keyword variantsIf ten variants share one page, forecast the cluster once.
Presenting one numberShow a range with stated assumptions; it's more honest and more persuasive.

Tips from practice

Frequently asked questions

How do you forecast SEO traffic?

Estimate the CTR you can achieve for each keyword cluster, multiply by search volume and an attainment factor, adjust for seasonality, then convert to conversions and value.

How accurate are SEO forecasts?

They're planning estimates, not promises. Accuracy improves with your own CTR data, conservative attainment and regular comparison to actuals.

What's an attainment factor?

A discount for how much of the modeled gain you expect to actually capture, accounting for competition, execution and time.

Can I forecast revenue with this template?

Yes. Use value per conversion as contribution (margin) for profit, or average order value for revenue.

Jourdan Rombough

Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.

More on digital marketing in our blog

See all posts