How Excel Can Help You Measure Value and Engagement

Knowing how much the value of each customer to your site is worth is crucial, especially if you buy advertising and need to track whether or not you are making a profit as a result of your marketing spend. Knowing how much money you can dedicate to advertising whilst still being able to at least break even is important, and it’s more and more vital that you are aware of the long term value of your customers.

Every website owner wants as many visitors as possible to their website, however some website and business owners fail to realize that some visitors are more valuable than others, whilst some visitors could even be costing you money – if you pay for advertising, those visitors who click on an ad but don’t spend much time on the page may be increasing your traffic levels, but you’re actually losing money because of them. Therefore, knowing the value of your average visitor is essential, and breaking down visitor value further is often a valuable thing to do.

If you’re paying for traffic this becomes even more necessary, as you’ll want to know how much you can invest in marketing and make every spend on your web advertising to work as hard as possible. There are a number of tools out there that you can use for this purpose, but you don’t need to spend loads on them – in fact, there’s a lot you can do with basic Microsoft Excel. Here’s how.

Working Out Basic Visitor Value

Before you can work out your basic visitor value, you’ll need to have some figures to hand. You will need to know how many visitors come to your site each month, and how much money your website makes. How do you make money from your website? Do you sell products and/or services, or does your revenue come from showing advertising? You will need to take your monthly visitors and sales figures, and enter them into the spreadsheet. In the final column, divide the revenue amount by the number of visitors in order to give you a figure for visitor value. For example, a site with 30,000 visitors that makes $10,000 per month has an average visitor value of $0.33. How does this help website owners? It helps you to make decisions on your advertising spend – for example if your visitor value is $0.33 and you currently spend $0.25 per click in AdWords, you can afford to spend more and still break even.

Drill down Visitor Value

Although working out visitor value is useful, leaving it as it is isn’t fair, as not every source of website traffic is equal. For example, you could notify your audience about a new product on your website in a number of ways, including a blog feed, social media such as Twitter, and an email newsletter. In this example, let’s say that email comes first and brings in the most revenue, the blog feed second, and your Twitter account last, only making up for $20 of your revenue in total. However, this doesn’t mean that Twitter cannot have a large visitor value – the Twitter message can convert at a much higher rate, meaning that there is a higher percentage of Twitter visitors bought. If you record your figures in PDF format, you may want to consider using Cogniview’s powerful PDF to Excel Converter in order to easily input data into your spreadsheet when making calculations.

Responsives Vs. Subscribers

If your website is similar to the example given earlier, the obvious response that any website owner would have is that they should focus on growing their Twitter followers. However, simply because there is an indication that Twitter followers are more profitable on average does not mean that you know exactly how much each Twitter follower is worth – this is simply the value of a responsible Twitter follower. In order to understand how responsive your various followers and customers are, you will need to work out the Click Through Rate, or CTR.

CTR can be calculated by setting the cell format on your spreadsheet to a percentage by using the % button. Then, take the amount of visitors that you have, and divide it by the total of messages sent – for example, if you have 400 followers on Twitter, one tweet is sent to 400 people, but if the tweet is sent twice, the number of messages sent will be doubled. The results from this can provide you with a number of additional insights, for example you can see that your blog feed subscribers may be incredibly responsive when compared to email and social media followers. However, it’s important not to take results from one sample too seriously, and you will need to measure repeatedly. However, what you do know is that if you can get more of your readers, followers or subscribers to respond, they will ultimately become more valuable to you.

Engaging With Users through Microsoft Docs

During the beginning of the web, you could only display standard .html or .php files to your users. Today, you can upload Microsoft Word, PowerPoint and Excel. Microsoft Excel documents can be especially engaging, since you can share a lot of data that would be difficult to format otherwise. You can even allow users to filter and display it to their liking.

There are many interactive features and automation in Excel that can help you get work done faster and save time.

Make sure that you run split-testing experiments to see how engaged your users are with Excel and Word documents.

Measuring Conversions

Although you can use tools such as Google Analytics for automatically measuring conversions using the ‘Goals’ feature, it’s also possible to make some broad calculations using Microsoft Excel. Conversion rate is calculated in a similar way to CTR, however conversion rate is a percentage of visitors who buy, therefore to work this out you should divide sales by visitors, and present in a percentage format. For example, although you may have a number of

responsive followers on your blog feed, working out the conversion rate might reveal that they are clickers rather than buyers, and many do not spend any money when they visit your site. However, working out the conversion rate for your Twitter followers who had a lower CTR than blog feed followers may reveal that although you have a smaller amount of Twitter followers, they are more likely to buy when they visit your site.

Do you use Excel to discover how much your site visitors are worth? What have you learned from the process, and have you made any changes due to what you discovered? We’d love to hear from you in the comments.

Sean is a freelance writer and big data expert. He loves to write on big data, analytics and predictive analytics.