Customer Lifetime Value

Customer Lifetime Value measures a customer’s total profit contribution to an organization. In its simplest form it’s measured by using this simple formula:

(Revenue - Cost) * Retention.

Retention is the length of time the customer frequents the business. There are more complex ways of looking at CLTV including the addition of a discount rate to determine the present value of future cash flows.

Customers don’t stick around for ever although most companies would like them to. Some businesses don’t keep their current customers for much longer than a few years due to aging. Clothing stores that sell to the teenage market will typically see customers for a few years and then leave as tastes and lifestyles change with age. Customers who don’t stick around are the fuel for churn analysis models. Anyone in a service business needs to understand why customers leave in order to maximize profits.

The CLTV metric can be created from different areas of the business independently and then analyzed overall to understand the profit contribution from each area. For example, some areas might not be as profitable as others but they might be a key component in bringing in new customers to the business. Cross selling into this area is likely not going to add to the bottom line but customers who are part of this business unit would be ideal candidates for cross selling opportunities into other areas of the business.

In addition to CLTV you might want to consider creating a Customer Lifetime Market Basket (CLTMB). Companies that want to reduce inventories might look at certain products as being unprofitable and decide to remove them from the regular inventory. By determining what makes up a customer’s basket, a company might decide that certain inventory items are required for customer retention.

Julian Dates

I need to work with Julian dates since they will facilitate some MDX queries I am creating for customer lifetime value. Unfortunately SQL Server does not support Julian dates but they’re easy to create so that’s what today’s discussion is all about. I’ll be using the dates more than once so creating a user defined function is probably a good idea.

User defined functions consist of several parts. Data types of parameters and return values must be specified and you have to name the function so you can call it. For more information, check out books on line. You’re probably wondering why the functions start with January 1, 1753. This is where SQL Server starts with date arithmetic so we can’t go back any further than that. The Julian value of that date is 2361331 so that’s where that number comes from. These functions simply add or subtract integer values from that baseline. I normally don’t hard code values into programs but the baseline for Julian dates is constant so it will never change.

Here are the two functions. Just type them into the SQL Query editor and run them. They will automatically be created and are visible in the Programmability/Functions/Scalar Functions folder of the database you created them in.

CREATE FUNCTION udf_datetojul (@any_dte datetime)
RETURNS INTEGER
AS
BEGIN
RETURN DATEDIFF(D,'1/1/1753',@any_dte)+2361331
END

CREATE FUNCTION udf_jultodate (@julian_dte INT)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(D,@julian_dte-2361331,'01/01/1753')
END

Here’s the call to convert the system date to a Julian value:

SELECT dbo.udf_datetojul(getdate())

If you want more information on Julian dates, here’s a great reference: http://aa.usno.navy.mil/data/docs/JulianDate.html

Does Your Data Support The Information Your Business Needs?

We are currently working on a demonstration for a retail merchandising application. We begin with an architectural drawing of a retail environment. An overhead view of the entire store is used and we also look at each display unit from a head on perspective; the same way a shopper would view the display. We map all of our business metrics into the retail environment. This includes sales, supply chain, product and employee performance metrics. Our tools can display cross selling opportunities, product associations, and the effects of both internal and external marketing efforts. We can also show the same shelf and how it changes over time, profit contributions, metrics by employee or customer type or store and so on.

Here’s the problem we’re faced with. We wanted to map raw data into a mock up from an existing retail database but the appropriate data simply isn’t there to support the information metrics we need to run the strategic and tactical side of the business. The operational part of the OLTP database is fine and you wouldn’t think there is anything wrong with it. That’s one of the problems with databases and commercial applications.

An off the shelf application rarely encompasses the strategic vision of the company that uses it.

We wanted to create metrics for product categories and sub categories like gross revenues for the men’s wear department and then men’s shirts. But all of the raw data is focused on the performance of the sales rep. We can create basic metrics but we can’t create key performance indicators against the products, only against the employees. Each one of them has a goal to achieve but there’s nothing for the elements within the store. We also wanted to establish metrics for customer lifetime value and the data support for that was also sorely lacking. There are more examples but you get the idea. This retail database simply does not have sufficient raw data of the type needed to perform anything more than simple analysis.

What do you do if you’re faced with this problem? You have to go out and get a new retail application, right? This is what companies often do and they pay big money for shiny new apps because they feel that new applications must capture the information they need to run the company. Unfortunately new applications often don’t address the problem you are trying to solve.

Determine what your business information requirements are before you go out and get the latest software app. You probably already capture most of the data already in your current applications. Our retail example had 80% of what we needed. If something is missing, consider having a small app written to capture the missing pieces. It will likely be cheaper to implement since you get exactly what you’re missing and you won’t have to change everything else you already do. Create your business intelligence reporting from a centralized database so that users always see the same view of the data. This is how business intelligence solutions work anyway – centralized reporting of consolidated data.