V3 Motion DB Tables
- XCALLY Knowledge (Unlicensed)
- Product Knowledge
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.
- 1 Staff
- 1.1 report_agentÂ
- 1.2 report_member
- 2 Contact Manager
- 2.1 cm_contacts
- 2.2 cm_lists
- 2.3 cm_custom_fields
- 2.4 cm_companiesÂ
- 3 Voice
- 3.1 report_queue
- 3.2 report_call
- 3.3 report_dial
- 3.4 report_call_transfer
- 3.5 voice_recordings
- 4 Motion Bull Dialer
- 5 Cally Square (IVR)
- 6 Chat
- 7 Email
- 7.1 report_mail_queue
- 7.2 mail_interactions
- 7.3 mail_messages
- 7.4 mail_accounts
- 8 SMS
- 8.1 report_SMS_queue
- 8.2 sms_messages
- 8.3 SMS_AccountsÂ
- 9 Open Channel
- 10 Fax
- 10.1 report_fax_queue
- 10.2 fax_interactions
- 10.3 fax_messages
- 10.4 Fax_AccountsÂ
- 10.5 WhatsApp_Campaigns
- 11 Jscripty
Staff
report_agentÂ
 The table contains the information about the queue calls answered or unanswered by the agents
Â
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 |
|---|---|---|
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 |
|---|---|---|
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 |
varchar(255) | ||
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 |
varchar(255) | twitter account id | |
updatedAt | datetime | updatedAt |
url | varchar(255) | url |
UserId | int(11) | UserId |
viber | varchar(255) | viber account id |
varchar(255) | WeChat account id |
Â
cm_lists
The contacts list definition table
Â
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 |
|---|---|---|
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 |
|---|---|---|
city | varchar(255) | city |
companyId | varchar(255) | external company reference |
country | varchar(255) | country |
createdAt | datetime | createdAt |
description | varchar(255) | description |
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
Â
report_mail_queue
Email queue operation details
Â
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 |
|---|---|---|
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 |
lastReopenedAt | timestamp | when the last reopening occurred |
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 |
|---|---|---|
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 |
|---|---|---|
active | tinyint(1) | active |
agentTransfer | tinyint(1) | agentTransfer |
agentTransferTimeout | int(11) | agentTransferTimeout |
createdAt | datetime | createdAt |
description | varchar(255) | description |
varchar(255) | ||
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 |
|---|---|---|
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 | |
|---|---|
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 | |
|---|---|
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 |
|---|---|---|
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 | |
|---|---|
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 | |
|---|---|
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 |
|---|---|---|
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 | |
|---|---|
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 | |
|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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. |
Â
Â
Tables report_jscripty_questions and report_jscripty_answers show the answers related to the multiple choice questions in a different way:
in
report_jscripty_questionsthe answers are written in a single fieldin
report_jscripty_answerseach answer is written in a different row