Note: I wrote this article originally in 2011 when I was just starting out with blogging. It has been rewritten for simplification purposes and to keep things up to date. Let’s start off by saying that there are many tools that can calculate these formulas that have already been created. SEO’s use Microsoft Excel because it is quick and easy, and integrates very well with a lot of other programs. A lot of people in the industry like to export their data into Excel for further crunching, so this formula can be very useful when working with other types of numbers.. Anyhow, this is somewhat of an intermediate topic but we will keep it really simple. We assume that you know what CTR, CPC, and what a conversion means. We also assume that you have a very basic understanding of Microsoft Excel before using this. I’m going to go over 2 basic Excel calculations here today that will help you get more bang for your buck with your data.
In SEO, click-through rate (CTR) refers to the % of clicks you receive divided by the number of impressions you receive in the search engines. This can only be calculated by taking the number of impressions in webmaster tools and dividing by the number of clicks. Number of clicks can be taken from your analytics or via Google Webmaster Tools however be warned Google Webmaster Tools click data isn’t always accurate.
In internet marketing, affiliate marketing or internet advertising click-through rate (CTR) is calculated differently than the way SEO click through rate is. In affiliate marketing / advertising you take the amount of clicks you received and divide them by the number of impressions you have. Pretty simple.
CTR is calculated the same way for SEO or affiliate marketing. No matter what the case is, the formula is this:
CTR or Click Through Rate in organic SEO = Clicks / Impressions
Just remember to always use the same date ranges for clicks and impressions. Now let’s take a look at how to calculate this in Microsoft Excel. Most webmasters keep track of their statistics in Excel to track a number of different metrics. It can be very time consuming to calculate these formulas over and over again, and you also don’t want to introduce human error into the equation. Here is how you would calculate CTR in Microsoft Excel: Identify which cell you want to display the Click Through Rate. Within that cell, type:
"=CELL_WITH_CLICKS/CELL_WITH_VISITORS" or in the case of the example below it would be: =A2/B2
As you can see, this is pretty straightforward. You are basically dividing one cell into another, and displaying the output into another one. Give it a try with a blank Excel Worksheet if you want. Let me know if you have any questions.
Next is one that is a little bit more complicated concept yet easy formula, and that is calculating conversion rate. When calculating the conversion rate you should have the following information available:
Now sales or conversions can be any number of metrics such as:
The formula for calculating conversion rate is as follows:
number of conversions / traffic * 100
So lets say I have 350 signups If you’re looking in Excel, your data will look something like this: =B1/B2*100 or you can ditch the multiplying by 100 and just change the cell format to a “percentage” type. Using this formula, Excel will automatically calculate the conversion rate.
If you have any questions about this or would like more information on analytics, metrics, tracking, or Excel, feel free to drop us a line.