LP Login

Think Big. Move Fast.

A couple of years ago I did a post on how to estimate lifetime value for a subscription business where I uploaded a sample cohort analsyis that others can use as a template.

I’ve been asked several times how the analysis would differ for an ecommerce business, so I finally got around to uploading a sample cohort analysis for an ecommerce business. Please note that this is a SAMPLE only. Data is dummy data, so you should not use it for benchmarking purposes. I have not allowed editing to the google doc so that the spreadsheet will be useful to anyone who finds it, but you can download it and edit it offline as you see fit.

For an ecommerce business, rather than focusing on the percentage of retained subscribers per cohort, instead you focus on the net revenue (after discounts, returns and refunds) from that cohort in a given period. This revenue has to be normalized by dividing by the number of (original) buyers in each cohort so that you can make meaningful comparisons. You should focus on revenue per (original) buyer in each period for each cohort as the raw data from which you can build a lifetime value analysis. Then you should average across cohorts to understand “typical” revenue per sub in period 1, 2, 3 etc, where period 1 is the first month (quarter/ year) when you see a buyer make a purchase.

You still typically see a steep drop off in revenue per buyer after the initial period. But a well run ecommerce business that does a good job of retention marketing and line expansion should see stable revenue per buyer after the initial drop off. This is in contrast to subscription businesses which typically continue to see attrition over time. If you do see continued drop off, you should model that in a similar way that I do it for subscription businesses, but if you see relatively stable out month revenues per buyer, it’s OK to model that in the out months.

Lifetime Value is calculated as the cumulative contribution of an average customer, so you have to multiply lifetime revenue by contribution margin. Contribution margin should include all variable costs except one time acquisition costs. This typically includes COGS, packaging, shipping and handling, reverse logistics, inventory obsolescence/write offs, customer service, credit card charges, hosting costs, fraud accruals etc. It would not include fixed costs such as photography, production, site development, merchandising or other overhead.

The two most importnat metrics that I look at to gauge the health of an ecommerce business are LTV/Customer Acquisiton Cost ratio and payback period. This is why i highlighted these two metrics in the spreadsheet.

I like to see LTV/CAC > 2.5 (which tells you that you have a robust long term business with enough margin to cover overhead) and Payback periods under 12 months.

If you found this post useful, follow Lightspeed on Twitter @lightspeedvp

  • Stewart Bonn

    Shouldn’t the label in cell O4 in the sample cohort analysis be “Avg % Retained from Previous Month” instead of “Drop from Previous Month”? Aren’t you calculating retention?

  • http://lsvp.wordpress.com jeremyliew

    You’re referring to the original spreadsheet, the cohort analysis for a subscription business, and you are correct. I’ve made the correction. Thank you

  • http://www.talentpartners.fi/ Tuulikki Myllylä

    I agree that ecommerce business, LTV/Customer Acquisiton Cost ratio and payback period are most important to a long term business.

  • Pingback: Life Time Customer Value for Ecommerce Business | My Blog

  • Pingback: Why Do Ecommerce Startups Come in Waves? | PandoDaily

  • Pingback: Quora

  • Hiren

    Great article. I face two challenges in using this model though and would love to get your thoughts on how to work around it.
    1. Contribution margin is not constant across periods or cohorts. It has changed over the last 15 months and I expect that to keep changing every quarter in the next 12 months or so.
    2. The current model ignores the volume effect. Meaning the number of buyers increases significantly over time for us so cohort acquired last month is 10X more than cohort acquired 12 months back in volume but the revenue per buyer is constant. How do you account for this?

  • http://lsvp.wordpress.com jeremyliew

    1. I suggest using. Urgent or projected contribution margin as this is a forward looking tool
    2. That shouldn’t matter much as you are calculating unit economics so just use per user numbers

  • http://www.rjmetrics.com Jake Stein

    Thanks for posting this template, it’s very helpful. My company recently put together a benchmark study for different kinds of ecommerce sites which might help in understanding where individual sites sit relative to their peers.

    You can get the summary infographic at: http://www.rjmetrics.com/customer-lifetime-value-infographic-2012

    and the full report at:
    http://www.rjmetrics.com/eCommerce_Customer_Lifetime_Value_Benchmark_Summer_2012

  • http://gingerparts.com Darryl Collins

    Excellent! Just what I was looking for Jeremy. Very useful. And the comments are great too. Thanks for posting.

  • Pingback: Quora

  • Pingback: Cohort analysis reference (part 1) | For the love of spreadsheets

  • http://twitter.com/Tbizi Travis Biziorek (@Tbizi)

    Jeremy,

    I find this very interesting, however, I was initially confused as to why the big drop from month 1-2. You mention this is normal, but it wasn’t immediately obvious as to why.

    I now see that you’re actually accounting for churn (in a way) by calculating the average monthly spend per cohort user rather than ‘buyer’.

    It would be awesome to see this explained in the post. Also, the verbiage in the spreadsheet isn’t 100% clear since you say “per buyer”, which initially led me to believe per buyer during each month rather than dividing by the total cohort users. Something else that would have made this immediately clear would be some dummy data in the actual spreadsheet. By looking at the formulas, I’d immediately know how you were calculating those numbers.

    Sorry if that sounds too critical. I really enjoyed the post and will definitely leverage the spreadsheet.

    Thanks!

    Travis

  • http://lsvp.wordpress.com jeremyliew

    Thanks for the note, I will clarify

  • karine

    hi i am trying to calculate cumulative income for a 3 month subscription- if i have 10 new users a month who buy a 3 month membership then next month i have 10 new more but from the fourth month i have 1 less and then the fifth i have 2 less and so on- how do i calculate that?

  • http://lsvp.wordpress.com jeremyliew

    I’d suggest focusing just on monthly revenue from each cohort. So if a 3 mth subscription costs $30 then that’s $10/mth. So if you have 10 new subs for the 3 mth deal then for the first 3 months you’ll have $100/mth in revenue each month. Then depending on renewals you will have to continue to calculate monthly revenue (not cashflow) on the same basis. Then proceed as outlined above using that as your raw data

  • Pingback: What’s a Customer Worth Anyway? | Orkiv Blog

  • Pingback: How to Project Customer Retention for a Subscription Business « Lightspeed Venture Partners Blog

  • http://www.facebook.com/simon.u.krueger Simon Krueger

    Jeremy, thanks a lot first of all. I guess, since the ratio LTV/CAC is a rule-of-thumb it might not matter much for some rough estimations. However, modeling it in detail I found that there are two additional factors that can have a huge impact on lifetime values: 1) cost of capital and 2) customer retention costs. In regards to 1) Shouldn’t everything be done in present values thus discounting monthly revenues? 2) Shouldn’t the costs of retaining customers for several months or even years (loyalty gifts, extra discounts, calls & meetings in B2B even) also be deducted from the average lifetime value? Since then, I found that depending on higher levels of 1) and 2) the lifetime value tends to increase initially, before it comes down again – which means that it might be more profitable to stop retention efforts at some point.

  • Pingback: 3 Great Customer Lifetime Value Resources | 500friends Lifetime Value Blog

  • Pingback: I’m telling you, G, it’s all about the CLV | Tropical Considerations