Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Use AggCaseAnalytics to analyze aggregated case volume, response and resolution performance, customer feedback, reopens, and case-level service-level agreement (SLA) performance.
Grain and source
Each row summarizes cases with the same creation period, queue, origin, priority, status, business unit, owner, worker, case type, and product.
DateTimeKey groups case creation times into 15-minute intervals. Other attributes, such as queue, owner, worker, and status, reflect current or last known values.
Dimension columns
Filter and group cases by queue, origin, priority, owner, status, product, and the other attributes in this table.
| Column | Type | Description |
|---|---|---|
DateTimeKey |
BIGINT | Case creation time in UTC, grouped into 15-minute intervals. |
QueueId |
STRING | Case's current or last known queue. |
CaseOriginCode |
INT | Case origin. Relates to DimCaseOrigin. |
PriorityCode |
INT | Case priority. Relates to DimCasePriority. |
CaseStatusCode |
INT | Case's current status reason. |
AgentBusinessUnitId |
STRING | Worker's business unit. |
OwnerKey |
STRING | Case owner. Relates to DimCaseOwner. |
WorkerKey |
STRING | Case's current or last known worker. Relates to DimCaseWorker. |
CaseBusinessUnitId |
STRING | Case's owning business unit. |
CaseTypeCode |
INT | Case type. Relates to DimCaseType. |
CaseStatusKey |
STRING | Case state and status reason. Relates to DimCaseStatus. |
ProductId |
STRING | Case product. Relates to DimCaseProduct. |
Note
Ownership and worker assignment are separate. A team-owned case can still have an assigned worker.
Metric columns
The following columns provide counts and totals for case performance measures.
Volume and response
| Column | Type | Description |
|---|---|---|
TotalCases |
BIGINT | Number of cases. |
ActiveCaseCount |
BIGINT | Number of currently active cases. For captured backlog metrics, use AggCaseSnapshot. |
ResolvedCases |
BIGINT | Number of currently resolved cases. |
CancelledCases |
BIGINT | Number of currently canceled cases. |
EscalatedCases |
BIGINT | Number of cases marked as escalated. |
FirstResponseSentCount |
BIGINT | Number of cases with a sent first response, including canceled cases. |
ResponseEligibleCaseCount |
BIGINT | Number of noncanceled cases with known first-response status. |
ResponseCompletedCaseCount |
BIGINT | Number of eligible cases with a sent first response. |
ResponseUnknownCaseCount |
BIGINT | Number of noncanceled cases with unavailable first-response status. |
Resolution, feedback, and reopens
| Column | Type | Description |
|---|---|---|
FirstResolutionSecondsSum |
BIGINT | Total elapsed seconds to the first known resolution for resolved cases with valid resolution data. |
LatestResolutionSecondsSum |
BIGINT | Total elapsed seconds to the latest known resolution for resolved cases with valid resolution data. |
ResolutionValidCaseCount |
BIGINT | Number of resolved cases with valid resolution data. Use as the denominator for average first or latest resolution time. |
CSATScoreSum |
DOUBLE | Total of valid CSAT scores. |
CSATValidResponseCount |
BIGINT | Number of valid customer satisfaction (CSAT) responses. |
SentimentScoreSum |
DOUBLE | Total of valid survey sentiment scores. |
SentimentValidResponseCount |
BIGINT | Number of valid survey sentiment responses. |
EverResolvedCaseCount |
BIGINT | Number of cases that have been resolved at least once. |
ReopenedCaseCount |
BIGINT | Number of cases that were reopened after a known resolution. |
ReopenEventCount |
BIGINT | Number of known reopen events. |
SurveyedCaseCount |
BIGINT | Number of cases with at least one valid CSAT response. |
TrackedTimeMinutesSum |
DOUBLE | Total tracked minutes, including fractional minutes. For each case, includes only time entries marked as Final if any exist; otherwise, includes all time entries. |
TrackedCaseCount |
BIGINT | Number of cases with a tracked-time value under this rule. Use as the denominator for average tracked time. |
First and latest resolution times measure elapsed calendar time, not active work time.
For tracked time, cases without a value are excluded from the average, but cases with zero recorded minutes are included. Both TrackedCaseCount and TrackedTimeCount count cases, not individual time entries. To calculate either average across multiple rows, divide the sum of the tracked-time totals by the sum of the corresponding case counts.
Case-level SLA performance
Use these columns to understand SLA performance at the case level.
| Column | Type | Description |
|---|---|---|
SLAInScopeCaseCount |
BIGINT | Number of cases included in SLA performance calculations. |
SLABreachedCaseCount |
BIGINT | Number of cases with at least one breached SLA. |
SLAMetCaseCount |
BIGINT | Number of cases for which all applicable SLA requirements were met. |
SLAPendingCaseCount |
BIGINT | Number of cases with an SLA that's still in progress, nearing its target, or paused and doesn't have a breach. |
SLAOutOfScopeCaseCount |
BIGINT | Number of cases without an applicable SLA. |
SLAUnknownCaseCount |
BIGINT | Number of cases for which SLA status isn't available. |
Each case has one SLA outcome. When multiple SLA results apply, the outcome follows this order: breached, pending, unknown, and met.
To analyze individual SLA KPI executions instead of cases, use AggCaseSLA.
Additional columns
These columns are available in the table but aren't included in the default case dashboard.
| Columns | Type | Description |
|---|---|---|
CustomerContactedCount, TotalChildIncidents |
BIGINT | Number of cases marked as contacted and number of child cases. |
TotalCaseAgeInDays, CaseAgeCount |
DOUBLE, BIGINT | Total case age and the number of cases with an age value. |
TotalHandleTimeInMinutes, HandleTimeCount |
BIGINT | Total elapsed minutes from case creation to resolution and the corresponding case count. This isn't active handling time. |
TotalTrackedTimeInMinutes, TrackedTimeCount |
BIGINT | Total tracked minutes from all time entries, regardless of category, and the corresponding case count. Each entry is converted to whole minutes before summing, so fractional minutes are discarded. Unlike TrackedTimeMinutesSum, this total doesn't give preference to Final entries. |
TotalCSATScore, CSATResponseCount |
DOUBLE, BIGINT | Total case-level CSAT score and the number of cases with a score. |
TotalSentimentScore, SentimentResponseCount |
DOUBLE, BIGINT | Total case-level sentiment score and the number of cases with a sentiment score. |
FirstResponseInSLACount, FirstResponseSLAExpiredCount |
BIGINT | Number of cases that met or exceeded their first-response SLA target. |
Metadata columns
Use these columns to identify the creation period, record count, and refresh time for each aggregate row.
| Column | Type | Description |
|---|---|---|
_AggregationDate |
DATE | Date associated with the case creation period. |
_SourceRecordCount |
BIGINT | Number of case records represented by the aggregated row. |
_LastRefreshedDateTimeUtc |
TIMESTAMP | Last data refresh date and time in UTC. |
Measure examples
Calculate first-response completion by dividing completed cases by eligible cases:
FRT Completed % =
DIVIDE(
SUM(AggCaseAnalytics[ResponseCompletedCaseCount]),
SUM(AggCaseAnalytics[ResponseEligibleCaseCount])
)
Calculate average CSAT by dividing the total valid score by the number of valid responses:
Avg CSAT =
DIVIDE(
SUM(AggCaseAnalytics[CSATScoreSum]),
SUM(AggCaseAnalytics[CSATValidResponseCount])
)
For example, two cases with three valid survey responses and a combined CSAT score of 12 have an average CSAT of 4.
Refresh and coverage
The table covers a rolling 24 months based on case creation date. Changes to a case update its original creation period.
Response, survey, SLA, and resolution-history metrics depend on source data and might be unavailable for some cases. Don't treat unavailable values as zero.