Data Views are a powerful feature of Salesforce Marketing Cloud. They store subscriber information and the last six months of tracking data for your account. While they cannot be accessed or viewed from the UI, they can be used like any other data extension: you can write a query against a data view in Automation Studio or in the new Query Studio and populate the results into a new data extension. You can join them with each other and with other data extensions. You can also reference them in AMPscript, for example in a Lookup function. Remember, that in order to be able to use Data Views, you first need to ask support to enable them in your account.
Data Views provide the data and metrics that can help you drive your analytics efforts to the top. Understanding how they relate to each other is key to getting the maximum value from your Marketing Cloud data. The official Data Views documentation provides details about (almost) all available Data Views, table names, fields names and data types. The below visualization shows how the most important of the tables are related to each other (click on the image to enlarge it or download a high-quality PDF here).
Data Views in SQL queries
If you’re looking for some example SQL queries for use with Data Views, visit the SQL Reference. You can basically join any of the email-related Data Views, as all of them have SubscriberKey, SubscriberID and JobID fields. Here’s an example:
Unengaged subscribers (no opens or clicks) in the last 7 days
Here’s an example of a very generic query, that will pull all the tracking data from Data Views into one table. All you need to do, is to create a Data Extension with the following fields:
SubscriberKey
EmailName
SentDate
OpenDate
ClickDate
BounceDate
BounceCategory
UnsubscribeDate
and run the following query:
In order to combine the data about your subscribers kept in the Profile Attributes with any of the Data Views, you will need to join them on SubscriberID with the EnterpriseAttribute data view. If you are running your query outside the parent business unit, it has to be used with the ENT.
prefix. Here’s an example of a query that will pull all subscribers who opened an email in the last 7 days and their gender, stored in a Profile Attribute, is “Female”:
It’s a good idea to mirror Data Views in your account if you need access to more than the last 6 months of tracking data. This can be easily achieved in Automation Studio, where you can create a scheduled automation to regularly run SQL queries on Data Views and populate results into a data extension, here’s an example: Track Email Status logs using Data Views.
Data Views in AMPscript
If you want to use Data Views in AMPscript, you can reference them like any other data extension, for example:
Hidden Data Views
As mentioned in the beginning, almost all Data Views are documented, but there are at least four, which you won’t find in the official documentation. Two of them hold data related to Mobile Connect and you can access them by referencing _MobileAddress and _MobileSubscription. The other two hold data related to Mobile Push and their names are _PushAddress and _PushTag. Below is a full list of their attributes, courtesy of the Salesforce Stack Exchange community:
_MobileAddress
Field Name | Data Type | Required | Data Default |
_ContactID | Text | Y | |
_MobileNumber | Text(15) | Y | |
_Status | Text | N | |
_Source | Text | N | |
_SourceObjectId | Text(200) | N | |
_Priority | Text | N | 1 |
_Channel | Text(20) | N | |
_CarrierID | Text | Y | 0 |
_CountryCode | Text(2) | Y | |
_CreatedDate | Date | Y | GETDATE() |
_CreatedBy | Text | N | |
_ModifiedDate | Date | Y | GETDATE() |
_ModifiedBy | Text | N | |
_City | Text(200) | N | |
_State | Text(200) | N | |
_ZipCode | Text(20) | N | |
_FirstName | Text(100) | N | |
_LastName | Text(100) | N | |
_UTCOffset | Decimal(4,2) | N | 0 |
_IsHonorDST | Boolean | N | false |
_MobileSubscription
Field Name | Data Type | Required | Data Default |
_SubscriptionDefinitionID | Text(200) | Y | |
_MobileNumber | Text(15) | Y | |
_OptOutStatusID | Text | N | |
_OptOutMethodID | Text | N | |
_OptOutDate | Date | N | |
_OptInStatusID | Text | Y | |
_OptInMethodID | Text | N | |
_OptInDate | Date | N | |
_Source | Text | N | |
_CreatedDate | Date | Y | GETDATE() |
_CreatedBy | Text | N | |
_ModifiedDate | Date | Y | GETDATE() |
_ModifiedBy | Text | N |
_PushAddress
Field Name | Data Type | Required | Data Default |
_ContactID | Text | Y | |
_DeviceID | Text(200) | Y | |
_APID | Text(38) | Y | |
_Status | Text | N | |
_Source | Text | N | |
_SourceObjectId | Text(200) | N | |
_Platform | Text(100) | N | |
_PlatformVersion | Text(100) | N | |
_Alias | Text(100) | N | |
_OptOutStatusID | Text | N | |
_OptOutMethodID | Text | N | |
_OptOutDate | Date | N | |
_OptInStatusID | Text | Y | 0 |
_OptInMethodID | Text | N | |
_OptInDate | Date | N | |
_Channel | Text(20) | N | |
_CreatedDate | Date | Y | GETDATE() |
_CreatedBy | Text | N | |
_ModifiedDate | Date | Y | GETDATE() |
_ModifiedBy | Text | N | |
_City | Text(200) | N | |
_State | Text(200) | N | |
_ZipCode | Text(20) | N | |
_FirstName | Text(100) | N | |
_LastName | Text(100) | N | |
_UTCOffset | Decimal(4,2) | N | 0 |
_IsHonorDST | Boolean | N | false |
_SystemToken | Text(4000) | N | |
_ProviderToken | Text(200) | N | |
_Badge | Number | N | |
_LocationEnabled | Boolean | N | |
_TimeZone | Text(50) | N | |
_Device | Text(100) | N | |
_HardwareId | Text(100) | N | |
_DeviceType | Text(20) | N |
_PushTag
Field Name | Data Type | Required | Data Default |
_DeviceID | Text(200) | Y | |
_APID | Text(38) | Y | |
_Value | Text(128) | N | |
_CreatedDate | Date | Y | GETDATE() |
_CreatedBy | Text | N | |
_ModifiedDate | Date | Y | GETDATE() |
_ModifiedBy | Text | N |
Sources: https://salesforce.stackexchange.com/questions/73127/how-to-bulk-export-mobileconnect-sms-subscriptions, https://salesforce.stackexchange.com/questions/174179/attributes-in-pushaddress-and-pushtag-data-views/174180#174180
If you’re looking for more information about Data Views and SQL queries in Marketing Cloud, check out the official documentation, topics related to Data Views on Salesforce Stack Exchange and Adam’s post about Troubleshooting Queries in SFMC.
Hi, could you explain to me why are you ussing o.isunique=1 on the join condition?
LikeLike
isunique=1 means that the event counts as a single event – unique opens, unique clicks etc. If isunique=0 (by default) you will receive just opens including those opened multiple times by the same subscriber.
LikeLike
Hi
Could you tell me if it’s possible to get back a click on sms message via Data view ?
Thanks
LikeLike
This data views table visual is so great!
LikeLike