Skip to main content

Calculating customer service metrics

  • Updated

Index:

  1. Why do I need to calculate customer service metrics?
  2. Wich metrics can I calculate?
  3. General information for calculations
  4. Calculations
  5. Alternative formulas
  6. Related articles

 

Why do I need to calculate customer service metrics?

If you want or have the need to create custom indicators in an external tool to Blip, or even filter information that is not possible to filter within the tool, you can do that by extracting the raw customer service data and building the necessary metrics in a spreadsheet or data tool.

 

Which metrics can I calculate?

In this article, we will cover how to calculate the customer service metrics delivered by Blip. Here are the metrics:

 

Time Metrics:

  • Maximum time waiting in queue
  • Maximum time to 1st response
  • Average waiting time in queue
  • Average time until 1st response
  • Average total waiting time
  • Average Response Time
  • Average Service Time

Volume Metrics:

  • Open Tickets
  • Missed Tickets
  • Abandoned Tickets
  • Handled Tickets
  • Closed Tickets

Note: To obtain the raw data for customer service, you need to extract the ticket report from the report manager. To learn how to extract the data, access the article "Customer Service Reports."

 

General Information for Calculations

Understanding the Columns:

  • BotId: provides the identification of the bot where the service occurred.
  • TicketId: brings the unique code identifier of the ticket for the system.
  • SequentialId: provides the sequential number identifier of the ticket, visible to the attendant.
  • CustomerIdentity: brings the customer's unique code identifier for the system.
  • AgentIdentity: provides the unique code identifier of the attendant for the system.
  • Status: indicates the status of the ticket, which can be:
    • Waiting: the ticket is created with this status and remains in it while waiting in the service queue to be assigned to an attendant.
    • Assigned: This status represents tickets that are in the service queue but already have an attendant assigned. This only happens when automatic distribution is enabled.
    • Open: This status represents tickets that have left the service queue and have been assigned to an attendant.
    • Transferred: represents tickets that have been transferred to another queue or attendant. It is considered a closing status since transferring a ticket closes it and opens another one.
    • ClosedAttendant: are the tickets closed by the attendant at the desk or by the manager via the monitoring screen.
    • ClosedClient: are the tickets that were closed by the clients through the exit condition registered in the service box.
    • ClosedClientInactivity: represents tickets closed automatically due to client inactivity, when the option is configured in the portal and builder.
  • StorageDate: represents the date and time of ticket creation.
  • OpenDate: this column indicates the date and time the ticket was assigned to an attendant, either by automatic distribution or manually.
  • FirstResponseDate: this column shows the date and time when the customer received the first response from the attendant.
  • CloseDate: represents the date and time when the ticket was closed.
  • ExpirationDate: This column will always be empty, disregard it.
  • Team: this column informs the queue in which the ticket was serviced.
  • Closed: shows true and false statuses that refer to the removal of the ticket from the attendant's screen. If it is true, it means that the ticket has already been removed from the attendant's screen. If it is false, then the ticket is still open on the attendant's screen.
  • Tags: this column informs the tags included in the ticket after closing.
  • ParentSequentialId: this column brings the number of the source ticket that generated the new ticket after the transfer, it serves to track transferred tickets.
  • QueueTime: this column represents the waiting time in the queue, which is the time the ticket spent waiting in the service queue, calculated as the difference between the columns "OpenDate" and "StorageDate."
  • FirstResponseTime: indicates the first response time, which is the time the attendant took to give the first response to the ticket, calculated as the difference between the columns "FirstResponseDate" and "OpenDate."
  • AverageAgentResponseTime: this column provides the average response time, which is the average time the attendant takes to respond to client messages. This data will only be available in tickets that had at least one complete interaction, meaning a message from the client followed by a message from the attendant.
  • OperationalTime: this metric represents the service time, meaning the time the ticket took to be serviced, considering from the moment the attendant sent the first response until the moment of closing, calculated as the difference between the columns "CloseDate" and "FirstResponseDate."
  • TicketTotalTime: this column informs the total life time of a ticket, meaning the time between the creation and closing of the ticket, calculated as the difference between the columns "CloseDate" and "StorageDate."
  • CustomerName: this column provides the name of the customer who was serviced in the ticket if provided in the contact data.
  • CustomerEmail: this column provides the email of the customer who was serviced in the ticket if provided in the contact data.
  • CustomerGender: this column provides the gender of the customer who was serviced in the ticket if provided in the contact data.
  • CustomerCity: this column provides the city of the customer who was serviced in the ticket if provided in the contact data.
  • CustomerPhone: this column provides the phone number of the customer who was serviced in the ticket if provided in the contact data.
  • CustomerExtras: this column provides the data from the customer's extra fields who was serviced in the ticket if provided in the contact data.
  • AgentName: brings the name of the attendant who served the ticket.
  • AgentEmail: brings the email of the attendant who served the ticket.

Format Conversion

Before performing calculations and analysis, some adjustments are necessary in the data formats so that data readers can identify the information. We will see how to make these conversions below.

Note: The following steps consider the Google Sheets tool in the Portuguese language. If you use another spreadsheet editor or another language, the commands or formulas may need to be adapted.

 

Date and Time

The data containing date and time, located in the columns StorageDate, OpenDate, FirstResponseDate, and CloseDate, are delivered in the format 2023-04-17 17:01:41.12 (YYYY-MM-DD hh:mm:ss.ms), and we need them in the format 17/04/2023 17:01:41 (DD/MM/YYYY hh:mm:ss). To convert them, follow the steps below. We will use the StorageDate column as an example, but the steps for the other columns in this format are the same.

  1. After downloading the data to your computer, open Google Sheets.
  2. Create a new spreadsheet.
  3. Import the file with the ticket data.
  4. After importing, go to the StorageDate column.
  5. Select the entire column and right-click.
  6. Choose the option "+ Insert 1 right."
  7. Select the StorageDate column again.
  8. With the column selected, go to the "Data" menu.
  9. Then select the option "Split text into columns."
  10. Change the separator option from "Automatic detection" to "Dot" and click on any cell.
  11. Select the newly created column, which now contains the milliseconds.
  12. Right-click on the column and then click "Delete column."
  13. Select the StorageDate column again.
  14. Go to the "More formats" menu, represented by the icon with the numbers 123.
  15. Then select the "Date time" format.
  16. Done! The StorageDate column is now in the correct format.
  17. Repeat these steps for the OpenDate, FirstResponseDate, and CloseDate columns.

 

Time

The data containing time values, located in the columns QueueTime, FirstResponseTime, AverageAgentResponseTime, OperationalTime, and TicketTotalTime, are delivered in the format 1d 01:45:03 (D hh:mm:ss), and we need them in the format 25:45:03 ([HH]:mm:ss). To convert them, follow the steps below. We will use the QueueTime column as an example, but the steps for the other columns in this format are the same.

1. After downloading the data to your computer, open Google Sheets.

2. Create a new spreadsheet.

3. Import the file with the ticket data.

4. After importing, go to the QueueTime column.

5. Select the entire column and right-click.

6. Choose the option "+ Insert 1 right."

7. In the new column, go to the cell in row 2 and enter the formula:

  1. =TEXT(LEFT(A2, SEARCH("d", A2) - 1) * 24 + HOUR(MID(A2, SEARCH(" ", A2) + 1, LEN(A2))), "00") & MID(A2, SEARCH(":", A2), LEN(A2))
  2. You need to change the A2 fields in the formula to the field where the information we want to process is located, which is the 2nd cell in the QueueTime column, represented by cell P2 in the example.
  3. If your columns are in the same order, the formula should look like this: =TEXT(LEFT(P2, SEARCH("d", P2) - 1) * 24 + HOUR(MID(P2, SEARCH(" ", P2) + 1, LEN(P2))), "00") & MID(P2, SEARCH(":", P2), LEN(P2))

8. After adjusting the formula, press enter to apply.

9. Then copy the cell to the entire column.

  1. . In cells where the information in the QueueTime column is empty, an error result will be displayed as "#VALUE!", and this data should be deleted, leaving the cells blank.

10. Select the QueueTime column.

11. Go to the "More formats" menu, represented by the icon with the numbers 123.

12. Then select the "Custom number format" option.

13. Enter the format [HH]:mm:ss and click apply.

14. The next step is to copy the column with the converted data.

15. Then paste the data without formatting into the QueueTime column using the ctrl+shift+V command or the Edit>Paste special>Values only menu.

16. Finally, delete the additional column.

17. Done! The QueueTime column is now in the correct format.

18. Repeat these steps for the FirstResponseTime, AverageAgentResponseTime, OperationalTime, and TicketTotalTime columns.

 

Calculations

Time Metrics

In this section, we will learn how to calculate the metrics related to the handling times of the tickets, and it's essential to have the data in the formats indicated in the content above.

Maximum time waiting in queue

This metric is used to find the ticket where the customer spent the longest time waiting in the queue to be assigned to an attendant during the selected period. This metric considers the following conditions: Tickets closed during the period that have already been removed from the attendant's screen.

To find this value, follow these steps:

  1. Filter the desired period in the table using the CloseDate column.
  2. Filter to show only tickets with the status "True" in the Closed column.
  3. Then, simply find the highest time value presented in the QueueTime column.

 

Maximum time to 1st response

This metric is used to find the ticket where the customer waited the longest time to receive the first response from the attendant after being assigned to them during the selected period. This metric considers the following conditions: Tickets closed during the period that have already been removed from the attendant's screen.

To find this value, follow these steps:

  1. Filter the desired period in the table using the CloseDate column.
  2. Filter to show only tickets with the status "True" in the Closed column.
  3. Then, simply find the highest time value presented in the FirstResponseTime column.

 

Average waiting time in queue

This metric is used to find the average time that customers are waiting in the queue until being assigned to an attendant during the selected period. This metric considers the following conditions: Tickets closed during the period that have already been removed from the attendant's screen.

To find this value, follow these steps:

  1. Filter the desired period in the table using the CloseDate column.
  2. Filter to show only tickets with the status "True" in the Closed column.
  3. Filter the QueueTime column to exclude any blank results.

Note: When a ticket is waiting in the queue to be attended and is transferred to another queue through the monitoring screen, this ticket will not have a queue waiting time as it was not assigned to an attendant.

4.Then, simply calculate the average of the times presented in the QueueTime column.

 

Average time until 1st response

This metric is used to find the average time that attendants are taking to send the first response to customers after having the ticket assigned to them. This metric considers the following conditions: Tickets closed during the period, which received the first response and have already been removed from the attendant's screen.

To find this value, follow these steps:

Filter the desired period in the table using the CloseDate column.

Filter to show only tickets with the status "True" in the Closed column.

Filter the FirstResponseTime column to exclude any blank results.

Note: If a ticket is assigned to an attendant and is closed without receiving any message from the attendant, it will not have a first response time. Therefore, these tickets are not considered in the calculation.

4. Then, simply calculate the average of the times presented in the FirstResponseTime column.

 

Average total waiting time

This metric is used to find the average time that customers are waiting until they are attended by an attendant after the bot's handover. This metric considers the following conditions: Tickets closed during the period, whether or not they received the first response, and have already been removed from the attendant's screen. To calculate this metric, we will look at three columns: StorageDate, FirstResponseDate, and CloseDate.

To find this value, follow these steps:

1. Insert a new column in the table.

2. In the first row, label it with an identifier; for example, let's call it "averagewaitingtime."

3. The next step will involve the following calculation:

  1. For tickets that have already been closed and received the first response, calculate the difference between the FirstResponseDate column and the StorageDate column.
  2. For tickets that were closed but did not receive the first response, calculate the difference between the CloseDate column and the StorageDate column.

4. To do this efficiently, use the following formula: 

   =IF(AND(I2="", J2=""), "", IF(I2<>"", I2-G2, J2-G2)), where:

  1. G2 represents the StorageDate column.
  2. I2 represents the FirstResponseDate column.
  3. J2 represents the CloseDate column.
  4. If your columns are in a different order, adjust the cell references in the formula to correspond to the desired columns.

5. Apply the formula to cell 2 of the "averagewaitingtime" column.

6. Then drag the formula down to apply it to the rest of the column.

7. Select the "averagewaitingtime" column.

8. Go to the "More formats" menu, represented by the icon with the numbers 123.

9. Then select the "Custom number format" option.

10. Enter the following format: [HH]:mm:ss and click apply.

11. The next step is to copy the "averagewaitingtime" column.

12. Then paste the unformatted data into the same "averagewaitingtime" column using the ctrl+shift+V command or by going to Edit>Paste special>Values only.

13. Next, filter the desired period in the table using the CloseDate column.

14. Filter to show only tickets with the status "True" in the Closed column.

15. Finally, calculate the average of the times presented in the "averagewaitingtime" column.

 

Average Response Time

This metric serves to find the average time that agents take to respond to customer messages. The calculation considers the following conditions: Tickets closed within the selected period that had at least one complete interaction (customer message + agent message, in that order) and have already been removed from the agent's screen.

To find this value, follow these steps:

16. Filter the desired period in the table using the CloseDate column.

17. Filter to show only tickets with the status "True" in the Closed column.

18. Filter the AverageAgentResponseTime column to exclude blank results.

Note: If a ticket was closed without having a complete interaction (customer message + agent message, in that order), it will not have a response time and will not be considered in the calculation.

19.Then simply calculate the average of the times presented in the AverageAgentResponseTime column.

 

Average Handling Time

This metric serves to find the average handling time of the tickets, considering the time from when the agent sends the first message until the ticket is closed. The calculation considers the following conditions: Tickets closed within the selected period, which had at least one complete interaction (customer message + agent message, in that order), and have already been removed from the agent's screen.

To find this value, follow these steps:

  1. Filter the desired period in the table using the CloseDate column.
  2. Filter to show only tickets with the status "True" in the Closed column.
  3. Filter the OperationalTime column to exclude blank results.

Note: If a ticket was closed without receiving at least one message from the agent, it will not have handling time, as the agent did not spend time attending to that ticket, and therefore, it is not considered in the calculation.

4.Then simply calculate the average of the times presented in the OperationalTime column.

 

Volume Metrics

This section is focused on showing the number of tickets in each status, where:

Open Tickets

This metric serves to display the total number of tickets that were opened in the selected period. This metric considers the following condition: Tickets opened within the period.

To find this value, follow these steps:

  1. Filter the desired period in the table using the StorageDate column.
  2. Simply count the presented tickets.

 

Missed Tickets

This metric shows the tickets that were closed by the customer while they were still in the waiting queue and had not yet been assigned to an attendant.

Note: To allow customers to exit the queue before being assigned to an attendant, it is necessary to configure this condition in the flow of your intelligent contact. Learn more at this link.

This metric considers the following conditions: Tickets closed within the selected period, with a status of "ClosedClient" or "ClosedClientInactivity" and that have not been assigned to an attendant.

To find this value, follow these steps:

  1. Filter the desired period in the table using the StorageDate column.
  2. Filter the Status column to only show tickets with a status of "ClosedClient" or "ClosedClientInactivity";
  3. Filter the AgentIdentity column to bring only the tickets where AgentIdentity is equal to null.
  4. Simply count the presented tickets.

 

Abandoned Tickets

This metric shows the tickets that were closed by the customer after they were already assigned to an attendant.

Note: To allow customers to exit the conversation after being assigned to an attendant, it is necessary to configure this condition in the flow of your intelligent contact. Learn more at this link. Another way tickets can be considered abandoned is when they are closed due to customer inactivity. To enable this feature, you need to configure it in the portal and builder. Learn more at this link.

This metric considers the following conditions: Tickets closed within the selected period, with a status of "ClosedClient" or "ClosedClientInactivity" and that have been assigned to an attendant.

To find this value, follow these steps:

  1. Filter the desired period in the table using the StorageDate column;
  2. Filter the Status column to only show tickets with a status of "ClosedClient" or "ClosedClientInactivity";
  3. Filter the AgentIdentity column to bring only the tickets where AgentIdentity is not equal to null;
  4. Simply count the presented tickets.

 

Completed sessions

This metric shows the tickets that were attended and then closed or transferred to another queue or attendant.

This metric considers the following conditions: Tickets closed within the selected period, with a status of "ClosedAttendant"or "ClosedClientInactivity".

To find this value, follow these steps:

  1. Filter the desired period in the table using the CloseDate column.
  2. Filter the Status column to only show tickets with a status of "ClosedAttendant" or "ClosedClientInactivity";
  3. Simply count the presented tickets.

 

Closed Tickets

This metric shows the tickets that were closed within the selected period.

This metric considers the following conditions: Tickets closed within the selected period, with any status, and that have been removed from the attendant's screen.

To find this value, follow these steps:

  1. Filter the desired period in the table using the CloseDate column.
  2. Filter to show only tickets with a status of "True" in the Closed column.
  3. Simply count the presented tickets.

 

Alternative Formulas

Time Conversion

  • Google Sheets (English): =TEXT(LEFT(A2, SEARCH("d", A2) - 1) * 24 + HOUR(MID(A2, SEARCH(" ", A2) + 1, LEN(A2))), "00") & MID(A2, SEARCH(":", A2), LEN(A2))  
  • Excel (Portuguese): =TEXTO(ESQUERDA(A2; PROCURAR("d"; A2) - 1) * 24 + HORA(EXT.TEXTO(A2; PROCURAR(" "; A2) + 1; NÚM.CARACT(A2))); "00") & EXT.TEXTO(A2; PROCURAR(":"; A2); NÚM.CARACT(A2))  
  • Excel (English): =TEXT(LEFT(A2, FIND("d", A2) - 1) * 24 + HOUR(MID(A2, FIND(" ", A2) + 1, LEN(A2))), "00") & MID(A2, FIND(":", A2), LEN(A2)) 

Average Waiting Time Calculation:

  • Google Sheets (English): =IF(AND(I1="", J1=""), "", IF(I1<>"", I1-G1, J1-G1))  
  • Excel (Portuguese): =SE(E(I1=""; J1=""); ""; SE(I1<>""; I1-G1; J1-G1))  
  • Excel (English): =IF(AND(I1="", J1=""), "", IF(I1<>"", I1-G1, J1-G1))

 

Complementary Articles

For more information, visit the discussion on the subject in our community or the videos on our channel. 😃

Related articles