Salesforce Marketing Cloud Data Views

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

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

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
MemberIDThe unique ID of the Business Unit in SFMC.BIGINTNumber
FilenameFromTriggerFor Trigger and File Drop automations, the name of the file that triggered the start of the automation run.NVARCHAR(4000)TextX
AutomationTypeThe type of Automation, based on starting source. Possible values are File Drop, Trigger, Scheduled.VARCHAR(9)Text
AutomationStepCountThe total number of steps in the automation.INTNumber
AutomationNotificationRecipient_SkipThe email address for notifications of the automation run being skipped.NVARCHAR(500)TextX
AutomationNotificationRecipient_ErrorThe email address for notifications of errored automation.NVARCHAR(500)TextX
AutomationNotificationRecipient_CompleteThe email address for notifications of automation completion.NVARCHAR(500)TextX
AutomationNameThe name of the Automation.NVARCHAR(400)Text
AutomationInstanceStatusThe 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_UTCThe UTC time that the automation run began. NULL if the automation run was skipped.DATETIMEOFFSETDateX
AutomationInstanceScheduledTime_UTCThe UTC time that the run was scheduled to start. For Scheduled Automations.DATETIMEOFFSETDateX
AutomationInstanceIsRunOnceWhether the automation was configured to run once or not. TRUE = 1, FALSE = 0.BITBoolean
AutomationInstanceIDThe unique ID of the automation run.UNIQUEIDENTIFIERText
AutomationInstanceEndTime_UTCThe UTC time that the automation run finished. NULL if the automation is still running or the run was skipped.DATETIMEOFFSETDateX
AutomationInstanceActivityErrorDetailsThe 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)TextX
AutomationDescriptionThe description of the Automation.NVARCHAR(400)TextX
AutomationCustomerKeyThe 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

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
MemberIDThe Business Unit unique ID.BIGINTNumber
AutomationNameThe name of the Automation.NVARCHAR(400)Text
AutomationCustomerKeyThe unique ID of the automation in SFMC.NVARCHAR(400)Text
AutomationInstanceIDThe unique ID for the automation run.UNIQUEIDENTIFIERText
ActivityTypeThe type of the activity. Possible values are listed below, in the Activity Type IDs table.INTNumber
ActivityNameThe name of the activity.NVARCHAR(400)Text
ActivityDescriptionThe description of the activity.NVARCHAR(400)TextX
ActivityCustomerKeyThe unique ID of the activity in SFMC.NVARCHAR(400)Text
ActivityInstanceStepWhere the activity takes place in the automation sequence. E.g: 1.2 is step 1, activity 2.VARCHAR(25)Text
ActivityInstanceIDThe unique ID of the run of the activity.UNIQUEIDENTIFIERText
ActivityInstanceStartTime_UTCThe UTC time when the activity run started. NULL if the run was skipped.DATETIMEOFFSETDateX
ActivityInstanceEndTime_UTCThe 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.DATETIMEOFFSETDateX
ActivityInstanceStatusThe 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
ActivityInstanceStatusDetailsThe error log message, if applicable. If the error is unclassified or system, message displays System Error.NVARCHAR(4000)TextX

Data View Activity Types

ACTIVITY TYPE IDACTIVITY NAME
33SMS Activity
42Send Email
43Import File
45Refresh Group
53File Transfer
73Data Extract
84Report Definition
300SQL Query
303Filter
423Script
425Data Factory Utility Activity
426Refresh Segment Template
427Publish Audience
467Wait
724Refresh Mobile Filtered List
725Send SMS
726Import Mobile Contacts
733Journey Builder Event Activity
736Send Push
749Fire Event
771Salesforce Email Send
772Mobile Connect Send Salesforce Sync Subscriber
783Send GroupConnect
952Journey Builder Event Activity
1000Verification
1010Interaction Studio Data
1101Interactions
1701Batch Personalization
3700Contact 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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe ID number of your SFMC account.INTNumber
OYBAccountIDApplies to enterprise accounts only. The account ID number for On-Your-Behalf accounts. INTNumberX
JobIDThe job ID, unique number for the email send.BIGINTNumber
ListIDThe list ID number for the list that was used in the email send.INTNumber
BatchIDThe batch ID number for any batches that were used in the email send.BIGINTNumber
SubscriberIDThe subscriber ID of the bounced subscriber. This is a unique ID for each subscriber record.INTNumber
SubscriberKeyThe subscriber key for the bounced subscriber. The Primary Key.NVARCHAR(254)Text
EventDateThe date when the bounce took place. In Central Standard Time.DATETIMEDate
IsUniqueIndicates whether the event is unique or a repeated one.BITBoolean
DomainThe domain at which the bounce took place.VARCHAR(128)Text
BounceCategoryIDThe unique ID number for the category of the bounce.SMALLINTNumber
BounceCategoryThe bounce category.NVARCHAR(50)TextX
BounceSubcategoryIDThe unique ID number for the bounce subcategory.SMALLINTNumberX
BounceSubcategoryThe bounce subcategory.NVARCHAR(50)TextX
BounceTypeIDThe unique ID number for the bounce typeSMALLINTNumber
BounceTypeThe type of bounce that took place.NVARCHAR(50)TextX
SMTPBounceReasonThe reason for the bounce, as returned by the mail system.NVARCHAR(MAX)TextX
SMTPMessageThe mail system message about the bounce.NVARCHAR(MAX)TextX
SMTPCodeThe mail system error code for the bounce.SMALLINTNumberX
TriggererSendDefinitionObjectIDThe unique object ID for the triggered send definition of the send.VARCHAR(36)TextX
TriggeredSendCustomerKeyThe customer key for the triggered send.VARCHAR(36)TextX
IsFalseBounceIndicates a false bounce. TRUE = 1, FALSE = 0.BITBooleanX

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

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
BusinessUnitIDThe Account ID or MID number for the Business Unit (BU).BIGINTNumber
SubscriberIDThe unique subscriber ID for the subscriber. Unique ID for each subscriber record.BIGINTNumber
SubscriberKeyA possible alternative key to identify subscribers. Defaults to the value of Email Address.VARCHAR(254)Text
UnsubDateUTCThe UTC date when the subscriber unsubscribed from the Business Unit.SMALLDATETIMEDateX
UnsubReasonThe unsubscribe reason listed is a custom value configured by the admin of the account.VARCHAR(100)TextX

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

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe unique account ID number of the Marketing Cloud account.INTNumber
OYBAccountIDOnly applies to Enterprise accounts. The account ID number for On-Your-Behalf (OYB) accounts. INTNumberX
JobIDThe unique job ID number generated for the email send.BIGINTNumber
ListIDThe unique list ID number for the list that was used in the email send.INTNumber
BatchIDThe unique batch ID number for any batches that were used in the email send.BIGINTNumber
SubscriberIDThe subscriber ID for the subscriber. This number is a unique ID for each subscriber record.INTNumber
SubscriberKeyThe subscriber key of the subscriber.NVARCHAR(254)Text
EventDateThe date when the click happened.DATETIMEDate
DomainThe domain at which the click took place.VARCHAR(128)Text
URLThe URL of the link clicked. AMPScript or variables are not returned in this column. E.g: www.example.com?%%attribute%%VARCHAR(900)TextX
LinkNameThe link name that was given in the email send.VARCHAR(1024)TextX
LinkContentThe link content given in the email send. AMPscript and variables are returned and populated in this field. E.g: www.example.com?12345VARCHAR(MAX)TextX
IsUniqueIndicates 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.BOOLEANBoolean
TriggererSendDefinitionObjectIDThe unique object ID for the triggered send definition in SFMC.UNIQUEIDENTIFIERTextX
TriggeredSendCustomerKeyThe unique customer key for the triggered send in SFMC.VARCHAR(36)TextX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe unique account ID number.INTNumber
OYBAccountIDThe unique account ID for On-Your-Behalf (OYB) accounts. Applied only to enterprise accounts.INTNumberX
JobIDThe unique job ID number for the email send in SFMC.BIGINTNumber
ListIDThe unique list ID number for the list that was used for the SFMC send.INTNumber
BatchIDThe unique batch ID number for the batches that were used in the SFMC send.BIGINTNumber
SubscriberIDThe unique subscriber ID for the subscriber. INTNumber
SubscriberKeyThe subscriber key of the subscriber.NVARCHAR(254)Text
EventDateThe date when the complaint happened.DATETIMEDate
IsUniqueIndicates whether the event is unique or a repeated one.BITBoolean
DomainThe 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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
NameThis is the name of the coupon.nvarchar(128)Text
ExternalKeyThis is the unique external key used to point at your coupon via API.nvarchar(36)Text
DescriptionThe description of the coupon.varcharText
BeginDateThe date when the coupon is valid.datetimeDate
ExpirationDateThe date when the coupon becomes invalid.datetimeDate

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
_SubscriberIDThe unique subscriber ID for the subscriber. This is a unique number ID for each subscriber record.BIGINTNumber

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe unique account ID number for your account.INTNumber
OYBAccountIDThe account ID number for On-Your-Behalf accounts. Applies only to enterprise accounts.INTNumberX
JobIDThis is the unique job ID number for the email send in SFMC.INTNumber
ListIDThis is the unique list ID number for the list that was used in the send in SFMC.INTNumber
BatchIDThe batch ID number for any batches that were used in the send.INTNumber
SubscriberIDThe unique subscriber ID number for the subscriber. INTNumber
SubscriberKeyThe subscriber key of the subscriber.NVARCHAR(254)Text
TransactionTimeThis is the date when the forward to a friend happened.DATETIMEDate
DomainThe domain at which the forward to a friend took place.VARCHAR(128)Text
IsUniqueThis field indicates whether the event is unique or a repeated one.BITBoolean
TriggererSendDefinitionObjectIDThe unique object ID for the triggered send definition of the send.UNIQUEIDENTIFIERTextX
TriggeredSendCustomerKeyThe unique customer key for this triggered send.VARCHAR(36)TextX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
ChannelIDThis is the unique channel ID of the LINE account.NVARCHARText
ContactIDThis is the Marketing Cloud ContactId (ID generated by system) that is linked to the LINE UID.BIGINTNumber
ContactKeyThe unique Marketing Cloud Contact Key linked to the LINE UID. This is system generated.NVARCHARText
AddressIDThis is the unique LINE UID.NVARCHARText
IsActiveA boolean flag to indicate if active or not. 1 = TRUE, 0 = FALSE.BITNumber
CreatedDateThe date and time of record creation in Central Standard Time. The date when the customer started following the LINE account.DATETIMEDate
ModifiedDateThe date and time of record modification in Central Standard Time.DATETIMEDateX

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

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
ContactIDThe unique Marketing Cloud ContactID linked to the LINE UID. This is system generated.BIGINTNumber
ContactKeyThe 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.NVARCHARText
AddressIDThe unique LINE UID.NVARCHARText
CreatedDateThe Date and Time of record creation (in Central Standard Time). The Date when the customer started following the LINE account.DATETIMEDate

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
JobIDThe unique job ID number for the email send in SFMC.INTNumber
EmailIDThis is the unique email ID for the job.INTNumberX
AccountIDThe unique ID number for the account that executed this job.INTNumberX
AccountUserIDThe unique ID number of the user that performed the send job.INTNumberX
FromNameThe from name used in the email send in SFMC.NVARCHAR(130)TextX
FromEmailThe from email address used in the email send in SFMC.VARCHAR(100)EmailX
SchedTimeThis is the time when the send job was scheduled.SMALLDATETIMEDateX
PickupTimeThis is the time when the Marketing Cloud application started the send job.SMALLDATETIMEDateX
DeliveredTimeThis is the time when the email was delivered.SMALLDATETIMEDateX
EventIDThe unique ID for the event of the job.VARCHAR(50)TextX
IsMultipartThis field indicates whether the send job was sent as a multipart MIME or not.BITBoolean
JobTypeThe job type.VARCHAR(50)TextX
JobStatusThe job status.VARCHAR(50)TextX
ModifiedByIf applicable, the SFMC user who modified the job.INTNumberX
ModifiedDateThe date when the job was modified.DATETIMEDateX
EmailNameThe name of the email sent by the send job.CHAR(100)TextX
EmailSubjectThe email subject of the email send for the job.NCHAR(200)TextX
IsWrappedThis field indicates whether the links in the email were wrapped for tracking or not.BITBoolean
TestEmailAddrThe test email address that was used in the send job.VARCHAR(128)EmailX
CategoryThe category of the job.VARCHAR(100)Text
BccEmailIf applicable, the email address configured in the BCC field for the send.VARCHAR(100)EmailX
OriginalSchedTimeThis field indicates the original scheduled time for the send job in SFMC.SMALLDATETIMEDateX
CreatedDateThis is the date when the send job was created in SFMC.SMALLDATETIMEDate
CharacterSetThe character set that was used in this send job.VARCHAR(30)TextX
IPAddressThis field always has a NULL value.VARCHAR(50)TextX
SalesForceTotalSubscriberCountThis field is the total number of Salesforce Subscribers that were included in the job.INTNumber
SalesForceErrorSubscriberCountThis field is the total number of Salesforce Subscribers that were included in the job and received an error.INTNumber
SendTypeThe send type that was used in this job.VARCHAR(128)Text
DynamicEmailSubjectIf applicable, the dynamic email subject added to the job.NTEXTTextX
SuppressTrackingThis field indicates whether the tracking information for this job was suppressed from logs or not.BITBoolean
SendClassificationTypeThe send classification type that was configured for the send job.NVARCHAR(32)TextX
SendClassificationThe send classification that was used for the job.NVARCHAR(36)TextX
ResolveLinksWithCurrentDataThis field indicates whether the job resolved links with current data or not.BITBoolean
EmailSendDefinitionThe email send definition that was used in the job.NVARCHAR(36)TextX
DeduplicateByEmailThis field indicates whether the email addresses in the job are used to deduplicate subscribers or not.BITBoolean
TriggererSendDefinitionObjectIDThe unique object ID for the triggered send definition.UNIQUEIDENTIFIERnullX
TriggeredSendCustomerKeyThe unique customer key for the triggered send.VARCHAR(36)TextX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
VersionIDThis field is a unique identifier for the version of the journey.UNIQUEIDENTIFIER(36)Text
JourneyIDThis is the unique identifier for the journey. There can be multiple VersionIDs associated to a unique JourneyID.UNIQUEIDENTIFIER(36)Text
JourneyNameThe journey name.NVARCHAR(200)Text
VersionNumberThis is the number of the version of the journey.INTNumber
CreatedDateThis is the date when the version of the journey was created.DATETIMEDate
LastPublishedDateThis is the date when the version of the journey was published last.DATETIMEDateX
ModifiedDateThe date when the journey was last modified.DATETIMEDate
JourneyStatusThe 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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
VersionIDThis is a unique identifier for the journey version.UNIQUEIDENTIFIER(36)Text
ActivityIDA unique identifier for the activity in the journey. There can be multiple ActivityIDs associated with a single VersionID of a journey.UNIQUEIDENTIFIER(36)Text
ActivityNameThis field is the name of the journey activity.NVARCHAR(200)TextX
ActivityExternalKeyThe unique external key given to the journey activity.NVARCHAR(200)Text
JourneyActivityObjectIDA 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)TextX
ActivityTypeThe journey activity type.NVARCHAR(512)TextX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AddedByThis is the ID of the SFMC user who executed the process which added the subscriber.INTNumber
AddMethodThe adding method for the subscriber. This includes:
- Webcollect
- API
- Unspecified
- FTAF
- Application
- Salesforce
- Segmentation
- Custom Object
- Generic Extension
- Import
- Move
- Copy
VARCHAR(17)Text
CreatedDateThis is the date when the subscriber was added to the list.SMALLDATETIMEDateX
DateUnsubscribedThis is the date when the subscriber unsubscribed from the list.SMALLDATETIMEDateX
EmailAddressThe email address of the subscriber.NVARCHAR(254)TextX
ListIDThe unique list ID number for the list that was used in the send.INTNumberX
ListNameThe name of the list ID that includes the subscribers.VARCHAR(50)TextX
ListTypeShows the list type, whether a list or a group.

- Publication list
- Suppression list
- List
- Group
- FTAF list
- Unknown
VARCHAR(16)Text
StatusThe subscriber status.VARCHAR(12)TextX
SubscriberIDA unique subscriber ID for the subscriber and each subscriber record. INTNumberX
SubscriberKeyThe subscriber key of the subscriber.NVARCHAR(254)TextX
SubscriberTypeThe subscriber type.VARCHAR(100)TextX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe unique ID number of your account.INTNumber
OYBAccountIDThis is the account ID number for On-Your-Behalf accounts. Applies only to enterprise accounts.INTNumberX
JobIDThe unique job ID number of the email send in SFMC.INTNumber
ListIDThis is the unique list ID number for the list that was used in the send.INTNumber
BatchIDThe batch ID number of any batches that were used in the send.INTNumber
SubscriberIDUnique Subscriber ID for each subscriber and subscriber record.INTNumber
SubscriberKeyThe Subscriber Key of the subscriber.NVARCHAR(254)Text
EventDateThis field is the date when the open happened.DATETIMEDate
DomainThis is the domain at which the open took place.VARCHAR(128)Text
IsUniqueIndicates whether the open event is unique or a repeated one.BOOLBooleanX
TriggererSendDefinitionObjectIDThe unique object ID for the triggered send definition of the send.VARCHAR(36)TextX
TriggeredSendCustomerKeyThis is the unique customer key for the triggered send in SFMC.VARCHAR(36)TextX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe unique account ID number for your account.INTNumber
OYBAccountIDThe unique account ID number On-Your-Behalf accounts. Applies only to Enterprise accounts.INTNumberX
JobIDThis field is the unique job ID number for the email send in SFMC.INTNumber
ListIDThe unique list ID number associated with the list used in the send.INTNumber
BatchIDThe batch ID number for any batches that were used in the SFMC send.INTNumber
SubscriberIDThis field is unique for each subscriber record. The subscriber ID of the subscriber.INTNumber
SubscriberKeyThe Subscriber Key of the subscriber.NVARCHAR(254)Text
EventDateThis is the date when the send happened.DATETIMEDate
DomainThis is the domain at which the SFMC send took place.VARCHAR(128)Text
TriggererSendDefinitionObjectIDThe unique object ID for the triggered send definition of the send.VARCHAR(36)TextX
TriggeredSendCustomerKeyThe unique customer key for the triggered send of the send.VARCHAR(36)TextX

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

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
MobileMessageTrackingIDThis is a unique ID for tracking information associated with message sent.BIGINTNumber
EIDThis is the unique enterprise ID number of the sender.BIGINTNumberX
MIDThe unique Member ID.BIGINTNumberX
MobileThis field is the subscriber's Mobile Number.VARCHAR(15)Phone
MessageIDThis is the unique Mobile message ID.INTNumber
KeywordIDThis field is the unique identifier for the KEYWORD.UNIQUEIDENTIFIERTextX
CodeIDThe SMS code unique identifier.UNIQUEIDENTIFIERTextX
ConversationIDNot used any more (this is a legacy field).UNIQUEIDENTIFIERTextX
ConversationStateIDThis field is a unique identifier that correlates mobile originated messages (MO messages) and mobile terminated messages (MT messages) in a single conversation.UNIQUEIDENTIFIERTextX
CampaignIDThe ID of the SMS campaign associated to the record. INTNumberX
SentIndicates whether the message was sent or not. 1 = TRUE, 0 = FALSE.TINYINTBoolean
DeliveredIndicates whether the message was delivered or not. 1 = TRUE, 0 = FALSE.BooleanX
UndeliveredFlag that indicates whether the message was delivered successfully or not. 1 = TRUE, 0 = FALSE.BITBooleanX
OutboundFlag that indicates if the message was outgoing or not. 1 = TRUE, 0 = FALSE.BITBooleanX
InboundFlag that indicates whether the message was incoming or not. 1 = TRUE, 0 = FALSE.BITBooleanX
CreateDateTimeThis is the datetime (Central Standard Time ) when the tracking record was created.SMALLDATETIMEDate and time
ModifiedDateTimeThe datetime (Central Standard Time) when the tracking record was modified.SMALLDATETIMEDate and time
ActionDateTimeThis 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.
SMALLDATETIMEDate and Time
MessageTextThis field is the text of the message sent.NVARCHAR(160)TextX
IsTestIndicates whether the message was a test message or not. 1 = TRUE, 0 = FALSE.BITBoolean
MobileMessageRecurrenceIDThe ID for the recurring schedule for the message.

Note: this field can be used for troubleshooting issues with sends.
BIGINTNumberX
ResponseToMobileMessageTrackingIDThis is the unique tracking ID of the response to the message sent.BIGINTNumberX
IsValidIndicates whether the message is valid or not. 1 = TRUE, 0 = FALSE.BITBooleanX
InvalidationCodeThe invalidation code for the message.SMALLINTNumberX
SendIDThe unique send ID number of the SMS send.BIGINTNumberX
SendSplitIDIf the message was split, this field indicates the ID of the split.BIGINTNumberX
SendSegmentIDThe unique ID of the segment associated with the message.BIGINTNumberX
SendJobIDThe unique job ID for the SMS send in SFMC.BIGINTNumberX
SendGroupIDThe unique group ID associated with the SMS send.BIGINTNumberX
SendPersonIDThis is the unique sendperson ID of the SMS send.BIGINTNumberX
SubscriberIDThe unique subscriber ID of a subscriber (represents a unique ID for each subscriber record).BIGINTNumberX
SubscriberKeyThe Subscriber Key of the subscriber.NVARCHAR(254)TextX
SMSStandardStatusCodeIdThis is the SFMC code for delivery status.INTNumberX
DescriptionThe description of the status code.NVARCHARTextX
NameThe name of the message.NVARCHARTextX
ShortCodeThe 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.
NVARCHARTextX
SharedKeywordThis is the KEYWORD that was used in the message.NVARCHARTextX
OrdinalThis number indicates the different parts in a multi-part message. Ascending order, starting at 0.TINYINTNumberX
FromNameThis is the From Name that was used for an individual message.

Note: maximum length = 11 characters.
NVARCHARTextX
JBActivityIDThis is the unique identifier for the journey activity that triggered and deployed the message.UNIQUEIDENTIFIERTextX
JBDefinitionIDThis is the unique identifier for the specific journey in which a message was triggered and deployed.UNIQUEIDENTIFIERTextX
SMSJobIDThis attribute is a unique identifier for each SMS job that is triggered in SFMC MobileConnect.UNIQUEIDENTIFIERunique identifierX
SMSBatchIDThis ID identifies a batch associated with the send of an SMS.BIGINTbigintX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
JobIDThe unique job ID number of the email send in SFMC.BIGINTNumber
ListIDThe unique list ID number given to the list that was used in the send.INTNumber
RegionTitleThis is the title of the region shared via Social Forward.VARCHARText
RegionDescriptionThis field is the description of the region shared via Social Forward.VARCHARTextX
RegionHTMLThis is the HTML linked to the social forward region.VARCHARText
ContentRegionIDThe unique ID for region of the shared content.INTNumber
SocialSharingSiteIDThe ID of the social network where the sharing of the content region took place.INTNumber
SiteNameContains the name of the social network where the sharing of the content region took place.VARCHARText
CountryCodeThe unique country code of the social network where the sharing of the content region happened.VARCHARText
ReferringURLThe referring URL that was used in the Social Forward activity.VARCHARTextX
IPAddressThis field is the IP address of the URL from which the content region is shared.VARCHAR(50)TextX
TransactionTimeThe time when the sharing of the content area took place.DATETIMEDate
PublishedSocialContentStatusIDThis field is the status ID for the social content published.VARCHARText
ShortCodeA short code of the status of the published social content.

Possible values:
- Active
- Inactive
- Deleted
VARCHARText
PublishTimeThe time when the social content area was published.DATETIMEDate

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
SubscriberIDThe unique subscriber ID of each subscriber record.INTNumber
SubscriberKeyThe Subscriber Key of the subscriber.NVARCHAR(254)Email
ListIDThe unique list ID number given to the list that was used in the send.INTNumber
BatchIDThe batch ID number for batches that were used in the send.BIGINTNumber
SocialSharingSiteIDThe ID of the social network that was used for the sharing of the content region.INTNumber
SiteNameThis is the name of the social network where the sharing of the content region took place.VARCHARText
CountryCodeThis is the unique country code of the social network where the sharing of the content region took place.VARCHARText
PublishedSocialContentIDThe unique ID number of the content area published via the Social Forward.VARCHARText
RegionTitleThe title of the region that was shared via the Social Forward.VARCHARText
RegionDescriptionThis field contains the description of the region shared via the Social Forward.VARCHARTextX
RegionHTMLThis is the HTML linked to the social forward region.VARCHARTextX
ContentRegionIDThe unique ID of the shared content region.VARCHARTextX
OYBMemberIDThe unique ID number for On-Your-Behalf (OYB) account involved in sharing the content area.INTNumberX
TransactionTimeThis is the time when the sharing of the content area took place.DATETIMEDate
IsUniqueIndicates whether the event is unique or not. 1 = TRUE, 0 = FALSE.BITBoolean
DomainThis is the domain from which the sharing of the content took place.VARCHARText
PublishedSocialContentStatusIDThis field is the status ID of the social content published.VARCHARText
ShortCodeThe short code that represents the status of the published social content.

Possible values:
- Active
- Inactive
- Deleted
VARCHARText
PublishTimeThe time when the social content area was published.DATETIMEDate

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
SubscriberIDThe unique subscriber ID of each subscriber record.BIGINTNumber
DateUndeliverableThis is the date when an email sent to the subscriber was returned as undeliverable.SMALLDATETIMEDateX
DateJoinedThe date when the subscriber subscribed to the list.SMALLDATETIMEDateX
DateUnsubscribedThe date when the subscriber unsubscribed from the list.SMALLDATETIMEDateX
DomainThe subscriber's domain.NVARCHAR(254)TextX
EmailAddressThe email address of the subscriber.NVARCHAR(254)Email
BounceCountThis number is the total number of bounces accumulated by the subscriber.SMALLINTNumber
SubscriberKeyA potential alternative key to identify subscribers. Default value is Email Address.NVARCHAR(254)Text
SubscriberTypeThe 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
StatusThe status of the subscriber.

Possible values:
- active
- unsubscribed
- held
- bounced
VARCHAR(12)TextX
LocaleThe subscriber's locale errorINTLocaleX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
LogDateThis is the date when the subscription is logged.DATETIMEDateX
SubscriberKeyThe subscriber key of the subscriber.NVARCHARText
MobileSubscriptionIDThe unique ID for the subscription record.BIGINTNumber
SubscriptionDefinitionIDThe KEYWORD IDUNIQUEIDENTIFIERText
MobileNumberThe Mobile Number of the subscriber.NVARCHARPhone
OptOutStatusIDIndicates whether the subscriber is opted out of SMS messages. View key for possible values.TINYINTNumberX
OptOutMethodIDIndicates the method which the subscriber used to opt out of SMS messages. View key for possible values.TINYINTNumberX
OptOutDateThe date when the subscriber opted out of SMS messages.DATEDateX
OptInStatusIDIndicates whether the subscriber is opted in to SMS messages. View key for possible values.TINYINTNumber
OptInMethodIDThe method which the subscriber used to opt in to SMS messages. View key for possible values.BITNumberX
OptInDateThe date when the subscriber opted in to SMS messages.DATEDateX
SourceThe source of the subscription.TINYINTNumberX
CreatedDateThe date of the message creation.DATEDate
ModifiedDateThe date of the message modification.DATEDate

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe unique ID number of the account.INTNumber
OYBAccountIDThe unique account ID number for On-Your-Behalf accounts. Applies only to Enterprise accounts.INTNumberX
JobIDThe unique job ID number of the email send in SFMC.INTNumber
ListIDThe unique list ID number given to the list that was used in the send.INTNumber
BatchIDThe batch ID number for batches that were used in the send.INTNumber
SubscriberIDThe unique Subscriber ID for each subscriber record.INTNumber
SubscriberKeyThe Subscriber Key of the subscriber.NVARCHAR(254)Text
EventDateThis is the date when the survey response happened.DATETIMEDate
DomainThis is the domain where the survey response took place.VARCHAR(128)Text
SurveyIDThis field is the unique ID of the survey.INTNumber
SurveyNameThe survey name,VARCHAR(100)Text
IsUniqueIndicates whether the response is unique or not. 1 = TRUE, 0 = FALSE.INTNumber
QuestionIDThe unique ID of the survey question.INTNumber
QuestionNameThe survey question name.VARCHAR(50)Text
QuestionThis contains the survey question.VARCHAR(4000)Text
AnswerIDThe unique ID of the answer.INTNumber
AnswerNameThe Answer name.VARCHAR(4000)TextX
AnswerThis is the boolean answer for the survey question.VARCHAR(4000)TextX
AnswerDataThis contains the text content of the survey response.NVARCHAR(MAX)TextX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
MobileNumberThis is the Subscriber's Mobile Number.VARCHAR(15)Phone
UndeliverableThis flag indicates that this subscriber's number is Held. SFMC MobileConnect stopped sending messages to the subscriber.BITBoolean
BounceCountThis number is the total bounced SMS messages for this subscriber.SMALLINTNumber
FirstBounceDateThis is the date when the first bounce for the subscriber took place.DATETIMEDate
HoldDateThis is the date when SFMC MobileConnect stopped sending messages to the subscriber.DATETIMEDateX

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.

Data View Columns

NAMEDESCRIPTIONDATA TYPEDATA EXTENSION DATA TYPENULLABLE
AccountIDThe unique account ID number of your account.INTNumber
OYBAccountIDThe unique account ID number for On-Your-Behalf accounts. Applies only to Enterprise accounts.INTNumberx
JobIDThis is the unique job ID number of the email send in SFMC.BIGINTNumber
ListIDThe unique list ID number given to the list that was used in the send.INTNumber
BatchIDThe batch ID number for the batches that were used in the SFMC send.BIGINTNumber
SubscriberIDThe unique Subscriber ID number for each subscriber record.INTNumber
SubscriberKeyThe Subscriber Key of the subscriber.NVARCHAR(254)Text
EventDateThis is the date when the unsubscribe happened.DATETIMEDate
IsUniqueIndicates whether the unsubscribe event is unique or a repeated one. 1 = TRUE, 0 = FALSE.BITBoolean
DomainThe unsubscribe domain.VARCHAR(128)Text

SQL Query

SELECT  AccountID,
        OYBAccountID,
        JobID,
        ListID,
        BatchID,
        SubscriberID,
        SubscriberKey,
        EventDate,
        IsUnique,
        Domain
FROM [_Unsubscribe]