Why am I doing this in PowerPivot? Primarily as a challenge.
This is a question I should have answered before I even started down this road.
To be honest, I did it primarily as a challenge – to stretch my brain a little bit. If I were faced with this exact same task in my daily work, undoubtedly I would just use normal Excel formulas. In some ways, this modeling exercise has been a deliberate misuse of PowerPivot. A handful of parameters with no source data whatsoever – this is NOT what the PowerPivot engine was built for, which explains why the PowerPivot solution is actually significantly more difficult than the Excel solution.
“So you’ve been deliberately wasting our time??”
No, I do think there is real value in this exercise, for two reasons:
- Brain-stretching with new techniques always comes in handy later. For instance, on the first post Sergey commented that he’d been thinking about loan amortization measures and this could be applied to that.
- I can see this technique being added, as a supplement, to a broader PowerPivot model. For instance, a model containing lots of real customer data over time, and then a [Projected Customers] measure that forecasts future customer populations based on various assumptions and/or marketing investments.
So with that in mind, here it is: the final installment of viral/marketing modeling in PowerPivot.
Supplementing Viral Growth with Real Advertising
The last part of Rahul’s model that I will address is this: what if we also spend money on advertising? How much do we have to spend to make a difference?
There are two additional variables to add:
- How many advertising “impressions” per month do I buy – the number of times potential customers see my ads.
- What’s my conversion rate – what percentage of the impressions lead to people purchasing or signing up?
I create those using the normal disconnected slicers trick:
[Conversions per Month]=
[Impressions per Month] * [Conversion Rate]
(I am skipping some steps here – download the workbook at the end of the post to see them).
Accounting for this in a “new customers – hybrid” measure
OK, now I can write a new measure that accounts for ALL new customers in a given month – virally-won customers plus those won over by advertising:
[New Customers Diminishing – Hybrid] =
[Initial Customers]*[Diminishing Viral Factor] +
[Conversions Per Month] +
IF(MAX(Months[MonthNum])>1,
[Conversions Per Month]*[ViralFactor]
,0
)
Recall that the non-hybrid [New Customers Diminishing] from part two was defined as:
[Initial Customers]*[Diminishing Viral Factor]
Which is the identical to the first line of the new hybrid measure above.
So in English, our new Hybrid measure is:
“take the number of new customers we get from diminishing viral spreading, add to that the new customers we get from advertising, and then, if we’re not in month 1, add the customers recruited by last month’s advertising-converted customers.”
Hybrid Total Customers
Continuing the pattern:
[Total Cust – Diminishing Viral plus Advertising (Hybrid)] =
CALCULATE(SUMX(Months, [New Customers Diminishing – Hybrid]),
ALL(Months),
FILTER(ALL(Months),
Months[MonthNum]<=MAX(Months[MonthNum])
)
)
+ [Initial Customers]
This is the same formula as in part two – it just has our Hybrid measure for new customers substituted in.
Displaying for Comparison
I can now place both measures – the Viral Only and the Hybrid – on a single chart for comparison while I manipulate variables via slicers.
Here are a few combinations and the results of each:
Start with 5,000 Customers and a 10% Viral Recruitment Factor. 500 Ad Impressions per Month and a 5% Conversion Rate on those Ads Doesn’t Make a Huge Difference.
But Reduce Initial Customers to 1,000 and Suddenly, Advertising is Quite Necessary!
Revert to 5k Initial Customers But Increase Impressions per Month from 500 to 2k,
and Again, Advertising Earns its Keep.
Rather than post more screenshots, I will post a more attractive version of this spreadsheet to the demo site so you can just try it out. Check back for a link tomorrow.
Last Observation
The slope of the red line in the graph above is NOT due to advertising conversions alone!
To illustrate, at 2k impressions per month and 5% conversion rate, that means I am adding 100 customers per month from pure advertising.
But the month to month difference in customer count is actually 110 in that chart – the 100 advertising customers plus the 10 customers virally recruited by last month’s advertising customers.
So if you have a better viral factor than 10%, that will boost the blue line for sure, but it will boost the red line even more. An observation that somehow strikes me as both intuitive and non-obvious at the same time. This is one of those moments where I learned something about the world, and I really like those moments.
