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

Field	Description
UniqueId	Primary Key
CreationDateTime	The date and time when the record was created in our database
EmailAddress	The email address of the MSC user
LastUpdatedDateTime	The date and time of the last change of the record

ChannelAccounts

Field	Description
UniqueId	Primary key
Channel_Id	Foreign key to Channels
CreationDateTime	The date and time when the record was created in our database
ExternalIdentifier	The name of the channel which is used by the API to identify the channel
LastUpdatedDateTime	The date and time of the last change of the record
Name	The name of the channel
ChannelType	The name of the channel type (offline or instant)

Channels

Field	Description
Id	Primary Key
ChannelName	The name of the channel
CreationDateTime	The date and time when the record was created in our database
IsCustomChannel	Indicates whether the channel is custom (true or false)
LastUpdatedTime	The date and time of the last change of the record

ConversationActions

Field	Description
UniqueId	Primary key
Action	The name of the action. Consists of: Archive, BulkActionOpen, BulkActionClose, MarkAsUnwanted, MoveToInbox, NewMessage, NewConversation, ReOpen, Snooze, StartDecreaseWorkload, TagAdded, TagDeleted, UnSnooze, or WinMessage
ActionDateTime	The date and time when an action was executed
Conversation_UniqueId	Foreign key to Conversations
CreationDateTime	The date and time when the record was created in our database
LastUpdatedDateTime	The date and time of the last change of the record

ConversationGroups

Field	Description
UniqueId	Primary key
CreationDateTime	The date and time when the record was created in our database
LastUpdatedDateTime	The date and time of the last change of the record

Conversations

Field	Description
UniqueId	Primary key
AcquisitionDate	The date the conversation contributed to acquisition
AnswerDateTime	The date when the MSC user has answered the last message of the relation
AnswerState	Indicates 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.
ArchiveDateTime	The latest date a user archived the conversation
ArchivePersonUniqueId	The user that was the last one to archive the conversation
Category_UniqueId	Foreign key to categories
CreationDateTime	The date and time when the record was created in our database
ExternalIdentifier	The external identifier to the equivalent of this conversation in external systems (e.g. the sessionId of the router-session or the callId of the Native Voice call with the relation)
Guid	The unique guid which is being used in the widget
InboxChannelId	The channelId which is being used in the inbox to show the correct icon
InboxChannelType	The name of the channel type (offline or instant)
InboxCustomerSince	The date since the relation is known in the order system
InboxNumberOfOrders	Total number of orders of the relation
InboxSentiment	The sentiment of the relation: Negative, Neutral, or Positive
InboxTotalRevenue	Total revenue of the relation
InboxUserHasAnswered	Whether a user has answered the last message of the relation
LastTagDateTime	The last date and time the conversation was tagged
LastUpdatedDateTime	The date and time of the last change of the record
LatestExternalMessageDateTime	The date and time of the last external message (relation or user)
LinkedOrderNumbers	All order numbers that are linked to the conversation
Referrer	The URL or e-mail from where the conversation is started
RetentionDate	The date the conversation contributed to retention
ServiceLevelOrange	The date when the SLA turns orange
ServiceLevelRed	The date when the SLA turns red
SnoozeDateTime	The date and time of the moment when the conversation was snoozed
SnoozeInInboxDateTime	The date and time the snoozed conversation will appear in the inbox again
SnoozeServiceLevelRedDateTime	The date when the SLA turns red in combination with snooze
SnoozeState	The snooze state: SnoozedConversationInInbox or ReSnoozeIsAvaillable
StartDateTime	The 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.
State	State of the conversation: Open, Archived, or Snoozed
Subject	The subject of the conversation
WebStore_UniqueId	Foreign key to Webstores. This field is dynamic and can be changed by agents.
InboxOwnerPersonId	Foreign 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_Id	1= unwanted mailbox, 2 = normal inbox
InboxExternalIdentifier	Email address or phone number that started the conversation

ConversationContexts

Field	Description
Conversation_UniqueId	Foreign key to Conversations
Name	The name of the field that holds the unique identifier to the customer profile in the customers' dynamic integration
Value	The value for the unique identifier to the customer profile in the customers' dynamic integration
UniqueId	Primary key
CreationDateTime	The date and time when the record was created in our database
LastUpdatedDateTime	The date and time of the last change of the recordef

ConversationNativeVoiceData

obsolete

Field	Description
UniqueId	The unique id of the ConversationNativeVoiceData record
AnswerDateTime	The 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.
CallId	The unique id of the call. Used as reference to native VOICE
CallingDateTime	The date/time the connection is setup and the phone starts ringing at the consumer (outgoing call) or the agent accepted the call (incoming call).
CreationDateTime	The creation of the ConversationNativeVoiceData record
DisconnectDateTime	The date/time the call was disconnected
LastUpdatedDateTime	The date and time of the last change of the record
NativeVoiceDirection	The direction of the call
NativeVoiceState	The state of the call
NewCallDateTime	The date/time the inbound call was received at MSC. The caller is now put in the waiting queue
RoutingKeyWords	Optional Routing keywords, given by the IVR to route the call to the webstore related to the given keywords

ConversationTags

Field	Description
UniqueId	Primary Key
Conversation_UniqueId	Foreign key to Conversations
CreationDateTime	The date and time when the record was created in our database
LastUpdatedDateTime	The date and time of the last change of the record
Tag_UniqueId	Foreign key to tags
Actor_UniqueId	Foreign key to people
State	NULL = unknown, 1, = active, 2 = inactive
CreationAction	NULL = unknown, 1 = manual, 2 = opt-out by rule-based trigger, 3 = opt-in by advanced trigger, 4= opt-out by advanced trigger

DailyOpeningHours

Field	Description
UniqueId	Primary Key
CreationDateTime	The date and time when the record was created in our database
DayOfTheWeek	Monday, Tuesday, Wednesday Thursday, Friday, Saturday, or Sunday
FromTime	The opening time of the day
ToTime	The closing time of the day
LastUpdatedDateTime	The date and time of the last change of the record
ServiceHoursTemplate_UniqueId	Foreign key to serviceHoursTemplate

Messages

Field	Description
UniqueId	Primary key
Actor_UniqueId	Used for system messages, the MSC user who executed the action
AutoMessageContent	The content of an auto message
BccParticipants	The email addresses in the bcc
CcParticipants	The email addresses in the cc
ChannelAccount_UniqueId	Foreign key to ChannelAccounts
CollaborationMode	Internal or External
Content	The content of a message
Conversation_UniqueId	Foreign key to Conversations
ConversionMessageSentDateTime	The date and time of the last external agent message before the win-message took place.
CreationDateTime	The date and time when the record was created in our database
Discriminator	AttachmentNotAvailableMessage, 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
IsPrivate	Whether the message is private (Twitter or Channel API). NULL = unknown, 0 = False, 1 = True
LastUpdatedDateTime	The date and time of the last change of the record
OrderId	The OrderId of the order shown in the win-message
Origin_UniqueId	Used for system messages, the owner of the conversation before the action was executed
Rating	Sad, Neutral, or Happy
RatingDateTime	The date and time of the last rating
Revenue	The revenue of the order shown in the win-message
Scope	Internal or External
Sender_UniqueId	Foreign key to participants, the sender of the message
SenderWasOwner	Whether the sender also was the owner of the conversation
SentDateTime	The date and time when the message was sent (don’t confuse this with CreationDateTime)
Subject	The subject of the message
Subject_UniqueId	Used for system messages, the new owner of the conversation after the action was executed
ToParticipants	The email addresses in the to

Participations

Field	Description
UniqueId	Primary key
Conversation_UniqueId	Foreign key to conversation
CreationDateTime	The date and time when the record was created in our database
LastUpdatedDateTime	The date and time of the last change of the record
Person_UniqueId	Foreign key to people (Relation, User, or ExternalCollaborationUser)
Status	Active or InActive

People

Field	Description
UniqueId	Primary key
Avatar	The URL of the 40px users’ avatar
CreationDateTime	The date and time when the record was created in our database
Discriminator	Type of person (Relation, User or ExternalCollaboration-User)
EmailAddress	The Email Address of the person
ExternalIdentifier	The external identifier of the relation (e-mail, phone number etc.)
FirstName	The first name
LastName	The surname
LastUpdatedDateTime	The date and time of the last change of the record
MaxChats	The maximum queue size of chats for a user
MaxWaitingChats	The maximum queue size of chats for a user that is waiting for a first response
UserPresence	Automatic, Away, BusyMaxWaitingChats, BusyMaxChats, BusyOnThePhone, Offline, or Online
LastSentDateTime	The date when the last message was sent
Avatar128	The URL of the 128px users’ avatar
Account_UniqueID	Foreign key to Accounts

Profiles

Field	Description
UniqueId	Primary key
Channel_Id	Foreign key to Channels
CreationDateTime	The date and time when the record was created in our database
ExternalIdentifier	The external identifier of the relation (e-mail, phone number etc.)
LastUpdatedDateTime	The date and time of the last change of the record
Person_UniqueId	Foreign key to People (Relation)
PhoneNumber	In case of a phone channel, this indicates the phone number
ScreenName	The Twitter screen name

SalesPerformance

Field	Description
UniqueId	Primary key
RetentionCounter	The total number of retention orders of the day
AcquisitionCounter	The total number of acquisition orders of the day
RetentionRevenue	The total revenue of retention orders of the day
AcquisitionRevenue	The total revenue of acquisition orders of the day
Person_UniqueId	Foreign key to People (User)
AcquisitionProfit	The total profit of acquisition orders of the day
RetentionProfit	The total profit of retention orders of the day
DayIndicator	The linked day for the sales performance

ServiceHoursTemplates

Field	Description
UniqueId	Primary key
Active	When false, the template has been deleted
CreationDateTime	The date and time when the record was created in our database
LastUpdatedDateTime	The date and time of the last change in the record
ServiceLevelInMinutes	The time in minutes when the conversation turns red
TimeZoneId	The timezone that is selected when the conversation turns red

TagCategories

Field	Description
UniqueId	Primary key
Active	Is the Tag Category active? True or False
CreationDateTime	The date and time when the record was created in our database
LastUpdatedTime	The date and time of the last change of the record
Name	Name of the category

UserPresenceLogs

Field	Description
UniqueId	Primary key
Person_UniqueId	Foreign key to People
UserPresence	User status in the app: Automatic, Away, BusyMaxWaitingChats, BusyMaxChats, BusyOnThePhone, Offline, or Online
CreationDateTime	The date and time when the record was created in our database
LastUpdatedDateTime	The date and time of the last change of the record

Webstores

Field	Description
UniqueId	Primary key
Active	Indicates whether the webstore is active
AddressOne	First line of the address in the widget
AddressTwo	Second line of the address in the widget
CreationDateTime	The date and time when the record was creation in our database
DefaultStore	Indicates whether the webstore is the default webstore
LastUpdatedDateTime	The date and time of the last change to the record
OnlineIconUrlLarge	The image of the webstore
OnlineNotificationEmail-ReplyFromEmailAddress	The reply email address of the webstore
OnlineNotificationEmail-ReplyFromSenderName	The sender name of the webstore
OnlineNotificationEmail-ReturnUrl	The return URL of the webstore
PhoneNumber	Phone number in the widget
SelfServiceLanguage	The language of the widget
WebStoreName	The name of the webstore
ServiceHoursTemplate_UniqueId	Foreign keys to templates

WebstoreUsers

Field	Description
UniqueId	Primary key
~~Chat~~	~~Indicates whether chats can be assigned to the MSC user~~ This information is now in the Channels table
CreationDateTime	The date and time when the record was created in our database
LastUpdatedTime	The date and time of the last change of the record
~~Mail~~	~~Indicates whether mails and contact forms can be assigned to the MSC user~~ This information is now in the Channels table
User_UniqueId	Foreign key to People (User)
WebStore_UniqueId	Foreign key to Webstore
WebStoreOwner	Indicates whether the MSC user is the webstore owner