Skip to main content

SFMC System Data Views

Dive deep into System Data Views - the backend Marketing Cloud data. Actionable pearls await.

Data Views Basics#

System Data Views are built-in backend Data Extensions that you can use to find information about your subscribers and sends. You cannot change them, but you can leverage their data in SQL queries for quick reports in Query Studio or outputting to the standard Data Extension. They are excellent fuel for SSJS logic.

Data Views have data retention settings set to 6 months. If you want to store any data for a more extended period, you must create an Automation that will be copying data to your Data Extension.

Be careful, however, as some of the System Data Views can get absurdly big quick, and making copies within your Marketing Cloud Instance may lead to performance issues. In such cases, a much better idea might be to export that data on a scheduled basis to an external Data Warehouse.

Another important point related to Data Views to always be aware of which Data View you genuinely want to use. For example, in most cases, you will be interested in parent-level _Subscribers Data View, which means you will need to use the ENT. prefix in your query.

You Should Know

Below you can find selected Salesforce Marketing Cloud Data Views focused on Subscribers and Email Channel. I plan to cover the rest of them in the future.

Be sure to check the awesome Data Views relationship diagram created by Zuzanna Jarczyńska. It will for sure help you with multi-view JOINs.


Subscriber Data Views#

_Subscribers#

Stores information about your Subscribers. Great for high-level perspective or adding Email Address field to other more detailed Data Views.

NameDescriptionData TypeNullable
SubscriberIDThe subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.Number
DateUndeliverableThe date an email for the subscriber was returned as undeliverableDateX
DateJoinedThe Date, the subscriber, joined your listDateX
DateUnsubscribedThe Date, the subscriber, unsubscribed from your listDateX
DomainThe domain of the subscriberTextX
EmailAddressThe subscriber's email addressEmail
BounceCountThe total number of bounces accrued by the subscriberNumber
SubscriberKeyA potential alternate identifier for subscribers.Text
SubscriberTypeThe subscriber type for the affected subscriberText
StatusThe status of the subscriber: active, held, unsubscribed, or bounced.TextX
LocaleThe locale code for the subscriberLocaleX

When working with _Subscribers Data View:

  1. If you are using the query in child Business Unit, always check whether you are querying local or enterprise Data View (ENT. prefix).
  2. If you need high-level data on your subscribers, this Data View might be enough. If you want to learn more (for example, why subscriber bounced, where did he unsubscribe) you will need to JOIN other Data Views.
  3. Use SubscriberID to JOIN data from different engagement-based Data Views like _Sent, _Open, _Click or _Bounce.
  4. The Status field is excellent for a quick check of your database quality. It is also nice to manage multiple records. Pull the selection of your subscribers with SQL Query, add a new status with custom value (for example 'unsubscribed' AS Status) and leverage Automation to export-transfer-import the Data Extension to All Subscribers.

_EnterpriseAttribute#

Stores information about your Profile Attributes from Email Studio.

NameDescriptionData TypeNullable
_SubscriberIDThe subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record stored.Number

When working with _EnterpriseAttribute Data View:

  1. If you are using the query in child Business Unit always use ENT. prefix as it is enterprise-level only Data View.
  2. When querying or joining _EnterpriseAttribute always be sure to include the underscore before SubscriberID - this is the only Data View that requires it.
  3. Despite Email Address field is visible in the Salesforce Marketing Cloud UI when you go to Profile Attributes, it is not part of the _EnterpriseAttribute Data View. To get an Email Address of your Subscriber, query the _Subscribers Data View.
  4. _EnterpriseAttribute Data View is unique, as it might contain spaces in field names. You build it by creating the Profile Attributes in Email Studio. If your profile attribute name contains space, you will have to use the square bracket notation to capture it: SELECT [Attribute With Spaces] FROM _EnterpriseAttribute.

Email Data Views#

_Job#

Stores information about your Email Sends Jobs. Great for capturing detailed data about your communication, especially when paired with Send Log. Must-have for extending your engagement-based Email Data Views.

NameDescriptionData TypeNullable
JobIDThe job ID number for the email sendNumber
EmailIDThe email ID for the jobNumberX
AccountIDThe ID number for the account that performed the jobNumberX
AccountUserIDThe ID number for the account user that performed the jobNumberX
FromNameThe from name in the email sendTextX
FromEmailThe from email address in the email sendEmailX
SchedTimeThe time the job was scheduledDateX
PickupTimeThe time the Marketing Cloud application started the jobDateX
DeliveredTimeThe time the email was deliveredDateX
EventIDThe ID for the job eventTextX
IsMultipartWhether the job was sent as multi-part MIME or notBoolean
JobTypeThe type of jobTextX
JobStatusThe status of the jobTextX
ModifiedByIf modified, the user who modified the jobNumberX
ModifiedDateThe date the job was modifiedDateX
EmailNameThe name of the email sent by the jobTextX
EmailSubjectThe subject of the email sendTextX
IsWrappedWhether the links in the email were wrapped for trackingBoolean
TestEmailAddrThe test email address used in the jobEmailX
CategoryThe job categoryText
BccEmailAny email address send in the BCC fieldEmailX
OriginalSchedTimeThe initially scheduled time for the jobDateX
CreatedDateThe date the job was createdDate
CharacterSetThe character set used in the jobTextX
IPAddressThis value will always be a null value.TextX
SalesForceTotalSubscriberCountThe total number of Salesforce subscribers included in the jobNumber
SalesForceErrorSubscriberCountThe total number of Salesforce subscribers included in the job that received errorsNumber
SendTypeThe type of send used in the jobText
DynamicEmailSubjectThe dynamic email subject included in the jobTextX
SuppressTrackingIf tracking information for this job was suppressedBoolean
SendClassificationTypeThe send classification type for the jobTextX
SendClassificationThe send classification for the jobTextX
ResolveLinksWithCurrentDataIf the job resolved links with current dataBoolean
EmailSendDefinitionThe email send definition used in the jobTextX
DeduplicateByEmailWhether the email addresses in the job are used to deduplicate subscribersBoolean
TriggererSendDefinitionObjectIDThe object ID for the triggered send definitionNumberX
TriggeredSendCustomerKeyThe customer key for the triggered sendTextX

When working with _Job Data View:

  1. JobID field will be your bread and butter to JOIN with other engagement-based Data Views.
  2. EmailName, FromName, and FromEmail are handy data points missing in the _Sent, _Open, _Click and _Bounce Data Views and worth checking for reporting needs.
  3. If you are performing A/B tests or want to create an analysis of the subject impact on performance, be sure to pick EmailSubject. Pair it with CASE to create a great knowledge source for your content team.
  4. For debugging purposes be sure to check out IsWrapped and SuppressTracking - if you are missing tracking data, CharacterSet - if you have problems with formatting, SendClassification - if you have issues with unsubscribers or spam complaints.
  5. AccountUserID field is fantastic for _Job-based Audit Log creation. Unfortunately, there is no UI-based easy way to determine which user has which UserID. You can either use SOAP API or match based on these SQL results.
You Should Know

_Job Data View is unique, as it is limited to a single Business Unit. Your parent Business Unit has access to _Sent, _Open, _Click and other data from all Business Units, but this is not the case with _Job. You can query only those jobs that occurred in the Business Unit where you execute your SQL snippet.

Always consider this when choosing the type of JOIN statement.


_Sent#

Stores information about your Email Sends. Great for understanding who had a chance to receive your message and whose communication did not leave your system.

NameDescriptionData TypeNullable
AccountIDYour parent account ID (MID) numberNumber
OYBAccountIDThe account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be NullNumberX
JobIDThe job ID number for the email sendNumber
ListIDThe list ID number for the list used in the sendNumber
BatchIDThe batch ID number for any batches used in the sendNumber
SubscriberIDThe subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.Number
SubscriberKeyThe subscriber key for the affected subscriberText
EventDateThe date the send took placeDate
DomainThe domain at which the send occurredText
TriggererSendDefinitionObjectIDThe object ID for the triggered send definitionTextX
TriggeredSendCustomerKeyThe customer key for the triggered sendTextX

When working with _Sent Data View:

  1. Use JobID, ListID, BatchID and SubscriberId or SubscriberKey to match various events from multiple Data Views like _Open, _Click, _Bounce for a single engagement with a particular subscriber. Example: Query for Debugging Email Sends.
  2. The most useful data here is the EventDate field that lets you know when the email was sent (and whether that happened at all).
  3. If you have triggered a send for an email and after a few minutes you don't see it here, in most cases it means that the send was blocked and never left Marketing Cloud. Check personalisation, data source, RaiseError scripts.

_Open#

Stores information about your Email Opens. For when you need to know when someone opened your email. Kind of.

NameDescriptionData TypeNullable
AccountIDYour parent account ID (MID) numberNumber
OYBAccountIDThe account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be NullNumberX
JobIDThe job ID number for the email sendNumber
ListIDThe list ID number for the list used in the sendNumber
BatchIDThe batch ID number for any batches used in the sendNumber
SubscriberIDThe subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.Number
SubscriberKeyThe subscriber key for the affected subscriberText
EventDateThe date the open took placeDate
DomainThe domain at which the open occurredText
IsUniqueWhether the event is unique or repeatedBooleanX
TriggererSendDefinitionObjectIDThe object ID for the triggered send definitionTextX
TriggeredSendCustomerKeyThe customer key for the triggered sendTextX

When working with _Open Data View:

  1. Use JobID, ListID, BatchID and SubscriberId or SubscriberKey to match various events from multiple Data Views like _Sent, _Click, _Bounce for a single engagement with a particular subscriber. Example: Query for Debugging Email Sends.
  2. Use _open.IsUnique = 1 in JOIN or WHERE to focus on first occurrences of each event. Example: Query for Debugging Email Sends.
  3. The most useful data here is the EventDate field that lets you know when the email was opened (and whether that happened at all). But with email opens it's not that easy. In many cases, lack of open data in this Data View doesn't mean that your recipient for sure did not open the email. And in some instances tracked open might not mean he did.
You Should Know

There is no official feedback loop that informs Marketing Cloud (or any other Marketing Automation Platform) that your recipient opened the email.

All Marketing Automation Platforms put an invisible 1px wide image in the email body that is unique for every email you send. When someone opens an email, its images are downloaded from the server. SFMC treats download of this 1px wide image as proof that the recipient opened the email. And in most cases it is right. But...

The images don't always render (that's why you should always use alt-text). Some recipients opt-in on a per-email basis to download email graphics. Government audiences might not even have this option. It can be because of the admin policy, the privacy concerns over engagement tracking or limited internet bandwidth. In such a case, even if the user opens an email, you won't have information about it.

It might lead to weird data, like clicks visible for contacts that did not open. Marketing Automation Platforms track Clicks via a different, more confident approach.

It can also lead to cases, where user have both click and open data, but click data is earlier than the open. Why? Your recipient clicked something in your email and only after that decided to allow his email program to load the images in it.

There might be some cases, where the data is wrong the other way - when you see that email as opened, but in reality, the user didn't engage with it. It sometimes happens for B2B sends when your recipient's administrator uses bots that automatically check the emails for malicious or spammy content. You can recognise such things by checking the EventDate on _Open and _Click Data Views, as those bots will open the email and click all its links within few seconds. Something not possible for a real human.


_Click#

Stores information about your Email Clicks. Detailed click reporting and behavioural logic are possible thanks to this one.

NameDescriptionData TypeNullable
AccountIDYour parent account ID (MID) numberNumber
OYBAccountIDThe account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be NullNumberX
JobIDThe job ID number for the email sendNumber
ListIDThe list ID number for the list used in the sendNumber
BatchIDThe batch ID number for any batches used in the sendNumber
SubscriberIDThe subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.Number
SubscriberKeyThe subscriber key for the affected subscriberText
EventDateThe date the click took placeDate
DomainThe domain at which the click occurredText
URLThe URL for the link clicked. Dynamic elements are shown as code: www.example.com?id=%%SubID%%TextX
LinkNameThe link name assigned in the email sendTextX
LinkContentThe link content assigned in the email send. Dynamic elements are shown as result: www.example.com?id=12345TextX
IsUniqueWhether the event is unique or repeated
TriggererSendDefinitionObjectIDThe object ID for the triggered send definitionTextX
TriggeredSendCustomerKeyThe customer key for the triggered sendTextX

When working with _Click Data View:

  1. Use JobID, ListID, BatchID and SubscriberId or SubscriberKey to match various events from multiple Data Views like _Sent, _Open, _Bounce for a single engagement with a particular subscriber. Example: Query for Debugging Email Sends.
  2. Use _click.IsUnique = 1 in JOIN or WHERE to focus on first occurrences of each event. Example: Query for Debugging Email Sends.
  3. The most useful data here is the EventDate field that lets you know when the email was clicked (and whether that happened at all).
  4. You can significantly improve the value of that information by checking the LinkName and LinkContent fields to understand which links proved interesting for your customers.
  5. If you want to create a big report on link clicks you might want to use URL field instead of LinkContent as due to not showing final personalisation values it will be much easier to group clicks or even cleanly delete all AMPScript from them.
You Should Know

There might be some cases, where the click data is wrong - when you see that email as clicked, but in reality, the user didn't engage with it. It sometimes happens for B2B sends when your recipient's administrator uses bots that automatically check the emails for malicious or spammy content. You can recognise such things by checking the EventDate on _Open and _Click Data Views, as those bots will open the email and click all its links within few seconds. Something not possible for a real human.


_Bounce#

Stores information about your Email Bounces. Best place in Marketing Cloud to understand why your messages are not getting delivered.

NameDescriptionData TypeNullable
AccountIDYour parent account ID (MID) numberNumber
OYBAccountIDThe account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be NullNumberX
JobIDThe job ID number for the email sendNumber
ListIDThe list ID number for the list used in the sendNumber
BatchIDThe batch ID number for any batches used in the sendNumber
SubscriberIDThe subscriber ID for the affected subscriberNumber
SubscriberKeyThe subscriber key for the affected subscriberText
EventDateThe date the bounce took placeDate
IsUniqueWhether the event is unique or repeated - 1 for the first occurrence, 0 for subsequentBoolean
DomainThe domain at which the bounce occurredText
BounceCategoryIDThe ID number for the bounce categoryNumber
BounceCategoryThe category of the bounceTextX
BounceSubcategoryIDThe ID number for the bounce subcategoryNumberX
BounceSubcategoryThe subcategory of the bounceTextX
BounceTypeIDThe ID number for the bounce typeNumber
BounceTypeThe type of bounce that occurredTextX
SMTPBounceReasonThe reason for the bounce relayed by the mail systemTextX
SMTPMessageThe message regarding the bounce from the mail systemTextX
SMTPCodeThe error code for the bounce from the mail systemNumberX
TriggererSendDefinitionObjectIDThe object ID for the triggered send definitionNumberX
TriggeredSendCustomerKeyThe customer key for the triggered sendTextX

When working with _Bounce Data View:

  1. Use JobID, ListID, BatchID and SubscriberId or SubscriberKey to match various events from multiple Data Views like _Sent, _Open, _Click for a single engagement with a particular subscriber. Example: Query for Debugging Email Sends.
  2. Use _bounce.IsUnique = 1 in JOIN or WHERE to focus on first occurrences of each event. Example: Query for Debugging Email Sends.
  3. The Domain is useful for aggregating the bounce data to see any domain-specific problem with deliverability.
  4. SMTPBounceReason is the most helpful information when you want to understand what happened to your send. Be careful - this value can be configured to a custom string by the server owner. Some administrators are using this to inform you about a specific issue. Some are even faking the information to limit the emails coming to their servers from selected sources.
  5. SMTPCode is useful for getting additional data on the Bounce Reason. Watch especially for 541 and 554. In most cases, it means you are considered a spammer by the server or already blacklisted. I recommend you to create an Automation with Verification Activity to get an automated warning whenever there are more than a few occurrences. Triage immediately.
You Should Know

SMTPBounceReason field is nvarchar(max) SQL type and due to that might be longer than the 4000 characters limit for the Data Extension string length in Salesforce Marketing Cloud. If you want to save this data outside of Data View, be sure to use LEFT(SMTPBounceReason, 4000).


MobileConnect Data Views#

MobileConnect Data Views are unique, as most of them are no longer supported by Salesforce. But some still works.

You Should Know

Most of the MobileConnect data is assigned to Mobile Number, not Contact. It means that it might be shared by a few contacts (if multiple have the same mobile number set) or migrate between them (if the mobile number gets reassigned).

It shouldn't be a big problem in B2C environments, as most people will have their mobile number and keep it long. However, remember that unused numbers might be recycled after a few years by telecom and go back to the market and given to a new user.

Mobile number recycling is much more present in B2B scenario, where there is a bigger chance for shared mobile numbers and much faster recycling of the numbers. Many companies reuse the phone number of the employees that left.

When working with Mobile Number history of engagement and subscription, take this into consideration.

_MobileAddress#

_MobileAddress Data View is officially not supported by Salesforce. But it's too good not to use it. And there is no replacement for it. Why? Because Marketing Cloud still uses this Data View as a backend data source for the MobileConnect Demographics Data.

NameDescriptionData TypeNullable
_MobileIDMobileConnect Contact IDMobileConnectNumber
_ContactIDGlobal Contact ID (not Contact Key)Number
_MobileNumberMobile number for the subscriberText
_StatusStatus of the Contact. Possible values listed belowNumberX
_SourceSource of the Contact. Possible values listed belowNumberX
_SourceObjectIdID of the object if _Source = 10TextX
_PriorityIn case the contact uses multiple mobile numbers, the Priority field's value establishes the order in which to use those mobile numbers. _Priority = 1 is first in orderNumberX
_ChannelCommunication channel. Used to be Mobile, now not in useTextX
_CarrierIDNumeric code for the mobile carrier used by the contactNumber
_CountryCodeTwo letter country codeText
_CreatedDateDate the subscription was createdDate
_CreatedByPerson responsible for contact creationDate
_ModifiedByPerson responsible for contact modificationTextX
_CityContact's CityTextX
_StateContact's StateTextX
_ZipCodeContact's Zip CodeTextX
_FirstNameContact's First NameTextX
_LastNameContact's Last NameTextX
_UTCOffsetThe number of hours from which the user's time zone deviates from UTC. Defaults to 0NumberX
_IsHonorDSTWhether the user's time zone observers Daylight Savings Time or not. Defaults to falseBooleanX

_Status possible values:

  • 1 = Active
  • 2 = Inactive
  • 3 = Deleted

_Source possible values:

  • 0 = Unspecified
  • 1 = Webcollect
  • 2 = API
  • 3 = FTAF
  • 4 = Import
  • 5 = MoveCopy
  • 6 = Manual
  • 7 = SalesForce
  • 8 = Segmentation
  • 9 = GenericExtension
  • 10 = CustomObject
  • 11 = FacebookAPI
  • 12 = SmartCapture
  • 13 = MobileOptIn

When working with _MobileAddress Data View:

  1. There is no guarantee of how long it will work and how long the data will be correct. Be sure you are checking it periodically or - even better - have validation in your code.
  2. Rule of thumb when working with the MobileConnect is to use the official, supported solutions. Only when they cannot deliver - check whether _MobileAddress can help.
  3. Do not use User Interface editor in MobileConnect Demographics part of the Contact to edit information in this Data View. It is broken and overwrites all connected Mobile Numbers with a change made to just one. However, the changes are possible with SSJS - including ContactID and MobileNumber changes and limited merging. It is the most significant selling point of this Data View.
  4. _MobileAddress Data View is available in Data Designer within MobileConnect Attribute Group. It allows you to add custom fields to it that you can later leverage in sends.
  5. Whenever creating a MobileConnect Contact, be sure to provide the _MobileNumber with the dialling code prefix (without the + or 00). For example, Poland has +48 dialling code and nine-digit numbers, so the correct format is 48123456789. Double-check whether _CountryCode is appropriate two-letter code (for example, PL).
  6. The Contact may have multiple Mobile Numbers assigned, but it will receive communication only to the one with highest _Priority.
  7. Whenever you add a Mobile Number to a new Contact, for all other Contacts with this number it gets lower _Priority (SFMC assumes that it was transferred).
  8. _ContactID makes this Data View useful for cross-channel applications. New Data Views (_SMSSubscriptionLog and _SMSMessageTracking) have Subscriber Key information, but if the Mobile Number is shared, it won't provide the data.

_MobileSubscription#

_MobileSubscription Data View is officially unsupported by Salesforce. Marketing Cloud still fills it with data, but there is no guarantee of how long it will persist or whether it will be correct.

Worry not, there is a nearly identical Data View available and supported - _SMSSubscriptionLog.

The new Data View contains nearly all the same fields (sans _CreatedBy and _ModifiedBy) and even adds three more (LogDate, SubscriberKey and MovileSubscriptionID).

NameDescriptionData TypeNullable
_SubscriptionDefinitionIDThe unique ID of the subscription recordText
_MobileNumberMobile number for the subscriberText
_OptOutStatusIDWhether the subscriber has opted out of SMS messagesTextX
_OptOutMethodIDMethod the subscriber used to opt-out of SMS messagesTextX
_OptOutDateDate, the subscriber, opted out of SMS messagesDateX
_OptInStatusIDWhether the subscriber has opted-in to the SMS messagesText
_OptInMethodIDMethod the subscriber used to opt-in to the SMS messagesTextX
_OptInDateDate, the subscriber, opted-in to the SMS messagesDateX
_SourceThe subscription sourceTextX
_CreatedDateDate the subscription was createdDate
_CreatedByPerson responsible for subscription creationTextX
_ModifiedDateDate the subscription was modifiedDate
_ModifiedByPerson responsible for the modificationTextX

You shouldn't be working with _MobileSubscription Data View. Use _SMSSubscriptionLog instead.


_UndeliverableSMS#

_SubscriberSMS Data View is a legacy table. It is not supported, but you might have some records there. However, Marketing Cloud used it in pre-MobileConnect times, so I do not recommend using it for any projects.

NameDescriptionData TypeNullable
MobileNumberMobile number for the subscriberText
UndeliverableWhether the subscriber is eligible for future deliveriesBoolean
BounceCountNumber of times message was not deliveredNumber
FirstBounceDateDate of the first unsuccessful deliveryDate
HoldDateDate when the subscriber can be used in sends againDateX

You shouldn't be working with _SubscriberSMS Data View.


_SubscriberSMS#

_SubscriberSMS Data View is a legacy table. It is neither supported nor updated, and you won't have records there. Marketing Cloud used it in pre-MobileConnect times, so I do not recommend using it for any projects.

NameDescriptionData TypeNullable
SubscriberIDThe subscriber ID for the affected subscriberNumber
SubscriberKeyThe subscriber key for the affected subscriberText
MobileNumberMobile number for the subscriberPhone
TransactionalOptInWhether the subscriber has opted-in to Transactional SMS messagesBoolean
TransactionalOptInDateDate, the subscriber, opted-in to Transactional SMS messagesDate
TransactionalOptOutDateDate, the subscriber, opted out of Transactional SMS messagesDateX
MarketingOptInWhether the subscriber has opted-in to Marketing SMS messagesBoolean
MarketingOptInDateDate, the subscriber, opted-in to Marketing SMS messagesDate
MarketingOptOutDateDate, the subscriber, opted out of Marketing SMS messagesDateX
SMSCarrierNameName of the SMS Carrier used for deliveryTextX

You shouldn't be working with _SubscriberSMS Data View.


_SMSSubscriptionLog#

_SMSSubscriptionLog Data View stores full information about subscription status for each Mobile Number. It replaced unsupported _MobileSubscription Data View.

NameDescriptionData TypeNullable
LogDateThe date the subscription is loggedDateX
SubscriberKeyThe subscriber key for the affected subscriberText
MobileSubscriptionIDThe unique ID of the subscription recordNumber
SubscriptionDefinitionIDThe unique Keyword ID (GUID())Text
MobileNumberMobile number for the subscriberPhone
OptOutStatusIDWhether the subscriber has opted out of SMS messages. Possible values listed belowNumberX
OptOutMethodIDMethod the subscriber used to opt-out of SMS messages. Possible values listed belowNumberX
OptOutDateDate, the subscriber, opted out of SMS messagesDateX
OptInStatusIDWhether the subscriber has opted-in to the SMS message. Possible values listed belowNumber
OptInMethodIDMethod the subscriber used to opt-in to the SMS messages. Possible values listed belowNumberX
OptInDateDate, the subscriber, opted-in to the SMS messagesDateX
SourceThe subscription sourceNumberX
CreatedDateDate the subscription was createdDate
ModifiedDateDate the subscription was modifiedDate

OptOutStatusID possible values:

  • 0 = NotOptedOut
  • 1 = OptedOut
  • 10 = DeletingNotOptedOut
  • 11 = DeletingOptedOut

OptOutMethodID possible values (more can come in the future):

  • 4 = Import
  • 6 = Application
  • 14 = MobileOriginated
  • 15 = Suppression
  • NULL = Subscriber texted an Opt-Out Keyword

OptInStatusID possible values:

  • 0 = NotOptedIn = 0
  • 1 = OptInPending = 1
  • 2 = OptedIn = 2
  • 10 = DeletingNotOptedIn = 10
  • 11 = DeletingOptInPending = 11
  • 12 = DeletingOptedIn = 12

OptInMethodID possible values:

  • 0 = Unspecified
  • 1 = WebCollect
  • 2 = API
  • 3 = FTAF
  • 4 = Import
  • 5 = MoveCopy
  • 6 = Application
  • 7 = SalesForce
  • 8 = Segmentation
  • 9 = GenericExtension
  • 10 = CustomObject
  • 11 = RMM
  • 12 = Mobile Opt-In
  • 13 = DeviceRegistration

Source possible values:

  • 2 = API
  • 4 = Import
  • 6 = Application
  • 10 = CustomObject
  • 12 = MobileOptIn

When working with _SMSSubscriptionLog Data View:

  1. SFMC assigns subscription data to Mobile Number, not particular SubscriberKey or Contact. If you reassign the Phone Number to a different contact - Subscriptions will follow.
  2. SubscriberKey will be correctly populated if there is one (and only one) Subscriber with the same Mobile Number. Else, it will have either have No MobileAddress record has been found or this mobile number or Multiple subscriber keys found for this mobile number as a value.
  3. SubscriptionDefinitionID is a GUID, and there is no apparent connection to a specific Keyword. But there are two tricks.
    • JOIN _SMSMessageTracking Data View and use KeywordID and SharedKeyword columns to map the GUID to readable Keyword name. If you cannot find this information there, there is a second option:
    • Go to MobileConnect Contacts, open your test contact and make sure he is subscribed to all your Keywords. Click the Attributes tab and scroll to MobileConnect Data. Click on the Carrier ID value. It will open a MobileConnect Subscriptions view - the above Data View in User Interface format. If you check the Keyword column, you will see your MobileConnect Short or Long Code along with Keyword in a readable format. Write it down and click Edit. Now you will see the same data, but with Keyword GUID. With those two values, you can use a SQL CASE statement to convert this Data View to a more readable format.

_SMSMessageTracking#

NameDescriptionData TypeNullable
MobileMessageTrackingIDUnique Tracking ID - one for each SMS sentNumber
EIDEnterprise ID number for the senderNumberX
MIDMember ID (Business Unit ID)NumberX
MobileMobile number for the subscriberPhone
MessageIDUnique Mobile Message IDNumber
KeywordIDUnique identifier for the keyword (GUID)TextX
CodeIDUnique identifier for the SMS code (GUID)TextX
ConversationIDUnique identifier for the SMS conversationTextX
CampaignIDThe SMS campaign tied to the recordNumberX
SentIf the message was sentBoolean
DeliveredIf the message was deliveredBooleanX
UndeliveredWhether the message was delivered successfully or notBooleanX
UnsubIf the subscriber unsubscribed. Currently always null.BooleanX
OptInWhether the subscriber has opted-in to the SMS messages. Currently always null.BooleanX
OptOutWhether the subscriber has opted out of SMS messages. Currently always null.BooleanX
OutboundIf the message was outgoingBooleanX
InboundIf the message was incomingBooleanX
CreateDateTimeThe date and time the tracking record was createdDate
ModifiedDateTimeThe date and CST time the tracking record was modifiedDate
ActionDateTimeThe actual date/time the Marketing Cloud received a delivery or non-delivery event. Timestamps for delivery receipts are not indicative of when a message was received on a handset. The SMS industry does not support read receipts, and the data returned in this view are based on when a delivery receipt is returned to our system.Date
MessageTextThe text of the messageTextX
IsTestIf the message was a test messageNumberX
MobileMessageRecurrenceIDThe ID of the recurrence schedule for the message. Can be used for troubleshooting send issues.NumberX
ResponseToMobileMessageTrackingIDThe tracking ID of the response to the messageNumberX
IsValidPopulates to 1 for inbound message and 0 for Outbound messageNumberX
InvalidationCodeInvalidation code for the message. Currently always null.NumberX
SendIDThe send ID number for the SMS sendNumberX
SendSplitIDIf the message was split, the ID of the splitNumberX
SendSegmentIDThe ID of the segment tied to the messageNumberX
SendJobIDThe job ID for the SMS sendNumberX
SendGroupIDThe group ID for the SMS sendNumberX
SendPersonIDThe sendperson ID for the SMS sendNumberX
SubscriberIDThe subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber recordNumberX
SubscriberKeyThe subscriber key for the affected subscriberTextX
SMSStandardStatusCodeIdSFMC delivery status codeNumberX
DescriptionDetailed description of the status codeTextX
NameThe SMS Message or Journey Activity nameTextX
ShortCodeThe short code or long code used to send your messageTextX
SharedKeywordThe keyword used in your messageTextX
OrdinalRepresents the parts in a multi-part message. Represented in ascending order starting at 0.NumberX
FromNameFrom Name that an individual message was deployed with. Maximum length: 11 charactersTextX

When working with _SMSMessageTracking Data View:

  1. If you send one SMS to multiple Subscribers, you will have multiple rows of data with different MobileMessageTrackingID but same MessageID. Apart from MessageID you may also use the Name field that contains the asset name from MobileConnect.
  2. KeywordID and CodeID are GUIDs, but the same table also has the readable versions of those columns - ShortCode and SharedKeyword. ShortCode, despite the name, also displays Long Codes.
  3. The fields marked above as boolean (like Sent, Delivered, IsTest, Outbound) have the value in the form of a number: 0 = false, 1 = true. Some might also be null - for example, if a message was outbound, Outbound will have the value of 1, whereas Inbound will be null.
  4. MessageText will not only show you the text you sent in your SMS - it will also display it after personalisation is applied. On the one hand, it is great for debugging, as it allows you to check the final content the user received. On the other hand, you won't be able to assess personalisations used across your messaging.
  5. ResponseToMobileMessageTrackingID is an excellent way to check the customer response. You can make Self-Join between inbound ResponseToMobileMessageTrackingID and outbound MobileMessageTrackingID to output response data in the same line.
  6. Some fields in this Data View are filled in only under specific conditions:
    • CampaignID is populated only when an SMS is sent to Data Extension as Audience or send type is Send to Data Extension. SMS must be connected to a Campaign for the field to be populated.
    • SendID, SendJobID are populated only for SMS messages sent via Automation Studio's Send SMS Activity or when an SMS is sent to Data Extension as Audience.
    • SendSplitID, SendSegmentID, SendGroupID, SendPersonID are populated only when SMS is sent to Data Extension as Audience.
    • ConversationID is populated only when a conversation is created with a MO user using AMPScript CreateSMSConversation.
  7. Unsub, OptIn, OptOut are currently not populated at all. Data should be pulled from _SMSSubscriptionLog.
  8. Name field is perfect for connecting SMS data with _JourneyActivity Data View to have full picture of your Journey (JOIN ON _SMSMessageTracking.Name = _JourneyActivity.ActivityName).
  9. SMSStandardStatusCodeId and Description are great for understanding your SMS send's current status.

SMS Status Codes#

CodeStatusDefinition
1000QueuedToSfmcSendServiceMessage queued to internal send service.
1500QueueFailureToSfmcSendServiceMessage failed to queue to internal send service. Retry your send.
1501ValidationErrorInternal validation error. Retry your send.
2000DeliveredToAggregatorMessage delivered to the aggregator. The status will be updated when the delivery confirmation comes from the carrier or mobile device. For shared codes, this is the final status.
2500FailedToAggregatorMessage not delivered to the aggregator. Retry your send.
2501UnknownToAggregatorUnknown aggregator error.
2600ThrottledToAggregatorMessage not accepted by aggregator due to capacity issues. Send will be retried automatically.
3000EnrouteMessage is en route to carrier. Waiting on carrier confirmation.
3001SentToCarrierMessage sent to carrier. Waiting to be accepted by carrier.
3002AcceptedByCarrierMessage accepted by carrier. Waiting for delivery confirmation.
3400UnknownUnknown error
4000DeliveredMessage delivered to mobile device.
4500UndeliverableMessage not delivered to mobile device.
4501ExpiredMessage expired. Message exhausted the carrier retry process. Mobile device may be out of carrier range.
4502DeletedMessage deleted by the carrier.
4503RejectedMessage rejected. Carrier may have detected a loop or assumed that message is spam. This status can indicate an administrative or financial problem between the operator and the end-users.

Journey Data Views#

_JourneyActivity#

Stores information about Activities available in your Journeys. Must-have for joining Email data with Journey data.

NameDescriptionData TypeNullable
VersionIDThe unique identifier for the version of the JourneyText
ActivityIDThe unique identifier for the activity. There are one or more ActivityID's associated to a VersionID.Text
ActivityNameThe name of the activityTextX
ActivityExternalKeyThe external key associated with the activityText
JourneyActivityObjectIDUse this unique identifier to join to email tracking system Data Views to identify a Journey email's Triggered Send Definition.TextX
ActivityTypeThe type of activityTextX

When working with _JourneyActivity Data View:

  1. There are two key fields here: VersionID and JourneyActivityObjectID. JourneyActivityObjectID lets you match Journey Activity with specific Email Send. Just use TriggererSendDefinitionObjectID available on either _Job, _Sent, _Open, _Click or _Bounce Data View. It is handy when you later use VersionID field to JOIN _Journey Data View and with this connect specific Email engagement with particular Journey and its exact version. Fantastic for full scope reporting.
  2. With the help of _Journey Data View, you can use ActivityType field for an excellent little report created with SQL that will show which Journeys use which channels and what types of logic. For a more in-depth understanding of what happens in Journey, you would instead need to use Script Activity with REST API, but it is perfect for a glance on the multi-channel use.

_Journey#

Stores information about your Journeys, for when context matters.

NameDescriptionData TypeNullable
VersionIDThe unique identifier for the version of the JourneyText
JourneyIDThe unique identifier for the Journey. There are one or more VersionID's associated to a JourneyID.Text
JourneyNameThe name of the JourneyText
VersionNumberThe version number of the version of the JourneyNumber
CreatedDateThe date that the version of the Journey was createdDate
LastPublishedDateThe date that the version of the Journey was last publishedDateX
ModifiedDateThe date that the version of the Journey was last editedDate
JourneyStatusThe current running mode of the Journey. Possible values are Draft, Running, Finishing, and Stopped.Text

When working with _Journey Data View:

  1. VersionID field is perfect to JOIN with _JourneyActivity. It allows you to map multi-channel engagement to particular Journey (visible in JourneyName) and its specific version (as shown in VersionNumber).
  2. Use JourneyStatus to check what is the current Journey Status. It is fantastic for both the WHERE statement and a data point for engagement analysis. Think about scheduled Automation that finds all emails performing below expectations and checks which are in the Active Journeys. For all found it sends you a notification to check and improve. For awful results, you can even use this data in Script Activity to automatically stop the Journey with the SFMC REST API.
  3. The Date fields (CreatedDate, LastPublishedDate, ModifiedDate) are cool for automated cleanup of old Journeys, especially when paired with the _Sent data. For example, you can create monthly Automation that checks all Journeys that were last modified at least three months ago and based on the send volume or performance, stop them and notify you about that.

Custom Data Views#

On top of all built-in Data Views mentioned above there is also a paid option for a custom Data Views. As for the possibilities, the sky is the limit. Example? Lukas Lunow highlighted a fascinating one that solves a problem of knowing what is the current status of a Contact in a given Journey:

NameDescriptionData TypeNullable
JourneyVersionNumberThe version number of the version of the JourneyNumber
JourneyNameThe name of the JourneyText
JourneyStatusTells you whether the Journey is still ActiveText
ContactKeyThe subscriber key for the affected subscriberText
ContactIDGlobal Contact ID (not Contact Key)Number
ContactStatusTell you whether the contact is still in the Journey (Active, Exited or even more detailed like DidNotMeetEntryCriteria, ContactExitedByAPI)Text
EntryDateDate of Journey EntryDate
ExitDateDate of Journey Exit - including Goal / Exit Criteria. Null if still in Journey.Datex

Yes, you can create a workaround to know whether the Contact is in a given Journey using standard Data Extension and Update Contact Journey Activities on the beginning and end of every Journey. Not only it requires you to remember always to add those additional steps; it also blocks you from using Goals and Exit Criteria.

Custom Data View allow you to eat a cookie and have a cookie.

You can certainly come up with other use cases that could be solved if only you had one additional data point. Now you can.