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
saas customer success department How to Start a SaaS Customer Success Department?

Customer success is about doing all you can to help your customers succeed with your SaaS product. A well-functioning customer success department can improve customer retention and satisfaction and drive revenue growth. Therefore, having a customer success department in SaaS can help you succeed.

Read more
customer success plan  What is a Customer Success Plan and How to Create it?

Your business’ success depends on the success of your customers. If you help your customers achieve their goals with your product, you’ll also help your business grow and increase revenue. 

Read more
customer onboarding statistics 15 Customer Onboarding Statistics and Trends in 2024 to Note

In the SaaS world today, each lead you turn into a customer is a call for celebration. Still, to ensure them a smooth journey and success with your product, it is crucial to first help them understand how they can use your product or service effectively to get all the benefits they offer in the […]

Read more
how to become a customer success manager How to Become a Customer Success Manager: All You Need to Know

Nowadays, it seems that acquiring each new customer calls for celebration. Yet, their choosing you over competition is merely the beginning of a beautiful friendship, as retaining them is equally, or usually even more, difficult. 

Read more
Voice of the customer best practices Voice of the Customer Best Practices and Tips in 2024

When we aim to understand someone or something, the first step is to listen or observe carefully. The same rule applies for customers.  If you wish to truly meet your customers and understand their concerns and goals, you must not only solicit and gather their feedback but also act on it. This is precisely what […]

Read more
what is product discovery What Is Product Discovery: Definition, Benefits and How to Do it?

Developing, launching and promoting a new product or feature is not only time and energy-consuming but also expensive. This is why frustration is inevitable if the new feature of a product goes unnoticed or unused.  To avoid that, companies must first understand market trends and customer needs to be able to create products that address […]

Read more