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!
| 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 |
| 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 |
| Name | Name of the category |
| 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) |
| 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 |
| 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 |
| 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 |
| 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 of the relation (e-mail, phone number etc.) |
| 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 |
| 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 |
| 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 |
| 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 |
| 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, 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, 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| Field | Description |
|---|
| UniqueId | Primary key |
| Active | If false, the tag is deleted |
| 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 |
| Name | The name of the tag |
| TagCategory_UniqueId | Foreign key to TagCategories |
| 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 |
| 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 |
| 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 |
