Marketing Cloud Data views are system-generated tables of events like email and SMS sends, or view behavioral information such as email opens, emails forwarded, the status of your subscribers, and more.
These data points get stored in these metadata tables (data extensions). Data views are somewhat hidden in Marketing Cloud, and you can’t just look at them like data extensions – you can only leverage them using SQL or Ampscript lookups.
You may have used a few Data Views before – but, do you know that there are currently 25 Data Views available in Marketing Cloud? The image below, from the sfmarketing.cloud blog, represents these Data Views in a visual format:
Access Data Views: SQL Query Example
To find the information you need in data views, you can create an SQL query in Automation Studio or by using an Ampscript lookup.
Example: You want to find subscribers who have bounced or subscribers who haven’t opened the email in the last 14 days. You can find the example of the SQL query where the data view _Sent is used:
Note: The distinction between a data extension and a data view is that data views always start with an underscore. If you want to query them, this is the correct way to find one.
Step 1: Create a Data Extension
Create a Data Extension with the column names below, to store the data.
- SubscriberKey
- AccountID
- JobID
- ListID
- BatchID
- EventDate
There are a few more columns available in the Data Views that we can use for this. Refer to the Salesforce Help documentation to get the column names of these Data Views. We can add or remove these columns, based on the data you require.
Step 2: Create an SQL Activity in Automation Studio
Create an SQL Activity in Automation Studio using the query below. While creating the SQL activity, we will select the Data Extension that we have created:
” SELECT
SubscriberKey,
AccountID,
JobID,
ListID,
BatchID,
EventDate
FROM _OPEN WHERE JobID = 13;”
Step 3: Run the Query
When we run this query, the results will appear in the Data Extension.
Note: These Data Views only store information from the past six months.
Common Marketing Cloud Data Views
1. _Subscribers
Find out which accounts are subscribers in SFMC, as well as know their status.
2. _Click
Find data on email clicks from your campaign in SFMC.
3. _Bounce
Find data on bounces from your email campaigns in Salesforce Marketing Cloud.
4. _Open
Contains the information about the email opens in SFMC.
5. _Sent
Find the list of subscribers the communication was sent to from your Salesforce Marketing Cloud account.
Uncommon Marketing Cloud Data Views
Now, let’s talk about some of the uncommon Data Views to give your skills a boost…
6. _BusinessUnitUnsubscribes
Using BusinessUnitUnsubscribes Data View, you can find the unsubscribers in each Business unit. You can even track the timing of the unsubscribing event and the reason mentioned by the user. However, there is a drawback in using this Data View; it can only run on the parent account.
7. _Journey
Using the Journey Data Views, you can check the Journey’s status, the creation date, the last modified date, and other general journey information.
8. _UndeliverableSms
Using UndeliverableSMS Data View, you can find the Marketing Cloud MobileConnect Subscribers’ messages that did not get delivered.
9. _FTAF
Using the FTAF Data View, you can check the information related to the emails that your subscribers are forwarding. Also, you can check the email forward timing and the domain name of the receiver’s email address.
10. _Complaint
Using the Complaint Data View, you can view the data regarding spam complaints from subscribers about the emails sent out from your Marketing Cloud account. You can check the subscriber id, complaint time, domain name, and other information.
Summary
We think that every email marketer working with Marketing Cloud can easily benefit from better understanding data views. The information in them can be very useful when segmenting your audience for future campaigns, as well as extracting information for reporting purposes.
We hope this article has sparked your interest in leveraging data views in Marketing Cloud!
Comments: