From Last Week’s Client Work

Last week a client asked us to solve a somewhat unusual problem: given any two lists of Twitter followers, tell us how many followers “overlap” between the two lists.

How Many of Han Solo’s Followers Also Follow Leia Organa, and Vice Versa?(Randomly-generated Twitter handles are funny. I particularly like “@Gommo” and “@Xxfok”)

Loading the Data: Using Power Query

Let’s use Power Query to perform the import this time, both because we’re using PQ a lot more around here now that we have Power Update, and because we’re gonna need PQ for the more complex steps later.

Note that all of the steps below are performed using Excel 2013. (I find Power Query to be a bit too clumsy in Excel 2010.)

Importing from a Table Using Power Query: Step 1(Unchecked “has headers” because of the “Han Solo’s Followers” Row)

What is “Net Promoter Score?”

Fundamentally, it’s a measure of how many of your customers love you, minus how many of them dislike you. Hence the name – Net Promoter Score.

WARNING: I am personally no expert here. I am doing my usual thing: take a small amount of knowledge and wield it like a battle axe. I was helping a client today (Monday) with this, and am writing about it a mere three hours later. But I figure there are lots of people out there who need to do this sort of thing, and THEY get what it all means. So allow me to share how EASY these calcs are in Power Pivot.

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.

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.