CaddieOpen in CaddieDownload for Mac

How to Calculate Mobile Lifetime Value (LTV) Without a Lifetime of Data

Shared by Caddie Library · Updated
App Masters11:56Open on YouTube
Chapters show their main points

The bottom line

You can estimate a day 365 LTV from just 30 days of cohorted revenue by fitting a power trend line in a spreadsheet and extending it. This lets you set early payback targets for day 7 buying decisions.

Key takeaways

  • Revenue per user for days 1 to 30 is enough raw material. 2:23
    • This cohorted revenue is usually available in your MMP, best split by channel, and it can also be split by geo.
    • LTV is tied to a time frame such as day 60, 180 or 365. The same approach works for each, with no databases or programming.
  • A power trend line fitted to that curve describes its shape. 6:32
    • In Google Sheets, chart cumulative ARPU by day, add a trend line, choose power series and show the equation.
    • The organic traffic fit is about 0.323 × x^0.395.
  • Plugging the equation into days up to 365 projects the long-term LTV. 7:36
    • Organic ARPU of $1.22 at day 30 grows to about $3.32 at day 365.
    • The fitted curve gives $1.25 at day 30, a small jump from the real $1.22.
  • Dividing early LTVs by the day 365 LTV shows how much cost to recover by each day. 9:52
    • To break even by day 365, recover about 21% by day 7, 36% by day 30, 57% by day 90 and 75% by day 180.
    • This matters because much acquisition is judged on a day 7 basis.
  • The method fits a single revenue stream only. 10:42
    • Mixing in-app purchases with ad revenue needs a different approach.
    • If day-by-day ad revenue is available, the same method works for it too.

The goal here is to take a limited amount of data, perhaps day 30 data or even day seven, and extrapolate it to get your day 365 LTVs without requiring elaborate databases, without requiring any programming, and with just the help of a spreadsheet.

Shamauf Rao, RocketShip HQ

Chapter summaries

0:00 Why LTV needs a time frame

A lifetime of data never exists, so LTV has to be a forecast to a fixed horizon.

  • LTV can only be forecast to a fixed horizon, never measured over a lifetime. 1:12
    • Waiting for a user's full lifetime of data is impossible.
    • The model here targets day 365.
    • What LTV means depends on the app, so the definition comes first.
    • Most of the apps this method was used on tie LTV to a set time frame.

2:18 Starting from the cohorted revenue export

The raw input is cohort revenue after each day, and the task is to turn it into a day 365 estimate.

  • Keep the input simple with one revenue stream. 2:32
    • The example uses in-app purchase revenue only.
    • The export lists revenue after day 1, day 2, day 3 and so on up to day 30.
    • The MMP shows every day from 1 to 30 readily.
    • The problem to solve: convert this export into day 365 LTV estimates.
  • Turn the export into revenue per user for each day, then chart it. 3:56
    • The work is done in Google Sheets, and Excel works just as well.
    • Only AppLovin and organic are compared, to keep it simple.
    • The sheet gets columns for day 1 to day 30, with day 7 and day 30 as checkpoints.
    • A line chart of the days shows the shape of the curve, which is the basis for extending past day 30.

5:58 Fit the curve and extend it

A fitted trend line turns 30 days of real data into a curve you can read at any later day.

  • The curve can be read at any target day, not only day 365. 6:00
    • Day 60, 180 or 365 all come from the same curve.
    • Day 30 is the last real point, and the curve's shape decides where it goes next.
    • The trend line is added to the organic-by-day chart. Line thickness is cosmetic and doesn't matter.
    • Under label, pick "use equation" and the sheet prints the power curve's formula on the chart.
  • The equation is typed into a table of days to extend it. 7:04
    • The first day is extended down to day 365.
    • Each cell takes the coefficient times the day number raised to the fitted exponent.
    • The result is a smoother, continuous extrapolated curve running from day 30 to day 365.
    • This run covers organic traffic only.

8:27 Turn LTV into payback targets

A table of LTV by day shows how much of the final value a day 7 buyer must already have earned.

  • Day 7 ARPU is the number most buying decisions use. 8:36
    • Organic day 7 ARPU is $0.71.
    • Most acquisition activity is judged on a day 7 calculus, so this is the figure to compare against.
  • LTV is listed for days 7, 30, 60, 90, 180 and 365. 8:58
    • Day 60 is $1.63, day 90 is $1.91, day 180 is $2.51.
    • Day 365 reads $3.32 in the sheet.
    • Day 60 is about 49% of day 365 by these figures.
  • Each early LTV divided by day 365 LTV gives the payback target. 9:52
    • The assumption is a goal of recovering 100% of costs by day 365.
    • Any other goal, such as a different recovery window, can be set the same way.

In the growth library

Shamanth Rao, Founder of RocketShip HQ, a paid user acquisition agency for apps. Shows how to estimate a day 365 lifetime value from only 7 to 30 days of cohorted revenue, using a spreadsheet and a power-curve trendline. The worked example turns an organic day 30 ARPU of 1.22 into a day 365 estimate of about 3.32, which gives concrete payback targets for paid acquisition.

More from App Masters · The whole library, and ask it anything

Transcript280 lines
0:00

What is up App Nation? It is Steve P. Young, founder of appmasters.com, the place you go when you want an action-packed content in the app business, the podcast, the YouTube channel that you should be subscribed to when you really want to know how to grow in the app business. In this video, my friend Shamauf Rao, who is the founder of rocketshiphq.com, if you guys are looking for paid advertising help, go check them out. It is once again, rocketshiphq.com.

0:33

He's going to walk you through how to calculate your LTV, your lifetime value of a user with very little data. It's a simple formula and you can follow along with him as he shows you real data and then calculates the app's LTV based on the data that he currently has. So, without further ado, here is my man, Shamauf. Hey everyone, this is Shamauf from rocketshiphq. In this video, I'm going to show you how to calculate the LTV of a user without a lifetime of data.

1:12

Uh I'm obviously half flippant when I say that uh because there's no way in the world you're going to get a lifetime of data. And we're going to start by defining what LTV means or it could mean for you. In most cases that uh in most cases and for most apps that I've worked with, an LTV typically is related to a specific time frame. See the day 60, day 365, day 180, and whatnot. Right? In this case, we're going to uh model out getting the day 365 LTV, but certainly you can use the same approach for day 180, day 60, or what have you.

1:55

And the goal here is to take a limited amount of data, perhaps day 30 data or days even day seven, right? And extrapolate it to get your day 365 LTVs without requiring elaborate databases, without requiring any programming, and with just the help of a spreadsheet. Okay. So, we start with what is already available to you. You may not have day 365 data, but most often you will have day 30 cohorted revenues like this. Again, in this case, I'm going to assume for the sake of simplicity just one revenue stream, which is you know, in this case it could just be IAPs, in-app revenue in-app purchase revenues after day one, day two, day three, and so on and so forth until day 30.

2:47

This is usually available in your MMPs. Uh if you see here, I have this I just open revenue after day one, after day two, day three, and so and so and so forth until day 30 is available readily here. Right? Uh and you can get this by channel. I typically recommend doing it by channel. You can also get this by geos, right? But uh I'm going to illustrate the overall approach here, which you can apply however you might want to do this.

3:16

Great. Okay. So, let So, here's our exported data after day one day through day 30. This is what the revenue stream looks like for each of these sources for a specific time frame, right? And our problem statement, so to speak, is how do you take this, convert, and estimate our day 365 LTVs? Great. Let's get going. Okay. So, I'm going to take this, I'm going to put this into this Google spreadsheet because uh I like spreadsheet Google spreadsheets better.

3:51

But I'm sure you can do this in uh Excel just as well. Right. So, what I'm going to do is calculate our pool. Right? I'm going to say D1 our pool, which is uh there we go. Perfect. You have D1 our pool, D1 through D30 our pool. Let's calculate these two sources for now, okay? So, D1 our pool D1 D2 Perfecto, as we say in Spanish. Perfect. Great. Okay. So, let me uh uh we need to make sure this is right. Yeah. This formula is right, and there you go.

4:42

Perfect. Great. Cool. So, we have D7 and D30 numbers. This and the D1 through D30 numbers. For the sake of simplicity, let's just do AppLovin and uh organic. Right? Uh so, this is our pool organic. And this is going to be our pool AppLovin. Perfect. Great. So, here's what we're going to do. And this is day, right? So, day one to whatever. Good. Perfect. Uh let's see what happens. Let's do our magic here. Insert chart.

5:29

Perfect. Right? We have a chart. Let's make this uh Okay. I mean, it shouldn't matter too much. Uh just got to choose a line. Let's do this. Okay. So, here's what you want to do. You're like, "Look, from day one to day seven, day 10, day 30, this is the shape of the curve. Right? And as you go beyond day 30, this is going to continue. Right? So, your our goal is to estimate what this curve will look like at day 365. Right? Or day 180, or day 60, or what have you. Right? So, at this point it's day $1.22. That's the ARPU.

6:15

At day 30, they want to see Right? At day 60, to be 165 or 180 or what not, depending on the shape of this curve. Okay? So, cool. So, we have organic by day. Here's what we want to do. We want to add a trend line. Good. And the type, I would make this a power series. Okay? Good. And line thickness, I'll make it a little thinner. Uh Oh, this is Okay, never never mind. Doesn't matter. Right? Label. Use equation. Perfect. So, this is the equation of the power curve.

6:55

So, let's let's do this, right? Okay, good. So, here's our uh equation for this curve. Great. Now, we we let's We're going to take this equation. We're going to plug it into this table. So, here's how it works. First of all, I'm going to take this. Take this first one, and I'm going to extrapolate this to Right? 365 days.

7:32

Perfect. 365 days. Good. Okay. And our equation is 0.323 x ^ 0.395. Great. 1.323 * x power 1.395. Good. Yeah, there we go. 125. This is more continuous. Obviously, there's a bit of a jump here, 122 to 125. Uh that's the nature of the beast, right? Okay, good. So, here's our extrapolated retention curve. Right? So, here we are. From day 30, we've gone on to day 365. And that's it, really, right? So, you should have day 30 at 122. Day 365 is 332.

8:24

This is just for organics. And let's do this, right? So, how how do we sort make sense of this for our acquisition? Right? A lot of acquisition activity is done based on a day seven calculus. So, let's say day seven day seven ARPU Let's just do Let's do organics for now. Day seven ARPU 0.71 day 30 ARPU day 30 LTV because well, that's what we're calculating, right? Day seven LTV Right? Day 30 is 122 and day 60 is 163.

9:15

Day 90 is 191 day 180 should be Day 180 is 251 332. Right? 2.51 then 3.22 and this is D365 D180 D90 D60 Perfect. So, if you're buying on a D7 basis you know that your LTV is you know, so you need to and let's say assume you need to recover your target is to recover 100% of your costs by D365 then you need to recover D2 by D365. 21% by day 7 Right? And 36% by day 30, 29% by day 60, 90 57% by day 90, 180 75% by day 180 and of course all of it by day 365.

10:42

Okay. And of course this is true if you have one stream of revenue. If you have multiple streams of revenue uh for instance IAPs plus ad revenue you might you will have to use a different approach and we can cover that in a different video or if you have day-wise ad revenue available which some providers do provide you can certainly use a very similar approach for ad revenue just as well. Well, I hope this was helpful.

11:10

All the best. Thank you. All right guys, that was it. Make sure you let me know what you thought in the comments below and make sure you hit that like button if you got any value out of this video. And if you want to learn more about working with Shamoff on how to grow your installs using paid advertising, go check him out. It is rocketshiphq.com. It's also linked up into the description below. And I'll see you on the next video.

11:39

Click the top video if you want to see the video ad format that is working for Shama and his team. And click the bottom video if you want to learn how to lower your cost per install on Instagram advertising. Don't forget to subscribe. Peace.