When it comes to content marketing, the 80/20 rule suggests that only a small fraction of published content is actually capable of achieving the results you need. Identifying what it is that makes that content great is the key to effective content marketing.
In parts one and two of this series, I began to lay out the process I use to accurately determine which kinds of content are most valuable. In this post, I will continue outlining this process, by showing you how to add position data to your Content KOB tab.
Step 4. Add position tab data to your Content KOB tab
We now have all the data we need for each competitor, so all that’s left is for us to organize it all in a way that's parsable.
To do this, access your content KOB tab, and add four new columns to the right of column A (which should be titled “URL”).
Name these four new columns in this order, from right to left:
- # of KWs in Top 10
- # of KWs in Top 20
- Estimated Traffic
- Average Difficulty
Now your columns should be in this order with the columns 2-5 being empty at the moment
- # 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
For visual reference, it should look like this:
Now we want to have this Content KOB tab pull the needed information from the positions tab on the same sheet. To do this we have to employ some google sheets formula-wizardry.
For “#’s of KWs in top 10” paste in this formula:
This will look up the URL, check the positions tab for that exact URL, and count how many KWs next to the URL are in positions 1–10.
Note: If you’re having problems, double check that you spelled “positions” correctly on the tab name, and that your tab name matches the name in the formula. Next, check that the order of your columns is exactly as I described above.
For “# of KWs in top 20” paste in this similar formula:
For “Estimated Traffic” paste in this formula:
This will pull the estimated traffic for each KW and add it all up. If you're wondering how Ahrefs calculates estimated traffic, it basically estimates how many people click through to the ranking page based on a average percentage that each rank gets of the ranking KWs monthly search-volume.
I don’t know exactly how Ahrefs breaks down these percentages but a public facing study breaks down it down like this:
- 1: 31%
- 2: 25%
- 3: 11%
- 4: 8%
- 5: 6%
- 6: 4%
- 7: 4%
- 8: 5%
- 9: 4%
- 10: 2%
For “Average Difficulty” paste in this formula:
This will simply average out the difficulty score of all the KWs the URL is ranking for (which is why we had to clean up the difficulty data in step 1).
Once you have plugged in all these formulas and double checked they are pulling the right data, you can simply highlight the first cell with the right data, ctrl + shift + down, select the rest of the cells in the column, and then hit ctrl + enter.
This will apply the rest of the cells to this formula.
Repeat this process for every competitor.
This is part three of a four-part series on creating quality content. In the fourth-and-final part, we’ll bring it all together, to show you how to finalize your process for identifying effective content.