App Monetization, Resource

CLV by Modeling Retnetion Function with a Spreadsheet

LTV calcualtion by using retention data in a spreadsheetThis is the 5th section in a series of 6 posts. Here is a summary post explaining all the different methods.

In sections 1-4 we relied on basic math to calculate customer lifetime value. In this method we are taking it a step further and relaying on tools like statistical regression, logarithmic functions and integral calculations. To do this we will need to feed the retention data to a spreadsheet.

This method is useful if you want to build a model that can be used over and over while having varying number of data points from different apps, traffic sources and cohorts.

Download Options

This method will be more useful for you if you have your own copy so feel free to get one:

Calculator Details

Inputs:

  • Day 1-30 retention data (column B)
  • Day 1-30 ARPDAU data (column D)

Outputs:

  • The expected lifespan in user days (users*days)
  • 180 day LTV

Calculation:

This model assumes that the retention function is a power function – y=a*x^b

It uses statistical regression to estimate the parameters a and b based on the input data (known x’s and known y’s). Statistical regression is for linear functions so to find a power function we are running the regression on ln(x) and ln(y). All this magic happens in cells F5 and G5 of the spreadsheet.

Once we have the function we are simply calculating all the values from x=1 to x=180 (day-1 to day-180 retention values). We are estimating the retention numbers from day-31 to day-180 using the retention fucntion. You can see this in column J.

The next step here is to sum all the retention values from day-1 to day-180. This is referred to as Integral and the result is the number of user days to date. You can see the integral to date in Column K and the 180d Integral in Column L.

The last step is to multiply the expected user days by the expected ARPDAU. We are averaging the revenue per user per day over the first 30 days to estimate the forward ARPDAU.

Pros:

  • Very flexible and can be applied with any number of retention data points

Cons:

  • Complicated
  • Calculates every segment on it’s own without using data from previous segments
  • Assumes ARPDAU is constent

Advanced hack – tweaking for partial retention data

If you only have partial retention data from the first 30 days you can adapt the spreadsheet to take any number of retention data points. For example you might only have day-1, day-7, day-14 and day-30. As long as you put the input in columns A and B as pairs of x,y where x is the day and y is the retention you should be ok. Simply modify the formulas in cells F5 and G5 to reference the new range of cells that contains the new input. Here are the current formulas with the reference highlighted –

  • F5=exp(index(linest(ln(C6:C35),ln(A6:A35)),2))
  • G5=index(linest(ln(C6:C35),ln(A6:A35)),1).

More methods to calculate user value

Here is a simpler method that can be applied if you have data from 180 days on existing segments.

If you like the spreadsheet method you might also try modeling the revenue function as an alternative.

 

Feel free to share:

Related Posts

One Comment

  1. Fiona

    November 4, 2016 at 6:18 am

    Nice post. The average ARPDAU part is intuitive. I didn’t get the retention rate part. Basically, you get daily expected retention rate. Why integrate daily retention rate? Suppose there is no overlap between users coming back after 1 day and other days. Your formula is calculating average user life. How about there are overlap between users? Your formula will over-estimate the expected user life.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

DOWNLOAD OUR RECENT REPORT

Join 7847 other smart people who get email updates for free!

We don't spam!

Unsubscribe any time

Categories