By: Barrett Dastrup

Ahrefs Top Pages Report 2.0 + FREE Template

January 10, 2018 10 minutes

Share:

The Ahrefs Top Pages Report

We use Ahrefs quite a bit at the agency. One of the features I find myself coming back to is the Top Pages report. I use this to get a quick idea of pages that are performing well organically on a client’s or on competitor sites. Let’s use Musiciansfriend.com as an example — I like music and I have a small weakness for impulse buying instruments, okay? 🙂

Ahrefs Top pages musiciansfriend image

So under site explorer I’ve plugged in musiciansfriend.com, and you can see in the sidebar that I’ve selected the “Top Pages” report. I am served up a report full of pages that are driving organic traffic, and I’m given the number of keywords for which each page is ranking, as well as the top keyword for each page.

As a side note, this data should not be viewed as completely accurate or comprehensive. See the “Organic traffic” section of this post from Ahrefs back in March of 2017 for a full explanation as to why, but basically it comes down to Ahrefs not being able to track every keyword in existence (though they’re making a pretty impressive dent with around 200 million keywords in their database for the U.S. alone). At any rate, this is a fantastic way to start gaining some quick insights.

But, and of course there is a but or else this post wouldn’t exist, there are a few things I’d like to see in this report that are absent. For example, I’d love to see the average difficulty for all of the keywords mapped to individual pages. That way I’d have a high level idea of how to prioritize pages to research deeper when projecting traffic growth for existing or prospective clients.

I’d also like to see the average volume and position of all the keywords mapped to individual pages for the same reason as having the average difficulty, helping me prioritize and use my time effectively.

To illustrate my point, let’s say Page A is ranking for 200 keywords. The average difficulty of those keywords is 9, the average volume is 1,000, and the average position is 10. I then take a look at Page B, which is also ranking for 200 keywords but has an average difficulty of 15, an average volume of 900 and an average position of 25.  If I am putting together a pitch for a client and trying to determine existing pages on the site that would be worth my focus, I’m obviously interested in digging deeper into what’s going on with Page A before I look at Page B. Page B has comparable difficulty and volume but with an average position of 25, it’s going to take me longer to push those keywords to page 1. I’m not saying to ignore page B, but these added metrics have helped me prioritize; they have saved me time.

The way the top pages report is now, I can see that Page A ranks for 200 keywords, and I can see the one top keyword for the page and its volume. I can see the same info for Page B, but this doesn’t really help me prioritize anything.

The Solution

Enter, the Ahrefs Top Pages 2.0 Tool. Let me show you how it works.

Sticking with the Musician’s Friend example, let’s pull a spreadsheet of all their organic keywords. I do this by plugging their domain into site explorer, like the example above, and navigating to the “Organic Keywords” menu item on the side bar housed under the “Organic Search” drop down tab.

This is what I see:

Ahrefs Top Pages 2.0 Musiciansfriend Organic keywords

We’re looking at 522,262 keywords in the U.S. alone. For this example I’m going to go ahead and apply a few filters to make that number a bit smaller (where possible, try to include all of the keywords this report gives you in the Ahrefs Top Pages 2.0 Tool), I am looking at only keywords between positions 1 and 20, and keywords with volumes between 500 and 200,000, and that gives us 6,627 keywords to work with:

Organic keywords with filters

Okay, now I export that keyword list, and import it into the “Ahrefs” tab on the Ahrefs Top Pages 2.0 Tool. It should look like this:

Ahrefs Top pages tool spreadsheet

Download this Template

Now, all you have to do is wait. The Data tab will auto populate all of the columns in the “Data” tab. Those columns are:

  • URL
  • Number of Keywords
  • Average Difficulty
  • Average Position
  • Average CPC of Keywords (to get an idea of conversion potential for the keywords)
  • Total Volume

The one problem is that the formula in Column A of the “Data” sheet takes a long time to execute. Here are the steps to short-cut the wait:

  1. Delete the formula in column A
  2. Go to the “Ahrefs” tab on the spreadsheet and copy all of the URLs in Column G
  3. Use the Power Tools plugin to remove duplicates
  4. Copy and past your de-duplicated list of URLs in Column A of the “Data” tab

If you don’t want branded keywords showing up in the Data tab portion of the sheet, make sure to filter them out on the “Ahrefs” tab. After that, let the numbers populate and start prioritizing and saving time when selecting top organic traffic driving pages to push even further, or low organic traffic driving pages with lots of previously hidden potential.

Download this Template

Make a copy and use it to your heart’s content

Also, huge thanks to Brandon Stauffer (@bstauff19) for help with the formulas on the sheet!

Written by Barrett Dastrup on January 10, 2018

Barrett is a Campaign Manager at 97th Floor, overseeing a team of marketers, content managers and designers.

Follow me: