A Google sheets template for SEO traffic forecasting
A Google sheets template for SEO traffic forecasting
Forecasting traffic is hard. That's why I created this SEO forecasting template that helps you project seasonality, impact from SEO projects, and YoY growth.
In Monday’s article on SEO traffic and revenue forecasting, I laid out a concept to help SEOs make better business cases (please check it out now if you haven't read it, yet).
Forecasting traffic is a big challenge because you have to take a lot into account:
Seasonality
Impact of your SEO projects/recommendations
Year-over-Year growth
On top of that, you want to consider different scenarios, in case your strategy plays out a bit more conservatively than expected.
To solve all of these problems, I created a Google Sheets template that does all of this for you, exclusively for Tech Bound Members.
The best part?
All you need is your historic organic traffic!
The template
The first sheet is an example with fake traffic numbers to show you what this model can look like.
The second doc is the actual template you can use for your own traffic projections (please make a copy). You simply copy/paste your organic traffic data into the sheet and add the projected SEO traffic from your projects – and boom! Traffic forecast done.
The template itself includes all the stuff that’s often forgotten: Year-over-Year Growth of your baseline traffic, seasonality and impact of your SEO projects.
Manual
Step 1: Make a copy of the template I linked above.
Step 2: Export organic sessions from Google Analytics (or your web analytics tool of choice) -> Audience -> Overview -> add the organic traffic segment -> set the time range (current year + previous 2years) -> export the data (to Google Sheets).
Make sure to select "weekly" for the date range in GA.
Step 3: Edit your data so it fits into the blue columns on the "Raw-Data" tab: one year per column.
Step 4: Copy/paste the data over to the template (into the tab "Raw-Data", so it fills out the blue columns).
Step 5: Add the estimated traffic from your SEO projects to the table in the “Overview” tab.
Step 6 (optional): adjust the scenarios on the “Scenarios” tab.
Step 7 (optional): you could multiply your average conversion rate with the projected traffic to forecast revenue from SEO.
Explanation
Tab “Overview”: Here’s where you can see your current traffic (column B), baseline forecast (column C), and growth forecast (column D).
You can also add your SEO Projects, including their name, launch week, and expected additional traffic (table in F23:I34). You get the latter from the article about projecting SEO Traffic and Revenue.
You can also see the growth from your baseline traffic in cell H20 (growth you would see if you did nothing) and growth forecast in cell H21 (growth from your SEO projects).
Tab “Raw Data”: Here you add your own traffic data of the current year and from the previous 2 years. That’s all you need to do here.
Tab “Process”: Contains the same step-by-step manual as written out here on this page.
Tab “Scenarios”: See how realistic, conservative, and optimistic scenarios play out and adjust them as needed. You can change the conservative scenario to -10%, for example, if you feel that -20% is too much.
“Baseline Forecast”: The traffic you would get if you did nothing, simply based on the growth of the previous two years.
“Growth Forecast”: The final traffic forecast, including expected traffic from SEO projects, seasonality and YoY growth.
Please enjoy and tell me about the success you have with this template!