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 |
Last updated