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.
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.
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))
The Results Tab:
For Cell A3: Insert the following formula to pull in the URL you would like to data for.
='URL Edit'!B2
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!