Simple customer lifetime value analysis are simple calculations for small business owners.
Customer Lifetime Value is an estimate of measure of the total worth to a business of its relationship with a particular customer.
The customer lifetime value analysis is a useful tool for businesses that sell goods or services.
It helps in estimating future revenue each customer can bring to your business.
By analysing customers purchasing history, you can use it to analyse and determine your long term customer strategies.
In this post, we’ll look at benefits of customer lifetime value analysis and how to make simple customer lifetime value calculations.
Finally, how to use a custom function on Google Sheet to calculate customer lifetime value.
When the customer lifetime value analysis is done properly across the database, it can help businesses in the following ways:

To improve market segmentation

Customer lifetime value can help you to improve market segmentation for your business.
For example, when you want to plan a strategy that treats the market as a collection of customers.
The customers are grouped together into segments according to their similar needs, gender, age, lifestyles etc.
This helps to design products or services and marketing programs to appeal to the greatest numbers of customers possible.

For Resource allocation

It is not realistic to target a whole market. Therefore, a business is able to manage its resources adequately by selecting the most attractive parts of the market to focus on.
In addition, customer lifetime value analysis can guide your business in determining how much you are willing to spend to acquire new customers.

To improve profits

Customer lifetime value is a tool that helps businesses in developing strategies for acquiring and retaining the most profitable customers.
In addition, customer lifetime value can help businesses explore the future behaviour of their database.
Therefore, businesses can reduce cost by concentrating resources on gaining and retaining customers who will be  profitable.
In addition to having positive impact on financial management, customer lifetime analysis is also useful to determining market value.

To customize marketing communications

Customer lifetime value also helps as a decision-making tool that aids in choosing the right marketing media.
It is particularly useful for answering questions such as; which media should we use for our marketing activity?
On what basis should we select potential customers for target?
It is also useful in optimizing the timing of offers and also beneficial for evaluating business competitors

Simple customer lifetime value analysis

Lets say you want to calculate the lifetime value of a customer.
You’ll be interested in adding up the discounted yearly future contributions of that customer.
For example, in your database, you have a customer that buys a particular product from you.
You can use his purchase history to calculate how much revenue this customer or any customer with a similar profile, can potentially offer your business in the future.
  • Total income = Amount spent by customer yearly buying the product.
  • Also, Total cost = Estimated customer service costs e.g. aggregate sales and marketing costs for the customer.
  • Yearly contribution = Yearly contribution (Total income – Total costs).
  • Discounted value = Discounted rate (usually between 8% -15%) multiplied by yearly contribution.
  • Customer lifetime value = total discounted value.

Simple customer lifetime value analysis using Google sheets

Customer lifetime value calculations for small business owners can be easily done using Google Sheets.
The information you need in order to calculate customer lifetime value are:
Customer ID –  the customer ID helps to track all purchases by each customer.
You can also use the customer unique ID to group each customers transactions.
You’ll typically get that from your invoicing tool.
Lifetime value calculation involves:
  • Average Order Size
  • Lifetime value calculation also involves Average Order Frequency
  • Average Customer Value
  • Average Customer Lifespan
  • Customer Lifetime Value

How to use a custom function on Google Sheet to calculate customer lifetime value

Customer lifetime value analysis on Google Sheet makes use of a custom function.
The custom function in Google Sheets helps you to make many calculations all at once.
Lets say you want to calculate customer lifetime value of more than one customer and many products.
You’ll need to apply a series of formulas in many columns, which can be time consuming.
For the customer lifetime value calculation on Google Sheets, you need three sets of information namely:
  • Customer ID
  • Date
  • Amount
This video explains what you need to do and how you can get a copy of the template.


Although it can be challenging if you are trying to attribute costs to a particular customer account or a customer segment.
However, the customer lifetime value analysis is a prediction model that can have different levels of sophistication and accuracy.
Therefore,  for a small business, it is be used as an estimate to aid an intuitive process.

Leave a Reply

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