For many of us, our relationship with SEO can be a mixture of emotions from love to hate. There are few more satisfying careers that provide constant reminders that your hard work has paid off. For each keyword I target, I experience a mixture of feelings from anticipation, frustration, and hopefully at the end of the tunnel, elation, and then finally relief. Relief that those hours I spent preparing to get certain keywords to the first page wasn't a waste. Relief that I can look forward to hearing the satisfaction in my clients voices when I tell them of their latest rankings. Those are the moments I live for.

But somewhere along the road to that first page ranking, things aren't so enjoyable. One of my internal struggles revolves around the appropriate time to spend on processes, research, and outreach. And amidst searching for the best sites to target, I am always considering factors like Page Authority, Domain Authority, and External Links. Granted, the Mozbar has been a huge help in this department, but when I have a large list of URLs to check, I'm usually filled with dread at the idea of viewing each site and taking the time to personally record this data in an excel sheet for my records.

So after struggling to find a lot of information on how to speed up this process, I decided to provide you with advice on how to build a Google Doc that will automatically pull all of these factors for your records. One that will pull in the Mozbar data for a large list of URLs.

Fortunately, before this post, Ian Lurie and Tom Anthony both provided similar versions of documents to complete this task. Without the code they provided, I could never have dreamed of doing this myself! So my aim is to just provide a few more adjustments that make the process all that much easier.

There is one more thing to note. In order for this process to work, you must have Pro membership to Seomoz.org.

Without further ado, here's how you can save yourself tons of time and headaches:

In a new document you will need 3 tabs: URL Edit, Results, and Config.

The Configuration Tab:

Create fields for your SEOmoz Access ID and Secret Key. They can be found at http://www.seomoz.org/api. Insert these fields into Cells B1 and B2 respectively (Seen in black).

The Code:

Go to Tools / Script Editor.

You'll then input the following script with only a few changes from the one that Tom Anthony graciously provided on his blog:

The first section of code (Rows 1-11) processes your Access ID and Secret key for the API to work. It is set up to reference the cells where you input the information (B1 and B2 on the Config Tab).

The second section (Rows 12-29) of code deals with a lot of Developer Talk that regards the security methods and signatures for SEOmozs API. Simply include it in your code.

The third section (Rows 30-54) provides the code the pulls all of our prized information.

YZJjIo

For added security, it's recommended that you add this bit of code to finish it up (Rows 55-58):

And that's the end of the coding!

Now back to the actual spreadsheet.

In order for the API data to recognize a URL. It must be in a very specific format. The URL cannot contain the http:// or anything after .com Oh, how that complicates things!

So to solve this problem, I created the tab, URL Edit.

rjdJaa

The Url Edit Tab:

In Column A: Input of any URL you want to check. This will be the column that you will paste all of the URL's you hope to pull information for.

In Column B: Enter the following Array Formula to manipulate the URL to only illustrate the stripped domain.

=ARRAYFORMULA(MID(A2,LEN(http://)+1,255))

Array Formula

The Results Tab:

vTKbna

For Cell A3: Insert the following formula to pull in the URL you would like to data for.

='URL Edit'!B2

 

J5HrZs

You're Done!

And that's the last step! The document should automatically populate the API records for Page Authority, Domain Authority, Linking Domains, and mozRank. And to pull this for multiple domains, simply drag the formulas down.

I've found this process has saved me countless hours of tedious data entry. So I hope that you savvy SEO's will be able to save yourself both time and money by implementing this faster process!

Shane Jones

Shane Jones is a content marketeing professional who has a special passion for blogging and all things internet marketing. Find him on Google+

Local Soccer News

You May Also Like

4 Responses to “The Ultimate SEO Timesaver: SEOmoz Linkscape API and Google Docs”

  1. Brian says:

    Great post, I'm trying to follow this right now. Is there a place where we can copy these scripts from? Or is the only option to re-type from your screenshots?

    Thanks!

  2. Shane Jones says:

    Hey Brian!

    Sorry I don't have it published anywhere other than through these screenshots! But if you send me your email, I'd be happy to give you a copy!

    Direct Message me on Twitter. @ShaneJones15

    Thanks! Hopefully I can help!
    -Shane

  3. Kathy Gage says:

    On our home page of the website when I use the SEOMoz tools there is nothing listed under SEOMoz linkscape. Is this important and how do I get the linkscape information to show.

  4. Mike says:

    Hey Shane!

    Thanks so much for taking the time to publish this – it's exactly what I need. Unfortunately, I can't get the columns to auto-populate in "Results" once the URL is in.

    I've went back and checked my code three times now, so I'm thinking I might be missing something else. Has there been anyone else experiencing a populating error within the results tab? Perhaps there's something simple I've overlooked.

    Lastly, I've noticed in your screen shot that the "Config" tab has a lock icon alongside it. Mine does not. Only reason I mention is because I'm looking for discrepancies.

    Thanks so much for your help!