Open-ended survey text analysis in Excelby
Asking open-ended questions in your customer satisfaction surveys can help you gain insight into your NPS. Valme Claro presents a few useful Excel analysis techniques.
Your Net Promoter Score (NPS) can tell you where your firm stands on customer satisfaction, as explained in the first article in this tutorial series.
But do you know why your product or service received that score and what you could do to make your firm better in the future?
Adding open-ended questions to your survey can give you valuable insight of where and why customers would like to see improvements in your services.
Some of the questions you could ask are:
- What could we improve?
- What do you like the most about our service/ product?
- What do you like the least?
Once you have collected the answers, you need to analyse the results. This article presents an overview of how coding the replies can help you identify the underlying patterns.
The first step is to categorise the answers into themes. Manual coding can be a time-consuming process, so it is advised only to do this when there are a hundred answers or less. If you wish to analyse more answers, you might need to consider using a specific automated coding solution such as Thematic.
In this tutorial, we will code the answers manually. Starting with a list of all the free text responses to the question, look for common themes among them and then count the number of responses that apply to each themes. For instance, if you are analysing the answers to the question “what could be improve?” and encounter the answer,“the person who helped me wasn’t very friendly”, it would make sense to assign that to a “customer service” category.
Step 1: Copy all the responses to a new column. In this example, They have been copied to column C along with an individual response number in column B:
To start coding your data, read a sample of the data and think about the codes (themes) into which they could be could be categorised. As you start categorising the answers with those codes, you will find that some answers cannot be categorised, so you will need to review your coding structure and devise new themes to cover all the options. There will be a temptation to add lots of different codes, but try to limit them to 10 or so. If necessary, the last category can be “other”.
Step 2: Write the themes in column E and apply a letter code for each theme in the next column:
And assign a code to each response:
Once the answers have been coded, we can start analysing them.
Step 3: Perform a frequency count to see how many times each category appears. For example, for the software theme (which was coded with the letter “s”) the frequency formula in cell G4 looks like this:
For the rest of the letters, just fill down the formula (using the control-click trick on the box at the lower righthand side of cell G4), but remember you will need to change the letter s for each of the other codes in the consecutive rows.
To check that the COUNTIF formula has worked, calculate the total and check the number against the total number of responses. In order to do this, we use the SUM formula in cell G11:
In the column to the right, we can convert the numbers to proportions by dividing each frequency value by the total. In the case of the theme “software” this means =G4/G11
Once the proportion for each theme has been calculated, the number format can be altered (using the Format Cells option from the right click menu or by selecting the relevant cells and clicking the % icon on the Home menu ribbon Number block.
As in the previous step, the total can also be calculated to make sure it is 100%.
The information can also be presented in a chart for a more visual representation.
Highlight the themes and proportions and go to Insert > Column > Clustered column or press F11 to insert a chart. In this instance, we will go for a very simple bar chart option:
We can then delete the legend and add a title and an axis title (under the layout tools):
As is often the case in such market research, Communication has emerged as a significant factor in customer satisfaction. This tutorial was designed as a very basic introduction to survey text analysis, but the principles and techniques can be applied to a wide spectrum of business scenarios.
The final article will look at a couple of other text-handling techniques and more sophisticated ways to analyse and present your survey findings.