Whether you’re working on SEO or paid search, you’d probably agree that keyword research is a hairy beast. It’s slow, manual, and well… boring.

But what if we told you that you can automate the process and get a list of hundreds of relevant keywords and your current ranking data flowing directly into your Google Sheet in under 5 minutes?

Cool, right? Here’s our friend Ross Tavendale from Type A Media showing you how.

Ingredients

To follow along this tutorial, you’ll need:

Psst! If you haven’t tried Supermetrics for Google Sheets before, you can start your free 14-day trial right now. You don’t need a credit card to get started and there are absolutely no strings attached.

Get started with Supermetrics

Try Supermetrics for Google Sheets for free with a 14-day trial.

Start free trial

Got it? Let’s go.

Step 1: Populate your top-level keyword categories

The very first thing you’ll want to do is to open the tab called “Setup – Start Here”. This is your top-level keyword list.

top level keyword list in Google Sheets

Next, you’ll want to populate this list with core search terms relevant to your business.

So for an SEO agency like Type A Media, this list would go a little something like:

  • SEO
  • PPC
  • Google Ads
  • Paid search

And for a SaaS company in the marketing data space like Supermetrics, it would be more like:

  • Marketing analytics
  • Data warehouse
  • Google Data Studio
  • BigQuery
  • Snowflake

Psst! Especially if you’re doing keyword research for a new agency client, you’ll want to keep your top-level keywords broad (and your mind open). 

Remember: you can always come back and remove or add keywords from this list later so don’t worry about it too much.

(And if you haven’t already, now is a good time to authenticate SEMrush as a data source. You can do this by launching the Supermetrics sidebar, choosing SEMrush as your data source, and following the on-screen instructions.)

When you’re happy with your top-level keywords (and you’ve authenticated SEMrush), the last thing you’ll want to do is go to “Add-ons” >> “Supermetrics” >> “Refresh all”.

Refresh Supermetrics queries in Google Sheets

Once your sheet has refreshed, you’ll see three new tabs:

  • ph_semrush, which contains phrase match keywords to your top-level keywords from SEMrush
  • rl_semrush, which contains related keywords to your top-level keywords from SEMrush
  • categorise, which automatically groups your keywords into the buckets you defined on the setup tab

Cool, right? Moving on.

Step 2: Pull keyword ranking data from Google Search Console

Now that you have your keyword list, you’ll want to find out whether your website is currently ranking for those keywords. 

Simply open the “SC keywords” tab, which will have refreshed automatically when you refreshed your queries earlier. 

(And psst! If you’re using Supermetrics for the first time, make sure to actually open the sidebar, authenticate GSC as a data source and change the source to your client’s or your own website.)

Supermetrics query in Google Sheets

Once you’ve refreshed the query, the tab should populate with the search queries your site is already ranking for, the corresponding landing pages, keyword impressions, CTR, and average position.

Google sheets keyword research template

That’s pretty neat, right?

Step 3: Edit your keywords

Next, you’ll want to navigate to your “edit these keywords” tab. This just so happens to be the only manual piece of data in your spreadsheet.

So go back to your “categorise” tab and copy the entire table. Then, move back to your “edit these keywords” tab and simply paste the table you just copied there.

You’ll end up with a mess like this:

Edit keywords tab in Google Sheets automated keyword research template

But because nobody likes a messy spreadsheet, your next step is to clean it up a little.

Like so:

  • Move the “volume” column to the right side of the keyword column
  • Rename “January” as “difficulty”
  • Move the old “competition” column (which is actually seasonality) into the far right (to the right side of the new “difficulty” column)
  • Delete the old Feb to December data
  • And split the “seasonality” column into months by choosing the column, then opening the “Data” menu and choosing “Split text to columns”
  • Rename the columns for each month

After all this hard work, your “edit these keywords” table will look like this:

Reorganized edit keywords tab in Google Sheets

You might also want to sort the list based on the category, after which it’s time to start going through your keyword list manually to see if the keywords you’ve gotten make sense.

Once you find less relevant keywords (like “car analytics” — WTF is even that?), feel free to just delete the entire row. Aaaand rinse and repeat until you’ve gone through every single row.

When you’re done, it’s time to pat yourself on the back because you’re so close to being done!

Step 4: Assess your SEO opportunities

Now open the “opportunity” tab, where you should see your data populated (for the most part).

SEO opportunities tab in Google Sheets keyword research template

The only exception is your AOV (or average order value), which you will have to change yourself. 

As you can see, this sheet doubles as a handy ROI calculator to your boss or client. While it’s good to note that the numbers are averages, it’s a quick and dirty way of demonstrating the potential of your future SEO work.

Once you’re done playing around with this sheet, there’s a couple more cool things you’ll want to check.

Step 5: Plan for seasonality

It should come as no surprise to anyone that search volumes for specific keywords can fluctuate throughout any given year. 

And that’s why the “seasonality” tab exists. It’ll help you identify opportunities for specific keyword groups.

Like so:

Seasonality in Google Sheets keyword research template

From here, you can clearly see that based on search volume trends, September might be a good time to go all in on “content marketing” related campaigns.

By toggling with the keyword category dropdown, you can check the seasonality trends of each of your keyword groups.

Step 6: Identify keywords to target

Finally, you’ll want to open the real goldmine, i.e. the “Keyword list” tab.

How to find keywords to target

This is where the magic happens. This tab will show all the keywords you’re already ranking for, the corresponding landing pages, and your average position in the SERPs.

Absolute gold, I tell you. 🥇

From here, you can quickly identify opportunities for new keywords to target.

That’s literally it

Now that you know how to automate keyword research, Ross and I can call it a day.

Don’t forget that you can grab your free 14-day trial of Supermetrics for Google Sheets to get started with automating your marketing data workflows. 👇

Get started with Supermetrics

Try Supermetrics for Google Sheets for free with a 14-day trial.

Start free trial

Try Supermetrics for free

Get full access to Supermetrics with a 14-day free trial.
No credit card required.