Analyzing NPS Responses in SQL

Note: PostgreSQL’s 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:

Output:

| NPS       |
|-----------|
| 89.347    |

Note:

• 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?

Run this template against our sample database that mirrors real startup data. See the connection credentials, then connect in PopSQL.

Bonus Content

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
;

Output:

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;

Output:

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.

Pro Tips

• 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.}