App Monetization, Resource

LTV by Modeling Revenue Function with a Spreadsheet

Mobile_App_LTV_Calculator_copyThis is the 6th section in a series of 6 posts. Here is a summary post explaining all the different methods.

In section 5 we modeled the retention function with a spreadsheet. Here we will use similar statistical methods like regression and log functions to model out the revenue function.

This method is great if you want to build a full model that takes any number of data points and accounts for both revenue and retention.

Download Options

The spreadsheet 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 C)

NOTE: If you have datapoints on ARPU to date instead you can override column F instead of putting the inputs in columns B and C.

Outputs:

  • 180 day LTV

Calculation:

This model assumes that the revenue function is a logarithmic function – y=a*ln(x)+b

The input data (known x’s and known y’s) is used in order to calculate the ‘a’ and ‘b’ paramters with statistical regression. This is done in cells H5 and I5.

Once we have the function it’s easy to calculate 180d LTV – a*ln(180)+b.

The only modification is that we are comparing the 30d ARPU given by the function to the data we already know and adjust the 180d LTV result accordingly.

Pros:

  • Very flexible and can be applied with any number of data points
  • Accounts for variable ARPDAU

Cons:

  • Complicated (although slightly simpler compared to modeling retention function)
  • Calculates every segment on it’s own without using data from previous segments

Advanced hack – working with partial data 

You can easily use this spreadwheet with less than 30 days of data:

  • Put the user and arpdau data in columns B and C or simply put the ARPU to date in column F
  • As long as you have days in column A and the repective ARPU to date in column F the spreadsheet would work
  • If you are using less than 30 points of data simply adjust the formulas in cells H5 and I5 to reference the right ranges

More methods to calculate user value

If you have data from one segment you can use this data to model the LTV in other segments more easily. Here is the post

You can also try other spreadsheet method – modeling your retention fucntion

 

Feel free to share:

Related Posts

2 Comments

  1. Vi Nguyen

    July 28, 2016 at 10:14 am

    Hi Yaniv,
    Can you explain: diffefence between ARPUDAU and Daily ARPU?
    Thanks

    Reply

    • Yaniv Nizan

      July 28, 2016 at 10:59 am

      Hi, thanks for your question.
      ARPDAU is the amount of revenue of that day divided by the amount of active users in that day.
      Daily ARPU is the amount of revenue of that day divided by the amount of users in the original cohort (in this case 1,000). This column is presented as part of the calculation that leads to Commulative ARPU. The statistical regression is applied to the Commulative ARPU column.
      Hope this helps – let me know if you have additional questions.

      Reply

Leave a Reply

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

DOWNLOAD OUR RECENT REPORT

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

We don't spam!

Unsubscribe any time

Categories