This customer wants to know exactly how long it took to answer each individual phone call. This example uses Google Sheets and pivot tables to determine the custom SLA.
HOW TO DETERMINE SLA PER PHONE CALL
- Generate a Company CDR sheet for the amount of time you want to analyze, for this example we are using a month.
- Create an Answered Calls pivot table for a month with:
- Rows Start Time, Answer Time, To User, Duration, and From.
NOTE: When filtering data based on specific users or extensions, it is recommended to use the "To User" and "From User" rows instead of "To" and "From" because the "To" and "From" values sometimes show the sip domain and sometimes show the caller ID number. - Filters By Sub (the company or user you want to analyze), To User (All), and Answer Time (All, EXCEPT Blanks).
- Rows Start Time, Answer Time, To User, Duration, and From.
- Apply the Answer Speed using the formula: "=sum(B#-A#)*86400"
-
Create an Abandoned Calls pivot table for a month with:
- Rows Start Time, To User, Duration, and From.
-
Filters By Sub (the company or user you want to analyze), To User (All), and Answer Time (Blanks).
NOTE: Having a "Blank" answer time filter will generate unanswered/abandoned calls.
-
From here the SLA formula can be applied on a per-call basis.