Anther Unforgivably Long Post on Lifetime Value

A few weeks ago I wrote a long series of posts about the uses of Lifetime Value. I made several references to a “Lifetime Value system” that would support many of these applications. I’ve now taken the logical next step and built a sample system, just to see how well what I described hung together in practice. This only took a few days work, using a very slick and efficient business intelligence tool called QlikTech (for which Client X Client is a reseller, if anyone is interested). I’m happy to report that the results were more than satisfactory—the system exceeds my expectations for the amount of useful information it makes easily accessible. And it’s lots of fun to play with, in a geeky sort of way.

I’ll go through the contents shortly. But first you need a bit of context. You’ll recall from my earlier posts that my fundamental premise was that starting with Lifetime Value and then breaking it into components is a powerful way to understand what’s happening in a business. In the test system, I started with 3.3 million transactions relating to 1.1 million customers. Dates ranged from 2002 through mid-2006. Customers were assigned to groups based on the year of their first order (Start Year), the product purchased in that order and the promotion source. Transactions were further classified by ”Life Year”—that is, whether the purchase within the first year, second year, third year or fourth year since the original order. I also calculated a summary figure at the customer level for whether each customer was “active” (that is, had at least one transaction) during each Life Year. For each transaction, I had available the marketing cost, revenue (positive or negative, since refunds were included in the data), product cost (cost of goods plus fulfillment). I also had the product purchased in each transaction, which I summarized into three categories: original purchase, repurchase of the original product (renewal), and purchase of a different product (cross sell).

Once the actual data had been summarized, I built a simple forecasting system that used it to project future results for each customer group with less than four years of history. The projections included the same elements (costs, revenues, etc.) as the original transactions. This meant I had four “life years” of data, either actual or forecast or a combination, for each set of customers. This was all the data I needed for my analyses.

The user interface of the system is organized into tabs, each designed to answer a particular question. Because QlikTech makes it very easy to drill down along multiple dimensions, I could view reports in each tab for the business as a whole or for subsets such as a particular product, source, start year, life year, etc. This effectively meant I could examine results for different segments, sliced pretty much whatever way I wanted.

The questions and their associated tabs are:

- How does total performance compare across products? (Overview tab). This is the natural starting point. It shows the number of new customers, lifetime value per customer, and total lifetime value added (i.e., number of customers x value per customer). This shows the total amount of value created by each product. On this and all other tabs, I can see the data both in tables and on charts that highlight different elements.

Incidentally, the system is set up so I can choose how many Life Years to include in the LTV calculation and what discount rate to apply to future years. These values are controlled with sliders so I can change them and see the numbers and charts adjust instantly. I told you this was geeky fun.

- Which products show the greatest change in value? (Variance tab). To my mind, this is the key to the system because it lets the user identify products that merit closer examination because their performance has changed, either for better or worse. It does this by calculating the values for 2004 and 2005 for Lifetime Value and its two major components (number of new customers and value per customer). The system then calculates the change in each of these elements from year to year. It then does a standard variance calculation showing how much of the total change in LTV added was due to quantity (change in new customers x 2004 value per customer), how much to rate (change in value per customers x 2004 new customers), and how much to the combination of changes (change in value per customer x change in new customers). Users can sort the product list on any of these elements. This means they can quickly identify which products, say, had the greatest improvement in value per customer or worst drop in number of new customers. The ones with the biggest changes are the ones you want to look at first.

- How has LTV changed over time? (Detail by Start Year). This shows whether a product is strengthening or weakening over time by breaking out the three main LTV measures (value added, new customers, value per customer) by Start Year. ‘Nuff said.

- How much value is earned in the first, second, third, etc. year of the customer’s lifetime? (Detail by Life Year). This breaks down the total value per customer figure to see how much is earned in each Life Year. It also multiples this times the number of starting customers to show the net dollar amount earned in each year. This helps companies understand the cash flows associated with a new customer, and in particular how quickly they are recouping the acquisition cost.

- How have the source mix and LTV by source changed over time? (Detail by Source). This shows the number of new customers, value per customer, and LTV added, broken down by source by start year. Thus it shows both the change in mix and the change in performance by source. This helps users understand some of the dynamics driving the changes in the value for the product as a whole. The charts on this tab are particularly helpful, making it easy to see which sources are growing and shrinking, how performance within each source is changing, and which sources are providing higher or lower fractions of the total value.

- How many customers remain active in the first, second, third, etc. year after they begin? (Active Customers by Year). This shows the active customer counts by Life Year for each Start Year group. Seeing how long customers remain active helps to illustrate attrition and longevity—but, unlike summary measures, it shows the drop-off patterns from year to year. Showing how the values change for different Start Year groups show whether results are getting better or worse over time. This might reflect either a change in the nature of the customers acquired during different years, or a change in customer satisfaction with their experience with the company. This tab (like most others, although I haven’t mentioned it) also shows these values further broken down by source, so users can see whether a change in over-all results is due to a change in source mix, can compare performance for different sources (which usually does vary significantly), and can see how the sources themselves change over time. Again, charts make this much easier to understand than raw tables.

- How much value is earned from original, renewal and cross sell orders, and how is this distributed over time? (Value by Order Type). This shows the net value per starting customer for the different types of orders (original, renewal and cross sell). It further shows these broken down by Life Year. It’s particularly important in businesses which depend on “loss leaders” to bring in new customers and then make their money by selling them other products. Gaining this holistic view of the customer is one of the major reasons to look at Lifetime Value rather than simply analyzing product sales on their own.

- How much do revenue, marketing costs and product costs contribute to total value, and how does this change over time? (Value by Value Type). This is yet another way of looking at the lifetime value components, in this case considering the revenue, marketing costs and products costs. These are further broken out by Life Year, which particularly highlights the impact of acquisition costs (which by definition occur during the first Life Year). Further splitting the results by source makes even clearer which sources succeed because their acquisition costs are low, and which perform well because their customers are of high quality. This insight can help suggest which sources have the best prospects for further growth and which might need to be cut back.

- How is attrition impacting customer value and how is it changing? (LTV Components Overview). This shows some detail within the value per customer component, breaking it down by value per original order, value on later orders (renewals plus cross sell), and active years per customer. Note that the math isn’t quite right here—value on later orders actually is calculated by (active years per customer x later order value per active year). I didn’t show that final component because it’s redundant and there is enough going on already. The most concrete new piece of information shown in this tab is the active years per customer figure. This is shown by Start Year to see any changes over time. The tab also shows original value, which was already presented in the Value by Order Type tab, although not by Start Year. Later value was also shown in Value by Order Type, although there it was split between renewal and cross sell.

- What are the detailed performance measures? (LTV Components by Source). This tab shows the finest level of component analysis. It breaks the original order value into acquisition cost and gross margin (revenue – product cost), and breaks the later order value into value per year and active years per customer. These are divided by source (for all Start Years combined) and by Start Year (for all sources combined.)

- What are detailed performance measures by source over time? (LTV Components by Year). This also shows the acquisition cost, gross margin, later value per year and active years per customer, but now broken down by source by Start Year. This provides the most precise view of how these measures have changed over time.

- What are active customers worth in later years? (Value per Active Customer). This shows the values earned per active customer in later Life Years. Note that all previous figures looked at value per starting customer. The value per active customer figure is of course higher in any given year, since there are always fewer active than starting customers once you get past Life Year 1. Value per active customer gives some indication of what you might spend to retain these customers, although of course a proper comparison would be to look ahead over the same time horizon as your original Lifetime Value calculations. I couldn’t do this for most groups in my sample system because I didn’t forecast beyond 2006. A more sophisticated forecasting system wouldn’t have that limit and would thus give a true LTV per active customer. Results in this tab are also broken down by Start Year so any trends become apparent.

- How does value per active customer change by source? (Value per Active Customer Detail). This adds a source break-down to the value per active customer by Life Year by Start Year. Again the primary purpose is to help understand what currently active customers are worth, this time at a source level.

- Has across-the-board performance changed significantly from one calendar year to another? (Value by Transaction Year). This shows value per Life Year per starting customer, but it’s organized by calendar year rather than Start Year. This would highlight any changes affecting all customers at the same time, such as an across-the-board price increase, general fall-off or improvement due to economic conditions, or the result of a fulfillment problem. Values in this tab are not discounted relative to the customer Start Year, so they will differ from the figures in the Detail by Life Year tab unless the discount rate is set to 0.

Whew! You definitely deserve a prize if you’ve actually read this far. I know this is dense stuff, but it’s also fascinating (to me, at least) just how much can be extracted from a relatively simple set of information. Trust me that it’s much more exciting when it’s your own data you’re looking at, and you can suddenly see information that’s been hidden for years.

It also helps that the charts have pretty colors.