One of the biggest errors I see (consistently) in SEO and PPC analysis is using Excel’s AVERAGE function on Click Through Rate (CTR). As I mentioned in my SEO Pivot Tables post, do not do this. Here’s why averaging CTR is dangerous.
Take the following set of 10 data points.
If you SUM all of the Impressions and Clicks and then do the CTR calculation you arrive at 10.05%. If you AVERAGE the 10 CTR percentages you arrive at 6.14%.
If I change the Clicks for these 10 data points I can produce the opposite effect.
And will you look at that, the average CTR is the same in both instances. Can you see how misleading average can be here?
Don’t Average Click Through Rate
For years, I’ve used a structured Excel quiz in my hiring process that tests just this issue. In my experience upwards of 50% of applicants fail the quiz. If you’re pulling down data into Excel for PPC or SEO, make sure you don’t fall into this trap.
The Next Post: How To Get 100 Likes From 2 People
The Previous Post: Facebook Like Number Bookmarklets
Comments About Don’t Average CTR
// 2 comments so far.
Rob Diana // November 08th 2010
Ah yes, the difference between a basic average and a weighted average! It is amazing how many people miss the value of the difference.
Jeremy Post // November 08th 2010
Definitely important when calculating Average Position as well. For CPC reports, remember to multiply total impressions by average position for each line in your report (keyword, adgroup, campaign, etc).
Sum this column and divide by the sum or all impressions. This number is your true Average Position.
You can follow any responses to this entry via its RSS comments feed. You may also leave a trackback by clicking this link.