Reporting Database Schema

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_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)

session_id

PK, NOT NULL

[nvarchar](50) 

[varchar](50) 

Unique id to identify this channel session.

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)

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. DONE, AGENT_LOGOUT, CANCELED, NO_AGENT_AVAILABLE  etc

disposition

NOT NULL

[nvarchar](20)

[varchar](20) 

This is the reason why the conversation was closed. 

BOT-HANDLED - It is when the bot handled the conversation. This could happen in the following scenarios:

• • If there is no AGENT_SUBSCRIBED event in the conversation events and there is no agent task with CANCELLED disposition.

  • If the latest task in the conversation has the disposition i.e. NO_AGENT_AVAILABLE.  For instance,

    • When the task was routed to the agent but the agent did not accept and RONA occurred, the conversation was re-queued but no agents were available to handle the conversation.

    • When the agent was handling the conversation and it got logged out, the conversation was re-queued but no agents were available to handle the conversation.

  • When the customer stays in the conversation but the customer inactivity timeout occurs, the conversation is closed by the bot.

• AGENT-HANDLED - Task with the state CLOSED and with reason code DONE. This is in case when the agent successfully answered the customer queries and closes the conversation from Agent Desk while the customer also closes the chat (for instance, in case of webchat). If the agent closes but the customer stays in the conversation, the conversation is not considered to be closed yet. The customer might or might not engage the bot again so the conversation now activates with the bot.

• ABANDONED - Task with the state CLOSED and with reason code CANCELLED

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_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

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_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_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.

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)

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)

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

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.

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)

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_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 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 mode.

• 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 that is associated with this task.

list_id

NULL

[nvarchar](50) 

[varchar](50) 

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) 

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_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

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.  

A 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 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.

When a conversation is queued and accepted by agent within SL threshold, it is said to be answered within the Service Level.

When a conversation is queued and accepted by agent after SL threshold, it is said to be answered after the Service Level.

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

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

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

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 / (OfferedWithinSL – AbandonedWithinSL) 

• 2 - Abandoned Conversations have Negative Impact : AnsweredWithinSL / (OfferedWithinSL)

• 3 - Abandoned Conversations have Positive Impact : (AnsweredWithinSL + AbandonedWithinSL) / OfferedWithinSL