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 AggAgentPerformance for supervisor and operational reports on service representative performance. It combines session, participant, conversation, messaging, callback, response-time, quality, and sentiment data in a preaggregated model.
The table includes only customer service representative participation in customer interactions; it excludes agent-only sessions. Supervisors and analysts can compare workload, responsiveness, handling effort, customer experience outcomes, and callback performance across primary, consult, and monitor roles.
Use this table with conversation-level and queue-level analytics to:
- Analyze service representative workload and participation levels.
- Measure responsiveness and handling efficiency.
- Evaluate performance across primary, consult, and monitor roles.
- Track callback performance and adoption.
- Monitor customer experience metrics such as CSAT, NPS, and sentiment.
- Support workforce management and coaching scenarios.
Grain and source
Grain: One row per unique combination of the 12 dimensions listed below. DateTimeKey identifies the 15-minute interval when the session was created. Multiple sessions can contribute to one row.
The table is aggregated by the following dimensions.
GROUP BY
DateTimeKey,
QueueId,
ChannelCode,
ConversationStatusCode,
AgentBusinessUnitId,
ConversationDirectionCode,
AgentId,
SessionStateCode,
SessionState,
SessionStatusCode,
SessionStatus,
ParticipantModeCode
Each row combines session and participant records with matching dimension values. It doesn't represent an individual session.
Source tables
These sources combine session and participant activity with conversation context.
| Source table | Purpose |
|---|---|
| FactSession | Primary source for session-level metrics, including queue information, channel, wait times, answer times, handling durations, session durations, callback attributes, and session outcomes. |
| FactSessionParticipant | Provides participant-level activity such as agent identity, participant role, active time, inactive time, idle time, talk time, hold time, chat time, wrap-up time, and participant message activity. |
| FactConversation | Provides conversation-level attributes, including conversation status, conversation direction, service representative business unit, customer sentiment, CSAT scores, and NPS scores. |
How rows are created
Data processing assigns each session to a 15-minute interval based on its creation time, combines it with participant and conversation details, and groups records with matching dimension values. It doesn't split session activity across intervals.
TotalSessions and _SourceRecordCount count the combined records, not distinct sessions. A session with multiple participants contributes a record for each participant. Sessions without a matching participant record are also retained.
For example:
| Queue | Agent | Role | Channel | Status | Interval |
|---|---|---|---|---|---|
| Support | Agent A | Primary | Chat | Closed | 09:00 |
| Support | Agent A | Primary | Chat | Closed | 09:00 |
| Support | Agent A | Primary | Chat | Closed | 09:00 |
These three accepted sessions produce one aggregate row if all 12 dimension values match, including those not shown in the example.
Example output:
AcceptedSessions = 3
TotalHandleTimeInSeconds = 1800
TotalResponseTimeInSeconds = 420
TotalIncomingMessages = 95
Aggregation improves reporting performance while retaining activity patterns and workload characteristics.
Dimension columns
Use the following dimensions to group and filter performance by queue, channel, organizational unit, participant role, and session outcome.
Universal dimensions
| Column | Type | Source table | Source field or ETL logic | Description |
|---|---|---|---|---|
| DateTimeKey | BIGINT | FactSession | CreatedOnDateKey | Identifies the 15-minute interval when the session was created. Enables trend analysis by session creation time. |
| QueueId | STRING | FactSession | QueueId | Identifies the queue associated with the session. Supports queue-level workload and performance analysis. |
| ChannelCode | INT | FactSession | ChannelCode | Identifies the channel used for customer interaction, such as voice or messaging. |
| ConversationStatusCode | INT | FactConversation | ConversationStatusCode (via ConversationId) | Identifies the status of the parent conversation, allowing performance analysis by conversation outcome. |
| AgentBusinessUnitId | STRING | FactConversation | AgentBusinessUnitId (via ConversationId) | Identifies the business unit associated with the service representative. Supports organizational reporting and performance comparisons. |
| ConversationDirectionCode | INT | FactConversation | ConversationDirectionCode (via ConversationId) | Distinguishes inbound and outbound interactions. |
Table-specific dimensions
| Column | Type | Source table | Source field or ETL logic | Description |
|---|---|---|---|---|
| AgentId | STRING | FactSessionParticipant | AgentId | Identifies the service representative associated with the activity record. Enables individual performance reporting and coaching analysis. |
| SessionStateCode | INT | FactSession | SessionStateCode | Identifies the lifecycle state of the session. |
| SessionState | STRING | FactSession | SessionState | Human-readable session lifecycle status used in reports and filters. |
| SessionStatusCode | INT | FactSession | SessionStatusCode | Detailed session status reason within the state. |
| SessionStatus | STRING | FactSession | SessionStatus | Human-readable session status value used for reporting and filtering. |
| ParticipantModeCode | INT | FactSessionParticipant | ParticipantModeCode | Identifies the participant role in the session, such as Primary, Consult, or Monitor. |
Participant mode reference
| Code | Value | Description |
|---|---|---|
| 192350002 | Primary | Primary service representative responsible for handling the session. |
| 192350003 | Consult | Consult participant who can actively participate in the interaction. |
| 192350004 | Monitor | Monitoring participant or supervisor observing the interaction. |
Metric columns
The table stores counts and totals, not calculated averages. Reports use these values to calculate performance metrics at query time.
Volume metrics
Use volume metrics to measure workload, participation, session outcomes, and transfers.
| Column | Type | Source | Source field or ETL logic | Description |
|---|---|---|---|---|
| TotalSessions | INT | FactSession + FactSessionParticipant | COUNT(*) | Counts combined session and participant records, including sessions without a matching participant. It isn't a distinct session count. |
| AcceptedSessions | INT | FactSession + FactSessionParticipant | Mode-aware acceptance logic | Stores sessions accepted by the participant according to role-specific acceptance criteria. |
| RejectedSessions | INT | FactSession + FactSessionParticipant | Mode-aware rejection logic | Stores sessions rejected by participants according to role-specific rejection conditions. |
| TimedOutSessions | INT | FactSession + FactSessionParticipant | Mode-aware timeout logic | Stores sessions that timed out before participant acceptance. |
| TransferredSessions | INT | FactSession | Transfer closure reasons | Stores sessions transferred by the service representative to another destination. |
Time metrics
Use these durations to measure handling effort, engagement, inactivity, and session lifecycle timing. All values are in seconds.
| Column | Type | Description |
|---|---|---|
| TotalHandleTimeInSeconds | BIGINT | Stores cumulative session handling time for participating service representatives. |
| TotalWaitTimeInSeconds | BIGINT | Stores cumulative customer wait time before session acceptance. |
| TotalAnswerTimeInSeconds | BIGINT | Stores cumulative answer time across sessions. |
| TotalActiveTimeInSeconds | BIGINT | Stores cumulative active engagement time during customer interactions. |
| TotalInactiveTimeInSeconds | BIGINT | Stores cumulative inactive time during sessions. |
| TotalIdleTimeInSeconds | BIGINT | Stores cumulative idle time where the participant wasn't actively handling work. |
| TotalTalkTimeInSeconds | BIGINT | Stores cumulative voice conversation time. |
| TotalHoldTimeInSeconds | BIGINT | Stores cumulative customer hold time. |
| TotalActiveChatTimeInSeconds | BIGINT | Stores cumulative active messaging time. |
| TotalWrapupTimeInSeconds | BIGINT | Stores cumulative post-interaction wrap-up effort. |
| TotalSessionDurationInSeconds | BIGINT | Stores cumulative session duration for completed sessions. |
Response time metrics
Use these totals and counts to calculate service representative response times during customer interactions.
| Column | Type | Description |
|---|---|---|
| TotalFirstMessageResponseTimeInSeconds | DOUBLE | Stores cumulative first-response times across conversations. |
| FirstMessageResponseCount | INT | Stores the number of interactions contributing to first-response calculations. |
| TotalResponseTimeInSeconds | DOUBLE | Stores cumulative response times across all customer-service representative exchange cycles. |
| MessageBlockCount | INT | Stores the total number of customer-to-service representative exchange cycles. |
| TotalFirstResponseTimeAfterAcceptanceInSeconds | DOUBLE | Stores cumulative time between session acceptance and first response. |
| FirstResponseTimeAfterAcceptanceCount | INT | Stores the number of interactions contributing to first-response-after-acceptance calculations. |
Quality metrics
Use customer feedback and sentiment metrics to evaluate service representative performance.
| Column | Type | Description |
|---|---|---|
| TotalSentimentPulse | DOUBLE | Stores cumulative AI-generated sentiment values associated with conversations handled by the service representative. |
| SentimentPulseCount | INT | Stores the number of sentiment observations represented in the aggregated row. |
| Survey_TotalCSATScore | DOUBLE | Stores cumulative customer satisfaction scores. |
| Survey_CSATResponseCount | INT | Stores the number of customer satisfaction survey responses. |
| Survey_TotalNPSScore | DOUBLE | Stores cumulative Net Promoter Scores. |
| Survey_NPSResponseCount | INT | Stores the number of Net Promoter Score survey responses. |
Message and callback metrics
Use these metrics to measure messaging activity and callback performance across customer interactions.
| Column | Type | Description |
|---|---|---|
| TotalIncomingMessages | INT | Stores the total number of customer messages. |
| TotalOutgoingMessages | INT | Stores the total number of service representative messages. |
| CallbacksOffered | INT | Stores the number of callback opportunities presented to customers. |
| CallbacksOptedIn | INT | Stores the number of callback offers accepted by customers. |
| CallbacksInitiated | INT | Stores callbacks that progressed through offer, acceptance, and service representative assignment. |
| CallbacksAcceptedByAgent | INT | Stores callback sessions accepted by a service representative. |
| CallbacksAnswered | INT | Stores callbacks where the customer successfully connected after callback initiation. |
Metadata columns
Use these system-generated columns for partitioning, refresh tracking, operational monitoring, and data lineage.
| Column | Type | Source table | Source field or ETL logic | Description |
|---|---|---|---|---|
| _AggregationDate | DATE | FactSession | CreatedOnDate | Date associated with the aggregation interval. Used as a partition column and foreign key to DimDate. |
| _SourceRecordCount | INT | ETL | COUNT(*) | Number of source records aggregated into the output row. Useful for validation and troubleshooting. |
| _LastRefreshedDateTimeUtc | TIMESTAMP | ETL | current_timestamp() | UTC timestamp of the most recent ETL refresh operation. |