Reports
Hybrid chat provides a number of different reports including, Agent Availability, Chat productivity, Chat Detail, Chat Volume by Queue, Chat Volume by Channel, and Agent Chat Productivity. All these reports are shipped with a basic Hybrid Chat license in Cisco CUIC. These can also be enabled in any third-party tools that you use. Hybrid Chat, in this case, will expose its reporting DB.
Reports are developed on the following solutions. See the report's availability status on each under the Status column.
- CUIC - Cisco Unified Intelligence Center for UCCX and UCCE - Used when Hybrid Chat run in integrated mode with Cisco contact center.
- EFBI - Expertflow Reporting solution on Windows with SQL Server backend - May be used when Hybrid Chat is deployed in a standalone mode.
- EFBI-Custom - A customized implementation of EFBI - Used on some legacy deployments.
| Title | Report Summary | Status |
|---|---|---|
| Agent Availability for Chat Report | Day Wise statistics on agent availability |
CUIC DEVELOPED, EF-BI NOT DEVELOPED, EFBI-Custom DEVELOPED |
| Agent Chat Productivity by Channel | Daily Agent productivity by Chat report |
CUIC DEVELOPED, EF-BI NOT_DEVELOPED, EFBI-Custom NOT_DEVELOPED |
| Agent Chat Productivity by Queue | Daily Agent Productivity by Queue Report |
CUIC DEVELOPED, EF-BI NOT_DEVELOPED, EFBI-Custom NOT_DEVELOPED |
| Agent Chat Leg Detail Report | Shows a detail of all conversation legs handled/answered by the agents including RONA legs. The actual number of conversations may be different. |
CUIC DEVELOPED, EF-BI NOT_DEVELOPED, EFBI-Custom NOT_DEVELOPED |
| Chat Volume By Queue | Display the summary of Queue Wise Offered Chats |
CUIC DEVELOPED, EF-BI NOT_DEVELOPED, EFBI-Custom NOT_DEVELOPED |
| Chat Volume By Channel | Channel-Wise Daily Chat Statistics |
CUIC DEVELOPED, EF-BI NOT DEVELOPED, EFBI-Custom NOT DEVELOPED |
| Chat Volume By Bot | Channel-Wise Daily Chat Statistics |
CUIC DEVELOPED, EF-BI NOT_DEVELOPED, EFBI-Custom NOT_DEVELOPED |
Entity Relationship Diagram
Ready to go
This is the SQL server reporting database schema of Hybrid Chat.
Agents
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| agent_id | NVARCHAR(50) | PK, NOT NULL | In the case of standalone deployment, this is the same as the UMM Username. In the case of CCX integration, this is CCX resource_login_id. In the case of Cisco CCE integration, it's PeripheralNumber. |
| name | NVARCHAR(110) | NOT NULL | In the case of standalone deployment, This is UMM fullName. In the case of Cisco Finesse integration, this is concatenated resource_first_name + space + last_name. |
| createdAt | DATETIME | NOT NULL | The time when this record was created/inserted into the database. |
| updatedAt | DATETIME | NOT NULL | The time when the agent record was last updated in the database. |
| is_deleted | BIT | NOT NULL | If the agent is deleted, the value should be 1. If the agent is active, the value should be 0. |
Agent_MRD_State_Events
This table contains agent state change within that MRD. Upon each state change event, a record is inserted in this table.
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| id | INT | PK NOT NULL | Autoincrement |
| agent_id | NVARCHAR(50) | FK NOT NULL | Foreign key from the Agent table |
| mrd_id | INT | FK NOT NULL | Foreign key from the MRD table |
| state | VARCHAR(50) | NOT NULL | Hybrid Chat MRD states. [LOGGED_IN | READY | ACTIVE | NOT_READY | BUSY | INTERRUPTED | UNKNOWN | LOGOUT] |
| mode | BIT | NOT NULL | If agent mode is routable, the value should be 1. |
| reason_code | INT | FK, NULL | Foreign key from the Reason Code table - Reserved for future use. |
| event_time | DATETIME | NOT NULL | The time when the state change event occurred. |
| createdAt | DATETIME | NOT NULL | The time when the record is inserted in this table. |
| login_time | DATETIME | NOT NULL | The time when the agent logged in. For all agent state change events, this value remains the same for a single logged-in session. |
Queues
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| queue_id | INT | PK, NOT NULL | The auto-generated auto-increment Queue identifier. |
| queue_name | NVARCHAR(50) | NOT NULL | The queue name as mentioned in MRE UI. This name cannot be changed in MRE once created. |
| service_level_threshold | SMALL INT | NOT NULL | Option to add the SL threshold of the queue at the time of the creation. This is the time in seconds. It contains only positive values from 0-MAX_INT. |
| service_level_type | SMALL_INT | NOT NULL | The value will be (1,2, or 3) 1- Ignore abandoned chats (Subtracted from the Offered Chats during Service Level Calculation). 2- Abandoned chats have a negative impact (Not included at all in the Service Level Calculation). 3- Abandoned chats have a positive impact ( Added with the Service Level answered chats). |
| createdAt | DATETIME | NOT NULL | The time when the queue was created in MRE/MRE_UI. |
| updatedAt | DATETIME | NOT NULL | The time when the queue is updated in MRE/MRE_UI. |
| is_deleted | BIT | NOT NULL | If the queue is deleted, the value should be 1. If the queue is active, the value should be 0. |
Queue Chat Details
Each record in this table represents a chat handling session by a contact center resource (agent or bot).
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| id | INT | PK NOT NULL | auto increment |
session_id TASK-ID | VARCHAR (50) | NULL | This ID is assigned to every chat request for queuing to the agent by the Routing Engine. If the chat request is re-routed, the same session_id is preserved. There can be multiple sessions for the same conversation. In the customer-to-bot conversation leg, the value is |
conversation_idCHAT-SESSION-ID | VARCHAR (100) | NOT NULL | It's the Conversation ID from the Chat Server. It's unique ID for individual chat session. |
customer_id | VARCHAR (100) | NOT NULL | A random id assigned to the customer by the Chat Server as a chat participant. It has nothing to do with customer identification. |
| customer_name | NVARCHAR (100) | NOT NULL | The name of the customer as received from the customer channel. For:
|
| customer_nd | VARCHAR (100) | NOT NULL | Customer unique identifier
|
| queue_id | int | FK, NULL | Foreign key from the Queue table |
| agent_id | NVARCHAR(50) | FK, NULL | Foreign key from the Agent table. The value will be |
| bot_id | NVARCHAR(50) | FK, NULL | The ID of the bot that handled this chat leg. Foreign key from the Bots table |
| type | VARCHAR (50) | NOT NULL | INBOUND | TRANSFER | CONFERENCE
|
| enqueue_time | DATETIME | NULL | The time when the chat was enqueued. If NULL, the chat was handled by the chatbot and wasn't enqueued for routing. |
| start_time | DATETIME | NULL | The time when the task state transitions to ACTIVE. This happens when the agent accepts the chat or when the chat is automatically started with the agent due to AUTO_ACCEPT. If NULL means that the agent did not accept the chat request. |
| end_time | DATETIME | NULL |
|
| ended_by | VARCHAR(50) | NULL | Specifies which participant ended the chat, or it was ended due to a network outage.
|
| chat_link | NVARCHAR(250) | NULL | This is the chat history URL for the conversation, so all messages exchanged on that conversation will be available on this link. |
| createdAt | DATETIME | NOT NULL | It's the time when the record was inserted into the database |
| updatedAt | DATETIME | NULL | It's the time when the record was updated into the database |
| channel_code | INT | FK NULL | Foreign key from the Channel table |
| channel_data_1 | NVARCHAR(250) | NULL | Channel data parameter 1 |
| channel_data_2 | NVARCHAR(250) | NULL | Channel data parameter 2 |
| channel_data_3 | NVARCHAR(250) | NULL | Channel data parameter 3 |
| channel_data_4 | NVARCHAR(250) | NULL | Channel data parameter 4 |
| channel_data_5 | NVARCHAR(250) | NULL | Channel data parameter 5 |
| session_start_time | DATETIME | NULL | This is the time when the new chat leg is created in the system. For example:
|
Wrapup
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| id | INT | AI | Auto increment ID |
| conversation_id | VARCHAR(100) | NOT NULL | |
| agent_id | NVARCHAR(50) | FK, NOT NULL | Foreign key from the Agent table. |
| wrapup_time | DATETIME | NOT NULL | The time when wrapup was applied |
| wrapup_label | NVARCHAR (50) | NOT NULL | Wrap-up label |
| category_name | NVARCHAR (50) | NULL | Category Name |
| createdAt | DATETIME | NOT NULL | The time when this record was created in the database |
Channels
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| channel_code | INT | PK, NOT NULL | |
| Name | VARCHAR(50) | NOT NULL | Multiple channel instances of the same channel may be specified as WhatsApp099, WhatsApp9800 |
| createdAt | DATETIME | NOT NULL | The time when the record is created. |
| updatedAt | DATETIME | NOT NULL | The time when the record is updated. |
| is_deleted | BIT | NOT NULL | If the queue is deleted, the value should be 1. If the queue is active, the value should be 0. |
MRDs
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| mrd_id | INT | PK NOT NULL | |
| Name | VARCHAR(50) | NULL | |
| createdAt | DATETIME | NOT NULL | The time when the record is inserted into this table |
| updatedAt | DATETIME | NOT NULL | The time when the record is updated in this table |
| is_deleted | BIT | NOT NULL | If the queue is deleted, the value should be 1. If the queue is active, the value should be 0. |
Bots
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| bot_id | NVARCHAR(50) | NOT NULL | Identifier of the bot that participated in this conversation |
| bot_name | NVARCHAR(50) | NOT NULL | Name of the bot as given in the bot-connector configurations |
| createdAt | DATETIME | NOT NULL | The time when the record is inserted into this table |
| updatedAt | DATETIME | NOT NULL | The time when the record is updated in this table |
| is_deleted | BIT | NOT NULL | If the bot is deleted, the value should be 1. If the bot is active, the value should be 0. |
Reason Codes
This table is intended for future use
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| reason_code | INT | PK NOT NULL | |
| state | VARCHAR(50) | NOT NULL | Hybrid Chat MRD states. [NOT_READY | UNKNOWN | LOGOUT] |
| Description | VARCHAR(100) | NULL | |
| createdAt | DATETIME | NOT NULL | The time when the record is inserted into this table |
| updatedAt | DATETIME | NOT NULL | The time when the record is updated in this table |
| is_deleted | BIT | NOT NULL | If the queue is deleted, the value should be 1. If the queue is active, the value should be 0. |
Chat Session
Session-wise detailed data.
For Future Use
| Field | Data Type | Constraints | Description |
|---|---|---|---|
| id | INT | PK | Autoincrement |
| Chat session id | |||
| session_type | VARCHAR(50) | NOT NULL | Chat session type e.g. BOT | AGENT | HYBRID |
| conversation_id | VARCHAR(100) | NOT NULL | Conversation Id |
| customer_nd | NVARCHAR(100) | NOT NULL | Customer identifier |
| start_time | DATETIME | NOT NULL | Chat session start time |
| end_time | DATETIME | NOT NULL | Chat session end time |
| transcript_url | NVARCHAR(250) | NOT NULL | Chat transcript URL, not to be confused by chat history URL. |
| channel_code | INT | NOT NULL | Channel for which this chat session was initiated |
Hybrid Chat Reporting Concepts
| Abandoned Chat | A chat request is considered abandoned if it was queued for a human agent but ended without finding any agent. -- QUERY TO list all the Abandoned Chats
SELECT *
FROM Queue_Chat_Details AS QCD
WHERE
QCD.enqueue_time IS NOT NULL
AND QCD.start_time IS NULL
AND QCD.ended_by IN ('CUSTOMER', 'NETWORK', 'BOT')
-- No clause for checking if the chat was actually ended
-- EndedBy NULL would include all the legs not yet ended. No?
-- Should have considered the following too
AND QCD.end_time IS NOT NULL
|
|---|---|
| Answered Chat | A chat request is considered answered by agent if it was handled by at least one agent. If a chat request is handled by more than one agents, answered count should still be 1. -- QUERY TO list all the chats legs answered by agents SELECT * FROM Queue_Chat_Details AS QCD WHERE QCD.enqueue_time IS NOT NULL -- this is even redundant, only start_time not null is enough AND QCD.start_time IS NOT NULL AND QCD.end_time IS NOT NULL
|
| Service Level calculations | ServiceLevel is defined at the Queue level. ServiceLevel should be calculated based on all the legs of a chat request (conversationID) and not for an individual leg.
|