A Spreadsheet Model for Viral Growth (2)

In the previous post I introduced a spreadsheet that can model viral growth. In this post I want to introduce two additional concepts into the model, to make it conform better to what we see in the real world. These concepts are Total Available Market and Retention Rate:

Total Available Market (Carrying Capacity)

Funny things happen with viral models when the Viral Coefficient is greater than 1 and the Viral Cycle Time is short enough. The model will 'hockey stick' to very high numbers because of the exponential growth that is embedded in the model.

Take a look what happens with the model from the previous post if the Coefficient is set to “2” and the Cycle Time to “1”:

Freemium Model (03)According to the model you will have 2 million users by the end of week 10 and pass 1 billion in week 19. Before you panic and start recruiting additional support staff, let us introduce the concept of Total Available Market (TAM) into the model.

Assume our product is a tool that will be used by Java Developers. Our estimate for the total worldwide number of such developers is about 10 million. It is clear that the total number of users of our tool will never exceed 10 million, and the viral effects will slow down as the number of users grows to a meaningful percentage of that 10 million.

The way to include this in the model is to multiply the number of acquired new users with the TAMFactor, which is defined as:

TAMFactor = (1 – TCU/TAM)
(TCU means “Total Current Users” and
TAM means “Total Available Market”)

TCU/TAM will be close to zero in the early phase of the viral cycle, and therefore the TAMFactor will not slow down things too much. But by the time TCU is 25% of TAM, the factor slows the viral spreading down to 75% of the normal growth.

Here is the updated model:

Freemium Model (04)You can see the formula for cell H26 (=H$25*(1-G$27/$B$22)) and its impact: it slows the number of virally acquired users from 63,636 to 63,237, a modest slowing. But as you can see on the chart below, that changes by week 13: in that week the unadjusted number shoots off the chart, and in the adjusted chart the trend flattens and never gets above 9 million.

Freemium Model (05)

Retention Rate

In any community of users, some percentage will drop out. Users may change jobs, change technology, loose interest, become dissatisfied, merge with other users, be promoted, demoted, whatever the case may be. In this model we will define the Retention Rate as the percentage of users that continues to be a user a year later. In the software industry 80% is generally seen as a good number.

In the spreadsheet model we convert the annual Retention Rate into a weekly Attrition Rate with the formula:

Weekly Attrition Rate = (1 -(Annual Retention Rate^(1/52))

Here is the updated model with the attrition calculated for cell H28:
Freemium Model (06)The updated chart shows that later in the cycle, growth declines and the ongoing attrition starts driving the overall number of users down:

Freemium Model (07)Here is the Excel spreadsheet, so you can review the model and experiment with the values of the key variables to learn to understand their impact.

TAM and Retention Excel Model (Click to download)

In my next post about this topic, I will expand the spreadsheet model to incorporate a free product that depends on viral distribution to succeed and a commercial product that will be purchased by a subset of the users of the free product. This is referred to as the Freemium model. It will model the conversion from free product to evaluation of the premium product, conversion of evaluation to purchase and the impact of evaluation time.

Stay tuned.

4 thoughts on “A Spreadsheet Model for Viral Growth (2)”

  1. Mr. de Visser:

    I have just started my career in Marketing and am being introduced to these concepts of social media and viral growth. I find it all fascinating and your viral growth model has been of exceptional help. I do have a questions re: the TAM and Retention Model, though. In the preceding example, you used a cycle time of 1 week and converted the annual retention rate to a weekly attrition rate. If the cycle time were changed to 2 weeks, would you then convert the annual retention rate to a bi-weekly attrition rate, using the formula (1 -(Annual Retention Rate^(1/26)) rather than (1 -(Annual Retention Rate^(1/52))?

    Thanks so much!

  2. Bianca,

    It can be a bit of a mind twister indeed, but the retention rate is independent of the cycle time. The coefficient and cycle time determine how many and how fast new users come on board. Once on board, the retention rate determines how long you keep them.

    In many companies I have been involved with, the two jobs are in fact carried out by independent teams.

  3. Mr. de Visser:

    If carrying out the model over a period of three years, is it wrong to do the projections for year 1 and use the figure generated at week 52 as the seed amount for year 2?

  4. Hi,

    Thank you for this very useful model. I was investigating the subject a bit and found an edit of the Chen model that accounts for variable “churn” rate (http://bit.ly/9hjaPE).

    It would most certainly be interesting if this feature could somehow be implemented in your next iteration. The author of the modified Chen doesn't appear to justify the variable churn rate, but it does sound logical in many ways: a) as you gain loyal customers over time, they may send more meaningful invites to a better targeted audience, b) as you learn from experience and tweak your content or service you lower attrition, c) as customers find more of their friends using a service, they are more reluctant to leave. There are probably a dozen more explanations that look good on paper, but would they make sense in the Excel spreadsheet?

    I don't want to say Survival Analysis, but that immediately springs into the [very] back of my mind from college stats class.

    Thank you for the model and I'll be looking forward for your next version!

Leave a Reply

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