If you are sitting on a large amount of feedback from your customers it can be a gargantuan task to separate the signal from the noise. Reading all the comments, figuring out what the main topics or concerns are can be time consuming! In this post I will share some App Script code that you can use straight away in your Google Sheets to automate the boring but important task of finding categories within your comments.
Usually, when the first comments generated by your surveys - such as NPS - come in, the first move is to dig into it straight away, trying to find out what topics can be extracted: "this comment is about UX", "this one refers to the performance of the product" etc... But after a while who would not give up trying to categorise the comments? It is a mind numbing, error prone and lengthy task. Nobody has the bandwidth and/or the patience to do it (at least manually). However it is a mistake: bringing an extra level of granularity within your survey data can help uncover very important gems.
The goal of this post, as mentioned in the introduction, is straight forward. I will share with you some Apps Script code that you can copy and paste in your Google Sheets and use straight away. I have used it times and times again. The first four steps will take you through some set up to ensure that it will run smoothly and at the end of the post I will share some of the trends I usually look into after running the code.
Setup your Google Sheets first! (boring but important)
The script I am sharing with you assumes the following:
- you have two sheets:
- the first one contains the comments you want to analyse, call it Data,
- call the second one Summary: this is where the macro will paste the result.
- In the Data sheet, the information is organised as follows:
- Column A should have the comments.
- Column B should show the NPS score (likelihood to recommend) associated with the comments. Do not worry if you are not using the code for an NPS survey and you do not have any score, the script will run regardless.
Visual summary below:
If you have an NPS score associated with your comments, the script will give you the number of promoters, detractors and neutral associated with each word. Very handy.
Get the code and add it to your Google Sheets
This will take you to my GitHub repository where you will find the code to be used in your Google Sheets.
Next go to your Google Sheets and click Tools > Script editor. Delete what’s in there and paste the code you have just copied.
Hit save and give your project a name. Close the App Script window and refresh your Google Sheets.
Run the code!
You should see after a few seconds an extra menu appearing beside Help, called Analyze Comments. Click it and then select Find Keywords which is going to run the code that will analyse your comments.
Click through the permissions popup to allow your Script to run.
You may see "This app isn't verified", in which case click on "Advanced" and then "Go to [name] project - unsafe" and then click "Allow". These are extra security steps introduced by Google for non-verified scripts. Since we're the author of this script though, it's safe to run.
Here you go! Wait for the script to run (Google Sheets will let you know): it might take a few minutes (it took me a bit less than a minute for around 2k comments).
Quick comments on how to use the script
If you return into the Script Editor (Tools > Script editor) and scroll down to row 208, you would find the CheckNotJunk function. This function basically remove any stopwords from the analysis, words that do not add any value. You can update this list as you see fit and add any term in the list array: just add , "yourword" at the end of the list on row 223.
Also, if you have added NPS scores to the Google Sheets, you can run the part of the script that will provide you with stats associated with each word or topic in your comments - more on that below. That part of the script is quite slow as there is a lot to process and Google Sheets does not allow App scripts to run longer than a few minutes. So I have limited the code to run for the top 20 words only (by the way you can change this parameter as well by going into row 147 of the code). But the good news is that if you run the code again, it will start where it stopped and will crunch the numbers for the following 20 words. You can do that as many times as you need.
Right, now let's see what we can do with the script!
Let's have a look at the results
When you run the App Script and select Find Keywords, you will obtain a list of words that are used in the comments you provided as well as the number of times they appear.
I would usually run the script on comments coming in every month (we used to run NPS on a monthly cadence in the company I was working at) and would keep a record of the result for every single month. This way I am able to look at monthly trends which is very powerful. I would usually focus on the top 10 or 20 words and check if their ranking change over time, whether they disappear from the top 10 / 20 after a while or if new topics emerge etc... If your team of engineers is working hard at solving bugs or crashes on your software, ideally you want your customers to stop complaining about them. You should witness the benefits of that work by seeing the words "bugs" and "crashes" disappearing from the comments over time.
If you are running the script on NPS surveys, you should include the NPS score associated with each comment. This is where the script can provide you with even more powerful trends. If you select Analyze Comments in the menu and then Run stats ... as below:
Now you see that additional data was added. In other words you have extra context: in the example above, you can see that the term "functionality" is associated with comments that have pretty low NPS score (as shown by the average). The context is quite negative and indicate that the product you have built might lack functionalities.
Looking at the average NPS score on a monthly basis, can let you know whether the context around a specific word (like "functionality" here) is improving or deteriorating. Even better you can track the numbers of promoters & detractors over time, by running the script on a monthly basis (or each time new surveys are released). Then you are in a better position to understand the real dynamic behind why a specific topic appears in your top10 month after month and whether the context in which that word is used is improving or degrading.
These steps are very important. They can help decide how best to categorise your NPS feedback so you can move away from looking at NPS scores in aggregate. How is my NPS score trending over time? is a good question. However, the understanding of why your score is trending in a certain way needs more granularity. What is the satisfaction of my customers who mentioned the word "performance" in their comments? is a better question. Some follow up ones: what is their NPS score and how is it trending? Is the word "performance" being mentioned more frequently month over month? Is it mentioned mostly by detractors? promoters? etc...
Automating categorisation of your comments based on the words or topics you surfaced would be powerful. If you add as well the sentiment behind the comments on top of the satisfaction provided by NPS, this would be amazing! Understanding to what degree your customers are annoyed with some aspect of your product or delighted with some other aspects is where, I believe, lie the explanations for the overall satisfaction with your product or service.