Representative Ranked by CSAT

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
I'm having a rough time with this one. Buckle up: I need to create a ranking of our support team based on their aggregated customer satisfaction (CSAT) score from helping customers based on data we collect from surveys. The important data we collect from a survey - for this exercise - is as follows:
  • Taken On (Survey completion Date)
  • String Value (3 questions)
    • Metric#1: choice of A (Excellent), B (Neutral), or C (Needs Improvement)
    • Metric#2: choice of A (Excellent), B (Neutral), or C (Needs Improvement)
    • Metric#3: Yes/No
  • Agent (Who served the client)
What I need to calculate is:

1. CSAT per survey: calculated by taking the the number of positive answers divided by the number of questions. Negative and Neutral are not needed in this equation to find CSAT.
2. Agent average CSAT in last 30 days: Each agent name and their average CSAT score
3. Agent average CSAT in last 90 days: Each agent name and their average CSAT score

Still with me? o_O

The data from the survey will look like this in a separate worksheet:
ABCDEFGHIJK
1InstanceIDTaken onAssigned toCompanyCategoryMetric #Metric ValueString ValueGroupAgent
2AINST0042251INC07132224/21/20Bob SmithAcmeCustomer Satisfaction Survey1Quality of Service?A - ExcellentSystemsJane Doe
3AINST0042251INC07132224/21/20Bob SmithAcmeCustomer Satisfaction SurveyCustomer Satisfaction Survey2Support engineer?A - ExcellentSystemsJane Doe
4AINST0042251INC07132224/21/20Bob SmithAcmeCustomer Satisfaction Survey3Resolved to my satisfaction?YesSystemsJane Doe
6AINST0013764
INC0231676

7/3/17

Peter Pan
Unreal CoCustomer Satisfaction Survey1Quality of Service?C - Needs ImprovementUC TeamRick Ross
7AINST0013764
INC0231676

7/3/17

Peter Pan
Unreal CoCustomer Satisfaction Survey2Support engineer?C - Needs ImprovementUC TeamRick Ross
8AINST0013764INC0231676
7/3/17

Peter Pan
Unreal CoCustomer Satisfaction Survey3Resolved to my satisfaction?NoUC TeamRick Ross

And I want it to populate like this on a separate worksheet where it ranks the top performers (say 15 of the 50 we have):

Screen Shot 2020-04-24 at 4.50.53 PM.png


How do I get there???????? I'm guessing it is a series of COUNTIFS with DATE ... ="&TODAY()-30 ... dependencies but not sure past that. Especially with tying it to Agent names when it could change one day to the next with new agents.

I hope this is enough info. Any help is SUPER appreciated.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

samitnair

Board Regular
Joined
Jul 5, 2010
Messages
155
Hello

Below formula will give you the count and they you can average it to get the CSAT%. I didnt understand how we reached the number 98 & 91 in the CSAT by CMC representative.

VBA Code:
=SUM(COUNTIFS($D$2:$D$11,A15,$I$2:$I$11,{"A - Excellent","Yes"}))

=SUM(COUNTIFS($D$2:$D$11,A15,$I$2:$I$11,{"A - Excellent","Yes"},C2:C11,">="&TODAY()-90))

1587848802633.png
 

Attachments

  • 1587848699658.png
    1587848699658.png
    10.8 KB · Views: 2

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
@samitnair thank you. Great start! However I think this may require some more explaining on my part. Your codes make sense, but only if the name in column A is manually populated by me and static.

I'd like it to automatically rank the names based on their CSAT score in tandem. From highest to lowest. As well as tie in their 90day CSAT for those people.

SO I think we have to have a code for column A and C that associates to column B. And then have that table (A,B and C) rank based on Column B from highest to lowest.

What do you think? And how do I do that??? :)
 

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
Anyone have any ideas? Need a ranking system based on their score calculated from Metric# 1 and 2 in Column G.

For the ranking table; Column A will have their name, B their current 30d score, C their 90d score - all pulled from the example data spreadsheet I provided.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top