CLV by Modeling Retention 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


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


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


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.


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


  • 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:
Previous articleSegment LTV by Partial Data from Other Segment
Next articleLTV by Modeling Revenue Function with a Spreadsheet
Raised in the Kibbutz and reborn in the city, Yaniv is a certified entre-parent-neur. When he’s not busy doing SEO, content marketing, administration, QA, fund raising, customer support… [stop to breathe], you can find Yaniv snowboarding down the slopes of France and hiking with his kids. Yaniv holds a B.Sc. in Computer Science and Management from Tel Aviv University. He is also an avid blogger and a speaker at industry events. Before SOOMLA, Yaniv co-founded EyeView


  1. 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.


Please enter your comment!
Please enter your name here