🔥 Note: The weekly How the F*ck newsletter will be written by a guest author throughout August. This week, Tim Hanson is taking over the show. Tim's a good friend and one of the people I most trust in the SEO and content strategy space. Need a strategist or coach to push your content team to the next level? Tim is your guy.
If you’ve been following Ben’s amazing content for any time you’ll know about the benefits of content at scale.
As much as I love scaled content production, this brings along its own issues.
One big one is...keyword prioritisation.
I’ve seen far too many great content strategies get lost in a spreadsheet of 10k keywords with no idea what to do next.
I’m Tim Hanson, owner of fivethreeoh. I’ve built content strategies for 50+ series A-E SaaS clients. At this point, I’ve organised and prioritised a few million keywords to help make my clients over £23m in revenue from organic traffic in the last 3 years.
I used to do this manually. Over 5 days. Now it takes an hour.
In this newsletter, I'll show you how I do it for my clients.
I’ll take a list of 10k+ keywords and prioritize them so that:
- Easy wins come first
- They're aligned to business goals
- Whilst also giving the difficult content the time it needs to rank
We'll need a few things: A keyword list, some data against those keywords, and about an hour of time.
This one hour will save me months in flopping around, back and forth, making no headway.
I’ll then create a formula in Google Sheets to spit out a number based on the various inputs I’m gonna give it.
- Those inputs depend on what I’ve gotten from the tools we’ve used, search vol, cpc, cps, a keyword difficulty score, an “SEO” score, trending data, how many keywords are being considered in the same search, SERP overlap, etc.
- I’ll need to know what my goals for this content are, too. Is it traffic? Is it conversion? Topical Authority? I’ll need to chat with my client to get straight on this.
With this data, I’ll create a series of prompts to help me work through this problem and give me a formula that works best for me, the data I have, and the goals I have.
🔥 A quick note on data sources. You’re going to want to get them all into one spreadsheet. Whichever way you wish to pull all the data into one spreadsheet is up to you. I got real familiar with array vLookups. You choose your poison.
Now, let’s have a chat with our favourite AI chatbot.
I’m gonna use ChatGPT.
Prompting ChatGPT Like a Consultant
I’ll use an approach to prompting I like to call the consultation.
I’ll tell ChatGPT what I want, and it’s going to ask me a series of questions related to the problem. Upon getting an answer it’ll tell me how close to a solution it is. I like this because I’ll have multiple chances to build an answer instead of just throwing shit at the wall and seeing what sticks.
“You are a world class SEO and topic researcher capable of creating custom prioritisation formulas for keyword lists with thousands of entries. I have a list of keywords that you’re going to help me score.
You will ask me any number of questions you need to score these keywords based on the data I have for the keywords and the goals I have for this list.
This is the data I have
- Keyword difficulty
- Keyword volume
- CPC
- CPS
- Etc
On each step, you will give me a score out of 100 to let me know how close you are to getting an answer. Once you are over 90 you can start giving me formulas for me to test.
The formula needs to work in Google Sheets/excel”
Throw the prompt in and hit go:
The prompt will ask you a few questions to get an idea of exactly what you want to prioritise and what your goals are.
It'll then develop a prioritisation formula for you based on your goals, which you can plug directly into Google Sheets.
After moving through the questions I end up with this conversation—read through to see how I got to my final formula (and asked ChatGPT to turn it into a GSheets formula ready-to-use).
Setting Rules for Your Prioritisation Score
I added the formula to Google Sheets, and it wasn’t quite what I wanted. So I went back with a few more suggestions.
Ultimately, prompting ChatGPT with these rules got me to a formula I was happy with:
- Direct it how to care about Search Vol/KD to help work out if the effort to rank is worth the search volume. Is the juice worth the squeeze?
- If I rank for a keyword, then I add a small scoring bonus. This is to help me highlight places we already have trust. So if you have your own ranking data you could give a bonus to pages between 8-25 to show the low-hanging fruits.
- Ask ChatGPT how to improve its "confidence score" and answer the questions provided.
Ideas that could further improve the formula:
- You could go further and if you don’t rank at all, then add a penalty, a minus to the score. To show how much more work you need to do.
- A complete 180 to this might be that if you want to highlight and prioritise keywords where you don’t rank to show content and keyword gaps, and as such your scoring would be totally different.
- Maybe a bonus to large clusters of keywords, if you have that data, so the more keywords you could potentially rank for, the higher the score
- A note on this, if you have the search vol for a whole cluster, you could score the cluster and not just each keyword.
We all have different goals, different data, different time scales, and different publishing schedules.
So, I suggest building a prioritisation score around your own needs and using it to direct your content strategy.
Where I Got Stuck and How I Overcame It
Search Volume Outliers
I found anything with over 10k search volume was skewing the final score significantly. So I asked GPT what to do about this.
I ended up adding a log function to help normalise these better.
Dividing or Multiplying by 0
I had issues when there was no data or the search volume was "0". So I added a contingency for this: "if 0 then change it to 0.01 to help avoid any divide by, or multiply by zero errors."
The End Product (So Far)
Here's the final Google Sheets formula:
= (LOG(SearchVol + 1) * 0.5 * IF(OR(ISBLANK(CPC), CPC=0), 0.01, CPC)/0.23 * (SV / IF(OR(ISBLANK(KD), KD=0), 1, KD)))
+ (IF(OR(ISBLANK(CPS), CPS=0), 0.01, CPS) * 3)
+ IF(OR(ISBLANK(TO), TO=0), 0.01, TO)
- (10/IF(OR(ISBLANK(CR), CR=0), 10.01, CR))
+ IF(OR(ISBLANK(TD), TD=0), 0, IF(TD > 0, 3, 0))
+ IF(AND(CR <= 30, CR > 10), 3, IF(CR <= 10, 5, 0))
⬆️ That’s a lot, so let me break it out, line by line
Search Vol as a log function
Log(SearchVol + 1)
This takes the natural logarithm of the search volume, incremented by 1. The +1 is to ensure the logarithm is defined for when Search Vol =0.
Logarithm scaling reduces the impact of crazy-high search volumes.
Cost Per Click (CPC) Adjustment with SV/KD ratio
* 0.5 * IF(OR(ISBLANK(CPC), CPC=0), 0.01, CPC)/0.23 * (SV / IF(OR(ISBLANK(KD), KD=0), 0.01, KD))
This part does a few things:
- It multiplies the log-scaled SV by 0.5 to reduce its weight.
- It then multiplies by the CPC (from column O). If the CPC is blank or zero, it uses a small constant (0.01) instead.
- This product is then divided by 0.23, which seems to be a constant you've chosen, possibly to adjust the scale or weight.
- Finally, it multiplies by the ratio of SV to Keyword Difficulty (KD from column P). If KD is blank or zero, it uses a small constant (0.01) to avoid division by zero.
Click Per Search (CPS) Weighting
+ (IF(OR(ISBLANK(CPS), CPS=0), 0.01, CPS) * 3)
This takes the CPS value and multiples it by 3 to increase its weighting. Also, a small constant was added to stop dividing by zero issues.
Traffic Opportunity with adjustment
+ IF(OR(ISBLANK(TO), TO=0), 0.01, TO)
Current Rank (CR) Inverse Weighting
This part inversely weights the current rank. The lower the rank (closer to position 1) the higher score it has. If the score is 0, or not ranking, then it’s set to 100.
- (10/IF(OR(ISBLANK(CR), CR=0), 100, CR))
Trending Data Bonus
This is to give a bonus based on if the keyword is trending. If positive, then we add a bonus of 3. If zero or negative then no bonus is added.
+ IF(OR(ISBLANK(TD), TD=0), 0, IF(TD > 0, 3, 0))
And finally, a nice Ranking Bonus
This gives a bonus based on the current rank, a bonus if it's the first page. Another smaller bonus if it’s page 3 or higher.
No bonus otherwise.
+ IF(AND(CR <= 30, CR> 10), 3, IF(CR <= 10, 5, 0))
Here, CR = Current rank
Where I Found Each Bit of Data
- Search vol - Ahrefs
- Keyword difficulty - ahrefs
- CPC - Keywords Everywhere
- Current rank - Ahrefs + Search Console
- Clustering - Keyword insights
- Clicks per search - Ahrefs
- Trending - Keywords Everywhere
- Traffic Opp - Custom formula based on CTR from different positions
After a bit of back and forth, I ended up with something custom. A prioritised list. And a way forward. It won’t be perfect. I’m not saying it will be. What I am saying is you’ll have a much better direction than you did before.
