SFMC System Data Views
Dive deep into System Data Views - the backend Marketing Cloud data. Actionable pearls await.
#
Data Views BasicsSystem 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.
#
Subscriber Data Views#
_SubscribersStores information about your Subscribers. Great for high-level perspective or adding Email Address field to other more detailed Data Views.
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 | ||
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 |
When working with _Subscribers
Data View:
- If you are using the query in child Business Unit, always check whether you are querying local or enterprise Data View (
ENT.
prefix). - 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. - Use
SubscriberID
toJOIN
data from different engagement-based Data Views like_Sent
,_Open
,_Click
or_Bounce
. - 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.
#
_EnterpriseAttributeStores information about your Profile Attributes from Email Studio.
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 |
When working with _EnterpriseAttribute
Data View:
- If you are using the query in child Business Unit always use
ENT.
prefix as it is enterprise-level only Data View. - When querying or joining
_EnterpriseAttribute
always be sure to include the underscore before SubscriberID - this is the only Data View that requires it. - 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. _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#
_JobStores 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.
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 | 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 | X | |
Category | The job category | Text | |
BccEmail | Any email address send in the BCC field | 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 |
When working with _Job
Data View:
JobID
field will be your bread and butter toJOIN
with other engagement-based Data Views.EmailName
,FromName
, andFromEmail
are handy data points missing in the_Sent
,_Open
,_Click
and_Bounce
Data Views and worth checking for reporting needs.- 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 withCASE
to create a great knowledge source for your content team. - For debugging purposes be sure to check out
IsWrapped
andSuppressTracking
- if you are missing tracking data,CharacterSet
- if you have problems with formatting,SendClassification
- if you have issues with unsubscribers or spam complaints. 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.
#
_SentStores information about your Email Sends. Great for understanding who had a chance to receive your message and whose communication did not leave your system.
Name | Description | Data Type | Nullable |
---|---|---|---|
AccountID | Your account ID (MID) number | Number | |
OYBAccountID | The account ID (MID) number of the child Business Unit. Null on parent Business Unit | 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 |
When working with _Sent
Data View:
- Use
JobID
,ListID
,BatchID
andSubscriberId
orSubscriberKey
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. - The most useful data here is the
EventDate
field that lets you know when the email was sent (and whether that happened at all). - 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.
#
_OpenStores information about your Email Opens. For when you need to know when someone opened your email. Kind of.
Name | Description | Data Type | Nullable |
---|---|---|---|
AccountID | Your account ID (MID) number | Number | |
OYBAccountID | The account ID (MID) number of the child Business Unit. Null on parent Business Unit | 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 |
When working with _Open
Data View:
- Use
JobID
,ListID
,BatchID
andSubscriberId
orSubscriberKey
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. - Use
_open.IsUnique = 1
inJOIN
orWHERE
to focus on first occurrences of each event. Example: Query for Debugging Email Sends. - 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.
#
_ClickStores information about your Email Clicks. Detailed click reporting and behavioural logic are possible thanks to this one.
Name | Description | Data Type | Nullable |
---|---|---|---|
AccountID | Your account ID (MID) number | Number | |
OYBAccountID | The account ID (MID) number of the child Business Unit. Null on parent Business Unit | 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 |
When working with _Click
Data View:
- Use
JobID
,ListID
,BatchID
andSubscriberId
orSubscriberKey
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. - Use
_click.IsUnique = 1
inJOIN
orWHERE
to focus on first occurrences of each event. Example: Query for Debugging Email Sends. - The most useful data here is the
EventDate
field that lets you know when the email was clicked (and whether that happened at all). - You can significantly improve the value of that information by checking the
LinkName
andLinkContent
fields to understand which links proved interesting for your customers. - If you want to create a big report on link clicks you might want to use
URL
field instead ofLinkContent
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.
#
_BounceStores information about your Email Bounces. Best place in Marketing Cloud to understand why your messages are not getting delivered.
Name | Description | Data Type | Nullable |
---|---|---|---|
AccountID | Your account ID (MID) number | Number | |
OYBAccountID | The account ID (MID) number of the child Business Unit. Null on parent Business Unit | 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 | Number | X |
TriggeredSendCustomerKey | The customer key for the triggered send | Text | X |
When working with _Bounce
Data View:
- Use
JobID
,ListID
,BatchID
andSubscriberId
orSubscriberKey
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. - Use
_bounce.IsUnique = 1
inJOIN
orWHERE
to focus on first occurrences of each event. Example: Query for Debugging Email Sends. - The
Domain
is useful for aggregating the bounce data to see any domain-specific problem with deliverability. 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.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 ViewsMobileConnect 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.
Name | Description | Data Type | Nullable |
---|---|---|---|
_MobileID | MobileConnect Contact ID | MobileConnect | Number |
_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 |
_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:
- 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.
- 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. - 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.
_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.- Whenever creating a MobileConnect Contact, be sure to provide the
_MobileNumber
with the dialling code prefix (without the+
or00
). 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
). - The Contact may have multiple Mobile Numbers assigned, but it will receive communication only to the one with highest
_Priority
. - 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). _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
).
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 |
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.
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 |
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.
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 |
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.
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 |
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:
- 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.
- 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
orMultiple subscriber keys found for this mobile number
as a value. SubscriptionDefinitionID
is a GUID, and there is no apparent connection to a specific Keyword. But there are two tricks.JOIN
_SMSMessageTracking
Data View and useKeywordID
andSharedKeyword
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.
#
_SMSMessageTrackingName | 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 |
When working with _SMSMessageTracking
Data View:
- If you send one SMS to multiple Subscribers, you will have multiple rows of data with different
MobileMessageTrackingID
but sameMessageID
. Apart fromMessageID
you may also use theName
field that contains the asset name from MobileConnect. KeywordID
andCodeID
are GUIDs, but the same table also has the readable versions of those columns -ShortCode
andSharedKeyword
.ShortCode
, despite the name, also displays Long Codes.- 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 benull
- for example, if a message was outbound,Outbound
will have the value of1
, whereasInbound
will benull
. 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.ResponseToMobileMessageTrackingID
is an excellent way to check whether the customer responded. You can make Self-Join to output response data in the same line.- 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 AMPScriptCreateSMSConversation
.
Unsub
,OptIn
,OptOut
are currently not populated at all. Data should be pulled from_SMSSubscriptionLog
.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
).SMSStandardStatusCodeId
andDescription
are great for understanding your SMS send's current status.
#
SMS Status CodesCode | 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. |
#
Journey Data Views#
_JourneyActivityStores information about Activities available in your Journeys. Must-have for joining Email data with Journey data.
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 |
When working with _JourneyActivity
Data View:
- There are two key fields here:
VersionID
andJourneyActivityObjectID
.JourneyActivityObjectID
lets you match Journey Activity with specific Email Send. Just useTriggererSendDefinitionObjectID
available on either_Job
,_Sent
,_Open
,_Click
or_Bounce
Data View. It is handy when you later useVersionID
field toJOIN
_Journey
Data View and with this connect specific Email engagement with particular Journey and its exact version. Fantastic for full scope reporting. - With the help of
_Journey
Data View, you can useActivityType
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.
#
_JourneyStores information about your Journeys, for when context matters.
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 |
When working with _Journey
Data View:
VersionID
field is perfect toJOIN
with_JourneyActivity
. It allows you to map multi-channel engagement to particular Journey (visible inJourneyName
) and its specific version (as shown inVersionNumber
).- Use
JourneyStatus
to check what is the current Journey Status. It is fantastic for both theWHERE
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. - 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.