How to calculate a customer's lifetime value in Excel

Transcript: I've got a quick question for you what does david ogilvy the father of advertising and founder of ovals emaza what does he have in common with an Ag cooker a cooking stove just like the one you might see right now the truth of the matter is david ogor be the legendary father of advertising started his working life as an apprentice chef and because he was an apprentice chef he understood the buying decision of chefs and that made him a really successful salesman of the AGA cooker he even wrote a book called the theory and practice of selling an AGA cooker so that was his first real success but what's the moral to the story the monitor story is that that understanding of the buying decision is key to your marketing campaign plan and how effective and how valuable that return on investment is so click on this link here if you want to learn about a framework to focus your marketing campaign plan to get the most out of your marketing dollar however if you stay tuned what I'm going to do now is I'm going to give you a excel workbook and an example... See more →

Transcript: What's up y'all, this is Scott Davis and mygoal today is to make calculating customer lifetimevalue accessible and maybe even enjoyable for you. I'll refer to customer lifetime value by itsabbreviation CLV throughout this video. Let's dig in. First, why is CLV important? CLV gets at the heart of marketing's objectiveto attract and keep profitable customers. Youneed to understand CLV to put together effective marketing plans and make strategic decisions. How much money are you going to allocate toattract new customers and generate leads? Attracting new customers might cost 3 or 4times as much as retaining existing ones. CLV willhelp you understand what kind of return to expect on today's investments in customeracquisition and retention. How are you going to stop customers from defecting? What aspects of customer attrition arecontrollable? An average company loses 10 percent of itscustomers each year. A better understanding of CLV will help youmanage these dynamics. Think of managing customers as managing aleaky bucket. Your customers are represented by water andthe water already inside the bucket is your customer base. New customers added to the bucket increaseyour customer base. Ah, But there's also a hole near the bottomof the bucket and the water leaking out represents... See more →

Key quotes:

• "Customer lifetime value (CLV) — or customer profitability, customer profitability analysis, or simply “figuring out who makes you the most money” — is an essential metric that can help optimize your business so that it makes you the most possible money while wasting the least possible time and resources."
• "Customer acquisition costs and customer lifetime values are not just pieces of information that are only relevant in retrospect; there are steps that can be taken to make better prospective decisions and help determine who your best customers are and will be."
• "You need to determine the following for each of your current customers: Once you have determined those variables, a general formula to calculate customer lifetime value (CLV) is: Imagine you own an e-commerce company that launched ~3 years ago, you’ve had a number of sales but you’re not sure how the underlying metrics look, so you want to determine your customer lifetime value."

Key quotes:

• "Let’s unwrap the customer lifetime value (CLV) metric and make the most of it for your business."
• "Customer lifetime value (CLV, or LTV for “lifetime value”) helps you predict future revenue and measure long-term business success."
• "To figure out how much you should invest, you need to calculate the customer lifetime value for your business."
• "Based on this customer lifetime value metric, Amazon started focusing on Prime users and increased the company’s profit over the past four years."
• "There are two methods of calculating CLV using the historical approach: by determining the average revenue per user (ARPU) and using cohort analysis."
• "In addition to calculating CLV, cohort analysis can help you find the number of loyal clients, improve lifetime value by finding the points where purchasing drops off, and accurately assess ad campaign performance."

Key quotes:

• "For owners and managers of businesses that provide and sell services to clients — charging for your time and expertise rather than exclusively selling tangible products — one of the greatest challenges is identifying the most profitable projects and types of customers, understanding the real costs, and putting that knowledge to use when determining how to target and sell to potential customers."
• "Many small businesses sell a variety of different types of services — from consulting to product development — and each method produces different revenue streams which each have a different cost structure and unique indicators (e.g."
• "a standard markup on a physical product where margin matters, or a flat fee or hourly rate for work on a project where time spent is a critical factor)."

Key quotes:

• "Cohort analysis and customer LTV will change the way you view the value of your user/customer base, and will provide measurable data to help determine what kind of acquisition/retention strategies, branding/messaging and customer outreach you need to move forward."
• "The current data is the basis for a cohort analysis, but in order to do it you need to first use it to calculate new information, such as cohort, number of active months and customer LTV."
• "Use your expanded data set to group your individual customer data into cohorts, and from there you can start building charts to visualize your data and aid in your analysis."
• "Right click on the cell selection, go to Format Cells, go to the Number tab and select Percentage.] [Note: Before attempting this function understand how to lock and unlock formulas in Excel, so you can control how they change when copied to different areas."
• "The Final Product is a visualized analysis of customer churn for each cohort, which will significantly aid in understanding which retention strategies were not effective and which should be replicated in the future."

Key quotes:

• "Subscription is a powerful business model because it creates an environment where the default customer behavior is retention, as opposed to one where the default behavior is churn."
• "The pillar of any subscription business is Lifetime Value (LTV), or the total profit you can expect to receive from a new customer over the course of their lifetime."
• "When we were scaling our subscription business, I found that there were surprisingly few publicly available resources that explained in a detailed way how to calculate LTV."
• "So, I built a simple spreadsheet that you can use to calculate and project Lifetime Value."
• "To use the model effectively, it’s helpful to understand the inputs of LTV: Retention curves generally reach an asymptote over time (meaning the curve will flatten out)."
• "Revenue and Margin give you expected profit for a customer that is retained, while the Cohort Retention Curve gives you the probability that the customer will be retained in each month."

Transcript: Welcome to how to use the customer lifetime value calculator as found on this site CL the dash calculator calm as you can see at the top here it says follow the four steps complete the gold sells only so step one step two step three etc so step one how much did the company spend on total customer acquisition so you simply type in our wall we spent a million dollars last year and how many new customers did we get about it there was a thousand new customers that works out the blue cell numbers of customers per acquisition spend so let's call the average customer acquisition cost a little note here if you have already worked out what that is say you worked out that it's $500 per new customer you just put $500 here and set that to one customer and it works it out for you step two is to have your discount rate I would suggest ten or fifteen percent is appropriate and as you change it the numbers further down will cap calculate if you don't want a discount rate set it to zero okay but we'll leave this at ten percent step... See more →