indiemusicboy
New Member
- Joined
- Apr 9, 2020
- Messages
- 47
- Office Version
- 365
- Platform
- 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:
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:
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.
- 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)
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.