Yes, forecasting SEO is hard but extremely useful. This spreadsheet uses basic statistical methods and historic traffic data to reveal the direction the website is heading to. Having a solid forecast is a great way to close a deal, to justify a requested budget or ask for additional resources.
What you need for SEO forecasting
- Two years of historic organic traffic data (ideally even more)
- A list of events that happened in the last two years and affected your website traffic
- Seasonality data (can be extracted from your organic traffic)
- SEO plan for the next year
The logic behind the forecasting tool
Step 1: Get your historical data
As I mentioned above, to get a forecast you can count on, you need to get your monthly traffic data for at least a two-year period. This is really important for revealing seasonal trends. If you have only one-year of data, then you’re unable to say which spikes or trends are caused by seasonality, website improvements or other events.
If you use Google Analytics, getting data is really simple:
- Go to the Channels report (Acquisition -> All Traffic -> Channels)
- Click Organic Search
- Select desired date range
- Export data (top navigational menu)
Step 2: Clear your data
Two years is a long period of time for which a lot of events might have happened. It’s ok, and desirable, to take all events that you expect to happen in the future into account. Your annual free shipping promotion in August should be considered for sure.
However, if an atypical event has occurred in your historical data, you be better to remove the impacts of that event from your data. If you don’t, the forecasting model will calculate inaccurate seasonal trends. To put some clarity on it, imagine a couple scenarios:
- In December 2015, due to technical problems on the server and the following migration to a new server, you lost 100,000 visits. This event (hopefully) is not going to happen again, therefore, you should increase your numbers for December 2015 by 100,000 visits.
- On the other hand, in December 2016, you ran a TV commercial that generated an extra 60,000 visits via brand searches. Even though this event increased your traffic, the campaign ended up with a negative ROI so you don’t plan using TV commercials again (only one-time event). Then you should lower your numbers for December 2016 by 60,000 visits.
The standard deviation can quickly show if there are any unusual data spikes (outliers) in your dataset. It tells you how far each data point is from the mean (the average, or sometimes called the arithmetic mean).
Each website is different so it’s hard to give any solid advice but, if a month is two standard deviations from the mean, I start looking for answers.
Traffic spikes are normal and occur on any site so use your judgment and knowledge of the site, in combination with standard deviation, to evaluate outliers.
Step 3: Calculate base trend
For calculating the overall trend, we use the =FORECAST function and it calculates the expected y-value for a specified x, based on the linear regression of a dataset.
Note: You can find the same function in Excel.
This calculates the linear trend and extends the trend to the year 2017.
Step 4: Apply seasonal trends
Once you have determined the trend, it’s time to start working with seasonality – by which some businesses may be strongly influenced.
For the sake of simplicity, we will calculate a seasonal index for each month as a deviation of each month’s organic traffic from the average traffic (the mean). Let’s take some numbers from our example:
- July 2016: 674,659 visits
- Average: 574,772 visits
Seasonal index (July 2016) = 674,659 / 574,772 = 1.17
Seasonal index (July 2016) = 1.17
Repeat this for each month and you will get a corresponding index for every month of your historical data.
Validating your data is extremely important so, if you can use other sources to verify seasonal trends for your industry, I strongly encourage you to do so.
The next step is applying the seasonal indexes to the base forecast data.
If you plot new data points to a chart, you will see something like this.
Step 5: Predict the impact of your SEO plan
This one may be tricky and, because you don’t have a crystal ball, you will need to rely on your knowledge and experience.
Basically, you’re estimating the impact of your SEO effort for each month.
For example, you will start the year with low hanging fruit and easy wins (such as optimizing meta titles and content) that don’t require developers’ time (because they are busy anyway so you don’t have a choice). You may predict that these changes will increase organic traffic by an additional 5% in February.
In spring, web developers decrease the page load time, implement structured data, and fix indexation issues. Now, you’re confident to predict an increase by 10% in July. You got the point!
Forecast (July 2016) = Base Forecast (July 2016) * Seasonal Index (July 2016) * (1 + Growth (July 2016) )
Forecast (July 2016) = 641,293 * 1.17 * (1 + 0.10)
Forecast (July 2016) = 802,608
Do this estimate for each month and you will get another line to your chart.
It’s a good idea to try to predict a couple of scenarios:
- Aggressive forecast – Everything goes right! You got all the resources you need and all the suggested changes were implemented based on the schedule.
- Conservative forecast – There are some ups and downs. Not everything goes as smoothly as expected but all major updates were implemented.
You can also create a very conservative forecast if you use negative growth.
By the end of the day, you will be looking at a chart similar to the one below. It shows you historical data for 2015 and 2016 as well as forecasted data points for 2017.
Evaluate your forecast after 4-6 months
Google updates or refines its algorithm daily so I wouldn’t recommend forecasting your SEO success without reviewing it every once in a while. A lot of things may change in 6 months, so take your time to review what has been done in that period and what the results are.
With each revision, you will get more confident and accurate in predicting the impact of each change and be able to make better forecasts in the future.
I also like comparing actual traffic numbers with forecasted numbers on a monthly basis to see if we hit the targets or not. If you are 5% short of your aggressive target, there is no need for panic because SEO takes time. Especially for bigger sites and site-wide changes (e.g. canonical tags, facet navigation, etc.), as it may take a bit longer for Google to recrawl the entire site and recalculate signals.
However, if you’re missing 20% or more visits then it’s time to start investigating what the hell is happening.
Keep an eye on your progress but don’t recalculate the forecast every month.
Forecasting SEO revenue
The spreadsheet doesn’t help you forecast the revenue but it’s possible to and I’ll give you two methods.
Use revenue instead of traffic
In step one, you exported historic organic traffic data from Google Analytics. What you can do is export historical revenue data instead and use it in the sheet. It will work just fine.
Keep in mind that most of Google Analytics reports work with the Last Non-Direct Click attribution model. Customer journey and attributing modeling have become incredibly complex topics but explaining these topics is not intended in this post.
Add conversion rate and average order value
This is the simplest formula for calculating revenue:
Revenue = Traffic * Conversion Rate * Avg. Order Value
If you forecast organic traffic, then you may extend the sheet by adding conversion rate and average order value. Once you have done that, you just multiply all three numbers to get estimated revenue for each month.
It’s important to note that conversion rate and average order value may be different for each month. For instance, if you sell ski products, there is a big chance that your conversion rate in December is higher than in June.
Large e-commerce website?
Large websites (50,000+ pages) can be usually divided into bigger groups with the same type of pages (category pages vs. product detail pages vs. blog …). If you manage such a website, then it’s worth considering to create a forecast for each group of pages. This helps to better align your forecast with your SEO plan. If your plan is mainly focused on optimizing product detail pages, you will expect faster growth of organic traffic towards product detail pages rather than to any other parts of your website.
Breaking a site into smaller groups provides additional insights, and helps you understand how users interact with your site.
How to use the sheet
The template includes two sheets:
- Data Source – Dumping your traffic data into column B and adjusting spikes in column C is needed (note that I made adjustments in December 2015 and December 2016 on the test data).
- SEO Forecast – Change the estimated growth for each month (column D).
That’s it! The template will calculate the rest and the forecasted numbers will be shown in column E and plotted on the chart next to it.
It’s not perfect and some data analyst experts might not be happy with this simplified forecasting, however, using this tool gives you a solid idea of what to expect from the future.
Forecasting SEO is everything but easy so I’d love to hear how you face this challenge. What are your methods?Open the sheet