skip to Main Content

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:

  1. 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.
  2. 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: 

  1. How many advertising “impressions” per month do I buy – the number of times potential customers see my ads.
  2. 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:

image

[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:

image

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.

image

But Reduce Initial Customers to 1,000 and Suddenly, Advertising is Quite Necessary!

image

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.

Download the Workbook

Click here to Download

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has One Comment
  1. Is there a way to calculate the sum of future customers? I’m having troubles writing a similar measure. So basically sum of the total customers for each measure from months 2-36, then 3-36, then 4-46, etc.

Leave a Comment or Question