Data Extraction

Understating eva's database

Access to the database

Different kinds of reports can be created from the information available in eva’s database, such as information on usage, volume, evolution in accesses and customer satisfaction. To extract data from eva, four main tables must be understood:

• Session

• User_interaction

• Satisfaction

• Technicallog

Important:

The datebase access has to be given by your eva`s deploy administrator.

How to start MySQL 5.7:

​https://dev.mysql.com/doc/refman/5.7/en/tutorial.html​

Important:

The database access can be concurrent with the virtual agent use. Check with the system administrator the best time to extract data.

Session

The session table has all the services made by the virtual agent. No matter how many questions the user has asked during a session, only one record will appear for each session. This table can generate reports such as:

Average daily sessions: how many sessions are run per day.

Volume of session: total sessions. It can be broke down in different time frames, such as month or timeframe/total sessions.

Channel/session evolution: a line graph showing the evolution of the number of sessions, making possible to see if there was a increase or decrease.

Column Name	Data Type	Not Null	Description
sessionCode	varchar(50)	No	UUID/GUID code that represents a user session
createDate	datetime	No	Session creation date
expireDate	datetime	Yes	Session expiration date
businessKey	varchar(100)	Yes	The business key is defined by the customer. It is used to identify the customer. Examples of business key: phone and customer ID. This field is optional and may have no value.
sessionType	varchar(10)	No	Shows the session status: created, updated or expired
botId	bigint(20)	No	ID representing a virtual agent related to the table virtual agent
channelId	bigint(20)	No	ID representing a channel, related to the channel table
operatingSystem	varchar(20)	Yes	Identifies the operating system
operatingSystemVersion	varchar(30)	Yes	Identifies the operating system version
browser	varchar(20)	Yes	Shows the browser used
browserVersion	varchar(30)	Yes	Shows the browser version
userRef	varchar(50)	No	User reference, shows where the user accessed from. This value is dependent on the channel being used, for example: WEB uses IP, IVR uses the customer`s contact phone and Facebook uses facebook user ID
locale	varchar(6)	Yes	Identifies the language and country
flow	varchar(1000)	Yes	Information related to the flows table. This table is no longer used and may be removed in future releases.
fest	tinyint(1)	No	Informs if the sessions is an automated test request or if it is from the user.

User Interactions

The user_interaction table stores each one of the users interactions, be the question asked or the answer sent by the virtual agent. The interactions between user and virtual agent are stored and identified by the session code. It is also possible to identify whether it was a user or a virtual agent interaction. This table can generate the following reports:

Volume of questions: total amount of questions asked by users

Number of questions per channel: total number of user questions per channel

Question evolution: a graph showing the number of questions from the users evolution.

Average question per user: shows the average number of questions per user.

Top 10 words: ranking with the 10 most typed words by users

Column Name	Data Type	Not Null	Description
Id	bigint(20)	No	ID that identifies interaction between virtual agent and user
createDate	datetime	No	Date of creation
sessionCode	varchar(50)	No	Code representing a session
userSent	tinyint(4)	No	Identifies if it was a user message
answerld	bigint(20)	Yes	ID that identifies the delivered response
optionId	bigint(20)	Yes	This field is no longer being used and may be removed in future releases
text	text	Yes	Shows the conversation content
statusId	bigint(20)	Yes	This field is no longer used
confidence	decimal(12,10)	Yes	Shows the level of confidence returned by the NLP
userSentId	bigint(20)	Yes	Identifies to wich user ID the response was sent. It is related to the ID column on the user_interaction table. When a response is sent to the user, this field will be filled with the question that generated the response.
evaluation	tinyint(1)	Yes	Shows if the user liked or disliked
uuid	varchar(50)	Yes	The UUID is generated so that eva identifies the evaluation field when the user giver a like or dislike. With this, it is possible for the channel to identify which answer the user evaluated.
intent	varchar(100)	No	Name of the intent identified
entities	varchar(200)	No	Names of the entities identified separated by commas (i.e "entityName1,entityName2")

Satisfaction

The satisfaction table stores the user's assessment of virtual agent attendance. At the end of the session, the user is asked to evaluate the virtual agent service and comment their experience. This table can generate the following reports:

Satisfaction survey result: shows the result of the satisfaction survey, that allows to analyse the level of user satisfaction with a grading system.

Volume of answer: total amount of responses from the satisfaction survey

Solved questions: number of users who had their questions answered

Column Name	Data Type	Not Null	Description
id	bigint(20)	No	Column identifier
sessionCode	varchar(50)	No	Code representing a session
evaluation	tinyint(4)	No	Shows user evaluation
answered	tinyint(1)	No	Shows if users had their questions answered
userComments	varchar(500)	Yes	Shows user comments
createDate	datetime	No	Creation date

Technicallog

The technicallog table stores technical information from user questions, such as response times and service calls, information about calls and execution errors. The response from the eva chat service, when it returns an error, sends a UUID for troubleshooting (found in this table). This table can generate the following report:

Average response time for external services: calculates the average response from external services.

Column Name	Data Type	Not Null	Description
id	bigint(20)	No	ID that identifies a log
sessionCode	varchar(50)	No	Code representing a session
logCode	varchar(50)	No	Log code that is returned by eva when an error is generated. From this field the entry error can be searched and the troubleshoot can be done
type	tinyint(4)	No	Indicates if it is an error, a warning or an information. 0 = error 1 = warning 2 = information
message	text	No	Shows a message
stacktrace	varchar(1000)	Yes	Shows a log if there is an error
moduleType	varchar(50)	No	Indicates which architecture module generated the log
createDate	datetime	No	Record creation date
processingTime	int(11)	Yes	Processing time in milliseconds

Eva also has other important tables related to the main tables:

• answer

• answer_template

• virtual agent

• channel

• channel_classification

• channel_type

Answer

The answer table stores the settings for the response that are created in the virtual agent. The answer can be identified by a name (for example “Welcome”), a description and tags related to the answer subject. It also can be configured to be evaluable and/or transactional.

Column Name	Data Type	Not Null	Description
id	bigint(20)	No	ID representing a response
name	varchar(80)	No	Name identifying the response title
description	text	Yes	Answer description
evaluable	tinyint(1)	Yes	Shows if the message can be evaluated by the user
transactional	tinyint(1)	Yes	Show if the response is transactional
createdAt	datetime	Yes	Record creation date
updatedAt	datetime	Yes	Record creation date
updatedBy	bigint(20)	Yes	ID representing the user responsible for the update
createdBy	bigint(20)	Yes	ID that represents the user who created the answer
removed	tinyint(1)	Yes	Identifies if it has been removed. It is possible to revert it by changing the value of the Boolean. The response will be unavailable only to the channels to which it is connected. 0 = not removed 1 = removed
botId	bigint(20) unsigned	Yes	ID representing a virtual agent, related to the virtual agent table
channelId	bigint(20) unsigned	Yes	ID representing a channel, related to the channel table
webhook	varchar(255)	Yes	API URL that will be called when this response is executed
type	varchar(4)	Yes	This field is no longer being used and may be removed in future releases

Answer Template

The answer_template is the content of an answer that was created in the answer table. One record in the answer table can have many records in the answer_template table, since each channel that a particular answer is delivered generates a recording. The content is the answer that will be sent to the user. For example, the name of the answer is “Welcome” and the content is “Hello, I’m a virtual assistant”.

Column Name	Data Type	Not Null	Description
id	bigint(20)	No	Column identifier
content	text	Yes	JSON representing the selected template. This JSON can vary depending on what was selected by the user in the Cockpit
answerId	bigint(20)	No	ID representing a response, it is related to the answer table
channelTypeId	bigint(20) unsigned	No	ID identifying the type of the channel, it is related to the channel_type table
createdAt	datetime	Yes	Record creation date
updatedAt	datetime	Yes	Last update date
updatedBy	bigint(20)	Yes	ID representing the user who updated
createdBy	bigint(20)	Yes	ID representing the user who created
type	varchar(20)	No	Shows the selected template type
channelId	bigint(20) unsigned	Yes	ID representing a channel, it is related to the channel table
removed	tinyint(4)	Yes	Identifies if it was removed. It is possible to revert by changing the value of the Boolean. The response (from answer table) is not affected when a template is removed. 0 = not removed 1 = removed
technicalText	text	Yes	Technical text in JSON format

Virtual agent

The virtual agent tables stores virtual agent data from the moment it is created.

Column Name	Data Type	Not Null	Description
id	bigint(20) unsigned	No	ID representing a BOT
name	varchar(50)	No	Name identifying the virtual agent
locale	varchar(6)	No	Identifies virtual agent language
description	text	Yes	Virtual agent description
image	mediumblob	Yes	Stores the virtual agent profile picture
imageContentType	varchar(50)	Yes	Shows the image file format (png or jpeg)
chatbaseApiKey	varchar(50)	Yes	bot-Chatbase integration key
createdBy	varchar(50)	No	ID representing the user who created
Updatedby	varchar(50)	Yes	ID representing the user who updated
createdAt	datetime	Yes	Record creation date
updatedAt	datetime	Yes	Date of the last updated
removed	tinyint(1)	Yes	Identifies if it has been removed. It is possible to revert by changing the value of the Boolean. 0 = not removed 1 = removed

Channel

The channel table stores channel data creation. It is possible to identify to which virtual agent the channel is associated, the channel name and type.

Column Name	Data Type	Not Null	Description
id	bigint(20) unsigned	No	ID representing a channel
botId	bigint(20) unsigned	No	ID representing a virtual agent related to the virtual agent table
typeId	bigint(20) unsigned	No	ID identifying the channel type, related to the channel_type table
name	varchar(45)	No	Channel name
description	text	Yes	Channel description
createdBy	varchar(50)	Yes	ID representing the user who created
createdAt	datetime	Yes	Record creation date
updateBy	varchar(50)	Yes	ID representing the user who updated
updatedAt	datetime	Yes	Date of the last update
removed	tinyint(4)	Yes	Identifies if it has been removed. It is possible to revert by changing the value of the Boolean, 0 = not removed 1 = removed
browserVersion	varchar(30)	Yes	Shows the browser version
userRef	varchar(50)	No	User reference, shows where the user accessed from. This value is dependent on the channel being used, for example: WEB uses IP, IVR uses the customer`s contact phone and Facebook uses facebook user ID
locale	varchar(6)	Yes	Identifies the language and country
flow	varchar(1000)	Yes	Information related to the flows table. This table is no longer used and may be removed in future releases.
fest	tinyint(1)	No	Informs if the sessions is an automated test request or if it is from the user.

Channel Classification

The channel_classification table stores the classifications of channels, each one identified by an ID. These groups are:

• Smart Speakers & Social Robots

• Smart Assistants

• Messaging Platform

• Synthetic Reality

• Mobile/Tablet/Desktop

• Cognitive Contact Center

Column Name	Data Type	Not Null	Description
id	bigint(20) unsigned	No	ID representing a channel classification
name	varchar(50)	No	Name of the channel classification
createdBy	varchar(50)	Yes	ID representing the user who created
updatedBy	varchar(50)	Yes	ID representing the user who updated
createdAt	datetime	Yes	Record creation date
updatedAt	datetime	Yes	Last update date

Channel Type

The channel_type table stores the types of channels. Within each classification, there are of channels types, such as:

• Smart Speakers & Social Robots: Amazon Echo e Google Home

• Smart Assistants: Alexa, Cortana e Siri

• Messaging Platform: Facebook, Twitter e Skype

• Synthetic Reality: ARCore e Samsung Gear VR

• Mobile/Tablet/Desktop: Andriod, iOS e Web

• Cognitive Contact Center: IVR e VR

Column Name	Data Type	Not Null	Description
id	bigint(20) unsigned	No	ID representing the type of channel
classificationId	bigint(20) unsigned	No	ID identifying the channel classification, related to the channel_classification table
name	varchar(50)	No	Channel type name
image	varchar(30)	Yes	Stores the image that is being used to represent the channel type
createdBy	varchar(50)	Yes	ID representing the user who created
updatedBy	varchar(50)	Yes	ID representing the user who updated
createdAt	datetime	Yes	Record creation date
updatedAt	datetime	Yes	Last update date