Contact Center Analytics
Enum Definitions & UI Styling Mappings
Exposes the standard enums used by string dimensions, including color codes and friendly labels for widget styling.
1. call_type
| Value | Label (UI Display) | UI Color |
|---|---|---|
inbound | Inbound | #3B82F6 (Blue) |
outbound | Outbound | #10B981 (Green) |
campaign | Campaign | #8B5CF6 (Purple) |
internal | Internal | #6B7280 (Gray) |
conference | Conference | #6B7280 (Gray) |
2. status (Call/Dialer Outcome)
| Value | Label (UI Display) | UI Color |
|---|---|---|
answered | Answered | #10B981 (Green) |
not_answered | Missed | #F59E0B (Orange) |
missed | Missed | #F59E0B (Orange) |
abandoned | Abandoned | #EF4444 (Red) |
agent_rejected | Rejected by User | #D1D5DB (Light Gray) |
dnc | Do Not Call | #4B5563 (Dark Gray) |
preview | Preview | #6366F1 (Indigo) |
3. direction
| Value | Label (UI Display) | UI Color |
|---|---|---|
inbound | Inbound | #3B82F6 (Blue) |
outbound | Outbound | #10B981 (Green) |
4. segment_state (Call Legs)
| Value | Label (UI Display) | UI Color |
|---|---|---|
ringing | Ringing | #F59E0B (Orange) |
talking | Connected | #10B981 (Green) |
hold | On Hold | #EF4444 (Red) |
aftercall | Wrap-up (ACW) | #8B5CF6 (Purple) |
dialing | Dialing | #6366F1 (Indigo) |
queue_waiting | Waiting in Group | #EC4899 (Pink) |
5. source_type / destination_type (Leg Endpoints)
| Value | Label (UI Display) | UI Color |
|---|---|---|
user | User (Extension) | #10B981 (Green) |
external | External Line | #3B82F6 (Blue) |
ivr | IVR | #8B5CF6 (Purple) |
group | Group Queue | #EC4899 (Pink) |
6. termination_reason
| Value | Label (UI Display) | UI Color |
|---|---|---|
completed | Completed | #10B981 (Green) |
canceled | Canceled | #6B7280 (Gray) |
transfer | Transferred | #6366F1 (Indigo) |
no_answer | No Answer | #F59E0B (Orange) |
busy | Busy | #EF4444 (Red) |
failed | Failed | #DC2626 (Dark Red) |
Model Definitions & Fields Mapping
Table 1: calls (Unique Call Stats)
Exposes call detail metadata. One row per telephony call. Exposes only 2 core measures.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description / Enums | 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 | Enum: see call_type | ${CUBE}."call_type" |
status | status | Dimension | string | Enum: see status | ${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 | Enum: see termination_reason | ${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" |
| Seeded (Removed) | start_hour | Dimension (Virtual) | string | Virtual Dimension: Exposes abstract hour (00 to 23) for profiling. | EXTRACT(HOUR FROM ${CUBE}."start_time")::text |
| Seeded (Removed) | day_of_week | Dimension (Virtual) | string | Virtual Dimension: Exposes day name (Monday to Sunday) for profiling. | TO_CHAR(${CUBE}."start_time", 'FMDay') |
count | total_calls | Measure | count | Base Measure: Total count of unique calls. Filter on client side for status/campaign types. | type: 'count' |
campaignUniqueDialedCallCount | unique_leads | Measure | countDistinct | Base Measure: Distinct count of CRM leads dialed. Filter by status on client side for dials/reached. | sql: ${CUBE}."leadId", filters: leadId IS NOT NULL |
Table 2: callParticipants (Performance & SLA)
[!NOTE] Formerly
summarizeCalls. Summarizes legs into user and group sessions. Exposes only core base counts and duration sums. Rates are calculated in BI dashboards using DAX / client-side formulas.
| Existing Field | Proposed Field | Dimension / Measure | Type | Description / Enums | 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. | ${CUBE}.user_id |
group_id | group_id | Dimension | number | Reference to handling group. | ${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 | Enum: see direction | ${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) |
termination_reason | termination_reason | Dimension | string | Enum: see termination_reason | ${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) | Base Measure: Distinct total call count. Client applies status/SLA filters on this. | type: 'countDistinct', sql: ${CUBE}.call_id |
talk_duration | total_talk_duration | Measure | number (sum) | Total talk duration in seconds. | type: 'sum', 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 |
wrap_time | total_wrap_duration | Measure | number (sum) | Total wrap duration in seconds. | type: 'sum', 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 |
| [NEW] | total_handle_duration | Measure | number (sum) | Total handle time in seconds. | type: 'sum', 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 / Enums | 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 | Enum: see segment_state | ${CUBE}.status |
source_number | source_number | Dimension | string | Dialing source phone number/extension. | ${CUBE}.source_number |
source_type | source_type | Dimension | string | Enum: see source_type | ${CUBE}.source_type |
destination_number | destination_number | Dimension | string | Destination phone number/extension. | ${CUBE}.destination_number |
destination_type | destination_type | Dimension | string | Enum: see destination_type | ${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" |
termination_reason | termination_reason | Dimension | string | Enum: see termination_reason | ${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 |
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 / Enums | 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) | Sum of shift duration in seconds. | type: 'sum', 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 / Enums | 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. | ${CUBE}."statusId" |
timestamp | start_time | Dimension | time | Timestamp status state was entered. | ${CUBE}."timestamp" |
| [NEW] | end_time | Dimension | time | Timestamp status state 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 |
Table 6: 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 / Enums | 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 reference. | ${CUBE}."currentStatusId" |
previousStatusId | previous_status_id | Dimension | number | Previous status reference. | ${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 7: queueLoginHistory (Group Login Logs)
Tracks when users log in and out of specific groups (queues).
| Existing Field | Proposed Field | Dimension / Measure | Type | Description / Enums | 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 |