Social Application/Game Growth Model Spreadsheet

by Jon on June 11, 2010

I’ve put together an Excel spreadsheet to help model growth curves within social games and social applications. I thought I’d put it into the wild and let it benefit from the “wisdom of the crowds” to make it even better.

Because social games and applications on social networks like Facebook fluctuate daily (if not more often!) the model breaks every individual day into its own cohort. This is because each individual cohort will experience its own growth (due to the new users within the cohort introducing the application to their friends) and decay (as individuals in the cohort stop playing). These growth and decay curves happen along a normal distribution. Because of the complexity of this model, it means that the spreadsheet is 365 columns wide and over a thousand rows–so on some computers, it will load a bit slowly.

Here are the main input variables you can manipulate:

k-Factor, which is the rate of growth for a social application based on how man people, on average, a product will spread to from one other person (the concept is drawn from infection rates within epidemiology).
The rate of spread that the k-Factor reveals isn’t enough on its own; you also need to know how fast it is occurring, so the model also allows you to manipulate the mean time to spread to another person.
Likewise, you can manipulate the mean time to remain a customer, which is also central to measuring how many total active customers you have.
There are other variables you can play with, such as revenue/user, reinvestment of revenue in customer acquisition costs (i.e., advertising),

What this doesn’t attempt to do is establish a ratio between daily active users (DAU) and monthly active users (MAU). Clearly, that’s an important set of information for determining the engagement level of an application or game’s customers, but it’s probably dealt with better in a separate model. For purposes of this, you can think of the DAU/MAU ratio as a determinant of the assumptions used for revenue and mean time to remain a customer as used in this model.

Comments are welcome and appreciated. If anyone has helpful criticism, feedback–or even if you want to modify it and share your evolved version with me, I would be happy to post updates here.

Download the spreadsheet:

SocialAppVirality_v12.zip

(Other helpful spreadsheets, for those interested: The Excel Nexus)

Thank you for reading this article. Please follow me on Twitter to hear more from me on innovation, games and entrepreneurship. If you'd like to learn how games can transform your business, also check out my book, Game On: Energize Your Business with Social Media Games.

{ 5 trackbacks }

Social Gaming Roundup: AdParlor, Payments, Ubisoft and More: June 12, 2010 at 12:31 am
Facebook DAU and MAU: what they tell you (and what they don’t) « Insight analysis: July 21, 2010 at 9:47 am
Les outils de mesures dans les social games « Kiiwiigames – Chroniques des social, casual, serious et advergames: January 17, 2011 at 2:52 pm
Quora: March 1, 2011 at 12:16 pm
Top 6 Stats for a Social App | Michael Gugel: July 26, 2011 at 11:15 pm

{ 12 comments… read them below or add one }

Dan Goldman June 13, 2010 at 3:25 pm: I may very well be wrong (quite possibly, I did not understand how to use this spreadsheet) but, it seems to me like the numbers are very erratic or at least extremely sensitive and I find it very difficult to really understand what is going on there.
Jon June 14, 2010 at 9:49 am: Because it is broken down into daily cohorts, minor changes in k-Factor or mean-time-to-spread will have a profound effect, similar to compound interest.
Heather Stark June 28, 2010 at 9:20 am: I am studying this question too. One factor that seems to be critical to the outcome of a modelling exercise is your view on the nature of the probability distribution of k (where k = N links for a node). (Am reading Barabasi at the moment, as you might be able to guess..! Also reading Duncan Watts, Lambert from Sloane/MIT, Newman from Santa Fe…) Does your model parameterise the nature of the probability distribution function of k in any way? (Sorry! too busy scratching my head to download it and zap around with it right now…..)
Jon June 28, 2010 at 1:57 pm: @Heather, my model uses a normal distribution for the actual probability formula. Different curves would certainly have a significant impact. Aside from the mean value of k and and the mean length of time to realize k, the number of standard deviations to use in the probability formula is also relevant (although their impact seems to have more to do with the smoothness of the curve, rather than the ultimate results).
Heather Stark July 4, 2010 at 3:18 am: Hi, Jon – Re-reading, I realise now you actually said that in your post. Duh. Sorry. Anyhows…it could be interesting to pop in a parameterised model of a scale-free network, where P(k) follows a power law. There’s at least some evidence this is could be a better empirical fit to the actuals (c.f. Barabasi).
Heather Stark July 4, 2010 at 3:30 am: Another thought: if you have managed to coax out any interesting (or surprising) dependencies between model inputs and model outputs, it might be interesting to summarise them graphically, using a series of big thumbnails (like you have in your post), separated by headlines, to show them off.

Another question: when I even so much as think about modelling this stuff in excel, I get a huge pain in my neck. I wonder if you ( or any of your readers) would have any ideas about alternative tools?
Jon July 4, 2010 at 9:46 am: I used Excel because it is the most accessible format, but there are much better tools that one could use if broad distribution isn’t a concern. Mathematica would be perfect.
Lior December 20, 2010 at 12:50 pm: Hi Jon,

Amazing excel!
I have some questions about some of the Vars’ there.
I’m looking to use your excel in my business and I would like to than upload is as a case-study.

Can you please email me ?
paul January 2, 2011 at 12:13 am: Thanks for the work, Jon, but I can’t seem to open your excel file. It locks up the app when I try. I also tried using Google Docs to convert it, but Google throws an error on it as well. Maybe it got corrupted when you zipped?
Spacekat January 13, 2011 at 5:25 pm: Jon – thanks so much for posting this! You’re really generous to share so much hard work.
Nigel Legg February 21, 2011 at 12:18 pm: Finally got this open. What I notice is that if you increase the revenue per user, this increases the starting number of users to 13,929, on day 0, which does not seem right to me. hmmm.
Kadank October 7, 2011 at 4:45 am: Hi Jon,

Thanks for this very nice material, Nicholas Lovell from Gamesbrief just released a spreadsheet focusing on monetization which combines very well with yours in order to have a model for the whole ARM funnel .
I have a question about your spreadsheet. On the viral gain of customers, you use the k factor, from two days before, is there any reason for that ? on your spreadsheet it does not change anything as the k-factor is always 1.1 but when we include diminishing returns, it changes quite a lot.