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.
- Complaint reason analysis – Part 1
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
- 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 firstname.lastname@example.org 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.
13 Responses to “Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]”
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!).
Hi, wondering why in the Summary of Complaints section the 2015 Count is formatted the way it is? Typo or valid reason?
No typo. As I could not fit the full number in column, I formatted it in thousands.
Awesome stuff! Is there a single file (pdf, for example) with the text of the 3 parts available?
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.
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 🙂
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...?
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.
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)?
.God Bless You Chandoo!
After a few years, once the succeeding consequences
were over, we halted collectively.
Love this website. I am wondering how these types of
dashboards can be used in court hearings to help the judge visualize how one person is telling the truth (based on evidence) and how another person is lying (based on forged evidence, how many times they filed
Frivolous court motions, changing statements they made at different hearings based on transcript, etc. I would probably have to add calendars or timelines?