How to use historical CTR data to project your SEO impact

One of the most challenging parts of working in SEO is estimating the potential impact of our work. It’s a vicious cycle in the fight for precious time and resources; we know SEO can drive value for an organization, and we also know that it’s particularly tricky to quantify that value in comparison to other marketing channels. Impact and ROI projections are critical to secure budget and resources to implement SEO recommendations, but getting to a realistic ROI measure for a channel affected by so many external factors can feel like a constantly moving target. So, what’s a data-driven SEO to do? It’s not a perfect science, but today I’m going to walk you through the methodology we follow at Seer to estimate the directional impact of our recommendations.

Click-Through Rate Data Is Your Best Bet at Forecasting SEO ROI, but It’s All in How You Interpret and Present It

There are plenty of posts out there that walk you through how to use CTR data to estimate traffic and ROI from SEO optimizations. This method isn’t new, and it’s still the best option SEOs have for grounding future projections in historical performance. Truth be told, historical CTR data is a great place to start - but it is only as useful as the consulting you provide alongside it.

[TIP] It’s important to understand that SEO projections will never be 100% accurate. That’s okay - we’re not looking to guarantee performance, we’re looking to understand the potential influence that our recommendations can have on performance.

Let’s Get Started.

Here are the two links you need to set up the projection calculator for yourself:

Make a copy of this Google Sheets calculator template
Access this Looker Studio dashboard through the same Google account that has access to your Google Search Console property

Click here to skip ahead to the setup guide, and massive shout out to Tracy McDonald for whipping this up for the Seer team to share with our community!

What Data Should You Leverage to Provide SEO Projections?

We recommend that you look at historical CTR data from Google Search Console for positions 1-20, all device types, over the last 3 months, and refresh your data set quarterly. There are a few reasons for that criteria:

SERP layouts are constantly changing. SERP features are in flux, SGE is coming and going, and Google is always testing things. You can hedge against the impact of these changes by maintaining a fresh data set to use for your projections.
People browse Google across many different devices. Accounting for all of them in your data gives you the best chance at estimating impact considering different user behaviors.
Endless scroll opens up more interaction with “lower” ranking sites in SERPs. We’ve seen this in our own data - recently, we saw that Seer’s CTR in position 15 and 16 (1.65% and 1.43%) were actually higher than 5 or 6 (1.51% and 1.34%) over a 3-month period. Including data for positions 1-20 allows you to account for user behavior in an endless scroll SERP.

What Else Should You Consider When Estimating SEO Impact?

Our calculator template factors in a few other things as well:

It automatically filters out Query & Landing Page combinations that have not been clicked at least 2 times in the selected date range, because Queries that have received fewer than 2 clicks across your date range are likely irrelevant. Those would be considered outliers that drag the average CTR down, which isn’t relevant for impact calculations.
It removes Query & Landing Page combinations that do not reach at least an average position of less than 20 in the selected date range. With endless scroll and mobile rankings, filtering to the top 10 positions alone doesn’t give us an accurate view into user behavior. Filtering to the top 20 helps us better understand SEO potential that aligns with how we analyze actual site and competitor rankings.

How Should You Present SEO ROI Projections?

Our philosophy is to provide traffic and conversion ranges, not hard numbers. These estimates are not a guarantee of performance, they are our best estimates based on a site’s existing performance. This can (and will) change over time!
SERP landscapes are shifting a lot these days as Google incorporates SGE results. For this reason, it’s strongly recommended to pull the most recent data that matches the criteria for use (3 months AND at least 15,000 clicks total) so you are projecting impact based on today’s SERPs.
The template provides opportunities to better understand the context of the data within it. Reference the “% of Total Data” column to determine how likely it is that your page will reach that ranking threshold. The higher the percentage in that column, the more likely it is that your page will rank in that bucket. If your data is distributed fairly evenly across multiple buckets, you can present wider ranges to manage expectations of performance.
- In this example, you can say with 91% confidence that a new page built in this “Strains” site section will reach the top 3 positions - so you can safely apply the “Best CTR” for your projections:
- Compare that to this example, where you can see the ranking distribution of “News” pages is more divided. You can present your projections as a range between the “Better” and “Best” estimates:

How to Set Up Your SEO ROI Projections Calculator

Make a copy of this Google Sheets calculator template.
Access this Looker Studio dashboard through the same Google account that has access to your Google Search Console property.
Follow the instructions in the first tab of the Looker Studio setup guide (“Apply Your Filters”) to collect your data:
1. Select your Google Search Console property from the dropdown. If you do not see your website listed, confirm it’s connected to the Google account you are currently signed into.
2. Select the country for which you want to see data. If you need more than one country’s data, download them separately. Do not select more than one country in this dropdown menu. This allows for more accurate region-specific data.
3. Select the devices for which you want to see data. We recommend leaving all selected unless there is a specific reason you need to segment out mobile vs desktop vs tablet.
4. Select your date range, at least 3 months and at least 15,000 clicks total. Note that your calculator may run slowly if you select too wide of a range of data; we recommend selecting the smallest date range that meets the criteria (3 months AND 15,000 total clicks across that date range). Consider your site’s seasonality and recent SERP layout changes when selecting months. If you’re working with a new site that doesn’t have enough data to meet this criteria, that’s okay - you can only work with the data you have available to you, and you can always refresh the calculator to give more accurate estimates once you have more data.
Move to the second tab of the setup guide (“Review & Download”) and confirm the filters here match what you selected on the Apply Your Filters tab.
Download your data by hovering over the top right corner of the table at the bottom of this tab to reveal the three-dot “More” menu. Click the menu and select “Export.”
Save the CSV to your computer.
Move over to your Google Sheet template to set up your own calculator. A few things to note:
1. ONLY EDIT GRAY BOXES IN THIS SHEET. Some cells have hidden formulas that will only trigger to alert you if something is not functioning properly, so if you accidentally edit one of those cells you risk the calculator breaking.
2. Helpful hint: Include the date you set up the calculator in the title of the GSheet so it is easy to find the most recent version with accurate data for projections.
Copy and paste the data from the downloaded CSV into the first tab (“Raw Data”) of the SEO Projections Calculator.
1. Be patient! You’re pasting tens to hundreds of thousands of data points into the sheet, so it will take some time to load. It may display timeout error messages along the way - trust the process and give it time.
2. Do not refresh the sheet while the data is loading. Doing so may disrupt the process.
Move to the second tab (“Set Up”) and follow the instructions to customize your brand terms and site sections:
1. Use regex to enter the terms that represent your brand in the first gray cell. Follow this regex template and replace “term” with your brand terms: .*term.*|.*term.* etc. You can enter as many brand terms as you like. Include common misspellings, branded product names, and other non obvious brand variations. Scroll through the table below to confirm your brand and nonbrand terms are breaking out correctly. If you see errors, confirm your regex is correct.
2. Use regex to enter your custom landing page groupings in the gray cells of the table to the right. Follow this regex template and replace “section” with your site sections: .*section.* Reference the tips in the template for more regex customization options. You can enter as many custom site sections and landing page groupings as you like.
Move to the third tab (“Cleaned Data”) and briefly review the data to confirm it looks as expected. Don’t edit anything in this tab, it’s all automated!
Move to the fourth tab (“Good, Better, Best CTR”) to begin using your SEO Projections Calculator!
1. Choose options from the gray dropdown menus at the top of the tab to make the calculator relevant for the type of page you’re making recommendations for.
2. Manually enter the total MSV for the keywords you’re targeting, and your conversion rate for the site section you’re building a page for to calculate the estimated impact of your recommendations. You can easily add a column to calculate estimated revenue too.
3. Make copies of this tab and adjust filters to easily reference CTRs for different site sections. Rename the tabs for easy reference.

Once you’ve set up your calculator, we recommend refreshing it quarterly to leverage the most up-to-date CTRs as SERP layouts shift regularly. Here’s how to do that:

Make a copy of your existing Calculator GSheet. Copying your existing GSheet instead of starting fresh with a new template will retain all of your Brand/Nonbrand and Landing Page groupings with existing breakout tabs. No extra setup steps!
Follow Steps 1-6 from the section “How to Set Up” above to download your up-to-date data.
Copy and paste the data from the downloaded CSV into the first tab (“Raw Data”) of the SEO Projections Calculator. Make sure to completely replace the old data. You don’t want to accidentally combine new and old data.
Update the name of the file to include the refresh date so that it is easy to find the most recent version with accurate data for projections.

Have At It!

We hope this template and methodology gives you a way to more easily estimate the impact of your SEO recommendations. If you test drive it, I’d love to hear about it on X or LinkedIn.