Messages Level Data

In Message Level data, in order to enhance the user’s reporting/analytics experience we have implemented modern data transformation for Messages data.

For the silver(detail) layer data of Messages, each message type has its dedicated table in the target database. Schema for each silver layer table is as follows:

Agent Messages

Description: This tables gives the details of agent messages extracted from the messages bronze layer raw data table.

Table Name: agent_messages_silver

Table Schema: Schema for the silver table in the database is as follows:

Field	MYSQL Data Type	Description
id	varchar(255)	Identifier for MongoDB document.
customer_id	varchar(255)	Unique identifier for customer
conversation_id	varchar(255)	Unique identifier for conversation
channel_session_id	varchar(255)	Unique identifier for channel session
channel_session_direction	varchar(255)	Shows the channel session direction (Inbound/Outbound)
activity_id	varchar(255)	Unique identifier for activity
activity_name	varchar(255)	Activity name showing the type of message (e.g. customer_message, agent_message, bot_message)
activity_type	varchar(255)	Shows the type of activity. (in this case this is ‘Message’)
activity_timestamp	DATETIME(3)	Timestamp for activity
event_emitter_type	varchar(255)	Type of event emitter (e.g. Agent)
channel_name	varchar(255)	Channel Name (e.g. CX voice, web, Facebook etc)
channel_customer_identifier	varchar(255)	Unique customer identifier
service_identifier	varchar(255)	Service identifier for CX
channel_mode	varchar(255)	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.
routing_mode	varchar(255)	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.
agent_selection_policy	varchar(255)	Shows the agent selection policy for agent engagement in the conversation (e.g. LONGEST_AVAILABLE)
state_name	varchar(255)	State of the conversation (e.g. Started)
state_reason_code	varchar(255)	Reason code for state change (e.g. Customer)
customer_name	varchar(500)	Records the customer name with whom this conversation is associated.
sender_id	varchar(255)	Unique field for Agent
sender_type	varchar(255)	Type of sender (e.g. agent)
sender_name	varchar(255)	Name of the agent
room_id	varchar(255)	Unique identifier for room
room_mode	varchar(255)	Room mode (e.g. Contact_Center)
tenant_id	varchar(255)	Unique identifier for tenant. (Hard-coded to ‘expertflow’)
message_type	varchar(255)	Shows the type of message. (Plain, Wrapup, Comment etc)
message_text	varchar(255)	(Shows the messages text sent by the agent)
comment_post_id	varchar(255)	Identifier the comment id (Populated only in case of message type ‘Comment’)
comment_text	varchar(255)	Shows the comment text (Populated only in case of message type ‘Comment’)
comment_item_type	varchar(255)	Identifies the action performed on comment for conversation. (e.g. Text, Like, Private_Reply, Delete)
wrapup_note	varchar(255)	Shows the wrapup notes given by the agent at the end of the conversation (if any)
wrapup_category	varchar(255)	Identifies the category of the wrapup in the conversation
wrapup_value		Gives the value of the wrapup in the conversation
timestamp	DATETIME(3)	Timestamp of the activity

Customer Messages

Description: This tables gives the details of customer messages extracted from the messages bronze layer raw data table.

Table Name: customer_messages_silver

Table Schema: Schema for the silver table in the database is as follows:

Field	MYSQL Data Type	Description
id	varchar(255)	Identifier for MongoDB document.
customer_id	varchar(255)	Unique identifier for customer
conversation_id	varchar(255)	Unique identifier for conversation
channel_session_id	varchar(255)	Unique identifier for channel session
channel_session_direction	varchar(255)	Shows the channel session direction (Inbound/Outbound)
activity_id	varchar(255)	Unique identifier for activity
activity_name	varchar(255)	Activity name showing the type of message (e.g. customer_message, agent_message, bot_message)
activity_type	varchar(255)	Shows the type of activity. (in this case this is ‘Message’)
activity_timestamp	DATETIME(3)	Timestamp for activity
event_emitter_type	varchar(255)	Type of event emitter (e.g. Customer)
channel_name	varchar(255)	Channel Name (e.g. CX voice, web, Facebook etc)
channel_customer_identifier	varchar(255)	Unique customer identifier
service_identifier	varchar(255)	Service identifier for CX
channel_mode	varchar(255)	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.
routing_mode	varchar(255)	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.
agent_selection_policy	varchar(255)	Shows the agent selection policy for agent engagement in the conversation (e.g. LONGEST_AVAILABLE)
state_name	varchar(255)	State of the conversation (e.g. Started)
state_reason_code	varchar(255)	Reason code for state change (e.g. Customer)
customer_name	varchar(500)	Records the customer name with whom this conversation is associated.
sender_id	varchar(255)	Unique field for Sender
sender_type	varchar(255)	Type of sender (e.g. connector)
sender_name	varchar(255)	Name of the sender
room_id	varchar(255)	Unique identifier for room
room_mode	varchar(255)	Room mode (e.g. Contact_Center)
channel_tenant_id	varchar(255)	Unique identifier for channel tenant.
tenant_id	varchar(255)	Unique identifier for tenant. (Hard-coded to ‘expertflow’)
message_type	varchar(255)	Shows the type of message. (Plain, Comment etc)
message_text	varchar(255)	Shows the messages text sent by the customer
comment_post_id	varchar(255)	Identifier the comment id (Populated only in case of message type ‘Comment’)
comment_text	varchar(255)	Shows the comment text (Populated only in case of message type ‘Comment’)
comment_item_type	varchar(255)	Identifies the action performed on comment for conversation. (e.g. Text, Like, Private_Reply, Delete)
timestamp	DATETIME(3)	Timestamp of the activity

Bot Messages

Description: This tables gives the details of bot messages extracted from the messages bronze layer raw data table.

Table Name: bot_messages_silver

Table Schema: Schema for the silver table in the database is as follows:

Field	MYSQL Data Type	Description
id	varchar(255)	Identifier for MongoDB document.
customer_id	varchar(255)	Unique identifier for customer
conversation_id	varchar(255)	Unique identifier for conversation
channel_session_id	varchar(255)	Unique identifier for channel session
channel_session_direction	varchar(255)	Shows the channel session direction (Inbound/Outbound)
activity_id	varchar(255)	Unique identifier for activity
activity_name	varchar(255)	Activity name showing the type of message (e.g. customer_message, agent_message, bot_message)
activity_type	varchar(255)	Shows the type of activity. (in this case this is ‘Message’)
activity_timestamp	DATETIME(3)	Timestamp for activity
event_emitter_type	varchar(255)	Type of event emitter (e.g. Bot)
channel_name	varchar(255)	Channel Name (e.g. CX voice, web, Facebook etc)
channel_customer_identifier	varchar(255)	Unique customer identifier
service_identifier	varchar(255)	Service identifier for CX
channel_mode	varchar(255)	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.
routing_mode	varchar(255)	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.
agent_selection_policy	varchar(255)	Shows the agent selection policy for agent engagement in the conversation (e.g. LONGEST_AVAILABLE)
state_name	varchar(255)	State of the conversation (e.g. Started)
state_reason_code	varchar(255)	Reason code for state change (e.g. Customer)
customer_name	varchar(500)	Records the customer name with whom this conversation is associated.
sender_id	varchar(255)	Unique field for Sender
sender_type	varchar(255)	Type of sender (e.g. Bot)
sender_name	varchar(255)	Name of the bot sender (e.g. Rasa, Facebook Bot)
room_id	varchar(255)	Unique identifier for room
room_mode	varchar(255)	Room mode (e.g. Contact_Center)
channel_tenant_id	varchar(255)	Unique identifier for channel tenant.
tenant_id	varchar(255)	Unique identifier for tenant. (Hard-coded to ‘expertflow’)
body_type	varchar(255)	Body type of the bot message. (e.g. Button, Plain)
interactive_type	varchar(255)	Interactive type menu given by the bot (e.g. list)
interactive_button	varchar(255)	Interactive button given by the bot (e.g. Main Menu)
header_type	varchar(255)	Type of header given by the bot.
header_text	varchar(255)	Text of the header given by the bot.
body_text	varchar(255)	Text of the bot message
timestamp	DateTime(3)	Timestamp of the activity

Action Messages

Description: This tables gives the details of action messages extracted from the messages bronze layer raw data table.

Table Name: action_messages_silver

Table Schema: Schema for the silver table in the database is as follows:

Field	MYSQL Data Type	Description
id	varchar(255)	Identifier for MongoDB document.
customer_id	varchar(255)	Unique identifier for customer
conversation_id	varchar(255)	Unique identifier for conversation
channel_session_id	varchar(255)	Unique identifier for channel session
channel_session_direction	varchar(255)	Shows the channel session direction (Inbound/Outbound)
activity_id	varchar(255)	Unique identifier for activity
activity_name	varchar(255)	Activity name showing the type of message (e.g. customer_message, agent_message, bot_message, action_message)
activity_type	varchar(255)	Shows the type of activity. (in this case this is ‘Message’)
activity_timestamp	DATETIME(3)	Timestamp for activity
event_emitter_type	varchar(255)	Type of event emitter (e.g. Bot)
channel_name	varchar(255)	Channel Name (e.g. CX voice, web, Facebook etc)
channel_customer_identifier	varchar(255)	Unique customer identifier
service_identifier	varchar(255)	Service identifier for CX
channel_mode	varchar(255)	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.
routing_mode	varchar(255)	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.
agent_selection_policy	varchar(255)	Shows the agent selection policy for agent engagement in the conversation (e.g. LONGEST_AVAILABLE)
state_name	varchar(255)	State of the conversation (e.g. Started)
state_reason_code	varchar(255)	Reason code for state change (e.g. Customer)
customer_name	varchar(500)	Records the customer name with whom this conversation is associated.
sender_id	varchar(255)	Unique field for Sender
sender_type	varchar(255)	Type of sender (e.g. Bot)
sender_name	varchar(255)	Name of the sender (Rasa, Facebook Bot etc)
room_id	varchar(255)	Unique identifier for room
room_mode	varchar(255)	Room mode (e.g. Contact_Center)
channel_tenant_id	varchar(255)	Unique identifier for channel tenant.
tenant_id	varchar(255)	Unique identifier for tenant. (Hard-coded to ‘expertflow’)
action_message	varchar(255)	Action Message sent in the system. (e.g. ‘Find_Agent’)
message_priority	Boolean	Priority of the action message (1 or 0)
is_reinitiated	Boolean	Shows if the action message is reinitiated or not (true or false)
timestamp	DATETIME(3)	Timestamp of the activity