A Tale of Two Charts
Let’s say you operate a business that relies heavily on “word of mouth” – customers recommending your product/service to their friends and colleagues. Or at least, you THINK it relies heavily on that sort of thing.
You need to decide how much to spend on traditional advertising – to supplement the social/viral marketing that your customers do on your behalf. Take a look at each of these two charts – the captions for each attempt to capture the knee-jerk conclusions you might draw:
“Advertising? We Don’t Need No Stinking Advertising!
That is SO Yesterday! We’re Viral Baby!”
“All These Youngsters and Their ‘Viral This’ and ‘Social Media That’ – That’s All Just Fancy Excuses to Be Lazy – You Clearly Need to BRING Your Message to the Customer”
If chart 1 reflected reality, you may opt to spend very little on traditional advertising. But in a chart 2 world, you’d be silly to rely on viral growth. But which one (if either of them) describes your situation?
Back in October, Rahul Vohra (CEO of Rapportive) wrote a two-part blog series on this topic, posted here on LinkedIn. I took a note, at the time, to revisit his work and “convert” it to PowerPivot.
It’s a very different kind of problem from what I normally do in PowerPivot – this isn’t about analyzing data I already have, but about calculating future outcomes based on a handful of parameters. And that leads to some different kinds of thinking, as you will see.
Two Primary Inputs: Initial Customer Base, and Viral Factor
The Simplest Version of Rahul’s Original
Spreadsheet Takes These Two Inputs
Initial Customers – the number of customers you have right now.
Viral Factor – the percentage change that one of your existing customers “recruits” another customer in a given amount of time (which will be one month, at least in this example).
Calculating Growth in Traditional Excel
This is quite a straightforward thing to calculate in the normal Excel grid:
In Month 1, your Users (Customers) are just equal to the Initial Customers input, which is 5,000 at the moment. Then you multiply that by 0.2 (our Viral Factor input) to get 1,000. Then you and add 5,000 + 1,000 to get next month’s customer base of 6,000. Now repeat that pattern as far down the grid as you’d like.
I colored the cells green, grey, and blue for a reason – each grey cell has exactly the same formula as every other grey cell, just filled down. Same is true for the blue cells. But the green cell is different from the grey cells – it is NOT the addition of the two cells in the row above it (like all of the grey cells). It’s the “anchor” for the whole process, and merely references the input cell.
When we convert this to PowerPivot, that “anchor” is a tricky little wrinkle, so remember that for later.
Setting the Stage
Three Single-Column PowerPivot Tables: Two That I Use as Disconnected
Input/Param Slicers, One that I Use for Month Number on Rows of My Pivot
I start with three single-column tables that I pasted into PowerPivot in order to give me the pivot depicted above. (I’m skipping that step here but you can download the workbook at the end of the post to see what I did).
Next step, as always with disconnected parameter slicers, is to write “harvester” measures – measures that return whatever the user has selected on each slicer:
Two Parameter “Harvester” Measures (Formulas Below)
[ViralFactor]=
MAX(‘ViralTable'[Viral Factor])
[Initial Customers]=
MAX(‘CustTable'[InitialCustomers])
OK, now I need a [Total Customers] measure – one that respects those parameters.
Lions and Tigers and Circular References Oh My!
My first instinct was to write three measures along the lines of:
[Total Customers] = simple arithmetic
[New Customers] = [Total Customers] * [ViralFactor]
[Next Month Customers] =
[Total Customers] + [New Customers]
But what is the “simple arithmetic” for [Total Customers]?
It actually wouldn’t be that simple. It would be something like:
IF(I am in month 1,
[Initial Customers],
go back and get [Next Month Customers] from LAST month
)
And I would go back and fetch [Next Month Customers] from the prior month using the GFITW. Something along the lines of:
CALCULATE([Next Month Customers], go back 1 month with GFITW)
Do you see the problem? [Next Month Customers] and [Total Customers] both reference each other!
Circular Reference: This is Not Legal
So those formulas will yield an error when you try to use them. That doesn’t mean I didn’t try, heh heh.
So How DO You Do It? I Used… The POWER
This post is running a bit long already, so I’m going to have to revisit it on Tuesday. There’s a lot of interesting and valuable stuff to cover here I think.
But I won’t leave you hanging completely. Briefly, here are the two measures I wrote:
[Continuous Viral Factor] =
POWER(1+[ViralFactor], MAX(Months[MonthNum])-1)
[Total Cust – Ongoing Viral Only] =
[Initial Customers]*[Continuous Viral Factor]
I think that’s my first-ever use of the POWER() function in a measure! Exciting times indeed! 
And those seem to work:
For Next Time
There’s a lot left to explore in the next post, including:
- Explaining the measures I am using above.
- Even these simple results do NOT match Rahul’s yet, but that’s not because of a formula mistake, but because of differing assumptions of how “viral spreading” operates in the real world. So I will explore Rahul’s assumption as well in the next post.
- Factoring in the impact of “direct” advertising as a supplement to (or replacement for?) viral spreading
Download the Workbook
The workbook contains everything above as well as a lot of what I’m going to cover on Tuesday.
