Data Documentation

Introduction

In case you’d like to access your own raw MSC Pro data to connect to your internal BI, you can ask your Customer Success Manager or Support for this access.

Once you received your access credentials, you can log into your data warehouse in access mode. You are unable to see the tables in this access mode, but when you start writing a new query, it will automatically connect to the right tables.

The data in your DWH will be refreshed once every 24 hours, typically between 02:00 and 06:00 CET. In case you bought the MSC Wallboards and have them open, some tables (not all tables) will be refreshed every couple of seconds/minutes.

All date/times fields in the tables described are in UTC.

In the following chapters, all tables and fields will be shown with the relevant information.

If you log into your data warehouse and see tables that have not been documented here, they are meant for other purposes or are at end-of-life. Therefore, we advise you not to use them.

Please note that our Customer Success Team can share with you some best practises on how to get the most use out of your data, however we do not offer support on the raw data itself nor of what you do and build with this data.

Last but not least we have to inform you that we are working on a better DWH, this means that somewhere in the future the way of getting access might change.

Have fun with your data!

Accounts

FieldDescription
UniqueIdPrimary Key
CreationDateTimeThe date and time when the record was created in our database
EmailAddressThe email address of the MSC user
LastUpdatedDateTimeThe date and time of the last change of the record

Categories

FieldDescription
UniqueIdPrimary key
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change of the record
NameName of the category

ChannelAccounts

FieldDescription
UniqueIdPrimary key
Channel_IdForeign key to Channels
CreationDateTimeThe date and time when the record was created in our database
ExternalIdentifierThe name of the channel which is used by the API to identify the channel
LastUpdatedDateTimeThe date and time of the last change of the record
NameThe name of the channel
ChannelTypeThe name of the channel type (offline or instant)

Channels

FieldDescription
IdPrimary Key
ChannelNameThe name of the channel
CreationDateTimeThe date and time when the record was created in our database
IsCustomChannelIndicates whether the channel is custom (true or false)
LastUpdatedTimeThe date and time of the last change of the record

ConversationActions

FieldDescription
UniqueIdPrimary key
ActionThe name of the action. Consists of: Archive, BulkActionOpen, BulkActionClose, MarkAsUnwanted, MoveToInbox, NewMessage, NewConversation, ReOpen, Snooze, StartDecreaseWorkload, TagAdded, TagDeleted, UnSnooze, or WinMessage
ActionDateTimeThe date and time when an action was executed
Conversation_UniqueIdForeign key to Conversations
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change of the record

ConversationGroups

FieldDescription
UniqueIdPrimary key
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change of the record

Conversations

FieldDescription
UniqueIdPrimary key
AcquisitionDateThe date the conversation contributed to acquisition
AnswerDateTimeThe date when the MSC user has answered the last message of the relation
AnswerStateIndicates whether the initial written message on the external side in the agent inbox is too late or not (based on the service level): OnTime or TooLate. Be aware that the Answer State will be logged for the channel Phone, in case a note is written on the external side.
ArchiveDateTimeThe latest date a user archived the conversation
ArchivePersonUniqueIdThe user that was the last one to archive the conversation
Category_UniqueIdForeign key to categories
CreationDateTimeThe date and time when the record was created in our database
ExternalIdentifierThe external identifier of the relation (e-mail, phone number etc.)
GuidThe unique guid which is being used in the widget
InboxChannelIdThe channelId which is being used in the inbox to show the correct icon
InboxChannelTypeThe name of the channel type (offline or instant)
InboxCustomerSinceThe date since the relation is known in the order system
InboxNumberOfOrdersTotal number of orders of the relation
InboxSentimentThe sentiment of the relation: Negative, Neutral, or Positive
InboxTotalRevenueTotal revenue of the relation
InboxUserHasAnsweredWhether a user has answered the last message of the relation
LastTagDateTimeThe last date and time the conversation was tagged
LastUpdatedDateTimeThe date and time of the last change of the record
LatestExternalMessageDateTimeThe date and time of the last external message (relation or user)
LinkedOrderNumbersAll order numbers that are linked to the conversation
ReferrerThe URL or e-mail from where the conversation is started
RetentionDateThe date the conversation contributed to retention
ServiceLevelOrangeThe date when the SLA turns orange
ServiceLevelRedThe date when the SLA turns red
SnoozeDateTimeThe date and time of the moment when the conversation was snoozed
SnoozeInInboxDateTimeThe date and time the snoozed conversation will appear in the inbox again
SnoozeServiceLevelRedDateTimeThe date when the SLA turns red in combination with snooze
SnoozeStateThe snooze state: SnoozedConversationInInbox or ReSnoozeIsAvaillable
StartDateTimeThe StartDateTime is the date time of the first message that was sent, where the CreationDateTime is the time the conversation is created in the database.
StateState of the conversation: Open, Archived, or Snoozed
SubjectThe subject of the conversation
WebStore_UniqueIdForeign key to Webstores. This field is dynamic and can be changed by agents.
InboxOwnerPersonIdForeign key to People (User), this is the owner of the conversation
TagId1 (through 10)The Id’s of the tags allocated to the conversation
TagName1 (through 10)The names of the tags allocated to the conversation
Evaluation_Id1= unwanted mailbox, 2 = normal inbox
InboxExternalIdentifierEmail address or phone number that started the conversation

ConversationNativeVoiceData

FieldDescription
UniqueIdThe unique id of the ConversationNativeVoiceData record
AnswerDateTimeThe date/time the calling parties can hear each other at this given date/time. Outgoing call:the consumer picks up the phone. Incoming call: technical delay after accepting the call.
CallIdThe unique id of the call. Used as reference to native VOICE
CallingDateTimeThe date/time the connection is setup and the phone starts ringing at the consumer (outgoing call) or the agent accepted the call (incoming call).
CreationDateTimeThe creation of the ConversationNativeVoiceData record
DisconnectDateTimeThe date/time the call was disconnected
LastUpdatedDateTimeThe date and time of the last change of the record
NativeVoiceDirectionThe direction of the call
NativeVoiceStateThe state of the call
NewCallDateTimeThe date/time the inbound call was received at MSC. The caller is now put in the waiting queue
RoutingKeyWordsOptional Routing keywords, given by the IVR to route the call to the webstore related to the given keywords

ConversationTags

FieldDescription
UniqueIdPrimary Key
Conversation_UniqueIdForeign key to Conversations
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change of the record
Tag_UniqueIdForeign key to tags
Actor_UniqueIdForeign key to people
StateNULL = unknown, 1, = active, 2 = inactive
CreationActionNULL = unknown, 1 = manual, 2 = opt-out by rule-based trigger, 3 = opt-in by advanced trigger, 4= opt-out by advanced trigger

DailyOpeningHours

FieldDescription
UniqueIdPrimary Key
CreationDateTimeThe date and time when the record was created in our database
DayOfTheWeekMonday, Tuesday, Wednesday Thursday, Friday, Saturday, or Sunday
FromTimeThe opening time of the day
ToTimeThe closing time of the day
LastUpdatedDateTimeThe date and time of the last change of the record
ServiceHoursTemplate_UniqueIdForeign key to serviceHoursTemplate

Messages

FieldDescription
UniqueIdPrimary key
Actor_UniqueIdUsed for system messages, the MSC user who executed the action
AutoMessageContentThe content of an auto message
BccParticipantsThe email addresses in the bcc
CcParticipantsThe email addresses in the cc
ChannelAccount_UniqueIdForeign key to ChannelAccounts
CollaborationModeInternal or External
ContentThe content of a message
Conversation_UniqueIdForeign key to Conversations
ConversionMessageSentDateTimeThe date and time of the last external agent message before the win-message took place.
CreationDateTimeThe date and time when the record was created in our database
DiscriminatorAttachmentNotAvailableMessage, Automessage, BulkActionSystemMessage, CallQueuedMessage, CallAcceptedMessage, CallClosedMessage, CallConnectedMessage, CallDisconnectedMessage, CallFailedMessage, CallOnHoldMessage, CallRecordingStartedMessage, CallRecordingStoppedMessage, CallRingingMessage, ClickableContentMessage, CoBrowseInvitationSystemMessage, ConnectedConversationSystemMessage, ConversationArchivedAdvancedRoutingSystemMessage, ConversationArchivedSystemMessage, ConversationForwardedSystemMessage, ConversationReopenedSystemMessage, ConversationReroutedSystemMessage, ConversionSystemMessage, DisconnectedConversationSystemMessage, LeaveConversationSystemMessage, ListPickerInteractiveReplyMessage, ListPickerMessage, LocationMessage, MissedCallMessage, OwnerAwaySystemMessage, OwnerChangedSystemMessage, ParticipationMessage, ParticipationRemovedSystemMessage, PhoneForwardedSystemMessage, PhoneParticipationMessage, ReplyButtonsInteractiveReplyMessage, ReplyButtonsMessage, StoryMentionMessage, StoryReplyMessage, SnoozeSystemMessage, TakeOwnershipSystemMessage, TemplateParticipationMessage, UnSnoozeSystemMessage, UnsupportedTypeMessage, ViewedProductMessage or WebStoreChangedSystemMessage
IsPrivateWhether the message is private (Twitter or Channel API). NULL = unknown, 0 = False, 1 = True
LastUpdatedDateTimeThe date and time of the last change of the record
OrderIdThe OrderId of the order shown in the win-message
Origin_UniqueIdUsed for system messages, the owner of the conversation before the action was executed
RatingSad, Neutral, or Happy
RatingDateTimeThe date and time of the last rating
RevenueThe revenue of the order shown in the win-message
ScopeInternal or External
Sender_UniqueIdForeign key to participants, the sender of the message
SenderWasOwnerWhether the sender also was the owner of the conversation
SentDateTimeThe date and time when the message was sent (don’t confuse this with CreationDateTime)
SubjectThe subject of the message
Subject_UniqueIdUsed for system messages, the new owner of the conversation after the action was executed
ToParticipantsThe email addresses in the to

Participations

FieldDescription
UniqueIdPrimary key
Conversation_UniqueIdForeign key to conversation
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change of the record
Person_UniqueIdForeign key to people (Relation, User, or ExternalCollaborationUser)
StatusActive or InActive

People

FieldDescription
UniqueIdPrimary key
AvatarThe URL of the 40px users’ avatar
CreationDateTimeThe date and time when the record was created in our database
DiscriminatorType of person (Relation, User or ExternalCollaboration-User)
EmailAddressThe Email Address of the person
ExternalIdentifierThe external identifier of the relation (e-mail, phone number etc.)
FirstNameThe first name
LastNameThe surname
LastUpdatedDateTimeThe date and time of the last change of the record
MaxChatsThe maximum queue size of chats for a user
MaxWaitingChatsThe maximum queue size of chats for a user that is waiting for a first response
UserPresenceAutomatic, Away, BusyMaxWaitingChats, BusyMaxChats, BusyOnThePhone, Offline, or Online
LastSentDateTimeThe date when the last message was sent
Avatar128The URL of the 128px users’ avatar
Account_UniqueIDForeign key to Accounts

Profiles

FieldDescription
UniqueIdPrimary key
Channel_IdForeign key to Channels
CreationDateTimeThe date and time when the record was created in our database
ExternalIdentifierThe external identifier of the relation (e-mail, phone number etc.)
LastUpdatedDateTimeThe date and time of the last change of the record
Person_UniqueIdForeign key to People (Relation)
PhoneNumberIn case of a phone channel, this indicates the phone number
ScreenNameThe Twitter screen name

SalesPerformance

FieldDescription
UniqueIdPrimary key
RetentionCounterThe total number of retention orders of the day
AcquisitionCounterThe total number of acquisition orders of the day
RetentionRevenueThe total revenue of retention orders of the day
AcquisitionRevenueThe total revenue of acquisition orders of the day
Person_UniqueIdForeign key to People (User)
AcquisitionProfitThe total profit of acquisition orders of the day
RetentionProfitThe total profit of retention orders of the day
DayIndicatorThe linked day for the sales performance

ServiceHoursTemplates

FieldDescription
UniqueIdPrimary key
ActiveWhen false, the template has been deleted
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change in the record
ServiceLevelInMinutesThe time in minutes when the conversation turns red
TimeZoneIdThe timezone that is selected when the conversation turns red

TagCategories

FieldDescription
UniqueIdPrimary key
ActiveIs the Tag Category active? True or False
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedTimeThe date and time of the last change of the record
NameName of the category

Tags

FieldDescription
UniqueIdPrimary key
ActiveIf false, the tag is deleted
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change of the record
NameThe name of the tag
TagCategory_UniqueIdForeign key to TagCategories

UserPresenceLogs

FieldDescription
UniqueIdPrimary key
Person_UniqueIdForeign key to People
UserPresenceUser status in the app: Automatic, Away, BusyMaxWaitingChats, BusyMaxChats, BusyOnThePhone, Offline, or Online
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedDateTimeThe date and time of the last change of the record

Webstores

FieldDescription
UniqueIdPrimary key
ActiveIndicates whether the webstore is active
AddressOneFirst line of the address in the widget
AddressTwoSecond line of the address in the widget
CreationDateTimeThe date and time when the record was creation in our database
DefaultStoreIndicates whether the webstore is the default webstore
LastUpdatedDateTimeThe date and time of the last change to the record
OnlineIconUrlLargeThe image of the webstore
OnlineNotificationEmail-ReplyFromEmailAddressThe reply email address of the webstore
OnlineNotificationEmail-ReplyFromSenderNameThe sender name of the webstore
OnlineNotificationEmail-ReturnUrlThe return URL of the webstore
PhoneNumberPhone number in the widget
SelfServiceLanguageThe language of the widget
WebStoreNameThe name of the webstore
ServiceHoursTemplate_UniqueIdForeign keys to templates

WebstoreUsers

FieldDescription
UniqueIdPrimary key
ChatIndicates whether chats can be assigned to the MSC user This information is now in the Channels table
CreationDateTimeThe date and time when the record was created in our database
LastUpdatedTimeThe date and time of the last change of the record
MailIndicates whether mails and contact forms can be assigned to the MSC user This information is now in the Channels table
User_UniqueIdForeign key to People (User)
WebStore_UniqueIdForeign key to Webstore
WebStoreOwnerIndicates whether the MSC user is the webstore owner

Relational Schema