Customer Lifetime Value
Historical CLV Calculation

Customer Lifetime Value: Historical CLV Calculation

Customer Liftime Value Caolculation BLog Post

Facebooktwittergoogle_plusredditpinterestlinkedinmail

In the last article, Customer Lifetime Value: A Beginner’s Overview, two key forms of Customer Lifetime Value (“CLV”) were covered: Historical and Predicted. This article focuses on how to calculate and use Historical CLV analysis for a single customer.

From the previous article, we know Historical CLV looks at a customer’s entire transaction history and shows the total value of that customer at some point in time. From this definition, we select three important items needed for the calculation: entire transaction history, total value, and point in time. Let’s start with transaction history.

Obtain transaction history data from where your customer and their corresponding order data is stored. Most businesses use modern day Customer Relationship Management (“CRM”) systems, which assign unique customer ID’s to individual customers and group the child orders those customers produce. An example of a business using a CRM is an online retailer who makes customers register online before they are able to make an online purchase. By registering online, the customer now has a unique username (a type of ID), which allows the online retailer to combine all of the orders this customer places. Given this data structure, it’s simple to track all orders relating to the transaction history for this unique customer.

Let’s take customer 01. This customer is known to shop with the business on a contractual basis, purchasing a set amount of products over a pre-defined frequency. An export of all of his order records shows the following information:

CUSTOMER ID ORDER ID DATE AMOUNT ORDER STATUS
01 165651 1/1/2014 $152.00 Success
01 171651 2/1/2014 $152.00 Success
01 196545 3/1/2014 $152.00 Success
01 256913 4/1/2014 $152.00 Success

This export gives us the transaction history for customer 01. We can see from the data export that the customer was acquired on 1/1/2014, has 4 orders, 3 which are successful and 1 refunded. Let’s use this information to start the construction of a total value analysis.

Total value is a quantitative measurement of what an order is “worth”. Value is always defined as Revenue minus Expenses. However, which expenses (and sometimes revenue) a business uses to calculate value vary. Due to this variance, the items included in the calculation are subjective. For example: a business thinks including the cost of paper communications is important, while others find it negligible.

The goal is to find the value items that make up the majority of the customer’s worth on a variable basis. Variable costs are the expenses that change and relate directly to an individual customer. We want to avoid including fixed costs in this analysis since fixed costs are sunk costs and incorrectly add expenses to a customer’s value. Instead, we want to locate expenses related to acquiring and servicing a customer’s specific orders. The most common variable costs that makeup CLV expenses are: Marketing and Acquisition, Product & Shipping, Maintenance, Transaction Fees, Refunds, Chargebacks, and Customer Service Inquires. Each business tracks and stores these items differently. Ideally, these expense items are fields contained in an order spreadsheet row and update automatically with any changes. However, most businesses have a good idea of these costs and can approximate them fairly accurately. Let’s extend our earlier data table with this new information:

CUSTOMER ID ORDER ID DATE OF SALE AMOUNT OF ORDER ACQUISITION COST PRODUCT COST REFUNDS CUSTOMER SERVICING COSTS NET VALUE
01 165651 1/1/2014 $152.00 $85.00 $58.00 $0.00 $14.50 ($5.50)
01 171651 2/1/2014 $152.00 $0.00 $58.00 $0.00 $5.75 $88.25
01 196545 3/1/2014 $152.00 $0.00 $58.00 $0.00 $21.50 $72.50
01 256913 4/1/2014 $152.00 $0.00 $58.00 $152.00 $4.00 ($62.00)

We can now calculate the Historical CLV of customer 01. From the table above, we see the sum of the net value is $93.25 (-$5.50 + $88.25 + $72.50 + -$62.00). Therefore, the Historical CLV of customer 01 on 4/1/2014 is $93.25.

When assessing Historical CLV, it is helpful to create a cumulative running total of the customer to see the changes in their CLV over time. An example of a cumulative table is:

CUSTOMER ID DATE CLV(T)
01 1/1/2014 ($5.50)
01 2/1/2014 $82.75
01 3/1/2014 $155.25
01 4/1/2014 $93.25

To create the cumulative table, take the previous CLV value and add it to the next CLV value. From the data, we see the customer had a starting CLV at 1/1/2014 of -$5.50. The next time period with an order shows a CLV of $88.25. To create the cumulative amount, we add the previous period CLV of -$5.50 to the next time period CLV of $88.25 to get the cumulative CLV of $82.75. We repeat this process through the entire date range for orders associated with the customer. It’s important to see your cumulative CLV at 4/1/2014 matches the overall CLV on 4/1/2014. Our data above shows the sum of all the net values equals the cumulative running total on 4/1/2014.

The last component of calculating Historical CLV is the point in time. From the information presented above, you can see we’ve already calculated Historical CLV. The point in time is relevant because it tells us the value of Historical CLV at that specific point in time. As one would expect, Historical CLV increases over time as long as the customer is active. This is typically the case; however, Historical CLV can decrease if refunds are given on previous orders.

Since customer 01 kept the last product, and the business refunded them, we can safely conclude they are no longer an active customer. This idea of active versus inactive customers alerts you if a customer’s CLV will be changing in the future. For the purpose of this article, we will assume a cancelled customer is inactive indefinitely. However, this is not always the case. It is completely possible that this customer returns and purchases more products in the future. You can account for this by switching to a Predictive CLV calculation. We will discuss that application in later articles.

Now that we have a cumulative Historical CLV table, we can begin to answer many questions relating to the value of this customer. If a decision maker were to ask, “What is the Return on Investment for the acquisition method used for customer 01?”, you can answer it. Let’s walk through how in an example.

First, we need to understand time impacts value. Everyone would prefer to receive $100.00 today as opposed to one year from today. Because of that, you need to look at the time value of money when assessing the ROI for a customer. The idea of time value is to adjust the dollar value at some point in time to account for cost of capital. There is a large amount of information on the Time Value of Money, so we will assume here that you have basic knowledge of the topic. We will assume a 10% discount factor for our analysis. Here is a table showing the adjustment for time value (Present Value):

CUSTOMER ID DATE NET VALUE DISCOUNT PRESENT VALUE
01 1/1/2014 ($5.50) $0.00 ($5.50)
01 2/1/2014 $88.25 ($0.70) $87.55
01 3/1/2014 $72.50 ($1.14) $71.36
01 4/1/2014 ($62.00) $1.46 ($60.54)

Our example is a small series of orders over a short period of time. As you can imagine, the longer the order history, the more impact the Time Value of Money will have on the ROI of you customer. Let’s create the cumulative CLV table showing the Present Value of the orders:

CUSTOMER ID DATE PV [ CLV(T) ]
01 1/1/2014 ($5.50)
01 2/1/2014 $82.05
01 3/1/2014 $153.41
01 4/1/2014 $92.87

Recall the acquisition cost of the customer is $85.00 on 1/1/2014. From our table above, we see customer 01 created a PV of CLV of $92.87. Therefore, the ROI for this customer under the acquisition method is 109% ($92.87 / $85.00). One pitfall people often make is forgetting the $85.00 value is already built into the CLV calculation, so subtracting it will double count the acquisition cost.

Though the information above relates to a simple example with one customer and 4 orders, it demonstrates the entire process of assembling Historical CLV and shows how it can be used to answer real world questions. The next article will extend these concepts to a group of customers and will expand on using Historical CLV for more real life problem solving.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Sign up to get updates and fresh content!

Add a Comment