Edit

FactCase table reference

Use FactCase to analyze individual cases, including their assignments, resolution history, tracked work time, customer feedback, and support entitlements. For summarized case performance, use AggCaseAnalytics.

Grain and source

Each row represents one case, identified by CaseId. The table combines case information with available assignment, resolution, time-tracking, and survey data.

Case status and ownership reflect the latest available information. Queue and worker details reflect the current assignment or, if none is available, the last known assignment. They don't identify who performed every action or resolved the case.

Case details

Use these columns to identify a case or open its record.

Column Type Description
CaseId STRING Unique case identifier.
CaseNumber STRING Case number shown to users.
CaseTitle STRING Case title.
CaseUrl STRING Link to open the case, when available.

Dimension columns

Use these columns to filter and group cases by assignment, classification, product, or support entitlement.

Column Type Description
QueueId STRING Current active queue or, if none, last known queue. Cases without an available queue are reported under (Unassigned).
OwnerId STRING Identifier of the user or team that owns the case.
CurrentOwnerKey STRING Case owner. Relates to DimCaseOwner.
OwningBusinessUnitId STRING Case owner's business unit.
AgentId STRING Identifier of the current or last known worker.
CurrentWorkerKey STRING Current or last known worker. Relates to DimCaseWorker.
AgentBusinessUnitId STRING Worker's business unit, when available.
QueueAssignmentState STRING Queue assignment coverage: Assigned, NoQueue, or Unknown. Assigned can include a last known queue.
WorkerAssignmentState STRING Worker assignment coverage: Assigned, Unassigned, or Unknown. Assigned can include a last known worker.
SubjectId STRING Case subject. Relates to DimSubject.
PriorityCode INT Case priority. Relates to DimCasePriority.
SeverityCode INT Case severity. Relates to DimCaseSeverity.
CaseTypeCode INT Case type. Relates to DimCaseType.
CaseOriginCode INT Channel through which the case was received. Relates to DimCaseOrigin.
CaseStateCode INT Current case state: 0 active, 1 resolved, or 2 canceled.
CaseStatusCode INT Current case status reason.
CurrentCaseStatusKey STRING Current case state and status reason. Relates to DimCaseStatus.
ProductId STRING Associated product. Relates to DimCaseProduct.
CurrentEntitlementId STRING Assigned support entitlement. Relates to DimCaseEntitlement.

Ownership and worker assignment are separate. A team-owned case can have an individual worker. An unknown assignment means the information isn't available, not that the case is unassigned.

Date and time columns

Use these columns to understand when a case was created, updated, escalated, or resolved.

Column Type Description
CreatedOnDateTime TIMESTAMP Date and time the case was created.
ModifiedOnDateTime TIMESTAMP Date and time the case was last updated.
ResolvedOnDateTime TIMESTAMP Last update time for a currently resolved case. For recorded resolution times, use FirstKnownResolvedOn or LatestKnownResolvedOn.
EscalatedOnDateTime TIMESTAMP Date and time of the recorded escalation.
IncidentResolutionDateTime TIMESTAMP Date and time of the latest available completed resolution record.
FirstKnownResolvedOn TIMESTAMP Date and time of the earliest available completed resolution record.
LatestKnownResolvedOn TIMESTAMP Date and time of the latest available completed resolution record, retained if the case is reopened.

Resolution dates depend on the records available for analysis. They don't guarantee a complete history of every resolution.

Metric columns

The following columns describe case activity, elapsed time, tracked work, and customer feedback.

Case activity

Column Type Description
IsResolved BOOLEAN Whether the case is currently resolved.
IsEscalated BOOLEAN Whether the case is marked as escalated.
IsCustomerContacted BOOLEAN Whether the case is marked as having a customer contact.
IsFirstResponseSent BOOLEAN Whether the first response is marked as sent. Check FirstResponseFlagKnown before treating a false value as not sent.
FirstResponseFlagKnown BOOLEAN Whether first-response information is available. False means unknown.
FirstResponseSLAStatusCode INT First-response service-level agreement (SLA) status, including 3 for succeeded and 4 for expired. These codes differ from the execution statuses in FactSLAKPIInstance.
CopilotEngaged BOOLEAN Whether Copilot engagement is recorded for the case. A false value can also mean the information isn't available.
NumberOfChildIncidents INT Number of child cases.

Resolution and case age

Column Type Description
CaseAgeInDays DOUBLE Calendar days from creation to ResolvedOnDateTime for currently resolved cases, or to the refresh time for other cases.
CaseAgeDescription STRING Age category based on CaseAgeInDays.
HandleTimeInMinutes BIGINT Elapsed calendar minutes from creation to ResolvedOnDateTime for currently resolved cases. This isn't active work time.
FirstKnownResolutionId STRING Identifier of the earliest available completed resolution record.
LatestKnownResolutionId STRING Identifier of the latest available completed resolution record.
KnownResolutionCount BIGINT Number of available completed resolutions. Zero means none are recorded in the available history; blank means resolution data isn't available.
FirstKnownResolutionSeconds BIGINT Elapsed calendar seconds from case creation to the earliest available completed resolution.
LatestKnownResolutionSeconds BIGINT Elapsed calendar seconds from case creation to the latest available completed resolution.
ResolutionHistoryCoverage STRING RetainedOnly means only retained resolution records are available. Unknown means resolution data isn't available.

Reopened cases keep their known resolution history. Use the first and latest known resolution columns to analyze those earlier resolutions.

Tracked time

Column Type Description
TrackedTimeMinutes DOUBLE Tracked work minutes, including fractional minutes. Uses only entries marked as Final if the case has any; otherwise, uses all entries.
TrackedTimeRecordCount BIGINT Number of time entries used to calculate TrackedTimeMinutes, not a case count.
TrackedTimeInMinutes BIGINT Total minutes from all time entries, regardless of category. Fractional minutes are discarded from each entry before summing.

Cases without tracked-time data have blank values, not zero. Tracked time measures recorded work, not the elapsed time between case creation and resolution.

Customer feedback

Column Type Description
CSATScore DOUBLE Average valid customer satisfaction (CSAT) score for the case.
CSATScoreSum DOUBLE Total of valid CSAT scores for the case.
CSATResponseCount BIGINT Number of valid CSAT responses.
CSATCoverage STRING Available means survey data is available for analysis, even if the case has no responses. Unknown means the data isn't available.
SentimentScore DOUBLE Average valid survey sentiment score for the case. Zero-valued survey responses are excluded.
SentimentScoreSum DOUBLE Total of valid survey sentiment scores.
SentimentResponseCount BIGINT Number of valid survey sentiment values.
SentimentLabelCode INT Sentiment category: 0 negative, 1 neutral, or 2 positive. Blank when no score is available.

To calculate average CSAT across cases, divide the sum of CSATScoreSum by the sum of CSATResponseCount. This gives each response equal weight, even when cases have different numbers of responses.

Metadata columns

Use these columns to identify the case creation period and data refresh time.

Column Type Description
CreatedDateKey BIGINT Case creation time in UTC, grouped into 15-minute intervals.
_StartDate DATE Case creation date.
_LastRefreshedDateTimeUtc TIMESTAMP Last data refresh date and time in UTC.

Relationships and coverage

Use CaseId to relate a case to its SLA executions in FactSLAKPIInstance. One case can have multiple executions. Count distinct cases when combining these tables so that multiple executions don't inflate case totals.

Optional details, such as product, entitlement, survey, and resolution information, depend on the available source data. Don't interpret missing information as zero, a failed SLA, or confirmation that no entitlement is assigned.

Case analytics data model
AggCaseAnalytics table reference
FactSLAKPIInstance table reference