It's fun to share this, as figuring out these numbers has always been tricky. Feels good to finally open up the discussion and get these formulas figured out. Also been really fun to develop the spreadsheet as a community effort.
What are the figures based on (real numbers, log(/similar) real numbers, fake numbers) - would love to know more behind this other than the generic pdf from BVP!
Any reason that "avg rev per customer" is kept constant and not variable from month to month? It's easy enough to change in the spreadsheet but I was wondering if there's a beneficial reason for using a constant value?
I've been looking for something similar but for non SaaS websites. My site will rely on targeted advertising and the sale of classified ads. Is there something similar that suits my use, taking into account viral coefficients etc.
And along the same lines, is it more reasonable to make estimates on page views or uniques per month??
My site is going to have specific tags, somewhat like Stack Overflow. I see Trafficspaces has the ability to support contextual ads but there's not a lot of info. Ideally I'd like advertisers to be able to bid on a set of tags--is this possible?
Using this formula, I get different results for "Avg customer lifetime (months)" than the OP.
I calculate the average length of the customer tenure based on the formula: 0.5 = (1-churn%)^t, where "churn%" is the monthly churn rate, and t is time passed in months. Basically, this formula says: when will 50% of the customers be left?
You can solve for t:
0.5 = (1-churn%)^t
ln (0.5) =ln(1 - churn%)^t
ln (0.5) =t x ln(1 - churn%)
t = ln (0.5) / ln (1 - churn%)
You can test this math by calculating how long t is for a churn of 50% (it's 1 month).
Using this math, the average tenure for a monthly churn of 1% would be 60 months. The average tenure is useful because you can then do a discounted cash flow analysis on 100% of the cash flows until time t, to calculate the lifetime value of the average customer. So in this case, you would be discounting 100% of 60 months of cash flows.
The average tenure goes down rapidly as you increase the churn rate. At 2% churn, the average tenure is 34 months. At 3%, it's 23 months. At 5%, it's 14 months. And at 10%, it's 7 months.
If you have enough data, you can use a non-constant churn rate as well, as churn rate definitely goes down over time.
14 comments
[ 5.4 ms ] story [ 49.9 ms ] threadIn case you find this worthwhile, I uploaded a spreadsheet showing my calculations which you can see here: https://spreadsheets.google.com/ccc?key=0Ag-jmDLo09WNdHBNY1d...
What are the figures based on (real numbers, log(/similar) real numbers, fake numbers) - would love to know more behind this other than the generic pdf from BVP!
Any reason that "avg rev per customer" is kept constant and not variable from month to month? It's easy enough to change in the spreadsheet but I was wondering if there's a beneficial reason for using a constant value?
googledoc---->upload file.
you are done.
And along the same lines, is it more reasonable to make estimates on page views or uniques per month??
See trafficspaces.com
Using this formula, I get different results for "Avg customer lifetime (months)" than the OP.
I calculate the average length of the customer tenure based on the formula: 0.5 = (1-churn%)^t, where "churn%" is the monthly churn rate, and t is time passed in months. Basically, this formula says: when will 50% of the customers be left?
You can solve for t:
0.5 = (1-churn%)^t
ln (0.5) =ln(1 - churn%)^t
ln (0.5) =t x ln(1 - churn%)
t = ln (0.5) / ln (1 - churn%)
You can test this math by calculating how long t is for a churn of 50% (it's 1 month).
Using this math, the average tenure for a monthly churn of 1% would be 60 months. The average tenure is useful because you can then do a discounted cash flow analysis on 100% of the cash flows until time t, to calculate the lifetime value of the average customer. So in this case, you would be discounting 100% of 60 months of cash flows.
The average tenure goes down rapidly as you increase the churn rate. At 2% churn, the average tenure is 34 months. At 3%, it's 23 months. At 5%, it's 14 months. And at 10%, it's 7 months.
If you have enough data, you can use a non-constant churn rate as well, as churn rate definitely goes down over time.