Salesforce Service Cloud comes with a great tool called Entitlements which, among other things, helps you measure your team responsiveness under the different case statuses. Personally, I find the tool to be an overkill for most of my client needs which eventually comes to:
- Tracking the total time since a case was opened until it was first picked by an agent.
- Measuring the total time under each of the case’s statuses?
- measuring how much time the case was opened waiting for the customer to get back with an answer?
- How many cases were opened after they were resolved?
- How much time the case is delayed because Tier 1 is waiting for an answer from another department?
What does it look like?
To answer the above needs I’ve created a simple solution which I use whenever I need to implement the service module.
The screenshot above shows the SLA History custom object I’ve added to the case page layout. When a new case is created or every time the status field is changed, the system creates a new record under the SLA History object.
- Total Time measures the total hours passed from the moment the status was set until it was changed again.
- Total Days represent Total Time in days.
- Total Days Since Created shows the number of days that passed since the cases were created.
Sample charts created from the data stored under SLA History
How it’s done
Below is a table with all custom fields for this object:
Few notes regarding the fields above:
- Status should have the same list of values as Case.Status.
- We need to create a custom field named Modified By since we want to track the user who changed the status and not the user who run the trigger.
- The Recent Status Value is used to indicate whether the current SLA History record for the given status, is the most recent one or perhaps there is another record with the same status under the case. This field is useful when we want to create a report which tracks the total time passed until the case was resolved for the last time.
The following rules can be done using visual force or flow:
* When a case record is created or Status was changed, Create a new record under SLA History with the following information:
- Case: <Case>
- Start Time: <Current Time>
- Status: <Case.Status>
- Modified By: <Case:Last Modified By>
- Total Time: 0
* When the case status is changed, locate the latest record (using SLA History.Created By) under SLA History and update the following fields:
- End Time: <Current Time>
- Total Time: <Current Time> – <Start Time>
* When SLA History record is created and there is another record with the same status related to the case, set Recent Status Value to False on the record found.
* Make sure to remove the New button from the related list of SLA History so users will not be able to create new records.
With more than 20 years in the software industry, 15 of them designing and implementing more than 100 CRM systems, Ofir has built himself a reputation as a leader in the CRM industry. He is also one of the very few people in Israel who has deep knowledge both in Salesforce and Microsoft Dynamic CRM, which allows him to feel confident to offer the most suitable solution based on his client’s needs.
Have something on your mind? Just leave us a message using the contact page and will be happy to assist you in any way we can.
