1-855-552-6464Comments Off on Introduction to Using Excel to Calculate Your Website Visitor Value Metrics
Introduction to Using Excel to Calculate Your Website Visitor Value Metrics
Posted by nitzan on Thursday, July 4th, 2013
Do you know how much each visitor to your website is worth? If you buy advertising, do you know how much money you can spend and still break even? Do you know the long term value of your visitors?
We all want as many visitors as possible to our websites, it almost seems like crazy talk to imply otherwise, but in actual fact for a business website, some visitors are more valuable than others, and some visitors could be even costing you money.
Knowing your average visitor value is therefore essential, and breaking visitor value down further could be a very profitable thing to do.
Even more important is if you are paying for this traffic. You want every spend on your web marketing to work as hard as possible, both to know how much you can invest, and also to trim the fat.
There are custom and commercial tools out there, but in fact, you can do a lot with plain old Microsoft Excel!
Using Excel for Basic Visitor Value
First you need to know how many visitors you get each month, and how much money your website makes.
Do you sell products or services? Maybe you show advertising?
Take your monthly visitors and sales total and plug these numbers into the spreadsheet. In the final column the revenue is divided by the visitors to give your visitor value.
So in this example, 30,000 visitors bringing in a revenue of $10,000 provides an average visitor value of $0.33.
How is this useful? Well, with this in mind, if you are currently paying $0.25 per click in Google Adwords then you can up it and still break even.
Drilling Down Visitor Value
This is not really fair though. As I say in the introduction, not every traffic source is created equal.
Say, for example, you notify your audience about a product in three ways:
- Blog Feed
In the example shown, Email generates the most revenue, with feed coming second, and Twitter looking pathetic at only $20, BUT, look at the per-visitor value!
This is because the Twitter message converted at a much higher rate, that is a higher percentage of Twitter visitors bought. We will look at conversions in a moment. For now just know the message and channel can have a profound impact on visitor value, even a negative effect, as well as the total revenue where email clearly wins in this case.
Responsives Versus Subscribers
The obvious conclusion you would draw from the above example is “wow, I had better grow my Twitter followers”, but WAIT!
Yes, there is an indication that those Twitter clickers are worth $6.67 on average, but it does not tell you how much your Twitter followers are worth! At this point you just know how much your responsive followers are worth from one test.
To know how responsive your various fans are, we can check the Click Through Rate, or CTR.
CTR is calculated by setting the cell format as percentage using the % button, then taking the visitors and dividing it by the total messages sent (if you have 300 followers then one tweet is sent to 300 people, but if you send it twice then the number of messages sent doubles).
In my case here the formula is =(C7/B7) (where / means divide).
These results can give you additional insights. You can see Feed subscribers are incredibly responsive – a full half clicked through, were as email and twitter followers were not in the same league.
You can not take the results from just one sample too seriously. You need to measure repeatedly for a start. Secondly, if you have 10 followers in total and 3 clicked through, then your click and conversion rates seem high, but we could be dealing with too small numbers to know if your results are statistically significant.
Statistical relevance is too much math for my meager brain, but there are spreadsheets available that will help you do the calculations.
What you do know is that if you can get more of your followers, subscribers or readers to respond, then you will increase their value to you, and while some sources are less responsive than others, their propensity to buy can be very different … that is where conversion rates come in.
Measuring Conversions
Using a tool such as Google Analytics you can automatically measure conversions using “Goals”, but you can do some broad calculations using Excel of course.
Taking the example given above, we can add another couple of columns to reveal a better picture of what went on with that promotion.
Conversions are calculated much like CTR, but CR is percentage of visitors who buy, therefore Sales divided by Visitors presented as percentage format.
So in our example you can see that while this particular sites Feed readers are highly responsive, they are clickers not buyers, whereas this site owner has a small amount of raving fans who were primed to buy following on Twitter.
Why might this be? Well, Twitter is often used for conversation. There might have been many messages leading up to the offer being made preparing those who were interested that something special was going on sale, or perhaps an existing product heavily discounted. The Twitter and email recipients might have been hovering over their keyboard ready, while the blog readers while still interested left it too late and clicked over after the offer had gone away!
Going for Gold
So now we know that not all visitors are equal, but what about customers? Earlier I said that some visitors or customers could in fact be costing you money, how do we know? And what about the long term value?
In the next part of this series I will show you how to split your customers into groups for increased profitability and so you know who to lavish your super special customer service on! We will later also work out who are the best customers long term, and who you might want to stop buying from you.
Subscribe so you don’t miss anything!
