Loading...

How to Find the Best Possible Topics for Online Content

Have you ever slaved over content or paid a pretty penny for it, only for said content to fall flat? Did it look good? Sound good? Internally, were people excited about it? Did your content end up not moving the needle in any meaningful way? Here's why.

Founder of Klicker. Branding obsessed. Marketing connoisseur.

Posted on August 3, 2016 in Search

Have you ever slaved over content or paid a pretty penny for it, only for said content to fall flat?

Did it look good? Sound good? Internally, were people excited about it? Did your content end up not moving the needle in any meaningful way?

I understand the feeling—that your creative energy could have been spent better elsewhere.

I’ve made that mistake more times than I care to admit. But through my failures, I’ve created a process that almost completely prevents this from ever happening.

And the entire process is built upon the “80-20” rule.

The Pareto Principle and Content Marketing

Technically known as the “Pareto principle,” it originated in 1896 when Italian economist Vilfredo Pareto realized ~80% of Italian land was owned by 20% of the population.

Oddly enough, he also noticed his pea garden expressed a similar phenomena. Roughly 20% of his pea pods were producing 80% of his pea harvest.

Fast forward to the present. Today, the term is frequently used to describe how 20% of work gets 80% of results. This ratio is not always exactly 80/20 but the idea that small actions produce disproportionate amounts of consequences can be observed everywhere.

In the digital marketing sphere, this principle is especially prevalent as most websites have a few pieces of content generating the majority of traffic / sales / leads / etc.

The 80-20 rule isn’t new & applying it to digital marketing isn’t ground-breaking either. NY times bestseller “The 4-Hour Workweek” by Tim Ferris was one of, if not THE 1st body of work to get big by applying this principle to marketing.

Yet I still see (and often work with) websites creating content simply to create content for content’s sake. This strategy has can work by creating massive amounts of content, where you can get lucky and hit a home run…sometimes. Maybe.

But a broken clock is right twice a day, and you can do a hell of a lot better than that.

I’d prefer hitting more home runs with less content. Wouldn’t you?

Your competition has done the R&D for you.

Now, I must warn you that traffic is not the sole way to measure whether a piece of content is a home run or not.

Content can diffuse sales objections, strengthen your brand, create context for the user and much, much more. A home-run could be any combination of the above.

For the purpose of this post, I’ll be focusing on how to leverage the 80 – 20 concept specifically to maximize the probability your content will drive qualified traffic with the least amount of effort.

I do this by finding the 20% of content industry wide driving 80% of the traffic and dissecting the 20% slice by SEO competition, cherry-picking the ripest, lowest-hanging fruit.

Each of your competitors has been contributing to a public pool of research and development. There are tens to hundreds of thousands of pages and posts in any given niche that can be analyzed to easily find the 20%.

Most of your competitors know what 20% of content is driving 80% of their traffic, but they probably don’t know what that picture looks like industry wide.

Ride the Coattails of Your Competition

Free your bandwidth up to focus on content ideas proven by data, and knock them out of the park. There’s no need to reinvent the wheel. Google didn’t come up with the idea for the search engine, Alan Emtrage did, and he called it the Archie search engine.

Ever heard of it? Didn’t think so.

There were an additional 13 other engines invented and in use prior to Google answering it’s first query. Take my word for it, you probably haven’t heard of them either.

Google took an existing good idea and made it great.

Be like Google.

“But bro, I don’t copy other people, I create original work. Copying other people is a shortcut bro. Stop riding off the coattails of others maaaaaan.”

FIrst off, yes it is shortcut. Secondly, all your heroes you naively think were ‘orignal’ stole from the greatest minds in their peer circle without remorse.

This idea is best described by Kirby Ferguson in his series “Everything is a Remix,” or by famed author T. S. Eliot, who once said “Good writers borrow, great writers steal.”

Led Zeppelin’s “Stairway to Heaven” is a shallow remix of Taurus’ “Spirit” a band that opened for them on tour.

The list goes on and on.

I’m not saying blatantly rip-off your competitions content, as that should flag you for duplicate content by Google. What I am saying is that almost all of modern music’s top songs have all been built on the same 4 chord formula.

It’s ludicrous to not factor that formula into your process for writing a song.

High performing content on the internet has its own “4 chord formula” unique to every niche, every SERP and every subject.

I’m going to teach you not just how to find this formula, but to find the variations of the formula that provide the best bang for your buck.

Now after this long intro patterned after the wildly successful man blog Danger and Play (only copy the best of blogs when blogging), I present to you a step by step guide in finding the 20% of content providing 80% of your industry’s traffic.

Phase 1: List Out ALL Competitors

I’m guessing you can rattle off a handful of competitor websites from memory. Use this as a starting point and throw these websites into a newly minted Google sheet. I like to name this first sheet “–Insert industry/niche– Master Content Breakdown” and place it in a new Google drive folder with the same name.

Now to expand upon this list. There are multiple ways to do this, but for the sake of this post I recommend using semrush as this can be done without having a paid account.

Simply input any of your competitors into the input box, ignore the sales pop-up and let the tool work. When the tool loads the results, about 3/4th’s of the way down on the page you will see a section titled “MAIN ORGANIC COMPETITORS”

semrush

Don’t bother clicking on “see full report” if you don’t have a paid account as the free version will only show 5 URL’s.

Throw those into your list and repeat this process for every site until semrush isn’t providing any new domains in the results.

Another method I like to use is Googling ‘top (insert niche/industry here) blogs.’

Chances are you will find a good chunk of competitors you haven’t heard of, discover some blogs you would read yourself and find another grouping of sites to add to your list.

Keep doing this until you are satisfied. Remember the more sites you discover, the more work it will be to perform this analysis. However, this will be well worth it as the more sites you analyze the better your results will be.

Again, the idea is to find out what 20% of content is bringing in 80% of the traffic industry wide. As a rule of thumb I personally shoot for at least 20 sites/blogs.

Once you have completed your list, in the Google drive folder you created above, create a separate subfolder for each competitor as things are going to get messy. Mark my words, if you don’t organize the way instructed, you will get lost and waste considerable amounts of time.

Phase 2: Grab Each Competitor’s Organic Rankings

I am only aware of one way to do this, and that is using Ahrefs site explorer. This requires a paid account to do so.

I love Ahrefs and would recommend it to anyone confidently, but I am sure if you went digging you could find an alternative. Since I have only done this one way, I will continue under the assumption you will be using Ahrefs as well.

But If you’re not, the macro process will be the same.

The purpose of this step is simple, find out every KW your competition is ranking for as well as getting the following data about each KW:

  • What URL is ranking for every KW?
  • What position is each ranking KW in?
  • What is the difficulty score for each KW?
  • What is the estimated traffic each ranked KW is bringing in?

Some notes on Ahrefs data:

  • Rankings are not updated in real time, but usually within a month. For the purpose of this analysis this will do.
  • Ahrefs difficulty score is different than the famous Moz difficulty score but I find it accurate enough. Plus if you have ever attempted to bulk pull difficulty scores in Moz then you know what a nightmare that is.
  • Estimated traffic is calculated by checking what position each KW is ranking for and applying the average CTR rate for that rank to the monthly search volume of the respective KW.

I don’t know what percentages Ahrefs estimates for each positions CTR as each subject or niche varies, but a study by chitika.com breaks it down like this:

  • Rank 1 – 33%
  • Rank 2 – 17%
  • Rank 3 – 11%
  • Rank 4 – 8%
  • Rank 5 – 6%
  • Rank 7 – 4%
  • Rank 8 – 3%
  • Rank 9 – 2%
  • Rank 10 – 2%

My paid subscription to Ahrefs only allows me to export 30k rows at a time. The problem is most top competitors (depending on niche) will be ranking for more than 30K KW’s.

To reduce the amount of row’s you can tell Ahrefs to only pull rankings for KW’s that the competitor’s website is ranking for in positions 1 – 20.

Ahrefs positions tool

I’m also not particularly concerned with rankings past positions 20, as those KW’s are not moving the needle in any significant way. You might be wondering why I pull KW’s in positions 10-20 at all, too.

It’s because these are the KW’s your competition could easily get on the 1st page with targeted link building and improved on-page optimization. This is an easy area for you to improve upon your competition when you “remix” the content.

Now, often you’ll find that even restricting the report to positions 1-20 still exceeds your row limit—for me it’s 30k.

To work around this, simply change position increments to smaller ranges. First try only pulling positions 1-10, then 11-20. If that still exceeds your limit drop it down to increments of 5. In some cases I’ve had to only pull rankings for 1 position at a time for really successful websites.

Each time you export the report throw it into the corresponding competitor’s Google Drive subfolder you created earlier. This is why you want separate folders because you will end up with lots of reports named nonsensical things like “report090934850934.”

If they’re not in the right folders, you will get lost and have to start all over again.

Next, create a G-sheet for each competitor separately in the corresponding G-Drive sub-folder. Then go through each ahrefs export and copy/paste the data from each one combining them on the competitor specific G-Sheet.

Name the tab “positions” and repeat this for each competitor until you have a single google sheet for each site with all the Ahrefs data for that site on the one tab titled “positions.”

***Note: You don’t have to name the tab positions, but all the formulas were written assuming that tab will have this name, you can change it as long as you change the formulas to reflect this.)

For the sake of clarity, lets double check where you’re at.

If you originally had a list of 20 competitors, you should now have 20 separate Google sheets, named by competitor in 20 separate subfolders with the same names. Each sheet should have the combined data from however many Ahrefs exports you needed for that specific competitor.

Now is a good time to delete all those ahrefs exports as they’re just going to get in the way.

Next, you will need to rearrange a few columns in each competitor google sheet in order for the formulas I will be providing later to work. You will need to move the URL column to column A, the position column to column B, and the KW column to column C.

Your columns should be in this order:

  • URL
  • Positions
  • KW
  • Position History
  • Volume
  • Difficulty
  • Traffic
  • CPC
  • Results
  • Last Update

For the visual learners:

Ahrefs positions tool

Repeat this process for each competitor until you’ve finished them all.

Next we need to clean up the data in the “difficulty” column to prevent the formulas we will be using from having errors.

DO NOT skip this step. I promise you will regret it.

First highlight the “difficulty” column (should be column G), sort the column by A-Z, change the first 0 to 5 and select the five. Then drag and select the rest of the zeros and hit Ctrl + Enter. This will change all the “0” difficulties to 5. Close enough for our purposes and zeroes don’t play well with the math down the road.

Second, we want to fix all the bars pulled instead of numbers in the same column. I don’t know why ahrefs pulls some difficulties as 0 and some as a bar, but it is what it is.

Copy a cell with a bar in it, select the whole column, hit Ctrl + F, click the 3 dots on the pop up, paste the bar in the “Find” window and type in “10” in the “Replace with” window and click “Replace all”

See image below:

semrush

Last part of this step, you’ll want to copy all the URL’s in column A, create a new tab, name it “Content KOB” and paste the URL’s into the new “Content KOB” tab’s column A.

Click “add-ons” and choose “remove duplicates” (if you don’t have the add-on yet, it will automatically download it) follow the instructions and on step 4 make sure to select the last option “delete rows” & click start. The tool will run and all you will be left with only unique URL’s.

semrush

Repeat this process for each competitor.

Phase 3: Pull URL & Domain Link Metrics For Each Unique URL

I use the tool URL profiler to do this, I’m sure there are other options, but this tool is the bee’s knees and if you don’t have, buy it. Don’t think, just buy. You will get lots of mileage with it I am 100% confident you won’t regret the purchase.

Note: If you’re not using Ahrefs, but you want to use URL profiler for this step, you will need an Ahrefs api for this to work.

To add your Ahrefs API token (get it here) to URL profiler, open it, click “accounts,” select “Ahrefs,” and paste in your API key. Click test to make sure it’s working and you’re all set.

Now we need to tell URL profiler what data from Ahrefs to pull. Luckily we don’t need URL profiler to pull much.

Under the “Domain Level Data” section check the “Ahrefs” box. Under the “URL level data” section check the “Ahrefs” box as well as the “social shares” box.

That’s it. For you visual learners your settings should look like this:

semrush

Next, go to the Google Drive folder for your first competitor, open the Google Sheet and go to the tab “content KOB.” Copy the list of unique URL’s from column A (the ones you de-duplicated) and past them into the URL profiler “URL list”

Note: In URL profiler for some reason you can’t Ctrl + V, you have to right click in the window and select “paste from clipboard.”

Then run the tool.

The tool will usually run for around ten minutes depending on how many URL’s you have in conjunction with how fast your CPU is. Take a break, do some pushups & grab some water. We’re just getting warmed up.

Once that is done, open the file, select all (Ctrl + Shift + A) and paste this over the URLs you have on your Content KOB tab.

It should look like this:

semrush

Next, like when we arranged the “positions” tabs we need to re-arrange the columns and rid ourselves of data we won’t be using in order for the formulas to work.

Arrange your columns in this order while deleting everything else not in this list.

  • URL
  • URL Ahrefs Backlinks
  • URL Ahrefs Ref Domains
  • URL Total Shares
  • Domain Ahrefs Backlinks
  • Domain Ahrefs Ref Domains

Repeat this process for each competitor.

Phase 4: Tie All that Juicy Data Together

If you’re getting tired, hang in there. We’re closing in on finishing this bitch up. All that’s left is telling the “Content KOB” tab how to pull the data from the “positions” tab.

First, on your content KOB tab we need add 4 columns to the right of column A (URL column). Name these 4 columns in this order:

  • # of KW’s in top 10
  • # of KW’s in top 20
  • Estimated Traffic
  • Average Difficulty

Now your sheet should have these columns in this order with the columns 2-5 being empty:

  • URL
  • # of KW’s in top 10 (empty)
  • # of KW’s 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

Here’s another fancy screenshot:

semrush

In order to tell Google Sheets how to populate these 4 blank columns we have to dive deep into spreadsheet formula black magic. This is like Voldemort-creating-a-horcrux level black magic.

Luckily I have figured it out for you, and by that I mean paid somebody much smarter than me to figure it out.

But don’t worry, I’ll hook you up for free because I’m such a good person.

For “#’s of KW’s in top 10” paste in this formula:
=countif(filter(Positions!A:B,Positions!B:B>=1,Positions!B:B<=10),A2)

This will look up the URL in column A, find that URL on the “positions” tab, and count how many KW’s that URL has in positions 1-10.

Note: Double check that you spelled positions correctly on the tab name, or 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 KW’s in top 20” paste in this similar formula:
=countif(filter(Positions!A:B,Positions!B:B>=11,Positions!B:B<=20),A2)

For “Estimated Traffic” paste in this formula:
=SUMIF(Positions!A:A,A2,Positions!H:H)

For “Average Difficulty” paste in this formula:
=AVERAGEIF(Positions!A:A,A2,Positions!G:G)

This will calculate the average difficulty of all the KW’s each unique URL is rankings for. This is why we had to clean up our difficulty data earlier as averages don’t work well with zero’s

Plug these in and double checked they are pulling the right data. Check by picking any URL, copying it, ctrl + F on the “positions” tab and search it. Cross reference the numbers with what the formula is pulling.

Once you’re sure the formulas are working 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 formula for the column to the rest of the cells in the column.

Repeat this for the other 3 columns, then repeat for all competitors.

Phase 5: Combining Competitors and Calculating Final Scores

Remember the sheet I had you make way back during step 1 called “Master” breakdown? Open that bad boy up, you’re about to stare deep into the depths of your competitors’ souls.

What you do with this knowledge is up to you.

Because Google Sheets starts to get real slow and frustrating as the data sets get larger, I like to put the final breakdown on this separate “Master Breakdown” sheet so that it doesn’t have the massive overload from all the positions tabs.

Once you have your Content KOB tab done for each competitor you want to select all of it and paste special – values only (Ctrl + Shift + V) onto your master content sheet.

Do this for all your separate competitors sheets. For clarity, you are taking the data from each competitor’s “Content KOB” tab, selecting it all, pasting values only onto your “Master Breakdown” sheet. This will combine all the unique URL’s and their corresponding stats onto one sheet.

All that is left is to create a formula that incorporates all this data into one “final score” for each unique URL.

I call this final score “KOB” (it’s a long story) but you can call it whatever you want. Call it the Victory Metric.

The main idea for the formula is to take the overall performance of each URL and divide it by the competition. When we sort by this number, the highest scoring URL’s will be the ones with the best performance to low competition ratio.

These are the pieces of content you want to integrate into brainstorming and you can feel confident spending the time to create the best version of this content as it will not go to waste.

Google didn’t just make the best version of any regular idea. They made the best version of the best idea.

Same principle applies here.

The formula I use is….. (drum roll please)

(((#kw’s in top 10*2)+# of kw’s in top 20+(estimated traffic*4))/((average difficulty)+(# 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:
=(((B2*2)+C2+(D2*4))/((E2)+(F2*4)+(G2*3)+H2+I2+(J2*2)))*1000

That doesn’t look as sexy as I built it up to be, does it? Let’s break this down and maybe you’ll see the beauty in the madness. Keep in mind this is my dumb monkey brain’s formula, I have ZERO experience in math and it most definitely can be improved.

See below:

[PERFORMANCE] / [COMPETITION]
=(((B2*2)+C2+(D2*4))/((E2)+(F2*4)+(G2*3)+H2+I2+(J2*2)))*1000

Lets first breakdown the first section, or the “performance” section of the formula:

  • I weight #kw’s in top 10 by 2 because these are needle moving KW’s
  • I leave# of kw’s in top 20 as is because I simply want to factor the potential to improve the rankings over my competition.
  • I weigh estimated traffic by 4 because this is what matters the most. If you have 200 KW’s in the top 10 but they only bring in 5 search a month…# of KW’s is pointless

* Note: I have recently starting adding average CPC of the KW’s to this formula as higher CPC indicates that the KW’s 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 it drives traffic ready to buy or convert. If you want to do this, simply tweak the “average difficulty” formula to pull from the extra CPC column you will add. When I do this I weight CPC by 3 in this final score formula.

Now the second section or the “competition” section of the formula:

  • Average difficulty No weight as its an approximation of the difficulty score of all the KW’s for each unique URL.
  • # of URL inbound links weighted by 4 because this is the hardest part for me to play catch up with competition. A URL may be bringing in tons of traffic but also have thousands of links. If I were to “remix” this content, I would have to build all those links just to be competitive.
  • # of URL referring domains I weight by 3. Much like above, I don’t want to play catch up with links and varied referring domains.
  • Total social shares No weight as I am of the opinion that social shares don’t influence rankings as much as links do.
  • # of root domain links No weight but the domain the URL is on does matter very much. I don’t want to be deceived by URL’s with little to no links going to the actual URL, but have great rankings because the domain is a monster.
  • # of root domain referring domains I weigh this by 2 for the same reasons as above, I want to factor in domain level power in sorting the top performing URL’s.
  • Lastly, I multiply the final number 1000. This is just to make the final score not a weird less than zero decimal and make it easier to understand. Purely for my own sanity.

Apply this formula to the whole column, sort by Z – A and viola! You now are looking at a ranked list of top performing content that needs the least amount of SEO power to capitalize on, industry wide.

Phase 6: Profit and Enlightenment

Now the hard part comes. Picking a post/subject, remixing and refining it to its sharpest edge possible. If you need help, let us do the heavy lifting! This is only one of the many tools we use for creating content with charisma that ties perfectly into the smartest search strategies industry wide. Bar none.

I will write a post later on how I go about deciding how to improve upon my competitions content, but until then happy researching!

Written by

Founder of Klicker. Branding obsessed. Marketing connoisseur.

Posted on August 3, 2016 in Search