Data Views in Salesforce Marketing Cloud

On By Zuzanna JarczynskaIn Marketing Cloud Data Management

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

You can also bookmark my new website, dataviews.io, which is an online version of the above visualization.

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

30 thoughts on “Data Views in Salesforce Marketing Cloud”

  4. Calil

    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!

    Like

    Reply
  5. Maciej Barnaś

    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!

    Like

    Reply
  6. Rajesh

    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.

    Like

    Reply
      1. Rajesh

        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

        Like

  8. Aliraza Ali

    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)

    Like

    Reply
      1. Aliraza Ali

        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 ‘–’.

        Like

  10. Pingback: 5 Uncommon Marketing Cloud Data Views | THE DRIP

  11. Pingback: Marketing Cloud system Data Views integration - Activation Studio

  12. Pingback: New Automation Studio Data Views and Updated Data Views visualization – sfmarketing.cloud

  13. PG

    Hi i’m Querying unengaged customer data from past 6 months for journey DE,i want to use FirstName in Journey DE inorder to use it in email , so how can i get FirstName when i’m Querying Dataview

    Like

    Reply
      1. PG

        thanks for the resposne Zuzanna, in my case i’m Creating a DE and Querying _ListSubscribers joining the _Sent to find out last 6 months unengaged customer and using the same DE for Journey as a entry Data extension.
        can i use SFMC profile attribute in email to display FirstName ? will that work

        Like

      2. I’d actually use AMPscript together with personalization strings to pull the first name from profile attributes and have a backup value like “Customer” in case the first name is not present:

        %%[IF Empty(firstname) == “False” THEN SET @FN = firstname ELSE SET @FN = “Customer” ENDIF]%%

        Hi %%=v(@FN)=%%

        Like

  15. Pingback: Data Views App: Display Data Views data in real-time on a CloudPage – sfmarketing.cloud

  16. Pingback: Marketing Cloud Data Views: 10 Data Views You Should Know | Salesforce Ben

  18. Weslei Carvalho Santos

    Hi, Huys

    Does anyone know how to connect _smsmessagetracking _journey and _journey_activity. I would like to know the journey_name performance to sms message?

    Liked by 2 people

    Reply

Leave a comment