Edit

AggCaseSnapshot table reference

Use AggCaseSnapshot to analyze active cases, backlog, case age, response status, assignment, and staleness at the latest capture. The table contains only the latest snapshot, not historical backlog trends.

Grain and source

Each row summarizes cases with the same snapshot date, creation period, queue, owner, worker, business unit, origin, priority, case type, status, product, and age band.

Use DateTimeKey for case creation time and SnapshotDate for capture time. These fields distinguish case age from the date when the case's state was observed.

Dimension columns

Filter and group cases by queue, owner, priority, status, age, and the other attributes in this table.

Column Type Description
SnapshotDate DATE Date of the case data capture.
DateTimeKey BIGINT Case creation time in UTC, grouped into 15-minute intervals.
QueueId STRING Case's current or last known queue.
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.
OriginCode INT Case origin. Relates to DimCaseOrigin.
PriorityCode INT Case priority. Relates to DimCasePriority.
CaseTypeCode INT Case type. Relates to DimCaseType.
CaseStatusKey STRING Case state and status reason at capture. Relates to DimCaseStatus.
ProductId STRING Case product. Relates to DimCaseProduct.
AgeBandCode STRING Active case's age range. Relates to DimCaseAgeBand.

Metric columns

Use these counts and durations to analyze volume, response and assignment status, age, and staleness. Apply the aggregation specified for each column.

Column Type Aggregation Description
CaseCount BIGINT SUM Number of cases at capture time, including resolved and canceled cases.
ActiveCaseCount BIGINT SUM Number of active cases at capture time.
AwaitingResponseCount BIGINT SUM Number of active cases awaiting a first response.
UnknownResponseCaseCount BIGINT SUM Number of active cases with unavailable first-response status.
KnownUnassignedCount BIGINT SUM Number of active cases with no worker assigned.
UnknownAssignmentCaseCount BIGINT SUM Number of active cases with unavailable assignment status.
ActiveAgeSecondsSum BIGINT SUM Total active-case age in seconds.
ActiveAgeValidCount BIGINT SUM Number of active cases with a valid age value.
OldestActiveAgeSeconds BIGINT MAX Oldest active-case age in seconds.
ActiveLastUpdateAgeSecondsSum BIGINT SUM Total elapsed seconds since active cases' last updates.
ActiveLastUpdateAgeValidCount BIGINT SUM Number of active cases with a valid last-update age.
OldestActiveLastUpdateAgeSeconds BIGINT MAX Longest elapsed time since an active case's last update, in seconds.
StaleCaseCount BIGINT SUM Number of active cases whose last update was at least seven calendar days before the capture date.

Note

Any case update, including an automated update, affects last-update age. Staleness doesn't necessarily measure time since a service representative last worked on the case.

Age bands

Group active cases by age with AgeBandCode.

Code Display label Age
D0_1 0-1 day Less than 2 days.
D2_7 2-7 days At least 2 days and less than 8 days.
D8_30 8-30 days At least 8 days and less than 31 days.
D31_89 31-89 days At least 31 days and less than 90 days.
D90Plus 90+ days At least 90 days.
NotActive Not active Case isn't active, or its age information is unavailable.

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

Use these examples to calculate active-case counts, average and oldest age, and stale case rate.

Active Cases =
    SUM(AggCaseSnapshot[ActiveCaseCount])
Avg Active Case Age (days) =
    DIVIDE(
        SUM(AggCaseSnapshot[ActiveAgeSecondsSum]),
        SUM(AggCaseSnapshot[ActiveAgeValidCount])
    ) / 86400
Oldest Active Case Age (days) =
    MAX(AggCaseSnapshot[OldestActiveAgeSeconds]) / 86400
Stale Case Rate =
    DIVIDE(
        SUM(AggCaseSnapshot[StaleCaseCount]),
        [Active Cases]
    )

Import and refresh

Each refresh updates the table with the latest case data and replaces the previous view of the backlog. Earlier views aren't saved.

Case metrics reference
Case analytics data model