What are my most linked-to subfolders?

by on 5th June 2010

I’ve had two separate occasions in as many weeks where I’ve needed to answer the question: “What Are My Most Linked to Subfolders?” for a client, or for myself. I thought it would be quite fun to do a quick write up on how I answer this question using a simple Excel query, some Webmaster Tools data, analytics data or Open Site Explorer.

In this post we’re going to use raw external link data from Webmaster Tools to quickly identify the most linked to subfolders on a site. You can repeat the same process using Open Site Explorer data, too.

Download your external linked to URL data

First, download your external links data from Google Webmaster Tools – here’s some anonymous data showing a site I’m working on and how many links their top pages have (blurring idea courtesy of DaveN)

Fun with tables, pivots and Excel queries

Do the usual “CTRL+L” to create an Excel table, I’ve called mine “GoogleLinks”.

In a new column, paste the following query:

=MID(GoogleLinks[[#This Row],[Page]],24,FIND(“/”,GoogleLinks[[#This Row],[Page]],25)-23)

This query starts at a specified point and plays back the characters in a cell until the next “/”. I’m using MID to start at a fixed position (24 = “”) and FIND to count the number of characters to the next “/”. Subtracting the count of characters in the domain from the count of characters to the next “/” gives me a column listing the folder name only next to the count of links in the data.

The end result is a little something like this chart, lovingly created using a pivot table:

Value errors

You’ll notice the #VALUE! result in the 4th column. That’s because my query gets upset if there isn’t another “/” from my MID starting point. Not to worry, try this slightly “improved” query:

=IFERROR(MID(GoogleLinks[[#This Row],[Page]],24,FIND(“/”,GoogleLinks[[#This Row],[Page]],25)-23),MID(GoogleLinks[[#This Row],[Page]],24,LEN(GoogleLinks[[#This Row],[Page]])-23))

If there’s an error, a slightly modified query kicks in, using LEN to give a count of the total characters after our starting position. The count, along with the MID query makes sure we can just play back all of the characters found in the cell. The end result looks a little better:

What’s the point of this?

In my humble opinion, there’s always been huge value in analysing your competitors top pages, and your own. When we’re dealing with a larger site architecture or we’re in need of the 10,000ft view, a count of all links by subfolder can add additional insight into the mix. I also believe that having an idea of your best linked to subfolders can help a lot with understanding strong and weak points with inbound link authority to certain content types, especially after the May Day update. That thought is for another post though.

Fun looking at the competition, or just big websites

In the meantime I’ll leave you with this fascinating view on the most linked to subfolders and pages at – data courtesy of Open Site Explorer and SEOmoz’s Top 500 list (my source of inspiration…)


This approach worked well for me – but you may have to tweak the queries slightly for it to work for you.

If you have an alternative suggestion, I’d love to hear it. Let me know your approach and I’ll gladly update this post with your queries!


  1. Hi Richard,

    this was well timed post, i hadn’t really thought of about looking at how the folder structure and links went together to highlight weak and strong points on a site and it seems like such a simple thing to review. i am currently using this combined with Xenu to look at both the internal structure and how that maps together with the external data.

  2. Hey Richard

    Would really love your help on an issue I am having with the above. When I create the pivot table to include folders and link root domains, instead of including the folder name, it shows the count value i.e.

    / = 1
    /folder/ = 6

    so my pivot table is just numbers

    1 78
    3 89

    etc etc.

    I am looking to do the above for a large international site so i can look at links on a country folder level i.e. /uk/ /us/ /fr/.

    Also, is there any quick method to consolidate all links under each folder. It pulls back links for pages under each folder i.e.

    folder1/index.jsp = 2 links

    is there any quick way of assigning all these links to the folder and not the page ?

    Thanks, great tip


  3. Thanks for posting the query for auto-filtering subfolders. Huge time saver!

Comments are closed.

We're hiring – check out our careers page Careers

Get insights straight to your inbox

Stay one step ahead of the competition with our monthly Inner Circle email full of resources, industry developments and opinions from around the web.