SEO Forecasting: How to Forecast SEO Traffic (Step by Step + Template)
A spreadsheet method you can build in an afternoon: a seasonal baseline from Search Console, a CTR-by-position opportunity model, ranking scenarios and a ready-to-copy template, presented as a range and checked against actuals every month.
SEO forecasting is estimating future organic search traffic, and often the conversions and revenue it brings, from your own Search Console history plus assumptions about rankings and click-through rate. A useful SEO forecast is a range with named assumptions, not a single number.
Most SEO projections fail for one of two reasons: they are a single number from a tool with no visible assumptions, or they add up the search volume of every target keyword as if the site will rank first for all of them next month. The method below makes every assumption a visible input cell, so a finance lead or client can change your click-through rate or ramp-up time and watch the forecast move. It takes an afternoon in Google Sheets or Excel. If you would rather compare software that automates parts of it, see the guide to SEO forecasting tools.
The three parts of any SEO forecast
Every credible forecast separates three things, because each has a different source and a different level of certainty:
1. Baseline. What organic clicks will do over the next 12 months if you ship nothing new. It comes from your own history, so it is the most reliable part.
2. Opportunity. The extra clicks from defined work: new pages, better rankings on named keywords, a technical fix. It rests on keyword data and assumptions, so it is the least certain part.
3. Timing. How fast the opportunity arrives. Ranking gains take months.
Keep brand and non-brand traffic in separate rows. Brand searches move with advertising, PR and product launches, not with SEO work, and mixing them makes both the baseline and the attribution harder to defend.
The baseline is your regular salary: you know roughly what arrives each month, including the months that are always a bit higher or lower. The opportunity is a raise you are negotiating: worth planning for, but you would not spend it before it is signed. A sensible budget lists both separately, and so does a sensible SEO forecast.
Step 1: Build the baseline from Search Console
Search Console keeps 16 months of performance data, which is enough for one full year plus four months of year-on-year comparison. Export it before you need it: open Performance → Search results, set the date range to the last 16 months, open the Dates tab and export. For more than the interface allows, pull it with the Search Console API or the bulk export to BigQuery.
Sum daily clicks into calendar months. Filter out brand queries first if you can (in the Queries filter, use "Queries not containing" your brand name), keeping in mind that Search Console hides some rare queries for privacy, so query-filtered totals are lower than the unfiltered total. Then clean the history: if the last year contains a one-off spike or crash, such as a viral page or an outage week, replace those days with a normal value, or the forecast will repeat it next year.
Step 2: Adjust for seasonality and trend
Two numbers turn monthly totals into a baseline:
Seasonal index. For each of the last 12 months, divide that month's clicks by the 12-month average. A December index of 1.30 means December runs 30% above an average month. With only one year of data the index includes some noise; if you have older data in Analytics or a BigQuery export, average two or three years.
Underlying trend. Compare the four months that appear in both years (months 13 to 16 against months 1 to 4). If they are up 6% year on year, use 6% as the starting trend, then sanity-check it against anything you know: a migration, a core update hit, a product line you dropped.
Baseline for any future month = 12-month average × that month's seasonal index × (1 + trend). This is the "if we change nothing" line. Everything you add on top has to be earned by specific work.
Step 3: Build the CTR-by-position opportunity model
The opportunity model answers "if we do this work, how many extra clicks?" It is a table with one row per target keyword or keyword group:
| Column | Where it comes from |
|---|---|
| Monthly search volume | Keyword Planner or your keyword tool. Treat it as an estimate; volumes are rounded and averaged. |
| Current position | Search Console average position for that query, or your rank tracker |
| Current CTR | Search Console CTR for that query. Use your real number, not a curve, wherever you have it. |
| Target position | Your judgment, based on who ranks there now and how strong your page is |
| Expected CTR at target | Looked up from your own CTR-by-position curve (see below) |
| Probability of reaching target | Your judgment: 0.3 for a hard keyword, 0.8 for one where you already sit on page one |
| SERP feature discount | An input from 0 to 1 for results with AI Overviews, ads, shopping or video packs that take clicks |
Incremental clicks per keyword = volume × (expected CTR − current CTR) × probability × discount. Sum the column for the total monthly opportunity at full maturity.
Build your own CTR curve. Published click-through rates by position disagree, sometimes widely, because they shift with industry, device, intent and what else sits on the results page. So build the curve from your own data. Export the Queries table from Search Console for the last 3 months, drop brand queries, and group the rest into position buckets: 1, 2, 3, 4 to 5, 6 to 10 and 11 to 20. For each bucket, divide total clicks by total impressions (do not average the CTR column, which lets tiny queries skew the result). In a sheet, that is =SUMIFS(Clicks,Bucket,"1")/SUMIFS(Impressions,Bucket,"1") per bucket. Label the result as an input that anyone reviewing the forecast can change.
Step 4: Model ranking scenarios and ramp-up
Run the opportunity model three times with different inputs, never by multiplying one number by 0.8 and 1.2. Each scenario should describe a world you can explain in a sentence.
| Input | Conservative | Expected | Aggressive |
|---|---|---|---|
| Share of keywords reaching target | Low | Middle | All |
| CTR at target position | Low end of your curve | Your curve | High end of your curve |
| SERP feature discount | Heavier | Moderate | None |
| Ramp-up | Slower | Your history | Faster |
| Baseline trend | Flat or slight decline | Recent year-on-year trend | Recent trend |
Opportunity does not arrive the day you publish. New pages need to be crawled, indexed and then earn rankings; improvements to existing pages usually show up sooner. Model this as a ramp: the share of full opportunity you expect in each month after the work ships.
| Months after launch | New content (example input) | Existing page improvement (example input) |
|---|---|---|
| 1–3 | 0% | 25% |
| 4–6 | 25% | 60% |
| 7–9 | 50% | 90% |
| 10–12 | 80% | 100% |
These percentages are placeholders, not benchmarks. Replace them with what happened to your own pages published a year ago: their clicks by month since launch. Start the ramp on the delivery date: if the pages ship in month 3, the ramp starts in month 3, not month 1.
Step 5: Turn it into ranges, not points
Your forecast for each month is now baseline plus ramped opportunity, in three versions. Keep all three. The conservative case should be a result you would dislike but not be surprised by; the aggressive case should be possible if most things go right. If expected sits close to aggressive, your inputs are probably optimistic.
Report the conservative-to-aggressive band as the forecast and the expected case as the most likely point inside it. Round every number to two significant figures. The width of the band is information, not a weakness: it tells decision-makers how much uncertainty they are buying.
Worked example (illustrative numbers)
The numbers below are invented to show the arithmetic. They are not benchmarks.
Baseline. A site had 240,000 non-brand clicks over the last 12 months, an average of 20,000 a month. Its seasonal index for November is 1.25, and the year-on-year trend from the overlapping months is +5%. Baseline for next November = 20,000 × 1.25 × 1.05 = 26,250 clicks.
Opportunity. The team plans to improve pages for 40 keywords with a combined 60,000 searches a month. They rank around position 14 today and Search Console shows a 1% CTR for that group. The CTR at target comes from the site's own curve.
| Input / result | Conservative | Expected | Aggressive |
|---|---|---|---|
| Share of volume reaching target | 50% | 75% | 100% |
| CTR at target (input) | 4% | 6% | 9% |
| SERP feature discount | 0.70 | 0.85 | 1.00 |
| Monthly gain at full maturity | 60,000 × 0.5 × 3% × 0.7 = 630 | 60,000 × 0.75 × 5% × 0.85 ≈ 1,910 | 60,000 × 1.0 × 8% × 1.0 = 4,800 |
| Year-one gain with the new-content ramp | ≈ 2,900 clicks | ≈ 8,900 clicks | ≈ 22,300 clicks |
Year-one gain is the monthly gain multiplied by the ramp: three months at 0%, three at 25%, three at 50% and three at 80%, which adds up to 4.65 months of full-maturity traffic. For the expected case, 1,910 × 4.65 ≈ 8,900.
Value. If non-brand organic visitors convert at 2% and a conversion is worth $120 to the business (both from Analytics, both hypothetical here), the expected case is worth about 8,900 × 0.02 × $120 ≈ $21,400 in year one, with a range of roughly $7,000 to $53,500. That width is the real information in the forecast. To turn this into a return figure, see calculating SEO ROI.
SEO forecast template: columns and formulas
The template is four tabs in Google Sheets or Excel. The formulas work in both. The layout below assumes 16 months of non-brand clicks in Baseline rows 2 to 17, oldest first, so rows 2 to 13 are the first 12 months and rows 14 to 17 are the four newest.
| Tab | Column / cell | What goes in | Formula |
|---|---|---|---|
| Inputs | One row per assumption | Name, value, source, date set | Typed values only |
| Baseline | A, B | Month, non-brand clicks | From the Search Console export |
| Baseline | F1 (trend) | Year-on-year change | =SUM(B14:B17)/SUM(B2:B5)-1 |
| Baseline | F2 (average) | Last 12 months average | =AVERAGE(B6:B17) |
| Baseline | C (seasonal index) | Each month vs the average | =B6/$F$2, filled down to C17 |
| Baseline | D (baseline forecast) | Same month next year | =$F$2*C6*(1+$F$1) |
| Opportunity | A to F | Keyword, volume, current CTR, CTR at target, probability, SERP discount | Inputs and lookups from your CTR curve |
| Opportunity | G (incremental clicks) | Monthly gain at maturity | =B2*MAX(D2-C2,0)*E2*F2 |
| Opportunity | G42 (total) | Sum for 40 keywords | =SUM(G2:G41) |
| Ramp | A, B | Months after launch, share of full gain | Inputs from your own past launches |
| Forecast | Gain in month n | Ramped opportunity | =Opportunity!$G$42*VLOOKUP(A2,Ramp!$A$2:$B$13,2,FALSE) |
| Forecast vs Actual | Variance % | How far off each month was | =(Actual-Expected)/Expected |
| Forecast vs Actual | Inside range? | Did the month land in the band? | =AND(Actual>=Conservative,Actual<=Aggressive) |
Build the Opportunity, Ramp and Forecast tabs three times (or add a scenario column) for the conservative, expected and aggressive cases. Keep every assumption on the Inputs tab with a note saying where it came from. That tab is what reviewers read.
Excel SEO forecast shortcut. If you would rather let a statistical model find the trend and seasonality for the baseline, select the month and clicks columns in Excel and use Data → Forecast Sheet, or the FORECAST.ETS function. It detects seasonality and adds upper and lower confidence bounds. Use its output as the baseline row and keep your opportunity model on top.
Prefer not to build it yourself? Our free SEO dashboard includes an 8-week traffic forecast built from your own Search Console history.
Predictive SEO: can you predict SEO results?
You can estimate SEO results; you cannot predict them exactly. "Predictive SEO" usually means one of two things: using past data to project traffic (what this guide does), or spotting rising topics early so you publish before demand peaks. Both are useful. Neither can foresee a core update, a competitor's new section, or a results page that suddenly shows an AI Overview.
What you can predict with reasonable confidence is the shape of the year: seasonality repeats, and a stable site's baseline is usually close over the next few months. What you can only estimate is the gain from new work, which depends on rankings you do not control. That is why SEO projections should come as a range with stated assumptions, and why the useful question is "how wide is the range and what moves it?" rather than "what is the number?".
How to present an SEO forecast to clients and stakeholders
A single number invites people to hold you to it. A range tells them what you know. In practice:
Lead with the range. "We expect 3,000 to 22,000 additional non-brand clicks in the first 12 months, most likely around 9,000." Not "we will add 9,000 clicks".
Show the chart as a band. Plot the baseline as one line, then shade the area between conservative and aggressive, with the expected case as a line inside it. Keep the actuals line on the same chart when you report later.
State the three or four assumptions that move the result most. Usually the CTR at target, the share of keywords that reach target, and ramp time. Say which one you are least sure of.
Separate baseline from incremental. If seasonality alone takes traffic up 25% in November, that is not the result of your work, and a stakeholder who sees it claimed as such will stop trusting the rest.
Tie the forecast to deliverables. For clients, list the pages and fixes the forecast assumes and when they ship. If delivery slips, the forecast moves, and everyone can see why.
Agree the review rhythm up front. Monthly check against actuals, quarterly re-forecast. Saying this before the work starts turns a miss into a scheduled update rather than an excuse.
Round. "About 9,000", not "8,893". False precision implies confidence the method does not have.
For framing the numbers for people outside SEO, see showing SEO value to non-SEOs.
How accurate are SEO projections, and why do they miss?
There is no published, reliable error rate for SEO forecasts in general, and you should be wary of anyone who quotes one. Accuracy depends on how stable the site is, how far out you forecast and how much of the forecast is baseline (fairly predictable) versus opportunity (much less predictable). The baseline for a stable, established site is often close to the mark for the next few months. The opportunity part is where forecasts miss, for reasons you can name:
Algorithm updates. Google ships several core updates a year plus smaller changes. Any of them can move your rankings, or your competitors', in ways no model can foresee.
SERP features. Results pages change shape. When an AI Overview, a video pack or more ads appear above the organic results, the CTR at a given position drops even if your ranking holds.
AI Overviews and AI Mode. Several industry studies have reported lower organic click-through rates on queries that show an AI Overview. Google has said clicks and impressions from AI features are counted inside the normal Search Console performance totals rather than reported separately, so you cannot cleanly isolate their effect. Use a heavier SERP discount on informational queries and review it quarterly.
Search volume error. Keyword tool volumes are rounded estimates and averages over the year. Your opportunity model inherits that error.
Competitor and execution risk. Competitors publish too, and planned work ships late or not at all. A forecast assumes delivery; say so.
Distance. Error grows with the horizon. A 3-month forecast is far more reliable than a 12-month one. Re-forecast quarterly rather than defending a year-old number.
AI answers also change what "SEO results" means. A brand can lose clicks while being named more often in ChatGPT, Perplexity or Google's AI Mode answers, and none of that shows in a click forecast. If AI visibility matters to the business, track it as its own metric next to the forecast; our AI visibility tracker measures how often engines mention and cite you for a set of prompts.
How to track forecast vs actual
A forecast nobody checks teaches you nothing. Set up a monthly comparison from the start:
1. Freeze the forecast. Save a dated copy of the inputs and outputs when it is approved. Never edit history; publish a new version instead.
2. Pull actuals the same way. Same Search Console property, same brand filter, same calendar months. A change in the filter can look like a forecast miss.
3. Compare against the band. Each month, record actual, expected, variance % and whether it landed inside the conservative-to-aggressive range.
4. Diagnose misses by component. Did the baseline miss (seasonality, a core update, a tracking change) or the opportunity (pages shipped late, rankings stalled, CTR lower than assumed)? Check rankings and CTR for the target keywords separately from the total. The ranking drop analysis guide helps when the baseline falls.
5. Update the inputs. Replace the CTR curve, ramp and probabilities with what actually happened, and re-forecast the remaining months. After two or three cycles your inputs come from your own results, and the forecasts get noticeably better.
To test whether a specific change caused a lift, compare the changed pages with a similar group you left alone over the same period. The control group absorbs most of the seasonality and algorithm noise.
Mistakes to avoid
Adding up search volume as if you will rank first everywhere. Volume is not traffic. Multiply by a CTR you can defend and a probability below 1.
Ignoring seasonality. Forecasting from the last three months in a seasonal business either overstates or understates the year. Use the 16 months Search Console gives you.
Mixing brand and non-brand. Brand demand follows marketing spend. Forecast it separately or leave it out.
Starting the ramp on approval day. The ramp starts when the work ships and is indexed.
Using it to promise rather than plan. A headline number written to win a budget will miss, and the miss costs more trust than the forecast earned.
Never re-forecasting. A 12-month forecast not updated after a core update is no longer a forecast.