
Subscription Revenue Model (spreadsheet) - rlivsey
https://spreadsheets.google.com/ccc?key=0AkxZrw3662U_dEhQa0Y4T3c5RU5mcGd6N0twYXhLZWc&hl=en&authkey=CN_dm8wH#
======
BobbyH
I use a different method for calculating the "Avg customer lifetime (months)",
which is implied by some logic from a great book called The Loyalty Effect:
[http://books.google.com/books?id=ctAj_SfSrKIC&printsec=f...](http://books.google.com/books?id=ctAj_SfSrKIC&printsec=frontcover)

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.

------
streety
The spreadsheet links to <http://bit.ly/93pK2v> (pdf) as a source of further
information. Seems to be a discussion of SaaS in general.

------
ryancarson
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.

~~~
dotBen
Hey Ryan, is there a background post/etc to this?

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!

------
mtr
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??

~~~
DJN
Targeted advertising? Classified ads? Sounds like music to my ears.

See trafficspaces.com

~~~
mtr
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?

------
zackham
Does anyone know how to copy this spreadsheet to a personal Google account so
it is editable and private? I am not seeing that option. Thanks!

~~~
rlivsey
"File -> Make a copy..." from the toolbar should do the trick

------
jacquesm
What did you base the customer life cycle on?

~~~
ryancarson
It's automatically calculated based on churn. IE 10% churn would be 10 months
(100/10 = 10)

~~~
BobbyH
Hi Ryan, I think that the rule of thumb used in the spreadsheet overstates the
average customer tenure (as calculated by the alternate formula I outlined in
another comment):
[https://docs.google.com/drawings/edit?id=1-9z5wczSfbM3KLZavF...](https://docs.google.com/drawings/edit?id=1-9z5wczSfbM3KLZavFdM1WoQhK7q5wEyoiWWchInhwc&pli=1)

In 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...](https://spreadsheets.google.com/ccc?key=0Ag-
jmDLo09WNdHBNY1d0ejY3OWlHcGMtTWJSemI4WlE&hl=en#gid=0)

