Data Views in Salesforce Marketing Cloud

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)

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

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

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)=%%

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

Query Studio for Salesforce Marketing Cloud

The ability to write and automate SQL queries in Automation Studio is an indispensable feature when it comes to working with data in Salesforce Marketing Cloud. The current SQL query editor in Automation Studio is very rudimentary and makes it almost impossible to debug SQL queries. It can be very time consuming and frustrating to work on more complicated queries as each time your run your query, you need to go to Data Extensions to check the results.

Salesforce has decided to make things a bit easier for Marketing Cloud users and launched a new app last month called Query Studio for Marketing Cloud. The app allows you to quickly run queries with a similar experience to SQL Server Studio or MySQL workbench.

You can get Query Studio for Marketing Cloud for free from Salesforce AppExchange and install in a couple of clicks. After you install the app, you need to log out and log back in, to be able to access it from the App Exchange menu:

The user interface of Query Studio is quite simple:

  • From the Query search box at the top, you can choose an existing query that you wish to run, or you can write your own query in the query editor window.
  • Click the Run button or use the keyboard shortcut CTRL + Enter to run your query – you will see a timer next to the Run button, showing you how long the query takes to execute.
  • You can also validate your query to check if query results can be inserted into target data extension – once you execute your query, choose a target data extension from the Data Extension search box, choose Data Action and click on Validate Data.

The validation function, according to Salesforce, will cross check for required fields, field lengths, and mismatched field data types. I ran a few tests and made a few mistakes on purpose to see how the validation feature works. It’s nice to see a description of the validation error in the results, but if you have more than one error, you will have to correct them one by one, as the validation tool only displays the first error it encounters:

Unfortunately, the Query Studio is still missing a debugging feature, which would show syntax errors in your query:

It is worth mentioning that Query Studio creates a new folder in your Data Extensions folder, called “QueryStudioResults”:

QueryStudioResults data extension folder will be created in your account to hold temporary data extensions, which have data retention set to auto-delete after 24 hours. Each time you run a query in Query Studio, a new Data Extension is created.

Every Query Studio user will also have a query activity created in Automation Studio for them, which is used for running one-off queries. It will contain ‘InteractiveQuery’ in the name:

Overall, the new Query Studio is great for developing new queries, when you want to see the results on-screen and be able to test and amend your queries quickly. I would say that this is a satisfying minimum viable product, which will hopefully evolve into a full-on SQL Console with proper debugging support in the future.

Read more and install here: https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FP3yFUAT

Related article: Data Views in Salesforce Marketing Cloud