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

	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)

view raw unengaged-subscribers-last-7-days.sql hosted with ❤ by GitHub

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:

	SELECT
	s.SubscriberKey,
	j.EmailName,
	s.EventDate as SentDate,
	o.EventDate as OpenDate,
	c.EventDate as ClickDate,
	b.EventDate as BounceDate,
	b.BounceCategory,
	u.EventDate as UnsubscribeDate
	FROM _Sent s LEFT JOIN _Job as 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
	LEFT JOIN _Bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1
	LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID and s.ListID = u.ListID and s.BatchID = u.BatchID and s.SubscriberID = u.SubscriberID and u.IsUnique = 1

view raw generic-tracking-query.sql hosted with ❤ by GitHub

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”:

	SELECT
	o.SubscriberKey,
	ea.Gender
	FROM _Open o
	INNER JOIN ENT._EnterpriseAttribute ea ON o.SubscriberID = ea._SubscriberID
	WHERE ea.Gender = 'Female'
	AND o.EventDate > dateadd(d,–7,getdate())

view raw profile-attributes.sql hosted with ❤ by GitHub

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:

	%%[
	var @email, @skey
	set @skey = AttributeValue("_subscriberkey")
	set @email = Lookup("_Subscribers","EmailAddress","SubscriberKey", @skey)
	]%%
	Email Address: %%=v(@email)=%%

view raw lookup-data-views.amp hosted with ❤ by GitHub

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.