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
Olá!
Não há um “mapa” de data views com Mobile Push?
Estou precisando entender algumas data views que contenham o dado de opt in em push / mobile push e coisas relacionadas!
—————————————————————————————————————————
Hi!
Isn’t there a “map” of data views with Mobile Push?
I need to understand some data views that contain the opt in data in push / mobile push and related things!
LikeLike
Hi 🙂
Great page 🙂 Can you add to the schema new columns for SMSMessageTracking? Now we have JBActivityID and JBDefinitionID available and we are able to join with Journey and JourneyActivity data views!
LikeLike
Will do! 😊
LikeLike
Hi, Is it possible to find the users who clicked on a specific headline(as of now there are 4 headlines in my email) in a newsletter email without adding a link to headline. or please suggest how we can track. Thanks in advance.
LikeLike
Hi Rajesh, no, this cannot be tracked without adding an external link (a link that would take your subscriber outside of the email)
LikeLike
Thank you so much for your quick reply and apologies for my late reply. One more question: I ave an API EVENT. Defined and event rest endpoint , we are getting data into our de. But the email
Address is coming with lot of trailing spaces , so mail is not going to subscribers even when subscribers are active. Is there a way to trim the data from sfmc side anytime the data is inserting into a DE. Please advise. Thanks
LikeLike
Hi ,
How can i find number of unsubscribes for emails deployed in Journey. Is a proper count possible?
LikeLike
Unengaged subscribers (no opens or clicks) in the last 7 days
I am getting the following error in Query Studio “Error saving the Query field.Incorrect syntax near ‘–'”
SELECT DISTINCT
s.SubscriberKey,
j.EmailName
FROM _Sent s
LEFT JOIN _Job j
ON s.JobID = j.JobID
LEFT JOIN _Open o
ON s.JobID = o.JobID AND s.ListID = o.ListID AND s.BatchID = o.BatchID AND s.SubscriberID = o.SubscriberID AND o.IsUnique = 1
LEFT JOIN _Click c
ON s.JobID = c.JobID AND s.ListID = c.ListID AND s.BatchID = c.BatchID AND s.SubscriberID = c.SubscriberID AND c.IsUnique = 1
WHERE
s.EventDate > dateadd(d,–7,getdate())
AND (o.SubscriberID is NULL AND c.SubscriberID is NULL)
LikeLike
Can you give it a try in Automation Studio?
LikeLike
Tried it in automation Studio…..When I click Validate Syntax I get the following message
An error occurred while checking the query syntax. Errors: Incorrect syntax near ‘–’.
LikeLike
It worked 🙂
LikeLike