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 model if the Coefficient is set to “2″ and the Cycle Time to “1″:

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:

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.

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:
The updated chart shows that later in the cycle, growth declines and the ongoing attrition starts driving the overall number of users down:

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.