When only about 20% of your content is netting you approximately 80% of your results, then it becomes obvious that the secret to effective content marketing is quality, not quantity. In this four-part series, I’ve shared with you the first four steps that I use to identify which pieces of content are worth the effort (and which ones aren’t), so that everything you post or publish can actively contribute to your success. Now, in part four, I’ll show you how to finish up the process.
Step 5. Bring it all together
Remember the sheet I had you make in the beginning called “master breakdown”? It’s time to track that down and pull it up. Because Google Sheets and Excel tend to run more slowly as the data sets get larger, I like to put the final breakdown on a new sheet that isn’t correlating massive amounts of data from all the positions tabs.
Once you have your Content KOB tab for each competitor, you’ll want to select all of it and paste special-values only (ctrl + shift + v) onto your master content sheet.
Then add a final column called “KOB.”
Do this for all your competitors until this master sheet has all the unique URLs for all your competitors with this data populated:
- # of KWs in top 10 (empty)
- # of KWs in top 20 (empty)
- Estimated Traffic (empty)
- Average Difficulty (empty)
- URL Ahrefs Backlinks
- URL Ahrefs Ref Domains
- URL Total Shares
- Domain Ahrefs Backlinks
- Domain Ahrefs Ref Domains
Now, in the last column you created called “KOB,” we are going to put in our final formula. “KOB” is a term derived from “keyword over benefit” which is often used in KW research to get the KWs with the most search and the least amount of competition to sort to the top.
Traditionally, that formula looks like this: (Volume*CPC)/Difficulty
We are going to use the same idea, but with a deceptively complicated looking formula (that is, in fact, still fairly simple).
Remember the whole point of this is to find that 20% of content that bringing in 80% of traffic, industry wide. The more sites you crawled during your preparation phase, the better the results are going to be.
This is the formula I use:
(((#kws in top 10*2)+# of kws in top 20+(estimated traffic*4))/((average difficutly)+(# of URL inbound links*4)+(# of URL referring domains*3)+total social shares+# of root domain links+(# of root domain referring domains*2)))*1000
Or if your sheet is set up the same way as mine:
Now before you get intimidated, let’s break this down. Keep in mind this is my formula and it can definitely be improved. I will simply explain the method behind the madness, but feel free to change the weights to reflect what SEO metrics you think are most important.
It’s easiest to think of the formula as two sides. The overall benefit or performance of the post/page divided by the overall competition level of the page/domain the content resides on. See below:
[ BENEFIT ] [ DIFFICULTY ]
Let’s first breakdown the first section, or the “benefit” section of the formula:
- I weight #kws in top 10 by 2, because 1st page KWs are most important.
- I leave# of kws in top 20 as is because I simply want to factor the potential to improve the rankings with savvy link building.
- I weight estimated traffic by 4 because this is the most important metric. How much actual traffic is coming in because of the content? If you have 200 KWs in the top 10, but each KW has 2 searches a month, then this doesn’t mean much.
* note: I have started adding average CPC of the kWs, as higher CPC indicates that the kWs are more likely to be driving monetizable traffic. If the PPC market is willing to pay a higher price for the KW, then it’s usually because those people searching are likely to convert. If you want to do this, simply tweak the “average difficulty” formula. When I do this, I weight CPC by 3.
Now the “difficulty” section of the formula:
- average difficulty I don’t weigh as it’s not very accurate. It’s little more than an average, but does offer a numerical hint on the competition level
- # of URL inbound links I weight by 4, because the most important indicator of competition is links to the actual page. If I am making new content based on these ideas, I will be starting with 0 links. I don’t want to play catch up if the post has 100k links.
- # of URL referring domains I weight by 3. Much like above, inbound links to the URL matter, as does how many different domains are doing the linking.
- total social shares I don’t weight at all because sometimes this number may be disproportionately large, and I’m not sure social shares play that much of a role in organic rankings, anyway.
- # of root domain links I don’t weigh, but I put in because the domain the post is on does matter very much. Some posts may be ranking with no links to the actual URL, because they reside on a monster domain with millions of links just funneling extreme amounts of link juice (that I can’t compete with).
- # of root domain referring domains I weigh this by 2 because I think # of referring domains on the domain level heavily influences the overall power of the domain and how much link juice is getting passed onto the actual page/post from the domain.
- I multiply the final number by anywhere from 1k to 100k just to make the final number not be some long decimal number that is harder to understand. This is just for convenience sake.
Lastly, I post the formula in (ctrl + shift + down, then ctrl + enter, which applies the formula all the way down on the KOB column).
Sort by Z–A, and boom! Now you’ve got a data based, 80/20 rule strategic list of content for your brainstorming sessions.
Find which ones resonate with you, and feel free to go all out on improving, refining and making better content than your competition because you know that the work you put into these pieces have an extremely high likelihood to perform very well.