At one time or another, we’ve all come across a “juicy” list of directories, blogs, or other websites that we might be able to use for some link building. I recently came across such a list and was quickly annoyed at how low the quality of some of the sites was. I wanted a quick and dirty way to rate all of the sites so I could start my linkbuilding process with the most promising websites. That’s when I found the SEOmoz Free API. With my mad Excel skills and lots of help from Leith Ross with some Excel code, I was able to create what I think is the fastest and easiest implementation of the SEOmoz API out there. It’s also easy to customize if you want to hack it into something that suits your needs.
This is a self-service spreadsheet, so you’ll need to have your own API credentials. (Get credentials here) If there’s enough demand, I’d be happy to publish a more polished version for mass consumption. (leave a comment)
I wrote a custom function in Excel that queries the SEOmoz API URL with the various parameters pulled from the spreadsheet. You just need to download the spreadsheet and insert your API credentials.
Press Alt+F8 and run the GetURLMetrics macro to begin processing. Be patient with large lists of URL’s. Currently, it takes about a half-second per URL to process. LinkScape has been renamed Mozscape API and new throttling limits have been added. For the free version, we’re limited to 1 request every 10 seconds. Using that version, please don’t try to run huge lists of URLs. 1000 URLs would take about 3 hours.
To follow along, download the latest Excel macro-enabled spreadsheet below: (Enable macros after opening.)
Excel Spreadsheet (Beta1) 4/19/2011 (Original Version)
Excel Spreadsheet (Beta 2) 9/22/2011
*** 7/23/2012 – Issues with V2 have been resolved. Please download version 3 below. ***
Excel Spreadsheet (Beta 3) 7/23/2012
***[UPDATE: 8/14/2012: I joined the Mozscape API beta testing group today. Expect a new and hugely improved version of this spreadsheet very soon!!!]
***[UPDATE: 9/1/2012: The Mozscape API beta testing is complete and has been released to production. Unfortunately, the release broke our macro. Luckily, during beta testing we were working on an add-in / plugin version. See below…
********* UPDATE 9/5/2012 **********
Version 3.0 is now in beta testing. (very very beta)
Rather than a vba macro, this new version is an installable Excel Add-in.
Check it out here: http://www.businesshut.com/seo/seomoz-mozscape-add-in-for-excel/
This also renders most of the instructions on THIS page, null and void. The new page above will contain the latest information for this project.
**************************************
The function, named GetURLMetrics, takes a specified URL and whether or not to follow redirects as parameters. [eg. =GetURLmetrics(“google.com”,TRUE) ]
It returns the entire response of the Linkscape url-metrics API call, and the cells to the right parse that into usable fields. If you’re looking for a way to assess many websites quickly, I think this will work for most applications.
This is very much a beta version and I appreciate anyone giving it a try. It worked fine for me, but others have not had the same success. Your questions and suggestions are very much appreciated.
*** Update: If you receive an “invalid signature” error, please refresh the API page on SEOmoz to get new credentials. Make sure to re-copy all of the information needed, and that you don’t accidentally copy line breaks or other hidden characters. It takes a few tries for some, but usually works.
*** Update: Mac users: If anyone was able to get this working on their Mac, or with OpenOffice on any platform, we’d love to hear from you. Several users have tried, but weren’t able to get it working. If you figured it out, please let us know.
Download the spreadsheet here.
(right-click and Save Target As)
The macro/function is saved within and can be viewed by pressing Alt+F11 once in Excel.
Press Alt_F8 to Run.
You can download the text of the macro below. You’ll need all of the included functions for everything to work properly. To run as a stand alone macro, just insert your credentials directly into the code and uncomment the input boxes. (There are notes included in the macro.)
********* UPDATE 12/13/2011 **********
Rolling out December, 16th, 2011, the SEOmoz Free API will be severely limited.
The new rate limit for the Free API is 1 request every 10 seconds.
(Compared to 2 or 3 per second that we’ve been running.)
And batch requests for the URL Metrics Call has been limited to 10 URLs for every POST request.
You can read the official SEOmoz API update here.
I’m currently looking into alternatives, and am entertaining the idea of building my own API.
**************************************
********* UPDATE 9/5/2012 **********
Version 3.0 is now in beta testing. (very very beta)
Rather than a vba macro, this new version is an installable Excel Add-in.
Check it out here: http://www.businesshut.com/seo/seomoz-mozscape-add-in-for-excel/
This also renders most of the instructions on THIS page, null and void. The new page above will contain the latest information for this project.
**************************************
Suggestions and support are welcome. Thank you to everyone who has given feedback thus far.
It’s weird that I have to put in all of my information from the seomoz website. Is there any way to make it so there isn’t so much information needed?
I could make it a little easier if I can find a way to do the SHA1 hashing within Excel.
However, it would then need for you to provide your secret api key and your access id so I could combine them with the unix timestamp to create the signature hash.
I could also just leave in my api for everyone, but I’m afraid that would end badly. 🙂
Please let me know if you have any more feedback. Thanks!
I Think SHA1 Don’t Need Built-in Command to be Implemented.
Im very new to the SeoMoz Api.How do I generate the timestamp and signature?
I either get “invalid signature” or “Request timed out”
Sorry to hear it’s not working for you. A few people I had testing this also had the same error message. For some, it worked if they refreshed the SEOmoz page to get a new signature. Simply refreshing the API page should generate the new credentials for you. The timestamp causes the signature to change each time, so make sure you copy all 3 credentials each time you try.
If you could give it another try and let me know if it works, I’d really appreciate it. I’m trying to get to the bottom of this bug, but it’s proving to be fairly elusive. Thanks for trying it out!
I also got the “invalid signature” error. It looks like when you copy and paste from the API credentials page, you’re copying and pasting in a carriage return at the end of the timestamp and the signature (making the data in the Excel cells two lines tall instead of 1). Delete the carriage return and it should work fine.
Thanks Ruth! I knew it was probably user error. 😉
Thanks for the interesting spreadsheet. It seems to work for me using the URLs you included. For some reason when I enter my own URL it turns into a link. When I hit Alt-Ctrl-F9 nothing happens.
How / where should I be entering my own URLs and how do I make it perform an update.
Thanks for your help.
-Mark
Mark,
It changing to a link shouldn’t hurt anything. I would check to make sure macros are enabled. Also try saving and reopening. Sometimes it just takes a clean start to get things working. Let me know if still not working.
Thank you so much for this tool. I have modified it a little bit to do backlink analysis for domains. I take the mozRank of a linking domain and compare it side by side with it’s PageRank to see if there is a significant difference. It isn’t as automated as I would like but it gets the job done. Great tool!
My pleasure. If you have any suggestions for improvement, please let me know.
Is it possible to add a PageRank api to it? I have been using http://josh-fowler.com/prapi/?url=www.businesshut.com to return the PageRank for google docs but not sure how to use it in excel. It would be nice to add this metric in along with the SEOmoz metrics.
Jeff,
That’s a nice tool, and should be extremely easy to interface from Excel. I’ll have to see about adding it to v2.0. 🙂
Sounds great, let me know when you release v2.0. I’ll check back now and then and look for updates.
Nice! this is awesome! thanks!
Thanx a million! This Dutch/French speaking SEO’er will have a great time with it the next couple of days!!!
Jason,
I downloaded the spreadsheet, got my API credentials, entered them in the spreadsheet (including API key) and still see “unauthorized” response. Am I doing something wrong? Ctr+Alt+F9 does not change the result.
Lyena,
Try refreshing the api screen to get a new signature and timestamp. Also be very careful when copying, so you only get the text you want.
It seems that entering the credentials can be tempermental, but if you give it a few tries, it’ll work.
Please let me know if it doesn’t.
Works great for me. Thanks for sharing this really cool tool.
This will be really useful to get an idea of what kind of competition I am up against, and to be able to track changes in site metrics as a result of link building.
Thanks everyone for your comments and assistance in testing!
I just copied the text of the macro into this post if anyone is interested and doesn’t want to download the Excel file.
If there are any Excel-Using Mac Users or OpenOffice users on any platform, please let us know if this works for you. There have been reports that it doesn’t, and we could use some help troubleshooting.
Thanks!
This is great work. Very impressed!
Great work! I think it would help if you could add the # of linking root domains as well as PR(right next to the mozRank column)
Hi Jason,
This is a nice and very well done tool, thanks for sharing!
I’ve added a few extra tabs for some URL manipulation and some extra rows on my version but I’m a little annoyed with the fact that the Spreadsheet tries to update the results all the time. I don’t know much of VB so the only way I’ve found to stop this it is by setting calculating options to manual.
Is there a way we could make it update the results only when clicking a button or hitting CTRL+ALT+F9?
Thanks Leonardo. I’m glad you like it.
I agree that the auto updating is annoying, and I’m working on a new version that you run when you choose.
Until then, there is a setting in excel to tell it to not auto update. I don’t remember the exact location, but I’ll post details here soon.
FYI – Version 2 was released a few months ago which allows you to update on-demand.
This great code works amazing!
Do you know on more free SEO API that I could use – i.e. retrieving domain age, H1, H2, and so on?
I am total noob to Seomoz api, but I want to learn how to use it. Thanks for sharing this, it is a good start for me!
Downloaded and running in Mac Office 2011, doesn’t seem to work. Any advice?
@JR Sorry. So far, I haven’t heard of anyone having success with this in Mac Office. If there are any success stories out there, please let us know.
– Jason
I’ve enabled Macros, but I’m getting an error message saying:
“Excel found unreadable content. Do you want to recover the content of this workbook? If you truxt the source of this workbook, click Yes.”
I click Yes, and it opens, but a dialog pops up saying:
“Excel was able to open this file by repairing or removing the unreadable content: Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
Any ideas would be appreciated…
Thanks
Ben
Ben,
I just downloaded the spreadsheet from here and it tested fine. Are you using a version of Excel prior to 2003? Or perhaps on a Mac?
Do other macros work for you? As a test, maybe try recording a macro and making sure it runs. That error message sounds like it could be internal to your installation of Excel.
Let me know if that doesn’t work.
Thanks,
Jason Green
Thanks for the swift reply, Jason,
I’m using Excel 2007 on Windows XP (running within Parallels Desktop on a Mac).
I’ll have a go at recording a macro, as you suggest and get back to you.
Cheers
Ben
Hi Jason,
Followed your advice and it’s now working. You were right it was a problem with my installation of Excel. I didn’t have VBA installed. Once that was rectified, everything worked as expected.
Many thanks
Ben
@ben,
Glad to hear it. Yours is the closest we’ve come to getting this to work on a mac.
Thanks for the swift reply, Jason,
I’m using Excel 2007 on Windows XP (running within Parallels Desktop on a Mac).
I’ll have a go at recording a macro, as you suggest and get back to you.
Cheers
Ben
This is very impressive. I love the different ways people find to use Excel.
Using Office 2010 with Win7 64 bit, I had to change the declaration in Module1 to “Private Declare PtrSafe Sub Sleep Lib “kernel32″”
Worked like a champ after that!
Thanks a lot!!
Ryan
Thanks for the tip Ryan!
Also, if you remove the Sleep line from the main body of code, you can remove that top line all together. (The text parsing takes long enough that the additional pause isn’t always necessary.)
Finally, someone made an excel pull for the SEOmoz API. I seem to be having problem getting the macro to populate the data in the URL Report. Has anyone else had this problem? The query seems to be working but it does not output the result and populate the page with any metrics. Any advice on how i could fix this?
Thanks,
Gerad Hoyt
Gerad, If it seems to run, but doesn’t populate data, it’s probably an issue with the credentials. Verify them and make sure you’ve removed any line breaks.
Hi Jason,
Great excel sheet. I’m trying to expand it to our own needs. I’m pretty into macro’s myself. But ive got a strange problem. Couple a days ago i first downloaded your sheet and it works fine. Now when i download your sheet (without changing anything myself) it returns the word DA & PA’s. DA = PA which is wrong and it returns a DA of 12 for http://www.google.com, which is of course impossible. I’ve tested on multiple computers.
Maybe seomoz changed something in their api?
Thanks in advance
I’ve looked further into it and it looks like it is not returning the dpa and upa values, so the instr function can not find the values and is returning the value on the 7th and 8th position from the start. The return i got is:
{“feid”:122652924,”fejp”:9.407759854460711,”fejr”:1.894158562567842e-05,”fid”:3082169,”fjp”:9.519968051849157,”fjr”:2.568931948489892e-05,”fmrp”:9.237258154228616,”fmrr”:0.0005411216432240423,”ftrp”:9.236339696156261,”ftrr”:0.002787595313792627,”peid”:344304409,”pejp”:9.765527029172523,”pejr”:5.004495539278803e-05,”pid”:3917761,”pjp”:9.846357097352101,”pjr”:6.232894712750642e-05,”pmrp”:9.324501871358258,”pmrr”:0.003056393837973641,”ptrp”:9.390477000148232,”ptrr”:0.005093754609879058,”ueid”:27606362,”uemrp”:9.043758409740917,”uemrr”:7.048874431933717e-06,”ufq”:”www.google.com/”,”uid”:33368003,”uifq”:959543,”uipl”:657331,”ujid”:27638415,”umrp”:9.04636030324413,”umrr”:7.098856531341386e-06,”upl”:”google.com/”,”urid”:12057280196,”us”:200,”utrp”:8.382619213751632,”utrr”:0.001523533890770625,”uu”:”www.google.com/”}
Thanks very much for creating this!
When I run the macro I get a message that said it took 2 seconds to process 6 URLs however the table does not populate.
Any ideas?
Thanks!
Joe
Joe, It’s probably an authorization issue. Check that your credentials don’t have any trailing spaces. In these cases I usually refresh my creds on the seomoz site and try again.
Thanks so much for this! One problem I ran into (PC, Excel 2007) is I couldn’t paste my Access ID, Timestamp, and Signature into the cells b/c they were merged. So I unmerged them and inserted each into B7, B9, and B11, respectively. But it doesn’t work. Am I doing something wrong?
Annie, You shouldn’t need to unmerge the cells. To paste your credentials, double-click the cell so it only pastes the text. Let me know if that doesn’t work. 🙂
With how many URLs does this work? I have a latop with 2 x 2 GHz, 4 GB, Win7, Excel 2007. Any ideas?
Matthias, The only limitations are those imposed by the seomoz api. (Free accounts can hit 1million url’s per month.) There is also alimit on how fast you can run them, which is about 2 or 3 per second. If you’re willing to wait, the limit is 1million, but that would take about 4 straight days of processing.
Hi Jason, thanks for your replay.
Today, I tried to pull data for 50 K urls. It took 2 hours and then the excel sheet stopped with “success” at 7210 urls. I tried it a second time with 25 K urls and again it stopped at 7210 urls with succuss. Memory and cpu usage are low during runtime. Do you have an idea why it stops at 7210 urls even if there are more urls to go?
Hi Jason,
Thank you for creating this. I’m new to Seomoz api and was looking for a way to implement it – this might just be it.
One slight problem i’m having – I can enter my credentials and they work ok, then I run the macro and it comes up with the ‘please be patient..’ message. If I don’t press ‘ok’ the message doesn’t disappear and if I press ‘ok’ I get a message saying ‘It took 0 seconds to process 0 urls’ – I have 2 urls in the URL field.
Any ideas? Thanks.
I probably should have mentioned..
I’m using Office 2010 with Win7 64 bit so I tried changing the declaration in Module1 to “Private Declare PtrSafe Sub Sleep Lib “kernel32″, but I got the following error message;
Compile error:
Expected: end of statement
I then removed the sleep line as you suggested and got to the stage above.
Robert, This sounds like a credentials, or usage limit issue. I’d re-download a fresh spreadsheet and try again. The macro can be a bit touchy, so if anything got changed, it might blow the whole thing up.
I used default version did not work for me. I tried making changes to macro by entering the url below format (which worked for me in the browser) . Doesnt work in the macro 🙁
I used this format in the url
http://%5Bmember%5D:%5Bsecret key]@lsapi.seomoz.com/linkscape/url-metrics/www.cnn.com
Everything seems to be okay when I add my URLs and run the macro, but the data just doesn’t populate in the fields. What am I missing here?
Using Windows 7 / Office 2010
(even tells me “It took 10 seconds to process 13 results”, but results don’t show)
Help!
Same problem here.Running Excel 2007.
I’m getting the same result as well. However, my setup is Windows 7 and Excel 2007..
This tool does not work at all… API info is correct, running Excel 2007, when I run the macro it does not display any data at all. It does, however, say it took 12 seconds to process 10 results. Not sure if coding needs updated or if this is just a super crappy tool…
Thanks for the interesting spreadsheet. It seems to work for me using the URLs you included. For some reason when I enter my own URL it turns into a link. When I hit Alt-Ctrl-F9 nothing happens.
How / where should I be entering my own URLs and how do I make it perform an update.
Great job, shame i haven’t managed to make it work yet but i’m working on it. You have all my moral support!
Great work Jason! A very handy tool. I am wondering if this could be adopted into a spreadsheet that can pull Google rank providing the URL and keywords, the same way the SEOMoz Rank Tracker tool does. Any thoughts? Thanks!
good article .i learn from you web .I’m new to Seomoz api and was looking for a way to implement it
Hi
Been trying to get this thing to work for 2 days.
I have checked the correct credentials, including checking for any empty carriage spaces. I have refreshed the moz api, but still no luck. I have tried various downloads of the spreadsheet.
Running windows 7 & Excel 2010. Should work!
It hangs on the first message.
Hey everyone! We fixed the issues with Version 2 of the spreadsheet. Please check above for the link to download Version 3.
There’s a 10-second limit per request, so it takes MUCH longer to process a list of URL’s, but we still think it’s a good tool for processing small lists.
Keep the feedback coming. You guys are great!
I just ain’t getting this and it’s frustrating as I know it’s not that difficult.
Here is what I am doing:
1. Enabling the macros etc when opening excel.
2. Imputing the required data into the credentials page. I am guessing the timestamp is sample expires on the seomoz api page?
3. Pressing the alt+f8 button and nothing at all is happening.
Sorry for being such a noob, hope you can help.
Regards
Bobby
Hi Jason,
I was running the tool last night and again this morning and it’s not pulling any data except Links (uid) and External Links (ueid).
Is this a problem I’m just having?
I’ve tried it with both my personal Moz memberID and our company’s.
Thanks!
Hi George,
We’ve encountered exactly the same problem. The issue coincided with an SEOmoz update – http://www.seomoz.org/blog/following-the-trend
I’ve emailed their support so when I get an update I will post it here.
Thanks for the update Robert. They rolled out a Beta of the new API, so I wonder if they’re starting to shut down the “old” version?
We are participating in the beta testing of the new mozscape API, and will have a plugin version ready for Excel soon. Hopefully we can get this done quickly and everyone can start using the new version of the API.
Please let us know if you hear anything else.
Unfortunately they couldn’t help because they don’t provide email support for the Free Mozscape API. They suggested to post the problem on https://seomoz.zendesk.com/forums/293196-developer-discussion-group to see if the community can assist…
Thanks Robert. I’m about a day away from releasing the plugin/addin. It works but isn’t polished yet. If anyone wants to test this beta version, I’d be happy to email you a link.
I’d be happy to test it in beta Jason. Thanks for your commitment to improving and adapting this tool.
Robert (and anyone else interested in testing),
The first/beta/draft version of the Excel Add-In for the Mozscape API can be downloaded below. Just unzip the file and run Setup.exe. It will install a new right-click menu item.
Select any number of URL’s in a spreadsheet.
Right-click the selection.
Choose “Run Mozscape on Selection”
Enter your access id and private/secret key when prompted. ***You’ll only need to do this once! No more returning to moz to download a new signature and timestamp! 🙂
It will take 10 seconds to process each URL, so don’t select a column of 10,000 URL’s. *This is a moz throttling limit.
It will then create a new sheet called “Mozscape” with all of the url-metrics data.
*If you already have a sheet named “Mozscape”, it will be deleted and replaced with the new data.
Please let me know if you have any suggestions. Thank you all so much for your support.
Download the zip file here: http://www.businesshut.com/wp-content/uploads/2012/09/MozscapeAdd-in.zip
– Jason Green
FYI – I just tested the download link and got a warning that “It could be dangerous.”
It’s not….
You can decide if you want to trust me or not. 🙂
– Jason
Hi,
Is there something else that has to be installed such as visual studio to get the addin to install? It will not install for me. Also, for the vsto file extension, which program will be opening it ( I set it to excel which is probably not correct )?
Thanks
It should install any dependencies if it needs them.
Here are a couple ideas for troubleshooting:
1. This version only works with Excel 2010. (and probably only on Windows)
2. Make sure to unzip the entire zip file. *Don’t run Setup.exe while it’s still zipped or it might not be able to find the other files for installation.
3. You only need to run Setup.exe. The other files contain the code used by the installation process.
If you’ve done everything above and it’s still not working, please let me know. I’ll also be testing this on a few different computers today.
I managed to install to excel 2010, but I had to install visual studio 2010 tools for office runtime first.
I tried to follow your instructions and was able to get the addin to run but I am now getting the following message : “The remote server returned an error: (401) Unauthortized. Here is a screenshot :
http://screencast.com/t/40JrsqUoi
Thanks
Aron, Thank you for working through the installation process!
That error means there’s a problem with the credentials. Are you copying your Access ID and Secret Key from the moz api page? Use the 2 fields in the green box, and make sure to delete any extra spaces that might get added in the copying process.
You sound like you know what you’re doing, but I don’t want to take anything for granted.
Please let us know how it goes.
*If you already saved your credentials, I’m afraid you’ll have to remove and reinstall the add-in to clear and add them again. *(Feature to be added.)
I’m going to close the comments on this page so we can move the conversation to the new page. We’ve released a new version that is entirely different from everything described on this page.
Please visit the page below to get the latest version and continue the discussion:
http://www.businesshut.com/seo/seomoz-mozscape-add-in-for-excel/