The F2P Excel Model is free to download, but you must first subscribe to the Mobile Dev Memo weekly newsletter to access it. You'll get updates each Monday when new posts are added, and you can unsubscribe at any time.
Note! This file is in .xlsx format and will not run on Open Office or Excel versions prior to Excel 2010.
Last Edited: February 07, 2014.
FREQUENTLY ASKED QUESTIONS:
- How old is this model?
This model was first published in January of 2013.
- Why is the number of "returning users" not based on a cohort's retention curve? / Why don't you take retention into account when calculating DAU?
Retention is taken into account in this model; the retention curves are used to calculate user lifetime, which is then multiplied by ARPDAU to get a LTV estimate. The reason that DAU isn't calculated based on cohort retention is that doing so in-cell in a spreadsheet would cause it to be unusably slow (one year's worth of DAU calculations would require 365 x 365 = 133,255 cells, as each day would need to aggregate all returning users from days 1 through N-1, calculated based on whatever point on the retention curve they are at for their age).
As a workaround, this model simply has each user playing each day from registration through the end of their lifetime (eg. if a cohort's retention profile with Day 1 retention of 40% produces an estimated lifetime of 30 days, then each of those users will be represented in Returning Users for Day 1 - 30 of their cohort, as opposed to 100 on Day 1 as new users, 40 on Day 2 as returning users, 20 on Day 3 as returning users, etc.). Basically, retention is used to calculate lifetime and the cohort is represented completely for that many days, starting with the day they are onboarded, rather than represented across a decaying retention curve.
It should be noted that this has no impact on calculated revenue across the lifetime of the game, just the distribution of revenue (eg. the total will be the same as it would have been had DAU been calculated via the retention curve and not front-loaded by cohort by the length of the calculated lifetime).
- I plugged real metrics from my game into your model and it didn't produce the same revenue numbers we see in our analytics dashboard. Why?
This is a model; no model is perfect.
- What should I use this model for?
This model is useful for evaluating how the interplay of retention, monetization, and virality can affect the total, aggregate revenue outcome for a game. As such, this model is a useful tool in making decisions around game design and marketing strategy with respect to those changes' impact on overall revenue. This model cannot credibly answer a question like, "If my retention is 40% on Day 1, how much money will my game make?". It does a better job of answering questions like, "If I increase virality, but in doing so decrease Day 1 retention, will my game make more money than it otherwise would have?".
- Can I contact you with a question I have about the model?
Sure, provided you have read this FAQ and the below text carefully beforehand.
As free-to-play transitions from an emerging, vaguely defined abstraction into the dominant business model on mobile, developers will face an increasing need to understand the concept from an analytical standpoint. I spent some time this weekend thinking about how free-to-play games generate revenue: what mechanics converge to deliver positive ROI on the development of a free-to-play title?
My goal in undertaking this model was to build a tool that developers could use to make data-driven business decisions about the economic viability of a free-to-play game based on the fundamentals of the free-to-play business model. My intention was to quantify the components of the free-to-play business model that previously had been estimated in the earliest stages of game development through intuition.
Speaking broadly, four fundamental product factors contribute, directly and indirectly, to a free-to-play game's revenue stream:
- Virality: The extent to which users are acquired free-of-charge as a result of being introduced to the game by an existing player;
- Retention: The extent to which users return to the game;
- Monetization: The extent to which users spend money in the game;
- Organic Growth: The extent to which new users are acquired free-of-charge as a result of discovering the game without any discernible prior connection to it.
One exogenous factor detracts from the revenue stream:
- User Acquisition: The price and volume (set by the market) of users that can be acquired into the game from mobile advertising networks and other paid sources.
These factors are fairly concrete, so I set out to model all aspects of a free-to-play game within the context created by the above parameters. These are the estimates the model outputs:
- Virality coefficient (k-factor)
- Average player lifetime
- Average Daily Net Revenue
Given some (admittedly difficult to define) constants assumed about all free-to-play games -- the universal monetization curve, viral growth, and a static retention profile pattern – this model derives the above metrics.
The model is comprised of 8 worksheets:
This is the tab in which all user inputs are set. Edit all blue fields with either estimates or historical actuals (eg, number of users acquired per day, daily organic installs, etc.). This worksheet drives the rest of the model and is the only worksheet that requires editing. Of course, every field in the model is editable, but fields with a black font are model outputs and editing them may cause problems.
This worksheet presents four visualizations which summarize the model’s output: the Monetization Curve (what percentage of players achieve which final Lifetime Customer Value levels), Virality Curve (how virality is distributed), DAU (new and returning users by day), and Daily Net Revenue.
This worksheet contains revenue estimates by day, broken down by player monetization category (I used the following category names, but the names as well as IAP spend thresholds can be changed in the Assumptions tab: Minnow, Fish, Dolphin, Whale). Daily User Acquisition expense is included in this tab, gathered from the Assumptions tab.
This tab estimates the number of players acquired virally, by day, based on calculations made in the Viral Growth tab. Manual adjustments to viral new users should be made in the cells at row 376 – if, for instance, you wanted to indicate that 5000 players entered the game through viral channels on day eight, you would edit cell K376.
This worksheet lists the retention profiles that can be selected from the dropdown menu on the Assumptions tab. You can specify your own retention profiles by editing the fields at the bottom named Custom 1, Custom 2, and Custom 3. Once data has been added to a Custom row, select that row from the dropdown menu in the Assumptions tab.
This worksheet calculates the percentage increase in the user base achieved through virality each day, based on the inputs from the assumption tab. This worksheet does not require editing; if actual virality numbers are available to be input for historical periods (eg you want to input historical, actual virality numbers to test the model against reality), this should be done in the Virality worksheet.
This worksheet makes the most wild assumptions about the universality of the free-to-play model. Here I have constructed a monetization curve based on a decreasing power law (the curve is graphed on the Summary worksheet). Lifetime Value Threshold (in cents) is designated as the independent variable; percentage of user base (that churn out at that threshold) is designated as the dependent variable. Given a user-provided conversion metric (in the assumptions tab; what percentage of the total user base converts at the minimum In-App Purchase value) and minimum IAP value, I construct the curve by deriving a negative exponent. What the graph depicts is the percentage of the total user base (Y axis) that churns out of the game at a given LCV (X axis). The area of this curve thus represents sum total Lifetime Customer Value, or total revenue, on a percentage basis.
To get started with the model, I’d recommend playing around with the assumptions and observing which variables affect revenue most profoundly. Since this is a model, the outputs calculated are fiction. But hopefully this model serves as a reasonable framework for making business decisions about free-to-play gaming development and allows for increased reliance on data in designing free-to-play gaming mechanics.