Analyzing NPS Responses in SQL
filter() _function makes this SQL NPS template simpler than any others we found. Plus it’s accurate (a lot of other tutorials were just... wrong 😬 ).
Why calculate NPS?
Do your customers love you? Or do they say bad things about your product to their friends? NPS (Net Promoter Score) answers that question.
To get NPS, ask customers one unchanging question (pictured above). Then compare the ratio of people who love you (9 - 10) vs people who actively dislike you (0 - 6).
Boatloads of articles explain NPS. If you need the basics, check out Hotjar’s write up.
SQL you can copy / paste
The N in NPS stands for “Net”. All you’re calculating is:
Here's the whole query, then we'll break it down.
with nps_calcs as ( select count(1) filter (where score >= 9)::float as promoter_ct, -- people who love you count(1) filter (where score <= 6)::float as detractor_ct, -- people who hate you count(1)::float as all_responses_ct -- all the people (love, hate and neutral. Don't exclude the neutral!) from nps_responses ) select ((promoter_ct/all_responses_ct) - (detractor_ct/all_responses_ct)) * 100.00 as NPS from nps_calcs ;
Within the CTE
nps_calcs, you calculate the only 3 numbers you need:
| promoter_ct | detractor_ct | all_responses_ct | |-------------|--------------|------------------| | 2080 | 42 | 2281 |
The second part of the query is simply:
| NPS | |-----------| | 89.347 |
- We converted the counts to floats to avoid integer division.
- Feel free to use the
ROUND()function on your NPS calc (we just like to know exactly how our customers feel 😉 ).
Try it yourself?
Why calculate NPS in SQL (vs inside of Delighted, Hotjar, etc)?
With SQL, you can explore trends far more deeply than in most tools.
Example 1: Cut your NPS data by different characteristics such as account age (i.e. who’s happier: your newer customers or your older customers?)
with nps_calcs as ( select months_subscribed::integer, -- easy to add! Use any characteristic you care about count(1) filter (where score >= 9)::float as promoter_ct, count(1) filter (where score <= 6)::float as detractor_ct, count(1)::float as all_responses_ct from nps_responses group by 1 order by 1 ) select months_subscribed, ((promoter_ct/all_responses_ct) - (detractor_ct/all_responses_ct)) * 100.00 as NPS from nps_calcs order by 1 limit 12 -- just looking at the first year experience ;
Looks like customer sentiment improves over time.
Example 2: Or you can see how your aggregate NPS metric changes over time:
with nps_calcs as ( select date_trunc('day', created_at) as period, -- or week, or month, etc count(1) filter (where score >= 9)::float as promoter_ct, count(1) filter (where score <= 6)::float as detractor_ct, count(1)::float as all_responses_ct from nps_responses group by 1 order by 1 ) select period, ((promoter_ct/all_responses_ct) - (detractor_ct/all_responses_ct)) * 100.00 as NPS from nps_calcs order by 1;
Things people often mess up with NPS:
- calculating it wrong 😬 . The most common error? Only looking at
# promoter / total responses(and forgetting to subtract the % detractors)
- omitting 0 as a choice in the survey.
- using a 1-5 scale in the survey and then fudging it
- app store ratings or 5-star reviews are helpful but different than NPS; by all means collect that data but don’t use it apples-to-apples as NPS
- asking multiple questions in the survey and biasing the respondent
- e.g. if you ask respondent to think about features they love or hate before asking the NPS question, their mind is not going to be a blank slate
- collecting this info and not doing anything with it! Be sure to:
- improve your product with the feedback (obviously)
- ask 9s and 10s to give a tweet-length testimonial or G2 review.
- When you collect an NPS response, record attributes of the respondent at the moment they answered the survey. It makes it so much easier to do analysis later.
- E.g. free vs. customer, weeks since sign-up, number of lifetime orders, etc).
- Using Superhuman’s Product Market Fit framework? You can pretty easily modify our NPS query:
select count(1) filter (where response = ‘very disappointed’)::float / count(1)::float from pmf_responses;
Obligatory Legal Stuff: Net Promoter, Net Promoter System, Net Promoter Score, NPS and the NPS-related emoticons are registered trademarks of Bain & Company, Inc., Fred Reichheld and Satmetrix Systems, Inc.
Spread the wordTweet