Build a (no-code) NPS tool using Google Workspace!

tl;dr – You can put together an NPS survey tool with analytics using Google Forms and Google Workspace. Here’s the form. Here are the results.

Many SaaS businesses measure their customers’ experiences by sending Net Promoter Score™ surveys. Even if you haven’t heard of an NPS™ survey before, you have likely seen one:

“How likely are you to recommend us to a friend or colleague on a scale from 0 to 10?”

Answer 9 or 10 and you are a “Promoter”. Answer 6 or less and you are a “Detractor”. Subtract the % of responses that are Detractors from the % that are Promoters (that’s the ‘net’ part) and you have your NPS score:

NPS = % Promoters - % Detractors

Or:

NPS = (# Promoters - # Detractors) / # Responses

NPS has its own detractors but it can be a useful tool for quickly gauging customer sentiment. Easy to collect. Easy to interpret.

There are a bunch of great services that allow you to collect and analyze NPS surveys. Delighted, Survey Monkey and our newest integration Beamer are just some of the services that allow you to collect and analyze NPS surveys.

But what if you want to do it yourself? Google Apps provides all the tools you need to email respondents (Gmail), collect responses (Google Forms), and analyze results (Google Sheets). Have a look at the finished product here.

Features

Includes an intuitive form, with validation, created with Google Forms

Exclude results older than 6, 7, 12… months (Optional)

This will exclude no-longer-relevant results and give you a better idea of how you are doing now.

Uses only the most-recent response for each email address

Includes loads of useful data, including:

• Your current NPS Score
• Number of Responses
• Number of Unique Respondents (Email Addresses)
• Number of Unique Email Domains (ie. Customers)
• Your NPS graphed over time
• Most recent NPS score per email address
• NPS per Customer (email domain)

Multiple ways to collect responses

You can send your users an email with the form, embed the form in your website or put a little bit of javascript code in your site asking users for feedback!

<script>    var formUrl = "https://forms.gle/trYKL1EaiDgspNsc8"; // CHANGE TO YOUR FORM    var div = document.createElement("div");    div.style = "text-align:center; background: yellow; line-height: 50px;position: fixed; left:0; right:0; bottom: 0; width:100%; height:50px";    div.innerHTML = "<a href='" + formUrl + "''>Click here to tell us what you think about Akita!</a>";    var lastChild = document.body.lastChild;    document.body.insertBefore(div, lastChild.nextSibling);</script>

(This is what it looks like!)

We’ve already done (most of) the hard work for you! There are a few steps required to make it work for you:

• Copy this Google Sheet;
• Create your own NPS form (see below);
• Change one formula (see below);
• Enable history over time (see below); and
• Send some surveys!

Should you roll-your-own NPS? Probably not—there are reasonably priced alternatives out there. But can you put together a surprisingly feature-rich solution for free? Absolutely!

Notes:

How to Create Your Form

Once you have copied the Google Sheet, you will add the form from the Google Sheet’s “Tools” menu:

• Click on “Tools” > “Create a new form”
• Add Question 1: “How likely are you to recommend us to a friend or colleague?” The type should be set to “Linear Scale”. The options should go from 0 “Not at all likely” to 10 “Extremely Likely”. It should be required.

• Add Question 2: “What is your email address?” The type should be set to “Short Text”. It should be required and should include validation to ensure it is a valid email address (see image).

Update Your Google Sheet to Use Responses from this Form

You can use the “Send” button to access the form and submit a response.

Unfortunately, it will be added to a newly created tab called Form Responses 2. We need to update the Google Sheet to use this as the source of data. To do so, click on the Calculations tab and then cell B2. You will update the function stored in this cell to point to the new tab:

=QUERY(‘NPS Responses‘!$A1:$C, “SELECT * WHERE A > date ‘”&TEXT(DATEVALUE($S$2),”yyyy-mm-dd”)&”‘”)

Should become

=QUERY(‘Form Responses 2‘!$A1:$C, “SELECT * WHERE A > date ‘”&TEXT(DATEVALUE($S$2),”yyyy-mm-dd”)&”‘”)

If you haven’t submitted a test result, the result of this formula will be #NA with an error saying Query completed with an empty output. This is OK—you just don’t have data yet. You can now delete the old NPS Responses tab.

Enable History Over Time

It is nice to see a graph of your NPS over time (hopefully going up!) To enable this, we need a way to store the value each day in a separate tab. Google “App Scripts” allow you do do just that. You create a function that runs (every night) and adds the current NPS to the History tab. From your Google Sheet:

Empty any existing dummy data in the History tab.

Click on “Extensions” > “Apps Script” (a blank project will be created and connected to your Google Sheet. Give it a good name).

In the Apps Script “Editor” tab, copy and paste the following function:

function updateHistory() {    var ss = SpreadsheetApp.getActiveSpreadsheet();     var historySheet = ss.getSheetByName('History');     var analysis = ss.getSheetByName('Analysis');     const currentNPSCell = analysis.getRange('$E$8');    historySheet.appendRow([Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"), currentNPSCell.getValue()]);}

Click the “Save” icon and then “Run”. You will be prompted to grant this function access to your Google Sheet.

Click on the “Triggers” tab and then “Add Trigger”. Create a trigger that calls the updateHistory() function daily.

With this setup, the NPS score will be copied to the History tab every night around midnight!