Life Time Value - a free spreadsheet to help with all the tricky sums
My last blog post in collaboration with Avinash Kaushik included a wonderful free souvenir. It was an Excel workbook that we created to help with all the tricky sums you'll need to crunch when you begin a life time value analysis of your business. The blog has generated lots of queries about the topic that we thought we would remind people there's a tool to help!
So, feel free to download the "Life Time Value Workbook" and use it to kick-start your LTV journey. One Worksheet helps you understand just how different your "best" and "average" customers may be. Simply plug in a few facts about order values, number of sales a year and how long they have been customers for and BINGO the spreadsheet shows you how much profit you make from different customer types.
The second worksheet takes you though a much more complicated LTV model for a specific customer segment. It lets you make projections up to 5 years ahead and builds in the "Net Present Value" calculations so that you can impress your Bean Counters that you recognise there is a risk involved in spending money on marketing.
One final thought. You'll need to do some rummaging around in buckets of data to get some of the customer information - don't expect it all to be waiting for you in your web analytics tool. For example, you may not have "repeat orders" consolidated for individual customers - you'll have to run a few reports to get a basic "single customer view" and then tot up their total spend and total orders for a given period.
Once you have that data, you can rank and segment the customers into best, average and worst. Or, by adding a source code, you could split your file down between "email list rental" versus "affiliate" customers, or people recruited by "price offers" versus "partner offers". You should be able to see patterns emegring that will have a profound impact on your future acquisition and retention activity.
Our blog and the comments will give you much more food for thought.
Enjoy!
David Hughes
in Analytics, LTV, Life Time Value
|
3 Comments
View Printer Friendly Version
Email Article to Friend
Reader Comments (3)
Hi David,
Thanks for your post and spreadsheet. Looks great.
I wrote about something similar here: http://www.adrianswinscoe.com/blog/lifetime-customer/
where I included the estimated value of referral business gained from a client. Could you integrate this into the spreadsheet?
Adrian
David - I LOVED your blog post! I'm a student at Northwestern, and we used Markov chain modeling last quarter in a very similar fashion for computing LTV. I wrote a blog post comparing our technique to yours. I'm fairly certain you presented an introductory spreadsheet, and incorporate the differences highlighted when required, but you can check it out anyway:
http://www.vitaminimc.com/2010/04/computing-customer-lifetime-value.html
Hi Adrian
Glad that you liked my "basic" version of LTV modeling. A few people have mentioned the issue of advocacy and how we calculate incremental value from "best customers" when the recruit more customers for us. The short answer is that pure LTV work always bases its calculations on the "original" customers and can look at their incremental value as a segment over time. They may bring us customers who are of a different value to themselves so when we are looking at "best customers", even if they bring in 1 more customer over 3 years the new ones may only be "average customers".
It's not impossible to model - we should really look at customers as an alternative acquisition source. If we have saved £20 on acquiring a new customer because we have such lovely advocates then that saving needs to be factored in somewhere, but maybe not straight into a classic transactional LTV model.
You could also argue that best customers are going to be active in social media and may encourage others to join our fan pages/forums...this kind of micro-conversation should be tracked but few companies are looking at LTV in such sophisticated wasy...YET!
Pooja
Yes, your method is more advanced than mne - maybe people can graduate onto that once they have found their feet with the basic principles. Thanks for the link anyway
David