Real-Time Monitoring of Customer Satisfaction With SurveyGizmo and Google

Tracking NPS, or Net Promoter Score, can help keep your finger on the pulse of your customers’ sentiment about your product or brand. (If you’re not familiar with this way of measuring customer satisfaction, you can see our guide to NPS surveys.)

Tracking and acting on these stats is great, but it’s even better if they can be front and center in your organization. A simple way to do this is by linking your NPS survey results to a Google spreadsheet and displaying it on a screen.

It sounds fancy, but all you need is SurveyGizmo, Google Sheets, and a flat panel monitor you can mount on the wall.

 (This is just one of many Google spreadsheet integrations available in SurveyGizmo, so feel free to expand on what we go over here and add your own suggestions in the comments. We love hearing what our customers are doing!)

Let’s get started!

Setup the Google Spreadsheet

The data from your NPS survey will be delivered to a Google spreadsheet in real time, so getting that spreadsheet setup correctly is our first step.

Create a new Google spreadsheet with four different worksheets.

Name the spreadsheet something descriptive like, “Our NPS Scores.” For the worksheets, call them “Promoters,” “Passives,” “Detractors,” and “Stats.”

On each of the first three worksheets make a column for each question that’s in your NPS survey (and that you want included in this data). Make the column names relatively simple, like “NPS,” “Name,” “Email,” etc.

SurveyGizmo will be sending each response from your survey to the correct worksheet, so the names of the columns need to be consistent across all three worksheets to ensure accurate data transfer.

In the screenshot below you can see the general setup of one of these worksheets. The other two will look just like this, but will have different names. Notice the column headers at the top and worksheet tabs at the bottom of the sheets.

net promoter score spreadsheet

Setting Up Your NPS Survey

Now that the three initial worksheets are set up and the columns have the right names, it’s time to create Google spreadsheet actions in SurveyGizmo.

If you want to try out the NPS survey for the first time, grab our handy NPS Survey Template (just click to download it directly to your account.)

For those who already have an NPS survey in their account, you can head straight to the “Build” section and find the final “Thank You” page of the survey.

options for customer satisfaction thank you page

From here we’ll create three separate actions. One will send all of the promoters’ information to the “Promoters” worksheet, the next will handle pushing the information from those that gave “Passive” scores to the correct worksheet, and the last one will handle “Detractors” scores.

Below is a screenshot of what the Google spreadsheet action looks like when it’s setup to send Detractor scores to their worksheet:

setting up google spreadsheet

Notice in the screenshot that we mapped each question in the survey to a corresponding column name in the Google spreadsheet.

Question logic will allow us to fire these actions only when certain conditions are met. For instance, we only want the data to go to the “Detractors” worksheet when people give an NPS score of 0-6.

You’ll need to set up three distinct logic conditions so that NPS scores from 0-6 are marked as Detractors and sent that worksheet, scores of 7-8 go into the Passive sheet, and scores of 9-10 get added to the Promoters sheet. Splitting the results like this makes getting totals and making calculations much simpler.

Below is the logic setup for the Detractors action. You’ll see that we created the logic condition to update the Detractor worksheet if the respondent identified themselves a a 0-6 on the NPS scale.

logic for NPS results

Repeat the same process for the other two actions for Passives (scores of 7-8) and Promoters (scores of 9-10). Make sure you apply the correct logic and avoid overlapping number ranges.

To test your setup take the survey and give all three score types (Detractor, Passive, and Promoter). Make sure your spreadsheet updates correctly.





surveys for customer satisfaction




Getting Statistical on Your NPS Results

NPS scores are calculated by subtracting the percentage of respondents who are Detractors from the percentage who are Promoters.

It’s not a complicated formula, but we want to automate it with our Google spreadsheet so our total NPS score gets updated in real time.

First, go to your “Stats” worksheet in the Google spreadsheet and use the Count function to get a count of all your Passives, a count of the Detractors, and a count of the Promoters.

Next use the Sum function to add up the total of all three of these:

equations in nps spreadsheet

Then calculate the percentage of each of them compared to the total number of respondents. For example:

Promoters percentage = number of promoters/total number of responses x 100

Finally, subtract the percentage of Detractors from the percentage of Promoters. This is your NPS Score! It can be easier to work with if you format it as a regular number rather than a percentage (e.g. 27.4% becomes 27.4).

formatting nps scores in excel

Track and Display Your NPS Results

If you plan to display your real-time updates on a screen, spend a little time formatting the spreadsheet for optimal readability and company branding. Then all you need to do is designate a computer or other web appliance to stay active and keep the Google spreadsheet up all day.

Wires becoming a problem? Check out wireless options like a ChromeCast or tab-casting through the Chrome browser for wireless connectivity.

Now everyone in your organization can see your NPS score as it changes throughout the days, weeks, and months, and you can hopefully all celebrate its gradual climb upwards.

How could you integrate SurveyGizmo with Google spreadsheets? We’d love to hear from you in the comments!

Related Articles
Guide to Customer Satisfaction Surveys

Join the Conversation
  • John R

    It’s possible to send the NPS formula when posting a survey to a Google Sheet, instead of using the display logic. I find this helpful if I’m already using display logic to post certain surveys to certain Google Sheets.

    You can use relative values for the cell to associate the NPS question with the formula field. For instance, I use this:
    =ROUNDUP((100*((COUNTIF(R[0]C[-12],”>8″)-COUNTIF(R[0]C[-12],”<7"))/COUNT(R[0]C[-12]))),0)

    This posts -100 for the detractor, 0 for passives and 100 for promoters, given that I have my NPS column 12 columns away from my NPS (0-10) rating. You then just average the column to get the NPS score.

    Relative values can be used for a number of other formulas you want to run within the row you're posting. Just be sure the formula falls in a higher column than the cell you're referencing.

  • Vinay Satrasala

    I don’t understand why to create 3 spreadsheets. one spreadsheet with data and other one with summary would suffice isn’t it?

    • sgizmo

      Hi Vinay,

      We recommend creating four different worksheets, one for Promotors, Passives, Detractors, and Stats. You can absolutely feel free to set up your spreadsheet in any way that best suits your needs. Please let us know if we can assist with anything else!

      – SurveyGizmo Team

  • Pat McCabe

    I have been wanting to learn how to get data into a Google spreadsheet but it’s been low on my list, mostly because I thought it would be too complicated (having to learn API things). I need to go through this information but I wanted to thank you for putting this information out there in the first place. It shows me that this feature was more complicated in my head than in reality. Kudos and thanks to you, Terry, for putting this information together. This kind of service (anticipating needs) and this feature just add to my many reasons I prefer SG over other survey software packages.

    • sgizmo

      Thank you for the feedback, Pat! We really appreciate it!

      – SurveyGizmo Team

  • KiwiJoker

    How did you set it up so that the dashboard is filtered by individual agents?

    Did you seperate surveys or do you use a count if function?

    • sgizmo

      Hi Kiwi, can you please provide more information? I’m not sure which dashboard you are referring to.

      Thank you,
      SurveyGizmo Team