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.




Entity Relationship Diagram

Ready to go
  
 


This is the SQL server reporting database schema of Hybrid Chat. 


Agents

FieldData TypeConstraints

Description

agent_idNVARCHAR(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.

nameNVARCHAR(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.

createdAtDATETIMENOT NULLThe time when this record was created/inserted into the database.
updatedAtDATETIMENOT NULLThe time when the agent record was last updated in the database.
is_deletedBITNOT 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. 

FieldData TypeConstraintsDescription
idINTPK
NOT NULL
Autoincrement
agent_idNVARCHAR(50)FK
NOT NULL

Foreign key from the Agent table

mrd_idINTFK
NOT NULL
Foreign key from the MRD table
stateVARCHAR(50)NOT NULL

Hybrid Chat MRD states. 

[LOGGED_IN | READY | ACTIVE | NOT_READY | BUSY | INTERRUPTED | UNKNOWN | LOGOUT]

modeBITNOT NULL

If agent mode is routable, the value should be 1.
If agent mode is not routable, the value should be 0.
It is used for Pending NOT READY state handling. Routable mode specifies that an agent can accept new chats either he/she is in READY / ACTIVE states. If an agent's mode is not routable, he/she cannot accept new chat requests and after current chats (if any) end, the agent state will be changed to NOT READY.

reason_codeINTFK, NULLForeign key from the Reason Code table - Reserved for future use.
event_timeDATETIMENOT NULLThe time when the state change event occurred.
createdAtDATETIMENOT NULLThe time when the record is inserted in this table.
login_timeDATETIMENOT NULLThe time when the agent logged in. For all agent state change events, this value remains the same for a single logged-in session.

Queues

FieldData TypeConstraintsDescription
queue_idINTPK, NOT NULLThe auto-generated auto-increment Queue identifier. 
queue_nameNVARCHAR(50)NOT NULL

The queue name as mentioned in MRE UI. This name cannot be changed in MRE once created.

service_level_thresholdSMALL INTNOT NULLOption 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_typeSMALL_INTNOT 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). 

createdAtDATETIMENOT NULLThe time when the queue was created in MRE/MRE_UI. 
updatedAtDATETIMENOT NULLThe time when the queue is updated in MRE/MRE_UI. 
is_deletedBITNOT 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).

FieldData TypeConstraintsDescription
idINTPK
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 NULL. This is not a chat session-id. If you want to see individual sessions in a conversation, use customer_id.

conversation_idCHAT-SESSION-ID

VARCHAR (100)NOT NULLIt's the Conversation ID from the Chat Server. It's unique ID for individual chat session. 

customer_id


VARCHAR (100)NOT NULLA random id assigned to the customer by the Chat Server as a chat participant. It has nothing to do with customer identification.
customer_nameNVARCHAR (100)NOT NULL

The name of the customer as received from the customer channel. For: 

  • Web Chat, it's the concatenation (FName, LName)
  • WhatsApp, it's the WhatsApp id sent by Twilio i.e +<phone-number> 
  • Facebook, it's the concatenation of customer account first name & last name
  • Viber, it's the concatenation of customer account first name & last name
  • SMS, it's the customer phone number
customer_ndVARCHAR (100)NOT NULL

Customer unique identifier

  1. Viber UserID in case of Viber - stored after Base64 encoding
  2. Facebook UserID in case of Facebook - stored after Base64 encoding
  3. Phone Number in case of WhatsApp, SMS, Web Chat - stored as received
queue_idintFK, NULLForeign key from the Queue table
agent_idNVARCHAR(50)FK, NULL

Foreign key from the Agent table. The value will be NULL for the customer-2-bot leg.

bot_idNVARCHAR(50)FK, NULL

The ID of the bot that handled this chat leg.

Foreign key from the Bots table

typeVARCHAR (50)NOT NULL

INBOUND | TRANSFER | CONFERENCE

  • INBOUND, when a customer's chat request is received
  • TRANSFER, when a chat is transferred to another agent
  • CONFERENCE, when the chat is conferenced.
enqueue_timeDATETIMENULLThe time when the chat was enqueued. If NULL, the chat was handled by the chatbot and wasn't enqueued for routing.
start_timeDATETIMENULLThe 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_timeDATETIMENULL
  • For Transferred chat, it's the time of transfer.
  • For RONA, it's the event time when the chat is re-queued on no response from an agent
  • When an agent leaves the conference, end_time is updated. 
  • For all other cases, it's the time when the task sate changed to CLOSED. That's when the chat is ended either by the customer, agent, or network. 
  • If NULL, the chat is not ended yet.
ended_byVARCHAR(50)NULL

Specifies which participant ended the chat, or it was ended due to a network outage.
Possible values are CUSTOMER | AGENT | NETWORK | RONA | BOT

  • RONA - In case ended_by RONA, the agent_id contains ID of the agent and start_time is nul
  • NETWORK - disconnection due to network outage. For example, in the case of web chat when the customer:
    • Closed the browser/browser-tab, 
    • web-socket disconnected automatically due to no communication on the channel
    • any network outage between the customer browser and the system
  • CUSTOMER - when the customer closes the web-chat widget.
  • BOT - when the Bot closes the conversation based on:
    • any of the customer's message or intent.
    • other reasons of Bot training 
chat_linkNVARCHAR(250)NULLThis is the chat history URL for the conversation, so all messages exchanged on that conversation will be available on this link.
createdAtDATETIMENOT NULLIt's the time when the record was inserted into the database
updatedAtDATETIMENULLIt's the time when the record was updated into the database
channel_codeINTFK  NULLForeign key from the Channel table
channel_data_1NVARCHAR(250)NULLChannel data parameter 1
channel_data_2NVARCHAR(250)NULLChannel data parameter 2
channel_data_3NVARCHAR(250)NULLChannel data parameter 3
channel_data_4NVARCHAR(250)NULLChannel data parameter 4
channel_data_5NVARCHAR(250)NULLChannel data parameter 5
session_start_timeDATETIMENULL

This is the time when the new chat leg is created in the system.

For example:

  • In the bot first scenarios,
    • For the customer-to-bot leg, when the chat lands on the system and answered by the bot.
  • For the customer-to-agent, when the chat enqueue request arrives. enqueue_time and session_start_time will have almost the same value.
  • For the customer-to-agent, when the chat is answered there's no change in the session_start_time.
  • For the survey-via-bot leg after the chat handling, a new customer-to-bot leg is created. session_start_time is the time when the survey leg was created.

Wrapup

FieldData TypeConstraintsDescription
idINTAIAuto increment ID
conversation_idVARCHAR(100)

NOT NULL 


agent_idNVARCHAR(50)FK, NOT NULL

Foreign key from the Agent table.

wrapup_timeDATETIMENOT NULLThe time when wrapup was applied
wrapup_labelNVARCHAR (50)NOT NULLWrap-up label
category_nameNVARCHAR (50)NULLCategory Name
createdAtDATETIMENOT NULLThe time when this record was created in the database

Channels

FieldData TypeConstraintsDescription
channel_codeINTPK, NOT NULL
NameVARCHAR(50)NOT NULLMultiple channel instances of the same channel may be specified as WhatsApp099, WhatsApp9800
createdAtDATETIMENOT NULLThe time when the record is created. 
updatedAtDATETIMENOT NULLThe time when the record is updated.
is_deletedBITNOT NULL

If the queue is deleted, the value should be 1. 

If the queue is active, the value should be 0. 

MRDs

FieldData TypeConstraintsDescription
mrd_idINTPK NOT NULL
NameVARCHAR(50)NULL
createdAtDATETIMENOT NULLThe time when the record is inserted into this table
updatedAtDATETIMENOT NULLThe time when the record is updated in this table
is_deletedBITNOT NULL

If the queue is deleted, the value should be 1. 

If the queue is active, the value should be 0. 

Bots

FieldData TypeConstraintsDescription
bot_idNVARCHAR(50)NOT NULLIdentifier of the bot that participated in this conversation
bot_nameNVARCHAR(50)NOT NULLName of the bot as given in the bot-connector configurations
createdAtDATETIMENOT NULLThe time when the record is inserted into this table
updatedAtDATETIMENOT NULLThe time when the record is updated in this table
is_deletedBITNOT 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

FieldData TypeConstraintsDescription
reason_codeINTPK
NOT NULL

stateVARCHAR(50)NOT NULL

Hybrid Chat MRD states. 

[NOT_READY | UNKNOWN | LOGOUT]

DescriptionVARCHAR(100)NULL
createdAtDATETIMENOT NULLThe time when the record is inserted into this table
updatedAtDATETIMENOT NULLThe time when the record is updated in this table
is_deletedBITNOT 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


FieldData TypeConstraintsDescription
idINTPKAutoincrement
session_idVARCHAR(100)NOT NULLChat session id
session_typeVARCHAR(50)NOT NULLChat session type e.g. BOT | AGENT | HYBRID
conversation_idVARCHAR(100)NOT NULLConversation Id
customer_ndNVARCHAR(100)NOT NULLCustomer identifier
start_timeDATETIMENOT NULLChat session start time
end_timeDATETIMENOT NULLChat session end time
transcript_urlNVARCHAR(250)NOT NULLChat transcript URL, not to be confused by chat history URL.
channel_codeINTNOT NULLChannel 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 
  • If the agent was reserved but the agent didn't accept the request, it's considered Abandoned.
  • A count of total answered would be based on unique conversation_ids 
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.
For example, if a request was enqueued three times and the third agent answered it. SL should be calculated based on:

  • the enqueue-time of the first leg and start-time of the last agent leg who answered it.
  • Similarly AbandonedSL stats should be based on the total abandoned duration of all the queued chat legs of a Conversation.