Calculate Organic CTR (Click Through Rate) and Conversion Rate

excel conversion rate CTR click through rate
This article will outline exactly how to calculate the Click Through Rate or CTR of a website using Microsoft Excel or using a simple calculator.

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.

CTR can mean different things in SEO and PPC

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.

wm

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.

Calculating Click Through Rate or CTR in Microsoft Excel

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
ctr ctr1

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.

Calculating Conversion Rate in Excel

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.

formula calculate conversion rate

Conclusion

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.

Patrick Coombe
Patrick Coombe is the founder and CEO of Elite Strategies Llc. Patrick takes a hands on approach to managing Elite Strategies and loves to get involved with technical projects relating to clients inbound marketing needs.
Patrick Coombe
Patrick Coombe
  0 COMMENTS
Analytics Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Info

900 Linton Blvd, Suite 104
Delray Beach, FL 33444

Phone: 561-526-8457
Toll Free: 855-353-8730
E-mail: info@elite-strategies.com
Fax: 561-526-8707

Free Manual SEO Report Our in-house SEO's will audit your website, and send you a report.
Kindly send us some information about your company, and we'll get back to you in about 24 hours.