Data Views Marketing Cloud Integration

We are happy to announce that we have integrated the Marketing Cloud data views directly into the Segment Designer. For now we have included _Subscribers, _Sent, _Open, _Bounce, _Click and _Unsubscribe. In feature releases we will also include the _Job and Journey Builder data views.

This feature makes it possible for marketers to include the data views when creating a segment. And combine data view values with other data available in the Segment Designer. For example;

• include all subscriber who have received an email in the last month, didn’t engage with the content and are part of a sales opportunity
• exclude subscribers who opened an email in last week and who purchased something on your website in the last month

View how data views have been integrated in the video below.

What are Marketing Cloud system data views?

Marketing Cloud stores certain data in data views such as bounce, sent, open and click data. There are more Marketing Cloud data views for Journey Builder activities and also for automations. Data View in Marketing Cloud can be queried via a Query Activity.

SQL example all tracking data Marketing Cloud data views

Below an example of a SQL query to get all tracking data from all relevant system Data Views. Example provided by sfmarketing.cloud. Fields included in example are:

• SubscriberKey
• EmailName
• SentDate
• OpenDate
• ClickDate
• BounceDate
• BounceCategory
• UnsubscribeDate

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