How to calculate lifetime customer value (LCV) from ecommerce transaction data

As an ecommerce business owner, understanding the value that each of your customers brings to your business over their lifetime is critical to making strategic decisions about marketing, sales, and customer service. This is where the concept of Customer Lifetime Value (CLV) or Lifetime Customer Value (LCV) comes into play.

What is Customer Lifetime Value?

Customer Lifetime Value is the total worth of a customer to your business over the course of their relationship with your brand, and it can be a powerful metric for understanding your business’s revenue potential and identifying areas for improvement.

One of the most effective ways to calculate Customer Lifetime Value is by analysing your ecommerce transaction data. By looking at historical purchase data, you can estimate the average amount of revenue that a customer will generate over their entire lifetime with your business.

In this blog, we’ll walk you through the steps for calculating Customer Lifetime Value using ecommerce transaction data, including how to identify key metrics, analyse data, and interpret the results. With these insights, you can make more informed decisions about how to allocate your resources and prioritise your efforts for maximum impact.

It’s important to remember that if new customers are happy with a product or service, then a percentage will turn into repeat customers and continue to generate predictable profits for years to come. This is why McDonald’s and Starbucks, for example, can afford to spend thousands of pounds in advertising just to acquire a single new customer.

Extracting transactional ecommerce data for CLV

Within your ecommerce CRM lies valuable data pointing towards your current customer’s lifetime values.

To extract CLV insights from your customer transaction lists, you’ll need to export these necessary data fields:

• Customer email address – this will be used as a unique ID for each customer.
• Transaction amount – the revenue generated from each transaction.
• Transaction date – the exact day the transaction was made.

An export of all transactions using these fields will allow you to identify new customers and then identify repeat transactions further down the line.

Each transaction will be a single row in the export, so you may want to include the transaction ID alongside the data for verification:

Picking the ideal date window to measure CLV

The only way we can 100% measure a customer’s lifetime value is by waiting until the customer, quite literally, dies!

As this could be a long, long time in the future, we’ll have to estimate the CLV by picking a sensible date window in which recent new customers are excluded from the data.

A recently acquired customer may not have had time to make any repeat transactions yet, so by including them in the data, you’ll be underestimating the CLV.

We recommend excluding any new customer data from the past year or so to avoid this issue, whilst getting in as much data as possible:

Pivoting the data to calculate CLV

Now that we have the transactional data for all customers, excluding any new customers from the previous year, we can pivot the data to calculate the Customer Lifetime Value.

We need to sum up all the transaction amounts and divide this number by the number of unique customer email addresses to calculate the CLV:

Remember that a valid transaction is one that originated from a customer that made their first transaction over a year ago, which may have been made at any time.

To gain insights on how far to push advertising for new customers, you’ll need to compare the CLV with the original transaction value, which is simply the average of all first transactions:

If the original transaction value was $100 and the CLV was $600 for example, then we could in theory spend an extra $500 in acquiring new customers to break even depending on the return of investment from advertising:

Advanced CLV insights

You can gain more CLV insights by adding more data from your e-commerce CRM, or by analysing the dates from transactions.

By analysing dates you can determine:

• How customer lifetime value changes over 1 week 2 weeks, 3 weeks, etc., up to the maximum date window – see below for a real example.
• When customers are most likely to make a repeat purchase – this can help optimise automated emails or re-marketing campaigns.
• When customers tend to stop making repeat purchases – this can help you rescue customers that are about to leave with offers or incentives.

You can gain extra insights by adding extra fields alongside each transaction, such as:

• Business/consumer customer type – to determine CLV for different types of customers who behave very differently.
• Product/service type – to see which products or services lead to new customers, and which add to subsequent sales.
• Demographics – to see the difference between different genders, ages, or locations for example.

Final thoughts