SfSkills Domains Docs GitHub

Home / data / service-metrics-data-model

service-metrics-data-model

Use when designing or reporting on Salesforce Service Cloud service metrics — covers the Case, Entitlement, and CaseMilestone object model, MTTR derivation via BusinessHours.diff(), IsViolated semantics, and ElapsedTimeInMins field usage for SLA reporting.

Open the skill folder on GitHub

Service Metrics Data Model

This skill activates when a practitioner needs to design, query, or troubleshoot Salesforce Service Cloud service metrics — including mean time to resolve (MTTR), SLA milestone compliance, first response tracking, and related KPI reporting. It covers the Case, Entitlement, and CaseMilestone object model, the semantics of key fields like IsViolated, ElapsedTimeInMins, and TargetDate, and how to derive metrics that have no native single field in the platform. It does NOT cover CRM Analytics (Tableau CRM / Einstein Analytics) or Field Service Lightning work order metrics.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

Case Object — Foundation of Service Metrics

The Case object is the primary record for all service interactions. Key fields for service metric derivation:

MTTR via formula field on Case (calendar time):

IF(IsClosed,
   (ClosedDate - CreatedDate) * 24 * 60,
   NULL)

This gives MTTR in minutes as a formula Number field. It does not respect Business Hours. For business-hours-aware MTTR, use BusinessHours.diff() in Apex and store the result in a custom field updated via trigger.

Entitlement Object — SLA Tier Linkage

The Entitlement object represents an SLA agreement (e.g., "Platinum Support — 24-hour response, 4-hour resolution"). It links an Account or Asset to an Entitlement Process that defines the Milestone sequence and timing.

Key fields:

The Entitlement Process configures which Milestones fire, when they are due (offset from case creation or prior milestone completion), and what happens on violation (notifications, field updates, escalations via Process Builder/Flow).

CaseMilestone Object — The SLA Measurement Unit

CaseMilestone is the junction object that tracks each milestone instance for each Case. It is the primary table for SLA compliance reporting.

Key fields:

Common Patterns

Pattern: MTTR Derivation Without a Native Field

When to use: A support operations team needs a Case-level metric for mean time to resolve, usable in Salesforce reports and dashboards without Apex.

How it works:

  1. Create a custom Number or Formula field on Case: MTTR_Calendar_Mins__c (Number, populated by trigger) or a Formula field (if Apex is not available).
  2. For a formula field: IF(IsClosed, (ClosedDate - CreatedDate) * 24 * 60, NULL) — gives calendar-time minutes.
  3. For business-hours-aware MTTR, write an Apex after-update trigger that calls BusinessHours.diff(bhId, CreatedDate, ClosedDate) and stores the result in MTTR_BH_Mins__c when IsClosed becomes true.
  4. Report on MTTR_Calendar_Mins__c or MTTR_BH_Mins__c using Summary reports with AVG aggregation grouped by Case Owner, Queue, Priority, or Month.

Why not just use ElapsedTimeInMins on CaseMilestone: ElapsedTimeInMins measures the time for an individual milestone (e.g., First Response), not the full case resolution span. MTTR from case open to case close spans multiple milestones and requires Case-level fields.

Pattern: SLA Compliance Rate Reporting via CaseMilestone

When to use: A service manager wants a monthly SLA compliance report showing what percentage of First Response and Resolution milestones were met on time by queue.

How it works:

  1. Query CaseMilestone joined to Case and MilestoneType:
    • Filter on IsCompleted = true OR IsViolated = true (to exclude future open milestones from compliance calculations).
    • Use IsViolated to compute violated count; use NOT IsViolated AND IsCompleted to compute compliant count.
  2. Build a Salesforce Summary report on CaseMilestone with the Custom Report Type joining Case and MilestoneType.
  3. Group by MilestoneType.Name and Case Owner.Queue (via Case.Queue field or owner hierarchy).
  4. Add custom summary formula: MTTR_Compliance_Rate = (NOT_VIOLATED_COUNT / TOTAL_COMPLETED_COUNT) * 100.

Why not filter on IsViolated = false alone: Open, in-progress milestones also have IsViolated = false (they have not been violated yet). Including them in a compliance denominator inflates compliance rate with incomplete data.

Pattern: First Response Time Isolation

When to use: The SLA contract defines a maximum time to first response (e.g., 4 business hours). Reporting needs to show compliance per case per support tier.

How it works:

  1. Query CaseMilestone WHERE MilestoneType.Name = 'First Response'.
  2. Use ElapsedTimeInMins as the actual response time in business minutes.
  3. Compare to the TargetDate-derived SLA threshold — or directly use IsViolated.
  4. For cases where CompletionDate is set but IsViolated = true: elapsed time exceeded the Entitlement Process target; these are late responses, not missing responses.

Decision Guidance

SituationRecommended ApproachReason
Need calendar-time MTTR on CaseFormula field: (ClosedDate - CreatedDate) * 24 * 60Zero Apex; works declaratively in reports
Need business-hours-aware MTTRApex trigger calling BusinessHours.diff() storing result in custom fieldFormula fields cannot call Apex methods; BH-aware metric requires Apex
Need SLA milestone compliance rateQuery CaseMilestone filtering on IsCompleted = true OR IsViolated = trueExcludes open future milestones from compliance denominator
IsViolated = true on a completed milestoneMilestone was completed late — after TargetDateNot a data error; expected Salesforce behavior
Need to report on milestones still open and at riskFilter IsCompleted = false AND IsViolated = false AND TargetDate < NOW()These are overdue and not yet auto-violated — escalation candidates
Need elapsed time in minutes for a specific milestoneUse ElapsedTimeInMins on CaseMilestonePlatform-computed; respects Business Hours; do not re-derive manually
Multiple entitlement tiers with different SLAsStore Entitlement.Name or SLA tier on Case via flow/formula for report groupingCaseMilestone does not expose Entitlement tier directly; denormalize at report design time
MTTR needed across thousands of cases in external BIExport Case with CreatedDate, ClosedDate, IsClosed, MTTR_BH_Mins__cBulk-exportable; pre-computed field avoids re-deriving BH logic outside Salesforce

Step-by-step instructions for an AI agent or practitioner working on service metrics data model tasks:

  1. Verify prerequisites: Confirm Entitlements are enabled (Setup > Entitlement Settings). Confirm at least one Entitlement Process is Active and assigned to Cases via the EntitlementId lookup. Check that Business Hours records are configured and assigned to relevant Entitlement records. Without these, CaseMilestone records will not exist and SLA metrics cannot be reported.
  2. Map the metric requirement to the object and field: Determine whether the metric is case-level (MTTR, handle time) or milestone-level (first response time, resolution SLA compliance). Case-level metrics require fields on Case (derive MTTR). Milestone-level metrics use CaseMilestone.ElapsedTimeInMins, IsViolated, TargetDate, CompletionDate.
  3. Design MTTR derivation: For calendar MTTR, create a Formula Number field on Case: IF(IsClosed, (ClosedDate - CreatedDate) * 24 * 60, NULL). For business-hours MTTR, write an Apex trigger on Case (after update, when IsClosed becomes true) that calls BusinessHours.diff(bhId, case.CreatedDate, case.ClosedDate) and stores the result in MTTR_BH_Mins__c. Confirm the correct BusinessHoursId is used — it should match the Entitlement's Business Hours assignment.
  4. Design SLA compliance queries and report types: Create a Custom Report Type joining CaseMilestone > Case > MilestoneType. Use IsViolated and IsCompleted together to correctly categorize milestone outcomes: compliant (IsCompleted=true, IsViolated=false), late (IsCompleted=true, IsViolated=true), open at-risk (IsCompleted=false, IsViolated=false, TargetDate in past), open on-track (IsCompleted=false, IsViolated=false, TargetDate in future). Do not use IsViolated = false as a proxy for "compliant" — it includes unresolved milestones.
  5. Validate ElapsedTimeInMins accuracy: Cross-check ElapsedTimeInMins on a sample of CaseMilestone records against the StartDate to CompletionDate delta using the assigned Business Hours. If Business Hours were changed after milestones were created, ElapsedTimeInMins may reflect the old Business Hours configuration. Confirm Business Hours alignment before publishing SLA reports.
  6. Build and test reports: Build Salesforce Summary reports grouped by MilestoneType.Name and period. Add custom summary formulas for compliance rate. Validate that the denominator excludes open future milestones. For MTTR reports on Case, validate AVG MTTR against a known set of closed cases. Cross-check formula field values against manual calculations on sample records.
  7. Document SLA tier context for stakeholders: Record which Entitlement Process and Business Hours configuration underpins each reported metric. Document the IsViolated=true AND IsCompleted=true interpretation for report consumers — late completions count as SLA misses, not failures to respond.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. IsViolated = true does not mean the milestone is still open — IsViolated is set to true when a milestone's TargetDate passes without completion. It remains true permanently even after the milestone is completed late. A CaseMilestone with IsCompleted = true, CompletionDate populated, and IsViolated = true represents a late completion — the most common misread in SLA reports.
  2. No native MTTR field exists — There is no platform-provided MTTR field on Case. Practitioners who assume ElapsedTimeInMins on CaseMilestone gives them full case resolution time are wrong: ElapsedTimeInMins measures only the span of that individual milestone, not the full case lifecycle from creation to close.
  3. ElapsedTimeInMins reflects business hours at the time of calculation, not at case creation — If Business Hours configuration changes after a CaseMilestone is created, the stored ElapsedTimeInMins may not accurately reflect the new Business Hours. Always validate against the effective Business Hours at the time of milestone creation.
  4. Entitlement StartDate/EndDate silently blocks milestone creation — If a case is created while the Entitlement's EndDate is in the past (expired entitlement), no CaseMilestone records are created even if the EntitlementId is populated. Cases appear to have an SLA but have no milestones to track — a silent gap in SLA reporting coverage.
  5. CaseMilestone TargetDate uses the Entitlement's Business Hours, not the org default — If a case is escalated and its Entitlement is changed mid-case to one with different Business Hours, existing open milestones retain their original TargetDate. New milestones created after the change use the new Entitlement's Business Hours. Reports comparing milestones across entitlement changes require careful interpretation.

Output Artifacts

ArtifactDescription
service-metrics-data-model-template.mdWork template for documenting service metrics requirements — captures metric-to-field mapping, MTTR derivation approach, SLA compliance query design, and known IsViolated edge cases
check_service_metrics_data_model.pystdlib Python checker that scans Salesforce metadata XML for common issues: missing Entitlement configuration, formula field MTTR definitions, Case milestone field usage patterns

Package files