How to use eHow Document proSEO Boston

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.

It broke.

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

1. 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. 
2. Login to your Google Account. This has to happen for you to get the script.
3. 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.
4. Hover under the area where you enter the keyword, a box outline will appear.
5. Right click and then click the blue text "Drawing" and click Delete.
6. Open the "Insert" Menu up top and go to "Drawing" at the bottom.
7. Make a button. Add text. Anything you want.
8. Click the new drawing and find that same menu. Select "Assign Script".
9. Type RankCheck in the box and click OK.
10. Click on it and accept the script. You have to do this before you can use it.

Now, we move on to how to use the document for those that were not there. Enter your keyword. Give it time to populate. Once the article titles have updated (there are only 25 of 50 I have it set up to pull) then click your new shiny button. The answer is 25. Watch the rankings update. (Note: Don't pull rank too much. This is using a script to pull rank from Google for eHow and returning the ranking URL. It's using your IP and it could ban you from using Google for a day or so.)

Editing the Content Site

Want 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.

1. Find the content site's search page URL. http://ezinearticles.com/search/?q=pet+bunny
2. Go to the second sheet in the document "eHow Scraper" and unhide all hidden rows (2, 13, 24, 33, 42)
3. Cell A2 is currently =CONCATENATE("http://www.ehow.com/search.html?q=",$A$1,"") Change to =CONCATENATE("http://ezinearticles.com/search/?q=",$A$1,"")
4. Do the same in A13, A24, A33, A42.
5. 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.
6. Change the cells to =importxml(A2,"//h3") -- the A2 changes to be the cell above it.
7. 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.
8. 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.
9. 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.

Now go have fun. Play. Get some ideas and write. Remember what I said in my presentation ... go for the results where the content site ranks #1 and rewrite it better. This tool is not perfect and is not a guarantee to bring you tons of traffic. But it will inspire content ideas where known SEO and marketing people were targeting. They are still ranking because there is not any better content out there. Google is looking for a reason to rank more targeted sites. Make it your site. Be the resource that Google search engines and searchers want.