How to Find Quick Win Keywords With Search Console Data in Google Sheets

Sometimes the student becomes the teacher.

This is a glorious guest post from CIFL acolyte Jake Sheridan, a marketer, writer and spreadsheet wrangler from Nottingham, UK.

He’ll walk you through how to use Search Console data to find quick win keywords for your site.  Take it away Jake!

With love,

David

Commissioner, Coding is for Losers

If you are lazy efficient, APIs are designed for you.

Add in a spreadsheet and you’ll get a lot more done.

Like, a lot more.

In this article I’ll be walking through how I use APIs + spreadsheet to find quick win keywords to go after for as part of an SEO campaign.

That’s why I decided to build this tool (cough; spreadsheet):

To speed up keyword prioritization for me.

So I can spend more time working on an SEO strategy (producing content, building links and drinking coffee).

Some say lazy, I say efficient.

What are quick win keywords? (and why should I care)

When I refer to quick win keywords, I mean keywords you rank on the first/second page for (average position of 5-20) but not within the top positions.

Got it? Let’s go.

Enter the machine (technically it’s a spreadsheet)

Right. It’s time to dive in and see what actions the tool allows you to take.

I really like quick win keywords. Often, you can just make a few tweaks and some some good results. These are really good terms to go after in the early stages of a campaign to show a client you know what you are doing.

It’s not a guarantee though. Uber competitive keywords are still going to require more work. There is no magic SEO pill.

Just a couple of things you can do to optimise your pages for quick wins:

  • Check On-Page SEO: Make sure your pages are optimised as they should be (title tags, keyword placement in headings and subheadings, copy, alt tags).
  • Improve content: Rework, rewrite or just make your content more relevant.
  • Internal Links: Grab a list of the most linked pages of your site (HINT: in Search Console). Add links on those pages back to the pages you want to rank higher. Make sure to take care with your anchor texts.
  • Link Building: Links, go build them. Point them at the URLs you want to rank higher. Don’t overdo it with exact match anchors and try to be natural.
  • Get Social: While social shares isn’t a direct ranking factor, good content gets shared.

But what about results?

Yeah yeah, I can hear you: quick win keywords sound great, but do they actually work?

And will they work for my client in a ‘boring’ industry?

Absolutely.*

*Obviously, I can’t say for certain they will work 100% for you, there’s too many factors to consider like budget, writing skill, industry etc. But in my experience, they work.

Check out the results:

This was for an SEO client I worked on, targeting both B2B and B2C terms. I had a small budget, most of which went towards producing informational content.

This wasn’t flashy content. Just articles that answered a query directly related to a question a potential customer might have.

Without the budget to push some links at these pieces, the results were ok, but not great.

So in January 2018, armed with my quick win keywords, I began improving the existing ranking content.

The effects on organic traffic were pretty good:

organic traffic quick wins

Likewise, the amount of keywords increased too:

organic traffic quick wins

I didn’t do anything fancy, just improved the readability of the content by breaking up large bodies of text and a better use of subheadings. Where it made sense, I added some extra content.

Overall, I just ensured that the content addressed the search query that would of brought the potential customer to the page via Google.

Quick win keywords FTW!

How it works & setup instructions

Go grab yourself a copy here.

To get it working, you only need 3 things to get the tool setup:

  • Google Sheets
  • Search Console Add-on (for sheets)
  • And a website (with GCS setup obviously)

If you don’t know what Google sheets is, why not? Start using it now. That’s an order. Go checkout the the CiFL lessons to supercharge your laziness digital marketing.

Sheets is great, it’s a cloud based spreadsheet that you can use for loads of different jobs. There’s a great library of free add-ons available which really boost what you can do within a spreadsheet.

Speaking of which…

Next up is a really great add on for sheets called: Search Console Add-on for Sheets. Seriously, it’s great.

This is a free add-on for Google Sheets. It lets you schedule the backup of Search Console data into a spreadsheet. For free! Go get it here, you’ll need it to build you Priority Machine. Did I mention it is free?

Side note: Obviously, this is all pointless unless you have a website to pull data from. The tool is designed to use keyword data for rankings you already have. So if your site is brand new, this might not be that helpful for you yet. Come back and use the tool after you have produced some content.

Likewise, if you haven’t setup Search Console, this won’t be much use to you.

That’s kinda obvious, but if you haven’t set it, go do it now! This video will show you how to set it up real quick.

The sheet works in two ways:

  1. Automated via Search Console.
  2. Manual via rank tracker of your choosing.

Automated setup

1) Install this add-on to pull in Search Console data: searchanalyticsforsheets.com

2) Configure the add-on:

  • Authorize your sheet
  • Go to Add-ons >>> Search Analytics for Sheets >>> Open Sidebar
  • Make sure your Google account is authorized to use the Search Console property you want to back up!
  • Authorize.
  • Choose your site.
  • Add Query & Page to the Group By box:

pngbase6448cdab4617fbe3d5

  • In Results Sheet, select ‘RAW DATA’ :

pngbase64512d11acf24aa19a

  • (If you don’t select the RAW DATA tab, it won’t work. 👎)
  • Request data. This will get the spreadsheet goblins working and you should see:

pngbase64838d2f8f875488fd

3) Go look at your quick win keywords! 👍

The Quick Wins tab will show you keywords ranking positions 5 – 20. Go and check on-page SEO, improve content, add internal links, or start link building to improve these pages.

Manual setup

If you don’t want to use Search Console data, you can also use this tool manually.

Just export your rankings from your tool of choice.

Make sure you only have  3 columns of data:

  1. Keyword
  2. URL
  3. Rank

Paste these 3 columns into the MANUAL tab.

Once you’ve pasted in your keywords, your data will populate in Quick Wins [Manual]  👍

Wrapping up

That is all. Get yourself a copy of the Quick Win Keyword Finder and go forth and optimize!

If you’re ready to go deeper into automation (and why the hell not) check out all of the Coding is for Losers courses on mastering Google Sheets.

6 Comments

  1. Very nice work Jake. I already had our site authorized in Search Console, so all I had to do was set up the Search Console Add-On for Sheets and BAM! Done in less than 5 minutes. And thank you, Commissioner, for sharing the guest post.

  2. Great template. I’ve got my hands in it right now.

    I’m starting to see the light – the way of the Lazy (efficient).

    Thanks!

  3. Hey guys just wanted to give you a heads up that there is a small mistake in the worksheet that is having a big impact on the results. Under the “Clean Data” tab the formula should be =IF(AND(F2>5,F2<20),"🔥 Quick wins","Not today my friend.") but somehow some (many) of the cells have "🔥 Quicks wins" (with the s) so they are excluded from the "Quick Wins" tab. Other than that love it and thanks for sharing.

Submit a comment