Understanding Salesforce Marketing Cloud Data Views
Imagine a book.
A book is organised into chapters. Each chapter, then, consists of paragraphs.
Those paragraphs, in turn, are made of individual words.
A relational database is very similar.
SFMC works as a relational database, containing tables that are related. Then, each table contains columns, and each column includes individual records (data).
SFMC Data Views are internal tables in that relational database. They store data about lots of aspects of your marketing activities: Subscribers and their data, Send campaigns, Engagement reporting (e.g: clicks, bounces), Complaints, Unsubscribes, Automations…
Here is the official Salesforce documentation link for SFMC Data Views:
https://help.salesforce.com/s/articleView?id=sf.mc_as_data_views.htm&type=5
1) The data in these internal tables cannot be modified.
2) Also, Data Views are not accessible by the usual User Interface. You will need to use SQL Language and write queries to retrieve information from Data Views.
3) Finally, the data is stored up to last 6 months. To store historical data, you’ll have to create Automations that save the data periodically in Data Extensions.
Data View: Automation Instance
The Automation Instance Data Views provides an overview of all the automations in a SFMC tenant. You can retrieve data about the efficiency and success rate of your automations, prevent failures and fix failed runs of the automations.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
MemberID | The unique ID of the Business Unit in SFMC. | BIGINT | Number | |
FilenameFromTrigger | For Trigger and File Drop automations, the name of the file that triggered the start of the automation run. | NVARCHAR(4000) | Text | X |
AutomationType | The type of Automation, based on starting source. Possible values are File Drop, Trigger, Scheduled. | VARCHAR(9) | Text | |
AutomationStepCount | The total number of steps in the automation. | INT | Number | |
AutomationNotificationRecipient_Skip | The email address for notifications of the automation run being skipped. | NVARCHAR(500) | Text | X |
AutomationNotificationRecipient_Error | The email address for notifications of errored automation. | NVARCHAR(500) | Text | X |
AutomationNotificationRecipient_Complete | The email address for notifications of automation completion. | NVARCHAR(500) | Text | X |
AutomationName | The name of the Automation. | NVARCHAR(400) | Text | |
AutomationInstanceStatus | The status of the automation run when the data view is queried. Possible values are QueuedFile, Initialized, Executing, Stopped, Complete, or Error. | NVARCHAR(400) | Text | |
AutomationInstanceStartTime_UTC | The UTC time that the automation run began. NULL if the automation run was skipped. | DATETIMEOFFSET | Date | X |
AutomationInstanceScheduledTime_UTC | The UTC time that the run was scheduled to start. For Scheduled Automations. | DATETIMEOFFSET | Date | X |
AutomationInstanceIsRunOnce | Whether the automation was configured to run once or not. TRUE = 1, FALSE = 0. | BIT | Boolean | |
AutomationInstanceID | The unique ID of the automation run. | UNIQUEIDENTIFIER | Text | |
AutomationInstanceEndTime_UTC | The UTC time that the automation run finished. NULL if the automation is still running or the run was skipped. | DATETIMEOFFSET | Date | X |
AutomationInstanceActivityErrorDetails | The error log message returned, if applicable. If multiple errors occured, only the first one is displayed. If error is unclassified or system, message displays System Error. To get further details on multiple error messages, query the _AutomationActivityInstance data view. | NVARCHAR(4000) | Text | X |
AutomationDescription | The description of the Automation. | NVARCHAR(400) | Text | X |
AutomationCustomerKey | The unique ID of the automation in SFMC. | NVARCHAR(400) | Text |
SQL Query
SELECT MemberID, AutomationName, AutomationDescription, AutomationCustomerKey, AutomationType, AutomationNotificationRecipient_Complete, AutomationNotificationRecipient_Error, AutomationNotificationRecipient_Skip, AutomationStepCount, AutomationInstanceID, AutomationInstanceIsRunOnce, FilenameFromTrigger, AutomationInstanceScheduledTime_UTC, AutomationInstanceStartTime_UTC, AutomationInstanceEndTime_UTC, AutomationInstanceStatus, AutomationInstanceActivityErrorDetails FROM [_automationinstance]
Data View: Automation Activity Instance
The Automation Activity Instance Data View provides information about the performance of the activities used in your automations. You can use this table to retrieve information about failing activities, those that take a long time to run, etc.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
MemberID | The Business Unit unique ID. | BIGINT | Number | |
AutomationName | The name of the Automation. | NVARCHAR(400) | Text | |
AutomationCustomerKey | The unique ID of the automation in SFMC. | NVARCHAR(400) | Text | |
AutomationInstanceID | The unique ID for the automation run. | UNIQUEIDENTIFIER | Text | |
ActivityType | The type of the activity. Possible values are listed below, in the Activity Type IDs table. | INT | Number | |
ActivityName | The name of the activity. | NVARCHAR(400) | Text | |
ActivityDescription | The description of the activity. | NVARCHAR(400) | Text | X |
ActivityCustomerKey | The unique ID of the activity in SFMC. | NVARCHAR(400) | Text | |
ActivityInstanceStep | Where the activity takes place in the automation sequence. E.g: 1.2 is step 1, activity 2. | VARCHAR(25) | Text | |
ActivityInstanceID | The unique ID of the run of the activity. | UNIQUEIDENTIFIER | Text | |
ActivityInstanceStartTime_UTC | The UTC time when the activity run started. NULL if the run was skipped. | DATETIMEOFFSET | Date | X |
ActivityInstanceEndTime_UTC | The UTC time when the activity run finished. NULL if the automation is still running, it was skipped or the activity was not selected in an Advanced Run Once. | DATETIMEOFFSET | Date | X |
ActivityInstanceStatus | The status of the activity run at the time when this Data View is queried. Possible values include: Error, NotSelected, Executing, Initialized, Complete. | NVARCHAR(256) | Text | |
ActivityInstanceStatusDetails | The error log message, if applicable. If the error is unclassified or system, message displays System Error. | NVARCHAR(4000) | Text | X |
Data View Activity Types
ACTIVITY TYPE ID | ACTIVITY NAME |
---|---|
33 | SMS Activity |
42 | Send Email |
43 | Import File |
45 | Refresh Group |
53 | File Transfer |
73 | Data Extract |
84 | Report Definition |
300 | SQL Query |
303 | Filter |
423 | Script |
425 | Data Factory Utility Activity |
426 | Refresh Segment Template |
427 | Publish Audience |
467 | Wait |
724 | Refresh Mobile Filtered List |
725 | Send SMS |
726 | Import Mobile Contacts |
733 | Journey Builder Event Activity |
736 | Send Push |
749 | Fire Event |
771 | Salesforce Email Send |
772 | Mobile Connect Send Salesforce Sync Subscriber |
783 | Send GroupConnect |
952 | Journey Builder Event Activity |
1000 | Verification |
1010 | Interaction Studio Data |
1101 | Interactions |
1701 | Batch Personalization |
3700 | Contact to Business Unit Mapping |
SQL Query
SELECT MemberID, AutomationName, AutomationCustomerKey, AutomationInstanceID, ActivityType, ActivityName, ActivityDescription, ActivityCustomerKey, ActivityInstanceStep, ActivityInstanceID, ActivityInstanceStartTime_UTC, ActivityInstanceEndTime_UTC, ActivityInstanceStatus, ActivityInstanceStatusDetails FROM [_AutomationActivityInstance]
Data View: Bounce
The _Bounce Data View provides information about bounce data of your email activities in SFMC. The Dates and Times in this data view are stored using Central Standard Time. Daylight Savings Time not observed.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The ID number of your SFMC account. | INT | Number | |
OYBAccountID | Applies to enterprise accounts only. The account ID number for On-Your-Behalf accounts. | INT | Number | X |
JobID | The job ID, unique number for the email send. | BIGINT | Number | |
ListID | The list ID number for the list that was used in the email send. | INT | Number | |
BatchID | The batch ID number for any batches that were used in the email send. | BIGINT | Number | |
SubscriberID | The subscriber ID of the bounced subscriber. This is a unique ID for each subscriber record. | INT | Number | |
SubscriberKey | The subscriber key for the bounced subscriber. The Primary Key. | NVARCHAR(254) | Text | |
EventDate | The date when the bounce took place. In Central Standard Time. | DATETIME | Date | |
IsUnique | Indicates whether the event is unique or a repeated one. | BIT | Boolean | |
Domain | The domain at which the bounce took place. | VARCHAR(128) | Text | |
BounceCategoryID | The unique ID number for the category of the bounce. | SMALLINT | Number | |
BounceCategory | The bounce category. | NVARCHAR(50) | Text | X |
BounceSubcategoryID | The unique ID number for the bounce subcategory. | SMALLINT | Number | X |
BounceSubcategory | The bounce subcategory. | NVARCHAR(50) | Text | X |
BounceTypeID | The unique ID number for the bounce type | SMALLINT | Number | |
BounceType | The type of bounce that took place. | NVARCHAR(50) | Text | X |
SMTPBounceReason | The reason for the bounce, as returned by the mail system. | NVARCHAR(MAX) | Text | X |
SMTPMessage | The mail system message about the bounce. | NVARCHAR(MAX) | Text | X |
SMTPCode | The mail system error code for the bounce. | SMALLINT | Number | X |
TriggererSendDefinitionObjectID | The unique object ID for the triggered send definition of the send. | VARCHAR(36) | Text | X |
TriggeredSendCustomerKey | The customer key for the triggered send. | VARCHAR(36) | Text | X |
IsFalseBounce | Indicates a false bounce. TRUE = 1, FALSE = 0. | BIT | Boolean | X |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, IsUnique, Domain, BounceCategoryID, BounceCategory, BounceSubcategoryID, BounceSubcategory, BounceTypeID, BounceType, SMTPBounceReason, SMTPMessage, SMTPCode, TriggererSendDefinitionObjectID, TriggeredSendCustomerKey, IsFalseBounce FROM [_bounce]
Data View: BusinessUnitUnsubscribes
The _BusinessUnitUnsubscribes Data View provides information about subscribers in your account and the child business unit unsubscribe data. This data view can only be queried from the Parent Business Unit. Dates and Times are stored in Central Standard Time (Daylight Savings Time not observed).
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
BusinessUnitID | The Account ID or MID number for the Business Unit (BU). | BIGINT | Number | |
SubscriberID | The unique subscriber ID for the subscriber. Unique ID for each subscriber record. | BIGINT | Number | |
SubscriberKey | A possible alternative key to identify subscribers. Defaults to the value of Email Address. | VARCHAR(254) | Text | |
UnsubDateUTC | The UTC date when the subscriber unsubscribed from the Business Unit. | SMALLDATETIME | Date | X |
UnsubReason | The unsubscribe reason listed is a custom value configured by the admin of the account. | VARCHAR(100) | Text | X |
SQL Query
SELECT BusinessUnitID, SubscriberID, SubscriberKey, UnsubDateUTC, UnsubReason FROM [_BusinessUnitUnsubscribes]
Data View: Click
The _Click data view gives you information about click activity and data of your emails in SFMC account. Use it to view clicks on links in the emails sent from Salesforce Marketing Cloud. Dates and Times are stored in Central Standard Time (Daylight Savings Time not observed).
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The unique account ID number of the Marketing Cloud account. | INT | Number | |
OYBAccountID | Only applies to Enterprise accounts. The account ID number for On-Your-Behalf (OYB) accounts. | INT | Number | X |
JobID | The unique job ID number generated for the email send. | BIGINT | Number | |
ListID | The unique list ID number for the list that was used in the email send. | INT | Number | |
BatchID | The unique batch ID number for any batches that were used in the email send. | BIGINT | Number | |
SubscriberID | The subscriber ID for the subscriber. This number is a unique ID for each subscriber record. | INT | Number | |
SubscriberKey | The subscriber key of the subscriber. | NVARCHAR(254) | Text | |
EventDate | The date when the click happened. | DATETIME | Date | |
Domain | The domain at which the click took place. | VARCHAR(128) | Text | |
URL | The URL of the link clicked. AMPScript or variables are not returned in this column. E.g: www.example.com?%%attribute%% | VARCHAR(900) | Text | X |
LinkName | The link name that was given in the email send. | VARCHAR(1024) | Text | X |
LinkContent | The link content given in the email send. AMPscript and variables are returned and populated in this field. E.g: www.example.com?12345 | VARCHAR(MAX) | Text | X |
IsUnique | Indicates whether the event is unique or a repeated one. NOTE: The IsUnique value is only true when any link in a JobID is clicked for the first time by a subscriber. Any further clicks return a false value, even if they are for different URLs. | BOOLEAN | Boolean | |
TriggererSendDefinitionObjectID | The unique object ID for the triggered send definition in SFMC. | UNIQUEIDENTIFIER | Text | X |
TriggeredSendCustomerKey | The unique customer key for the triggered send in SFMC. | VARCHAR(36) | Text | X |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, Domain, URL, LinkName, LinkContent, IsUnique, TriggererSendDefinitionObjectID, TriggeredSendCustomerKey FROM [_Click]
Data View: Complaint
The _Complaint Data View gives you data about the complaints related to emails from your Salesforce Marketing Cloud account. It stores records up to last 6 months.Query this data view to access data about spam complaints from your subscribers about emails sent in SFMC.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The unique account ID number. | INT | Number | |
OYBAccountID | The unique account ID for On-Your-Behalf (OYB) accounts. Applied only to enterprise accounts. | INT | Number | X |
JobID | The unique job ID number for the email send in SFMC. | BIGINT | Number | |
ListID | The unique list ID number for the list that was used for the SFMC send. | INT | Number | |
BatchID | The unique batch ID number for the batches that were used in the SFMC send. | BIGINT | Number | |
SubscriberID | The unique subscriber ID for the subscriber. | INT | Number | |
SubscriberKey | The subscriber key of the subscriber. | NVARCHAR(254) | Text | |
EventDate | The date when the complaint happened. | DATETIME | Date | |
IsUnique | Indicates whether the event is unique or a repeated one. | BIT | Boolean | |
Domain | The domain at which the complaint took place. | VARCHAR(128) | Text |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, IsUnique, Domain FROM [_Complaint]
Data View: Coupon
The _Coupon Data View includes data about the coupons used in your Salesforce Marketing Cloud account. To use this Data View, you must use live content in Content Builder Block SDK.
To use this Data View, you must use live content in Content Builder Block SDK.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
Name | This is the name of the coupon. | nvarchar(128) | Text | |
ExternalKey | This is the unique external key used to point at your coupon via API. | nvarchar(36) | Text | |
Description | The description of the coupon. | varchar | Text | |
BeginDate | The date when the coupon is valid. | datetime | Date | |
ExpirationDate | The date when the coupon becomes invalid. | datetime | Date |
SQL Query
SELECT Name, ExternalKey, Description, BeginDate, ExpirationDate FROM [_Coupon]
Data View: EnterpriseAttribute
The _EnterpriseAttribute Data View gives you data about your subscribers in your Enterprise Marketing Cloud account and their profile attributes. This Data View is only for Enterprise 2.0 clients. When profile attributes are created in an Enterprise 2.0 account, the new columns are added to the _EnterpriseAttribute data view table.
This Data View is only accessible in the Parent account. It cannot be used in a specific Business Unit.
Preference Attributes are not available in this table or data view. E.g: HTML Email
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
_SubscriberID | The unique subscriber ID for the subscriber. This is a unique number ID for each subscriber record. | BIGINT | Number |
SQL Query
SELECT _SubscriberID FROM [_EnterpriseAttribute]
Data View: FTAF
The _FTAF Data View (Forward To A Friend) contains behavioral data related to emails delivered from your Salesforce Marketing Cloud account that were forwarded to a friend. It stores data up to 6 months ago.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The unique account ID number for your account. | INT | Number | |
OYBAccountID | The account ID number for On-Your-Behalf accounts. Applies only to enterprise accounts. | INT | Number | X |
JobID | This is the unique job ID number for the email send in SFMC. | INT | Number | |
ListID | This is the unique list ID number for the list that was used in the send in SFMC. | INT | Number | |
BatchID | The batch ID number for any batches that were used in the send. | INT | Number | |
SubscriberID | The unique subscriber ID number for the subscriber. | INT | Number | |
SubscriberKey | The subscriber key of the subscriber. | NVARCHAR(254) | Text | |
TransactionTime | This is the date when the forward to a friend happened. | DATETIME | Date | |
Domain | The domain at which the forward to a friend took place. | VARCHAR(128) | Text | |
IsUnique | This field indicates whether the event is unique or a repeated one. | BIT | Boolean | |
TriggererSendDefinitionObjectID | The unique object ID for the triggered send definition of the send. | UNIQUEIDENTIFIER | Text | X |
TriggeredSendCustomerKey | The unique customer key for this triggered send. | VARCHAR(36) | Text | X |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, TransactionTime, Domain, IsUnique, TriggererSendDefinitionObjectID, TriggeredSendCustomerKey FROM [_FTAF]
Data View: GroupConnect Contact Subscriptions
Query the GroupConnect data to view active LINE followers, current subscribers, subscribers who blocked your account, etc.
Pre-requisites:
In order to use this data, you need to enable the business rule SYSTEM_DATA_VIEWS in your account and set it to 1. When the Group Connect app is loaded, the account is checked. If the data view doesn’t exist, then it’s created. In order to access this view for the first time in your account, an admin or user in the Business Unit must visit the GroupConnect channel app to trigger the creation of the view.
Follow these steps to access the data:
1. In Contact Builder, create a Data Extension with the same fields as the data view. Include an extra column with SystemDate NOW(), of Date data type.
2. In SFMC Automation Studio, create an Automation and add an SQL Query activity.
3. In the SQL Query Activity, write the query with _MobileLineAddressContactSubscriptionView.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
If you want to track LINE subscribers who have blocked your account, you need to compare daily lists, to try to find users who are not listed any longer.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
ChannelID | This is the unique channel ID of the LINE account. | NVARCHAR | Text | |
ContactID | This is the Marketing Cloud ContactId (ID generated by system) that is linked to the LINE UID. | BIGINT | Number | |
ContactKey | The unique Marketing Cloud Contact Key linked to the LINE UID. This is system generated. | NVARCHAR | Text | |
AddressID | This is the unique LINE UID. | NVARCHAR | Text | |
IsActive | A boolean flag to indicate if active or not. 1 = TRUE, 0 = FALSE. | BIT | Number | |
CreatedDate | The date and time of record creation in Central Standard Time. The date when the customer started following the LINE account. | DATETIME | Date | |
ModifiedDate | The date and time of record modification in Central Standard Time. | DATETIME | Date | X |
SQL Query
SELECT ChannelID, ContactID, ContactKey, AddressID, IsActive, CreatedDate, ModifiedDate, GETDATE() AS ExtraDateField FROM [_MobileLineAddressContactSubscriptionView]
Data View: GroupConnect MobileLineOrphanContactView
When an import of contacts for the same LINE Address ID is performed, if it contains multiple contacts, the system keeps only one ContactKey. The remaining ContactKeys become orphaned. You can query the _MobileLineOrphanContactView data view to view these orphaned contacts.
Pre-requisites:
1. In Contact Builder, create a Data Extension with the same fields as the data view. Include an extra column with SystemDate NOW(), of Date data type.
2. In SFMC Automation Studio, create an Automation and add an SQL Query activity.
3. In the SQL Query Activity, write the query with _MobileLineOrphanContactView.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
ContactID | The unique Marketing Cloud ContactID linked to the LINE UID. This is system generated. | BIGINT | Number | |
ContactKey | The unique ContactKey can be either provided by the user during the import, or generated by the system. This ContactKey is linked to the LINE UID. | NVARCHAR | Text | |
AddressID | The unique LINE UID. | NVARCHAR | Text | |
CreatedDate | The Date and Time of record creation (in Central Standard Time). The Date when the customer started following the LINE account. | DATETIME | Date |
SQL Query
SELECT ContactID, ContactKey, AddressID, CreatedDate, GETDATE() AS ExtraDateField FROM [__MobileLineOrphanContactView]
Data View: Job
Query the _Job SFMC Data View to view data about emails send jobs from your Salesforce Marketing Cloud account. It stores data up to last 6 months.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
JobID | The unique job ID number for the email send in SFMC. | INT | Number | |
EmailID | This is the unique email ID for the job. | INT | Number | X |
AccountID | The unique ID number for the account that executed this job. | INT | Number | X |
AccountUserID | The unique ID number of the user that performed the send job. | INT | Number | X |
FromName | The from name used in the email send in SFMC. | NVARCHAR(130) | Text | X |
FromEmail | The from email address used in the email send in SFMC. | VARCHAR(100) | X | |
SchedTime | This is the time when the send job was scheduled. | SMALLDATETIME | Date | X |
PickupTime | This is the time when the Marketing Cloud application started the send job. | SMALLDATETIME | Date | X |
DeliveredTime | This is the time when the email was delivered. | SMALLDATETIME | Date | X |
EventID | The unique ID for the event of the job. | VARCHAR(50) | Text | X |
IsMultipart | This field indicates whether the send job was sent as a multipart MIME or not. | BIT | Boolean | |
JobType | The job type. | VARCHAR(50) | Text | X |
JobStatus | The job status. | VARCHAR(50) | Text | X |
ModifiedBy | If applicable, the SFMC user who modified the job. | INT | Number | X |
ModifiedDate | The date when the job was modified. | DATETIME | Date | X |
EmailName | The name of the email sent by the send job. | CHAR(100) | Text | X |
EmailSubject | The email subject of the email send for the job. | NCHAR(200) | Text | X |
IsWrapped | This field indicates whether the links in the email were wrapped for tracking or not. | BIT | Boolean | |
TestEmailAddr | The test email address that was used in the send job. | VARCHAR(128) | X | |
Category | The category of the job. | VARCHAR(100) | Text | |
BccEmail | If applicable, the email address configured in the BCC field for the send. | VARCHAR(100) | X | |
OriginalSchedTime | This field indicates the original scheduled time for the send job in SFMC. | SMALLDATETIME | Date | X |
CreatedDate | This is the date when the send job was created in SFMC. | SMALLDATETIME | Date | |
CharacterSet | The character set that was used in this send job. | VARCHAR(30) | Text | X |
IPAddress | This field always has a NULL value. | VARCHAR(50) | Text | X |
SalesForceTotalSubscriberCount | This field is the total number of Salesforce Subscribers that were included in the job. | INT | Number | |
SalesForceErrorSubscriberCount | This field is the total number of Salesforce Subscribers that were included in the job and received an error. | INT | Number | |
SendType | The send type that was used in this job. | VARCHAR(128) | Text | |
DynamicEmailSubject | If applicable, the dynamic email subject added to the job. | NTEXT | Text | X |
SuppressTracking | This field indicates whether the tracking information for this job was suppressed from logs or not. | BIT | Boolean | |
SendClassificationType | The send classification type that was configured for the send job. | NVARCHAR(32) | Text | X |
SendClassification | The send classification that was used for the job. | NVARCHAR(36) | Text | X |
ResolveLinksWithCurrentData | This field indicates whether the job resolved links with current data or not. | BIT | Boolean | |
EmailSendDefinition | The email send definition that was used in the job. | NVARCHAR(36) | Text | X |
DeduplicateByEmail | This field indicates whether the email addresses in the job are used to deduplicate subscribers or not. | BIT | Boolean | |
TriggererSendDefinitionObjectID | The unique object ID for the triggered send definition. | UNIQUEIDENTIFIER | null | X |
TriggeredSendCustomerKey | The unique customer key for the triggered send. | VARCHAR(36) | Text | X |
SQL Query
SELECT JobID, EmailID, AccountID, AccountUserID, FromName, FromEmail, SchedTime, PickupTime, DeliveredTime, EventID, IsMultipart, JobType, JobStatus, ModifiedBy, ModifiedDate, EmailName, EmailSubject, IsWrapped, TestEmailAddr, Category, BccEmail, OriginalSchedTime, CreatedDate, CharacterSet, IPAddress, SalesForceTotalSubscriberCount, SalesForceErrorSubscriberCount, SendType, DynamicEmailSubject, SuppressTracking, SendClassificationType, SendClassification, ResolveLinksWithCurrentData, EmailSendDefinition, DeduplicateByEmail, TriggererSendDefinitionObjectID FROM [_Job]
Data View: Journey
Query the _Journey SFMC Data View to view data from SFMC Journey Builder, including all your journeys, their status, created and last modified dates, etc.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
VersionID | This field is a unique identifier for the version of the journey. | UNIQUEIDENTIFIER(36) | Text | |
JourneyID | This is the unique identifier for the journey. There can be multiple VersionIDs associated to a unique JourneyID. | UNIQUEIDENTIFIER(36) | Text | |
JourneyName | The journey name. | NVARCHAR(200) | Text | |
VersionNumber | This is the number of the version of the journey. | INT | Number | |
CreatedDate | This is the date when the version of the journey was created. | DATETIME | Date | |
LastPublishedDate | This is the date when the version of the journey was published last. | DATETIME | Date | X |
ModifiedDate | The date when the journey was last modified. | DATETIME | Date | |
JourneyStatus | The current status of the journey. Possible values include: Running, Finishing, Stopped and Draft. | NVARCHAR(100) | Text |
SQL Query
SELECT VersionID, JourneyID, JourneyName, VersionNumber, CreatedDate, LastPublishedDate, ModifiedDate, JourneyStatus FROM [_Journey]
Data View: Journey Activity
Query the _JourneyActivity SFMC Data View to view data about the different activities within your journeys in Journey Builder. Also, use this data view to join email tracking data to journey activities.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
The attribute TriggererSendDefinitionObjectID in the _Open, _Sent, _Click and _Bounce data views corresponds to the attribute JourneyActivityObjectID in this table.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
VersionID | This is a unique identifier for the journey version. | UNIQUEIDENTIFIER(36) | Text | |
ActivityID | A unique identifier for the activity in the journey. There can be multiple ActivityIDs associated with a single VersionID of a journey. | UNIQUEIDENTIFIER(36) | Text | |
ActivityName | This field is the name of the journey activity. | NVARCHAR(200) | Text | X |
ActivityExternalKey | The unique external key given to the journey activity. | NVARCHAR(200) | Text | |
JourneyActivityObjectID | A unique identifier used to identify a journey email activity's Triggered Send Definition, which is used to join with email tracking data views. | UNIQUEIDENTIFIER(36) | Text | X |
ActivityType | The journey activity type. | NVARCHAR(512) | Text | X |
SQL Query
SELECT VersionID, ActivityID, ActivityName, ActivityExternalKey, ActivityType, JourneyActivityObjectID FROM [_JourneyActivity]
Data View: ListSubscribers
Query the _ListSubscribers SFMC Data View to view data about subscribers on the lists in your Salesforce Marketing Cloud account.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AddedBy | This is the ID of the SFMC user who executed the process which added the subscriber. | INT | Number | |
AddMethod | The adding method for the subscriber. This includes: - Webcollect - API - Unspecified - FTAF - Application - Salesforce - Segmentation - Custom Object - Generic Extension - Import - Move - Copy | VARCHAR(17) | Text | |
CreatedDate | This is the date when the subscriber was added to the list. | SMALLDATETIME | Date | X |
DateUnsubscribed | This is the date when the subscriber unsubscribed from the list. | SMALLDATETIME | Date | X |
EmailAddress | The email address of the subscriber. | NVARCHAR(254) | Text | X |
ListID | The unique list ID number for the list that was used in the send. | INT | Number | X |
ListName | The name of the list ID that includes the subscribers. | VARCHAR(50) | Text | X |
ListType | Shows the list type, whether a list or a group. - Publication list - Suppression list - List - Group - FTAF list - Unknown | VARCHAR(16) | Text | |
Status | The subscriber status. | VARCHAR(12) | Text | X |
SubscriberID | A unique subscriber ID for the subscriber and each subscriber record. | INT | Number | X |
SubscriberKey | The subscriber key of the subscriber. | NVARCHAR(254) | Text | X |
SubscriberType | The subscriber type. | VARCHAR(100) | Text | X |
SQL Query
SELECT subscriberkey, Status, AddedBy, AddMethod, CreatedDate, DateUnsubscribed, EmailAddress, ListID, ListName, ListType, SubscriberID, SubscriberType FROM [_listsubscribers]
Data View: Open
Query the _Open SFMC Data View to view data about email opens in the email sends executed in your Salesforce Marketing Cloud account.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The unique ID number of your account. | INT | Number | |
OYBAccountID | This is the account ID number for On-Your-Behalf accounts. Applies only to enterprise accounts. | INT | Number | X |
JobID | The unique job ID number of the email send in SFMC. | INT | Number | |
ListID | This is the unique list ID number for the list that was used in the send. | INT | Number | |
BatchID | The batch ID number of any batches that were used in the send. | INT | Number | |
SubscriberID | Unique Subscriber ID for each subscriber and subscriber record. | INT | Number | |
SubscriberKey | The Subscriber Key of the subscriber. | NVARCHAR(254) | Text | |
EventDate | This field is the date when the open happened. | DATETIME | Date | |
Domain | This is the domain at which the open took place. | VARCHAR(128) | Text | |
IsUnique | Indicates whether the open event is unique or a repeated one. | BOOL | Boolean | X |
TriggererSendDefinitionObjectID | The unique object ID for the triggered send definition of the send. | VARCHAR(36) | Text | X |
TriggeredSendCustomerKey | This is the unique customer key for the triggered send in SFMC. | VARCHAR(36) | Text | X |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, Domain, IsUnique, TriggererSendDefinitionObjectID, TriggeredSendCustomerKey FROM [_Open]
Data View: Sent
Query the _Sent SFMC Data View to view data about email sends in your SFMC account and also to find subscribers who were sent your emails. It stores records up to 6 months ago.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The unique account ID number for your account. | INT | Number | |
OYBAccountID | The unique account ID number On-Your-Behalf accounts. Applies only to Enterprise accounts. | INT | Number | X |
JobID | This field is the unique job ID number for the email send in SFMC. | INT | Number | |
ListID | The unique list ID number associated with the list used in the send. | INT | Number | |
BatchID | The batch ID number for any batches that were used in the SFMC send. | INT | Number | |
SubscriberID | This field is unique for each subscriber record. The subscriber ID of the subscriber. | INT | Number | |
SubscriberKey | The Subscriber Key of the subscriber. | NVARCHAR(254) | Text | |
EventDate | This is the date when the send happened. | DATETIME | Date | |
Domain | This is the domain at which the SFMC send took place. | VARCHAR(128) | Text | |
TriggererSendDefinitionObjectID | The unique object ID for the triggered send definition of the send. | VARCHAR(36) | Text | X |
TriggeredSendCustomerKey | The unique customer key for the triggered send of the send. | VARCHAR(36) | Text | X |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, Domain, TriggererSendDefinitionObjectID, TriggeredSendCustomerKey FROM [_Sent]
Data View: SMSMessageTracking
Query the _SMSMessageTracking SFMC Data View to view message tracking information from the SMS sends in your Salesforce Marketing Cloud MobileConnect. You can view your SMS send and receive history. The information in this data view relates to the owner of a private long or short code, or it can also be linked to a client using a shared short code, if the subscriber has opted in for it.
How to identify the details of an SMS message you’d like to check:
1. Click the message name to retrieve the MessageID.
2. On the Message Overview tab, note the URL.
3. Copy the Base64 encoded text.
4. Use an online Base64 decoder to decode the Base64 text.
5. The result will appear in a format like this: 61:72:0 4. Based on this, the MessageID would be: 61
Considerations:
– All dates and times are stored in Central Standard Time (Daylight Savings Time not observed).
– A static GUID is used for Code and KEYWORD values.
– For shared codes, this data view only shows the delivery status 2000 to confirm delivery to the aggregator. The delivery receipts of shared codes are returned to the SFMC account that owns the code.
– View and query the _SMSSubscriptionLog data view for OptIn, OptOut and Unsubscription data.
– Keep in mind that there are cases where MobileConnect does not have a SubscriberID (e.g: when a number texts STOP, a record is created but no contact is created).
– If the Journey Builder activity is not named, then the content name acts as the activity name.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
MobileMessageTrackingID | This is a unique ID for tracking information associated with message sent. | BIGINT | Number | |
EID | This is the unique enterprise ID number of the sender. | BIGINT | Number | X |
MID | The unique Member ID. | BIGINT | Number | X |
Mobile | This field is the subscriber's Mobile Number. | VARCHAR(15) | Phone | |
MessageID | This is the unique Mobile message ID. | INT | Number | |
KeywordID | This field is the unique identifier for the KEYWORD. | UNIQUEIDENTIFIER | Text | X |
CodeID | The SMS code unique identifier. | UNIQUEIDENTIFIER | Text | X |
ConversationID | Not used any more (this is a legacy field). | UNIQUEIDENTIFIER | Text | X |
ConversationStateID | This field is a unique identifier that correlates mobile originated messages (MO messages) and mobile terminated messages (MT messages) in a single conversation. | UNIQUEIDENTIFIER | Text | X |
CampaignID | The ID of the SMS campaign associated to the record. | INT | Number | X |
Sent | Indicates whether the message was sent or not. 1 = TRUE, 0 = FALSE. | TINYINT | Boolean | |
Delivered | Indicates whether the message was delivered or not. 1 = TRUE, 0 = FALSE. | Boolean | X | |
Undelivered | Flag that indicates whether the message was delivered successfully or not. 1 = TRUE, 0 = FALSE. | BIT | Boolean | X |
Outbound | Flag that indicates if the message was outgoing or not. 1 = TRUE, 0 = FALSE. | BIT | Boolean | X |
Inbound | Flag that indicates whether the message was incoming or not. 1 = TRUE, 0 = FALSE. | BIT | Boolean | X |
CreateDateTime | This is the datetime (Central Standard Time ) when the tracking record was created. | SMALLDATETIME | Date and time | |
ModifiedDateTime | The datetime (Central Standard Time) when the tracking record was modified. | SMALLDATETIME | Date and time | |
ActionDateTime | This field is the specific date and time when a delivery or non-delivery event was triggered in SFMC. Note: the timestamps for delivery receipts of this data view and field do not represent when a message was actually received on a handset. The SMS industry doesn’t support read receipts. Thus, this data is based on when the delivery receipt is returned to the SFMC system. | SMALLDATETIME | Date and Time | |
MessageText | This field is the text of the message sent. | NVARCHAR(160) | Text | X |
IsTest | Indicates whether the message was a test message or not. 1 = TRUE, 0 = FALSE. | BIT | Boolean | |
MobileMessageRecurrenceID | The ID for the recurring schedule for the message. Note: this field can be used for troubleshooting issues with sends. | BIGINT | Number | X |
ResponseToMobileMessageTrackingID | This is the unique tracking ID of the response to the message sent. | BIGINT | Number | X |
IsValid | Indicates whether the message is valid or not. 1 = TRUE, 0 = FALSE. | BIT | Boolean | X |
InvalidationCode | The invalidation code for the message. | SMALLINT | Number | X |
SendID | The unique send ID number of the SMS send. | BIGINT | Number | X |
SendSplitID | If the message was split, this field indicates the ID of the split. | BIGINT | Number | X |
SendSegmentID | The unique ID of the segment associated with the message. | BIGINT | Number | X |
SendJobID | The unique job ID for the SMS send in SFMC. | BIGINT | Number | X |
SendGroupID | The unique group ID associated with the SMS send. | BIGINT | Number | X |
SendPersonID | This is the unique sendperson ID of the SMS send. | BIGINT | Number | X |
SubscriberID | The unique subscriber ID of a subscriber (represents a unique ID for each subscriber record). | BIGINT | Number | X |
SubscriberKey | The Subscriber Key of the subscriber. | NVARCHAR(254) | Text | X |
SMSStandardStatusCodeId | This is the SFMC code for delivery status. | INT | Number | X |
Description | The description of the status code. | NVARCHAR | Text | X |
Name | The name of the message. | NVARCHAR | Text | X |
ShortCode | The long or short code that was used to send the message Note: this column isn’t populated for MT sends and inbound ones. View the associated CodeID column. | NVARCHAR | Text | X |
SharedKeyword | This is the KEYWORD that was used in the message. | NVARCHAR | Text | X |
Ordinal | This number indicates the different parts in a multi-part message. Ascending order, starting at 0. | TINYINT | Number | X |
FromName | This is the From Name that was used for an individual message. Note: maximum length = 11 characters. | NVARCHAR | Text | X |
JBActivityID | This is the unique identifier for the journey activity that triggered and deployed the message. | UNIQUEIDENTIFIER | Text | X |
JBDefinitionID | This is the unique identifier for the specific journey in which a message was triggered and deployed. | UNIQUEIDENTIFIER | Text | X |
SMSJobID | This attribute is a unique identifier for each SMS job that is triggered in SFMC MobileConnect. | UNIQUEIDENTIFIER | unique identifier | X |
SMSBatchID | This ID identifies a batch associated with the send of an SMS. | BIGINT | bigint | X |
SQL Query
SELECT MobileMessageTrackingID, EID, MID, Mobile, MessageID, KeywordID, CodeID, ConversationID, ConversationStateID, CampaignID, Sent, Delivered, Undelivered, Outbound, Inbound, CreateDateTime, ModifiedDateTime, ActionDateTime, MessageText, IsTest, MobileMessageRecurrenceID, ResponseToMobileMessageTrackingID, IsValid, InvalidationCode, SendID, SendSplitID, SendSegmentID, SendJobID, SendGroupID, SendPersonID, SubscriberID, SubscriberKey, SMSStandardStatusCodeId, Description, Name, ShortCode, SharedKeyword, Ordinal, FromName, JBActivityID, JBDefinitionID, SMSJobID, SMSBatchID FROM [_smsmessagetracking]
Data View: Social Network Impressions
Query the _SocialNetworkImpressions SFMC Data View to view impression data of content that has been shared from your Salesforce Marketing Cloud sends via the Social Forward action.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
JobID | The unique job ID number of the email send in SFMC. | BIGINT | Number | |
ListID | The unique list ID number given to the list that was used in the send. | INT | Number | |
RegionTitle | This is the title of the region shared via Social Forward. | VARCHAR | Text | |
RegionDescription | This field is the description of the region shared via Social Forward. | VARCHAR | Text | X |
RegionHTML | This is the HTML linked to the social forward region. | VARCHAR | Text | |
ContentRegionID | The unique ID for region of the shared content. | INT | Number | |
SocialSharingSiteID | The ID of the social network where the sharing of the content region took place. | INT | Number | |
SiteName | Contains the name of the social network where the sharing of the content region took place. | VARCHAR | Text | |
CountryCode | The unique country code of the social network where the sharing of the content region happened. | VARCHAR | Text | |
ReferringURL | The referring URL that was used in the Social Forward activity. | VARCHAR | Text | X |
IPAddress | This field is the IP address of the URL from which the content region is shared. | VARCHAR(50) | Text | X |
TransactionTime | The time when the sharing of the content area took place. | DATETIME | Date | |
PublishedSocialContentStatusID | This field is the status ID for the social content published. | VARCHAR | Text | |
ShortCode | A short code of the status of the published social content. Possible values: - Active - Inactive - Deleted | VARCHAR | Text | |
PublishTime | The time when the social content area was published. | DATETIME | Date |
SQL Query
SELECT JobID, ListID, RegionTitle, RegionDescription, RegionHTML, ContentRegionID, SocialSharingSiteID, SiteName, CountryCode, ReferringURL, IPAddress, TransactionTime, PublishedSocialContentStatusID, ShortCode, PublishTime FROM [_SocialNetworkImpressions]
Data View: Social Network Tracking
Query the _SocialNetworkTracking SFMC Data View to view tracking data on content that was shared from your sends via the Social Forward action.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
SubscriberID | The unique subscriber ID of each subscriber record. | INT | Number | |
SubscriberKey | The Subscriber Key of the subscriber. | NVARCHAR(254) | ||
ListID | The unique list ID number given to the list that was used in the send. | INT | Number | |
BatchID | The batch ID number for batches that were used in the send. | BIGINT | Number | |
SocialSharingSiteID | The ID of the social network that was used for the sharing of the content region. | INT | Number | |
SiteName | This is the name of the social network where the sharing of the content region took place. | VARCHAR | Text | |
CountryCode | This is the unique country code of the social network where the sharing of the content region took place. | VARCHAR | Text | |
PublishedSocialContentID | The unique ID number of the content area published via the Social Forward. | VARCHAR | Text | |
RegionTitle | The title of the region that was shared via the Social Forward. | VARCHAR | Text | |
RegionDescription | This field contains the description of the region shared via the Social Forward. | VARCHAR | Text | X |
RegionHTML | This is the HTML linked to the social forward region. | VARCHAR | Text | X |
ContentRegionID | The unique ID of the shared content region. | VARCHAR | Text | X |
OYBMemberID | The unique ID number for On-Your-Behalf (OYB) account involved in sharing the content area. | INT | Number | X |
TransactionTime | This is the time when the sharing of the content area took place. | DATETIME | Date | |
IsUnique | Indicates whether the event is unique or not. 1 = TRUE, 0 = FALSE. | BIT | Boolean | |
Domain | This is the domain from which the sharing of the content took place. | VARCHAR | Text | |
PublishedSocialContentStatusID | This field is the status ID of the social content published. | VARCHAR | Text | |
ShortCode | The short code that represents the status of the published social content. Possible values: - Active - Inactive - Deleted | VARCHAR | Text | |
PublishTime | The time when the social content area was published. | DATETIME | Date |
SQL Query
SELECT SubscriberID, SubscriberKey, ListID, BatchID, SocialSharingSiteID, SiteName, CountryCode, PublishedSocialContentID, RegionTitle, RegionDescription, RegionHTML, ContentRegionID, OYBMemberID, TransactionTime, IsUnique, Domain, PublishedSocialContentStatusID, ShortCode, PublishTime FROM [_SocialNetworkTracking]
Data View: Subscribers
Query the _Subscribers SFMC Data View to find subscribers and their status in your Salesforce Marketing Cloud account. Subscriber attributes are not available in this data view. Also, this data view only returns results at the Enterprise level, not the Business Unit level. The SubscriberID, EmailAddress and SubscriberKey columns are indexed in this table.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
SubscriberID | The unique subscriber ID of each subscriber record. | BIGINT | Number | |
DateUndeliverable | This is the date when an email sent to the subscriber was returned as undeliverable. | SMALLDATETIME | Date | X |
DateJoined | The date when the subscriber subscribed to the list. | SMALLDATETIME | Date | X |
DateUnsubscribed | The date when the subscriber unsubscribed from the list. | SMALLDATETIME | Date | X |
Domain | The subscriber's domain. | NVARCHAR(254) | Text | X |
EmailAddress | The email address of the subscriber. | NVARCHAR(254) | ||
BounceCount | This number is the total number of bounces accumulated by the subscriber. | SMALLINT | Number | |
SubscriberKey | A potential alternative key to identify subscribers. Default value is Email Address. | NVARCHAR(254) | Text | |
SubscriberType | The subscriber type of the subscriber: 0 = ExactTarget (Marketing Cloud), Salesforce Lead, Salesforce Contact 3 = Unknown External System (Triggered Send Hidden Manage List) 4 = MS CRM Contact 5 = MS CRM Lead 6 = MS CRM Account | VARCHAR(100) | Text | |
Status | The status of the subscriber. Possible values: - active - unsubscribed - held - bounced | VARCHAR(12) | Text | X |
Locale | The subscriber's locale error | INT | Locale | X |
SQL Query
SELECT SubscriberID, SubscriberKey, DateUndeliverable, DateJoined, DateUnsubscribed, Domain, EmailAddress, BounceCount, SubscriberType, Status, Locale FROM Ent.[_Subscribers]
Data View: SMSSubscriptionLog
Query the _SMSSubscriptionLog SFMC Data View to find subscription log data from Salesforce Marketing Cloud MobileConnect SMS sends. For instance, you can find all the mobile numbers subscribed to a specific keyword, the history subscription log, etc.
This Data View stores more than 6 months of data.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
LogDate | This is the date when the subscription is logged. | DATETIME | Date | X |
SubscriberKey | The subscriber key of the subscriber. | NVARCHAR | Text | |
MobileSubscriptionID | The unique ID for the subscription record. | BIGINT | Number | |
SubscriptionDefinitionID | The KEYWORD ID | UNIQUEIDENTIFIER | Text | |
MobileNumber | The Mobile Number of the subscriber. | NVARCHAR | Phone | |
OptOutStatusID | Indicates whether the subscriber is opted out of SMS messages. View key for possible values. | TINYINT | Number | X |
OptOutMethodID | Indicates the method which the subscriber used to opt out of SMS messages. View key for possible values. | TINYINT | Number | X |
OptOutDate | The date when the subscriber opted out of SMS messages. | DATE | Date | X |
OptInStatusID | Indicates whether the subscriber is opted in to SMS messages. View key for possible values. | TINYINT | Number | |
OptInMethodID | The method which the subscriber used to opt in to SMS messages. View key for possible values. | BIT | Number | X |
OptInDate | The date when the subscriber opted in to SMS messages. | DATE | Date | X |
Source | The source of the subscription. | TINYINT | Number | X |
CreatedDate | The date of the message creation. | DATE | Date | |
ModifiedDate | The date of the message modification. | DATE | Date |
OptOutStatusID KeyOptOutMethodID Key:
0 = Unspecified
1 = WebCollect
2 = API
3 = FTAF
4 = Import
5 = MoveCopy
6 = Application
7 = SalesForce
8 = Segmentation
9 = GenericExtension
10 = CustomObject
11 = RMM
13 = ServiceFeedback
14 = MobileOriginated (CustomStopKeyword)
15 = ContactsSuppression
18 = MobileOriginated (GlobalStopKeyword)
OptInStatusID Key:
0 = NotOptedIn
1 = OptInPending
2 = OptedIn
10 = DeletingNotOptedIn
11 = DeletingOptInPending
12 = DeletingOptedIn
OptInMethodID Key:
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 Key:
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
SQL Query
SELECT LogDate, SubscriberKey, MobileSubscriptionID, SubscriptionDefinitionID, MobileNumber, OptOutStatusID, OptOutMethodID, OptOutDate, OptInStatusID, OptInMethodID, OptInDate, Source, CreatedDate, ModifiedDate FROM [_SMSSubscriptionLog]
Data View: SurveyResponse
Query the _SurveyResponse SFMC Data View to view the response data to surveys that were sent from your Salesforce Marketing Cloud account. The table stores data up to last 6 months at the query run time.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The unique ID number of the account. | INT | Number | |
OYBAccountID | The unique account ID number for On-Your-Behalf accounts. Applies only to Enterprise accounts. | INT | Number | X |
JobID | The unique job ID number of the email send in SFMC. | INT | Number | |
ListID | The unique list ID number given to the list that was used in the send. | INT | Number | |
BatchID | The batch ID number for batches that were used in the send. | INT | Number | |
SubscriberID | The unique Subscriber ID for each subscriber record. | INT | Number | |
SubscriberKey | The Subscriber Key of the subscriber. | NVARCHAR(254) | Text | |
EventDate | This is the date when the survey response happened. | DATETIME | Date | |
Domain | This is the domain where the survey response took place. | VARCHAR(128) | Text | |
SurveyID | This field is the unique ID of the survey. | INT | Number | |
SurveyName | The survey name, | VARCHAR(100) | Text | |
IsUnique | Indicates whether the response is unique or not. 1 = TRUE, 0 = FALSE. | INT | Number | |
QuestionID | The unique ID of the survey question. | INT | Number | |
QuestionName | The survey question name. | VARCHAR(50) | Text | |
Question | This contains the survey question. | VARCHAR(4000) | Text | |
AnswerID | The unique ID of the answer. | INT | Number | |
AnswerName | The Answer name. | VARCHAR(4000) | Text | X |
Answer | This is the boolean answer for the survey question. | VARCHAR(4000) | Text | X |
AnswerData | This contains the text content of the survey response. | NVARCHAR(MAX) | Text | X |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, Domain, SurveyID, SurveyName, IsUnique, QuestionID, QuestionName, Question, AnswerID, AnswerName, Answer, AnswerData FROM [_SurveyResponse]
Data View: Undeliverable SMS
Query the _UndeliverableSms SFMC Data View to access data about failed message deliveries to Marketing Cloud MobileConnect subscribers. The data in this table relates to the owner of a private long or short code, or the client using a shared code if a subscriber has opted in to it.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
MobileNumber | This is the Subscriber's Mobile Number. | VARCHAR(15) | Phone | |
Undeliverable | This flag indicates that this subscriber's number is Held. SFMC MobileConnect stopped sending messages to the subscriber. | BIT | Boolean | |
BounceCount | This number is the total bounced SMS messages for this subscriber. | SMALLINT | Number | |
FirstBounceDate | This is the date when the first bounce for the subscriber took place. | DATETIME | Date | |
HoldDate | This is the date when SFMC MobileConnect stopped sending messages to the subscriber. | DATETIME | Date | X |
SQL Query
SELECT MobileNumber, Undeliverable, BounceCount, FirstBounceDate, HoldDate FROM [_UndeliverableSms]
Data View: Unsubscribe
Query the _Unsubscribe SFMC Data View to access data about unsubscriptions from email lists in your Salesforce Marketing Cloud account. This table stores records up to 6 months ago.
Dates and Times are stored in Central Standard Time. Daylight Savings Time is not observed.
For new Profile Attributes in Enterprise 2.0 accounts, when a new attribute is created, the new column is added to the _EnterpriseAttribute data view. Data View queries in Enterprise 2.0 accounts can return profile attribute results together with the columns in this table.
Data View Columns
NAME | DESCRIPTION | DATA TYPE | DATA EXTENSION DATA TYPE | NULLABLE |
---|---|---|---|---|
AccountID | The unique account ID number of your account. | INT | Number | |
OYBAccountID | The unique account ID number for On-Your-Behalf accounts. Applies only to Enterprise accounts. | INT | Number | x |
JobID | This is the unique job ID number of the email send in SFMC. | BIGINT | Number | |
ListID | The unique list ID number given to the list that was used in the send. | INT | Number | |
BatchID | The batch ID number for the batches that were used in the SFMC send. | BIGINT | Number | |
SubscriberID | The unique Subscriber ID number for each subscriber record. | INT | Number | |
SubscriberKey | The Subscriber Key of the subscriber. | NVARCHAR(254) | Text | |
EventDate | This is the date when the unsubscribe happened. | DATETIME | Date | |
IsUnique | Indicates whether the unsubscribe event is unique or a repeated one. 1 = TRUE, 0 = FALSE. | BIT | Boolean | |
Domain | The unsubscribe domain. | VARCHAR(128) | Text |
SQL Query
SELECT AccountID, OYBAccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, IsUnique, Domain FROM [_Unsubscribe]