Find Out Your Average Organic CTR by Position

CTR by Rank: The Quick and Dirty Method

I call this the quick and dirty method because you only have to download one file from Webmaster Tools to make it work. As you'll see later, this is a huge time saver.  The drawback to this method is that you have to round the reported search engine positions for each query. And each of those positions is already an average of all the positions in which your site appeared for each search query. So, you're rounding averages of averages. Anyway, the first thing you need to do is log in to Webmaster Tools. Select the site for which you want to calculate CTR by position. Set your date range to include as much data as possible. At the time of this post, Webmaster Tools gives you the previous three months worth of data. Navigate to Traffic > Search Queries > Top Queries (tab). Click on "Download this table" to download the search query data including impressions, clicks, CTR and average position. Open the search query CSV in Excel. Delete all rows where clicks are <10. I also recommend deleting queries that contain your brand. Branded queries are sort of like "gimmies." Users who searched for your brand typically click through at a much higher rate and convert at a much higher rate because they likely would have visited your site and converted with or without the search engine. Non-branded queries, however, drive traffic and conversion that you probably wouldn't have gotten without the search engine. So, if your goal is to drive more and better traffic and conversions through search engine marketing, I recommend removing branded queries from your analysis. Next, add a column called "Avg. position (rounded)" and apply the following function to each row in that column. [text]=ROUND(H2,0)[/text]

Create a Pivot Table

Click on any cell inside your table of data in Excel. Go to the "Insert" table and insert a pivot table. From the PivotTable field list, select Impressions, Clicks and Avg. position. Move the "Avg. position" field to the "Row labels" area. Next, insert a calculated field. From the PivotTable Options tab, click "Fields, Items & Sets" and choose "Calculated field..." You'll get a menu like the one you see here. Name the field "Avg. CTR." Set the formula to: [text]= Clicks / Impressions[/text] Click "OK."

CTR by Rank: The Thorough Method

It would be nice if Google's Webmaster Tools API allowed us to download position data. Unfortunately, as far as I am aware, it doesn't. :( You could use the API to get the top search queries and then write a scraper that would log in to Webmaster Tools. Then for each search query, load the query detail URL with the search query appended. Finally, you could extract from each of those pages the position data. Or... Obviously, scraping Google Webmaster Tools violates their terms of use and could get you into a little trouble with Google. So, if you only need to do this once, you could just get the data manually. Click on each or your Top Queries and either download the "Position in search results" table or copy and paste the data into a spreadsheet. Put all your position data into one spreadsheet. Then follow the same process as above for creating your pivot table.