This guide will show you how to quickly and easily turn a list of keywords into neatly clustered topics based on scraping search engine results.
I’ll show you how to turn a seed keyword into a full topic map. It can be done in under 5 minutes!
This makes turning keyword research into topics effortless.
Example:
Each keyword is grouped based on the URL that ranks #1 for the keyword and sorted by the search volume.
The #2 and #3 URLs are in here for creating content briefs, which I’ll explain toward the end of this guide.
Step 1: Grab a seed keyword
The seed keyword is the big bad keyword you want to rank for and create subtopics around.
For my example, I’ll use “Cold Email”.
I want to find all the subtopics related to cold email to cover more subtopics in this category.
Step 2: Grab all the child keywords
I like to use Answer Socrates. It’s a free alternative to AnswerThePublic that does the same thing. It uses Google’s autosuggest API to find all the questions and variations of a keyword.
Like “Are Cold Emails Effective” and “How To Cold Email For A Job”.
Answer Socrates also automates the Alphabet Soup method. Where you enter your keyword and hit “A” and then it pulls up all the Keyword + A autosuggests.
Like “Cold Email Automation” and “Cold Email Agency”.
- You can also type your keyword into your favorite keyword research tool like Ahrefs, Semrush, Google Keyword Planner, etc.
Download your keywords and open them up in Google Sheets.
- If you used Answer Socrates, run your keywords through a keyword research tool to grab the search volume for them. This will come in handy later.
Step 3: Bulk SERP Scraping
Next you’ll need to scrape the Title tag and URL of the top 10 search results for each keyword.
I built an app that does this for you. It’s available exclusively to AI SEO Academy members.
This is my free Skool group where I house all the AI tools I build. Simply create an account and get instant access.
Customize the location and language, and paste your keyword list in. Run it, wait a few minutes, and download the data.
Then upload this data into a new tab in the same Google Sheet as your keyword list. It should look something like this:
Step 4: Pull in search volume
Add a new tab next to the keyword column named Volume.
Then do a Vlookup to pull in the volume: In cell B2 put this formula: =ifna(VLOOKUP(A2,’KEYWORD_TAB’!A:D,4,false),””)
Replace “KEYWORD_TAB” With the name of your actual tab where your keywords with search volume are located.
You may also need to adjust the “A:D,4” parameters based on how your data is formatted. If you need help with this step, just ask ChatGPT!
- Column A needs to be the keyword. If your keyword column isn’t in column A, change this.
- D needs to be where the volume column is located. If your volume isn’t in this column, change it.
- 4 is how many total columns there are from A-D. In my case, there are 4 columns (A,B,C,D), so I put 4.
Once you have the formula configured, double-click it, or copy/paste it down the rest of the column to pull in the volume data.
Step 5: Pivot Table
Now that you have volume added to your keywords, you just need a pivot table to put all the data together.
Click “Insert” then “Pivot Table” from the dropdown menu.
Here’s the pivot table configuration:
Rows:
- #1 Title
- Keyword (Check Show totals)
- #1 URL
- #2 URL
- #3 URL
Columns: Leave blank
Values: Volume
Then go back to your rows and sort all of them by SUM of Volume Descending.
Your result should look something like this:
View the sample spreadsheet here.
Note: You can change your pivot table configuration if you like.
Explanation of why I configured the pivot table like this:
- The Title of the top-ranking page gives you an idea of what your title should look like
- The keywords listed are all keywords that the #1 result ranks #1 for. This clusters the keywords based on who’s ranking #1 for them.
- The Volume field sorts by the top keyword in each cluster, and shows you the total search volume for each keyword.
- Note: It’s important to remember that these aren’t ALL the keywords each page ranks for. These are just rankings for the keywords you pulled.
Step 6: Remove duds
Not all the topics will be highly relevant and worth creating.
Some of them may be entirely irrelevant. Remove the ones that aren’t a good fit.
But keep in mind that Topical Authority requires good Topical Coverage.
Step 7: Make Content!
Now you have everything you need to plan and prioritize your content production to cover these subtopics.
You can even use my content brief generator to speed up the process.
This app creates detailed content outlines from a keyword and 3 URLs. (Which is why I configured the pivot table the way I did.)
Once you have briefs, you can also use my SEO content Writer to flesh them out into first drafts.
Simple, easy, accurate, SERP-based keyword clustering in a flash.
Join the AI SEO Academy
If you want free access to the SERP scraper, plus 30 other AI-powered SEO tools I’ve built, join the AI SEO Academy.
It’s free, and I provide video tutorials on how to use and customize all the apps.
