Now we are going to focus on building out your personal keyword strategy.
It’s important that we get this right, because this is what we are going to use as a basis to make decisions and then guide our strategy.
There are a lot of things that can go wrong here, so I’m going to guide your through setting up our internal topic research and how to make smart decisions.
My magical topic research sheet is designed to:
- Sort and filter topics by total search volume and average search difficulty
- Automatically sort keywords into target topics
- Analyse new potential topics
- Output a list of the best low-competition, high-traffic topics
- Detect if a featured snippet is possible
- Detect commercial intent keywords
- Remove tyre kicker keywords
- Create intelligent keyword plans
And it does all of that automatically from the keyword data, you export from Ahrefs.
Please watch the video below to properly understand how the sheet functions and WHY it functions that way.
There are 2x versions of the sheet for you to checkout-
- Drone Niche – This is a completed sheet for the drone niche that we will use as an example throughout this course
- BLANK TEMPLATE – This is for you to follow along with
Here’s how to set everything up-
How To Set Up Your Spreadsheet
Firstly, head to File > Make a copy–
To ensure you have a working version saved to your Google Docs.
Then head to Tab #1 and enter your seed topics list from the worksheet in the “How To Brainstorm Topics (Not Keywords!)” lesson.
You’ll then need to run each of these topics through Ahrefs and export the data.
This should be done using the Ahrefs Keyword Explorer tool:
Once you’ve entered the information, export the keywords using the steps from the last lesson.
Then copy and paste the keyword data into Tab #2:
If you are struggling with this please see watch the video further up this page which steps you through it. You should have watched that already if you are paying attention anyway!
With the data loaded, let’s look at how you can unlock the spreadsheet’s real potential…
How To Use Your Spreadsheet
The next tabs are where the magic happens.
In Tab #4, the spreadsheet will automatically extract the best topic ideas based on their:
- Search volume
And categorizes them from “Super-High Priority” through to “Ignore” so you can easily pick out the winners using the checkboxes-
Use this tab to find topics which align with your business.
You can click the “View” checkbox to view all of the keywords within a topic along with their individual search volumes and difficulty-
And once you find a topic that you like…
…all you have to do is click the “Select” checkbox-
I highly suggest you watch the video at the top of this page to learn more about how the sheet functions and why it functions that way. I won’t tell you again!
In the next lesson we’ll go through how to select which topics to target, but for now just get everything setup.
How To Add More Topic Ideas
When performing your research you’re likely to uncover new topics to focus on.
For example, when I was researching “Drone Case” I found a new topic called “Selfly” which is a new type of drone.
To add this to the spreadsheet I’d just repeat these steps:
- Enter the keyword into Ahrefs
- Export the data
- Copy and paste to the end of the data in Tab #2
This will update the data in the following tabs and immediately compare the viability of the new topic and it’s keywords against my existing topics and keywords.
The more time you spend adding topics to this sheet, the better.
Because the more data you have, the more intelligent decisions you can make. So I highly suggest you spend a few hours adding as many topic ideas and keywords as you can.
Because in the next lesson:
We’ll look at how you can supercharge your decision making process to pick the best possible topics and keywords for your content…