This tutorial shows how to perform the Customer Lifetime value (CLV) method in Excel using the XLSTAT statistical software. Show
Dataset and goal of this tutorial on Customer Lifetime Value (CLV)The dataset represents a sample of monthly subscription data from a phone service provider. The period covered is from 2010 to 2014. Three groups of users from a value-based segmentation are represented: Young, Classic and Premium. The goal of this tutorial is to calculate the CLV (customer lifetime value) of customers and also to estimate some KPI's such as the customers' retention rate or the time before churn. Set up the Customer Lifetime Value (CLV) dialog boxOnce XLSTAT is launched, select the Advanced Functions / Marketing Tools / CLV feature.  As the subscriptions are monthly, select monthly in the subscription period field. In the Options tab, we can take certain parameters such as the discount rate or fixed operating costs to be included in the CLV calculation. You can either choose to define the customers' retention rate if it is known or estimate it from the data by clicking on Estimate. The customers being charged at the beginning of the month, we choose in the Payment section, the option Start of period. Interpret the results of Customer Lifetime Value (CLV)The first result displayed is the average CLV per segment. The lowest average is for the Young segment. Further on we have the estimations of the time before customers defection and the related chart. Then the sensitivity analysis measures the impact of an increase in retention rate on CLV. The considered variations are increments of 5% from the estimated retention rate. The last table concern the CLV forecast. It contains a simulation on the average CLV value of customers remaining in the database over the 6-month period following the last recorded churn date.
Was this article useful? In this Part 2 article we’ll show you how to calculate customer lifetime value in Excel (sometimes abbreviated as CLV, LTV or CLTV). Knowing CLV is essential for database marketing and using the formula in Excel is a relatively cheap and easy way to know it. Calculate customer lifetime value formula in ExcelWe really, really want you to have more of this at the end of the year. Read on to learn more about how to calculate customer lifetime value (CLV) in Excel. Customer lifetime value defines how much money to invest to gain a new customer. When you use the customer lifetime value formula in Excel, you know what a customer is worth to your business. Our previous article (Part 1) briefly touched on lifetime value, as it served as an introduction to this article. This article will dig much deeper however, and give you a fuller understanding of lifetime value. CLV can really improve the effectiveness and return on investment (ROI) of your marketing budget. It helps determine how you should market to new and returning customers. We show three ways to calculate customer lifetime value using Microsoft Excel in this article, from easy to complex. Customer lifetime value definitionYou want more of this at the end of the year, right? Use customer lifetime value and you will!
Many marketers have heard of lifetime value, but may not have ever calculated it. We call it customer lifetime value (CLTV or CLV) or lifetime customer value (LCV). The concept of lifetime value started gaining popularity among database marketers in the 1980s and was defined in the 1988 book Database Marketing by Robert Shaw and Merlin Stone (go ahead and click, that’s not an affiliate link). It’s more important than ever today with mountains of big data available to online marketers. Net present value (NPV) of future profitsThe goal of determining lifetime value is to make sure your marketing program is profitable. It is used to improve sales, to maximize revenue per customer, and to focus your strategy to improve profitability.
It is well known that different customers are more valuable to a business than others because they spend more. Lifetime value takes these customers and projects how valuable they will be in the future. By determining how valuable they will be, you will know exactly how much effort and money you should put toward each customer. Lifetime value should be a key tool in any marketing campaign. Normally, customer lifetime value is broken up into yearly subsets, with each year used to calculate a new lifetime value for the following year. Some businesses, like a food or coffee chain, may use more frequent periods of time, like weeks or months. Since customers and marketing change over time, it is important to recalculate your customer lifetime value periodically. Customer lifetime value formula in Excel (simple version):Frequency x Time x Gross Margin Dollars = Lifetime Value Lifetime value is calculated by… 1. Determining the frequency of purchases; 2. Then determining the duration of time you expect a customer to be loyal to your business, or, this could also just be the amount of time you would like to estimate for; 3. The final calculation uses your gross margin dollars (net sales revenue – cost of goods sold). 4. Multiply all of three of these numbers together to get simple lifetime value. An alternative to this simple CLV formula is to use sales dollars instead of gross margin dollars. A more robust customer lifetime value calculation for Excel1. Start with your total revenue per year, the number of new customers and your total marketing costs. In the example below, this website has sales of $2.7 million and they spend about $10,000 per month on paid search which is $120,000 per year. This business had 10,000 new customers in Year 1, which means the acquisition cost for each new customer is $12.00 ($120,000 divided by 10,000 customers).
2). Add sales and customers for future years, a customer retention rate, orders per year and average order size to this spreadsheet. If you’re not sure about some of these numbers just put in your best guess. Below we have added the 10,000 new customers added in Year 1 with a retention rate of 25% which means in the second year, 25% of those customers of 2,500 will come back and buy. Since those are now repeat customers we will give them a higher retention rate of 70% in Year 2, which means 1,750 of them stick around to buy in Year 3.
In Years 2 and 3, we don’t have a marketing acquisition cost (you can argue with your accountant if you want to insert a cost here or not…accountants love to debate this kind of stuff). Calculate customer lifetime value in Excel: all the way in3. Finally let’s add gross margin, a discount rate (a combination of interest rate and business risk), which lets us calculate Net Present Value, cumulative NPV profit over three years, and finally customer lifetime value on the bottom line.
In this example, in Year 1 we acquired 10,000 new customers. Using a lifetime of three years, each of these customers are worth $142.62 to the bottom line. The importance of lifetime value in growing sales and profitsThere are a few extremely important things in this spreadsheet. First, you know the profitability of a customer during that customer’s lifetime. That means you know how much you can spend to acquire a customer and still make money. Second, customers who make repeat purchases are worth significantly more to your business. You should invest in getting customers to make a second purchase, third purchase, and so on. After all, you already invested the money to acquire this customer. Reach the right customers using CLVOne rule that is very important to understand, and applies to most businesses is the 80/20 rule. This rule states that 80% of your business comes from 20% of your customers. This stat is crucial to understand. If 80% of your business is coming from only 20% of your customers you need to make sure that you are focusing on the right customers. These customers tend to be ones that come back for repeat purchases and have a long lasting relationship with your business. Once you determine the most lucrative customers, contact them as much as your budget allows increasing profitability of your customers. Finding out the lifetime value of this 20% will be most beneficial to your business so you can plan the amount of money you would like to spend to market toward them. Customer lifetime value conclusionHopefully now you understand lifetime value, so start an Excel spreadsheet and calculate it for your customers. Figure out who is going to be the most valuable customer in the future to optimize your marketing campaigns. Remember that the top percentiles of your sales is what drives your business, so focusing your efforts on them will help drive you business. Understanding lifetime value can be the key to all of your future marketing campaigns. For more on customer retention, see our previous article. |
Customer lifetime value formula Excel
Postagens relacionadas
direito autoral © 2024 pauex Inc.
