How to automate keyword research and get it done in 5 minutes or less

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:

• Google Sheets — and more specifically, a copy of this template
• The Supermetrics add-on for Google Sheets
• Access to Semrush API
• Google Search Console

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.

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.

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”.

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.)

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.

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:

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:

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).

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:

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.

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. ?