)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Tracking the historical state of cases that were once unanswered but later received responses is crucial for accurate SLA reporting, but Salesforce reports only show current status.
Here’s how to enable comprehensive historical tracking by capturing cases in their unanswered state and preserving that record permanently for complete lifecycle analysis.
Implement historical tracking using Coefficient
Coefficient enables comprehensive historical tracking by capturing cases in their unanswered state and preserving that record permanently. This provides complete visibility into case response patterns that Salesforce’s current-state reporting cannot reveal.
How to make it work
Step 1. Capture unanswered cases with strategic filtering.
Create a Salesforce import filtering for Status = “New” OR “Open”, First Response Time IS NULL, and Case Age > 0 (to exclude just-created cases). Include case creation time, current age when captured, priority/severity, and assigned agent/queue information.
Step 2. Schedule strategic captures to build timeline data.
Run imports every hour to build a timeline of how long cases remained unanswered. This creates multiple records per case showing its unanswered duration over time, providing granular insight into response patterns.
Step 3. Build historical repository using “Append New Data”.
Enable this feature to accumulate all captures, creating a comprehensive database that shows the complete unanswered lifecycle of each case. Each capture includes automatic timestamps for precise duration calculations.
Step 4. Track status transitions and generate insights.
Create a companion import for all cases (regardless of status) and use VLOOKUP to identify when unanswered cases receive responses. Calculate actual time-to-first-response using first and last capture timestamps, identify cases that exceeded SLA while unanswered, and analyze patterns in response delays.
Transform your SLA measurement capabilities
This solution provides complete visibility into case response patterns, enabling accurate SLA measurement and identification of process bottlenecks that Salesforce’s current-state reporting cannot reveal. Start building your historical tracking system today.
