Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]

This is the final part of our series on how to analyze half a million customer complaints. Click below links to read part 1 & 2.

1. Complaint reason analysis – Part 1
2. Regional trends & analysis – Part 2

Customer satisfaction scorecard

In the previous parts of this case study, we understood what kind of complaints were made and where they came from (states). For the customer satisfaction scorecard, let’s focus on individual companies.

Gathering the data

As there are 3,946 companies in the data, we could end up making a ton of scorecards. So first step is to cull down the company list to a meaningful number. Let’s go with top 15 companies (which account for 61% of all complaints).

As we have data between 2011 to 2016, selecting top 15 companies based on grand total of complaints across all years may skew the names. So let’s select the top 15 companies by complaint count in 2015.

Using a simple pivot table, sorted by complaint count we can figure out the names.

But how do you filter the original data so only these 15 companies data remains?

We can use,

• Advanced Filters (set up filter on top 15 company list and copy the filtered values to another location)
• Power Query

I have used advanced filters to do the job. Once we have a new data set with just top 15 company data, let’s turn it in to a table named top.15

Also, as we have partial data for years 2011, 2012 and 2016, let’s use only 2013 to 2015 data for this analysis.

Defining scorecard criteria

If we have access to data like how many customers each business had, how many products they sold, how many transactions the customers made in each month, we can combine that with complaint data to arrive at truly remarkable satisfaction score. Unfortunately, we don’t know have that kind of data. We only know about the complaints. So based on what we know, I have defined the below 5 criteria to measure customer satisfaction.

1. Nasty issue %: Although there are 95 different issues about which customers complaint, not all of them are nasty. We could define a subset of complaints that are particularly nasty and measure what percentage they constitute. Example of nasty issues: Incorrect information on credit report, Cont’d attempts to collect debt not owed etc.
2. Frustration %: Customers have a choice to add narrative to their complaints. This is an option available only since 2015. Out of 91,399 complaints received by top 15 companies in 2015, only 25,482 complaints had this narrative (ie 28%). If we examine this free text narrative for words like frustrated, bad, hate, disappointed etc. we can calculate frustration score. 
3. YoY Complaint Growth:  If a company is improving its complaint volume YoY (between 2013 to 2015), it is certainly worth investigating. One reason could be more customers are not happy with the company. Other factors like adding new customers, change in compliance / regulations, more customers knowing about the complaint facility could contribute to complaint growth rate. As we don’t have the data about these other factors, let’s assume YoY complaint growth affects customer satisfaction rate.
4. Negative resolution %: Each complaint has a resolution offered by the company. The resolutions are usually – Closed, Closed with explanation, Closed with monetary relief, Closed with non-monetary relief, In progress and Untimely response. Out of these 6 responses, we can consider a few to be negative resolutions and calculate what percentage of all resolutions are negative. For example any complaint that is closed without any explanation or relief can be considered negative resolution.
5. Disputed complaint %: Customers have a choice to dispute the resolution offered by the company. If a customer disputes what the company says, it can be considered as a non favorable resolution. We can calculate what percentage of complaints are disputed and use it as a factor in calculating the customer satisfaction score.

Weights & customization of the criteria:

In order to calculate the final customer satisfaction score, we need a few more things:

• Weight for each criteria
• Parameters for certain criteria (ie list of negative resolutions, list of nasty issues etc.)
• Benchmark for each criteria: This can be used to set absolute minimum for each criteria. Any company scoring less than this will get a lower overall rating.

We can define all these in a settings worksheet so that our scorecard is dynamic.

Calculating satisfaction criteria

This is where things get tricky. I have summarized the calculation techniques below. For more, you need to examine the downloadable workbook. You need to also read about array formulas, pivot tables, weighted averages and text formulas.

Nasty Issue %:

• First we set up a pivot table of complaint count by year, company & issue.
• We also set up a table with list of nasty issues.
• We then use an array SUM formula to calculate the total count of nasty issues in the pivot table for each company in each year.

Frustration %:

• We define a list of negative words.
• Then for each company, we calculate how many complaint narratives had the given negative word in the year of 2015.
• For this we use COUNTIFS formula with wildcards.
• We calculate total complaints with any narrative and then figure out the frustration %.

YoY complaint growth: 

• This is easy. We just look at the yearly complaint numbers for each company and figure out how many YoY growths the company had.

Negative resolution %: 

• Set up a pivot table of complaint count by year, company and resolution.
• We define a list of negative resolutions too.
• Then calculate how many complaints had negative resolutions using array SUM formula

Dispute %:

• Using a COUNTIFS formula, we calculate how many complaints had [Consumer Disputed?]=”Yes” for a given year & company combination.

Calculating the final satisfaction score

Now that all of the individual components are calculated, we need to calculate one final score (out of 10).

For this we need to:

• Calculate PERCENTRANK for each factor among all the top 15 companies. This will give you a percentage (0 to 100%).
• We then weight each factor by the weightage assigned to them
• We then add up all these weighted factors and multiply by 10 to get a number from 0 to 10.
• This is the final satisfaction score of each company.

Designing the scorecard

For the scorecard, let’s go with the theme of My bank vs. Your bank. This will allow us to compare 2 companies at a time and understand their customer satisfaction scores along with few other details.

Apart from the score, rank, break-up by criteria, let’s also add:

• Summary of complaints – 2015 count, 3 year trend, 12 month trend.
• Complaints by state heatmap
• Complaint mode summary

We can use COUNTIFS formula to calculate the complaint totals by state & mode.

Few other things that need:

• Selection of two companies using combo box form control.
• Annual & monthly complaint trend using sparklines.
• State level complaints using conditional formatting heatmaps & picture links.
• Complaint mode summary using conditional formatting data bars

Customer Satisfaction Scorecard

Here is the final version of the scorecard.

Quick video demo of the scorecard

Check out a quick demo & explanation of the scorecard below. You can also see this on our YouTube channel.

Download Satisfaction Scorecard workbook

Click here to download customer satisfaction scorecard workbook. Play with the form controls, modify the settings, examine the calculations & pivots to learn more.

Note: this file is 55 MB. So give it a few minutes to download.

How would you analyze the complaint data?

Go ahead and play with this data yourself. How would you analyze it? Please share your ideas, analysis & charts in the comments section. If you wish to publish a chart, email it to chandoo.d@gmail.com with the subject “Half a million complaints”. I will add your chart at the end of this post.

Learn more about data analysis & reporting

I cover this topic and 50 other case studies in my online class 50 ways to analyze data. If you are keen to learn advanced analytics and data science thru Excel, I highly recommend 50 ways course. Check it out here.

For something less intense, but still as much fun and detailed, check out Excel School program. This course teaches intermediate to advanced Excel with specific focus on Dashboard Reporting. Check out Excel School here.