Faced with a new client, and having established a list of keywords they need to target, you want to evaluate the competition to find out what sites are dominating the SERPs for these keywords. However... being an SEO you're a busy guy (or gal), and you need it done right now. I've built a Google Docs tool to automagically do exactly that and this post will walk you through it.
The basis for this tool comes from a report in this linkbuilding post on YOUmoz which contained a neat little 'SERP Saturation' report. I don't know how Stephen made his snazzy looking report (he's now shared a few details in this comment), but in response to a few people asking about his I thought I'd put together a tool. Here is Stephen's report:
Cool, eh? We are going to produce something very similar, albeit not as pretty. We will automatically pull ranking data and tie into the Linkscape API to pull in some helpful metrics.
1. What does the report show?
So, what's the report all about? It is a pretty standard report, and most SEOs will have put together similar reports in their time. It shows which domains are dominating the results pages for the specified list of keywords. It is an excellent way to quickly see who the main players are, and see a few metrics for them.
Ours will be sorted by the cumulative number of times a subdomain has appeared in the top 10 of the search results over all the keywords we specify, and will display the mozRank, Domain Authority and Linking Root Domains for each. We'll show just the top 10 competitors in our report.
You can just duplicate the Google Docs spreadsheet I provide below, and change almost any of this to add, modify or take away as per your needs.
2. How do you configure it?
You must configure it the first time you use it:
1) If you've not yet done so, get a SEOmoz API key. Its free!
2) Open the Google Docs spreadsheet. In File menu select 'Make a copy' so you have a version you can edit (call it "Report Template" or such).
3) Go to the 'Config' sheet at the bottom, and enter your SEOmoz API details.
4) If you'd like to change the template for which Google URL to do (it defaults to UK for me), you can do that here too.
3. How do you use it?
Open your report template spreadsheet you just made.
1) On the config tab, paste up to 50 keywords, one per row, starting at cell B7 (its indicated).
2) Open the 'Report' sheet.
3) Now select 'Make a copy' and give it a name ("Client X Report" or whatever). This step is *essential* or the fields will not update properly (I'm working on making this not necessary - any clues?).
4. What should you see?
You should see a snazzy little report:
It shows everything I promised, and more even:
A colourful and interactive, albeit it slightly wonky, graph! What more could you want?!
5. Under the hood
You don't need to read this section if you are neither interested in how it works or need to edit it at all. Besides which, I'm mostly just going to refer you elsewhere! A big shout out to Tom Critchlow, whose prior work contributed heavily to this little tool. Firstly, you need to read:
How To Build Agile SEO Tools Using Google Spreadsheets
Which introduces how to scrape the SERPs for ranking data. I modified what Tom did slightly as I wanted a list of subdomains, rather than pages, so there is a bit of string cropping (and fudging!).
Next you need to read Ian Lurie's post (which Tom also helped with):
Linkscape + Google Spreadsheets. Together, at last.
Again, this I also edited. I changed the code around quite a bit, which you can see in the script editor. You end up with a function you can enter into a cell:
=getLinkscapeData(A1, 1)
The A1 is a cell reference to a URL, and the 1 is a dummy parameter to prevent annoying caching issues.
For a look at the full code for the Linkscape API interface, and some pointers on how to modify it to suit your needs I've put up a separate post on Using the Linkscape API with Google Docs, which includes a simpler example spreadsheet to try the code out with.
The rest of the spreadsheet is a few simple bits to filter and cumulate the necessary bits and pieces, along with a few tricks to try to sidestep some bugs in Google Apps. Nothing in the sheet is protected (there are a fed hidden columns) so you can take a look at the workings. If you have specific questions, post them in the comments and I'll try my best to answer.
This was my first real foray into Google Docs, so it might not be particularly elegant. Also the document seems to have trouble updating sometimes - if anyone has a solution that would be great. In the meantime, if you just 'Make a copy' it seems to force an update.
6. Wrap up
Ok, it isn't in depth analysis, but if you have a keyword list, and want a very quick peek at what domains are players, and their general stats, this tool gives you a quick and dirty look. Most importantly - it is free and open, so you can tweak it to your hearts content.
Questions, comments or suggestions are very welcome - post below and I'll get back to you.
I love this post Tom - and I love seeing people hacking stuff up in Google Docs as a result of my posts :)
Massive thanks to Ian for getting the linkscape API working in gdocs - that's opend up a world of awesome.
Thanks to you for all the awesome work with the bits and pieces that I glued together! It is just one example of how powerful both Google Docs and the Linkscape API can be, especially when brought together. :)
+1 this is awesome. Thanks for sharing! With the social api data, seomoz data, serp scraper and now this. Google Docs is indeed a prettey sweet tool for SEO.
Hey Norwegian SEO. Which SERP scraper do you mean? I'd like to take a look!
This is an awesome spreadsheet. I've been using it regularly. I don't think it's working anymore though. It doesn't seem to be pulling the data from the SERPs tab into the Report tab.
We are noticing the same thing now. Glad to see we're not the only ones. Tom - any chance you can reach out to the Mozzers to find a fix?Thanks!
Same here! It's still pulling the SERP data, but the report is not working. If anyone has an update, please let us know.
I take it no one has responded as of yet? I am going to leave a link in regards to our plight
Help!
As the tool seems broken, and I do not understand why, I tried to build my own stuff.
And now I give it back to the community. All comments are welcome :-)
https://bit.ly/seoops
(You must make a copy of the spreadsheet)
Great stuff, but where can i modify the query string? I need to google.hu instead of google.com! Thanks!
You have a sheet called "rankings", you can modify the B1 cell formula. All you need is to substitute the "google.com" in the CONCATENATE function by "google.hu" and copy your formula in all cells from the B column.
So this may be a dumb question...what is the google query template for US?
Signed: SEO noob/webdev noob learning as I go here
My bad, should have included it - sorry!
This should do the trick:
https://www.google.com/search?pws=0&gl=US&q=
Paste that into cell B2 on the Config sheet. :)
Hi :) this looks great, but do you happen to have the query template for Germany? so .de?
Thanks!
Tatiana
Yep, this should work a treat.
https://www.google.de/search?pws=0&gl=DE&q=
Let me know how you get on.
Macht spaß! :)
pasting it to b2 did nothing. But swaping the uk search query with the US one seems to have done the trick.
yeah well, this is the part where just basic reading and creative-tech part of SEO comes in...if you fail here, you will fail elsewhere
its very easy to change country...just think about it what defines a country. He already gave the answers
Thank you for asking. I felt I was clueless that I couldn't figure that out!
**************** FIX FOUND ****************
Hi All,
I was having the same issue but managed to work out a fix. I've created a new version of the spreadsheet which you can access at:
https://docs.google.com/spreadsheet/ccc?key=0Alrm-DB-8eBzdDNFSWthczIyVHhYMUd3eHA3NzVNSWc
Enjoy!
Matt
Tom,
Thanks so much for sharing this! What a great resource for our MozAPIers! I've added your post our our API Announcement about Ian and Tom's previous work.
You've created a wonderful resource for all of us. I look forward to seeing your other contributions.
Thought I would also share a few useful resources for folks.
Check out our API Dev Forum.
We also have an App Gallery featuring nifty products that use the API. You might like the Excel Integration or the SEOmoz Word Press plugin too!
Keep up the great work guys!
Hi Sarah!
Thanks! Hopefully, it'll prove a useful resource to prompt more people to create yet more awesome tools with the Linkscape API. I'm certainly looking forward to seeing what people do with it. :)
Also - thanks for sharing the Excel integration link. I think people who are enjoying this post should hopefully find that useful too. :)
Um, Moz App Gallery, sweet...
UPDATE: We have noticed that SEOMoz is now clamping down on the number of API requests allowed by the spreadsheet. As of this writing, the real limit is very close to the advertised 1 request every 10 seconds (or 1 request every 5 seconds for SEOMoz Pro users that have requested the rate increase). The errors we're seeing come in the form of both "authentication errors" and "too many requests errors" They are both caused by the same problem.
If you are seeing these errors with your own spreadsheet, go to TOOLS --> SCRIPT EDITOR... when you're viewing the spreadsheet. On the new tab that opens, change line number 85 to read this:
sleepyTime = (Math.floor(Math.random() * 10000) + 1000);
Make sure to save the script and then go back to the spreadsheet and refresh the page. That should help. Also, as is indicated on the bottom of this page (https://www.seomoz.org/api/pricing), be sure to send an email to [email protected] and request that they increase your rate limit to 1 API request every 5 seconds.
Let me know if you have trouble. I'll try to help if I can.
Jerod
Netvantage Marketing
Thanks for helping!
Hey, all. I'm pretty sure I fixed all the problems with this spreadsheet. I've written a blog post about it and included a link to the new Google Doc so that you can make a copy for yourself. Let me know if something doesn't work or if you'd like to see something done differently.
https://netvantagemarketing.com/blog/serp-saturation-report-spreadsheet
Cheers,
Jerod
Netvantage Marketing
You need to sign in with your google docs account first then it gives you the option to "Make a copy"
It's working for me but only for the default Google URL for the UK. What should I be putting in for the US?
Hi,
I used the spreadsheet for one of my clients. Facing following issues:
1. In the SERP tab not all the keywords are populating the top 10 serp result for that keyword.
2. Getting a message at the top "This spreadsheet is about to reach importxml limit"
3. Also I noticed that after generating the mozrank and domain authority data, spreadsheet updates automatically w.o hitting any tab, button etc.
Would appreciate your answers to above
I second these issues and additionally:
1. Also my main report page has an error where the charts should be
2. The table data is only part filled.
No data in domain column other than http https:Top ten count has first column with data in and no othersMost MozRank columns have 'No API' Details but 3 do have data?Linking domain columns are empty
Yes, a few people have started to say something is up with the Link Domains columns, but I am not sure why the problem has suddenly appeared. Will attempt to investigate. :)
I just made a copy, configured it, and entered a few details and it collected all the details fine. Maybe someone having a problem with "Linking Domains" could PM me a share link of their spreadsheet so I can have a poke about. :)
Thanks Tom
Would be great to get the linking domains working too.
Your post has really got me thinking more about the API and number crunching in general. Just hope my brain can cope :)
Great post! I love actionable how-to's... especially when they're useful.
Great post! With just a few tweaks to the chart I was able to get the entire domain names to display, which allows for the export of a nice standalone image to represent search saturation for the given keyword set.
I found the charts hard work, but hadn't ever tried them before. What's the secret to making the chart prettier?
I think I'd usually use Excel/Numbers to make a chart, but it would certainly be nice to know! Thanks. :)
I've found that, unlike with the visualization api, the charts embedded in google spreadsheets aren't very customizable in terms of appearance. What I didn't like about the chart in your quite awesome spreadsheet was the legend cutting off the domain names. Since there is no easy way to customize the appearance of the legend, I removed it (Edit Chart -> Customize -> Layout:Legend set to "none") and switched to a column chart where the domain names are listed along the bottom axis. It removed the pretty colors, but it allowed me to export the chart as a standalone image where the domain names were fully readable. You just have to stretch the chart until the longest domain name isn't cut off. Then, after export, I cropped the image in photoshop down to remove the unused space. Once again, nice work on the spreadsheet!
A useful share - thanks. Special thanks to Trevor for sorting the domain linking stats.
This is really quite awesome. Nice work and thanks for sharing!!!
Thanks for this it looks awesome!
You need to be signed in (Google Account) to make a copy.
Does that help ?
I've looked everywhere at the bottom of my Google Spread sheet, no where can I find where it says, "Config." Am I supposed to rename sheet one, "config?" If so, where do I enter the API details?
Thanks,
RRA
I have the same problem. Don't see anywhere to enter my API key.
You need to download this google docs file.
Thanks!!
How did everyone else know that but us? I didn't see it in the blog post.
Thanks Bartell for clearing that up.
It appears in step 2.2 of the blog post, but maybe I should have made it a bit clearer. Sorry! :)
Fantastic use of Google Docs and the api - Much appreciated!
Excellent work Tom!
But I'm experiencing the same problem with the No API details, I have put them in multiple times and also checked for spaces... {solved}
They were in the wrong fields...
Keep up the excellent work!
Thanks!
Thanks for the great share i was looking for something. Can you please describe two field two above fields.
Please let me know what does the numbers means under 'Top 10 Count' and 'Domain Authority'.
Thanks
You can read about Domain Authority on the SEOmoz API wiki here: Domain Authority. Essentially it is an indicator of how well a domain should ranking, independent of any particular search phrase or keyword.
"In the Top 10" counts how many times a domain appeared in the top 10 Google results over all of the keywords you used. If it appears multiple times on for one search it is indeed counted multiple times.
Does that make it clearer? If not badger me more. :)
Thank you so much Tom
sorry duplicate reply
Great work Tom, and big thanks to Trevor for the domain linking fix, will have to spend a weekend tweaking this.
Always good to see our community is still able to share and help each other out...
Hey Tom,
This is awesome, something I was planning to do but never had the time! I would like to be able to paste a list of sites to exclude in the calculation, sites which are not actually our competitors, even though they rank e.g. wikipedia, review sites, article directories etc. How possible would that be?
Thanks, great work!
That should certainly be doable. In the "Report" sheet you'll find some hidden columns to the right. The right set of these is sorted using the SORT() function to be arranged into descending order by the number of 'In the top 10' appearances. The top 10 from this column are what are displayed in the pretty report box.
You could use the FILTER function to filter out a list of domains (I think!), and so then, combined with the SORT(), this should do exactly what you want. Let me know how you get on! :)
Yes, it worked using the Filter function , thanks Tom!
Thank you very much. I do not know that much about working with API's so something like this is really welcome. So thanks again. I am also trying to get a graph of how the DA of the links is distributed. This can give some nice info about the linkprofiles of the competitors.
Easily one of the most awesome blog posts this year! I use to do this kind of analysis manually in excel - this is a huge time saver.
Thanks!! It is great to hear it is saving so many people so much time. Have fun with it. :)
maybe the author should update the Gdoc spreasheet. It's not working with the current formulas.
Hi. What is the problem you are having?
a fat thumbs up for you! Now I can see the overall picture of my target keywords and who is dominating overall. I see I am in 3rd place. This sure beats trying to eyeball who is the most dominant on the ranking reports. You are a pimp!
Thanks - glad you are enjoying it! :)
Cool tool. It's working great for some keywords, but I'm getting nothing for others, such as "top cars," "new york times," or "ice cream." Is this a limitation of the free API?
I am feeling a little bit stupid beacuse i haven't any other word to describe this article than - awesome. But literally, i have no any other words except "awesome". So: this article is awesome, awesome awesome.
Hi!
It's not working for me either:
This spreadsheet is about to reach the following limits: Number of ImportXml functions.
Any suggestions would be much appreciated.
Ah, this is my fav tool and it is broken. What can I do??????
https://spreadsheets.google.com/ccc?key=0Aq_UJxEWLZfcdERkS2toMWVSYV9JYVB5ZTJOd01VTkE&hl=en&authkey=CLfhxdwO
I modified the original spreadsheet to also count the number of times a domain appears in the top 3. Depending on the keywords you are using, it might be too much information but it doesn't hurt to have the data either.
https://spreadsheets.google.com/ccc?key=0Aq_UJxEWLZfcdERkS2toMWVSYV9JYVB5ZTJOd01VTkE&hl=en&authkey=CLfhxdwO
Awesome - thanks for sharing! :)
Great to see people reworking this and creating more stuff with it. :)
Very cool tool. Thanks for posting!
Seriously awesome. Thank you.
When I ran it, the first table showed some results (3 rows fleshed out while retaining an empty "Domain" column), but nothing populated in the second table.
Mousing over the cells showed this error message:
Request failed for https://lsapi.seomoz.com/linkscape/url-metrics/?AccessID=member-#######&Expires=#######&Signature=######%3D&Cols=##### returned code 503. Server response: { "status" : "503", "error_message" : "Throttled" } (line 36)
Any ideas?
Fantastic tool, so easy!
One question: In the Config sheet I changed the "Google Query Template" from UK to US. Is there any way to get the location even more specific than US, such as SERP for Boston, MA or ZIP code 02130? What would I enter into the URL String: https://www.google.com/search?pws=0&gl=US&q=
Thanks!
Not as far as I am aware. The only way would be to customise your keywords so they all included something to narrow the search down. However, this makes more sense, because it more accurately represents how your potential clients would be search (and thus what competitors they'd be finding instead of you) - if that makes sense! :)
Great spreadsheet - thanks so much for sharing!
This is really great and very informative. Already discovered new competitors!
ABSOLUTELY FANTATIC!!
Awesome! Producing something like this was on my todo list and I'm happy to mark that as done :)
Tom -
This is phenomenal work, my friend! I think you've taken some huge steps here and this will be very useful to a lot of SEO community. When we can automate things like this, it will just make us better at our jobs.
Cheers!
This is a beautiful little tool, thanks Tom!
Big thanks for sharing this tool. Excellent post, excellent tool! Very admirable your creativity and resourcefulness.
Okay, I don't know if this is just me messing it up or what but every time I fill all of the data in the only thing I get when I actually "run the report" is "No API Details," an occasional 1 in Domain Authority, some 0.00s in MozRank, and a 0 in Top 10 Count. Other than that there's no data. Now, I don't use many API's but I do have experience with them.
This is a fantastic report and would save me so much time in competitive research since I'm an SEM Manager but at this point I think I'm going to be taking the long route. Lol. Any help would be greatly appreciated because I just can't seem to get this one to work for me. Thanks!
Stephanie
Hi,
Bummer you're having problems. A couple of things to try...
Just in case - make sure you double check that in cells B2 and B3 on the Config sheet contain your SEOmoz Access ID and Secret Key, and that they are the right way around. The Access ID goes in B2 and should start 'member'.
In the Report sheet, in cell D4 you will have:
=iferror(getLinkscapeData(B4, 1), "No API Details")
Change it to:
=getLinkscapeData(B4, 1)
The cell may now show an error message when you mouse over it which will help to diagnose API problems.
Are the domains all being pulled in correctly and showing their 'In the Top 10' count?
Have you tried "Make a copy" in the File menu? That often helps with Google Docs and its refusal to update!
Good luck getting it working!
If none of this works, drop me a PM and maybe I could take a quick look at your sheet. :)
Just as I was getting ready to message you I tweaked one more thing and finally got it to work! Thanks so much for your help and for the amazing report! It will truly save me soooo much time!!
Thanks again!
Stephanie
Hi Stephanie,
Could you tell me what you tweaked as I am having the exact same problem, thanks.
I too having same problem and can't figure it out. Plz tell me waht to do.
me three
Hi Stephy, please message me too about your solution, thanks
Hi Tom,
I'm having the same problem. In the spreadsheet, my columns are B3 for the Access ID and B4 for the Secret Key and I got the same error as Stephy. I tried changing them to B2 and B3 but that didn't fix it. The error I get says Invalid argument: https://lsapi.seomoz.com/linkscape/url-metrics/ + my accessID + &Expires + &Signature (with the strings in between). Any help would be appreciated
I am having the same issues Stephanie had. I tried trouble shooting everything I can and followed all directions above but no success yet. Any guidance on this?
Make sure that there are no whitespace characters around your api credentials. That happened to me when I copy / pasted from SEOmoz site ingelligence page.
Does this work, as this post was over 1 year ago?
In my report I get no data for "Linking Domains" and only 50% of the rows have data. What's strange is 4 of the rows have Moz API data, but the other 6 say "NO API DETAILS"
Any ideas?
Great spreadsheet! This was really the jumpstart I needed to get started!
Thanks Tom!
Super smart move mate! Simple loved this one!
Remember yesterday Thomas Hogenhaven of SEOmoz talked about the importance of community and how to motivate them as a team… I think this is another big example of the motivated community were people love and help each other in any manner they can!...
Thank you Tom once again this is really helpful!
Wow Tom! As a non-techie - I've been wondering what the potential of SEOmoz API and Google Docs is. But I never knew how to start tackling the SEOmoz API. Your post welcomed me in the ring! Thanks a mill!
Brilliant work! I'm going to have fun this weekend playing with these :-)
By the way - the new post analytics is awesome!
Tom, this is freaking awesome! I don't consider myself a developer and so just the word API has had me shaking in fear but man great breakdown, the steps were perfect ...the only hiccup I had was with the uk to usa conversion but you posted that in the comments!! really great post!!
Thanks for sharing this Tom, a nice little addition to my 'SEO Toolbox'.
Time savers are always a very welcomed addition.
Thanks for sharing that!
It appears that this tool is broken, any updates on it?
Very cool and extremely useful! Thanks so much for sharing!
Wow, this is the first time I've seen XML used for scraping web sites. Definitely more agile then scalablie. Can't wait to try this out. Thanks!
Man this used to work then....it stopped. I tried the newest 2 versions and neither seemed to work. I relly liked this tool. Gonna have to look at seeing if I can fix the issue.
This looks fantastic! Thank you so much for sharing I am going to have a go at this with a couple of projects today.
Hey Tom
This looks awesome, can't wait to have a play. :)
Cheers
Marcus
Wow, this is brilliant. Thank you for sharing this!
This is excellent!
badass. tools like this make having a quick conversation with a prospective client a LOT easier. toss in a quick list of keywords and you can get a loose idea of how deep the water is that you'll be wading into. many thanks for sharing this!
Thanks.
This scenario is exactly one the killer scenarios that I imagined for this tool. It is so quick to use to get an understanding of 'how deep the waters are' (nicely put!). :)
Same problem here. I only get partial updates on the "report" sheet, often just one cell with "http" in it, nothing else.
I've attempted to fix this but i'm hopelessly lost. I believe the error is caused by the fact all the results in the SERPS tab now have the following strings attached to the end of the URLs:
&sa=U&ei=E75pT5fZGorQgAeR8KXJCQ&ved=0CCwQFjAE&usg=AFQjCNGiJoMV3-eD0RlFSILDqHTu4c2D1A
It's the same for each result but changes when I run the report again so it's obviously some sort of session id. I think if this could be stripped out then the report would work again.
Nice post
Hello,
tool is useful. But i noticed one limitations in SERPs sheet, we are getting data in rowwise which is not helping us if we try to search particluar domain for particular keyword,
Data must be cloumnwise instead of rowwise. means keyword name should be in row and related to domain should be shown vertically related to keywords.
I can't seem to get this great tool to work anymore. Is there any plan for SEOMoz (or anyone else) to offer this functionality in a web app outside of Google Docs? This tool's functionality was awesome though I found it pretty buggy even when it "worked".
I think offering this as an SEMoz tool with a good UI would be fantastic.
Hello TomAnthony.. i am not able to make the copy of your given excel template....the options of making a copy is disabled...please help..
Still cannot make a copy. :-(
It's working for me but only for the default Google URL for the UK.
What should I be putting in for the US?
It's https://www.google.com/search?q=
Thanks self.
Does anyone know where the most recent article is about setting up the API in Excel? I recall Richard Baxter putting together something good a few years ago. Thanks.
Hello Tom,
Well it is obvious that I loved the tool and it does help a lot to know the competition but I am not able to see much data on the Report Sheet. I can see the domain and link results in the SERP sheet but no changes in the first one. Please tell me what I am doing wrong. I followed your steps closely and used only 7 keywords to test.
Hi All
This is a great spreadsheet. I've fixed the problem with the URL fields only showing "http:" and the trailing parameters - you can find my spreadsheet at https://docs.google.com/spreadsheet/ccc?key=0AvemHo4msf1pdFJQT2hweWthRkR2MW1LREdHdmJUM3c
The problem I have is with the throttling of the API. Does anyone know how to slow down the checks to less than 1 per 10 seconds without breaking the SEOMoz TOS? I've tried using the 'sleep' function, but not being a developer I got out of my depth pretty quickly!
Thanks
Guy
I've updated the spreadsheet so that you can select the country you want to see results for on the config page. There is also a link to lookup the country code... useful for anyone outside the US.
https://docs.google.com/spreadsheet/ccc?key=0AvemHo4msf1pdFJQT2hweWthRkR2MW1LREdHdmJUM3c
I am having trouble making this work. I am able to get other Google Docs to work such as "SEOmoz API for Google Docs" noted here https://www.seomoz.org/ugc/updated-tool-seomoz-api-data-for-google-docs.
All I want is the SERP tab to work, I don't even care about the report page. Any ideas?
I made a copy of the doc and changed the url to US. The result was little to no results. I think my url may be wrong.... Any suggestions?
Hi Tom,
I've followed your instructions to the letter, but can't get it to work. I see a warning by google docs on the number of importxml functions, but can't see any other obvious reasons for it to fail.
If I assume that my problem is along the same lines as CDMS's, following the instructions to open the script editor shows no scripts to edit - I just get the option to create a new one :o(
What do you think?
Hi, Hazel-
The warning about the importXML functions is nothing that anyone can fix. Don't worry, though. It's not affecting the operation of the spreadsheet at all. Google puts a hard limit of 50 calls to the importXML function in any single Google Doc and this spreadsheet uses all of those. So everyone who uses this spreadsheet gets that warning. Yeah, it's annoying, but like I said, it doesn't impact the spreadsheet's functions.
As for your other issues... Without a specific error message I really can't diagnose the kind of trouble you're having. Hover your mouse cursor over any cell that says "ERROR" and a more detailed message will appear. If you copy / paste the exact error message I may be able to help further.
In the mean time, you must follow all these steps precisely to get a working copy of the spreadsheet.
1.) log in to your own personal Google account
2.) go to https://karam.me/serp to access the shared spreadsheet
3.) click FILE --> MAKE A COPY to copy the spreadsheet to your Google Docs folder
4.) go to the "config" tab at the bottom of the spreadsheet
5.) in cell B5 type (or cut / paste) your SEOMoz Access ID
6.) in cell B6 type (or cut /paste) your SEOMoz Secret Key
7.) in cells B9 though B58 (or any subset thereof) type or cut / paste your keywords.
Then the spreadsheet should do some crunching and display the information you need on the "Report" tab.
If you're getting a lot of errors that say something like:
then follow the instructions I wrote just above on September 7th, 2012. That will slow down the requests.
I hope this helps.
I'm actually leaving town this afternoon and won't be back for a couple of weeks. If you can't get it working please post something to this comment string and perhaps some nice soul will jump in and assist…or I'll check it when I get back and help however I can.
Best,
Jerod
I've tried everything you've discussed above but still get 8 out of ten results bringing back errors. Can anyone confirm if this is working currently?
Hello, Adam. Have you tried adjusting the "sleepytime" variable on the code page? If you look back a few posts to what I wrote on September 7, 2012 you can see the instructions in detail. You may even try increasing that variable to 12 or 15 seconds just for testing and then decrease it gradually to find your own optimal refresh speed.
If you have any other questions please feel free to send me a note!
Jerod Karam
[email protected]
I try clicking on tools>edit scripts but it wants to create a new script, not edit the spreadsheet. What page do I need to be clicked on, and how do I tell 'Edit Scripts' to edit the ones in the current document?
The spreadsheet works, except for the most important part: the Seomoz stuff. I just generated a free api key, but the error response I'm getting in the sheet reads: "This request exceeds the limit allowed by your current plan."
Is this normal, since it happened on the very first use of the key, with just 3 keywords?
Hi, CDMS. I'm sorry for the delay in my response; I didn't see this comment until just now. (The email notifications go to someone else in our office.)
I think the problem lies in the variable "sleepyTime." This variable controls how long the spreadsheet waits between requests to the SEOMoz servers. According to the SEOMoz API Pricing page (located here: https://www.seomoz.org/api/pricing), a free user can make 1 API request every 10 seconds. If you look at the fine print underneath the table on that pricing page, you'll see that SEOMoz Pro Members can request to have that throttling rate increased to 1 request every 5 seconds. I'm assuming that's why a faster rate works for us with few errors. You can request to have your account changed as well.
In the mean time, if you want to slow down the requests so you get fewer errors, follow these instructions...
-- Open the Google Docs Spreadsheet. Click TOOLS >> SCRIPT EDITOR...
-- Scroll down to line 85 where you'll see the code: sleepyTime = (Math.floor(Math.random() * 4000) + 1000);
-- Change that number 4000 to 9000 so the line of code reads like this: sleepyTime = (Math.floor(Math.random() * 9000) + 1000);
-- Save the code document (click FILE >> SAVE or click the disk icon in the toolbar)
-- Go back to the spreadsheet document and refresh the page. You should see no errors (or at least fewer errors)
What you've just done is change the wait time between requests. Whereas the wait time that I had set was random up to 5 seconds, you have just increased that to be random up to 10 seconds. The spreadsheet will run slower but this should solve your problem in the mean time while SEOMoz increases your throttling limit.
If this doesn't work please let me know and I'll see what other help I can offer. If you're still getting errors, it would help if you posted the error in its entirety so I can have more complete information.
Good luck! Have a great weekend!
Jerod
What page of the spreadsheet to I need to be in for this to work? Selecting Tools>Edit Scripts wants to make a new script, not edit the document.
Sorry to seem dimwitted. I'm basically doing a whole bunch of things at the same time, and just NOW gotten to a point where this kind of web development has become a necessity....so here goes...
Will this spreadsheet keep "hitting" the SERPs after it's built? I've noticed that it seems like it keeps updating every once in a while, and I don't want my IP to be banned from Google. Is there a way that it can be "timestamped" so it shows SERPs on an interval of time (similar to when you use Rank Checker on Firefox and have it Check every week or every month)?
Thanks, and great post...
I have the same question as Alex. The spreadsheet doesn't seem to be working...
Great document, thanks for sharing
Well i see this is a great Tool, but i cant make it work because Google docs shows me a message about limits in formulas.
Is it possible to use it on Excel or Open Office?
Thanks For your work
Doesn't work for me ''No API Details''
I am also getting the no API Details on the REport page. Any clue to what this could be coming from?
You should try to open the script manager and to run each script individually, then go back to your spreadsheet. It usually corrects your pb.
I tried your suggestion of going into the script manager, but there are no scripts found.
anyone found a fix for this yet
this tool does not seem to be working properly anymore. any idea what we can try to use?
Simply amazing.It would be great al least 20 serach results on serp. What do you think?
Thanks for sharing anyway!
Yep well, unfortunatly this super great tool doesn't seem to be working anymore. I was using it regularly.. to sad. Any fix anybody?
This just might be a Google Docs limitation. I'd love to export that bar graph as an image but all the domains get cutt off and there doesn't seem to be a way to expand that. Any ideas?
First of all: AWESOME POST!Second: This open a role new door to develop agile/cheap tools and using something most of people knows how to use.
I'll work to get a few more info about each position in SERPS. Have someone else working on it?
Sorry - am confused. Working on what part? :)
I know quite a lot of people are working on using this as the basis for their own more awesome tool. @dohertyjf is doing some awesome stuff I think he'll share at some point, pulling in data from some other APIs. :)
This looks great, and I'm very eager to try it, however, I am having difficulty!
Could anyone help out a 'noob' in maybe pointing out why I cannot make a copy of the spreadsheet? I've tried in both chrome and firefox, tried the usual fixes such as clearing cache etc..but still no luck :-(
Any help would be much appreciated!
Thanks.
What happens when you try to make a copy? You are trying in the Google Docs File menu?
File>Make A Copy, however make a copy is simply greyed out and unclickable.
It is a Google App's account I am using, however this has never given me a problem before, any chance its due to that?
Issue resolved, Thanks anyway!
Glad you got it working. :)
What was your solution? im have the same problem. thx
i got it, thanks
Are you logged in to your google account? You need to be..
I was getting the same problem. Here's what I noticed:
Bottom line: Don't try viewing the file from within a Google Apps account.
Thanks for sharing this - hopefully it'll help out anyone who is stuck. :)
Great post...I am currently sharing this with all my colleagues
Wonderful job and than you for taking the time out to share. I am having issues with the links deal, I will pm you.
I know some folks are having a problem with the Linking Domains column - and that is totally my fault. I just realised it isn't part of the free API! Sorry folks! I am working on a solution to getting links for the domain level. In the meantime there are a bunch of other metrics you can pull for free, see a table here: https://apiwiki.seomoz.org/w/page/13991153/URL-Metrics-API.
Also I made a separate post about updating the code for other metrics: https://www.tomanthony.co.uk/blog/seomoz-linkscape-api-with-google-docs/ which should to do so. For links the best option for now is 'ueid' in the table which is the free links but is not the whole domain but just for the homepage. Sorry about that! I will look into a better solution for that problem. :) If you have the Site Intelligence API (paid) you should find everything works great. :)
Tried changing 'fipl'(Root Domains Linking to Subdomain) in the script editor to both 'uid'(External Links) and 'ueid'(Links) which are available in the free API, still no data is pulled through into the 'Linking Domains' Column, even have trying to copy the doc to force API update. Any ideas how to get this working?
Changing 'fipl' to 'uid' in the script editor is all i did, following this change should i change anything else in the script editor or the doc itself
There will be a second line of code to change too - Tom's explained it further here: https://www.tomanthony.co.uk/blog/seomoz-linkscape-api-with-google-docs/ (the bit about the long number is what you need to edit)
Also, thanks Tom, great tool! :)
BINGO!!!!
Thanks Tom for the post and TME Solutions for pointing out how to make the Linking Domains work!
For those, like me, using the free API and still not sure you can add the 'total number of juice-passing external links to the url' by doing this:
In the Google Doc go into 'Tools' and 'Script Editor' and find: inV="https://lsapi.seomoz.com/linkscape/url-metrics/" + url + "?AccessID=" + AccessID + "&Expires=" + Expires + "&Signature=" + signature64 + "&Cols=85899378688";
and replace with:
inV="https://lsapi.seomoz.com/linkscape/url-metrics/" + url + "?AccessID=" + AccessID + "&Expires=" + Expires + "&Signature=" + signature64 + "&Cols=68719509536";
Also find: IDomains = data2["fipl"];
and replace with
IDomains = data2["ueid"];
This, as is explained in the link from TME Solutions, is changing the 'fipl' to 'ueid' and changing the total of the cols accordingly.
I haven't needed to save a copy yet either.
Do that and it will work a treat :)
OK. It works now. Thanks
Finally :)
Thanks Trevor!
That worked! Thanks!
Looks great but...
I tried it but somethings are wrong
Some websites in the results are showing mazrank 9.64 and domain authority 99,95 and are simple websites, even when I put them in the opensiteexplorer, it shows domain authority 27%, linking root domains=4
must be something with the API
Did you try "Make a copy" to force an update? The Google Docs sometimes don't update properly after the sort() function, so you can see the wrong results. Very annoying but their bug, not mine! :)
Great work Tom! Thanks for sharing with everyone.
Going to save me lots of time when trying to find out who the main players are in any specific niche.
The linking domains column isn't updating for me but still a great addition to my toolbox.
Thanks Tom
This is great as was wanting to take a look at the API but always been something to try 'tomorrow'!
One question is anyone else having problems getting the linking domains to show up?
Thanks again Tom and also Jmueller as I had cut 'n' pasted with a space too.
I am also having probelms trying to get "linking domain" to show up, no numbers, everything else work fine. Tom, HELP!
This is awesome, I'll have to try it out! Thanks!
Everything seems to refresh as intended on my end when adding keywords, no need to create copy of the doc (except on step 1). Massive thanks for the template. It's great to start messing around with the API. Google Docs is indeed awesome.
You're very welcome. I hope it proves useful. If you add or modify it to add more awesomeness, I'd love to hear about it. :)
Edit: wrong reply sorry.
Wow Tom, this is fantastic and a great accompaniment to the link building post.
Is the linking domains function a bit temperamental? I've ran the report and everything comes through other than that data for each of the sites.
Google Docs is really temperamental with this. I tried loads of tricks to minimise, it but sometimes it gets stuck using the sort() function or updating the Linkscape data. Sometimes just waiting a few minutes work, but if you want to force, just make a copy of the spreadsheet and it will update.
Very annoying - but no work around that I know of, unfortunately.
Although... I could write a custom function to do the sort... Will have a look into that. :)
Hm, even making a new copy doesn't seem to be working atm -- I'll give it a few minutes to think.
Unfortunately, the number of linking domains is not included in the free API - you need a pro account to have access to that data. That may be the reason for the numbers not showing up.
Maybe mozBot Roger, through his big heart and generous nature, would be willing to open that metric up to the free API? (doesn't hurt to ask ;) )
Here's the list of metrics available through the API and whether they're included in the free API or not.
Thank you for this amazing work !
I tried this and followed your steps, but the infos:
MozRank, Domain Authority, Linking Domains didn`t show up.
Though i am a PRO Member.
Is there something i could miss...
Thank you
Lars
www.morepixel.com
You need to double check you've put your SEOmoz API details in the correct fields (a few people reversed them), and that there are no spaces included with them. If that doesn't work, PM a share link and I can take a quick peak at your sheet to see if I can help. :)
Hi Tom,
thank your for your help, i double checked it and in fact, i mixed up the secret key with the signature... for whatever reason.
Now it is working...
Regards Lars
Had the same isssue with the link data not populating. But other than that, this took a few minutes to roll out.
Thanks!
Meeta
This is an excellent sheet! I just don't like google docs so much.. they always sneak around in your stuff :~)
Fantastic work Tom!
Thanks for this, I'm pretty sure it will make my work (and others) much easier. Going to try it now!
Lots of thumbs up :)
Hey this is awesome, just had a play and set it up.
One thing I have noticed is it doesn't seem to want to pull in double listings ie 2 ranking domains for a given keyword.
Good work
Hi Craig,
Thanks. :)
I'm not sure I fully follow your problem. If the same (sub)domain appears multiple times in the same results it should be counted twice.
Would love to try to fix the problem if you can expand a bit! :)
Wow..Very unreal! I never thought of this quickie analysis be as fast like this. I used "SEO Philippines" as my root keyword and was fascinated that seomoz.org has 14 top 10 counts...hmmmm. Now Im wondering why seomoz.org appeared on the report for that keyword.
On the SERPs sheet in the spreadsheet you can see a list of the domains ranking in the top 10 for each of your keywords, so should help to investigate! :)
Now I see it. This one rocks! Keyword research in just a single copy paste of keyword set. A big hand for you Tom. Awesome ^_^
This is great. Shows all data except the number of linking domains. Not sure why this is the case. I will tinker and see If I can figure out why.
Thanks!
Yes, there is a problem with it (read this comment for details), but Trevor provided a good work around in this comment (with more details if you get stuck in this post on my site).
Hope this helps. :)
Is it just me or has this stopped working??? I hope not as its a great time saver. Please let me know.
Thanks
Great stuff
Do we need to have a pro account in order to get all the information or i have a problem ? I have only the 'domain' and the 'Top 10 Count' columns that worked :/ For MozRank, I have No API Details and for the last columns ...nothing.
In fact,the problems comes from the signature generation...then the algorithm is not good...when i make it manually ..it works. An idea for this problem ?
I'm not sure but i believe that i had solve my problem by commenting the indirectNoCacheBug function, refreshing the spreadsheet and uncommenting the indirectNoCacheBug function.
Is it just me or has this stopped working??? I hope not as its a great time saver. Please let me know.
Thanks
Is it just me or has this stopped working??? I hope not as its a great time saver. Please let me know.
Thanks
Same for me, Jasarrow. Any ideas why?
I’ve also been having problems, and others have too. What is happening is that now lots of people are using these tools and all of them are hitting Google from the same IP addresses (those belonging to the Google Docs servers) – so some of the time the Google Search servers are blocking the Google Docs servers because they can see they are being used for automated requests. Bummer. :(
Unfortunately, there isn’t much we can do about this at the moment, but I am looking into solutions and will post anything I find on my blog and via Twitter (@tomanthonyseo). I have a couple of ideas to look into which could pan out.
In the meantime you could try exchanging google.co.uk or google.com for an actual data center IP (Google for IPs). You may find a data center that isn't currently blocking - but it is only stop gap solution.
Thanks, looking forward to your new update.
Hey Tom,
Patiently awaiting a fix to the wonderful SERP Competitive Analysis tool. Still getting this message: This spreadsheet is about to reach the following limits: Number of ImportXml functions. Any breakthroughs re: the server problem?
Thanks!
:( to see one of the greatest tools to ever come out of seomoz blog broken. Could you send a link with information on how to exchange data center IPs?
Can't view link of domain! What's wrong with this?
You did an amazing job really!
One of the most useful and simple tool ever!
I love it!
Great Post, this totally increases my productivity. Thanks for posting.
Had to drop a note of appreciation. Very useful... for those that couldn't get it working, I had a space that appeared in the secret key when copying and pasting.
Be good to see this trended over time... guess you could save off the spreadsheet each week/month and then link into another spreadsheet to show +/- positions...
This may sound very silly, but just in case you are seeing values of 1.00 and 0.00 if you start playing around with the file, just make sure you don't have any URLs with the https:// in front...
[edit] Great post by the way, I have just discovered a whole new world ;-)
Hey Tom,
first of all great post and tool.
For some reason the 'linking domains' column dont work for me?!
Any idea why that might be?
Cheers
Mannie
Hi there - see my comment one above for a couple of links which will hopefully help you. :)
In File menu select 'Make a copy' Seems to be grayed out, any ideas?
Are you logged in to a Google account?
ahhhh that would be it :-)
Thanks for the fun tool.
Can I increase the number off keywords?
Does the tool count where I am cached?
Unforunately, Google Docs limits the number of importxml() functions you can run, I believe, which limits the number of keywords to 50.
In what sense do you mean count where you are cached?
Is the data from my IP? So what Google already knows about me. If I search Google for something I have already searched, Google will show me what I have already clicked on.
Depends, check the config page where it says 'Google Query Template'
If you have the "pws=0" in the template then the results are not personalized.
Powerful. On a related note, has anyone come across a Google Docs template that uses Google Analytics to report upon ranked organic search keywords on a monthly basis (snapshot) and on a longer term duration (to showcase changes in KW rank over time)?
Thanks that will be a nice little time saver!
Hi Tom, this is great stuff. Is there any way to increase the number of keywords to more than 50?