Hacker News Reader

Comment by jasonkester | Hacker News Reader

This article stops short of giving the one piece that we need to take action on this: The formula.

So, given a database full of customer records, all of which have a signup date, some of which have a cancel date, what is the formula one would use to determine the average expected "lifetime" of a customer.

I suspect that the author doesn't want to give us this formula, as it's part of the secret sauce that the company behind the blog post sells. Still, maybe if we ask nicely enough we can guilt it out of him?

View on HN

count(1) as cnt, age from ( select FN(cancled_at, now) as age from accounts where account.signup_date is between x and y ) group by age;

where FN is a function that takes two dates and gives you an integer unit (usually months.) this will give you the distribution of average account lengths for people that started between x and y. note that x-y should be 1 unit in FN(canceled_at, now), or 1 month (where a month is 30 days.)

Then what you can do is either simulate it, or (look at the data), see where you lumps lie so you can do some better back-of-the-napkin LTV calculations...

of course, this does not take into account upgrades or different account levels, etc..

I'd bet it's different for every company, so learning his formula would not really help. Learning the variables that he generates from his database and uses as inputs to his modeling would, on the other hand, be helful.

No, it's just statistics.

Sure, the reasons why people are leaving might be different, and their distribution of lifetimes might be the way they are because of business reasons. But the math to determine that distribution will be the same.

I actually went so far as to ask this question over at the Statistics stackexchange site. Being mathematicians, they debated it briefly, then concluded that it was possible to determine, which, to a mathematician, is the same thing as a solution.

I'm still hoping somebody who's done the calculation will share the magic SQL query.

Maybe we're talking past each other. I'm saying that the specific model for one site won't work for another site. But the procedure will work just fine, because the procedure is just regression or any of its cousins.

As mentioned below, it is possible to determine the customer distribution. We employ a few methods to accomplish this. Stay tuned for more content soon.

I have never looked in CLV, but I would

1) Set different account lifetime intervals (0-3 months, 3-6 months, 9-12 months, etc...).

2) Determine the percentage of customers we have in each intervals

3) Calculate the Interval Lifetime Value.

4) Now instead of having one CLV, I can say "For every 20 new sign ups we know 3 will stay for 3 months and we will break even on those, 50 will stay for one year and we will earn $1000 total from those, and the rest will stay for x months and we will earn $1500 from those...."

Reply on news.ycombinator.com