The following presentation was given on Wednesday, November 13th at the Slush Conference in Helsinki, Finland. Linked to within the presentation is a spreadsheet that showcases examples of the models discussed, each built on a data set of 100,000 rows of fake user profiles. The profiles were generated by a script.
As stated in the presentation, LTV is difficult to calculate for freemium products and is often done so programmatically (given the large amounts of data that must be parsed in order to capture the stratified nature of freemium user bases). But LTV estimates can be made in spreadsheets; the models introduced here fit curves to player metrics (retention and monetization) in order to project terminal values for various player segments.
The main limitation of spreadsheet modeling for LTV is the demand that adding additional filters to segments poses; the models exemplified in the spreadsheet from this presentation only offer three filters (country of origin, acquisition source, and trailing days of data used) for calculation, and yet the file runs sluggishly, even with a modestly sized data set.
Still, modeling LTV in a spreadsheet serves as a practical and fairly transparent means of making decisions around product design. While not as robust as estimation using programmatic methods, the models demonstrated in the presentation facilitate the data-driven development process and may be helpful in forecasting revenue based on cohort acquisition.