Home » correlation

Tag: correlation

Performing Driver Analysis in Microsoft Excel

Many Net Promoter programs start as small pilot programs without large investments in systems and infrastructure. This can put Net Promoter novices into a catch-22 situation – they can’t obtain the budget for more robust tools until they demonstrate results, but it is easier to demonstrate results with more robust tools.

The good news is that many of the most important analytical exercises needed for a successful Net Promoter pilot can be performed using software that you probably already own – Microsoft Excel.

 

What is Driver Analysis?

Driver Analysis is a powerful tool that can help you understand the factors that influence loyalty. Driver Analysis attempts to identify the attributes that are most correlated with loyalty (as measured by NPS), and illustrates areas where you are under (or over) delivering. This information can then be used to prioritize the investment of capital, time, and resources into areas that will yield the highest return in customer loyalty.

 

Example Scenario

In the scenario below, I will take you through a quick exercise in which we will create a Driver Analysis table for a fictional business.

You are the Vice President of Customer Experience for Acme Pizza Company. You ran a pilot Net Promoter survey that asked customers four questions: (1) Likelihood to Recommend [aka “the Net Promoter question”], (2) Satisfaction with Speed of Delivery, (3) Satisfaction with Quality of Pizza, and (4) Satisfaction with Customer Service. Each question had a response scale that ranged from 0 to 10. As a result of the survey, you see that Acme Pizza’s Net Promoter Score is 10%.

You have a limited budget to spend, and want to know which of the three areas (Speed, Quality, or Service) you should focus on improving. You have an excel spreadsheet with your survey responses that looks like this:



 

 

Step One: Calculating Satisfaction

To create a Driver Analysis table, you first need to calculate the average satisfaction for each attribute. This is relatively straightforward – for example, to calculate the average satisfaction for Speed (found in Column C), you would use the following formula:

=AVERAGE(C:C)

Once you repeated this step for all three attributes, the end result would look like this:

 

 

 

Step Two: Calculating Correlation

Unfortunately, many people stop at the previous step. Looking at the information generated, they would make the decision to direct their efforts towards improving the quality of the pizza – after all, that is the attribute for which customers are expressing the greatest amount of dissatisfaction.

In order to make a more informed decision, it’s not enough to know how satisfied our customers are with each attribute – we need to understand how strongly each attribute is correlated with loyalty. This is done by calculating the correlation between the Net Promoter question (likelihood to recommend), and each individual attribute.

Calculating correlation in Microsoft Excel is much easier than you might expect. The formula is simply:

=CORREL(array1,array2)

To create our Driver Analysis table, we’d first calculate the correlation between NPS (Column B) and Speed (Column C) like so:

=CORREL(B:B,C:C)

We would then repeat this step with the other two attributes – Quality (Column D) and Service (Column E) – using the following formulae:

=CORREL(B:B,D:D)
=CORREL(B:B,E:E)

Our finished product would look like this:

 

 

The result of this formula is called the correlation coefficient (or r value for short) with can range from -1.0 to +1.0. This rating can be interpreted using the following guide:

  • An r value close to 1 indicates that there is a strong relationship between the two variables
  • An r value close to 0 indicates that there is a weak relationship between the two variables
  • A positive r value means that as one variable increases in value, the other variable will increase in value. Likewise, as one variable decreases in value, the other variable decreases in value.
  • A negative r value means that as one variable increases in value, the other variable will decrease in value.

 

Step Three: Analyze the Data

We now have a table that tells us how satisfied our customers are with our speed, quality, and service, and how strongly each of those attributes are correlated with overall NPS.  Looking at our Driver Analysis for Acme Pizza, we can make the following observations:

  • Customers are very satisfied with our speed of delivery, which has very little correlation with loyalty
  • Customers are dissatisfied with the quality of our food, which has very little correlation with loyalty
  • Customers are mildly satisfied with our customer service, which is highly correlated with loyalty

Using Excel’s built-in graphing functionality, we might choose to display this information visually like so:

 

Now that we have completed the analysis, we are able to see that we should focus on improving Acme Pizza’s customer service. Of the three attributes, customer service represents the best opportunity for positively impacting Acme Pizza’s customer loyalty.

 

Driver Analysis is a powerful tool that can help ensure that you are focusing your time and energy on the items that will have the biggest impact on customer loyalty. By performing Driver Analysis using Microsoft Excel, you can now generate actionable data without making large investments into additional systems and tools.