Kate's note 2/10/12: The tool isn't working right now, I'll update when I get the chance.This week at ProSEO in Boston, I presented on keyword research. I know so many people were pumped about seeing that topic ... invigorating right? Well the event team worked with me (as well as the rest of my team) to ensure that it was as such. There was even an awesome document made to help the attendees identify low hanging fruit in terms of content. I mashed it up from other Google documents written by Tom Critchlow and Ben Estes. The result was priceless.
Hundreds of people checking ranks at once? Not great. Many from the same IP? Even worse. Matt, don't kill me.
And the button disappeared. My file was doing magic tricks. *headdesk* Turns out this is an issue with Google Docs. You can create a masterpiece, just to have the beautiful work of art (aka a button) go missing. It happened with another doc internally at Distilled. So here are my instructions for accessing the document, saving it for yourself (a must!), adding in the button, and using it.
Download Use eHow
- Open the sheet. Note: it is read only and this is a different link than the one in my presentation. I had to because the other one got edited too much.
- Login to your Google Account. This has to happen for you to get the script.
- Go to File --> Make a Copy --> Save with whatever name you like and leave the "Also copy document collaborators" unchecked. You now have your own editable copy.
- Hover under the area where you enter the keyword, a box outline will appear.
- Right click and then click the blue text "Drawing" and click Delete.
- Open the "Insert" Menu up top and go to "Drawing" at the bottom.
- Make a button. Add text. Anything you want.
- Click the new drawing and find that same menu. Select "Assign Script".
- Type RankCheck in the box and click OK.
- Click on it and accept the script. You have to do this before you can use it.
Editing the Content SiteWant to use your new sheet to pull results for another site? There are a number of things you need to change. But let me assure you, it's not that hard. Let's say we want to use ezinearticles.com. Start with making a copy ... trust me, it's better this way.
- Find the content site's search page URL. http://ezinearticles.com/search/?q=pet+bunny
- Go to the second sheet in the document "eHow Scraper" and unhide all hidden rows (2, 13, 24, 33, 42)
- Cell A2 is currently =CONCATENATE("http://www.ehow.com/search.html?q=",$A$1,"") Change to =CONCATENATE("http://ezinearticles.com/search/?q=",$A$1,"")
- Do the same in A13, A24, A33, A42.
- In the cells below each of those is something like: =importxml(A2,"//span[@class='name Heading5']") Find what the containing type is for ezinearticles -- happens to be an h3 with no class.
- Change the cells to =importxml(A2,"//h3") -- the A2 changes to be the cell above it.
- Go back to the main sheet "Use eHow" and change the cell reference for the article title. You just need the A columns in the other sheet no concatenate needed here.
- Pull down 27 times to get the others, but you will need to remove the ones that reference the URL. (A2, A13, A24) You'll see them. You only want 25 results to pull rank because above 50, the rank checker gets annoyed. Don't annoy the rank checker.
- Now unhide Columns D and E and change the URL to http://www.ezinearticles.com - it's what the rank checker uses to pull rank. Use the http:// and the main domain of whatever you are scraping.