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

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!)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!

Related Articles

Best customer segmentation software Best Customer Segmentation Software to Utilize in 2024

Understanding customers at scale has come a long way. Back in the day, companies used simple attributes to carve up their customer base in some way to apply different resources to support and/or expand those customers. Boy was it blunt though! Generally, it never got far beyond segmenting your account list by their spending or […]

Read more
best customer success software Best Customer Success Software for Your CS Department in 2024

If you are reading this, you are faced with a real challenge. Either you already manage a customer success function and your existing toolset is not fit for purpose, or you have decided to start a customer success department and want to implement the right software as a solid foundation for your Customer Success Managers […]

Read more
top customer retention software Top Customer Retention Software to Reduce Churn in 2024

Figuring out the right customer retention management software to help manage your Customer Retention goals can be tricky. It really comes down to what your specific goals are, what type of market you serve, the customer data you retain and where that data resides. There are some specific types of tools you should consider. Customer […]

Read more
Expansion revenue Expansion Revenue: Definitions, Metrics, Formulas and Tips

Recurring revenue is essential for any SaaS business. You are probably aware of the fact that customer acquisition is often very expensive, with the risk of getting only a few lukewarm leads. Now, is it possible to cut customer acquisition costs and still generate more recurring revenue? This is precisely what expansion revenue brings to […]

Read more
customer churn rate Customer Churn Rate: Definition, Formulas and Benefits

Although customer churn, losing some customers over time, is perceived as inevitable in certain cases, it is also undesirable among companies. However, every business experiences churn, regardless of its size, quality of customer service and the product itself.  What’s important is how your company responds to churn, whether you fully understand why it happens, and […]

Read more
Best customer success blogs 12 Best Customer Success Blogs in 2024 You Should Follow 

With their aims of transforming new customers into loyal ones, nurturing customer relationships during their entire journey, and driving revenue growth through upsells and cross-sells, customer success teams have become one of the most valuable assets of any SaaS company. And, it’s important to be updated on the latest trends, best practices and strategies in […]

Read more
customer churn analysis What is Customer Churn Analysis and How to Perform It? 

Churn is, unfortunately, an inevitable part of any business, no matter its size, reputation, or customer retention efforts. Some businesses are faced with higher and some with lower churn rates, yet what matters most is determining the primary cause of churn to be able to take effective measures to prevent it. This is precisely where […]

Read more
reasons for customer churn Reasons for Customer Churn and Solutions for Some Scenarios

In SaaS, nurturing strong customer relationships is one of the customer retention strategies for business survival and growth. Still, it seems that no matter how hard you work, there will be some customers who simply decide to leave. Although customer churn is inevitable, certain reasons for it can help you predict it and eventually, reduce […]

Read more
customer success goals Different Customer Success Goals: Which One to Choose?

Nurturing customer relationships, assisting and educating customers during onboarding, pushing upsells and expansion…the list goes on and on. Still, these are just some of the responsibilities of the customer success team. 

Read more