This guide details how to generate custom Click-Through Rate (CTR) curves using data from Google Search Console. In doing this we’ll be able to:

- Identify outliers to find queries that result in unusually high and low click-through rates for your site.
- Make personalized forecasts of ranking change impacts.
- Measure the effect of improvements made to title tags, meta descriptions and rich results.

- After plotting the CTR curve you see outlier pages that have unusually low CTR. You notice these pages have poor meta-descriptions and no rich markup.
- To formulate a business-case for implementing better meta-descriptions and rich markup, you forecast the increase in clicks to those pages. Using the site’s custom CTR curve (rather than generic or legacy click-curve data, e.g. AOL) you are able to maximize the accuracy of this forecast.
- Your proposed changes end up getting implemented. A few months pass and you want to see the effects. Generating the new CTR curve for the optimized pages, you see a marketable improvement compared to before. Your work has paid off and you have the data to prove it.

## Part 1: Extract Data from Search Console

Firstly, we need to release data from the confines of Search Console, a task easily accomplished using the Search Analytics add-on for Google sheets. If you already have this installed, open up a new sheet and skip to Step 2. Otherwise continue with Step 1.#### Step 1: Install the Sheets Add-on

After signing into Search Console, open up the Search Analytics Sheets add-on and click the “+” box to integrate it with your Google account. You should see a new sheet open and a dialog like this will appear: After authenticating the add-on, it can be accessed from any new sheet through the toolbar.#### Step 2: Make API Request

You are now ready to request data through the Search Console API. Select your site and the desired date range, filters and dimensions to group by. In this case, I have selected the maximum allowable date range of 90 days and grouped by Query (keyword), as well as Page. For the purposes of looking at CTR (i.e. this blog post) I would not suggest grouping by Date; this way each Query & Page combination will be represented by only one point on the scatter plots we generate in Part 2 and 3. Finally, filters can be applied here to do things like remove branded terms and segment on a given device type or country. Once you are happy with the selections, click “Request Data” and allow the sheet to populate with your sweet sweet data. So long as you did not group by Date (i.e. if you are following along with me) then you should be looking at Clicks, Impressions, CTR, and Position aggregated by Query and Page.#### Step 3: Export Data

At this point, you are welcome to attempt continuing the analysis in sheets, however, I find Excel much better for what we are going to do. Export the table in csv format (or xlsx) so we can open it locally in Excel.#### What does the Search Console CTR metric measure?

At a glance it might appear to represent the number of Clicks per search, however this is not true. The CTR is simply defined as the number of Clicks divided by the number of Impressions. The distinction is very important and greatly affects our interpretation of the results to follow.## Part 2: Generate the CTR Curve

#### Step 1: Make a Scatter Plot

We are going to make a scatter plot showing CTR as a function of Position. Do this as you see fit for your version of Excel; I find it easiest to cut and paste the CTR data to the right of the Position data (allowing the axes to come out right) and then select both columns and click Recommended Charts -> Scatter from the Insert tab. After labeling and selecting your favorite style you should be left with something like this: Increasing marker transparency allows you to get that heat map effect.#### Step 2: Add a Trend Line

An easy way to generate the CTR curve is by adding a trend line. From the Chart Design tab we can select Add Chart Element -> Trendline -> More Trendline Options and then select the Power trend line. The Power option may be grayed out, which happens when you have CTR values equal to 0. In this case you can filter them out: select all of the data, click Filter in the Data tab, and filter on CTR > 0. Alternatively, you may find a decent fit from the Logarithmic option which will not be grayed. As promised, we have calculated your site’s custom CTR curve! If this isn’t a satisfying result for you, then don’t worry; we’ll take a different approach to the problem and generate a new curve later in the guide. However, if you’re at least mildly pleased with this result then carry on as we identify outliners to find your site’s best and worst performing pages in terms of CTR.## Part 3: Calculate Residuals to find Outliers

#### Step 1: Do the Calculation

The residual values are defined as $latex \,r_i = y_i - \hat{y}_i\,&s=1$ where $latex \,y\,&s=1$ is the actual CTR, $latex \,\hat{y}\,&s=1$ is the predicted CTR and $latex \,i\,&s=1$ identifies the data point. For my Power trend-line, Excel calculated the predicted CTR as$latex \hat{y} = 0.1966x^{-0.735}&s=1$

where $latex \,x\,&s=1$ is the Position. With this in mind, we can calculate the residuals for each row with the Excel formula “= [CTR] - (0.1966*[Position]^(-0.735))” where [CTR] and [Position] should be changed to the corresponding cells in each row. Make sure to use the coefficients for your trend line in place of mine.
#### Step 2: Make a Scatter Plot to Check your Calculation

Plotting the residuals as a function of Position you should see something like this:#### Step 3: Identify and Analyze Outliers

The residuals quantify how well each data point fits the trend line. For a dataset with perfect fit, each residual value would be zero. In our case, where the target variable is CTR, the residuals can be interpreted as follows:- Large positive residual = higher CTR than predicted, page is performing well for given search term
- Large negative residual = lower CTR than predicted, page is performing poorly for given search term

## Part 4: Project the Impact of Ranking Changes

It can be desirable to report predicted impacts of SEO based on keyword-ranking changes. This might involve projecting how many more clicks a Query and Page combination will get if the Position is increased by a given amount. It’s tempting in this situation to defer to outdated CTR data such as that from AOL’s 2006 leak: Or better yet, something like this. However, I prefer to base my projections on data coming directly from the site, using my own calculations. If you have come this far, then it really isn’t very difficult.#### Step 1: Round Positions to the Nearest Integer

Firstly, we need to discretize the Position data; let’s add a new column for this using the formula “=ROUND([Position], 0)” where [Position] should be replaced with the corresponding cell for each row.#### Step 2: Aggregate Data using a Pivot Table

Next, we are going to use a Pivot table to calculate the average CTR for each discrete Position. Select all the data and click PivotTable in the Insert tab (you can filter out rows under some threshold for Impressions first). Click “OK” on the popup dialog to add the pivot table to a new tab. You will be presented with a new dialog where metrics and dimensions can be dragged into different boxes. I have done this as seen below, dragging the CTR field three times into the Values box and changing the type of aggregation for each by clicking on the circular i’s.#### Step 3: Plot the Results

By including the standard deviation, we see how well the average CTR for each Position should be trusted. Using these standard deviations values to set custom error bars, my discrete CTR curve looks like this: For this data, as can be seen, the Position 1 queries average a CTR of about 37% and the error bars tell us that 68% of these queries have CTR between roughly 10% and 63%. The CTR for other Positions should be interpreted analogously.#### Step 4: Forecast the Impact of Ranking Changes

There are two approaches I have used in the past, in each case the goal is to estimate the number of Clicks for a keyword given its forecasted Position. In either case there is an element of guesswork involved. The Impressions method goes as follows:- Get the number of Impressions for the keyword at its current Position.
- Project the number of Impressions at the forecasted Positon.
- Use the average CTR at the forecasted Position along with the projected number of Impressions to forecast the number of Clicks.

- Get the estimated Search Volume for the keyword.
- Convert the CTR from Clicks / Impressions (as visualized above) to Clicks / Search Volume. This can be done using an ad-hoc damping factor as seen below.
- Use the newly calculated Clicks / Search CTR along with the projected keyword Position to forecast the number of Clicks.

- 1 Impression for the Positions 1-4
- 1/2 Impression for Positions 5-7
- 1/4 Impression for Position 8-10