Get best weighted customer satisfaction grade

[Afarag]

Hello,

please I need help as I have a data for a call center answering call survey, that survey set by a customer to get us his satisfaction grade about his waiting on Queue till call center agent answer his call, I have 4 Inputs:

• Group of Customer
• Average waiting time for customers on the Queue divided per each 10 seconds
• Satisfaction grade divided into (Neutral - Satisfied - Unsatisfied - Very Satisfied - Very Unsatisfied)
• the number of customers per Average waiting time & Satisfaction grade

what I looking for is how can I set the best Neutral waiting per each group based on the numbers of rated customers and its Satisfaction grade composition. the best neutral waiting: it's mean the accepted waiting time which should I apply for each group. I need to get the accepted average waiting time, as at the little waiting time I found a huge number of Unsatisfied Customer, that's mean isn't the effective solution to highly decrease the waiting time and increase the cost to find those Unsatisfied customers.

I ask for a function which gets the best-accepted waiting time based on the number of distributed grades among the customer.

At first glance, you look that function is easy to apply, but the data preview some findings which didn't make sense as an example you can see a huge number of customers are Very Unsatisfied even though they wait a little time on Queue till answer there call.
I know the request could be complicated somehow or not, so please if there is any misunderstanding let me know

Gratefully,

 

[Chihiro]

I'd not recommend setting ASA threshold based on survey. As you found out, results are quite unreliable. As customer's reason for the call will influence their response.

Ex. If customer is upset with product/service provided, they will not answer favourably to the survey even though the question has nothing to do with the product/service rendered.

Rather, use call abandonment ratio to determine optimal ASA threshold.

 

[Afarag]

Hello Chihiro,

I totally agree with you. but the survey wasn't based on ASA only, the ASA was just a question, and there were questions related to the product/service and other quantifications, but my team works on ASA plan, and how to optimise the service level and control the call center headcount, I need a strategy or a logical function to help me on getting the best-accepted waiting time for further surveys, perhaps that surveys come with different or redistributed results.

Gratefully.

 

[p45cal]

I see that the big table on Result sheet is actually a pivot table based on data in a file I haven't got (CSI.xlsx) so I took the liberty of recreating that source data (more or less) on Sheet1 of the attached.
I've added a new pivot table near cell I18 of the Result sheet which uses the data on Sheet1 which shows the average of [ASA10] for each group, confining that average to Neutral satisfaction.
What worries me a bit is the use of the word 'weighted' in the title of this thread. How might you want the result to be weighted?

 

[Chihiro]

... I need a strategy or a logical function to help me on getting the best-accepted waiting time...

There is no logical way to achieve this based on survey design and current data set alone. No matter how you calculate, the result is going to be skewed and will not provide you with actual "best" accepted waiting time. To get an accurate estimate, rather than basing it on active user input, base it on passive user behavior (without redesigning the survey).

ASA analysis using this data has several flaws.

1. Biggest issue is that, it is based on actual wait time. So, if majority of calls are answered during certain time-frame it will skew result (take Tech_SA_New for example). If you want meaningful result, you will need large enough sample size for each 10 sec time-frame.

2. As stated in my original response, customer's reason for calling will skew result. You will need further analysis based on other factors. For an example, how does customer response to ASA vary based on their response to other question (ex. Overall satisfaction for the call, First Call Resolution etc).

3. There's no established error margin that I can tell from your data. You will need some control built into survey process unless there's well established methodology (from which you can infer error margins)

 

[bobhc]

@[URL='http://forum.chandoo.org/members/afarag.4385/']Afarag[/URL]
http://www.mrexcel.com/forum/excel-...est-weighted-customer-satisfaction-grade.html
CROSS POSTING
If you took the time to read the rules you would have read this section:-

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

You have done none of the above, if you cannot be bothered with forum rules then perhaps you should use one of the many forums that care for a no rule open house mess.

You have cross posted here:-


http://www.mrexcel.com/forum/excel-...est-weighted-customer-satisfaction-grade.html

http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/get-best-value/b8155b6a-fcb8-4555-9cf8-32086d574095

.