Google Docs and Spreadsheets is an interesting service that Google has been offering for some time now. It’s a combination of purchased and in-house technology to provide a document editor similar to Microsoft Word and a spreadsheet program similar to Microsoft Excel, free, online, and with some unique web-based functionality, especially the ability for multiple editors to be working on one document at the same time. I can create a spreadsheet and share it with you, and we can simultaneously edit it and see each other’s changes – which is pretty great.

They aren’t quite as fully functional as a non-web based solution, and can be kind of laggy and have server issues, but they are still useful tools. At work, we use Google Spreadsheets to help keep track of jobs and information, since it’s something everybody can edit, and edit simultaneously as necessary. You can even publish a document or spreadsheet to an RSS feed so you can stay fully on top of any edits.

There’s a couple of instigating factors for this post. First, Google has announced that they have acquired Tonic Systems, who have created online presentation software – so in addition to documents and spreadsheets, Google will now have presentations. This is pretty cool, and I’m looking forward to exactly what they come up with for the product when they release it.

The other news item – Google Spreadsheets now includes charts! This is a really awesome new feature that moves Google Spreadsheets a LOT further down the path toward becoming a really useful tool, and not just a novelty.Here’s a spreadsheet with chart that I made and published with Google Spreadsheets:

It’s published to this URL:

http://spreadsheets.google.com/pub?key=pJIDC70_NJCpUz1u7xqoBPQ

Pretty nifty stuff.

Unlike pay-per-click (PPC) advertising, obtaining a click-through rate (CTR) for organic search engine listings can be a difficult task, if not completely impossible. An organic CTR refers to the percentage of people who clicked on a search engine listing for your website as a result of submitting a query to a search engine. CTR is a basic SEM metric computed by dividing the number of user clicks by the number of ad impressions – the number of times your listing is displayed.

As you can imagine, garnering accurate impression data for a natural search engine listings is extremely difficult because none of the major search engines publish how many times a listing for your website is actually served. Yahoo does allow webmasters to get a look at the next best thing – an estimation of how many times its users searched for a keyword phrase during the previous months* (see note). I will explain how to use this to approximate your CTR in just a moment, but first a note about the Yahoo Keyword Selector Tool.

The Yahoo Keyword Selection Tool has been going through some hard times lately. *Yahoo stopped updating impressions for keywords circa March 2007, so any numbers you get are several months stale and impressions estimations for seasonal terms (such as “Christmas shopping”) aren’t dependable at all. Also, the Keyword Tool is prone to unannounced outages and slow response times due to high trafficking. Another downside is that you have to enter each term individually into the tool in order to get frequencies. Despite the fickle personality of the gadget, it remains one of the best means of acquiring keyword impression data.

Estimating Natural CTR

Now you’re ready to estimate your organic CTR. It helps to do this using a spreadsheet application like Excel. Here are the steps:

1. Examine the metrics for your natural search engine web traffic only. Find your “referring keywords” and the number of visits attributed to each term for the previous month. These are the terms your site visitors entered into the search engines to find your website. Most web metric systems allow the user to export the data to a spreadsheet.
2. Look-up and record the search frequency for each keyword using the Keyword Tool. Now here comes a bit of arithmetic. Because the impression data from the Keyword Tool reports the number of searches by Yahoo users exclusively, you must factor Yahoo’s market share into the the equation. Depending on who you ask, Yahoo’s market share for searches bounces between 25% and 30% for any given month. For these calculations, I will use 28% as Yahoo’s share of total Internet searches. Using this ratio, we can scale up the tally of Yahoo searches to interpolate total Internet searches across all search engines by dividing the estimated search frequencies from the Keyword Tool for each term by .28. This gives us estimated impression levels for each referring keywords
3. Finally, take the number of referrals for each keyword (clicks) from your web traffic data and divide by estimated impressions. Click on the thumbnail to view an example of calculating CTR for organic listings.

Occasionally, some oddities may arise from using this technique. In some cases the number of clicks may be greater than the number of impressions, or the amount of impressions may simply be zero or not applicable according to the Keyword Tool. But it’s still a good exercise to see if your search engine listing are attracting clicks. There are other variations to this method – for instances you can calculate organic CTR for Google listings only.