V3 Motion DB Tables

In this section you can find the list and the structure of the tables you can query to obtain custom reports. 

This page is periodically updated by our team. Please note most of the table fields are self-explanatory, if you need further explanation about specific fields or tables please contact us.

Staff

report_agent 

 The table contains the information about the queue calls answered or unanswered by the agents

 

column_name

column_type

description

column_name

column_type

description

accountcode

varchar(255)

accountcode

acwtime

int(11)

the maximum ACW time set on the GUI

agentacw

tinyint(1)

0: no acw | 1: acw

agentcalledAt

datetime

when agent phone start ringing

agentcomplete

tinyint(1)

0: not answered | 1: answered

agentcompleteAt

datetime

when agent complete the call

agentconnectAt

datetime

when agent picks up the call

agentdump

tinyint(1)

agentdump (Asterisk definition)

agentdumpAt

datetime

agentdumpAt

agentringnoanswer

tinyint(1)

0:default | 1: missing call

agentringnoanswerAt

datetime

missing call date time

answeredelsewheredestinationuniqueid

varchar(255)

answeredelsewheredestinationuniqueid

answeredelsewheremembername

varchar(255)

for ringall strategy: who answered the call

calleridname

varchar(255)

caller name

calleridnum

varchar(255)

caller number

channel

varchar(255)

channel

channelstate

int(11)

channelstate (Asterisk definition)

channelstatedesc

varchar(255)

channelstatedesc

connectedlinename

varchar(255)

agent name (available if call is answered)

connectedlinenum

varchar(255)

agent number (available if call is answered)

context

varchar(255)

context

createdAt

datetime

createdAt

destaccountcode

varchar(255)

destaccountcode

destcalleridname

varchar(255)

destcalleridname

destcalleridnum

varchar(255)

destcalleridnum

destchannel

varchar(255)

destchannel

destchannelstate

int(11)

destchannelstate (Asterisk definition)

destchannelstatedesc

varchar(255)

destchannelstatedesc

destconnectedlinename

varchar(255)

destconnectedlinename

destconnectedlinenum

varchar(255)

destconnectedlinenum

destcontext

varchar(255)

destcontext

destexten

varchar(255)

destexten

destlanguage

varchar(255)

destlanguage

destpriority

varchar(255)

destpriority

destuniqueid

varchar(255)

destuniqueid

exten

varchar(255)

called DID

holdtime

int(11)

queue waiting time (sec)

id

int(11)

autoincrement id

interface

varchar(255)

agent interface

language

varchar(255)

language

lastevent

varchar(255)

last Asterisk event (connect|called|complete|abandon|reject)

membername

varchar(255)

agent username

priority

varchar(255)

priority

queue

varchar(255)

queue name

reason

varchar(255)

call completed (agent|caller|transfer)|abandoned|ringnoanswer (timeout|reject|abandoned)

talktime

int(11)

talking time (sec)

type

varchar(255)

Call Type (inbound, outbound) 

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

 

report_member

The users activity log

 

column_name

column_type

description

column_name

column_type

description

channel

varchar(255)

the Motion channel (voice|chat|email|sms|fax|openchannel)

createdAt

datetime

createdAt

data1

varchar(255)

additional information (call type, pause type, etc)

data2

varchar(255)

additional information (caller id, etc)

data3

varchar(255)

additional information (called DID, etc)

data4

varchar(255)

additional information (trunk,etc)

data5

varchar(255)

additional information (queue name, etc)

duration

int(11)

duration of the event between enterAt and exitAt

enterAt

datetime

when the event started

exitAt

datetime

when the event ended

id

int(11)

autoincrement id

interface

varchar(255)

user SIP interface

internal

int(11)

internal SIP account

membername

varchar(255)

agent username

role

varchar(255)

role of the user (admin|user|agent)

type

varchar(255)

type of the event (interaction|talking|login|pause)

uniqueid

varchar(255)

call or interaction unique id

updatedAt

datetime

updatedAt

phoneBarDestinationPort

port number

This setting only takes effect with Phonebar version >= 5.7.0

 

Contact Manager

cm_contacts

The contact information table

 

column_name

column_type

description

column_name

column_type

description

cf_1

tinyint(1)

custom field example (boolean)

cf_2

text

custom field example (text)

city

varchar(255)

city

CompanyId

int(11)

CompanyId reference from cm_companies table

country

varchar(255)

country

createdAt

datetime

createdAt

dateOfBirth

varchar(255)

dateOfBirth

deletedAt

datetime

deletedAt (the contact is not deleted but marked with the deleted date)

description

text

description

email

varchar(255)

email

facebook

varchar(255)

facebook account id 

fax

varchar(255)

fax

firstName

varchar(255)

firstName

id

int(11)

autoincrement id

lastName

varchar(255)

lastName

line

varchar(255)

line account id

ListId

int(11)

ListId reference from cm_lists

mobile

varchar(255)

mobile

phone

varchar(255)

phone

postalCode

varchar(255)

postalCode

priority

int(2) unsigned

priority (used by Motion Bull)

scheduledat

datetime

scheduledat (used by Motion Bull)

skype

varchar(255)

skype account id

street

varchar(255)

street

tags

varchar(255)

tags

teams

varchar(255)

MS teams account id

twitter

varchar(255)

twitter account id

updatedAt

datetime

updatedAt

url

varchar(255)

url

UserId

int(11)

UserId

viber

varchar(255)

viber account id

wechat

varchar(255)

WeChat account id

 

cm_lists

The contacts list definition table

 

column_name

column_type

description

column_name

column_type

description

createdAt

datetime

createdAt

description

varchar(255)

description

dialPrefix

varchar(255)

If set, the prefix will be used when the contacts are called using the click2dial function

id

int(11)

autoincrement id

name

varchar(255)

name

updatedAt

datetime

updatedAt

 

cm_custom_fields

The custom fields definition table

 

column_name

column_type

description

column_name

column_type

description

alias

varchar(255)

alias

createdAt

datetime

createdAt

deletedAt

datetime

deletedAt

id

int(11)

autoincrement id

ListId

int(11)

ListId

required

tinyint(1)

0: no 1: yes (mandatory)

type

varchar(255)

Text|Select|Number|Switch|DateTime

updatedAt

datetime

updatedAt

values

longtext

values

 

cm_companies 

The companies list definition table

 

column_name

column_type

description

column_name

column_type

description

city

varchar(255)

city

companyId

varchar(255)

external company reference

country

varchar(255)

country

createdAt

datetime

createdAt

description

varchar(255)

description

email

varchar(255)

email address

emailDomain

varchar(255)

company email domain

fax

varchar(255)

fax number

name

varchar(255)

name

phone

varchar(255)

phone number

postalCode

varchar(255)

postal code

street

varchar(255)

street

sCity

varchar(255)

shipping city

sCountry

varchar(255)

shipping country

sPostalCode

varchar(255)

shipping postal code

sStreet

varchar(255)

shipping street

type

varchar(255)

type

updatedAt

datetime

updatedAt

vat

varchar(255)

vat number

website

varchar(255)

company website

 

Voice

report_queue

The queue calls details

 

column_name

column_type

description

column_name

column_type

description

accountcode

varchar(255)

agent internal  extension if available

assigned

tinyint(1)

1: call assigned to agent | 0: call not assigned

calleridname

varchar(255)

caller name

calleridnum

varchar(255)

caller number

channel

varchar(255)

SIP channel

connectedlinename

varchar(255)

agent name (where available)

connectedlinenum

varchar(255)

agent number (where available)

context

varchar(255)

context

count

int(11)

count (Asterisk parameter)

createdAt

datetime

createdAt

disposition

varchar(255)

call disposition

exten

varchar(255)

called DID

holdtime

int(11)

queue waiting time (sec)

id

int(11)

autoincrement id

lastAssignedTo

varchar(255)

agent username

mohtime

int(11)

sum of waiting time in queue + on hold time during call

originalposition

int(11)

originalposition (Asterisk queue parameter)

position

int(11)

queue position 

priority

varchar(255)

priority

queue

varchar(255)

queue name

queuecallerabandon

tinyint(1)

0: not abandoned | 1:  abandoned

queuecallerabandonAt

datetime

when the call is abandoned

queuecallercomplete

tinyint(1)

0: not answered | 1: answered

queuecallercompleteAt

datetime

when the call is completed

queuecallerenterreason

int(11)

0: new call | 1: transferred call | 2: consultant call

queuecallerexit

tinyint(1)

0: call is completed into the queue | 1: call is exited from the queue 

queuecallerexitAt

datetime

when the call is exited from the queue

queuecallerexitreason

varchar(255)

queue exit reason

queuecallerjoinAt

datetime

when the call joined to the queue

queuecallerleaveAt

datetime

when the call left the queue

transfer

tinyint(1)

0: no tranfer | 1: call is transferred

transferexten

varchar(255)

transfer destination extension (replace transferextension in V1)

transfertype

varchar(255)

type of transfer (blind|attended|warm)

transferuniqueid

varchar(255)

transfer destination uniqueid 

type

varchar(255)

call type (inbound | outbound)

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

 

report_call

Calls information based on the Asterisk cdr data

 

column_name

column_type

description

column_name

column_type

description

accountcode

varchar(255)

agent internal  extension if available

amaflags

varchar(255)

no use

answertime

datetime

when the call is answered by an agent

billableseconds

int(11)

billable seconds

callerid

varchar(255)

caller id

channel

varchar(255)

channel

ContactId

int(11)

contact id 

createdAt

datetime

createdAt

destination

varchar(255)

destination number

destinationchannel

varchar(255)

destinationchannel

destinationcontext

varchar(255)

destinationcontext

disposition

varchar(255)

Asterisk standard disposition

duration

int(11)

duration

endtime

datetime

when the call ended

id

int(11)

autoincrement id

lastapplication

varchar(255)

last Asterisk application

lastdata

text

last Asterisk app data

mohtime

int(11)

sum of waiting time in queue + on hold time during call

note

varchar(255)

note

prefix

varchar(255)

the outbound prefix used

routeId

int(11)

the dialplan route id

source

varchar(255)

source number

starttime

datetime

when the call started

tag

varchar(255)

tag

type

enum('inbound','internal','outbound','inbound-fax','outbound-fax','dialer')

call type

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

userDisposition

varchar(255)

user Disposition

userfield

varchar(255)

userfield

UserId

int(11)

UserId

 

report_dial

The table contains detailed info about the outbound calls

 

column_name

column_type

description

column_name

column_type

description

accountcode

varchar(255)

agent internal  extension if available

answertime

datetime

answertime

billableseconds

int(11)

billableseconds

calleridname

varchar(255)

caller name

calleridnum

varchar(255)

caller number

channel

varchar(255)

channel

channelstate

int(11)

channelstate

channelstatedesc

varchar(255)

channelstatedesc

connectedlinename

varchar(255)

agent name (where available)

connectedlinenum

varchar(255)

agent number (where available)

context

varchar(255)

context

createdAt

datetime

createdAt

destaccountcode

varchar(255)

destaccountcode

destcalleridname

varchar(255)

destcalleridname

destcalleridnum

varchar(255)

destcalleridnum

destchannel

varchar(255)

destchannel

destchannelstate

int(11)

destchannelstate

destchannelstatedesc

varchar(255)

destchannelstatedesc

destconnectedlinename

varchar(255)

destconnectedlinename

destconnectedlinenum

varchar(255)

destconnectedlinenum

destcontext

varchar(255)

destcontext

destexten

varchar(255)

destexten

destlanguage

varchar(255)

destlanguage

destlinkedid

varchar(255)

destlinkedid

destpriority

int(11)

destpriority

destuniqueid

varchar(255)

destuniqueid

dialstatus

varchar(255)

dialstatus

dialstring

varchar(255)

dialstring

duration

int(11)

duration

endtime

datetime

endtime

exten

varchar(255)

called DID

holdtime

int(11)

queue waiting time (sec)

id

int(11)

autoincrement id

language

varchar(255)

language

lastevent

varchar(255)

lastevent

linkedid

varchar(255)

linkedid

priority

int(11)

priority

privilege

varchar(255)

privilege

routeId

int(11)

the dialplan route id

starttime

datetime

starttime

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

 

report_call_transfer

The table contains detailed info about the blind and the attended transfer.

Please note the following definitions are used into the tables:

transferer = Who transfer the call (first call leg)

transferee = Who is transferred (second call leg)

 

column_name

column_type

description

column_name

column_type

description

context

varchar(255)

call context

createdAt

datetime

createdAt

extension

varchar(255)

Called DID

id

int(11)

autoincrement id

isexternal

enum('Yes','No')

Not Used

result

varchar(255)

Fail|Success|Invalid|Not Permitted

transfereeaccountcode

varchar(255)

transfereeaccountcode

transfereecalleridname

varchar(255)

transfereecalleridname

transfereecalleridnum

varchar(255)

transfereecalleridnum

transfereechannel

varchar(255)

transfereechannel

transfereeconnectedlinename

varchar(255)

transfereeconnectedlinename

transfereeconnectedlinenum

varchar(255)

transfereeconnectedlinenum

transfereecontext

varchar(255)

transfereecontext

transfereeexten

varchar(255)

transfereeexten

transfereelinkedid

varchar(255)

transfereelinkedid

transfereraccountcode

varchar(255)

transfereraccountcode

transferercalleridname

varchar(255)

transferercalleridname

transferercalleridnum

varchar(255)

transferercalleridnum

transfererchannel

varchar(255)

transfererchannel

transfererconnectedlinename

varchar(255)

transfererconnectedlinename

transfererconnectedlinenum

varchar(255)

transfererconnectedlinenum

transferercontext

varchar(255)

transferercontext

transfererexten

varchar(255)

transfererexten

transfererlinkedid

varchar(255)

transfererlinkedid

type

enum('blind','attended')

type

updatedAt

datetime

updatedAt

 

voice_recordings

The table contains detailed info about the recorded calls

 

column_name

column_type

description

column_name

column_type

description

accountcode

varchar(255)

agent internal  extension if available

calleridname

varchar(255)

caller name

calleridnum

varchar(255)

caller number

channel

varchar(255)

Asterisk SIP channel

connectedlinename

varchar(255)

agent name (where available)

connectedlinenum

varchar(255)

agent number (where available)

context

varchar(255)

context

createdAt

datetime

createdAt

exten

varchar(255)

called DID

id

int(11)

autoincrement id

membername

varchar(255)

agent username

queue

varchar(255)

queue name

rating

int(11)

rating

type

varchar(255)

call type (internal|inbound|outbound)

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

userDisposition

varchar(255)

user disposition

UserId

int(11)

UserId

value

varchar(255)

recording path

 

Motion Bull Dialer

 

The Motion Bull dialer has totally replaced the TigerDial module used on the Motion V1 and XCALLY Shuttle.

The new dialer mode is not backward compatible with the old dialer modules.

cm_hopper

The table contains the scheduled contacts the dialer engine has to dial

 

column_name

column_type

description

column_name

column_type

description

active

tinyint(1)

1: the contact is being processed otherwise is 0

callback

tinyint(1)

1: the contact source is the callback IVR block

callbackat

datetime

when the callback has been required

callbackuniqueid

varchar(255)

the source inbound call id 

CampaignId

int(11)

CampaignId

ContactId

int(11)

ContactId

countbusyretry

int(11)

number of busy attempts

countcongestionretry

int(11)

number of congestion attempts

countnoanswerretry

int(11)

number of no answer attempts

createdAt

datetime

createdAt

id

int(11)

autoincrement id

ListId

int(11)

ListId

phone

varchar(255)

phone

priority

int(2) unsigned

priority

recallme

tinyint(1)

the contact has been assigned to an agent 1: yes | 0: no

scheduledat

datetime

when the contact has been scheduled

updatedAt

datetime

updatedAt

UserId

int(11)

the agent id assigned to the contact

VoiceQueueId

int(11)

VoiceQueueId

 

cm_hopper_final

The final status of the contacts managed by the Motion Bull

 

column_name

column_type

description

column_name

column_type

description

agiafterat

datetime

if present, an IVR project has started after the agent hangup

amd

tinyint(1)

automatic machine detection 0:disabled | 1:enabled

answertime

datetime

when the call has been answered by the customer

blacklist

tinyint(1)

blacklisted contact

callback

tinyint(1)

1: the contact source is the callback IVR block

callbackat

datetime

when the callback has been required

callbackuniqueid

varchar(255)

the source inbound call id 

calleridname

varchar(255)

contact name

calleridnum

varchar(255)

contact number

campaign

varchar(255)

outbound campaign

CampaignId

int(11)

CampaignId

campaigntype

varchar(255)

campaigntype (queue|ivr)

ContactId

int(11)

ContactId

countbusyretry

int(11)

number of busy attempts

countcongestionretry

int(11)

number of congestion attempts

countglobal

int(11)

global retries counter

countnoanswerretry

int(11)

number of no answer attempts

createdAt

datetime

createdAt

deleted

varchar(255)

deleted (1: the contact has been deleted)

deletedat

datetime

deletedat

disposition

varchar(255)

disposition

dispositionat

datetime

when the agent has set the disposition

dropreason

varchar(255)

the reason the call has been dropped (timeout|callerexit)

droptime

datetime

when the call has been dropped

endtime

datetime

the end of the call

fax

tinyint(1)

fax

followuptime

int(11)

followuptime

holdtime

int(11)

queue waiting time (sec)

id

int(11)

autoincrement id

ListId

int(11)

ListId

membername

varchar(255)

agent username

originatecalleridname

varchar(255)

originatecalleridname

originatecalleridnum

varchar(255)

originatecalleridnum

reason

varchar(255)

hangup reason

recallme

tinyint(1)

the contact has been assigned to an agent 1: yes | 0: no

rescheduled

tinyint(1)

the contact has been rescheduled (0:no | 1:yes)

rescheduledat

datetime

when the contact has been rescheduled

responsetime

datetime

when the customer answered the call

ringtime

int(11)

ringing time

scheduledat

datetime

scheduledat

starttime

datetime

when the call started

state

int(2)

status of the contact

statedesc

varchar(255)

status description**

talktime

int(11)

talking time

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

VoiceQueueId

int(11)

VoiceQueueId

** Here is the complete status description

cm_hopper_history

The detailed log about every call handled by the Motion Bull

 

column_name

column_type

description

column_name

column_type

description

amd

tinyint(1)

automatic machine detection 0:disabled | 1:enabled

answertime

datetime

when the call has been answered by the customer

callback

tinyint(1)

1: the contact source is the callback IVR block

callbackat

datetime

when the callback has been required

callbackuniqueid

varchar(255)

the source inbound call id 

calleridname

varchar(255)

contact name

calleridnum

varchar(255)

contact number

campaign

varchar(255)

outbound campaign

CampaignId

int(11)

CampaignId

campaigntype

varchar(255)

campaigntype (queue|ivr)

ContactId

int(11)

ContactId

countbusyretry

int(11)

number of busy attempts

countcongestionretry

int(11)

number of congestion attempts

countglobal

int(11)

global retries counter

countnoanswerretry

int(11)

number of no answer attempts

createdAt

datetime

createdAt

dropreason

varchar(255)

the reason the call has been dropped (timeout|callerexit)

droptime

datetime

when the call has been dropped

edited

tinyint(1)

the contact has been manually edited

editedat

datetime

when the contact has been edited

endtime

datetime

the end of the call

fax

tinyint(1)

fax

followuptime

int(11)

followuptime

holdtime

int(11)

queue waiting time (sec)

id

int(11)

autoincrement id

ListId

int(11)

ListId

membername

varchar(255)

agent username

originatecalleridname

varchar(255)

caller id name for the originated call

originatecalleridnum

varchar(255)

caller id number for the originated call

reason

varchar(255)

hangup reason

recallme

tinyint(1)

the contact has been assigned to an agent 1: yes | 0: no

responsetime

datetime

when the customer answered the call

ringtime

int(11)

ringing time

scheduledat

datetime

scheduledat

starttime

datetime

when the call started

state

int(2)

status of the contact

statedesc

varchar(255)

status description**

talktime

int(11)

talking time

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

VoiceQueueId

int(11)

VoiceQueueId

**Here is the complete status description

 

Motion Bull Campaigns and db tables relation

The cm_contacts contains the contacts, as the name says itself, that will be called by the dialer. 

When the campaign is started and the contact list is added, the contacts to be called will be moved to the cm_hopper table, that is the ‘realtime’ table of the dialer. It means that it will show the contacts that are being processed and it will also hold the values for the schedule time in case of a contact reschedule.

Every attempt to call the single contact will be inserted as a single record in the cm_hopper_history with the call result, and when the contact is closed (for any of the calls result), a record will be written in the cm_hopper_final, so the last record for that contact that you will find in the cm_hopper_history will be a lot similar to the record you’ll find in the cm_hopper_final.

There is also the cm_hopper_black table, which will hold the contacts that, before (and instead of) being moved to the cm_hopper table, have been found in a contacts blacklist assigned to the campaign.

 

 

Cally Square (IVR)

report_square

CallySquare projects operations

 

 

column_name

column_type

description

column_name

column_type

description

accountcode

varchar(255)

agent internal  extension if available

callerid

varchar(255)

caller number

calleridname

varchar(255)

caller name

callingani2

varchar(255)

system config

callingpres

varchar(255)

system config

callingtns

varchar(255)

system config

callington

varchar(255)

system config

channel

varchar(255)

channel

context

varchar(255)

context

createdAt

datetime

createdAt

dnid

varchar(255)

called extension number

enhanced

varchar(255)

system config

extension

varchar(255)

called extension number

id

int(11)

autoincrement id

joinAt

datetime

when the call has joined the IVR progect

language

varchar(255)

language

leaveAt

datetime

when the call has left the IVR project

network

varchar(255)

network

network_script

varchar(255)

network_script

priority

varchar(255)

priority

project_name

varchar(255)

the CallySqaure project name

rdnis

varchar(255)

system config

request

varchar(255)

request

threadid

varchar(255)

threadid

type

varchar(255)

type

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

version

varchar(255)

version

 

report_square_details

CallySquare blocks data

 

column_name

column_type

description

column_name

column_type

description

application

varchar(255)

CallySquare application

callerid

varchar(255)

caller number

createdAt

datetime

when the application started

data

text

data

id

int(11)

autoincrement id

node

varchar(255)

block name

project_name

varchar(255)

project name

uniqueid

varchar(255)

call unique id

updatedAt

datetime

updatedAt

 

Chat

report_chat_queue

Chat queue operation details 

 

column_name

column_type

description

column_name

column_type

description

acceptAt

datetime

when the agent accepted the chat

ChatInteractionId

int(11)

ChatInteractionId

ChatMessageId

int(11)

ChatMessageId

ChatQueueId

int(11)

ChatQueueId

ChatWebsiteId

int(11)

ChatWebsiteId

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

exitAt

datetime

exitAt (in case of timeout)

from

varchar(255)

from

id

int(11)

autoincrement id

joinAt

datetime

joinAt

leaveAt

datetime

leaveAt

ListId

int(11)

ListId

reason

varchar(255)

reason (accepted | timeout)

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

 

chat_interactions

Chat interactions details

 

column_name

column_type

description

column_name

column_type

description

browserName

varchar(255)

browserName

browserVersion

varchar(255)

browserVersion

ChatWebsiteId

int(11)

ChatWebsiteId

closed

tinyint(1)

Status 0: open | 1: closed

closedAt

datetime

closedAt

closeReason

varchar(255)

contact|agent|system

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

customerIp

varchar(255)

customerIp

deviceModel

varchar(255)

deviceModel

deviceType

varchar(255)

deviceType

deviceVendor

varchar(255)

deviceVendor

disposition

varchar(255)

disposition

formData

longtext

formData

id

int(11)

autoincrement id

lastMsgAt

datetime

the last message datetime

lastMsgDirection

enum('in','out')

the last message direction

mailTranscript

varchar(255)

the destination email for the trsnscript

note

varchar(255)

note

osName

varchar(255)

osName

osVersion

varchar(255)

osVersion

pathTranscript

text

the text file of the chat transcript

ratingMessage

text

ratingMessage

ratingType

enum('star','thumb')

ratingType

ratingValue

int(11)

ratingValue

read1stAt

datetime

the chat answer time

referer

varchar(255)

the webpage

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

 

chat_messages

The chat message content table

 

column_name

column_type

description

column_name

column_type

description

AttachmentId

int(11)

AttachmentId

body

mediumtext

body

ChatInteractionId

int(11)

ChatInteractionId

ChatWebsiteId

int(11)

ChatWebsiteId

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

direction

enum('in','out')

direction

id

int(11)

autoincrement id

read

tinyint(1)

message unread (0) read (1)

readAt

datetime

readAt

secret

tinyint(1)

1: the message is not visible to the customer

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

 

chat_offline_messages

The offline message details

 

column_name

column_type

description

column_name

column_type

description

body

mediumtext

body

ChatWebsiteId

int(11)

ChatWebsiteId

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

id

int(11)

autoincrement id

updatedAt

datetime

updatedAt

 

chat_websites

Chat website configuration table

 

column_name

column_type

description

column_name

column_type

description

address

varchar(255)

address

agentAlias

varchar(255)

agentAlias

agentAvatar

text

agentAvatar

agentIdentifier

varchar(255)

agentIdentifier

agentTransfer

tinyint(1)

agentTransfer

agentTransferTimeout

int(11)

agentTransferTimeout

alignment

varchar(255)

alignment

animation

tinyint(1)

animation

autoclose

tinyint(1)

autoclose

backgroundColor

varchar(255)

backgroundColor

closingMessage

varchar(255)

closingMessage

closingMessageButton

varchar(255)

closingMessageButton

closingQuestion

varchar(255)

closingQuestion

color

varchar(255)

color

color_button

varchar(255)

color_button

conditionAgreement

tinyint(1)

conditionAgreement

createdAt

datetime

createdAt

customerAlias

varchar(255)

customerAlias

customerAvatar

text

customerAvatar

defaultTitle

varchar(255)

defaultTitle

defaultWhiteLabel

tinyint(1)

defaultWhiteLabel

description

varchar(255)

description

download_transcript

tinyint(1)

download_transcript

enableCustomerAttachment

tinyint(1)

enableCustomerAttachment

enableCustomerCheckmarks

tinyint(1)

enableCustomerCheckmarks

enableCustomerWriting

tinyint(1)

enableCustomerWriting

enableFeedback

tinyint(1)

enableFeedback

enableRating

tinyint(1)

enableRating

enableUnmanagedNote

tinyint(1)

enableUnmanagedNote

fontSize

int(11)

fontSize

formSubmitFailureMessage

varchar(255)

formSubmitFailureMessage

formSubmitSuccessMessage

varchar(255)

formSubmitSuccessMessage

forwardOffline

tinyint(1)

forwardOffline

forwardOfflineAddress

varchar(255)

forwardOfflineAddress

forwardTranscript

tinyint(1)

forwardTranscript

forwardTranscriptMessage

varchar(255)

forwardTranscriptMessage

header_offline

varchar(255)

header_offline

header_online

varchar(255)

header_online

header_shape

enum('rounded','squared')

header_shape

hideWhenOffline

tinyint(1)

hideWhenOffline

id

int(11)

autoincrement id

IntervalId

int(11)

IntervalId

key

varchar(255)

key

ListId

int(11)

ListId

mapKey

varchar(255)

mapKey

mapKeyOffline

varchar(255)

mapKeyOffline

messageFontSize

int(11)

messageFontSize

messagesAlignment

varchar(255)

messagesAlignment

name

varchar(255)

name

noteTitle

varchar(255)

noteTitle

notificationShake

tinyint(1)

notificationShake

notificationSound

tinyint(1)

notificationSound

notificationTemplate

text

notificationTemplate

offlineForm

text

offlineForm

offlineMessageBody

varchar(255)

offlineMessageBody

offlineMessageSubject

varchar(255)

offlineMessageSubject

offline_chat_button

varchar(255)

offline_chat_button

onlineForm

text

onlineForm

placeholderMessage

varchar(255)

placeholderMessage

queueTransfer

tinyint(1)

queueTransfer

queueTransferTimeout

int(11)

queueTransferTimeout

ratingStarsNumber

int(11)

ratingStarsNumber

ratingType

enum('star','thumb')

ratingType

remote

varchar(255)

remote

sendUnmanaged

varchar(255)

sendUnmanaged

showAgentAvatar

tinyint(1)

showAgentAvatar

showCustomerAvatar

tinyint(1)

showCustomerAvatar

sitepic

text

sitepic

skipMessageButton

varchar(255)

skipMessageButton

skipUnmanaged

varchar(255)

skipUnmanaged

start_chat_button

varchar(255)

start_chat_button

systemAlias

varchar(255)

systemAlias

systemAvatar

text

systemAvatar

textColor

varchar(255)

textColor

timeout

int(11)

timeout

timezone

varchar(255)

timezone

token

varchar(255)

token

unmanagedMessage

varchar(255)

unmanagedMessage

updatedAt

datetime

updatedAt

verticalAlignment

int(11)

verticalAlignment

waitForTheAssignedAgent

int(11)

waitForTheAssignedAgent

waitingMessage

varchar(255)

waitingMessage

waitingTitle

varchar(255)

waitingTitle

whiteLabel

varchar(255)

whiteLabel

 

Email

report_mail_queue

Email queue operation details

 

column_name

column_type

description

column_name

column_type

description

acceptAt

datetime

when the email has been accepted by the agent

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

exitAt

datetime

when the email exited from the queue (unmanaged)

from

varchar(255)

from

id

int(11)

autoincrement id

joinAt

datetime

when the email has joined the queue

leaveAt

datetime

when the email has left the queue

ListId

int(11)

ListId

MailAccountId

int(11)

MailAccountId

MailInteractionId

int(11)

MailInteractionId

MailMessageId

int(11)

MailMessageId

MailQueueId

int(11)

MailQueueId

reason

varchar(255)

accepted | timeout

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

 

mail_interactions

Email interaction details

 

column_name

column_type

description

column_name

column_type

description

attach

tinyint(1)

number of attachments

cc

longtext

cc

closed

tinyint(1)

0: open | 1: closed

closedAt

datetime

when the ineraction has been closed

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

disposition

varchar(255)

disposition

firstMsgDirection

enum('in','out')

direction of the first message (in | out)

id

int(11)

autoincrement id

inReplyTo

varchar(190)

inReplyTo

lastMsgAt

datetime

last message date time

lastMsgBody

longtext

las message body

lastMsgDirection

enum('in','out')

direction of the last message

MailAccountId

int(11)

MailAccountId

note

varchar(255)

note

read1stAt

datetime

when the first message has been read

subject

text

subject

substatus

varchar(60)

substatus

substatusAt

datetime

when the substatus has been applied

to

varchar(255)

to

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

 

mail_messages

Email messages details

 

column_name

column_type

description

column_name

column_type

description

attach

int(11)

number of attachments

bcc

longtext

bcc

body

longtext

body

cc

longtext

cc

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

direction

enum('in','out')

direction

from

varchar(255)

from

id

int(11)

autoincrement id

MailAccountId

int(11)

MailAccountId

MailInteractionId

int(11)

MailInteractionId

messageId

varchar(190)

messageId

originCc

longtext

originCc

originTo

longtext

originTo

read

tinyint(1)

0: message unread | 1: message read

readAt

datetime

when the message has been read

secret

tinyint(1)

0: public message | 1: message visible only by agent (note)

sentAt

datetime

when the email has been sent

subject

text

subject

to

longtext

to

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

 

mail_accounts

Email accounts configuration table

 

column_name

column_type

description

column_name

column_type

description

active

tinyint(1)

active

agentTransfer

tinyint(1)

agentTransfer

agentTransferTimeout

int(11)

agentTransferTimeout

createdAt

datetime

createdAt

description

varchar(255)

description

email

varchar(255)

email

fontFamily

varchar(255)

fontFamily

fontSize

int(2) unsigned

fontSize

id

int(11)

autoincrement id

key

varchar(255)

key

ListId

int(11)

ListId

markAsUnread

tinyint(1)

markAsUnread

name

varchar(255)

name

notificationShake

tinyint(1)

notificationShake

notificationSound

tinyint(1)

notificationSound

notificationTemplate

text

notificationTemplate

queueTransfer

tinyint(1)

queueTransfer

queueTransferTimeout

int(11)

queueTransferTimeout

service

tinyint(1)

service

template

text

template

TemplateId

int(11)

TemplateId

updatedAt

datetime

updatedAt

waitForTheAssignedAgent

int(11)

waitForTheAssignedAgent

 

SMS

report_SMS_queue

SMS queue operations details

 

 

column_name

column_type

description

column_name

column_type

description

acceptAt

datetime

when the SMS has been accepted by the agent

ContactId

int(11)

ContactId

createdAt

datetime

createdAt

exitAt

datetime

when the interaction exited the queue

from

varchar(255)

sender

id

int(11)

autoincrement id

joinAt

datetime

when the interaction has joined the queue

leaveAt

datetime

when the interaction has left the queue

ListId

int(11)

ListId

reason

varchar(255)

queue exit reason (accepted|timeout)

SmsAccountId

int(11)

SmsAccountId

SmsInteractionId

int(11)

SmsInteractionId

SmsMessageId

int(11)

SmsMessageId

SmsQueueId

int(11)

SmsQueueId

uniqueid

varchar(255)

unique id

updatedAt

datetime

updatedAt

UserId

int(11)

UserId

 

 

sms_interactions

Table name: sms_interactions

Table name: sms_interactions

id

 

closed

Indicates if the interaction is closed

closedAt

Timestamp when the interaction is closed

disposition

The closing disposition specified by the agent (if any)

note

Note written by the agent when disposing

phone

Customer mobile phone number

UserId

Agent owner id

SmsAccountId

Sms Account Id

ContactId

Contact Id

createdAt

Timestamp when the interaction is created

read1stAt

The date the interaction has been accepted

sms_messages

Table name: sms_messages

Table name: sms_messages

id

 

body

Text of the message

read

True if the message is read by the customer, false otherwise

direction

Message direction (in, out)

messageId

Message Id generated by the Sms provider

phone

Customer mobile phone number

SmsAccountId

Sms Account Id

SmsInteractionId

Sms Interaction Id

UserId

Agent Id

ContactId

Contact Id

createdAt

Timestamp when the message is created

readAt

The date the interaction has been read

 

SMS_Accounts 

SMS account configuration table

 

 

column_name

column_type

description

column_name

column_type

description

accountSid

varchar(255)

accountSid

agentTransfer

tinyint(1)

agentTransfer

agentTransferTimeout

int(11)

agentTransferTimeout

apiKey

varchar(255)

apiKey

authId

varchar(255)

authId

authToken

varchar(255)

authToken

baseUrl

varchar(255)

baseUrl

createdAt

datetime

createdAt

deliveryReport

tinyint(1)

deliveryReport

description

varchar(255)

description

id

int(11)

autoincrement id

key

varchar(255)

key

ListId

int(11)

ListId

name

varchar(255)

name

notificationShake

tinyint(1)

notificationShake

notificationSound

tinyint(1)

notificationSound

notificationTemplate

text

notificationTemplate

password

varchar(255)

password

phone

varchar(255)

phone

queueTransfer

tinyint(1)

queueTransfer

queueTransferTimeout

int(11)

queueTransferTimeout

remote

varchar(255)

remote

senderString

varchar(255)

senderString

smsMethod

enum('basic','classic','classic+')

smsMethod

token

varchar(255)

token

type

enum('twilio','skebby','connectel','clicksend','plivo')

type

updatedAt

datetime

updatedAt

username

varchar(255)

username

waitForTheAssignedAgent

int(11)

waitForTheAssignedAgent

 

 

Open Channel

report_openchannel_queue

The report_openchannel_queue is a table made for the explicit purpose to track data for tracking agents performance and time spent between message arriving on XCALLY and message accepted by agents.

That is why you will probably have one or more records on report_openchannel_queue for every interaction. A message incoming from customer to XCALLY will follow this routing algorythm:

  • first it wait for previously assigned agent, if not accepted, it will go to the assigned queue

  • if in the queue, it will wait the time specified in the queue

So if the message gets timeout from agent, it will go to the queue and each pass through the queue it is tracked as a new row in report_openchannel_queue

 

 

Table name: report_openchannel_queue

id

 

 

uniqueid

Unique ID

 

from

The value mapped within the API request

 

joinAt

Time the customer joined the queue

 

leaveAt

Time the customer left the queue

 

acceptAt

Time the agent accepted the chat

 

exitAt

Time the customer left the queue (it is set if the reason is timeout)

 

reason

Reason the customer left the queue (accepted, timeout)

 

OpenchannelAccountId

Openchannel Account Id

 

ListId

 

 

ContactId

 

 

OpenchannelInteractionId

 

 

OpenchannelMessageId

 

 

UserId

 

 

OpenchannelQueueId

 

 

openchannel_interactions

The table openchannel_interactions is not used for analytics because it contains live data as the interaction progresses.

Table name: openchannel_interactions

Table name: openchannel_interactions

id

 

closed

Indicates if the interaction is closed

closedAt

time the interaction is closed

disposition

The closing disposition specified by the agent (if any) or the routing algorithm (abandoned, unmanaged)

note

Note written by the agent when disposing

UserId

Agent owner id

OpenchannelAccountId

Openchannel account Id

ContactId

Contact Id

createdAt

Creation Date

read1stAt

The date the interaction has been accepted

openchannel_messages

 

Table name: openchannel_messages

Table name: openchannel_messages

id

 

body

Text of the message

read

True if the message is read by the customer, false otherwise

direction

Message direction (in, out)

OpenchannelAccountId

Openchannel Account Id

OpenchannelInteractionId

Openchannel Interaction Id

UserId

Agent Id

ContactId

Contact Id

createdAt

Creation Date

read1stAt

The date the interaction has been accepted

readAt

The date the interaction has been read

 

 

Openchannel_Accounts 

Openchannel account configuration table

 

 

column_name

column_type

description

column_name

column_type

description

agentTransfer

tinyint(1)

agentTransfer

agentTransferTimeout

int(11)

agentTransferTimeout

createdAt

datetime

createdAt

description

varchar(255)

description

id

int(11)

autoincrement id

key

varchar(255)

key

ListId

int(11)

ListId

mapKey

varchar(255)

mapKey

name

varchar(255)

name

notificationShake

tinyint(1)

notificationShake

notificationSound

tinyint(1)

notificationSound

notificationTemplate

text

notificationTemplate

queueTransfer

tinyint(1)

queueTransfer

queueTransferTimeout

int(11)

queueTransferTimeout

replyUri

varchar(255)

replyUri

token

varchar(255)

token

updatedAt

datetime

updatedAt

waitForTheAssignedAgent

int(11)

waitForTheAssignedAgent

 

 

Fax

report_fax_queue

 

 

 

Table name: report_fax_queue

id

 

 

uniqueid

Unique ID

 

from

Customer fax phone number

 

joinAt

Timestamp when the customer joined the queue

 

leaveAt

Timestamp when the customer left the queue

 

acceptAt

Timestamp when the agent accepted the chat

 

exitAt

Timestamp when the customer left the queue (it is set if the reason is timeout)

 

reason

Reason why the customer left the queue (accepted, timeout)

 

FaxAccountId

Fax Account Id associated to the interaction

 

ListId

CM List Id within the customer profile

 

ContactId

CM Contact Id associated to the interaction (customer profile)

 

FaxInteractionId

Fax Interaction Id

 

FaxMessageId

Fax Message Id

 

UserId

User who accepted the message

 

FaxQueueId

Fax Queue Id

 

createdAt

Creation date

 

fax_interactions

 

Table name: fax_interactions

Table name: fax_interactions

id

 

closed

Indicates if the interaction is closed

closedAt

Timestamp when the interaction is closed

disposition

The closing disposition specified by the agent (if any)

note

Note written by the agent when disposing

UserId

Agent owner id

FaxAccountId

Fax Account Id

ContactId

Contact Id

createdAt

Timestamp when the interaction is created

fax_messages

 

Table name: fax_messages

Table name: fax_messages

id

 

body

Attachment file name

read

True if the message is read by the customer, false otherwise

direction

Message direction (in, out)

FaxAccountId

Fax Account Id

FaxInteractionId

Fax Interaction Id

UserId

Agent Id

ContactId

Contact Id

AttachmentId

Attachment Id

createdAt

Timestamp when the message is created

readAt

The date the interaction has been read

 

 

Fax_Accounts 

Fax account configuration table

 

 

column_name

column_type

description

column_name

column_type

description

agentTransfer

tinyint(1)

agentTransfer

agentTransferTimeout

int(11)

agentTransferTimeout

createdAt

datetime

createdAt

description

varchar(255)

description

ecm

enum('yes','no')

ecm

faxdetect

varchar(255)

faxdetect

gateway

varchar(255)

gateway

headerinfo

varchar(255)

headerinfo

id

int(11)

autoincrement id

key

varchar(255)

key

ListId

int(11)

ListId

localstationid

varchar(255)

localstationid

maxrate

enum('2400','4800','7200','9600','12000','14400')

maxrate

minrate

enum('2400','4800','7200','9600','12000','14400')

minrate

modem

varchar(255)

modem

name

varchar(255)

name

notificationShake

tinyint(1)

notificationShake

notificationSound

tinyint(1)

notificationSound

notificationTemplate

text

notificationTemplate

queueTransfer

tinyint(1)

queueTransfer

queueTransferTimeout

int(11)

queueTransferTimeout

t38timeout

int(11)

t38timeout

tech

enum('SIP','IAX','DADHI','KHOMP')

tech

TrunkId

int(11)

TrunkId

updatedAt

datetime

updatedAt

waitForTheAssignedAgent

int(11)

waitForTheAssignedAgent

 

 

WhatsApp_Campaigns

WhatsApp account configuration table

 

column_name

column_type

description

column_name

column_type

description

hopper_history_duplicates_days

 

 

 

Jscripty

report_jscripty_answers

 

 

 

Table name: report_jscripty_answers

id

 

question

The questions of the survey. There will be as many rows per question as how many answers have been chosen for that question.

answer

The answer relative to the question. In case of multiple answers for the same question, there will be as many rows as the number of the chosen answers.

membername

The name of the agent that started the session.

projectname

The name of the Jscripty Project.

queue

The name of the Queue in case the caller is coming from a Queue.

uniqueid

The unique ID of the call.

calleridname

The caller name, if present.

calleridnum

The caller number.

createdAt

The date of creation of the row, that correspond to the date of the survey completion.

updatedAt

 

SessionId

The ID of the interaction.

ProjectId

The Jscripty Project ID.

 

 

report_jscripty_questions

 

Table name: report_jscripty_questions

id

 

question

The questions of the survey, one row for every question.

answer

The answers of the survey. In case of multiple answers on the same question, the values will be separated by a column symbol.

membername

The name of the agent that started the session.

projectname

The name of the Jscripty Project.

queue

The name of the Queue in case the caller is coming from a Queue.

uniqueid

The unique ID of the call.

calleridname

The caller name, if present.

calleridnum

The caller number.

createdAt

The date of creation of the row, that corresponds to the date of the survey completion.

updatedAt

 

SessionId

The ID of the interaction.

ProjectId

The Jscripty Project ID.

report_jscripty_sessions

 

Table name: report_jscripty_sessions

id

 

starttime

The start date of the session.

endtime

The end date of the session.

membername

The name of the agent that started the session.

projectname

The name of the Jscripty Project.

queue

The name of the Queue in case the caller is coming from a Queue.

uniqueid

The unique ID of the call.

calleridname

The caller name, if present.

calleridnum

The caller number.

createdAt

The date of creation of the row, that corresponds to the date of the survey completion.

updatedAt

 

VoiceQueueId

The Queue ID, if the caller is coming from a Queue.

ContactId

The Contact ID, if the session is associated to a Contact.

ProjectId

The Jscripty Project ID.

 

Â