Keyword Research. Whether it’s for SEO or Paid Search…
… really kinda sucks, doesn’t it? 😟
I think it’s incredibly boring and tedious: Definitely not my favourite part of being an SEO freelancer. But also, it’s super important!
So what can we do? 🧐
To make my own life easier, I’ve created a template in Google Sheets which allows me to do keyword research at least 5X faster, with better results.
I’ve used it with dozens of my clients in the last 12 months – and it’s never let me down. Over time, I made a lot of improvements and I decided that my baby is ready to see the world.
So today I’m sharing it with you… 100% free. 🥳🥳🥳
There are just 2 things you need to use it:
- 19 mins time to watch my instruction video on YouTube. (Pro tip: watch it on 1.5-2X and you’ll need even less time. 🤓)
- A SEMRush Pro, Guru or Business Plan (or trial). If you don’t have one, you can sign up here to get a free 7 day trial with no strings attached: Click Here to Sign Up (Disclaimer: I’ll receive a small commission if you decide to pay for it)
Sounds awesome, where can I start?
First, watch my video for instructions:
Access the sheet here: Click here and make a copy of the sheet
Can you tell me how this works before I waste 20 mins of my life?
Only 19 mins! But hey, I understand! Stay with me…
What is the output of this keyword template?
With this sheet, you will be able to create a list of hundreds to thousands of RELEVANT keywords for your website.
Hundreds to thousands, is that a lot? Yes, and actually QUANTITY isn’t the goal here. This is not a size comparison!
What my template allows you is to actually FILTER your keywords quickly to a manageable number. Some may like more, some less – this is totally up to you. I usually work with around 500 keywords, but this depends on the size of your website.
Here’s a little teaser of the output…
Is it just keywords, or will I have more data?
Great question amigo! 🤠
There’s A LOT more data that you’ll have available. This is a sheet for the geeks, misfits and data noodlers out there.
Here’s what you’ll have in one neat overview sheet for all of your keywords:
- The search volume
- The keyword difficulty
- Paid Search metrics such as Competition & CPC
- Number of search results
- What is the highest rank of any of the domains you’ve put in (a maximum of 6 including your own can be compared)
- What is the rank for each domain, and how much traffic do these domains receive for each term
But isn’t that just SEMRush data I have anyway?
Yap, there’s no magic new data here. 🤗
What the sheet does is saving you about 16 steps in between, and putting it all into 1 sheet almost fully automatically:
- No need to de-duplicate keywords
- No need to manually combine your competitors ranking data
- Easy filters to narrow down your keyword selection
- A table you can use directly to create graphs for Share of Voice, Keyword Opportunity etc.
In short, saving you a hell lot of time… 👹
You’ve mentioned filters. What exactly can I filter my data on?
I’m glad you ask, my friend. 🤓
You’ll be able to filter based on all important SEMRush metrics, plus a little magic dust 🧙♂️:
- Monthly Search Volume (sometimes size does count)
- Min. & Max. Keyword Difficulty (filter for easier opportunities first)
- Min. & Max. CPC (yes, this also works for paid search!)
- Min. & Max. Competition
And additional metrics which you can only easily get with my sheet…🧙♂️
- Filter between Brand, Non-Brand or both keywords
- Filter for Questions only (for English only)
- Filter keywords based on the number of words they consist of
- Filter based on the best rank of any competitor (helps to keep relevancy of your selection higher)
- Filter out NEGATIVE keywords (up to 50!)
Is it really fully automated?
Almost! 😎
The only thing you need to do manually is download a CSV file of ranking data for each of the domains you’re looking at and import it into the sheet.
This only about 5mins. Promise 🤗
But what about Keyword grouping?
You’ve paid attention young padawan, haven’t you? 🤗
Keyword grouping is indeed a feature in my sheet, you might have seen it in my little GIF above 🤩
Is this also automated? NOPE! 😐
Sorry! You’ll still have to group your keywords manually. The sheet makes it a little easier, which I explain at the end of my video.
So far, I have not found a solution to do it (semi-)automatically. Have some good ideas? Leave a comment below!
All this data in Google Sheets, isn’t that going to be super slow?
Yap, it’s not the perfect tool indeed. I’m working on a faster solution as you’re reading this.
But look at the bright side – it’s fully customisable. There are no limits to your creativity and what you can do with this data… 🥳
Right, can I get access now?
🙌Woop woop, I can’t wait for you to try it out!
First, watch my video with instructions: