Counting Overlapping/Shared Twitter, Facebook, Instagram, etc. Followers

Post by Rob Collie

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.

Two Lists of Twitter Followers:  How Do We Find the Overlap Using Power Pivot / Power BI / DAX?

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.)

Power Query, aka Power BI Data Import

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

Read the Rest

A Simple Measure for Projections with Act! CRM

Guest Post by Vivek Gargav

Intro from Rob:  continuing the series of guest posts that got temporarily shelved, today we have one from Vivek Gargav.  This post captures a trend that I monitor quite closely:  people who are heavy users of ERP, CRM, and Accounting systems are increasingly realizing that Power Pivot provides FAR superior reporting and analysis, against those systems, than what those systems provide “in the box.”

It makes perfect sense of course.  If you are a software company who produces CRM software, your specialty is…  CRM software!  Not reporting and analysis software.  Furthermore, the needs of your customers couldn’t be more divergent – whatever you build “in the box” is inevitably going to be “lowest common denominator” stuff – borderline relevant to everyone, but not terribly insightful for anyone.

Enter Power Pivot.  And people like Vivek Gargav.


By Vivek Gargav (twitter | website)

Act! CRM:  Begging for some Power Pivot Analysis

ACT! – a Leading CRM System.

Act! is one of the leading Contact Management/CRM products used by many SMBs worldwide within organisations that in some cases don’t have a formal IT department or resource internally. For many, the sole point for reporting on their organisational knowledge is via the weak and complex native Act! reporter, some even use the Dashboard component to extend their reporting capabilities.

Unfortunately neither of these routes provide the depth of reporting requirements that most businesses need (especially aggregate data) and so Act! users traditionally have had to look at 3rd party add-ons including the old stalwart of Crystal Reports.

Read the Rest

Modeling Viral and Marketing Growth, Part 3 of 3

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.

Read the Rest

Modeling Viral and Marketing Growth, Part Two

Picking up from last week’s post, the first thing I want to show is that I kinda cheated last time.  To see what I mean, let’s look at Rahul’s original chart:

Viral Marketing Growth in PowerPivot:  Customers Flatten Out Over Time

In Rahul’s Viral Model, Total Customers “Goes Flat” Quickly

In Rahul’s model, if we start With 5,000 initial customers and a viral factor of 0.2, we end up with 6,250 customers and we never get any more!

But in my model from last week, if I use 5,000 and 0.2, customers keep piling up exponentially:

Exponential Ongoing Viral Growth in PowerPivot

In My Model from Last Week, Customers Never Go Flat –
They Just Keep Growing Exponentially

So why the difference?

Read the Rest

New Customers Per Day Generalized to “New Customers per Month,” etc.

A Generalized New Customers (or unique visitors) in Time Period - per Month, Year, Etc. in PowerPivot

A Generalized “New Customers in Time Period” Solution, Inspired by Tuesday’s Post

David Hager’s post on Tuesday really planted a seed in my brain.  And then a comment on that post from Charlie got me thinking further.

How can we extend the “New Customers per Day” concept to become “New Customers in <Any Period of Time>?”  New Customers per Month for instance.

Read the Rest