Skip to main content
Skip table of contents

Reporting Database Schema

Introduction

This document covers the SQL-based reporting database schema.

The naming convention for table names and field names is an all-small snake case. Example: multiple_word_variable. 

  • Records are updated in the following tables in the reporting database, as per the scheduled interval of all ETL jobs.

  • If any changes are done to the configurations, the previous report records are not updated and the new changes are applied to the new reporting data only.

  • This schema guide provides details about the reporting database tables which are used only for historical reporting. For real-time dashboards, the data is extracted through real-time REST APIs and available on real-time dashboards for supervisors.

Channels and Channel Sessions

channel_type

A channel type describes the type/class of the channels, for example, WHATSAPP, SMS, WEB. Multiple channels of a channel type can be created. 

See more on Channel related terms

See Administrator Guide -> Add Channel Type for more details on Channel types.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

channel_type_id

PK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Unique id to identify this channel type

channel_type_name

NOT NULL

[nvarchar](50)

[varchar](50) 

Records the name for this channel type, any alphanumeric value

channel

A channel instance is created when a new communication channel is registered by the admin. It includes all the configuration details of a channel, for example, the channel name, service identifier, channel mode, etc. A channel is of a particular channel type. For instance, we can create two channels WhatsApp-external and WhatsApp-internal, both of a channel type WHATSAPP. 

See more on Channel related terms

See Administrator Guide -> Step 11: Set up a new Channel for more details on Channel types.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

channel_id

PK, NOT NULL

[nvarchar](50)

[varchar](50) 

Unique id to identify this channel

channel_name

NOT NULL

[nvarchar](50)

[varchar](50) 

Records the name for this channel

channel_type_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the channel type for this channel

channel_mode 

NOT NULL

[nvarchar](20)

[varchar](20) 

Indicates the channel mode for this channel

  • HYBRID - Both the bot and the human agents are engaged on communication with the customer. 

  • BOT - Only bot is engaged in communication with the customer. 

  • AGENT - Only human agents are engaged in communication with the customer. 

service_identifier

NOT NULL

[nvarchar](MAX)

[varchar](250) 

Unique identifier like a phone number or URL associated to this channel. 

Example: For a WhatsApp channel, the business WhatsApp account's phone number which is registered for receiving customer's messages will come here.

created_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is created.

updated_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is updated.

is_deleted

NOT NULL

[bit]

[TINYINT](1)

Indicates that this channel has been deleted from the system. (Used for soft deletion)

channel_session

A customer can converse on multiple channels at a time in one conversation. A new channel session is created every time a customer initiates a session on a channel. For instance, a customer first opens up a chat from Webchat and later on, sends a message from its WhatsApp.

See more on Channel related terms

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

session_id

PK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Unique id to identify this channel session.

conversation_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the conversation, this channel session is associated to

channel_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the channel, this channel session is associated to

start_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the channel session is started.

end_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the channel session is ended.

total_session_duration

NOT NULL

[int]

[INT]

Records the value of total duration of this channel session i.e. end_time minus start_time

disposition

NOT NULL

[nvarchar](20)

[varchar](20) 

Indicates the reason why the channel session was ended.

AGENT - the human agent ended the channel session. This is valid in case of Pull-based chats from the list by clicking the "Close" button.
CUSTOMER - the customer ended the session. This happens in case when the web chat widget is closed by the customer. Only true for web chat channels.
INACTIVITY - the customer was inactive for a configured timeout (timeout is managed by the Bot).
NETWORK - the conversation ended due to some network error usually in case when the network is disconnected. 

FORCE_CLOSED - the system (ExpertFlow CX) ends the channel session.

bot_activity_count

NOT NULL

[int]

[INT]

Records the number of messages sent by the bot on this channel session

agent_activity_count

NOT NULL

[int]

[INT]

Records the number of messages sent by the agent(s) on this channel session

customer_activity_count

NOT NULL

[int]

[INT]

Records the number of messages sent by the customer on this channel session

avg_bot_confidence_level

NOT NULL

[decimal]

[DECIMAL(5,5)]

Reserved for future.

For each bot response, there is a percentage value that tells how confidently the bot was able to answer a query. This field gives us the average value for all bot-confidence values.

record_creation_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the record is created in source DB i.e. MongoDB

device

NULL

[nvarchar](40)

[varchar](40)


browser_type

NULL

[nvarchar](40)

[varchar](40)


country

NULL

[nvarchar](40)

[varchar](40)


language

NULL

[nvarchar](40)

[varchar](40)


Conversations

conversation

A conversation entity records the customer's conversation details. A conversation consists of all channel sessions where a customer is conversing, the conversation participants (bots, agents, customers) and optionally also some conversation data.

See more about Conversations on Conversation Objects Unique id to identify this conversation

This table only records the essential conversation fields. There are separate tables for conversation data and conversation participants.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

conversation_id

PK, NOT NULL

[nvarchar](50) 

[varchar](50) 


room_id (reserved for future)

FK, NULL

[nvarchar](50)

[varchar](50) 

Identifies the room this conversation is a part of. Currently, null since a conversation is not a part of any room.

customer_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the customer with whom this conversation is associated.

customer_name

NOT NULL

[nvarchar](50)

[varchar](50) 

Records the customer name with whom this conversation is associated.

start_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this conversation was started.

end_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this conversation was ended.

conversation_duration

NOT NULL

[int]

[INT]

Records the value of total duration of this conversation i.e. end_time minus start_time

bot_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifier of the bot who was a participant of this conversation.

task_state

NULL


[varchar](50)

Records the state of conversation i.e. ACTIVE, CLOSED

reason_code

NULL


[varchar](50)

Records the reason code of conversation i.e. DONEAGENT_LOGOUT, CANCELED, NO_AGENT_AVAILABLE, RONA, NULL

disposition

NOT NULL

[nvarchar](20)

[varchar](20) 

This is the reason why the conversation was closed. The details can be referenced from the document Task Reason Codes  

There can be multiple tasks created for a conversation. We will sort these tasks w.r.t the date and time and see the latest ones. The disposition will be considered on last task.

direction

NOT NULL

[nvarchar](20)

[varchar](20) 

Records the direction of conversation i.e. INBOUND/OUTBOUND.

There can be multiple channel sessions (INBOUND/OUTBOUND) in a conversation. We'll consider the conversation's direction based on the first channel session's direction in a conversation. 

conversation_data

A conversation can have key-value pairs to record any arbitrary additional data required. This is known as the conversation data. A single key-value pair for a conversation is inserted as one record in this table.

See Conversation Data for more details.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

conversation_id

FK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Identifies the conversation for which this conversation data record is inserted

key

NOT NULL

[nvarchar](50)

[varchar](50) 

Any alphanumeric value

value

NOT NULL

[nvarchar](100)

[varchar](100) 

Any alphanumeric value

conversation_participant

Conversation participants are the participants that take part in a conversation. For example , customers, bots and agents. Each record in the table represents a Conversation Participant.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

id

PK, NOT NULL 

[nvarchar](50) 

[varchar](50) 

A unique identification of a participation by agent/customer/bot etc.

participant_id

NOT NULL

[nvarchar](50)

[varchar](50) 

Unique id (Customer ID/ Bot ID / Agent ID) to identify a participant.

conversation_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the conversation this participant is part of.

participant_role (AGENT | CUSTOMER | EXTERNAL)

NOT NULL

[nvarchar](30)

[varchar](30) 

Indicates the role of a participant in the conversation

  • AGENT - If the participant is an agent

  • CUSTOMER - If the participant is one of the channel_sessions that a customer is conversing on.

  • EXTERNAL - Other participants like a bot etc.

record_creation_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the record is created in source DB i.e. MongoDB

wrapup_detail

A conversation's wrap-up details are recorded in this table.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

wrapup_time

NOT NULL

[DateTime]

[DateTime](3)

Records the time when the wrap-up was applied.

conversation_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the conversation to which the wrap-up was applied 

wrapup_label

NULL

[nvarchar](50)

[varchar](50) 

The wrap-up label for the applied wrap-up 

category_name

NULL

[nvarchar](50)

[varchar](50) 

Records the wrap-up category

agent_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the agent who did the wrap up on the associated conversation

notes

NULL

[nvarchar](350)

[varchar](350) 

Optional notes entered by agents while applying wrap up to a conversation

record_creation_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the record is created in source DB i.e. MongoDB

Room (reserved for future)

room

A room is a group of customers conversing among each other and with the system entities like a bot or human agents. One customer participating in a room is inserted as one record in this table

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

room_id

PK, NOT NULL

[nvarchar](50)

[varchar](50) 

Unique id to identify the room

customer_id

FK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Identifies a customer participating in this room

creation_time

NOT NULL

[DateTime] 

[DateTime](3)

Records the date and time when this record is created in this table.

Bots

bot

This table records the details of a bot configured in the system by the admin. One bot's details are added as a single record in this table

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

bot_id

PK, NOT NULL

[nvarchar](50)

[varchar](50) 

Unique id of identifying the bot

bot_name

NOT NULL

[nvarchar](50)

[varchar](50) 

Records the name of the bot.

created_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is created.

updated_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is updated.

is_deleted

NOT NULL

[bit]

[TINYINT](1)

Indicates the bot has been deleted from the system.(Used for soft deletion)

Agents and States

agent

This table records the details of an agent. One agent's details are inserted as a single record in the table.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

agent_id

PK, NOT NULL

[nvarchar](50)

[varchar](50) 

Unique id to identify this agent

agent_name

NOT NULL

[nvarchar](100)

[varchar](100) 

Records the agent name 

username

NOT NULL

[nvarchar](100)

[varchar](100) 

The agent's unique username is logged.

agent_extension

NULL

[nvarchar](10)

[varchar](10) 

Records the agent's extension

team_id (reserved for future)

FK, NULL

[nvarchar](50)

[varchar](50) 

Identifies the team this agent is part of. This field is currently null since teams are not yet set up

created_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is created.

updated_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is updated.

is_deleted

NOT NULL

[bit]

[TINYINT](1)

Indicates the agent has been deleted from the system. (Used for soft deletion)

agent_mrd_state

This table records the details of an agent's MRD state at a particular instance. Each time an agent changes its state on the MRD, a new record is inserted into this table.

Learn more about Agent MRD state transitions

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

login_date_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when MRD state changed to login.

agent_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the Agent whose Agent MRD state is recorded here

mrd_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the MRD this MRD state is associated to

agent_mrd_state

NOT NULL

[nvarchar](20)

[varchar](20) 

Records the MRD state. For example 'READY', 'ACTIVE' etc.

duration

NOT NULL

[int]

[INT]

The length of time MRD spent in the specified state as recorded in the "agent_mrd_state" column.

This duration is recorded in seconds. 

Note:  When the duration value is "-1" , it indicates that the MRD is presently in its current state and has not transitioned to the succeeding state yet.

state_change_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the MRD state was changed

agent_state

This table records the details of an agent's state at a particular instance. For example, if this agent's state is 'READY' then 'NOT_READY' and then 'READY' again, three records will be inserted into the table.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

login_date_time 

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when MRD state changed to login.

agent_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the agent whose Agent state is recorded here.

agent_state

NOT NULL

[nvarchar](20)

[varchar](20) 

Records the Agent state value. For example, 'READY', 'NOT_READY', 'LOGOUT' etc.

reason_code

NULL

[nvarchar](100)

[varchar](100) 

Records the reason why agent switched to NOT_READY or LOGOUT state.

For example , SHORT_BREAK , TIME_OFF etc

duration

NOT NULL

[int]

[INT]

The length of time agent spent in the specified state as recorded in the "agent_state" column.

This duration is recorded in seconds.

Note:  When the duration value is "-1", it indicates that the Agent is presently in its current state and has not transitioned to the succeeding state yet. 

state_change_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the Agent state was changed.

agent_team (reserved for future)

A team is a list of agents that are grouped together to be managed by a contact center supervisor. 

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

team_id

PK, NOT NULL

[nvarchar](50)

[varchar](50) 

Unique id to identify this team. ETL job will put dummy values.

team_name

NOT NULL

[nvarchar](50)

[varchar](50) 

Records the team name, any alphanumeric value

description

NULL

[nvarchar](100)

[varchar](100) 

Records a short description of this team's value proposition

created_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is created.

updated_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is updated.

is_deleted

NOT NULL

[bit]

[TINYINT](1)

Indicates the team has been deleted from the system. (Used for soft deletion)

Routing

queue

This table records the properties of a routing queue. Whenever a human agent is required for a conversation, a request to assign an agent is created by the system and offered to a precision queue.

To learn more about queues, see precision routing

Also, see Unified Admin Guide to learn more about each queue properties listed below

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

queue_id

PK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Unique Id to identify this queue

queue_name

NOT NULL

[nvarchar](50) 

[varchar](50) 

Records the queue name, any alphanumeric string

sl_type

NOT NULL

[int]

[INT]

Service level type can have the following values

1 = Ignore Abandoned Chats

2 = Abandoned Chats have a Negative Impact

3 = Abandoned Chats have a Positive Impact

sl_threshold

NOT NULL

[int]

[INT]

Indicates the number of seconds in which a request must be routed to an agent

created_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is created.

updated_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is updated.

is_deleted

NOT NULL

[bit]

[TINYINT](1)

Indicates that the queue has been deleted from the system.(Used for soft deletion) 

agent_task

Whenever a conversation needs a human agent, Expertflow CX creates an agent task to be routed to an agent.

This is also true when a conversation is re-queued to the queue for whatever reasons, say, RONA occurred, no agents were available or agent got logged out. 

This means that a conversation can have multiple agent tasks created for each agent joining the conversation.

This table keeps the details of an agent task whenever a new task is created for a conversation.  

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

agent_task_id

PK, NOT NULL

[nvarchar](50)

[varchar](50) 

Unique id to identify this task

conversation_id

FK, NOT NULL

[nvarchar](50)

[varchar](50) 

Identifies the conversation this task is created for

agent_id

FK, NULL

[nvarchar](50)

[varchar](50) 

Identifies the agent which was assigned to this task. If the task was ended while in the queued state then this field is null

task_queue_time

NULL

[DateTime]

[DateTime](3)

Records the date and time when the task is offered to a routing queue like in case of PUSH mode routing.

This field is NULL in following cases :

  • If the task routing is skipped and the task is directly assigned to the agent, like in the case of pull mode routing.

  • If agent has initiated the chat i.e. OUTBOUND chat.

task_reserved_time

NULL

[DateTime]

[DateTime](3)

Records the date and time when an agent is reserved for this task.

This field is NULL in following cases :

  • If the task is directly assigned to the agent, like in the case of pull mode routing.

  • If agent has initiated the chat i.e. OUTBOUND chat.

  • If the task was ended while in the queued state i.e. for PUSH mode routing.

task_answered_time

NULL

[DateTime]

[DateTime](3)

Records the date and time when the reserved agent accepts the task. This field is null if the agent never answered the task.

For example in following cases : 

  • if the task was closed while in the queued state.

  • If the task was presented to the agent but the agent did not accept it.

queue_id

FK, NULL

[nvarchar](50)

[varchar](50) 

Identifies the queue in which this task is enqueued for routing.

This field is NULL in following cases :

  • In case where queue routing is skipped and the task is directly assigned to an agent like in the case of pull mode routing.

  • If agent has initiated the chat i.e. OUTBOUND chat.

task_queue_duration

NOT NULL

[int]

[INT]

For PUSH mode routing , it will record the task_reserved_time minus task_queue_time value.

It will be 0 in following cases :

  • For PULL mode routing.

  • If agent has initiated the chat i.e. OUTBOUND chat.

  • If the task was ended while in the queued state i.e. for PUSH mode routing.

task_alert_duration

NOT NULL

[int]

[INT]

For PUSH mode routing , it will record the task_answered_time minus task_reserved_time value.

It will be 0 in following cases :

  • For PULL mode routing.

  • If agent has initiated the chat i.e. OUTBOUND chat.

  • If the task was ended while in the queued state i.e. for PUSH mode routing.

task_end_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the task was ended/closed.

task_disposition

NOT NULL

[nvarchar](30)

[varchar](30) 

This is basically the reason why the task was ended.
The reason could be one of the following.

  • DONE

  • RONA

  • RESPONSE_TIMEOUT

  • NO_AGENT_AVAILABLE

  • REROUTE

  • CANCELLED

  • AGENT_LOGOUT

  • TRANSFERRED 

  • FORCE_CLOSED.

record_creation_time

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when the record is created in source DB i.e. MongoDB

wrapup_duration (future use)

NOT NULL

[int]

[INT]


mrd_id

FK, NULL

[nvarchar](50) 

[varchar](50) 

Unique Id to identify the MRD through which this task is created.

task_direction

NOT NULL

[nvarchar](50) 

[varchar](50) 

This records the direction how the task was created. The directions can be one of the following.

  • INBOUND

  • OUTBOUND

  • CONSULT

  • CONSULT_TRANSFER: A task direction changed from Consult to transfer, when the call is transferred to a consulted agent

  • CONSULT_CONFERENCE: A task direction changed from Consult to conference, when the call is conferenced to a consulted agent

  • DIRECT_TRANSFER: Directly transferred to an agent (the agent is not consulted first in this case)

  • DIRECT_CONFERENCE: Directly transferred to an agent (the agent is not consulted first in this case)

task_mode

NOT NULL

[nvarchar](50) 

[varchar](50) 

This records the mode how the task was created. The mode can be one of the following.

  • AGENT: Directly assigned to an agent.

  • QUEUE: Task pushed to queue to route to any agent.

routing_mode

NOT NULL

[nvarchar](50) 

[varchar](50) 

This records one of the following outing modes.

  • PUSH: The incoming request was pushed to a queue to be routed to agents.

  • PULL: Broadcasted to a Pull-based List to be taken up by any agents subscribed to the List.  

channel_session_id

NOT NULL

[nvarchar](50) 

[varchar](50) 

A unique identifier for this channel session is associated with this task.

list_id

NULL

[nvarchar](50) 

[varchar](50) 


media_routing_domain

This table records the properties of a media routing domain (MRD.) .

To learn more about queues, see Channel Categories (Media Routing Domains)

Also, see the Unified Admin Guide to learn more about each MRD properties listed below

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

mrd_id

PK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Unique Id to identify this MRD.

mrd_name

NOT NULL

[nvarchar](50) 

[varchar](50) 

Records the MRD name, any alphanumeric string

description

NULL

[nvarchar](250) 

[varchar](250) 

Short description of the media routing domain

max_task_request

NOT NULL

[int]

[INT]

This is the max tasks that the MRD can receive.

is_interruptible

NOT NULL

[bit]

[TINYINT](1)

Tells whether a conversion of a specific media domain can be interrupted at the agent's end by a higher priority conversation.

For instance, a chat conversation on WhatsApp can be interrupted by a voice call. On the contrary an ongoing voice call cannot be interrupted by a chat request.

is_managed_by_re

NOT NULL

[bit]

[TINYINT](1)

When the value is true, it indicates that the Routing Engine is responsible for managing this MRD.

Conversely, if the value is false, it means that other sources, such as Cisco, are in charge of managing this specific MRD.

created_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is created.

updated_at

NOT NULL

[DateTime]

[DateTime](3)

Records the date and time when this record is updated.

is_deleted

NOT NULL

[bit]

[TINYINT](1)

Indicates that the queue has been deleted from the system.(Used for soft deletion) 

IVR

ivr

This table records the properties of an IVR.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

conversation_id

FK, NOT NULL

[nvarchar](50) 

[varchar](50)


help_line

NULL

[nvarchar](50) 

[varchar](50)


start_date_time

NULL

DATETIME

DATETIME


caller_number

NULL

[nvarchar](20)

[varchar](20)


main_menu_selection

NULL

[nvarchar](20)

[varchar](20)


sub_menu_option_selected

NULL

[nvarchar](20)

[varchar](20)


journey

NULL

[nvarchar](250)

[varchar](250)


selection

NULL

[nvarchar](250)

[varchar](250)


duration

NOT NULL

INT

INT


language

NULL

[nvarchar](20)

[varchar](20)


call_id

NOT NULL

[nvarchar](50)

[varchar](50)


end_date_time

NULL

DATETIME

DATETIME


start_direction

NULL

[nvarchar](50)

[varchar](50)


call_disposition

NULL

[nvarchar](50)

[varchar](50)


record_creation_time

NOT NULL

DATETIME

DATETIME


LIST

list

This table records the properties of a routing queue. Whenever a human agent is required for a conversation, a request to assign an agent is created by the system and offered to a precision queue.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

list_id

PK, NOT NULL

[nvarchar](50) 

[varchar](50) 


list_name

NOT NULL

[nvarchar](50) 

[varchar](50) 


created_at

NOT NULL

DATETIME(3)

DATETIME(3)


updated_at

NOT NULL

DATETIME(3)

DATETIME(3)


is_deleted

NOT NULL

BOOLEAN

BOOLEAN


conversation_hold_resume

This table records the properties of conversations that were held and later resumed, capturing key details such as the time of hold and resume events, and the agent or channel session interaction involved.

Field

Constraints

Data type (MsSQL)

Data type (MySQL)

Description

conversation_id

FK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Unique identifier for the conversation, linking it to the main conversation table.

conversation_state

NOT NULL

[nvarchar](50) 

[varchar](50) 

Indicates the current state of the conversation (e.g., HOLD, RESUMED).

agent_id

NULL

[nvarchar](50) 

[varchar](50) 

Identifier for the agent involved in the conversation, if applicable.

event_timestamp

NOT NULL

DATETIME(3)

DATETIME(3)

Timestamp of the hold or resume event in the conversation.

channel_session_id

NOT NULL

[nvarchar](50) 

[varchar](50) 

Unique identifier for the communication session related to the conversation.

record_creation_time

NOT NULL

DATETIME(3)

DATETIME(3)

Timestamp indicating when the record was created in the source i.e mongoDB

Key Reporting Concepts

Handled Conversation A conversation is set to be "Handled" when the disposition of the last task in the conversation is set to CLOSED, with reason code DONE.  
Abandoned ConversationA conversation is considered as abandoned when the customer leaves while waiting in the queue or on ringing (agent alerting). In this case, the agent task in the conversation will be closed with the state CLOSED, with reason code CANCELLED.
Service Level Agreement % (SLA%) 

Service level measures the percentage of incoming conversations that an agent answers within the specified Service Level (SL) threshold defined in the queue configurations. The service level threshold timer starts as soon as the conversation is queued to a precision queue. The service level threshold is the number of seconds you set as a goal for connecting a conversation with an agent. 

For example, your goal might be to answer 80% of conversations within two minutes. In this case, you would set the service level threshold to 120 seconds. Reports show you the percentage of conversations that are answered within that time threshold, enabling you to see whether you are meeting your goal.

AnsweredWithinSLWhen a conversation is queued and accepted by agent within SL threshold, it is said to be answered within the Service Level.
AnsweredAfterSLWhen a conversation is queued and accepted by agent after SL threshold, it is said to be answered after the Service Level.
AbandonedWithinSLWhen a conversation is queued and gets abandoned within SL threshold,, it is said to be abandoned within the Service Level.
AbandonedAfterSL

When a conversation is queued and gets abandoned after SL threshold,, it is said to be abandoned after the Service Level.

OfferedWithinSL

The sum of AnsweredWithinSL and AbandonedWithinSL is said to be offered within SL.

Service Level calculations

The calculations for service level are based on the Service Level Type defined in the queue configuration. There are three possible Service Level Types:

  • 1 - Ignore Abandoned Conversations : AnsweredWithinSL / (TotalOffered – AbandonedWithinSL) 
  • 2 - Abandoned Conversations have Negative Impact : AnsweredWithinSL / (TotalOffered)
  • 3 - Abandoned Conversations have Positive Impact : (AnsweredWithinSL + AbandonedWithinSL) / TotalOffered


Note : 
Consult  SLA Calculations for further details.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.