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

Posted on February 23rd, 2016 in Charts and Graphs , Cool Infographics & Data Visualizations , Learn Excel - 11 comments

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

### A little background

How would you analyze data when you have lots of it? That is the inspiration for this series. We are re-opening enrollments to the 50 ways to analyze data program in a few days (on 24th of February, Wednesday). I want to share the process, techniques and visualizations you can use to analyze any business data with a case study. In this series of articles, let’s analyze fairly complex and large data set to derive insights.

## 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 will explain this process in a future article)

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 %:

• We set up a pivot table of complaint count by year, company and resolution.
• We define a list of negative resolutions too.
• We 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:

## Customer Satisfacation 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.

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.

### Introducing our Online Power BI Class:

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

### 11 Responses to “Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]”

1. Ardraaken says:

Thanks Chandoo, another great tutorial.

I was particularly interested in the text analysis and the way you did this.

I looked at sentiment analysis using my fairly basic VBA knowledge a while ago and created a UDF to calculate a satisfaction score based on analyising all the words in the sentence. I found a list of positive and negative words online and used these as my lookup lists with positive scores for good words and negative scores for bad words. I also analysed the preceeding two words when I found a match to see if one of the words increased the sentiment (e.g. words like 'very' and 'extremely') and if the words flipped the sentiment (e.g. 'not' bad = good). Due to the large list of words populating the document it's not exactly portable but it worked okay (although it doesn't deal well with sarcasm!).

2. PhilC says:

Hi, wondering why in the Summary of Complaints section the 2015 Count is formatted the way it is? Typo or valid reason?

• Chandoo says:

No typo. As I could not fit the full number in column, I formatted it in thousands.

3. Joe Thompson says:

Awesome stuff! Is there a single file (pdf, for example) with the text of the 3 parts available?

• Chandoo says:

Not yet Joe. I plan to give single PDF along with additional commentary and companion videos as part of the 50 ways program course content.

4. Mayur Gijam says:

Hi I am wondering how you guys can keep inspiring with the knowledge you have 🙂
I have one small question, In Pivot tabs how the Row Labels for Year Received are duplicated as in I know from Field Setting we can set Show item Labels in tabular form but It couldn't duplicate them down. I tried hell out me but couldn't reflect the exact pivot that you guys have created. I use Excel 2007. Can you please tell me 🙂

• Chandoo says:

I no longer have Excel 2007 or 2010 on my work computers to test, but I am using a feature called "repeat row labels" in pivot design ribbon. May be it was added in 2010...?

• Mayur Gijam says:

Thanks alot for your comment... You are awesome as always 🙂

After saw your excel file understand that, How better we can use the formula to get better result.

countifs with finding disappointing count.
In Heatmap sheet using name bring company name Dynamic
Index formula with conditional formatting

really awesome, this make me to feel associate with you always. If I get changes to work along with you, that will be great.

6. Joe Thompson says:

Can you explain why the benchmark is included in the percentile ranking formula? In other words, why is Calc!I76 =PERCENTRANK.INC(C\$76:C\$92,C76) and not =PERCENTRANK.INC(C\$76:C\$90,C76)?

7. Cap D says:

.God Bless You Chandoo!

 Analyzing half a million customer complaints – Regional Trends [Part 2 of 3] Please join 50 ways to analyze data course to become an awesome analyst