In this post I summarize into actionable steps parts of the substantial body of work by Peter Fader, Bruce Hardie, et. al. in the analysis of Customer Lifetime Value, a critical part of every customer experience program. The relevant source papers are referenced at the end of the post.

In this post, we will focus on the bottom left quadrant of the Fader/Hardie customer relationship map (as I will refer to it). We will be examining situations where there is no on-going contract between the supplier and the customer and where the customer can only purchase at discrete time intervals.

Where does this apply?

This approach is applicable to customer base analysis in many situations where an ongoing contract does not exist but where purchases are limited to once per period.

For instance: regular event attendance, non-subscription periodical magazine purchases, charity donations, mail order companies that send out a catalogue/email on a regular basis, etc.

The discrete nature of the transactions can be classified into three types:

1. Necessarily discrete: attendance at a periodic conference
2. Generally discrete: charity donations, responses to catalogue
3. Discretised by the recording process: cruise ship vacations.

Key assumptions to check

1. There is no on-going contract for sale
2. The times when a customer is able to purchase are limited in some way to just once a period.  You need a “well-defined point in time at which a transaction does or does not occur.”  The customer is NOT able to purchase at any time, in contrast to, say, a book store.
3. After each transaction the customer has a chance to “drop out” or cease to purchase from the company again but the company does not know when this has happened.
4. Different customers can have different probabilities to drop out
5. You should re-validate the model variables for different cohorts of customer. “Different” in this context can mean: different acquisition channels, time of acquisition, etc.

Data needed to model

To use this approach you will need to have a relatively small amount of information for each customer.  In fact this is one of the key benefits of this model.  In addition, you can group together customers with the same attributes making the process even more simple.

For each customer you need to collect:

1. The number of transactions that occurred in the total time under review
2. In what period the last transaction occurred

Summarise this data to count the number of customers that fall into each of the “number of transactions/last transaction” groups, i.e.
x (number of transactions), t (period of last transaction), n (number of customers)

Approach taken

The details of the statistical models are deliberately excluded from this summary but can be referenced in the source papers.

The 30 second version is that the customer transaction process is modelled in terms of different probability distributions. Excel’s Solver function is then used to determine the key variables that define the shape of those distributions. Once the key variables are known they are plugged into equations that have been developed to estimate important business outcomes such as future customer value, expected number of purchase in the next period, etc.

The full version is more complex and you should review the referenced papers in detail for more information.

Values you can determine

The authors have derived a variety of useful equations to determine useful customer information:

1. Probability that a customer is still active in the next period, i.e. that they are still buying from you
2. Probability that a customer will make “y” transactions in a given future multi-period
3. The expected number of transactions that a customer will make in a future multi-period
4. The number of expected future transactions for a specific customer, i.e. the the future value of the customer.

Each of these equations can be implemented in Excel, although they are not what you would call trivial.

How would you use it?

The first and most simple use for this information is simply to create more accurate sales forecasts for a business in a non-contractual setting.

However, a more interesting and useful approach is to use the model to determine when to take some customer treatment based on their expected future purchase pattern. This is made easier because the expected future purchases for a customer do not have to be calculated for each customer individually, they can be grouped into profiles.

After the model has been created, a simple look-up approach based on the two key data points (number of transactions, last transaction period) gives the expected future purchases for all customers with that profile. Customers can then be simply scored for action. Implementing the intervention is thus made much easier.

Implementing this approach

The implementation details of this customer base analysis approach are contained within the following referenced papers, tutorials and examples.

1. Customer-Base Analysis in a Discrete-Time Noncontractual setting
2. Implementing the BG/BB Model for Customer Base Analysis in Excel, includes notes and a sample Excel spreadsheet on how to implement the approach.
3. Probability Models for Customer-Base Analysis