Contact Center Analytics: Complete Technical Specification
This document is the unified, detailed specification for standardizing the contact center data models and mapping them to visualization widgets for both Real-Time Monitoring Dashboards and Historical Analysis Reports.
Part 1: Design Principles & Editable Formulas
A. General Standardization Rules
- User & Group Terminology: Standardized on
userandgroup(instead of agent and queue) because users and groups are shared between CRM modules and telephony logs. - Telemetry & CRM Integration: Standardized and preserved
conference_id(for grouping multi-party calls),module_id, andrecord_id(for CRM-triggered calls). - Numeric Durations: Expose all durations as integers in seconds (e.g.,
talk_duration_seconds). Formatting (like conversion toHH:MM:SSstrings) is offloaded to the BI layer (e.g. Power BI DAX or UI formatting). Exposing strings from the database/CubeJS layer breaks BI aggregations. - Case Consistency: Expose all dimensions and measures in
snake_case(e.g.,is_inbound,telephony_connection_id).
B. Editable KPI Formulas
Different organizations use different calculations for key metrics. Modify the definitions below in your Cube schema to match specific requirements.
1. Average Handle Time (AHT)
- Option A: Talk Time includes Hold Time (Default Telephony Setup)
Formula:
Handle Time = Talk Time + Wrap TimeEditable CubeJS Dimension:handle_duration_seconds: {sql: `COALESCE(${CUBE}.talk_duration_seconds, 0) + COALESCE(${CUBE}.wrap_duration_seconds, 0)`,type: `number`} - Option B: Talk Time and Hold Time are separate
Formula:
Handle Time = Talk Time + Hold Time + Wrap TimeEditable CubeJS Dimension:handle_duration_seconds: {sql: `COALESCE(${CUBE}.talk_duration_seconds, 0) + COALESCE(${CUBE}.hold_duration_seconds, 0) + COALESCE(${CUBE}.wrap_duration_seconds, 0)`,type: `number`}
2. Service Level (SLA)
- Option A: Answered-Only SLA (Default)
Formula:
SLA = Answered Calls within Target / Total Answered CallsEditable CubeJS Measure:service_level: {sql: `CASE WHEN ${answered_calls} = 0 THEN 0 ELSE (${answered_within_sla_count} * 100.0) / ${answered_calls} END`,type: `number`} - Option B: Answered + Abandoned SLA (Standard COPC)
Formula:
SLA = Answered Calls within Target / (Total Answered Calls + Abandoned Calls)Editable CubeJS Measure:service_level: {sql: `CASE WHEN (${answered_calls} + ${abandoned_calls}) = 0 THEN 0ELSE (${answered_within_sla_count} * 100.0) / (${answered_calls} + ${abandoned_calls}) END`,type: `number`}
Part 2: Model Definitions & Fields Mapping
Table 1: calls (Unique Call Stats)
Exposes call detail metadata. One row per telephony call.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
id | id | Dimension | number | Unique primary key. | ${CUBE}.id (Primary Key) |
call_history_id | telephony_call_id | Dimension | string | Unique session ID from telephony/3CX. | ${CUBE}."call_history_id" |
call_type | call_type | Dimension | string | inbound, outbound, campaign, internal. | ${CUBE}."call_type" |
status | status | Dimension | string | Outcome status (answered, unanswered, missed, abandoned, dnc, preview). | ${CUBE}."status" |
leadId | lead_id | Dimension | number | Associated CRM marketing lead/contact ID. | ${CUBE}."leadId" |
conference_id | conference_id | Dimension | number | ID grouping multiple calls under the same conference. | ${CUBE}."conference_id" |
moduleId | module_id | Dimension | number | CRM module identifier. | ${CUBE}."moduleId" |
recordId | record_id | Dimension | number | CRM record identifier within the module. | ${CUBE}."recordId" |
hotline | dialed_number | Dimension | string | Number dialed by the caller. | ${CUBE}."hotline" |
terminatedReason | termination_reason | Dimension | string | Reason for call termination. | ${CUBE}."terminatedReason" |
rescheduleAttempt | dial_attempt | Dimension | number | Dialer attempt count for campaign dials. | ${CUBE}."rescheduleAttempt" |
groupId | group_id | Dimension | number | Group ID the call originally entered. | ${CUBE}."groupId" |
start_time | start_time | Dimension | time | Timestamp when the call started. | ${CUBE}."start_time" |
transcript | transcript | Dimension | string | AI transcription text. | ${CUBE}."transcript" |
summary | summary | Dimension | string | Text summary of the conversation. | ${CUBE}."summary" |
call_time_hour_level | start_hour | Dimension | string | Hour of day (0-23) for hourly profiling. | EXTRACT(HOUR FROM ${CUBE}."start_time")::text |
count | total_calls | Measure | count | Total count of unique calls. | type: 'count' |
campaignDialedCallCount | campaign_dialed_calls | Measure | countDistinct | Total dialed campaign calls (excluding DNC/preview). | sql: ${CUBE}."call_history_id", filters: leadId IS NOT NULL AND status NOT IN ('dnc', 'preview') |
campaignUniqueDialedCallCount | campaign_unique_leads_dialed | Measure | countDistinct | Unique leads dialed. | sql: ${CUBE}."leadId", filters: leadId IS NOT NULL AND status NOT IN ('dnc', 'preview') |
campaignAnsweredCallCount | campaign_answered_calls | Measure | countDistinct | Answered campaign calls. | sql: ${CUBE}."call_history_id", filters: leadId IS NOT NULL AND status = 'answered' |
campaignUniqueAnsweredCallCount | campaign_unique_leads_answered | Measure | countDistinct | Unique leads answered. | sql: ${CUBE}."leadId", filters: leadId IS NOT NULL AND status = 'answered' |
Table 2: callParticipants (Performance & SLA)
[!NOTE] Formerly
summarizeCalls. Summarizes legs into user and group sessions. Crucial for user performance boards and group SLA reports. Highlighted fields show editable formulas.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
id | id | Dimension | string | Composite primary key for unique combinations. | CONCAT_WS('-', call_id, user_id, group_id, ivr_id, customer_number) |
call_id | call_id | Dimension | number | Call ID referencing calls. | ${CUBE}.call_id |
agent_id | user_id | Dimension | number | Reference to handling user (joins to users). | ${CUBE}.user_id |
group_id | group_id | Dimension | number | Reference to handling group (joins to groups). | ${CUBE}.group_id |
customer_number | customer_number | Dimension | string | Customer phone number. | ${CUBE}.customer_number |
hotline | dialed_number | Dimension | string | Hotline number dialed. | ${CUBE}.dialed_number |
direction | direction | Dimension | string | inbound, outbound. | ${CUBE}.direction |
start_time | start_time | Dimension | time | Participant session start time. | ${CUBE}.start_time |
is_answered | is_answered | Dimension | boolean | Was call answered by user/group? | ${CUBE}.is_answered |
is_abandoned | is_abandoned | Dimension | boolean | Was call abandoned in group? | ${CUBE}.is_abandoned |
is_sla_breached | is_sla_breached | Dimension | boolean | True if wait duration exceeded SLA. | ${CUBE}.is_sla_breached |
is_repeated | is_repeat_call | Dimension | boolean | True if customer called in last 24h. | ${CUBE}.is_repeat_call |
talk_time_duration | talk_duration_seconds | Dimension | number | Talk time in seconds (includes hold). | ${CUBE}.talk_duration_seconds |
hold_time_duration | hold_duration_seconds | Dimension | number | Total hold time in seconds. | ${CUBE}.hold_duration_seconds |
wrap_time_duration | wrap_duration_seconds | Dimension | number | Wrap-up time (ACW) in seconds. | ${CUBE}.wrap_duration_seconds |
ring_time_duration | ring_duration_seconds | Dimension | number | Ringing time in seconds. | ${CUBE}.ring_duration_seconds |
dial_time_duration | dial_duration_seconds | Dimension | number | Dialing time in seconds. | ${CUBE}.dial_duration_seconds |
queue_wait_duration | queue_wait_duration_seconds | Dimension | number | Group waiting duration in seconds. | ${CUBE}.queue_wait_duration_seconds |
mute_time_duration | mute_duration_seconds | Dimension | number | Muted line duration in seconds. | ${CUBE}.mute_duration_seconds |
| [NEW] | handle_duration_seconds | Dimension | number | EDITABLE: Total handling time effort. | COALESCE(${CUBE}.talk_duration_seconds, 0) + COALESCE(${CUBE}.wrap_duration_seconds, 0) (Talk includes Hold) |
creation_method | creation_method | Dimension | string | click-to-call, auto-dial, manual. | ${CUBE}.creation_method |
termination_reason | termination_reason | Dimension | string | Reason for call termination. | ${CUBE}.termination_reason |
disposition_id | disposition_id | Dimension | number | Code categorizing call outcome. | ${CUBE}.disposition_id |
recording_url | recording_url | Dimension | string | Audio recording URL link. | ${CUBE}.recording_url |
total_call_count | total_calls | Measure | number (distinct) | Distinct total call count. | type: 'countDistinct', sql: ${CUBE}.call_id |
answered_call_count | answered_calls | Measure | number (distinct) | Total answered calls. | type: 'countDistinct', sql: CASE WHEN ${CUBE}.is_answered = true THEN ${CUBE}.call_id ELSE NULL END |
unanswered_call_count | unanswered_calls | Measure | number (distinct) | Total unanswered calls. | type: 'countDistinct', sql: CASE WHEN ${CUBE}.is_answered = false THEN ${CUBE}.call_id ELSE NULL END |
abandoned_call_count | abandoned_calls | Measure | number (distinct) | Total abandoned calls in groups. | type: 'countDistinct', sql: CASE WHEN ${CUBE}.is_abandoned = true AND ${CUBE}.group_id IS NOT NULL THEN ${CUBE}.call_id ELSE NULL END |
repeated_call_count | repeat_calls_count | Measure | number (distinct) | Total repeat inbound calls. | type: 'countDistinct', sql: CASE WHEN ${CUBE}.is_repeat_call = true AND ${CUBE}.direction = 'inbound' THEN ${CUBE}.call_id ELSE NULL END |
answer_rate | answer_rate | Measure | number (ratio) | Percentage of calls answered. | sql: CASE WHEN ${total_calls} = 0 THEN 0 ELSE (${answered_calls} * 100.0) / ${total_calls} END |
abandon_rate | abandon_rate | Measure | number (ratio) | Percentage of calls abandoned. | sql: CASE WHEN ${total_calls} = 0 THEN 0 ELSE (${abandoned_calls} * 100.0) / ${total_calls} END |
answered_within_sla_count | answered_within_sla_count | Measure | number (distinct) | Answered calls within SLA target. | type: 'countDistinct', sql: CASE WHEN ${CUBE}.is_answered = true AND (${CUBE}.is_sla_breached = false OR ${CUBE}.is_sla_breached IS NULL) AND ${CUBE}.group_id IS NOT NULL THEN ${CUBE}.call_id END |
service_level | service_level | Measure | number (ratio) | EDITABLE: SLA Percentage. | sql: CASE WHEN ${answered_calls} = 0 THEN 0 ELSE (${answered_within_sla_count} * 100.0) / ${answered_calls} END |
fcr | first_call_resolution_rate | Measure | number (ratio) | First Call Resolution % (inbound). | sql: CASE WHEN InboundCalls = 0 THEN 0 ELSE (InboundCalls - ${repeat_calls_count}) * 100.0 / InboundCalls END |
talk_duration | total_talk_duration | Measure | number (sum) | Total talk duration in seconds. | type: 'sum', sql: ${CUBE}.talk_duration_seconds |
avg_talk_duration | avg_talk_duration | Measure | number (avg) | Average talk duration in seconds. | type: 'avg', sql: ${CUBE}.talk_duration_seconds |
hold_time | total_hold_duration | Measure | number (sum) | Total hold duration in seconds. | type: 'sum', sql: ${CUBE}.hold_duration_seconds |
avg_hold_time | avg_hold_duration | Measure | number (avg) | Average hold duration in seconds. | type: 'avg', sql: ${CUBE}.hold_duration_seconds` |
wrap_time | total_wrap_duration | Measure | number (sum) | Total wrap duration in seconds. | type: 'sum', sql: ${CUBE}.wrap_duration_seconds |
avg_wrap_time | avg_wrap_duration | Measure | number (avg) | Average wrap duration in seconds. | type: 'avg', sql: ${CUBE}.wrap_duration_seconds |
queue_waiting_duration | total_queue_wait_duration | Measure | number (sum) | Total group wait duration in seconds. | type: 'sum', sql: ${CUBE}.queue_wait_duration_seconds |
avg_queue_waiting_time | avg_queue_wait_duration | Measure | number (avg) | Average wait duration in seconds. | type: 'avg', sql: ${CUBE}.queue_wait_duration_seconds |
| [NEW] | average_speed_of_answer | Measure | number (avg) | EDITABLE: ASA for answered calls. | type: 'avg', sql: ${CUBE}.queue_wait_duration_seconds, filters: is_answered = true |
| [NEW] | total_handle_duration | Measure | number (sum) | EDITABLE: Total handle time in seconds. | type: 'sum', sql: ${CUBE}.handle_duration_seconds |
| [NEW] | avg_handle_time | Measure | number (avg) | EDITABLE: Average handle time (AHT). | type: 'avg', sql: ${CUBE}.handle_duration_seconds |
Table 3: callSegments (Raw Call Details / Legs)
Exposes individual timeline leg events for auditing call flow paths. One row per leg.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
id | id | Dimension | number | Unique internal leg identifier. | ${CUBE}.id (Primary Key) |
call_id | call_id | Dimension | number | Call ID referencing parent call. | ${CUBE}.call_id |
tcx_connection_id | telephony_connection_id | Dimension | number | Leg event ID from telephony server. | ${CUBE}.tcx_connection_id |
start_time | start_time | Dimension | time | Timestamp when the leg started. | ${CUBE}.start_time |
end_time | end_time | Dimension | time | Timestamp when the leg ended. | ${CUBE}.end_time |
status | segment_state | Dimension | string | State of this leg (ringing, hold, talking, etc.). | ${CUBE}.status |
source_number | source_number | Dimension | string | Dialing source phone number/extension. | ${CUBE}.source_number |
source_type | source_type | Dimension | string | Type of source (user, external, ivr, group). | ${CUBE}.source_type |
destination_number | destination_number | Dimension | string | Destination phone number/extension. | ${CUBE}.destination_number |
destination_type | destination_type | Dimension | string | Type of destination (user, external, ivr, group). | ${CUBE}.destination_type |
is_Inbound | is_inbound | Dimension | boolean | True if segment direction was inbound. | ${CUBE}.is_Inbound |
userId | user_id | Dimension | number | Associated user ID handling this leg. | ${CUBE}."userId" |
groupId | group_id | Dimension | number | Associated group ID handling this leg. | ${CUBE}."groupId" |
creation_method | creation_method | Dimension | string | Click-to-call, dialer, manual, etc. | ${CUBE}.creation_method |
termination_reason | termination_reason | Dimension | string | Termination cause code for this leg. | ${CUBE}.termination_reason |
recording_url | recording_url | Dimension | string | Call recording file URL. | ${CUBE}."recording_url" |
dispositionId | disposition_id | Dimension | number | Disposition ID for this leg. | ${CUBE}."dispositionId" |
| [NEW] | segment_duration_seconds | Dimension | number | Calculated duration of this leg. | EXTRACT(EPOCH FROM (COALESCE(end_time, NOW()) - start_time)) |
count | segment_count | Measure | count | Total count of segment lines. | type: 'count' |
duration | total_segment_duration | Measure | number (sum) | Total duration spent in this state. | type: 'sum', sql: ${CUBE}.segment_duration_seconds |
| [NEW] | avg_segment_duration | Measure | number (avg) | Average duration spent in this state. | type: 'avg', sql: ${CUBE}.segment_duration_seconds |
Table 4: workShifts (User Shift Clock-In/Clock-Out)
Tracks when users log in and out of their daily shifts.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
shiftId | shift_id | Dimension | number | Primary key of the shift. | ${CUBE}."shiftId" (Primary Key) |
userId | user_id | Dimension | number | User reference (joins to users). | ${CUBE}."userId" |
startTime | start_time | Dimension | time | Clock-in timestamp. | ${CUBE}."startTime" |
endTime | end_time | Dimension | time | Clock-out timestamp. | ${CUBE}."endTime" |
| [NEW] | shift_duration_seconds | Dimension | number | Calculated duration of the shift. | EXTRACT(EPOCH FROM (COALESCE(${CUBE}."endTime", NOW()) - ${CUBE}."startTime")) |
count | shift_count | Measure | count | Count of shift events. | type: 'count' |
shiftDuration | total_shift_duration | Measure | number (sum) | Standardized: Sum of shift duration in seconds. | type: 'sum', sql: ${CUBE}.shift_duration_seconds |
| [NEW] | avg_shift_duration | Measure | number (avg) | Average shift duration in seconds. | type: 'avg', sql: ${CUBE}.shift_duration_seconds |
Table 5: statusLog (User Presence State Timeline)
Logs status changes (Available, Break, Meeting) during a shift. Exposes durations.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
id | id | Dimension | number | Log row primary key. | ${CUBE}."id" (Primary Key) |
shiftId | shift_id | Dimension | number | Parent shift reference. | ${CUBE}."shiftId" |
statusId | status_id | Dimension | number | Reference to presence state (joins to presenceStatuses). | ${CUBE}."statusId" |
timestamp | status_change_time | Dimension | time | Timestamp status was entered. | ${CUBE}."timestamp" |
| [NEW] | next_status_change_time | Dimension | time | Timestamp status was exited. | ${CUBE}.next_timestamp |
| [NEW] | status_duration_seconds | Dimension | number | Standardized: Duration spent in this status state. | GREATEST(EXTRACT(EPOCH FROM (LEAST(COALESCE(${CUBE}.next_timestamp, NOW()), COALESCE(${CUBE}."endTime", NOW())) - GREATEST(${CUBE}."timestamp", ${CUBE}."startTime"))), 0) |
count | status_log_count | Measure | count | Count of state log updates. | type: 'count' |
duration | total_status_duration | Measure | number (sum) | Sum of status duration in seconds. | type: 'sum', sql: ${CUBE}.status_duration_seconds |
avgDuration | avg_status_duration | Measure | number (avg) | Average status duration in seconds. | type: 'avg', sql: ${CUBE}.status_duration_seconds |
Table 6: presenceStatuses (Presence Status Definitions)
Lookup table mapping statuses (Available, Busy, Break, Meeting) and approval rules.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
statusId | status_id | Dimension | number | Unique ID of status. | ${CUBE}."statusId" (Primary Key) |
name | status_name | Dimension | string | Display name (Available, Busy, Lunch, Break). | ${CUBE}."name" |
code | status_code | Dimension | string | System code representation (e.g., aux_break). | ${CUBE}."code" |
color | color_code | Dimension | string | UI color HEX code. | ${CUBE}."color" |
requiresApproval | requires_approval | Dimension | boolean | True if supervisor approval is required. | ${CUBE}."requiresApproval" |
tcxStatus | telephony_status | Dimension | string | Telephony state mapped (Available, Away, DND). | ${CUBE}."tcxStatus" |
enableQueueLogin | enable_group_login | Dimension | boolean | True if this status logs user into groups. | ${CUBE}."enableQueueLogin" |
Table 7: statusMonitor (Real-Time Presence Monitor)
Maintains the immediate presence state of all users. Used for live dashboards.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
id | id | Dimension | number | Primary Key. | ${CUBE}."id" (Primary Key) |
userId | user_id | Dimension | number | Reference to user (joins to users). | ${CUBE}."userId" |
currentStatusId | current_status_id | Dimension | number | Current status (joins to presenceStatuses). | ${CUBE}."currentStatusId" |
previousStatusId | previous_status_id | Dimension | number | Previous status. | ${CUBE}."previousStatusId" |
statusUpdatedTime | status_updated_time | Dimension | time | When current status was entered. | ${CUBE}."statusUpdatedTime" |
| [NEW] | time_in_status_seconds | Dimension | number | Real-time seconds in current status. | EXTRACT(EPOCH FROM (NOW() - ${CUBE}."statusUpdatedTime")) |
Table 8: queueLoginHistory (Group Login Logs)
Tracks when users log in and out of specific groups (queues).
| Existing Field | Proposed Field | Dimension / Measure | Type | Description | SQL Formula / Cube Definition |
|---|---|---|---|---|---|
id | id | Dimension | number | Primary Key. | ${CUBE}."id" (Primary Key) |
userId | user_id | Dimension | number | Reference to user (joins to users). | ${CUBE}."userId" |
shiftId | shift_id | Dimension | number | Reference to shift (joins to workShifts). | ${CUBE}."shiftId" |
queueId | group_id | Dimension | number | Reference to group (joins to groups). | ${CUBE}."queueId" |
loginTime | login_time | Dimension | time | Group login timestamp. | ${CUBE}."loginTime" |
logoutTime | logout_time | Dimension | time | Group logout timestamp. | ${CUBE}."logoutTime" |
| [NEW] | login_duration_seconds | Dimension | number | Duration logged into group in seconds. | EXTRACT(EPOCH FROM (COALESCE(${CUBE}."logoutTime", NOW()) - ${CUBE}."loginTime")) |
count | login_event_count | Measure | count | Count of login events. | type: 'count' |
| [NEW] | total_login_duration | Measure | number (sum) | Sum of logged-in duration. | type: 'sum', sql: ${CUBE}.login_duration_seconds |
| [NEW] | avg_login_duration | Measure | number (avg) | Average logged-in duration. | type: 'avg', sql: ${CUBE}.login_duration_seconds |
Part 3: Widget Mapping Specifications
For each dashboard and report, the tables below map frontend widgets directly to their required backend models, fields, and logical filters.
A. Real-Time Monitoring Dashboards (Live Operations)
Dashboard A1: Inbound Queue & Live Calls Monitor
Exposes live group performance and active queue levels.
📋 Wireframe & Mock
Wireframe Layout (ASCII)
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 🔴 LIVE QUEUE MONITOR [Today] [Filters ▼] [Export] [Refresh ⟳] │
├─────────────────────────────────────────────────────────────────────────────────┤
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ 42 │ │ 18 │ │ 92.5% │ │ 00:45 │ │
│ │ Calls Waiting│ │ Staff Online │ │ SLA Today │ │ Avg Wait │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ ACTIVE QUEUE STAFFING GROUP LOGIN DURATION │
│ ┌────────────────────────────────────────┐ ┌──────────────────────────────┐ │
│ │ Agent │ Status │ Since │ Calls │ │ Support │ Sales │ Billing │ │
│ ├────────────────────────────────────────┤ │ ├──────────────────────────────┤ │
│ │ John Smith │ Available│ 09:30 │ 24 │ │ 8 agents │ 5 agt │ 3 agt │ │
│ │ Jane Doe │ Available│ 10:15 │ 18 │ │ SLA:95% │ 88% │ 91% │ │
│ │ Mike Jones │ On Break│ 14:00 │ 15 │ │ Waiting: │ Waiting│ Waiting │ │
│ │ Sarah Lee │ Available│ 08:45 │ 31 │ │ 12 calls │ 3 calls│ 1 call │ │
│ └────────────────────────────────────────┘ └──────────────────────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ LIVE CALL TIMELINES (Auto-refreshing every 5s) │
│ ┌────────────────────────────────────────────────────────────────────────────┐ │
│ │ From │ To │ State │ Duration │ Group │ Queue Wait │ Agent │ │
│ ├────────────────────────────────────────────────────────────────────────────┤ │
│ │ 555-1001 │ Queue │ Waiting │ 00:42 │ Support │ 00:42 │ — │ │
│ │ 555-1002 │ 1234 (J.) │ Talking │ 00:18 │ Support │ 00:05 │ John │ │
│ │ 555-1003 │ Queue │ Waiting │ 00:28 │ Sales │ 00:28 │ — │ │
│ │ 555-1004 │ 5678 (S.) │ On Hold │ 00:12 │ Support │ 00:03 │ Sarah│ │
│ └────────────────────────────────────────────────────────────────────────────┘ │
│ Last Updated: 14:32:05 UTC | Latency: <1s | Next Refresh: 14:32:10 │
└─────────────────────────────────────────────────────────────────────────────────┘
| Widget Title | Frontend Component | Backend Model | Grouping Dimensions | Metrics / Measures | Active Filters | Description / Purpose |
|---|---|---|---|---|---|---|
| Live Calls Waiting | statboxWidget.tsx | callSegments | None | Distinct count of call_id | segment_state = 'queue_waiting' AND end_time IS NULL | Shows callers currently holding in queue. |
| Logged-in Staff | statboxWidget.tsx | statusMonitor | None | Distinct count of user_id | current_status_id.status_name != 'Offline' | Number of ready/active users. |
| Live SLA Today | statboxWidget.tsx | callParticipants | None | service_level | direction = 'inbound' AND start_time >= CURRENT_DATE | Today's running Service Level %. |
| Active Queue Staffing | tableWidget.tsx | statusMonitor joined to users/presenceStatuses | users.username, presenceStatuses.status_name | time_in_status_seconds | presenceStatuses.status_name != 'Offline' | Live list of agent states sorted by duration. |
| Live Call Timelines | tableWidget.tsx | callSegments joined to calls | calls.telephony_call_id, source_number, segment_state | segment_duration_seconds | end_time IS NULL | Live scrollable list of active calls in progress. |
Dashboard A2: Live Campaigns & Dialer Monitor
Monitors active outbound marketing campaigns and dialer pacing.
📋 Wireframe & Mock
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 🚀 CAMPAIGNS & DIALER MONITOR [Today] [Campaign Filter ▼] [Refresh ⟳] │
├─────────────────────────────────────────────────────────────────────────────────┤
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ 3 │ │ 48/min │ │ 287 │ │ 156 │ │
│ │ Running Cpgn │ │ Dialer Speed │ │ Dials Today │ │ Connections │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ DIAL STATUS PIE CHART │ RUNNING CAMPAIGNS TABLE │
│ ┌──────────────────────────────┐ │ ┌────────────────────────────────────┐ │
│ │ │ │ │ Campaign │ Status │ Dials │ Ans │ │
│ │ Answered (156) │ │ ├────────────────────────────────────┤ │
│ │ 31% ●━━━━━ │ │ │ Summer Sale │ Active │ 145 │ 82 │ │
│ │ Busy (89) │ │ │ Q3 Renewal │ Active │ 98 │ 51 │ │
│ │ 18% ●━━ │ │ │ Trial Offer │ Paused │ 44 │ 23 │ │
│ │ No Answer (142) │ │ └────────────────────────────────────┘ │
│ │ 29% ●━━━━━ │ │ │
│ │ DNC/Invalid (60) │ │ CAMPAIGN DIALS DETAIL (Last 10 mins) │
│ │ 12% ●━━ │ │ ┌────────────────────────────────────┐ │
│ │ Not Reached (40) │ │ │ Call ID │ Lead │ Status │ Agent │ │
│ │ 10% ●━ │ │ │─────────│──────│──────────│────────│ │
│ │ Other (10) │ │ │ C-2841 │ 5821 │ Answered │ Sarah │ │
│ │ 0% ● │ │ │ C-2840 │ 5820 │ Busy │ — │ │
│ │ │ │ │ C-2839 │ 5819 │ Answered │ Mike │ │
│ └──────────────────────────────┘ │ │ C-2838 │ 5818 │ No Answ. │ — │ │
│ │ └────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────────────────┘
| Widget Title | Frontend Component | Backend Model | Grouping Dimensions | Metrics / Measures | Active Filters | Description / Purpose |
|---|---|---|---|---|---|---|
| Running Campaigns | statboxWidget.tsx | calls joined to campaigns | None | Distinct count of campaign_id | campaigns.status = 'active' | Counts active outbound campaigns. |
| Dialer Speed | statboxWidget.tsx | calls | None | Count of starts / min | lead_id IS NOT NULL AND status NOT IN ('dnc','preview') AND start_time >= NOW() - INTERVAL '1 minute' | Pacing indicator of outbound calls. |
| Dial Status Ratios | PieDonutChart.tsx | calls | status | Count of calls | lead_id IS NOT NULL AND start_time >= CURRENT_DATE | Split of today's dialed outcomes (busy, answer, etc.). |
| Campaign Dials Detail | tableWidget.tsx | calls joined to users/callParticipants | telephony_call_id, lead_id, status, dial_attempt, users.username | None | start_time >= CURRENT_DATE - INTERVAL '10 minutes' | Recent calls detail list for audit. |
B. Historical Analysis Reports (Audits & Trends)
Report B1: Agent Shift & Adherence Report
Audits shifts, auxiliary codes (meeting, break), and scheduled compliance.
📋 Wireframe & Mock
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 👤 AGENT SHIFT & ADHERENCE REPORT [Date Range: 08/01 - 08/14] [Export ⬇] │
├─────────────────────────────────────────────────────────────────────────────────┤
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ 412 hrs │ │ 8.2 hrs/avg │ │ 78% Working │ │ 22% AUX Time │ │
│ │ Total Hours │ │ Per Shift │ │ vs Scheduled │ │ (Break/Mtg) │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ TIME SPENT BY STATUS (Pie) │ AGENT AUX MATRIX (Pivot Table) │
│ ┌──────────────────────────────┐ │ ┌──────────────────────────────────────┐ │
│ │ │ │ │ Agent │Working│Break │Lunch│Mtg │ │
│ │ Available (78%) │ │ ├──────────────────────────────────────┤ │
│ │ ●━━━━━━━━━━━━━━━━━━━━━━ │ │ │ John Smith │ 38:24 │ 1:20│ 1:00│0:30│ │
│ │ Break (12%) │ │ │ Jane Doe │ 39:15 │ 0:45│ 1:00│0:30│ │
│ │ ●━━ │ │ │ Mike Jones │ 37:50 │ 2:10│ 1:00│0:30│ │
│ │ Lunch (8%) │ │ │ Sarah Lee │ 38:30 │ 1:00│ 1:00│0:45│ │
│ │ ●━━ │ │ │ Tom Brown │ 36:25 │ 2:35│ 1:00│0:45│ │
│ │ Meeting (2%) │ │ │ Average │ 38:09 │ 1:38│ 1:00│0:36│ │
│ │ ● │ │ └──────────────────────────────────────┘ │
│ │ │ │ ✅ All agents within compliance │
│ └──────────────────────────────┘ │ ⚠️ Mike Jones: High break time │
│ │ │
│ DETAILED SHIFT LOGS │ │
│ ┌────────────────────────────────┐ │ │
│ │ Agent │ Date │ In │Out │ │ │
│ ├────────────────────────────────┤ │ │
│ │ John S. │ 2026-08-14│09:00│18:00│ │
│ │ Jane D. │ 2026-08-14│08:00│17:15│ │
│ │ Mike J. │ 2026-08-14│10:00│19:00│ │
│ │ Sarah L. │ 2026-08-14│09:30│18:30│ │
│ └────────────────────────────────┘ │ │
└─────────────────────────────────────────────────────────────────────────────────┘
| Widget Title | Frontend Component | Backend Model | Grouping Dimensions | Metrics / Measures | Active Filters | Description / Purpose |
|---|---|---|---|---|---|---|
| Hours Worked | statboxWidget.tsx | workShifts | None | total_shift_duration | Selected Time Range | Total hours staffed. |
| Average Shift Length | statboxWidget.tsx | workShifts | None | avg_shift_duration | Selected Time Range | Average clocked shift time. |
| Time Spent in Status | PieDonutChart.tsx | statusLog joined to presenceStatuses | presenceStatuses.status_name | total_status_duration | Selected Time Range | Proportion of AUX vs Available time. |
| Agent AUX Matrix | pivotTableWidget.tsx | statusLog joined to users/presenceStatuses | Rows: users.usernameColumns: presenceStatuses.status_name | total_status_duration | Selected Time Range | Pivot matrix of auxiliary state utilization by agent. |
| Detailed Shift Logs | tableWidget.tsx | workShifts joined to users | users.username, start_time, end_time | shift_duration_seconds | Selected Time Range | Raw list of clocked user shifts. |
Report B2: Inbound Queues & SLA Performance Report
Trends SLA metrics, abandonment thresholds, and speed of answer.
📋 Wireframe & Mock
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 📊 INBOUND QUEUES & SLA PERFORMANCE [Week] [Group Filter ▼] [PDF] │
├─────────────────────────────────────────────────────────────────────────────────┤
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ 92.8% │ │ 3.2% │ │ 156 │ │ 00:45 │ │
│ │ Total SLA │ │ Abandon Rate │ │ Total Calls │ │ Avg Wait │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ SLA TREND BY GROUP (Line Chart) │
│ ┌──────────────────────────────────────────────────────────────────────────┐ │
│ │ SLA % ▲ Support ——— Sales ---- Billing ╌╌╌╌╌ │ │
│ │ 100 ▼ │ │
│ │ 95 ├─ ──●─── ●──●──●──●── ● ← Support (95%) │ │
│ │ 90 ├──●──●──●──────────────────●────●─ ← Sales (88%) │ │
│ │ 85 ├──────────────●──●─●──●─●─── ──── ← Billing (92%) │ │
│ │ 80 ├────────────────────────────────── ← Target (80%) │ │
│ │ └────────────────────────────────────── (Mon-Sun this week) │ │
│ └──────────────────────────────────────────────────────────────────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ ABANDON WAIT CURVE (Sunburst) │ WAIT vs ASA TREND (2 Line Chart) │
│ ┌──────────────────────────────┐ │ ┌──────────────────────────────────┐ │
│ │ ┌─────────────────┐ │ │ │ Avg Wait (blue) ASA (orange) │ │
│ │ ╱ Support Queue ╲ │ │ │ │ 2:00 ▲ │ │
│ │ ╱ ├─0-1 min ╲ │ 12% │ │ 1:50 ─┼─────●─────●────●─────● │ │
│ │ ╱ ├─1-3 min ╲│ 18% │ │ 1:40 ─┼●───●────●────●───● │ │
│ │ ╱ ├─3-5 min ╲ │ 38% │ │ 1:30 ─┼─●────────────────── │ │
│ │ ╱ ├─5+ min ╲│ 32% │ │ 1:20 ─┴────────────────────── │ │
│ │ ╱ └─────────────────┘ │ │ Mon Tue Wed Thu Fri │ │
│ │ │ └──────────────────────────────────┘ │
│ │ Abandoned = 3.2% (5 of 156) │ Correlation: Wait time peaked Wed │
│ └──────────────────────────────────┘ ASA improved by 12s mid-week │
└─────────────────────────────────────────────────────────────────────────────────┘
| Widget Title | Frontend Component | Backend Model | Grouping Dimensions | Metrics / Measures | Active Filters | Description / Purpose |
|---|---|---|---|---|---|---|
| Total Inbound SLA | statboxWidget.tsx | callParticipants | None | service_level | direction = 'inbound' | Total historical Service Level %. |
| Total Abandon Rate | statboxWidget.tsx | callParticipants | None | abandon_rate | direction = 'inbound' | Percentage of group calls abandoned. |
| SLA Trend by Group | LineAreaChart.tsx | callParticipants joined to groups | start_time (by Day/Week), groups.name | service_level | direction = 'inbound' | SLA trend comparison over time. |
| Abandon Wait Curve | SunburstWidget.tsx | callParticipants joined to groups | groups.name $\rightarrow$ Wait Duration Bucket | abandoned_calls | direction = 'inbound' | Shows queue wait time before hangs. |
| Wait vs ASA Trend | LineAreaChart.tsx | callParticipants | start_time (by Day) | avg_queue_wait_duration, average_speed_of_answer | direction = 'inbound' | Visualizes speed of answer vs overall waits. |
Report B3: Outbound Campaign Historical Report
Audits campaign outcomes, agent conversion, and lead lists.
📋 Wireframe & Mock
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 🎯 CAMPAIGN HISTORICAL REPORT [Aug 2026] [Campaign Filter ▼] [Download ⬇] │
├─────────────────────────────────────────────────────────────────────────────────┤
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ 523 │ │ 287 │ │ 54.9% │ │ 156 │ │
│ │ Total Dials │ │ Unique Leads │ │ Connection % │ │ Connections │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ OUTCOME SPLIT (Donut Chart) │ CAMPAIGN MATRIX (Pivot Table) │
│ ┌──────────────────────────────┐ │ ┌──────────────────────────────────────┐ │
│ │ │ │ │ Campaign │Answered│Busy│No Ans│DNC│ │
│ │ Answered (156) 30% │ │ ├──────────────────────────────────────┤ │
│ │ ●────────────┐ │ │ │ Summer Sale │ 82 │ 28 │ 18 │17 │ │
│ │ Busy (89) 17% │ ●────┤ │ │ Q3 Renewal │ 51 │ 19 │ 15 │13 │ │
│ │ No Answer (142) 27%─┘ │ │ │ Trial Offer │ 23 │ 8 │ 7 │ 6 │ │
│ │ DNC (78) 15% │ │ │ Partner Promo │ 12 │ 4 │ 3 │ 3 │ │
│ │ Other (58) 11% │ │ │ TOTAL │ 168 │ 59 │ 43 │39 │ │
│ │ │ │ │ Total Dials │ 523 │ │
│ │ Click for raw dial list ▶ │ │ │ Connection % │ 32%│ │
│ └──────────────────────────────┘ │ └──────────────────────────────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ CRM MODULE SOURCE (Bar Chart) │
│ ┌──────────────────────────────────────────────────────────────────────────┐ │
│ │ Leads │ │ │
│ │ 300 ├─ │ │
│ │ 250 ├─ ┌─────────┐ │ │
│ │ 200 ├─ │ 287 │ ┌──────┐ │ │
│ │ 150 ├─ │ Leads │ │ 156 │ ┌────┐ ┌───┐ │ │
│ │ 100 ├─ │ Module │ │Sales │ │ 45 │ │ 8 │ │ │
│ │ 50 ├─ │ A │ │ Opp │ │Apps│ │EDI│ │ │
│ │ 0 └─────────────────────────────────────────────────────────────────│ │
│ │ Module A Module B Sales Opp Apps Internal EDI Feeds │ │
│ └──────────────────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────────────────┘
| Widget Title | Frontend Component | Backend Model | Grouping Dimensions | Metrics / Measures | Active Filters | Description / Purpose |
|---|---|---|---|---|---|---|
| Total Campaign Dials | statboxWidget.tsx | calls | None | campaign_dialed_calls | lead_id IS NOT NULL | Total dialed outbound leads. |
| Unique Leads Reached | statboxWidget.tsx | calls | None | campaign_unique_leads_answered | lead_id IS NOT NULL | Count of unique leads connected. |
| Outcome Split | PieDonutChart.tsx | calls | status | total_calls | lead_id IS NOT NULL | Percentage breakdown of outcomes. |
| Campaign Matrix | pivotTableWidget.tsx | calls joined to campaigns | Rows: campaigns.nameColumns: status | total_calls | lead_id IS NOT NULL | Call counts per campaign per status. |
| CRM Module Source | BarColumnChart.tsx | calls | module_id | total_calls | module_id IS NOT NULL | Shows call counts triggered by CRM module source. |
Report B4: Historical Time Profiles (Hour/Day Breakdowns)
Highlights high-traffic hourly profiles and day-of-week load volumes.
📋 Wireframe & Mock
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 📅 HISTORICAL TIME PROFILES [Aug 2026] [Week View] [Heat Map] │
├─────────────────────────────────────────────────────────────────────────────────┤
│ TRAFFIC HEATMAP (Calls by Hour × Day) │
│ ┌─────────────────────────────────────────────────────────────────────────┐ │
│ │ Mon Tue Wed Thu Fri Sat Sun Legend │ │
│ │ 0-1 AM │ 🟦 🟦 🟦 🟦 🟦 🟦 🟦 ░░░░░░░ │ │
│ │ 1-2 AM │ 🟦 🟦 🟦 🟦 🟦 🟦 🟦 Low: 5-15 │ │
│ │ 2-3 AM │ 🟦 🟦 🟦 🟦 🟦 🟦 🟦 Medium: 16-50 │ │
│ │ ... │ ⋮ │ │
│ │ 9-10 AM │ 🟩 🟩 🟩 🟩 🟩 🟥 🟥 High: 51-100 │ │
│ │ 10-11 AM │ 🟩 🟩 🟨 🟩 🟩 🟥 🟥 Peak: 100+ │ │
│ │ 11-12 PM │ 🟨 🟨 🟥 🟨 🟥 🟥 🟥 │ │
│ │ 12-1 PM │ 🟥 🟥 🟥 🟥 🟥 🟨 🟨 Hotspot: Wed 11-1 (peak) │ │
│ │ 1-2 PM │ 🟩 🟩 🟨 🟩 🟩 🟦 🟦 Coldspot: Sat/Sun nights │ │
│ │ 2-3 PM │ 🟩 🟩 🟩 🟩 🟩 🟦 🟦 │ │
│ │ ... │ ⋮ │ │
│ │ 11-12 PM │ 🟦 🟦 🟦 🟦 🟦 🟦 🟦 │ │
│ └─────────────────────────────────────────────────────────────────────────┘ │
├─────────────────────────────────────────────────────────────────────────────────┤
│ TRAFFIC & AHT TRENDS (Dual Axis Chart) HOURLY DETAIL MATRIX │
│ ┌───────────────────────────────────────────┐ ┌───────────────────────────────┐ │
│ │ Calls ▲ AHT ▲ │ │ Hour │ Inbound │ Outbound │ │
│ │ 500 ├─●───●─────●─────● │ │ ─────────────────────────────│ │
│ │ 400 ├─●────────────●──── │ │ 09:00 │ 45 │ 28 │ │
│ │ 300 ├────────────────── ← Calls │ │ 10:00 │ 58 │ 41 │ │
│ │ 200 ├────────────────── │ │ 11:00 │ 72 │ 35 │ │
│ │ 100 ├────────────────── │ │ 12:00 │ 91 │ 42 │ │
│ │ ├──────────────────────────────────│ │ 13:00 │ 68 │ 38 │ │
│ │ 600s ├────●──●───●───●──●──● ← AHT │ │ 14:00 │ 52 │ 45 │ │
│ │ 500s ├───●────●───●───●────● │ │ 15:00 │ 49 │ 52 │ │
│ │ 400s ├────────────────────── │ │ Average AHT rising in │ │
│ │ └─────────────────────────────────│ │ peak hours (lunch effect) │ │
│ │ Mon Tue Wed Thu Fri Sat Sun │ └───────────────────────────────┘ │
│ └───────────────────────────────────────────┘ │
│ │
│ Insights: Peak traffic Wed-Fri 11am-1pm | AHT increases 15% in peak window │
│ Weekend traffic down 60% | Consider staffing allocation review │
└─────────────────────────────────────────────────────────────────────────────────┘
| Widget Title | Frontend Component | Backend Model | Grouping Dimensions | Metrics / Measures | Active Filters | Description / Purpose |
|---|---|---|---|---|---|---|
| Traffic Heatmap | HeatMapWidget.tsx | calls | X-Axis: Day of Week Y-Axis: start_hour | total_calls | Selected Time Range | Displays high-traffic time blocks. |
| Traffic & AHT Trends | LineAreaChart.tsx | callParticipants | start_time (by Day) | total_calls, avg_handle_time | Selected Time Range | Trends call volume vs agent handle time. |
| Hourly Detail Matrix | pivotTableWidget.tsx | callParticipants | Rows: start_hourColumns: direction | total_calls, avg_handle_time | Selected Time Range | Performance metrics per hour of day. |