Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Simon Kilgarriff
    NetPromoter Score (NPS) - Analytics Cookbook Recipe
    Topic posted September 27, 2017 by Simon KilgarriffGold Trophy: 10,000+ Points, last edited September 29, 2017 
    489 Views, 5 Comments
    NetPromoter Score (NPS) - Analytics Cookbook Recipe

    NetPromoter Score (NPS)

    Calculating Your NPS for the Last 7 Days

    Author: Simon Kilgarriff

    Difficulty rating: (Intermediate)

    Target persona: Marketing/Survey Analyst, Service Managers, Executive


    5 Tables (questions, question_responses, question_sessions, flows, surveys)

    1 Outer / 3 Inner Joins

    7 Columns using formulas (7 count distinct (), 1 min(), 1 max() & date_diff())

    5 Filters - 1 required, 1 fixed, 3 run-time

    1 Function - multiple uses of count function


    Overall description/purpose:

    Survey reports can always be tricky because of the table joins. This report enables you to see a breakdown (by response score) for all the choice type questions for a survey. It is useful for analyzing things like customer satisfaction, where the most satisfied customers will select the choice with the highest score and the least satisfied customers will select the choice with the lowest score.

    Note: This report is intended for use with surveys with choice type questions, where the respondent can select only one response to the question.

    Use case being served:

    This report will benefit anyone who wishes to use an NPS metric for measuring Survey responses since there are no out-of-the-box reports to show NPS results.

    While I do not have a preference for NPS as a metric, I have had a few customers requesting reports to show an NPS calculation. Everyone wants things done differently of course, but here is a report that shows the ingredients for an NPS score report for responses to a survey question over the past 7 days. This report can easily be modified for your own specific uses and can be made far more appealing to the eye using the formatting options.

    Screen shot: 

    Special functionality usage: View the column definitions to see how the calculation was made for NPS.

    High level flow for report creation:  You must have a survey and question that is compatible with measuring NPS.  

    Master Chef Approved (a.k.a. Data Validated)? Yes

    Included are the following (in a .ZIP file):

    • Human readable PDF report definition
    • Complete Report Recipe and Report Configuration Details (PDF)
    • XML Report definition 



    • Erica (Leep) Anderson

      We are leaving these report recipe posts open for comments, because we want report chefs to hear how their report recipes helped you. If you found this report recipe helpful, please give Simon's post a thumbs up and leave a comment letting him know you appreciate his help, how this report helped you and your organization, etc.

      If you have a question about this report recipe, please start a new topic in the "Reporting and Analysis" forum board and link to this report recipe in your question. DO NOT post your reporting question here. We will remove any comments asking for help in the comments of this post.


    • Qing Liu

      awesome. thanks simon

    • Danette Beal

      This is fantastic, thanks Simon!

    • Jess Campbell

      Thank you Simon! This is a great report and is quite similar to what I use. You have made it accessible to the masses!

      I would caution, though, that unless the survey in question has a huge response rate, calculating NPS for a week is an iffy proposition, especially if you want to measure trends over time, as the sample size needed to reliably report change in a statistically significant way is relatively large.

      There is a very nice excel based tool available that allows you to confirm significant change (using MOE and Chi Squared) by inputting the raw data.

      But inputting the info manually is onerous and leaves you open to keypunch errors. To avoid that, and having to answer the question, "Is this change real?" question every month, I have actually written a month over month NPS trend report in which I calculate MOE and Chi Squared using a php script. One of these days, I will have actually have time to post it!

      I have attached a screenshot of my report.


    • Simon Kilgarriff

      @ Jess - thanks for your comment and a great point.  I don't actually recall what the date range was for the customers that requested the report and just put in something small for the recipe.