Representative Ranked by CSAT

[indiemusicboy]

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?

The data from the survey will look like this in a separate worksheet:

	A	B	C	D	E	F	G	H	I	J	K
1	Instance	ID	Taken on	Assigned to	Company	Category	Metric #	Metric Value	String Value	Group	Agent
2	AINST0042251	INC0713222	4/21/20	Bob Smith	Acme	Customer Satisfaction Survey	1	Quality of Service?	A - Excellent	Systems	Jane Doe
3	AINST0042251	INC0713222	4/21/20	Bob Smith	Acme	Customer Satisfaction SurveyCustomer Satisfaction Survey	2	Support engineer?	A - Excellent	Systems	Jane Doe
4	AINST0042251	INC0713222	4/21/20	Bob Smith	Acme	Customer Satisfaction Survey	3	Resolved to my satisfaction?	Yes	Systems	Jane Doe
6	AINST0013764	INC0231676	7/3/17	Peter Pan	Unreal Co	Customer Satisfaction Survey	1	Quality of Service?	C - Needs Improvement	UC Team	Rick Ross
7	AINST0013764	INC0231676	7/3/17	Peter Pan	Unreal Co	Customer Satisfaction Survey	2	Support engineer?	C - Needs Improvement	UC Team	Rick Ross
8	AINST0013764	INC0231676	7/3/17	Peter Pan	Unreal Co	Customer Satisfaction Survey	3	Resolved to my satisfaction?	No	UC Team	Rick 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):

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.

 

[samitnair]

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.

=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))

 

[indiemusicboy]

@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]

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.