Home | Excel for SEO | Making @SEOmoz’s SERP Analysis Tool More Awesome

Making @SEOmoz’s SERP Analysis Tool More Awesome

John Doherty —  August 31, 2011
  • Buffer
  • Vote on Hacker News
  • Sharebar
  • Buffer
  • Vote on Hacker News

Ladies and gentlemen, another awesome Excel spreadsheet for you to use. Today I have taken the SEOmoz SERP Analysis Tool export and modified it to spin you out a bunch of graphs that will show you where your site might be weak against your competitors. Huge!

Before further ado, you can download the sheet here. If you don’t want to read the background, skip to here.

A bit of background

When SEOmoz announced a few months ago that they were going to turn the SERP Analysis tool that was built by BusinessHut into a dynamic tool in the SEOmoz toolbox, you could say that I was stoked. Nay, I was overjoyed.

I love the tool, I really do. It may be a bit hidden in the toolbox, but I think that it is a thing of beauty and helps out a lot of people.

To me though, I was a little bit disappointed when I saw the tool. Yes, I love that you can plug in a site you want to compare the search results to, which is mega helpful. And the tool looks pretty. And yes, you can export it to CSV.

The feature I loved about the old tool, though, was how it would spin you out a bunch of graphs that would show you, visually, where your site stood against the competition, and it helped you understand how and why different sites were ranking for the term that you plugged in and where your site might be weak. Then you could take action and rank your site for awesome.

The SERP Analysis tool doesn’t have these graphs, though. Bummer, huh? I missed graphs like these:

Isn't that pretty? And helpful??

Choices

As far as I see it now, you have two choices:

1) Download the old spreadsheet and get all of the data manually, like I used to.

OR

2) Use this awesome spreadsheet that I’ve put together for you!

Making the SERP Analysis Tool even more awesome!

Today I decided that I was going to turn the SERP Analysis tool into a tool where you can plug in the full export that you just ran, using some of your monthly credits (I think you get 50?)

Here are the steps you should go through:

1. Download the Template for Moz SERP Analysis Awesome.

2. Run the SERP Analysis Tool for your keyword in question.

3. Run the full report using the button highlighted below:

 

4. Export to CSV.

Now, once you have exported to CSV, all you need to do is highlight ALL of the data that you have been given in your CSV and plug it into my Template for Moz SERP Analysis Awesome.

What will I see?

Now we get to the fun part! Now that you have inserted all of your data into the spreadsheet, you can click through the tabs and see images like these:

mozTrust compared to mozRank. How in line with your competitors is yours?

How well are your URLs and domain optimized for the term?

How about your domain mozRank? How does that compare?

You get your on-page optimization score as well. Bonus!

Yes, your social stats are there too.

Did you break them down by category/type?

You bet! Working from inspiration from the original document, I applied some conditional formatting to the sheet so that you can see when your onpage optimization is bad, like below:

Green is good, red is bad. Easy enough?

The different categories that you will receive graphs for are:

  • Ranking Page Link Metrics
  • Onpage Optimization
  • Domain Metrics
  • Social Metrics

Now what?

I’m an extremely visual person, so I find these graphs to be helpful to get an overall view of my site. For example, from running this sheet for my website and a term that I have been trying to rank it for, I see that my site is WAY behind in links, yet I’m still ranking pretty well for the term. If I needed to, and saw that my site is so far behind in domain metrics and links, I could try to outwork my competition by using videos, photos, and other types of media to get into the SERP and draw attention to my site.

Hmmmm….I might just do that.

Until later…happy optimizing!

And if you still haven’t gotten the spreadsheet, get it here.

As always, I’d love your feedback on how I could make this even cooler!

Enhanced by Zemanta

John Doherty

Posts

I'm the new (as of October 2013) Online Marketing Manager of Hotpads.com, soon to be based in San Francisco. Previous to Hotpads I worked at Distilled for 2 years as an online marketing consultant. In my spare time I shoot lifestyle photography, explore new and interesting food in New York, ski, rock climb, and update my Twitter and Google+ accounts.

25 responses to Making @SEOmoz’s SERP Analysis Tool More Awesome

  1. Nice breakdown here. I’d love for you to come and check out what we’ve built at serpIQ.com (totally free trial) as it seems like we’ve automated all of the things you had to go and figure out with the SEOMoz tool, and it’s a heck of a lot faster.

    Please feel free to reach out with any questions or feedback, we’re always looking for suggestions from our users!

    Thanks!

    Darrin

  2. Great post John! (and thanks for the mention!) I’m glad you liked the spreadsheet I created, and I’m even more excited to see how far it’s going. Someday, I’m sure we’ll create a tool where all you need to do is enter your URL and out will pop detailed instructions on how to beat the competition for all the keywords you want. Until then, you’ve made some great progress toward that goal, and I’ll definitely be using your spreadsheet soon. Good work!

    • Thanks for the comment, Jason, and thank YOU for creating the sheet in the first place! Seriously, when I was working in-house, your sheet helped me out so much in knowing how I was doing against my competitors in some very competitive SERPs.

      Cheeers man and I look forward to your next creations!

  3. John, Just a head’s up. The first link for “download the sheet here” is missing the href. The other links work, but that one had me going for a minute. :-)

  4. Nice article John, i just got done reading Tom Anthony LinkScape w Google Docs. For some reason i’m getting bogged down in the script for member id with an error.
    oh well, will check out your cool toy!
    Cheers!

  5. A great resource. I’ve spent countless hours manipulating the CSV results output from the fantastic SEOMOZ SERP Analysis Tool. No more copying & pasting screenshots of the DA/PA graphs into my client reports :)

  6. This paragraph is genuinely a nice one it assists new web users,
    who are wishing for blogging.

Trackbacks and Pingbacks:

  1. » Competitor Analysis and Benchmarking With the New Domain and Page Authorities MtvNews.org - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz’s SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  2. Competitor Analysis and Benchmarking With the New Domain and Page Authorities - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz’s SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  3. On The Last Turn Of The Universe – Top Ducks Secret » Competitor Analysis and Benchmarking With the New Domain and Page Authorities - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  4. Competitor Analysis and Benchmarking With the New Domain and Page Authorities « Fast Ninja Blog by Freelanceful – Web Design | Coding | Freelancing - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  5. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz’s SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  6. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | Breaking News - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz’s SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  7. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | Online Advertising, SEO, Internet Marketing - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  8. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | BBG Network - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  9. Competitor Analysis and Benchmarking With the New Domain and Page Authorities « Seo Traffic « SEO Traffic - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  10. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | Best Traffic Tips - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  11. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | Online Marketing News | Instant-Web-Biz - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz’s SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  12. Competitor Analysis and Benchmarking With the New Domain and Page Authorities « Webmaster Tools Selection - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  13. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | CS5 Design - December 5, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]

  14. Competitor Analysis and Benchmarking With the New Domain and Page Authorities | NEWS.IN-EGO.PL - December 6, 2011

    [...] Tool existed. You can read the full explanation of this sheet over at my site in the post called Making SEOmoz's SERP Analysis Tool More Awesome, but essentially what you can do with this sheet [...]