A comprehensive free-to-play game model: revenue, DAU, virality, and retention (spreadsheet included)

images

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

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:

  • DAU
  • 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:

Assumptions

assumptions

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.

Summary

summary

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.

Revenue Estimates

revenue_estimate

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.

Virality

virality

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.

Retention Profiles

retention_profile

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.

Virality Growth

virality_growth

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.

Monetization Curve

monetization_curve

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.

My appreciation goes out to Joakim Achrén and Raul Aliaga Diaz for looking at early versions of this model and providing me with their feedback.

  • Gary DePew

    Thank you for this amazing tool. I've already learned a great deal from playing with the assumptions as you suggested. One question; What is happening on the Assumptions tab, cell ZW29? It seems that the total of "Daily New Users, Acquired" drops to zero just about one month before the end of the cycle. Thanks again... I love your blog.

  • Gary DePew

    Thank you for this amazing tool. I've already learned a great deal from playing with the assumptions as you suggested. One question; What is happening on the Assumptions tab, cell ZW29? It seems that the total of "Daily New Users, Acquired" drops to zero just about one month before the end of the cycle. Thanks again... I love your blog.

© 2012-2013 Mobile Dev Memo