How to Forecast SEO Potential With Google Data Studio via @seocounseling
We’ve all been there before. You’re pitching a potential client or your boss on a massive long-term SEO strategy.
The pitch went great, they love your ideas, and then the CMO says:
CMO: “So, if we partner with you, how much growth can we expect?”
SEO: “Oh it doesn’t work like that. It’s actually impossible to forecast any specific improvements or ranking positions, because there’s no way to manually force a keyword to rank in a specific position. Also, we can’t force users to take actions, only encourage them through…. Blah blah blah.”
CMO: “Cool, sounds great! So, what kind of growth can we expect?”
It doesn’t matter much factual reasoning you give. The prospective clients may understand the benefit. But without numbers to present to their own bosses, the decision makers on the other end just won’t get it.
While it’s nearly impossible to forecast SEO potential, we can get close.
Caveats to Forecasting SEO Potential
Trying to forecast SEO performance is like trying to navigate through the Amazon rainforest without getting bit by ants. It’s possible, but there are more insects to worry than ants.
It might be easier to forecast SEO potential if we had more consistent and reliable data from search engines, but alas, here we are.
Before you start presenting your new forecast model in your pitch, it might behoove you to be honest about your intentions.
Let your prospect know that it’s impossible to be accurate and these are mere projects at potential lifts. In reality, it could always be much more!
Before you present your forecasted numbers, I would advise you to mention the various factors that prohibit accurate forecasting like:
- Algorithm updates.
- Competitive changes.
With that being said, I recommend positioning this as a way to show just how much impact a small set of valuable keywords can make when optimized for properly.
How This Forecasting Model Works
This model requires a little bit of work on your end. I wish it was as simple as entering your domain name, but this is more of a research exercise.
The data is sourced in Google Sheets and is presented in Data Studio. My data studio dashboard is completely interactive and allows you to filter down by keyword and position increase scenario.
First thing we’ll need to do is source our data and plug it into Google Sheets.
1. Finding Your Source Data
Before you begin, you will need to duplicate my handy dandy SEO Forecasting Google Sheet. This is where we will plug-in all of our source data.
Now we need to collect our source data.
Our core data that we’re looking at comes from keyword research. You may use any keyword research tool you like! All you need to include is:
- Top 150 Keywords you want to target.
- Average monthly search volume.
- Current position for domain.
- Average site conversion rate.
- Average traffic percentage by device (desktop vs. mobile).
The forecasting model is meant to show the impact that SEO can have for even just a small set of data.
The point here is to say, “Look at how much of a difference improvements on just 150 keywords can make. Imagine what would happen with an expanded optimization plan!”
The cool thing that this forecasting model does is that it solely looks at the impact of targeting specific keywords in our SEO efforts.
That means that it does not account for all of the potential miscellaneous search terms that lead to traffic on your site. It’s only looking at the top 150 keywords that you believe will impact their performance.
In our example, we’ll be using the top ranking keywords for Nike, filtered to contain the word “shoe.”
Once you have your 150 keywords, current position, and volume you will need to paste it into the Google Sheet.
Make sure you’re only pasting the data in columns A-C and rows 2-151. Note that there is data below row 151. That data will automatically update after you paste in your data in rows 2-151.
After you’ve entered your keywords, you will need to enter in some Google Analytics data on the “Average CTR Data & Inputs” tab. You will need:
- Average % Organic Desktop Traffic
- Average % Organic Mobile Traffic
- Average Organic Desktop Conversion Rate
- Average Organic Mobile Conversion Rate
Just plug-in those four metrics, then you’ve completed step one!
2. Connect Google Sheets to My Data Studio Template
We’re so close to the fun part! The next step will require you to duplicate my SEO Impact Analysis Scenarios Data Studio template.
Here’s how to connect Google Sheets to Data Studio:
- Open this link to our SEO Forecasting Data Studio template.
- At the top of the page, click the make a copy image icon.
- This will generate a popup and ask you to select a “New Data Source.” Click on the dropdown and select “Create New Data Source” at the bottom.
- As a data source, find and select Google Sheets.
- Find your raw data sheet and select that as your data source.
Your Data Studio should update and look something like this screenshot:
3. Features of This SEO Forecasting Dashboard
The main feature of this SEO forecasting dashboard is the ability to show how much impact position changes can impact CTR and site traffic.
This number is calculated by factoring average monthly search volume, average CTR by position*, and your current position.
*CTR data is sourced from a CTR study by Advanced Web Ranking.
As you move your cursor over the bar graph, you will see new data populated by each position.
Want to narrow this down by keyword? I got you covered! There’s a built-in feature to allow you to filter your results by specified keywords of your choosing.
Put SEO Forecasting into Action
Why just present quick numbers in your pitch decks when you can provide compelling visualizations to really show your value?
There are several features in this dashboard to help you provide forecasting data to your prospective clients or boss.
Not seeing a specific feature?
Reach out to me with your requests and I’ll see if it’s possible to get it added to the dashboard!
- How to Build an SEO Business Case That Your Boss Can’t Say No To
- The Top 5 Ways to Prove the Real Value of SEO
- 9 SEO Analogies to Tell Your Client or Boss
All screenshots taken by author, June 2019