How to improve organic CTR with Google Sheets

Last Updated on January 15, 2021 by Jake Sheridan

This template helps you improve CTR by finding which pages could benefit from an updated title tag and meta description.

High rankings are great right?

Course they are. But what if you are ranking your pages, but no one seems to be visiting them?

SEO isn’t just about ranking your pages. Some would say search ranking positions are just a vanity metric. But that’s a whole can of worms for another day.

If not enough people are clicking your search snippet, you need to fix up your CTR.

Hello CTR 👋

Organic CTR (click-through-rate) is the percentage of people who click on your site in the SERPs.

CTR, rankings and traffic are all connected. Data suggests that improving your CTR by just 3% will move you up the rankings. It’s also a great way to get more traffic without writing any more content.

Enough talk, more action:

How to improve organic CTR with Google Sheets

SEO metadata is a way for your website to talk to Google. It’s what appears in the SERPs and it lives behind the scenes in the HTML of your pages.

To see your CTR, go to Search Console and head over to performance. There, you’ll be able to see your average CTR across your site:

You can also scroll down to your CTR at a page or query level. But who’s got time to scroll? Panic not fellow lazy marketers, you can pull all this data right into a Google Sheet with a few simple formulas.

Ready, let’s go.

Finding underperforming title tags + meta descriptions

When it comes to optimising your SERP snippet, as I’m sure you know, there’s two elements you want to focus on: Title Tags & Meta Descriptions.

Thankfully, the Title + Meta Opportunity Finder makes finding the on-page optimisation opportunities easy. It finds opportunities to fix up your titles and metas for you like a faithful spreadsheet based dog.*

*not an actual dog.

It shows queries and landing page combinations that receive a high number of impressions but have a low CTR.

The spreadsheet also pulls in title tag, meta description and character counts, so you can quickly see which search snippets can be improved to increase CTR. Nice 👌

How to use the sheet

To get it setup…

What you’ll need:

  • Google Search Console setup for your site.
  • Search Analytics for sheets add-on installed.
  • Screaming Frog to grab your titles + metas.
  • A Google account (obviously) so you can make a copy of this template:

Grab the Title + Meta Opportunity Finder Sheet

Step 1 – Setting up the sheet

So you’ve made a copy of this sheet.

You’ll need Search Analytics for Sheets installed to start.

If you don’t: go to “Add-ons,” then “”Get add-ons…” and search for “search analytics for sheets.”

Step 2 – Pulling in the GSC data

Configure the search analytics for sheets add-on like so:

  • Pick your site you want to analyse
  • Select your date range
  • Group by query + page
  • Choose GSC RAW as the results page.

It should look something like…

NOTE: Limiting the Rows returned to 1000 rows is optional. Pulling in all the rows for a big site can slow down a sheet.

Once it’s setup like that, hit 👉 Request data.

Step 3 – Pulling in current on-page data

Now you want to get your current on-page optimisation in the sheet.

The quickest and easiest way to do this is to put your site in Screaming Frog and crawl away. (you can crawl up to 500 URLs for free btw)

Put your website in and hit start.

When it’s done, export your results and paste them into the SF Export tab.

How to import csv files into Google Sheets:

  • Download the csv of the results
  • Go to SF Export tab
  • File >>> Import and choose you file
  • Once it’s uploaded you’ll see import options. Choose 👉 Replace current sheet
  • Hit import data

Step 4 – Analyse your data

Now the sheet will do it’s thing (using the magical Query function) and combine the data from Google Search Console with your on-page SEO (cheers Screaming Frog & VLOOKUPs).

Give it a few minutes to load then head over to the “Analysis” tab to see which pages could use updated metadata.

Psssst. Hey you. Don’t forget to sign up to the newsletter for more content like this. Or don’t, that’s totally cool too. 👌
[yikes-mailchimp form=”3″]

Now what?

So you’ve found your improvement targets, now what?

Well it’s now time to improve our page titles and metas.

Your page titles and meta descriptions are key to telling users (and Google) what they will be getting from your website. You want metadata consistent with the topic of your site.

Here’s some quick tips to help fix underperforming pages:

Title tags optimisation tips

  • Use a CTA to make people want to click through
  • Use numbers to stand out
  • Use the relevant keywords (duh)
  • Use the right length, aim for 60 characters
  • Ask questions to engage people

Meta descriptions optimisation tips

  • Keep it around 150 characters or less
  • Tell people what they can expect from your content (your value proposition).
  • Intrigue people into clicking your SERP
  • Add a CTA if you can

The takeaway from optimising your page titles and meta descriptions: nothing is set in stone. Test, monitor & adapt.

See what works, then do more of that.

More on-page SEO resources

Finding what pages could benefit from updated page titles and meta descriptions is only half the battle. If you want to dig deeper into improving your optimisation skills, these are good places to start:

Don’t forget to grab a copy of the sheet here to help you improve your organic CTR. Also, checkout the directory of Google Sheets templates to help you automate other SEO tasks.