« The Best Election Email Ever? | Main | Life Time Value - solving tomorrow's problems today »

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! 

Posted on Friday, April 16, 2010 at 10:07AM by Registered CommenterDavid Hughes in , , | Comments3 Comments

PrintView Printer Friendly Version

EmailEmail 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

April 16, 2010 | Unregistered CommenterAdrian Swinscoe

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

April 20, 2010 | Unregistered CommenterPooja

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

April 30, 2010 | Unregistered CommenterDavid Hughes
Comments for this entry have been disabled. Additional comments may not be added to this entry at this time.