Kevin Hillstrom: MineThatData

Exploring How Customers Interact With Advertising, Products, Brands, and Channels, using Multichannel Forensics.

May 08, 2008

An Example: Applying A Life Table To A Problem

How often do we launch new products, brands, or services, only to realize that sales are not meeting expectations?

Expectations are a challenge, because we have to make an educated guess as to what might happen in the future.

Within a few months, we actually have enough data to make another educated
guess ... we can predict the annual repurchase rate for a product, brand or channel.

In this case, our brand launched a new product. After six months, the product is not meeting expectations. Our CEO asks us to understand if the small number of customers who purchased this product are "loyal" to the product.

We ask our SAS programmers or information technology experts to run a query for us. We identify every customer who purchased our new product, to date. For each purchase, we bring along the customer_id, as well as the order date. The dataset is sorted by customer_id and order date.

Next, we re-shape the datasets, adding a column for the order date of the first purchase of merchandise from this product classification for a customer. We then scan the database. Any customer who ordered a second time has the order date for the second purchase put into another column.

If I purchased two times, my row of data looks something like this:
  • Kevin .......... Date1 = 20080115 .......... Date2 = 20080507
If I purchased just one time, my row of data looks something like this:
  • Kevin .......... Date1 = 20080115 .......... Date2 = NULL.
Now we need to re-shape the dataset one more time. In this case, we calculate how many months pass between the first and second purchase. If no second purchase occurred, we instead calculate the total amount of time that passed. If no second purchase occurred, we create a new variable that tells us no second purchase occurred. If I purchased two times, my row of data looks something like this:
  • Kevin Months = 04 .......... Second Purchase = YES
If I purchased one time, my row of data looks something like this:
  • Kevin Months = 04 .......... Second Purchase = NO
We're almost there! Now we summarize the dataset, creating one row per each unique value of months between purchases. We sum the number of customers who purchased after "x" months. We also sum the number of people who went "x" months, through today, and have yet to purchase. These customers do not get included in the analysis after "x" months pass. The resulting life table is included in the image at the start of this post!

In the six months since launching
the new product, just under twenty percent of customers buying the product chose to purchase the product again.

We can extend this relationship from six months through a year. Take a peek at the modeled relationship below:


Notice the nice, smooth relationship exhibited by the data. The relationship indicates that, after twelve months, about 24% of customers will order the product again, putting the product squarely in "Acquisition Mode".

To answer the CEO's question ... it does not appear that customers are loyal to this product. It appears that if this product line is going to grow, it will grow by attracting new customers interested in a one-time purchase of this product line.

With as little as three months of purchase information, one can make a reasonably fair assessment of the anticipated annual repurchase rate of this product line.

And that, my friends, is one way that life tables can be applied to real world problems.

Labels: , ,

3 Comments:

At 1:07 PM , Anonymous Willyzekid said...

Really interesting... However, I can't seem to reproduce your life table (especially the important 'no rebuy' and 'rebuy' rate!). How do you calculate those for months '2'? I understand after 2 months you remove the persons who only had 1 month to repurchase... but i can't factor that correctly in the spreadshet to get your ratios. Driving me crazy :)

 
At 10:36 PM , Blogger Kevin said...

In month two, you calculate the "no rebuy" column first.

In month two, this is equal to 0.9275 * (1 - 0.0544) = 0.8771.

Therefore, the folks who repurchased did so at a 1 - 0.8771 = 0.1229 cumulative rate.

 
At 9:31 PM , Anonymous willyzekid said...

Arff...thank you so much!

 

Post a Comment

Links to this post:

Create a Link

<< Home