Valme Claro concludes our tutorial series on using Excel to manage customer survey data with a look at a couple of advanced visualisation techniques.
Previous articles in this series looked at how to calculate Net Promoter Scores (NPS) and how to analyse open-ended responses from customer satisfaction surveys to pinpoint potential areas for improvement.
This third and final article in the series will illustrate three different ways to visualise the influence of the themes indentified in the open-ended text responses to an NPS survey.
Impact of themes on NPS
The first approach highlights positive themes collected in response to the survey question,“What do you like the most about our service?” In the example, the data has been collected from surveys distributed twice per year during the past three years. Six themes emerged as the most common: range of services; advice provided; price; website; software and communication.
Step 1: Arrange all the relevant data in columns. In this case we have included the date of the survey, the frequency (number of times that each theme has been mentioned), and the NPS for each subgroup:
Step 2: Calculate the average frequency and the average NPS of the subgroup over time. In the case of “range of services” the formulae are =AVERAGE(C4:C9) for the average frequency and =AVERAGE(D4:D9) for the average NPS of the subgroup:
Content seriesView full content series
Step 3: Summarise the NPS of each subgroup in one column:
Step 4: Calculate the average NPS in a different cell, in this case with the formula =AVERAGE(H4:H9) and subtract the average NPS of all the groups to the NPS of each subgroup.
You can then select the data and reduce the number of decimal places to see the data more clearly:
Step 5: Now you can visualise the data with a bar chart selecting the column with the NPS difference for each group and the name of each theme and selecting Insert > Charts > Bar > Clustered bar.
This visualisation shows the relative impact of each theme. In this example, the subgroup of people who mentioned the software as their favourite area of the service, rated the service 4 points higher than the mean, while those who mentioned the website rated it five points lower than the average.
Impact of each theme on NPS overtime
It is important to track customer satisfaction changes over time and see how shifts in perception for each area influence changes in the NPS. This can be done by plotting theme frequency against the NPS values at the same points in time. However, this visualisation is more useful when the two variables correlate.
For this example, we will calculate the correlations use the data of the six data collections previously shown (from January 2015 to July 2017); a higher data sample would show more accurate results.
Step 1: Calculate the correlation of the variables frequency and NPS for each subgroup with the =CORREL formula. In this case, for “range of services” the formula is =CORREL(C4:C9,D4:D9)
To interpret each correlation value, you can see which of the following values each value is closest to:
- –1: Perfect negative correlation
- –0.70: Strong negative correlation
- –0.50: Moderate negative correlation
- –0.30: Weak negative correlation
- 0: No correlation
- +0.30: Weak positive correlation
- +0.50: Moderate positive correlation
In this case the correlation for “range of services” is 0.49, which is weak to moderate.
Step 2: The highest correlations can be shown in line charts. For instance, advice correlates strongly with NPS (0.73), so the relationship is easy to see in a chart. To include it, we can select the data and click on Insert > Charts > Line > Line with markers.
Finally, we can create a quadrant plot to get a general overview of all the themes importance and frequency.
Step 1: Go to Insert > Charts > Scatter.
Step 2: Right-click on the chart and click on select data. Under “Legend entries” click on “Add” and choose the appropriate ranges of data for each theme. In this case, the series name is the name of the theme, the series X values correspond to the correlation value for the theme and the series Y range is the mean frequency:
Once you have added all the themes to the chart it will look similar to this:
Step 3: Finally, we can change the way the chart looks by adding a few modifications. In this example, we have deleted the horizontal gridlines, and we have included lines to separate the areas of the quadrant (by clicking on Insert > Shapes > Lines) and text boxes on each corner of the quadrant to specify the frequency and the importance of the elements on each area: