A Spreadsheet Model for Viral Growth

I worked with two companies in the past month that needed a way to model viral adoption as part of their effort to understand the impact of a strategic decision to deliver part or all of their technology under an opensource license. Having such model enables them to understand the critical variables and success-factors, set targets for them and then measure as they embark on their strategies.

I set out to make a generic model that can at once be used at both companies. Hoping it may be of use to others as well, I will publish it here. I had much benefit from articles published by David Skok and Andrew Chen and try to use their terms and insights as much as possible.

The basic assumption for viral growth is that every new user of a product becomes an ambassador for that product and will convert others to start using that product as well. There are two variables that allow you to create a model for viral growth:

  1. Viral Coefficient: the total number of new users generated by an existing user
  2. Viral Cycle Time: the amount of time it takes before all these new users have been generated

Viral growth can happen in many ways, from word-of-mouth, to active solicitation and anything in between. This model makes no assumptions about that, it just measures the results. Here is an example in which the unit of time is set in weeks, the starting number of users is 1000, the viral coefficient is .3 and the cycle time is 3 weeks:

Freemium Model (01)

The model reflects that the viral impact starts in the first period (week) after a user has started using a product and lasts throughout the duration of the cycle. If new users join in the first week, they start working their viral magic in the week after that. So, the 1,000 users in week zero will deliver 300 new users (coefficient is .3) over 3 weeks (cycle time is 3), or 100 new users in week 1, 2 and 3. The 100 new users in week 1 deliver 0.3*100=30 new users,  10 in the weeks 2, 3 and 4. The 110 new users in week 2 deliver 33 more users, and so forth.

As the coefficient is less than 1 in this case, the number of new users per week trends to zero over time. If the coefficient is 1 or larger, the viral engine will keep on giving.

The pattern for the number of new users per week (in C15:L15) is that each number is equal to the sum of the numbers of the previous three weeks, multiplied with the viral coefficient and then divided by the viral cycle time. In the next image you can see how that pattern is turned into a spreadsheet formula:

Freemium Model (02)

The formula in cell H24 is:

=SUMIF($B23:G23,”>=”&(H23-$B$20),$B24:G24)*$B$19/$B$20

This formula defines the range of values that can be summarized as *all* previous weeks ($B24:G24), but only includes the numbers in the weeks for which the week number is 3 or larger (columns E, F and G).

Viral Formula Excel Model (Click to download)

You can download the Excel spreadsheet with this model (see above). I have also published the model as a Google Spreadsheet. Take a look and play with the values for Viral Coefficient and Viral Cycle Time, to see how they impact the growth of your user base. To inspect the formulas, select a cell and then hover the mouse over the formula area on the right bottom of the browser window.

In the next entries I will add further metrics to this model that have an impact on viral distribution.

3 thoughts on “A Spreadsheet Model for Viral Growth”

  1. This is awesome. I deal a lot with social media and try to explain the benefits of viral communication to different Silicon Valley companies. I would love to throw a chart like this in there.

    There's so much potential with viral adoption. It's exciting.

  2. Hi Craig,

    Happy that this is useful to you. Feel free to use these charts, that is why they are here.

    Mark

  3. Cool Mark,

    Since Jurvetson defined this formula way back it’s been my go-to formula for calculating viral growth. When I build a viral growth product, I code my product to measure the two key factors you mention (fan out, and propagation delay), and then plug those values into the spreadsheet to see when my tipping point is going to happen. Fascinating to see the effect that small changes in these two factors make.

    Often these two factors can be improved by making the right feature changes. With real user measurement in place, I can validate whether or not my feature changes improve these factors, and then recalculate when my tipping point will happen. Then you can explode in 6 months as opposed to waiting 18 months.

    David

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>