The vast confidence intervals of Net Promoter Score: why is it so hard to explain NPS movements over time.
“Our Net Promoter Score has gone down from 25 to 12.9, what a disaster!!!”. The CEO of ACME Corporation is getting nervous and nervous, screaming around and try to find an excellent reason to explain the change of that figure at the next shareholders meeting. Let me explain why the difference of the score is not a disaster and why – once again – you should report on the 11 NPS scale using the usual statistical suspects: mean, median, mode,… and lots of other mathematical good stuff, and not on the score itself …unless you have solid confidence intervals. (!)
Let’s start from the beginning, a single question measures the NPS: “How likely are you to recommend our product to a friend or colleague?”. An 11 points scale from 0 (“No absolutely!”) to 10 (“Yes, definitely!”) captures the answer. The second question follows the first, and it is an open-ended question: “Why?”. The idea is beautiful and straightforward. I think the concept of simplicity and not testing a hypothesis is gorgeous and elegant. It is also simple enough to be explained to whoever in your organisation. Excellent!
The real issue starts when you try to measure NPS over time, and the movements become hard to understand or simply “unexplainable.” Let’s go back to our initial question: why we lost 12.1 points in our NPS? It looks like a disaster! Keep calm and loves statistics!
NPS is an 11 scale, but the final score comes out of 3 values: detractors (D), passives (PS), and promoters (PM).
The transformation of the 11 points scale to 3: <=6 D, >=7 and <=8 are PS , and >=9 PM
The NPS Score formula is:
Where n is the population observed. Or equivalently:
NPS Score = (PM/n - D/n)*100
It is clear what our formula does: count. It counts the Promoters; it counts the Detractors; it calculates the whole population (n), and apply some basic mathematics. In particular, it calculates the proportion of PM and D in our ‘n’ population.
Since what we are doing with Promoters and Detractors is counting, why not flag them directly with a boolean variable and count them summing the boolean variable? We can flag 0 or 1 if a respondent is a detractor, passive, or promoters. We can do it in excel in a straightforward way, let’s create a boolean for Promoters. It is not more than the formula:
In plain text: if the cell contains a number equal or greater than nine flags 1, otherwise flags 0)
We can do the same for DETRACTORS and PROMOTERS, but since our NPS Score formula is:
NPS = count(PR)/n - count(D)/n
and, as you can see, we count promoters, we count detractors, and we divide those two numbers for the whole population (n), then what we are doing is working with means:
Net Promoter Score = mean(Count Promoters) - mean(Count Detractors)
The original formula doesn’t consider the passives. So when we count our population, instead to use a boolean flag as before (0,1), we can assign three values: 1, 0, and -1 and create an “NPS Population Count”. basically we count each promoter (+1), we subtract each detractor (-1), and we don’t touch the passives (0).
Our Excel formula becomes:
=IF(nps_cell >= 9, 1, IF(A2 <= 6, -1, 0))
Where nps_cell is the cell where you store the NPS vote for each customer, this recoding is precisely the one I explained before: turn NPS votes to 1, 0, or -1.
Let’s go back to our original question. Our score moved from 25 to 12.9, and our CEO is getting crazy. Our VP of Customer Experience is trying like hell explaining the reason a KPI moved and what are the reasons behind. All the people are driving crazy around try to find an explanation. In a dark corner of the room, an old data scientist is extraordinarily calm and says: “On a statistical point of view there is absolutely no difference between the two scores. The second score moves within the wide confidence intervals of Net Promoter Scores, and we cannot consider it as a significant movement.” Silence in the room, finally!
Let’s go back to our ACME Corporation NPS calculation; let’s have a look at the NPS data of that Company:
- We have 15 customers with answer 9 to NPS question.
- We have 5 customers with answer 7 to the same question.
- We have 11 customers with answer 0 again to the same question.
In scholastic NPS calculation we have the following computation:
n = 32 Promoters = 15/32 = 0.48387 Detractors = 11/32 = 0.354838 0.48387 - 0.34483 = 0.129 = NPS Score 12.9
Now, let’s do an average of 15 times 1, 11 times -1, and 6 times 0. I did it in Excel, and it comes out the same result: 0.129. We are working with a mean thus we can apply our statistic knowledge. For instance, we can calculate the standard deviation.
Standard Deviation is 0.921
To calculate Standard Deviation, simply apply the formula (STDV) on your column with the recoded NPS votes of 1, 0, and -1.
Now we can calculate the Standard Error of the Sample, which is the STDV divided by the square root of the total population -1.
The standard error of the sample is 0.16827
And now we can calculate the size of the estimated error which is:
=T.INV(1-confidence level/2, n-1)*standard error of the sample In our case gives 0.34265