0
$\begingroup$

I want to understand how much time (on average) each Customer Service Agent (CSA) is taking to reply to messages that are sent by a Customer on a single chat.

E.g., "Charles took on average, 22s to reply to Customer messages during chat id: 1224323." Now, there's a catch - my database only contains the following stats:

  • Communication ID: unique identifier of a chat contact.
  • Creation Date: date when the chat was received (UTC).
  • Average Message Response time of Agent in seconds: I.e., Average time the CSA took to reply to the messages sent by the customer on that chat contact.
  • Average Message Response time of Customer in seconds: I.e., Average time the Customer took to reply to the messages sent by the CSA on that chat contact.
  • Number of messages sent by the CSA: Count of messages sent by CSA during that chat contact.
  • Number of messages sent by the Customer: Count of messages sent by Customer during that chat contact.
  • Total Handle Time: The total time in seconds that the chat contact lasted.

It looks just like this: sample rows of database Now, it would in my interest to compute the Average Response Time of all the chats handled by a CSA in an specific time interval.

Instead of having the Average Message Response Time for a single contact, I'd like to somehow aggregate the Average Response Time to the agent level.

I can't do AVG(Average Message Response time of Customer) because that would be extremely inaccurate (wouldn't it?).

With the information that I have available:

  • What would be an accurate approximation to quantify the Average Time a CSA is taking to Reply to a Customer?

  • How can I rank Agents to find out, the Agents that are in the Bottom 20%.

  • What useful calculations can I perform to understand how each agent compares against the others?

I appreciate your input, if something is unclear or I missed something important, please point it out.

$\endgroup$
0
$\begingroup$

The Average Time a CSA is taking to Reply to a Customer would be given by something like $$\frac{1}{n_{total}}\sum_{i\leq n_{total}}t_i,$$ where $t_i$ is the time the agent took to answer the $i$-th message (including those from all customers) and $n_{total}$ the number of messages he got. If there were C customer calls, what you have is a list of averages like the following: $$\frac{1}{n_{customer 1}}\sum_{j\leq n_{customer1}}t_j\quad,...,\quad\frac{1}{n_{customer C}}\sum_{j\leq n_{customerC}}t_j,$$ where $n_{customer1}$ is the number of messages sent by customer 1, which I see you also have. What do you get when you multiply each of those contact averages by $n_{customerX}$? That should be enough of a hint!

$\endgroup$
  • $\begingroup$ Hi, I unfortunately that wouldn't work -an agent will often times reply with more than one message. So multiplying the Avg Resp. Time * n agent messages would not be accurate. That's why I need a workaround to aggregate to the agent level in order to be able to rank agents against each other. $\endgroup$ – Daniel Segura Mar 15 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.