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:
Important:
The database access can be concurrent with the bot use. Check with the system administrator the best time to extract data.

Session

The session table has all the services made by the bot. 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 bot related to the table bot
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 Bot. The interactions between user and bot are stored and identified by the session code. It is also possible to identify whether it was a user or a bot 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 bot 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.

Satisfaction

The satisfaction table stores the user's assessment of bot attendance. At the end of the session, the user is asked to evaluate the bot 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
    bot
    channel
    channel_classification
    channel_type

Answer

The answer table stores the settings for the response that are created in the bot. 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 bot, related to the bot 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

Bot

The bot tables stores bot 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 bot
locale
varchar(6)
No
Identifies bot language
description
text
Yes
Bot description
image
mediumblob
Yes
Stores the bot 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 bot 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 bot, related to the bot 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
Last modified 5mo ago