# Best Way to Aggregate further and Analyze a Data Point that is Already an Average

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: 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.

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!
• 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. Mar 15, 2019 at 0:23