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

PreviousAppendices

Last updated