Create a CTR curve in Google Sheets with SEOSheets

Why CTR matters

As a quick refresher, CTR for a query or specific page is calculated as follows:

CTR = Clicks / Impressions

Simply put, a CTR of 6% means that 6 out of 100 views of a search result lead to a click.

Usually CTR is considered to reflect how well a search result connects with users' search intentions. Another big factor however, is the position of the search result in the SERP.

Generally, the higher a result appears on the page, the more likely users are to click on it. So much so, that the first position typically has the highest CTR with figures like 20-30%. Links in lower positions see much lower click rates, sometimes dropping well below 5%.

Take a look in your own Google Search Console properties. It is likely that queries or pages with a relatively high average position also have a high CTR, but this is not always the case.

So how do you determine whether best running shoes is performing good or running shoes is underperforming? Enter the CTR curve.

Understanding CTR Curves

A CTR curve is usually a graph that shows the average CTR of queries for different positions in search engine results pages (SERPs). While often visualized as line graphs or scatter plots, CTR curves can also be represented as tables.

A bart chart nicely highlights differences between positions and relative to the max (= 100%). You can either stack bars or put them side by side, personally I find the former visually appealing:

The preceding bart chart can be directly translated to a line chart, whereas you can already draw a line through it. However, for a line chart I would suggest to use the lower left corner as origin:

In these examples we've limited our curve to position 10, but you can set this limit to any number you like.

Applications for CTR Curves

Looking at these visuals, you may already see multiple applications for CTR curves. We will discuss two of them here:

1. Benchmarking performance internally and externally
2. Forecasting results by moving queries up or down the curve

CTR Curves for Benchmarking

CTR curves serve as benchmarks in two ways:

1. Internal benchmark. When looking at the CTR of any query, we can compare it to the average CTR for it's position.
2. External benchmark. When looking at the average CTR for any position, we can compare it to industry benchmarks.

In both cases, you can measure your pages’ effectiveness. If queries or pages are underperforming compared to the average CTR for their position, it’s a clear signal that your meta descriptions, title tags and other aspects may need some work. Conversely, if you’re outperforming the curve, it may be worth investigating if there are winning elements that can be replicated across other pages.

CTR Curves for Forecasting

CTR curves can also help you forecast performance changes. By understanding the CTR for different search positions, you can predict how many visitors you might gain by improving your keyword rankings.

This can be very helpful to prioritize optimization efforts and quantify the potential impact of SEO strategies before investing time and resources. Especially when communicating this to clients or other stakeholders.

Create a CTR curve with SEOSheets

Creating a visualization itself can be a matter of seconds when you have the data. Getting the data however can take a lot more time, if done manually. Exporting data from Google Search Console, importing it into Google Sheets and aggregating it into positions. It adds up, especially when you need to create a CTR curve regularly.

Because of this I have integrated a function into SEOSheets which I named rank distribution. In only few clicks it enables you to:

• Import data from Google Search Console into Google Sheets.
• Aggregate query count, clicks, impressions, and average CTR for all positions present it the data set.

Everything you need to create a visual right away.

Compare CTR curves between periods

Curious to see how your CTR curve changes over time? So was I. Simply follow the preceding process to create a CTR curve and enable the periode comparison setting: