Add3.com

As we cover in our “SEM Secrets” white paper (you’ve read it right?), you need authority to win at SEO. Open Site Explorer from our friends at SEOmoz is a great tool that can tell you all kinds of things about your site’s authority, and the authority of your competitors. The Open Site Explorer tool is absolutely amazing and it gives page-by-page metrics, but when I’m doing an evaluation of a site, I like to get a more holistic view of the content that’s getting your site links… and which content isn’t. So here’s a nifty little thing I like to do using OpenSiteExplorer.org‘s data  to evaluate a site’s authority on a section-by-section basis and gives you a neat chart to go along with it.

To kick things off – here’s what I was watching/listening to while I was putting together this post. This is a documentary of the Foo Fighters doing a tour where they played in fan’s garages.

Now that you’ve got your rock on, let’s get started! There are 6 steps to the process:

  1. Download your site’s “Top Pages” report
  2. Categorize your pages by “Section”
  3. Create a SWEET pivot table
  4. Create an EVEN SWEETER chart
  5. Analyze your site’s authority by section
  6. Form a plan

You gotta have an SEOmoz Pro subscription to use OpenSiteExplorer for this process, but there’s a free 30 day trial. Totally worth checking out OSE and SEOmoz if you haven’t before. Use this link to sign up for the free SEOmoz Pro trial. [Disclosure: this is an affiliate link so thanks for using it]

Step 1 – Download Your Site’s “Top Pages” Report from OpenSiteExplorer

The next two steps require a little “intermediate” work in Excel. Need help with this? Use the Microsoft Excel for SEO – Lessons for Aspiring Ninjas (see the “IF” statement section and the section on pivot tables)

Step 2 – Work Some Excel Magic to Turn URL’s Into Site Sections

Save your CSV as an Excel file, turn your data into a table (CTRL – T on a Mac, no idea what it is on a PC?) – it makes it easier to work with. Then, insert a column next to your URLs. I’ll call it “Section”, this is where we’ll categorize each URL. How? Use a nested “IF” statement to label the different directories of your site. If your site has lots of sections – this formula can get REALLY long. Amplify isn’t that broad, so my IF statement looks like this:

(it’s actually quite simple and not as scary as it looks… I got a little overzealous and granular.)

And the table now looks like this:

You can actually do something similar with the “SEARCH” function as well, but all it does is extract a part of the URL and you don’t have control over the label. Again – Microsoft Excel for SEO – Lessons for Aspiring Ninjas is extremely helpful in figuring out how to do this if you don’t know already.

Step 3 – Create a Sweet Pivot Table to See Authority by Section

I tend to use “Max” for page authority and linking root domains because it makes the chart (see “Step 5″ below) more manageable.  I also like to include the count of pages crawled. This way – you can get an idea of the authority of any given section vs. the number number of pages in that section. If you’ve got a lot of pages in a section on your site, but the section has no authority, then you probably have some work to do.

Then you can expand each section to see the stats for individual pages, like this:

Don’t know how to use pivot tables? You really should learn – it’s one of the most useful features of Excel.

Step 4 – Now Chart It

All you gotta do is highlight a cell in your pivot table and create a chart from it. A little tweaking and you get something like the chart below. Pretty sweet, right?

I chart linking root domains instead of total links because we know from studies that the number of root domains likely has a bigger influence on authority than total number of links. In other words – 100 links from 100 sites is better than 100 links from one site. Get crazy and see what happens to the chart when you pop open one of the sections of your pivot table.

Step 5 – Analyze

A quick look at the chart and captain obvious says – aside from the home page, the blog is kicking ass.  Thankfully, our “services” section is doing alright in 3rd place. Could be better, but the blog is the center of our content / marketing strategy so that we can draw attention to our services because of how smart we are. :) However, in a competitive setting we need links to our services pages to boost their authority so that we can rank above our competition for search results. So how can we drum up links from more domains to these pages? Additionally, the “resources” section is also an area we’ve put some time and energy into, but it’s barely got more authority than our “contact” section/page. So, how can we raise the authority of the content in the resources section?

Step 6 – Form a Plan

Now that I understand that my resources section isn’t pulling the weight that I think it should – we can brainstorm some ways to improve the authority of this section. Does it need more visibility on our site? Should we be working harder at marketing this content by blogging about it and dropping an ‘announcement’ every time we put something new up? How can we use social? etc.

As I go back and add social sharing data from OSE into my pivot table – we can see that the home page and blog are the only places where our content has been shared. We’ve got these great resources but they haven’t been shared, which limits their visibility and links. Perhaps the answer is as simple as adding social sharing ‘widgets’ to those pages.

And about that services section – how can we drive more links to this content? Internal linking may help, but there’s likely some more we can be doing from a PR and outreach or even social standpoint as well. Maybe including links to services in our press releases/boilerplate, perhaps contributed articles, or even simply asking some of our partners to link to specific services pages depending on the work we’re doing with them.

Hope you found this helpful. If you’re looking for help with SEO services, give us a shout.

Would love to hear thoughts/feedback/comments.

Ben Lloyd

About Ben Lloyd

Ben Lloyd serves as Principal at Add3 and manages the agency’s Portland office.  Ben got his start in SEM way back in 1999 – when there was like, 15 search engines and Google was barely a thing. Prior to Add3, Ben had founded Amplify Interactive in 2003 (which was acquired by Add3 in 2013), and hasn’t looked back since. Ben likes lots of stuff like golf, pinball, food(ie), booze/beer/wine – in that order, etc. Mostly – he likes doing that stuff with his friends. Ben is also co-founder of SEMpdx. Connect with Ben on LinkedIn

Comments & Replies