7 ms·

Subscription Revenue Model (spreadsheet)

37 points by rlivsey 16y ago

jacquesm 16y ago
What did you base the customer life cycle on?
- ryancarson 16y ago
  It's automatically calculated based on churn. IE 10% churn would be 10 months (100/10 = 10)
  - BobbyH 16y ago
    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-9z5wczSfbM3KLZavFdM1WoQhK7q5wEyoiWWchInhwc&pli=1 https://docs.google.com/drawings/edit?id=1-9z5wczSfbM3KLZavF... 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-jmDLo09WNdHBNY1d0ejY3OWlHcGMtTWJSemI4WlE&hl=en#gid=0 https://spreadsheets.google.com/ccc?key=0Ag-jmDLo09WNdHBNY1d...
streety 16y ago
The spreadsheet links to http://bit.ly/93pK2v http://bit.ly/93pK2v (pdf) as a source of further information. Seems to be a discussion of SaaS in general.
ryancarson 16y ago
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 16y ago
  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!
- tylerrooney 16y ago
  Thanks for posting this Ryan. 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?
zackham 16y ago
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 16y ago
  "File -> Make a copy..." from the toolbar should do the trick
- Concours 16y ago
  file----> download (excel...etc) googledoc---->upload file. you are done.
mtr 16y ago
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 16y ago
  Targeted advertising? Classified ads? Sounds like music to my ears. See trafficspaces.com
  - mtr 16y ago
    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?
BobbyH 16y ago
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=frontcover http://books.google.com/books?id=ctAj_SfSrKIC&printsec=f... 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.

37 points by rlivsey 16y ago

jacquesm 16y ago

What did you base the customer life cycle on?

ryancarson 16y ago
It's automatically calculated based on churn. IE 10% churn would be 10 months (100/10 = 10)
- BobbyH 16y ago
  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-9z5wczSfbM3KLZavFdM1WoQhK7q5wEyoiWWchInhwc&pli=1 https://docs.google.com/drawings/edit?id=1-9z5wczSfbM3KLZavF... 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-jmDLo09WNdHBNY1d0ejY3OWlHcGMtTWJSemI4WlE&hl=en#gid=0 https://spreadsheets.google.com/ccc?key=0Ag-jmDLo09WNdHBNY1d...

streety 16y ago

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

ryancarson 16y ago

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 16y ago
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!
tylerrooney 16y ago
Thanks for posting this Ryan. 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?

zackham 16y ago

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 16y ago
"File -> Make a copy..." from the toolbar should do the trick
Concours 16y ago
file----> download (excel...etc) googledoc---->upload file. you are done.

mtr 16y ago

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 16y ago
Targeted advertising? Classified ads? Sounds like music to my ears. See trafficspaces.com
- mtr 16y ago
  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?

BobbyH 16y ago

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=frontcover http://books.google.com/books?id=ctAj_SfSrKIC&printsec=f... 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.