Creating a Dynamic View of Customer Status

When trying to manage a population of customers in a high frequency-of-purchase business where unique customer data are obtainable, it can be helpful to look at a grid comparing:

• Frequency of purchase in the last "active" 30 day period, and;
• Last purchase date.
  

This grid will look like this, when the report is run against the current state of customers:

Another way to look at these dimensions is as continuous dimensions where:

• Freq of Purchase {0-->∞}
• Last Purchase {0-->∞}

And each customer is plotted along these dimensions.

But, to make things convenient, we cut off "last purchase", at, say, 90 days, and frequency at, say, 4 X for our table. We can then bucketize these scalar dimensions into a neat 4X4 (or nXn) grid like the one above.

A simple customer count in each bucket lets one understand current state. This is a perfect application for an OLAP reporting tool. It's also interesting, however, to understand dynamics: How customer status has changed from previous periods--the real point of this post.

To do this, I use the concept of vectors. A table such as the following can be created:

We can then simply average all of the movements for each unique starting cell location. For example, for the starting cell 1,1, which would correspond to Frequency of Purchase = 1 and Last Purchase <= 30 days, we might get {1.283, 1.321} for movement. These two numbers are the sides of a right triangle, the hypotenuse of which is the vector of customer movement.

There is an even more precise way to do this, which is to measure actual change in frequency of purchase and last purchase for each customer. In this way, we can actually measure customers who are going "beyond the edges" of our table--for example purchasing more than 4 times per 30 day period.

We can then plot our vectors in our table, which might look like this:

Each vector "direction" has a meaning. For example, "North" means more recent; "East" means more frequent / valuable, and so on.

We can also get to the velocity of change--the second derivative--by simply differencing two vectors, say, a month apart. These would tell you how a dynamic trend like the one above is changing. This is a very useful indicator of how marketing programs are impacting customer status.

This approach can also be used with virtually any customer attribute replacing these two dimensions, for example:

• Total value
• Customer Lifetime Value
• Category-Specific Activity
• Etc.

This is a neat approach that can be done with basically any pivot table / OLAP tool and a database of customer purchases.