A few posts back I was talking about the high bounce rate I was seeing on my photoblog, and that one of my ideas on lowering the bounce rate was to increase the “quality” of the traffic I was receiving from the search engines (mostly, Google).
In that post, I mentioned my “trusty spreadsheet” and wanted to talk a little bit more about it. I’m making it available for download (Excel 2007) so grab a copy of your own and read on to see how I use it.
Column B (Keyphrases) – This is the column where I put in the keywords that I am targetting on my site. One thing to keep in mind while building this list is that most people search for phrases not individual words so try to target keyphrases of 2-3 words in length
Column C (Estimaed Traffic) – In this column enter in the number of searches that you get from the Google Keyword tool. I use the value form the “Approx Avg Search Volume” column, which represents the average monthly search traffic over the last 12 months.
Column D (Number of Searches) – In this column I enter the number of visitors my website received that the keyphrase we’re looking at. If you’re just starting out then this should be zero. If you don’t have an analytics package I would recommend getting Google Analytics up and running. It’s free and super easy to use.
Column E (Percentage of Search) – This is a caculated column so there’s nothing to enter here. This columns calculates the percentage of search that your site is receiving from a particular keyphrase.
Column F (Popularity) – Goto Google and do a search for the keyphrase that you entered in Column B. Enter the total number of pages that Google returned here.
Column G (Competition) – Back to Google again, and this time all the search modifier allinachor: before your search term. Enter the total number of pages returned in this column.
NOTE: Column F & G were taken from a great book about search engine optimization called “Get to the top of Google” by David Viney. So if you want to know more about these columns, and what they mean I would suggest picking up a copy of David’s book.
Column H (KEI) – This is a calculated column so there’s nothing to enter here. See the NOTE about David Viney’s book.
Column I (KOI) – This is a calculated column so there’s nothing to enter here. See the NOTE about David Viney’s book.
Column J,K,L – In these columns I enter where my website is currently ranking for each of the keyphrases I have entered into column B. This task is made much easier by using a great Firefox extension called Rank Checker.
Once I have entered all that information I sort by column I (KOI) and look at the pages that show on the first page from Google. From here, I start building my landing pages.
Couple of things to keep in mind,
- There is space for 30 keyphrases, but I would suggest that you do some brainstorming and come up with many, many more. You’re going to pick some real duds I promise you that.
- I am going to concentrate on my top 10 keyphrases as ranked by the calculated KOI value, otherwise I think that my pages will start to look like they were created for the search engines rather than my visitors – not good!
Keep in mind, that this is all still a work in progress so I have no hard numbers on how this is helping (or hurting) my photoblog, but I will keep you all informed.
5 responses so far ↓
Thanks for the tips. I have really been trying to work on getting my site noticed lately.
Great info, thanks for posting!
Some really interesting stuff there thanks. As a new blog on photography, it’s interesting to see how to develop the site to improve the SEO based on something concrete and measurable. A worthy read!
Thank you very much for this. This how I do keyword research too. May I ask how do you evaluate the data from Google. For example do you use any other keyword research tools such as wordtracker or google insights.
@Stefano: I typically stick with Google, and it’s keyword tool, although I am finding more and more uses for Insights as well. I got started after picking up a book by David Viney titled “Get to the top of Google” it’s a great resource and had all the math required for the spreadsheet.
Good luck!