Calculating The Data In The Graph

Yesterday I shared with you a graph ... showing you how likely first-time buyers were to respond by month. Remember the graph?

The graph prompted a lot of responses. I could write 50 posts in a row and not get any feedback, but this one ... this one generated a lot of feedback. Here was the number one question:

• "How do I calculate the incremental rebuy rates in the graph?"

So let's walk through the process.

I create a dataset that looks like this:

Each column is populated with the order date of the first order, second order, third order, and so forth. If a customer did not place a, say, fifth order, then the column is populated with the value 99999999.

Next, I transform order dates into months. I arbitrarily choose January 2015 as a value of 1001, then February 2015 as 1002, then March 2015 as 1003. December 2020 is then assigned a value of 1072. Here is what the dataset looks like.

From here, I have everything I need to produce the graph at the top of this post. By subtracting the first order date from the second order date, I can calculate how many months pass between a first purchase and a second purchase.

Look at Customer 1:  Take the second order date (1063), subtract from it the first order date (1061), and you get 1063 - 1061 = 2. The customer purchased for the second time in the second full month.

Look at Customer 3:  1042 - 1030 = the customer purchased for the second time twelve months later.

From here, I create a frequency distribution of all second order month time intervals.

Let's pretend that we have 100 customers. 5 purchase for the second time in the same month as a first purchase (a value of 0 when subtracting months above). The incremental probability is 5/100 = 5%.

Now, I subtract the repurchasers out of my base, leaving me with 95 customers. Let's say that 6 customers purchased in Month = 1. 6/95 = 6.3% repurchase in the first full month.

Then, I subtract the 6 repurchasers from my base of 95 customers, leaving me with 89 customers. Let's say that 4 repurchased. 4/89 = 4.5%.

Subtract the 4 repurchasers from my base of 89 customers, leaving me with 85 customers. Let's say that 2 repurchased. 2/85 = 2.4%.

This process is replicated as long as you have data to replicate the process. If you have 36 months of repurchase activity, you'll have 37 data points.

I now have the data I need to produce the graph at the top of this post:

• Month 0 = 5.0%.
• Month 1 = 6.3%.
• Month 2 = 4.5%.
• Month 3=  2.4%.

This analysis can be replicated for 2nd purchases, 3rd purchases, and so on.

This analysis can be done by acquisition month (analyze what December newbies look like vs. April newbies ... always a compelling story).

This analysis can be done by source of acquisition ... by average order value ... by channel, you name it.

It's a simple analysis, it's flexible, and it is informative. It's called a "Life Table" analysis.

Go run the analysis for yourself!

And if you can't run the analysis for yourself, for just $1,200 I'll run the analysis for first-time buyers, for 2x buyers, for 3x buyers, and for 4x buyers. I can turn the analysis around in a few days, max. Email me (kevinh@minethatdata.com) for file format and additional details.