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.
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.
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:
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.
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.
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:
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).
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!
Guide to Customer Satisfaction Surveys