When Average Isn't Good Enough

One of the numerous advantages an email management solution such as VisNetic MailFlow has over standard email clients are the abundant reporting options.

VisNetic MailFlow offers a range of reports that provide insight into the flow of internal email communications. These reports can be used to analyze open incident totals, communications history, as well as inbound/outbound email traffic.

Two such reports that draw a lot of attention by CSMs are Average Response Time and Average Time to Resolve. We're often asked the differences between the two.

Average Response Time - compares the time the first inbound message was received to the time the first outbound message (reply) was sent, then averages that over the time frame of the report.

So if you are looking at Avg Response Time for a month, it would average the above time difference for the specified TicketBox(s) over the time frame of a month.

As example, if an Inbound Message is received into the system and a Ticket is generated at 2:00 PM and then an Agent adds and Outbound Message to the Ticket (a reply) at 4:30 PM the response time for that particular ticket would be 150 minutes.

These calculations are repeated to provide an Average Response Time across the period.

Average Time to Resolve - takes the OpenMins column (in the DB) from the Ticket and averages it as above. The OpenMins column is incremented as the Ticket changes state (open/closed).

As example, an Inbound Message is received into the system at 2:00 PM and a Ticket is generated. At this time the OpenMins column in the Ticket is set to 0.

At 4:30 PM an Agent adds an Outbound Message to the Ticket and the Ticket is closed. The OpenMins column is set to 150 mins.

The next day at 9:00 AM the originator responds to the Agents reply and the Ticket is opened back up with the addition of the new Inbound Message.

At 10:15 AM and Agent adds an Outbound Message to the Ticket (a reply) and the Ticket is set to Closed. The 75 mins the Ticket was open the second time is added to the original 150 mins resulting in an OpenMin setting of 225 mins for the Ticket. If the Ticket is never opened again, this will be the final Time To Resolve for this Ticket. If the Ticket is opened again, the OpenMins will be adjusted the next time the Ticket transitions to the Closed state.

Knowing the teams average response time for the week is sufficient for most, however some clients require additional detail such as:

• How long did it take for our people to respond to each individual Ticket?
• What Tickets were replied to within our SLA of 10 minutes? 
• Which Tickets did not meet our SLA, and by how much?

The benefit of using an open, standards-based data base like SQL means custom queries may be defined to extract and present the information in usable formats such as TXT, CSV, or XML.

Here are examples of three custom queries that can be ran against the data base using standard Microsoft SQL Server Management Tools.

(Variables highlighted in Red)
Example 1 - Query for messages not replied to in 10 minutes.
SELECT TB.Name, I.TicketID, I.Subject, I.InboundMessageID, I.DateReceived, O.EmailDateTime, DATEDIFF(MINUTE,I.DateReceived, O.EmailDateTime) As MinutesToRespond, A.Name As Agent
FROM InboundMessages as I 
INNER JOIN OutboundMessages as O ON I.InboundMessageID = O.ReplyToMsgID 
INNER JOIN Tickets as T ON I.TicketID = T.TicketID
INNER JOIN TicketBoxes as TB ON T.TicketBoxID = TB.TicketBoxID
INNER JOIN Agents as A ON O.AgentID = A.AgentID
WHERE I.IsDeleted=0 AND O.IsDeleted=0 AND O.ReplyToIDIsInbound=1
AND OutboundMessageStateID!=1 AND OutboundMessageTypeID=3
AND O.EmailDateTime>I.DateReceived AND O.AgentID!=0
AND I.DateReceived BETWEEN '6/1/2013 00:00:00' AND '6/30/2013 23:59:59'
AND TB.Name IN ('Sales','Support','Info')
AND DATEDIFF(MINUTE,I.DateReceived, O.EmailDateTime) > 10
ORDER BY I.DateReceived

Example 2 - Query for messages replied to in less than 10 minutes.
SELECT TB.Name, I.TicketID, I.Subject, I.InboundMessageID, I.DateReceived, O.EmailDateTime, DATEDIFF(MINUTE,I.DateReceived, O.EmailDateTime) As MinutesToRespond, A.Name As Agent
FROM InboundMessages as I 
INNER JOIN OutboundMessages as O ON I.InboundMessageID = O.ReplyToMsgID 
INNER JOIN Tickets as T ON I.TicketID = T.TicketID
INNER JOIN TicketBoxes as TB ON T.TicketBoxID = TB.TicketBoxID
INNER JOIN Agents as A ON O.AgentID = A.AgentID
WHERE I.IsDeleted=0 AND O.IsDeleted=0 AND O.ReplyToIDIsInbound=1
AND OutboundMessageStateID!=1 AND OutboundMessageTypeID=3
AND O.EmailDateTime>I.DateReceived AND O.AgentID!=0
AND I.DateReceived BETWEEN '6/1/2013 00:00:00' AND '6/30/2013 23:59:59'
AND TB.Name IN ('Sales','Support','Info')
AND DATEDIFF(MINUTE,I.DateReceived, O.EmailDateTime) < 10
ORDER BY I.DateReceived

Example 3 - Query that will return a list of Tickets that have NOT been responded to.
SELECT TB.Name, I.TicketID, I.Subject, I.InboundMessageID, I.DateReceived
FROM InboundMessages as I 
INNER JOIN Tickets as T ON I.TicketID = T.TicketID
INNER JOIN TicketBoxes as TB ON T.TicketBoxID = TB.TicketBoxID
WHERE I.IsDeleted=0 AND I.InboundMessageID NOT IN (SELECT ReplyToMsgID FROM OutboundMessages)
AND I.DateReceived BETWEEN '6/1/2013 00:00:00' AND '6/30/2013 23:59:59'
AND TB.Name IN ('Sales','Support','Info')
ORDER BY I.DateReceived

Our engineers can work with you to define a query to extract just about anything. If we're recording the data, we can create the custom report.

Questions?  Send the details to sales at visnetic.com