Toll Free: 1-877-695-7388

GTA: (647) 699-2838

Search Engine People
  • SEO
  • SEM
  • CRO
  • Display
  • Blog
  • Why Us
  • Contact
  • Join Our Team
  • Get A Quote

Toll Free: 1-877-695-7388

GTA: (647) 699-2838

Calculate URL Depth Using Excel

Ruud Hein | October 15th, 2010
Tweet55
Share3
Share
Pin
58 Shares

girl with calculations scribbled on hand Last time I showed you how to use Excel to reveal a sites directory structure. Today Excel is going to show you how many levels deep your URLs go " even if those levels are URL parameters.

Why?

Oversimplified: the more shallow, simple and clean a URL is, the better.

Every extra http://example.com/directory/and?url_parameter adds another level of depth to a site that can potentially waste a search engine spiders time on your site.

Finding out how deep URLs go is as easy as counting directories by counting /s and &s.

Heres how you do that automatically with Excel.

Get A List Of URLs

Get your list of URLs by crawling the site, scraping Google, getting a sitemap " anything.

Most of the time Excel can import just about anything and show it in a pretty structured manner.

Like last time, Im using the sitemap of Allied American University only Ive added a few fake URL query parameters.

image

Insert Directory Depth Formula

With your URLs in column A, heres the formula I insert to have Excel count directory depth:

=SUM(LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))/LEN("/")+SUM(LEN(A2)-LEN(SUBSTITUTE(A2,"=","")))/LEN("=")-2

My data always has headers so the first URL is at line 2.

To repeat the formula for the other URLs, figure out how many URL lines there are. Easy way in Excel to go to the last line in a column is to put your cursor in the first cell, then press CTRL + SHIFT + cursor down.

image

Fill in: B2:B107 (in my case you might have more or less URLs). This of course presumes your URL depth information is in column B.

image

From the Home tab, click Fill and choose Fill Down.

image

Done

image

We can now see that /default.aspx is one step away from the domain or that /academics/degree/concentration/default.aspx is 4.

Just as easily we can see that /about-aau/contact-us.aspx?para1=hop&para2=dop is, due to its URL parameters, also 4 deep.

From here on we could do things like

  • sort the URLs by URL depth
  • highlight all URLs that are at or after an arbitrary level
Tweet55
Share3
Share
Pin
58 Shares
Posted in Web DesignTagged directory structure, excel, IA, URL structure

About the Author: Ruud Hein

My paid passion at Search Engine People sees me applying my passions and knowledge to a wide array of problems, ones I usually experience as challenges. People who know me know I love coffee.

Ruud Hein

5 thoughts on “Calculate URL Depth Using Excel”

  1. Victor says:
    October 15, 2010 at 2:00 pm

    Great! Thanks a lot for this trick, I’ve never thought that url depth was so important. I’ll use this formula to check the depth of the backlinks I get.

  2. mra says:
    October 16, 2010 at 9:56 pm

    Good post and fantastic tricks to know the depth of URL so easily. Blogger can use this trick to know the backlinks depth.
    .-= mra recently posted: How To Increase Traffic To Your Site =-.

  3. well says:
    October 18, 2010 at 12:09 pm

    I think it’s not directory depth Google follows, It is all about how the pages are being linked. Think of it as a google PR, The path depth is not important to Google. Usually sites get the most links to the homepage, and from the homepage the PR is spread through the site. In some cases a lower page has a higher PR than the homepage. That’s how many clicks away from the home page that determines the lessening of the PR. Furthermore, almost every single page for allied.edu is a couple of links away from the home page. So, I don’t think the page depth for this case shouldn’t be any problems.

    1. Aidan Beanland says:
      October 19, 2010 at 10:54 pm

      I agree with well – the number of directories in a URL doesn’t automatically equate to click depth (although I’ll be stealing your nifty Excel function for other purposes!). I use Xenu Link Checker to determine click depth as it’s conveniently returned in its crawl report.

    2. Ruud Hein says:
      October 20, 2010 at 7:23 am

      You’re right, Well — and Aidan: number of directories or URL parameters doesn’t necessarily equate click-depth. It is a reasonable approximative measure though.

      Re. Allied.edu: I’m just using that site as an example in my Excel posts as we nor they stand anything to gain from the exposure 🙂

      Aidan — nice use of Xenu!

Comments are closed.

Recent Posts

  • Maximizing Your E-Commerce Sales:
    A CRO Audit Guide
  • Movin’ On Up! Why Migrating to Google Analytics 4 (GA4) Should be a Priority
  • A Year in Review: The Digital Marketing Trends That Defined 2021
  • The Basics of Video Marketing
  • Just How Much Do Google Reviews Impact Your SEO Ranking?

Categories

  • Analytics & ROI Analysis
  • Company News
  • Content
  • Conversion Optimization
  • CRO
  • Display Advertising/RTB
  • Email Marketing
  • En Español
  • En Français
  • Inbound Marketing
  • Lead Nurture & Marketing Automation
  • Local Search
  • Marketing
  • Mobile
  • Partnership Marketing
  • PPC
  • PR
  • SEO
  • Social Media Marketing
  • Web Design

Additional Posts

5 Ways to Grow Your Ecommerce Sales This Week

October 13th, 2010 | by Kirsty LaVier

Ten Year Coma, Copywriting, Marketing and Guilt

October 13th, 2010 | by Gabriella Sannino

Promote Your Site With Social Media

October 12th, 2010 | by Julie Joyce

LET'S TALK

Need more information or want to get in touch?

Get in touch!
  • SEO
  • SEM
  • Display
  • Blog
  • Why Us
  • Join Our Team
  • Contact Us
  • Local SEO
  • Small Business SEO
  • Enterprise SEO
  • International SEO

LOCATION

1305 Pickering Parkway,
5th Floor Pickering, L1V 3P2

PHONE

Toll Free: 1-877-695-7388
Greater Toronto Area: (647) 699-2838

Social

© Search Engine People Inc. 2023 – Canada’s Top Digital Agency
© SEP 2023 – A Search Engine People Company | Privacy Policy

Search Engine People