Build your own SEO Tools – Ubersuggest, Google Docs and ImportXML()

written by William Patton on March 16, 2013 in 10 Minute SEO with 9 comments

If your an SEO or a online Marketer of any kind you’ve probably done more than your fair share of keyword research. We all have our own methods of doing keyword research so I won’t go into too much detail about that but there’s one universal job found in all of our methods.

Lists of keywords have to be built and then evaluated.

I’ve built a tool to do help out with building keyword lists using Google Docs and Google Drive. If you haven’t heard of Google Drive before then you should read all about what Google Drive is over on TekSocial.

If you don’t have time to read the whole article and build this tool yourself, don’t worry I’ve got you covered, just click the button below to get the app.

PattonWebz Ubersuggest Google Doc

Building your Keywords List

I make good use of Google Suggest to find keyword ideas in the early stages of building my lists. I use it for a couple of reasons. I know that those terms have search volume and I can easily see how searchers link my keyword with other search terms to get some natural language variants for my chosen term.

Getting the whole list of all suggestions Google will give for your keyword would take someone a long to do manually so we use tools like Ubersuggest to speed up that process and give us all the auto-suggest keywords for our term with an easy way to copy them all to the clipboard for use in other tools.

When you have that information you could then paste it into the Google Adwords Keyword Tool and get some search traffic information about each keyword to begin the evaluation process or combine the list with an existing list and remove duplicates.

There’s plenty of things you can do with an auto-suggest keyword list.

Ubersuggest Google Doc

We can go one better and automate the collection of all of those keywords from Ubersuggest into a Google Docs spreadsheet using ImportXML() and XPath.

You could then still copy/paste them into Adwords or do whatever you want with them, as well as have a copy of the original list saved.

Building an Ubersuggest App

The steps to building a simple version of this app are surprisingly easy. Simply follow the instructions and copy the code into the suggested boxes.

• A cell to enter the keyword to search.
• Convert the keyword into a URL friendly version.
• Combine the keyword into the URL.
• Get the page with ImportXML()and use XPath to find the keywords in the page and place them in the sheet.

So just 4 steps? Not that hard!

1. Cell A1 will be the cell that the keyword gets entered into.
2. Click cell B1 and use Substitate() to change the keyword’s spaces into dashes: =substitute(A1, " ", "+")
3. You need to combine the Keyword into the URL so we do it in 3 parts. Part 1 is the beginning of the URL, part 2 is the keyword, part 3 is the end of the URL which contains locale and search type. Put the start of the URL into cell C1 http://ubersuggest.org/?query= and the end into D1 &format=html&language=English%2FUSA&source=web. The end of the URL requests standard web results in English for the USA locale. We can then build the query right in our import or we can build it in it’s own cell. We’ll be using concatenate() to join the pieces to for the URL and putting it in it’s own cell. Click in cell E1 and paste =CONCATENATE(C1,B1,D1). That will join all the pieces and make a clickable link for you if you want it.
4. We get a copy of the page using ImportXML() and use Xpath queries to look through the page and find elements with a specific class. I’ve looked at the HTML of the Ubersuggest pages and they give their keywords the “keyword” class and search type class, which for us is “web”, and they are all listed in H4 tags. It’s all done with a single command so just paste this into cell A3 and the keywords will appear in a list moving downwards through the cells. =ImportXML(E1,"//span[@class='keyword web ']")

The more advanced version of this app was inspired by a Google Doc by Jeremy McDonald of BeetleWeb which accomplishes the very same thing as mine.

The advanced version brings in the ability to select the locale for results and it looks much nicer that the simple version. With a future version i have plans to enable the ability to change search type.

Both the simple and the advanced versions of this app are available in the Google Doc. Just click the button below to view the sheet, make a copy of it and then enter your own keyword in the box. It really is as simple as that.

PattonWebz Ubersuggest Google Doc

Updated: April 10th.

Too many bots were scrapping Ubersuggest. He had to add a captcha to the site to stop mass bot scraping  This, of course, affects us little guys who just want to scrape a single keyword at a time. Don’t know about you but I would rather the service remained open and I had to visit the site, solve the captcha and then scrape the keywords than it being closed down.

Until I figure out a workaround or find a way for users to solve the captcha within the Doc this one doesn’t work anymore 🙁

tags: Google Google Doc Google Drive HTML Keyword Ubersuggest XPath

---

Related Articles