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 is always knowing 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, Journeys, Email and SMS Channel. I plan to cover the rest of them in the future.

Be sure to check the excellent Data Views relationship diagram created by Zuzanna Jarczyńska or a bit more complex, but more in-depth System Tables relationship diagram created by Cameron Roberts. Those will for sure help you with multi-view JOINs.

Another useful link is System Data Views SQL Schema forked and updated from Sam Tylor. Ton of info about specific value lengths and ready-to-use snippets to prepare whole database structure for backup. Perfect match for MarketingThibs' System Data Views SSJS Cloning.

---

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.

_Subscribers Fields

Name	Description	Data Type	Nullable
SubscriberID	The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.	Number
DateUndeliverable	The date an email for the subscriber was returned as undeliverable	Date	X
DateJoined	The Date, the subscriber, joined your list	Date	X
DateUnsubscribed	The Date, the subscriber, unsubscribed from your list	Date	X
Domain	The domain of the subscriber	Text	X
EmailAddress	The subscriber's email address	Email
BounceCount	The total number of bounces accrued by the subscriber	Number
SubscriberKey	A potential alternate identifier for subscribers.	Text
SubscriberType	The subscriber type for the affected subscriber	Text
Status	The status of the subscriber: active, held, unsubscribed, or bounced.	Text	X
Locale	The locale code for the subscriber	Locale	X

_Subscribers SQL Query

SELECT
      sub.SubscriberID
    , sub.DateUndeliverable
    , sub.DateJoined
    , sub.DateUnsubscribed
    , sub.Domain
    , sub.EmailAddress
    , sub.BounceCount
    , sub.SubscriberKey
    , sub.SubscriberType
    , sub.Status
    , sub.Locale
FROM _Subscribers AS sub

When working with _Subscribers Data View:

1. If you are using the query in the child Business Unit, use Ent. prefix, as the data is stored on Parent BU level.
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 a 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.
5. This Data View, along with _EnterpriseAttribute, _BusinessUnitUnsubscribes and _ListSubscribers, does not have six-month data retention.

---

_EnterpriseAttribute

Stores information about your Profile and Preference Attributes from Email Studio.

_EnterpriseAttribute Fields

Name	Description	Data Type	Nullable
_SubscriberID	The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record stored.	Number

_EnterpriseAttribute SQL Query

SELECT
      ea._SubscriberID                     AS SubscriberID
    , ea.ProfileAttributeWithoutSpaces
    , ea.[Profile Attribute With Spaces]   AS ProfileAttributeWithSpace
    , ea.PreferenceAttributeWithoutSpaces
    , ea.[Preference Attribute With Spaces]   AS PreferenceAttributeWithSpace
FROM _EnterpriseAttribute AS ea

When working with _EnterpriseAttribute Data View:

1. If you are using the query in the child Business Unit, always use ENT. prefix as it is enterprise-level only Data View.
2. When querying or joining _EnterpriseAttribute, always include the underscore before SubscriberID - this is the only Data View that requires it.
3. Despite the 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 the Email Address of your Subscriber, query the _Subscribers Data View.
4. Unfortunately, the default Preference Attribute HTML Email is not accessible via query.
5. _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.
6. This Data View, along with _Subscribers, _BusinessUnitUnsubscribes and _ListSubscribers , does not have six-month data retention.

---

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.

_Job Fields

Name	Description	Data Type	Nullable
JobID	The job ID number for the email send	Number
EmailID	The email ID for the job	Number	X
AccountID	The ID number for the account that performed the job	Number	X
AccountUserID	The ID number for the account user that performed the job	Number	X
FromName	The from name in the email send	Text	X
FromEmail	The from email address in the email send	Email	X
SchedTime	The time the job was scheduled	Date	X
PickupTime	The time the Marketing Cloud application started the job	Date	X
DeliveredTime	The time the email was delivered	Date	X
EventID	The ID for the job event	Text	X
IsMultipart	Whether the job was sent as multi-part MIME or not	Boolean
JobType	The type of job	Text	X
JobStatus	The status of the job	Text	X
ModifiedBy	If modified, the user who modified the job	Number	X
ModifiedDate	The date the job was modified	Date	X
EmailName	The name of the email sent by the job	Text	X
EmailSubject	The subject of the email send	Text	X
IsWrapped	Whether the links in the email were wrapped for tracking	Boolean
TestEmailAddr	The test email address used in the job	Email	X
Category	The job category	Text
BccEmail	Any email address send in the BCC field	Email	X
OriginalSchedTime	The initially scheduled time for the job	Date	X
CreatedDate	The date the job was created	Date
CharacterSet	The character set used in the job	Text	X
IPAddress	This value will always be a null value.	Text	X
SalesForceTotalSubscriberCount	The total number of Salesforce subscribers included in the job	Number
SalesForceErrorSubscriberCount	The total number of Salesforce subscribers included in the job that received errors	Number
SendType	The type of send used in the job	Text
DynamicEmailSubject	The dynamic email subject included in the job	Text	X
SuppressTracking	If tracking information for this job was suppressed	Boolean
SendClassificationType	The send classification type for the job	Text	X
SendClassification	The send classification for the job	Text	X
ResolveLinksWithCurrentData	If the job resolved links with current data	Boolean
EmailSendDefinition	The email send definition used in the job	Text	X
DeduplicateByEmail	Whether the email addresses in the job are used to deduplicate subscribers	Boolean
TriggererSendDefinitionObjectID	The object ID for the triggered send definition	Number	X
TriggeredSendCustomerKey	The customer key for the triggered send	Text	X

_Job SQL Query

SELECT
      job.JobID
    , job.EmailID
    , job.AccountID
    , job.AccountUserID
    , job.FromName
    , job.FromEmail
    , job.SchedTime
    , job.PickupTime
    , job.DeliveredTime
    , job.EventID
    , job.IsMultipart
    , job.JobType
    , job.JobStatus
    , job.ModifiedBy
    , job.ModifiedDate
    , job.EmailName
    , job.EmailSubject
    , job.IsWrapped
    , job.TestEmailAddr
    , job.Category
    , job.BccEmail
    , job.OriginalSchedTime
    , job.CreatedDate
    , job.CharacterSet
    , job.IPAddress
    , job.SalesForceTotalSubscriberCount
    , job.SalesForceErrorSubscriberCount
    , job.SendType
    , job.DynamicEmailSubject
    , job.SuppressTracking
    , job.SendClassificationType
    , job.SendClassification
    , job.ResolveLinksWithCurrentData
    , job.EmailSendDefinition
    , job.DeduplicateByEmail
    , job.TriggererSendDefinitionObjectID
    , job.TriggeredSendCustomerKey
FROM _Job AS job

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, _Bounce Data Views and are worth checking for reporting needs.
3. If you are performing A/B tests or want to analyse the subject line's 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 among other Email-focused Data Views, as it shows data limited to a specific 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.

_Sent Fields

Name	Description	Data Type	Nullable
AccountID	Your parent account ID (MID) number	Number
OYBAccountID	The account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be Null	Number	X
JobID	The job ID number for the email send	Number
ListID	The list ID number for the list used in the send	Number
BatchID	The batch ID number for any batches used in the send	Number
SubscriberID	The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
EventDate	The date the send took place	Date
Domain	The domain at which the send occurred	Text
TriggererSendDefinitionObjectID	The object ID for the triggered send definition	Text	X
TriggeredSendCustomerKey	The customer key for the triggered send	Text	X

_Sent SQL Query

SELECT
      sent.AccountID
    , sent.OYBAccountID
    , sent.JobID
    , sent.ListID
    , sent.BatchID
    , sent.SubscriberID
    , sent.SubscriberKey
    , sent.EventDate
    , sent.Domain
    , sent.TriggererSendDefinitionObjectID
    , sent.TriggeredSendCustomerKey
FROM _Sent AS sent

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 valuable 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 enhanced Send Log, applied 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.

_Open Fields

Name	Description	Data Type	Nullable
AccountID	Your parent account ID (MID) number	Number
OYBAccountID	The account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be Null	Number	X
JobID	The job ID number for the email send	Number
ListID	The list ID number for the list used in the send	Number
BatchID	The batch ID number for any batches used in the send	Number
SubscriberID	The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
EventDate	The date the open took place	Date
Domain	The domain at which the open occurred	Text
IsUnique	Whether the event is unique or repeated	Boolean	X
TriggererSendDefinitionObjectID	The object ID for the triggered send definition	Text	X
TriggeredSendCustomerKey	The customer key for the triggered send	Text	X

_Open SQL Query

SELECT
      open.AccountID
    , open.OYBAccountID
    , open.JobID
    , open.ListID
    , open.BatchID
    , open.SubscriberID
    , open.SubscriberKey
    , open.EventDate
    , open.Domain
    , open.IsUnique
    , open.TriggererSendDefinitionObjectID
    , open.TriggeredSendCustomerKey
FROM _Open AS open

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 the the first occurence of each event. Example: Query for Debugging Email Sends.
3. The most valuable 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, the 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 engagement 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 the download of this 1px wide image as proof that the recipient opened the email. And in most cases, it is correct. 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. 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 a user has 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 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 a second. Something not possible for an actual human.

---

_Click

_Click Fields

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

Name	Description	Data Type	Nullable
AccountID	Your parent account ID (MID) number	Number
OYBAccountID	The account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be Null	Number	X
JobID	The job ID number for the email send	Number
ListID	The list ID number for the list used in the send	Number
BatchID	The batch ID number for any batches used in the send	Number
SubscriberID	The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record.	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
EventDate	The date the click took place	Date
Domain	The domain at which the click occurred	Text
URL	The URL for the link clicked. Dynamic elements are shown as code: www.example.com?id=%%SubID%%	Text	X
LinkName	The link name assigned in the email send	Text	X
LinkContent	The link content assigned in the email send. Dynamic elements are shown as result: www.example.com?id=12345	Text	X
IsUnique	Whether the event is unique or repeated
TriggererSendDefinitionObjectID	The object ID for the triggered send definition	Text	X
TriggeredSendCustomerKey	The customer key for the triggered send	Text	X

_Click SQL Query

SELECT
      click.AccountID
    , click.OYBAccountID
    , click.JobID
    , click.ListID
    , click.BatchID
    , click.SubscriberID
    , click.SubscriberKey
    , click.EventDate
    , click.Domain
    , click.URL
    , click.LinkName
    , click.LinkContent
    , click.IsUnique
    , click.TriggererSendDefinitionObjectID
    , click.TriggeredSendCustomerKey
FROM _Click AS click

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 the first occurence of each event. Example: Query for Debugging Email Sends.
3. The most valuable 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. Suppose you want to create an extensive report on link clicks. In that case, you might want to use the URL field instead of LinkContent due to the former 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 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 a second. Something not possible for a actual human.

---

_Bounce

_Bounce Fields

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

This Data View, along with _Complaint, should be monitored regularly to assess the health of your email marketing.

Name	Description	Data Type	Nullable
AccountID	Your parent account ID (MID) number	Number
OYBAccountID	The account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be Null	Number	X
JobID	The job ID number for the email send	Number
ListID	The list ID number for the list used in the send	Number
BatchID	The batch ID number for any batches used in the send	Number
SubscriberID	The subscriber ID for the affected subscriber	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
EventDate	The date the bounce took place	Date
IsUnique	Whether the event is unique or repeated - 1 for the first occurrence, 0 for subsequent	Boolean
Domain	The domain at which the bounce occurred	Text
BounceCategoryID	The ID number for the bounce category	Number
BounceCategory	The category of the bounce	Text	X
BounceSubcategoryID	The ID number for the bounce subcategory	Number	X
BounceSubcategory	The subcategory of the bounce	Text	X
BounceTypeID	The ID number for the bounce type	Number
BounceType	The type of bounce that occurred	Text	X
SMTPBounceReason	The reason for the bounce relayed by the mail system	Text	X
SMTPMessage	The message regarding the bounce from the mail system	Text	X
SMTPCode	The error code for the bounce from the mail system	Number	X
TriggererSendDefinitionObjectID	The object ID for the triggered send definition	Text	X
TriggeredSendCustomerKey	The customer key for the triggered send	Text	X

_Bounce SQL Query

SELECT
      bounce.AccountID
    , bounce.OYBAccountID
    , bounce.JobID
    , bounce.ListID
    , bounce.BatchID
    , bounce.SubscriberID
    , bounce.SubscriberKey
    , bounce.EventDate
    , bounce.IsUnique
    , bounce.Domain
    , bounce.BounceCategoryID
    , bounce.BounceCategory
    , bounce.BounceSubcategoryID
    , bounce.BounceSubcategory
    , bounce.BounceTypeID
    , bounce.BounceType
    , bounce.SMTPBounceReason
    , bounce.SMTPMessage
    , bounce.SMTPCode
    , bounce.TriggererSendDefinitionObjectID
    , bounce.TriggeredSendCustomerKey
FROM _Bounce AS bounce

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 the first occurence of each event. Example: Query for Debugging Email Sends.
3. The Domain helps aggregate 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 blocklisted. 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).

_Complaint

_Complaint Data View stores Email Service Providers Feedback Loop (FBL) data. It means that if someone clicks the Report Spam button for your email and there is FBL in place, this information will be added to the Data View. It will also result in an unsubscribe event and be visible in the _Unsubscribe.

Currently, Salesforce Marketing Cloud supports Feedback Loops for Bluetie (Excite), Comcast, Cox, Fastmail, Microsoft Hotmail, Italia Online, La Poste, Liberty Global, Locaweb, Mail.ru, OpenSRS (Tucows), Rackspace (Mailtrust), Seznam, Synacor, Telenor, Telstra, Terra, UOL (Brazil), USA.net, XS4ALL and Yandex.

You can additionally register for the Yahoo Feedback Loop. Gmail also has a Feedback Loop system, but it works differently and won't be in _Complaint Data View.

This Data View, along with _Bounce, should be monitored regularly to assess the health of your email marketing.

_Complaint Fields

Name	Description	Data Type	Nullable
AccountID	Your parent account ID (MID) number	Number
OYBAccountID	The account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be Null	Number	X
JobID	The job ID number for the email send	Number
ListID	The list ID number for the list used in the send	Number
BatchID	The batch ID number for any batches used in the send	Number
SubscriberID	The subscriber ID for the affected subscriber	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
EventDate	The date the send took place	Date
IsUnique	Whether the event is unique or repeated - 1 for the first occurrence, 0 for subsequent	Boolean
Domain	The domain at which the send occurred	Text

_Complaint SQL Query

SELECT
      complaint.AccountID
    , complaint.OYBAccountID
    , complaint.JobID
    , complaint.ListID
    , complaint.BatchID
    , complaint.SubscriberID
    , complaint.SubscriberKey
    , complaint.EventDate
    , complaint.IsUnique
    , complaint.Domain
FROM _Complaint AS complaint

When working with _Complaint 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.
2. Use _complaint.IsUnique = 1 in JOIN or WHERE to focus on the first occurence of each event.
3. The Domain helps aggregate the Complaint data to see any domain-specific problem with targeting your communication.

---

Subscription Data Views

Subscription management in Salesforce Marketing Cloud is complex. Very complex.

It starts with four levels of unsubscriptions:

1. List unsubscribe = no more sends from a particular list or publication list.
2. Business Unit unsubscribe = no more sends from a particular business unit.
3. Master unsubscribe = no more sends from your Marketing Cloud.
4. Global unsubscribe = no more sends from Marketing Cloud. Any, globally.

And it gets even more interesting when you consider that triggering specific unsubscription level depends on email configuration, Email Studio configuration, preference center configuration and subscriber's behaviour.

Oh, and there is no out-of-the-box unsubscription from Data Extensions, which are currently the most popular segmentation and targeting tool.

To give us at least a chance to make sense of that mess, Salesforce created multiple System Data Views related to (un)subscriptions.

You Should Know

Spam Complaint and List-Unsubscribe Header are pretty particular unsubscription use cases. They are added to _Unsubscribe and _BusinessUnitUnsubscribe as unsubscriptions from a list used in send (for example, ID of Publication List selected for the send). In reality, Marketing Cloud will perform automatic unsubscribe from the All Subscriber list (depending on Email Studio settings - Child BU's or Enterprise one), not the list mentioned in ListID.

As a result, when querying, you will see the list-specific unsubscribe that doesn't result in unsubscription from the list but instead blocks all commercial communication as a Business Unit or Master unsubscribe.

Custom solution (Automation with Query and Script Activities) will be necessary to perform the list-specific unsubscribe.

_Unsubscribe

_Unsubscribe Data View stores data about unsubscribe events. The wording here is crucial, as it explains the difference from _Subscribers Data View unsubsrciption counts.

In _Subscribers, you can check what is the current subscription status for the All Subscribers list. In _Unsubscribe, you see the unsubscription events happening from List-Unsubscribe Header, LogUnsubEvent API call, Reply Mail Management responses, Complaints and out-of-the-box Preference Center - all linked to a specific _Job. It is also not limited to All Subscribers but also covers unsubscribes from other types of lists.

It means that status changes like Import Activity, direct API change, manual change in the Email Studio (not Job-specific) will be reflected in the _Subscribers Data View, but not in the _Unsubscribe. On the other hand, UnsubEvents does not necessarily change the Status in the _Subscribers. For example, taking care of List-Unsubscribe Header clicks require dedicated Automation to get this data from _Unsubscribe Data View and push it to other parts of your consent data model.

This Data View stores data only for six-months, so retention-less _Subscribers Data View might show more unsubscribed records if you are not cleaning them from SFMC regularly.

All of this makes _Unsubscribe a much better source of knowledge about unsubscription.

_Unsubscribe Fields

Name	Description	Data Type	Nullable
AccountID	Your account ID number	Number
OYBAccountID	The account ID (MID) number of the child Business Unit that owns the data. If queried on parent Business Unit, it will be Null	Number	X
JobID	The job ID number for the email send	Number
ListID	The list ID number for the list used in the send	Number
BatchID	The batch ID number for any batches used in the send	Number
SubscriberID	The subscriber ID for the affected subscriber	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
EventDate	The date the unsubscribe took place	Date
IsUnique	Whether the event is unique or repeated - 1 for the first occurrence, 0 for subsequent	Boolean
Domain	The domain at which the unsubscribe occurred	Text

_Unsubscribe SQL Query

SELECT
      unsub.AccountID
    , unsub.OYBAccountID
    , unsub.JobID
    , unsub.ListID
    , unsub.BatchID
    , unsub.SubscriberID
    , unsub.SubscriberKey
    , unsub.EventDate
    , unsub.IsUnique
    , unsub.Domain
FROM _Unsubscribe AS unsub

When working with _Unsubscribe Data View:

1. Use JobID, ListID, BatchID and SubscriberId or SubscriberKey to match various events from multiple Data Views like _Job and _Subscribers to check unsubscription context.
2. Use _unsubscribe.IsUnique = 1 in JOIN or WHERE to focus on the first occurence of each event.
3. If you query this Data View on the child Business Unit, it will return only unsubscribe events from that specific BU.
4. The most valuable data here are:
   • EventDate field that lets you know when the unsubscription happened
   • ListID field that tells you which list is impacted by the unsubscription

You Should Know

If the subscriber clicks "Unsubscribe from All" on the out-of-the-box unsubscription center, it will perform Master Unsubscribe from all Lists. As it is a single event, it will be stored as a single record in the _Unsubscribe Data View with random ListID assigned to it. For a complete picture, you will need to check _ListSubscribers Data View.

---

_BusinessUnitUnsubscribes

_Subscribers Data View might give you information about the current state of subscriptions on the All Subscribers list. But if in Email Studio you configure Business Unit Level unsubscribes, this information will not be helpful, as the data stored there will not reflect the subscription status in a specific Business Unit.

Here comes the fresh _BusinessUnitUnsubscribes Data View. It shows you the only unsubscribed contacts for each Business Unit. If the contact resubscribes - it will disappear from this Data View.

Despite _BusinessUnitUnsubscribes Data View focus on Business Units, you can query it only from the Enterprise level (on Parent BU or using Ent. prefix).

_BusinessUnitUnsubscribes Fields

Name	Description	Data Type	Nullable
BusinessUnitID	The Account ID for the Business Unit	Number
SubscriberID	The subscriber ID for the affected subscriber	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
UnsubDateUTC	The date the subscriber unsubscribed on the BU.	Date	X
UnsubReason	The reason listed is a custom value configured by your administrator.	Text	X

_BusinessUnitUnsubscribes SQL Query

SELECT
      buUnsub.BusinessUnitID
    , buUnsub.SubscriberID
    , buUnsub.SubscriberKey
    , buUnsub.UnsubDateUTC
    , buUnsub.UnsubReason
FROM _BusinessUnitUnsubscribes AS buUnsub

UnsubReason possible values:

• Spam Complaint
• Unsubscribed from list details on subscriber properties dialog.
• Unsubscribed by Salesforce Marketing Cloud RMM service based on subscriber Leave/Reply email.
• Unsubscribed by subscriber edit API call.
• Unsubscribed from the Subscription Center
• Unsubscribed via Import

When working with _BusinessUnitUnsubscribes Data View:

1. Remember that it stores only currently unsubscribed contacts. Not unsubscribe event data (that's in _Unsubscribe) and not other statuses (like bounced, held). It will also not show unsubscribed users after they resubscribed or got deleted.
2. SubscriberID or SubscriberKey along with UnsubDateUTC might fuzzy match the data in this data view with _Unsubscribe.
3. UnsubDateUTC, as name suggests, is in UTC instead of the SFMC-standard UTC-6. Remember to use DATEADD to normalize it before comparing to other system dates.
4. The real treat in this data view (apart from finally having an easy option to see Business Unit unsubscribes) is the UnsubReason field that can tell you more about the unsubscribe.
5. This Data View, along with _Subscribers, _EnterpriseAttribute and _ListSubscribers, does not have six-month data retention.

You Should Know

UnsubDateUTC not only uses a different timezone from the rest of the system dates. It differs by a few milliseconds from _Unsubscribe EventDate. When querying (SFMC doesn't show seconds for date fields), you won't see it, but the comparison is using the full datetime value.

Because of it, you cannot match it out-of-the-box. DATEDIFF is here to save the day:

DATEDIFF will allow you to compare despite the difference

DATEDIFF(MINUTE, _Unsubscribe.EventDate, DATEADD(HOUR, -6, _BusinessUnitUnsubscribes.UnsubDateUTC)) < 1

---

_ListSubscribers

_ListSubscribers Data View stores data about the current status of the subscription. It might sound similar to _Subscribers and _BusinessUnitUnsubscribes, but there are significant differences:

• _Subscribers has data about All Subscribers list only. _ListSubscribers shows status for all Lists available in Salesforce Marketing Cloud.
• _BusinessUnitUnsubscribes has data about contacts unsubscribed from All Subscribers on every Business Unit. _ListSubscribers doesn't show Business Unit information, but it covers all Lists and all Statuses.

_ListSubscribers Fields

Name	Description	Data Type	Nullable
AddedBy	The ID of the user who ran the process that added the subscriber (might also be 0 or -1000)	Number
AddMethod	The method by which the subscriber was added	Text
CreatedDate	The date the subscriber was added to the specific list	Date	X
DateUnsubscribed	The date the subscriber unsubscribed	Date	X
EmailAddress	The subscriber's email address	Text	X
ListID	The list ID number for the list used in the send	Number	X
ListName	The name of the list containing the subscribers	Text	X
ListType	Shows whether the type is list or group	Text
Status	The status of the subscriber: active, held, unsubscribed, or bounced	Text	X
SubscriberID	The subscriber ID for the affected subscriber	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
SubscriberType	The type of subscriber (it will be ExactTarget)	Text	X

_ListSubscribers SQL Query

SELECT
      listSub.AddedBy
    , listSub.AddMethod
    , listSub.CreatedDate
    , listSub.DateUnsubscribed
    , listSub.EmailAddress
    , listSub.ListID
    , listSub.ListName
    , listSub.ListType
    , listSub.Status
    , listSub.SubscriberID
    , listSub.SubscriberKey
    , listSub.SubscriberType
FROM _ListSubscribers AS listSub

AddMethod possible values:

• API
• Imported
• WebApplication
• DataExtensionSend
• Unknown

ListType possible values:

• Publication list
• Suppression list
• List
• Group
• FTAF list
• Unknown

When working with _ListSubscribers Data View:

1. Thanks to having ListID, ListName and ListType fields, it allows you to make sense of the ListID field available in all other engagement-based System Data Views.
2. This Data View, along with _Subscribers, _EnterpriseAttribute and _BusinessUnitUnsubscribes, does not have six-month data retention.
3. You can only query Lists available on the current Business Unit. It's not possible to check Child's List subscription status from the Parent BU. The exception is the All Subscribers list that is shared across all Business Units.

---

MobileConnect Data Views

MobileConnect Data Views are unique, as most of them are no longer supported by Salesforce. But some still works. All those data views show data limited to a specific Business Unit.

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 the 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.

_MobileAddress Fields

Name	Description	Data Type	Nullable
_MobileID	MobileConnect Contact ID	Number	X
_ContactID	Global Contact ID (not Contact Key)	Number
_MobileNumber	Mobile number for the subscriber	Text
_Status	Status of the Contact. Possible values listed below	Number	X
_Source	Source of the Contact. Possible values listed below	Number	X
_SourceObjectId	ID of the object if _Source = 10	Text	X
_Priority	In 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 order	Number	X
_Channel	Communication channel. Used to be Mobile, now not in use	Text	X
_CarrierID	Numeric code for the mobile carrier used by the contact	Number
_CountryCode	Two letter country code	Text
_CreatedDate	Date the subscription was created	Date
_CreatedBy	Person responsible for contact creation	Date
_ModifiedBy	Person responsible for contact modification	Text	X
_City	Contact's City	Text	X
_State	Contact's State	Text	X
_ZipCode	Contact's Zip Code	Text	X
_FirstName	Contact's First Name	Text	X
_LastName	Contact's Last Name	Text	X
_UTCOffset	The number of hours from which the user's time zone deviates from UTC. Defaults to 0	Number	X
_IsHonorDST	Whether the user's time zone observers Daylight Savings Time or not. Defaults to false	Boolean	X

_MobileAddress SQL Query

SELECT
      mobileAddress._MobileID         AS MobileID
    , mobileAddress._ContactID        AS ContactID
    , mobileAddress._MobileNumber     AS MobileNumber
    , mobileAddress._Status           AS Status
    , mobileAddress._Source           AS Source
    , mobileAddress._SourceObjectId   AS SourceObjectId
    , mobileAddress._Priority         AS Priority
    , mobileAddress._Channel          AS Channel
    , mobileAddress._CarrierID        AS CarrierID
    , mobileAddress._CountryCode      AS CountryCode
    , mobileAddress._CreatedDate      AS CreatedDate
    , mobileAddress._CreatedBy        AS CreatedBy
    , mobileAddress._ModifiedBy       AS ModifiedBy
    , mobileAddress._City             AS City
    , mobileAddress._State            AS State
    , mobileAddress._ZipCode          AS ZipCode
    , mobileAddress._FirstName        AS FirstName
    , mobileAddress._LastName         AS LastName
    , mobileAddress._UTCOffset        AS UTCOffset
    , mobileAddress._IsHonorDST       AS IsHonorDST
FROM _MobileAddress AS mobileAddress

_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. The rule of thumb when working with MobileConnect is to use the official, supported solutions. Only when they cannot deliver - check whether _MobileAddress can help.
3. Do not use the 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 a +48 dialling code and nine-digit numbers, so the correct format is 48123456789. Double-check whether _CountryCode is an 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 the 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).

_MobileSubscription Fields

Name	Description	Data Type	Nullable
_SubscriptionDefinitionID	The unique ID of the subscription record	Text
_MobileNumber	Mobile number for the subscriber	Text
_OptOutStatusID	Whether the subscriber has opted out of SMS messages	Text	X
_OptOutMethodID	Method the subscriber used to opt-out of SMS messages	Text	X
_OptOutDate	Date, the subscriber, opted out of SMS messages	Date	X
_OptInStatusID	Whether the subscriber has opted-in to the SMS messages	Text
_OptInMethodID	Method the subscriber used to opt-in to the SMS messages	Text	X
_OptInDate	Date, the subscriber, opted-in to the SMS messages	Date	X
_Source	The subscription source	Text	X
_CreatedDate	Date the subscription was created	Date
_CreatedBy	Person responsible for subscription creation	Text	X
_ModifiedDate	Date the subscription was modified	Date
_ModifiedBy	Person responsible for the modification	Text	X

_MobileSubscription SQL Query

SELECT
      mobileSub._SubscriptionDefinitionID AS SubscriptionDefinitionID
    , mobileSub._MobileNumber             AS MobileNumber
    , mobileSub._OptOutStatusID           AS OptOutStatusID
    , mobileSub._OptOutMethodID           AS OptOutMethodID
    , mobileSub._OptOutDate               AS OptOutDate
    , mobileSub._OptInStatusID            AS OptInStatusID
    , mobileSub._OptInMethodID            AS OptInMethodID
    , mobileSub._OptInDate                AS OptInDate
    , mobileSub._Source                   AS Source
    , mobileSub._CreatedDate              AS CreatedDate
    , mobileSub._CreatedBy                AS CreatedBy
    , mobileSub._ModifiedDate             AS ModifiedDate
    , mobileSub._ModifiedBy               AS ModifiedBy
FROM _MobileSubscription AS mobileSub

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

---

_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.

_SubscriberSMS Fields

Name	Description	Data Type	Nullable
SubscriberID	The subscriber ID for the affected subscriber	Number
SubscriberKey	The subscriber key for the affected subscriber	Text
MobileNumber	Mobile number for the subscriber	Phone
TransactionalOptIn	Whether the subscriber has opted-in to Transactional SMS messages	Boolean
TransactionalOptInDate	Date, the subscriber, opted-in to Transactional SMS messages	Date
TransactionalOptOutDate	Date, the subscriber, opted out of Transactional SMS messages	Date	X
MarketingOptIn	Whether the subscriber has opted-in to Marketing SMS messages	Boolean
MarketingOptInDate	Date, the subscriber, opted-in to Marketing SMS messages	Date
MarketingOptOutDate	Date, the subscriber, opted out of Marketing SMS messages	Date	X
SMSCarrierName	Name of the SMS Carrier used for delivery	Text	X

_SubscriberSMS SQL Query

SELECT
      smsSub.SubscriberID
    , smsSub.SubscriberKey
    , smsSub.MobileNumber
    , smsSub.TransactionalOptIn
    , smsSub.TransactionalOptInDate
    , smsSub.TransactionalOptOutDate
    , smsSub.MarketingOptIn
    , smsSub.MarketingOptInDate
    , smsSub.MarketingOptOutDate
    , smsSub.SMSCarrierName
FROM _SubscriberSMS AS smsSub

You shouldn't be working with _SubscriberSMS Data View.

---

_SMSSubscriptionLog

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

_SMSSubscriptionLog Fields

Name	Description	Data Type	Nullable
LogDate	The date the subscription is logged	Date	X
SubscriberKey	The subscriber key for the affected subscriber	Text
MobileSubscriptionID	The unique ID of the subscription record	Number
SubscriptionDefinitionID	The unique Keyword ID (GUID())	Text
MobileNumber	Mobile number for the subscriber	Phone
OptOutStatusID	Whether the subscriber has opted out of SMS messages. Possible values listed below	Number	X
OptOutMethodID	Method the subscriber used to opt-out of SMS messages. Possible values listed below	Number	X
OptOutDate	Date, the subscriber, opted out of SMS messages	Date	X
OptInStatusID	Whether the subscriber has opted-in to the SMS message. Possible values listed below	Number
OptInMethodID	Method the subscriber used to opt-in to the SMS messages. Possible values listed below	Number	X
OptInDate	Date, the subscriber, opted-in to the SMS messages	Date	X
Source	The subscription source	Number	X
CreatedDate	Date the subscription was created	Date
ModifiedDate	Date the subscription was modified	Date

_SMSSubscriptionLog SQL Query

SELECT
      smsSubLog.LogDate
    , smsSubLog.SubscriberKey
    , smsSubLog.MobileSubscriptionID
    , smsSubLog.SubscriptionDefinitionID
    , smsSubLog.MobileNumber
    , smsSubLog.OptOutStatusID
    , smsSubLog.OptOutMethodID
    , smsSubLog.OptOutDate
    , smsSubLog.OptInStatusID
    , smsSubLog.OptInMethodID
    , smsSubLog.OptInDate
    , smsSubLog.Source
    , smsSubLog.CreatedDate
    , smsSubLog.ModifiedDate
FROM _SMSSubscriptionLog AS smsSubLog

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
• 1 = OptInPending
• 2 = OptedIn
• 10 = DeletingNotOptedIn
• 11 = DeletingOptInPending
• 12 = DeletingOptedIn

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. Otherwise, it will have either have a 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

_SMSMessageTracking Fields

Name	Description	Data Type	Nullable
MobileMessageTrackingID	Unique Tracking ID - one for each SMS sent	Number
EID	Enterprise ID number for the sender	Number	X
MID	Member ID (Business Unit ID)	Number	X
Mobile	Mobile number for the subscriber	Phone
MessageID	Unique Mobile Message ID	Number
KeywordID	Unique identifier for the keyword (GUID)	Text	X
CodeID	Unique identifier for the SMS code (GUID)	Text	X
ConversationID	Unique identifier for the SMS conversation	Text	X
CampaignID	The SMS campaign tied to the record	Number	X
Sent	If the message was sent	Boolean
Delivered	If the message was delivered	Boolean	X
Undelivered	Whether the message was delivered successfully or not	Boolean	X
Unsub	If the subscriber unsubscribed. Currently always null.	Boolean	X
OptIn	Whether the subscriber has opted-in to the SMS messages. Currently always null.	Boolean	X
OptOut	Whether the subscriber has opted out of SMS messages. Currently always null.	Boolean	X
Outbound	If the message was outgoing	Boolean	X
Inbound	If the message was incoming	Boolean	X
CreateDateTime	The date and time the tracking record was created	Date
ModifiedDateTime	The date and CST time the tracking record was modified	Date
ActionDateTime	The 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
MessageText	The text of the message	Text	X
IsTest	If the message was a test message	Number	X
MobileMessageRecurrenceID	The ID of the recurrence schedule for the message. Can be used for troubleshooting send issues.	Number	X
ResponseToMobileMessageTrackingID	The tracking ID of the response to the message	Number	X
IsValid	Populates to 1 for inbound message and 0 for Outbound message	Number	X
InvalidationCode	Invalidation code for the message. Currently always null.	Number	X
SendID	The send ID number for the SMS send	Number	X
SendSplitID	If the message was split, the ID of the split	Number	X
SendSegmentID	The ID of the segment tied to the message	Number	X
SendJobID	The job ID for the SMS send	Number	X
SendGroupID	The group ID for the SMS send	Number	X
SendPersonID	The sendperson ID for the SMS send	Number	X
SubscriberID	The subscriber ID for the affected subscriber. This number represents the unique ID for each subscriber record	Number	X
SubscriberKey	The subscriber key for the affected subscriber	Text	X
SMSStandardStatusCodeId	SFMC delivery status code	Number	X
Description	Detailed description of the status code	Text	X
Name	The SMS Message or Journey Activity name	Text	X
ShortCode	The short code or long code used to send your message	Text	X
SharedKeyword	The keyword used in your message	Text	X
Ordinal	Represents the parts in a multi-part message. Represented in ascending order starting at 0.	Number	X
FromName	From Name that an individual message was deployed with. Maximum length: 11 characters	Text	X
JBDefinitionID	Unique identifier for the related journey	Text	X
JBActivityID	Unique identifier for the related journey activity	Text	X

_SMSMessageTracking SQL Query

SELECT
      smsTracking.MobileMessageTrackingID
    , smsTracking.EID
    , smsTracking.MID
    , smsTracking.Mobile
    , smsTracking.MessageID
    , smsTracking.KeywordID
    , smsTracking.CodeID
    , smsTracking.ConversationID
    , smsTracking.CampaignID
    , smsTracking.Sent
    , smsTracking.Delivered
    , smsTracking.Undelivered
    , smsTracking.Unsub
    , smsTracking.OptIn
    , smsTracking.OptOut
    , smsTracking.Outbound
    , smsTracking.Inbound
    , smsTracking.CreateDateTime
    , smsTracking.ModifiedDateTime
    , smsTracking.ActionDateTime
    , smsTracking.MessageText
    , smsTracking.IsTest
    , smsTracking.MobileMessageRecurrenceID
    , smsTracking.ResponseToMobileMessageTrackingID
    , smsTracking.IsValid
    , smsTracking.InvalidationCode
    , smsTracking.SendID
    , smsTracking.SendSplitID
    , smsTracking.SendSegmentID
    , smsTracking.SendJobID
    , smsTracking.SendGroupID
    , smsTracking.SendPersonID
    , smsTracking.SubscriberID
    , smsTracking.SubscriberKey
    , smsTracking.SMSStandardStatusCodeId
    , smsTracking.Description
    , smsTracking.Name
    , smsTracking.ShortCode
    , smsTracking.SharedKeyword
    , smsTracking.Ordinal
    , smsTracking.FromName
    , smsTracking.JBDefinitionID
    , smsTracking.JBActivityID
FROM _SMSMessageTracking AS smsTracking

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 the 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 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 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. You should pull that data from _SMSSubscriptionLog.
8. JBDefinitionID and JBActivityID let you cleanly match SMS data to Journey and _JourneyActivity Data Views using VersionID and ActivityID respectively. However, it won't work for old (created before October 2020) SMS Activities - for those you can make a JOIN using Name field: ON smsTracking.Name = journeyActivity.ActivityName.
9. SMSStandardStatusCodeId and Description are great for understanding your SMS send's current status.

SMS Status Codes

Code	Status	Definition
1000	QueuedToSfmcSendService	Message queued to internal send service.
1500	QueueFailureToSfmcSendService	Message failed to queue to internal send service. Retry your send.
1501	ValidationError	Internal validation error. Retry your send.
2000	DeliveredToAggregator	Message 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.
2500	FailedToAggregator	Message not delivered to the aggregator. Retry your send.
2501	UnknownToAggregator	Unknown aggregator error.
2600	ThrottledToAggregator	Message not accepted by aggregator due to capacity issues. Send will be retried automatically.
3000	Enroute	Message is en route to carrier. Waiting on carrier confirmation.
3001	SentToCarrier	Message sent to carrier. Waiting to be accepted by carrier.
3002	AcceptedByCarrier	Message accepted by carrier. Waiting for delivery confirmation.
3400	Unknown	Unknown error
4000	Delivered	Message delivered to mobile device.
4500	Undeliverable	Message not delivered to mobile device.
4501	Expired	Message expired. Message exhausted the carrier retry process. Mobile device may be out of carrier range.
4502	Deleted	Message deleted by the carrier.
4503	Rejected	Message 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.

---

_UndeliverableSMS

Stores information about failed message deliveries from MobileConnect.

There might be multiple reasons for the SMS to be undelivered, for example:

• The mobile device is powered off.
• The user is out of range of cellular networks
• The number is for a landline

_UndeliverableSMS Fields

Name	Description	Data Type	Nullable
MobileNumber	Mobile number for the subscriber	Text
Undeliverable	Whether the subscriber is eligible for future deliveries	Boolean
BounceCount	Number of times message was not delivered	Number
FirstBounceDate	Date of the first unsuccessful delivery	Date
HoldDate	Date when the subscriber can be used in sends again	Date	X

_UndeliverableSMS SQL Query

SELECT
      undeliverableSMS.MobileNumber
    , undeliverableSMS.Undeliverable
    , undeliverableSMS.BounceCount
    , undeliverableSMS.FirstBounceDate
    , undeliverableSMS.HoldDate
FROM _UndeliverableSMS AS undeliverableSMS

When working with _JourneyActivity Data View:

1. If Undeliverable is TRUE, check HoldDate to know when the number will be reachable again.
2. Monitor this data view and blocklist numbers with significant BounceCount values to clean up your database, save money and protect from spamming potential future owners of the recycled phone number.
3. It's impossible to know the actual delivery status for SMS sends - Marketing Cloud reports only whether the SMS carrier accepted the message. It does not know what happens with it after the carrier takes over. However, most of the SMS messages accepted by carriers are delivered.

---

Journey Data Views

_JourneyActivity

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

_JourneyActivity Fields

Name	Description	Data Type	Nullable
VersionID	The unique identifier for the version of the Journey	Text
ActivityID	The unique identifier for the activity. There are one or more ActivityID's associated to a VersionID.	Text
ActivityName	The name of the activity	Text	X
ActivityExternalKey	The external key associated with the activity	Text
JourneyActivityObjectID	Use this unique identifier to join to email tracking system Data Views to identify a Journey email's Triggered Send Definition.	Text	X
ActivityType	The type of activity	Text	X

_JourneyActivity SQL Query

SELECT
      journeyActivity.VersionID
    , journeyActivity.ActivityID
    , journeyActivity.ActivityName
    , journeyActivity.ActivityExternalKey
    , journeyActivity.JourneyActivityObjectID
    , journeyActivity.ActivityType
FROM _JourneyActivity AS journeyActivity

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. Use TriggererSendDefinitionObjectID available on either _Job, _Sent, _Open, _Click or _Bounce Data View. It is handy when you later use the 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. You can also connect it to SMS channel using _SMSMessageTracking Data View by making a JOIN ON journeyActivity.ActivityID = smsTracking.JBActivityID.
3. With the help of _Journey Data View, you can use the 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 need to use Script Activity with REST API instead, but SQL is perfect for glancing at the multi-channel use.
4. _JourneyActivity Data View holds data about activities from deleted Journeys and Journey Versions, so if you want to see only activities currently in use, JOIN _Journey Data View.

You Should Know

_JourneyActivity Data View shows data limited to a specific 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 _JourneyActivity. You can query only those Journey Activities that were created in the Business Unit where you execute your SQL snippet.

Always consider this when choosing the type of JOIN statement.

---

_Journey

Stores information about your Journeys for when context matters.

_Journey Fields

Name	Description	Data Type	Nullable
VersionID	The unique identifier for the version of the Journey	Text
JourneyID	The unique identifier for the Journey. There are one or more VersionID's associated to a JourneyID.	Text
JourneyName	The name of the Journey	Text
VersionNumber	The version number of the version of the Journey	Number
CreatedDate	The date that the version of the Journey was created	Date
LastPublishedDate	The date that the version of the Journey was last published	Date	X
ModifiedDate	The date that the version of the Journey was last edited	Date
JourneyStatus	The current running mode of the Journey. Possible values are Draft, Running, Finishing, and Stopped.	Text

_Journey SQL Query

SELECT
      journey.VersionID
    , journey.JourneyID
    , journey.JourneyName
    , journey.VersionNumber
    , journey.CreatedDate
    , journey.LastPublishedDate
    , journey.ModifiedDate
    , journey.JourneyStatus
FROM _Journey AS journey

When working with _Journey Data View:

1. VersionID field is perfect to JOIN with _JourneyActivity. It lets you map multi-channel engagement to a particular Journey (visible in JourneyName) and its specific version (as shown in VersionNumber).
2. You can also connect it to SMS channel using _SMSMessageTracking Data View by making a JOIN ON journey.VersionID = smsTracking.JBDefinitionID.
3. 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.
4. 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.
5. You can query only Journeys that currently exists in your Business Unit.

You Should Know

_Journey Data View shows data limited to a specific 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 _Journey. You can query only those Journeys that were created in the Business Unit where you execute your SQL snippet.

Always consider this when choosing the type of JOIN statement.

---

Custom Data Views

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

You Should Know

As shared by Lukas, since May 2021, there is longer no possibility to create Custom Data Views leveraging the Journey Builder.

Name	Description	Data Type	Nullable
JourneyVersionNumber	The version number of the version of the Journey	Number
JourneyName	The name of the Journey	Text
JourneyStatus	Tells you whether the Journey is still Active	Text
ContactKey	The subscriber key for the affected subscriber	Text
ContactID	Global Contact ID (not Contact Key)	Number
ContactStatus	Tell you whether the contact is still in the Journey (Active, Exited or even more detailed like DidNotMeetEntryCriteria, ContactExitedByAPI)	Text
EntryDate	Date of Journey Entry	Date
ExitDate	Date of Journey Exit - including Goal / Exit Criteria. Null if still in Journey.	Date	x

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 at 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.