Data Views are the metadata table of Salesforce Marketing Cloud data. In simple words, they are the system-generated data of events like email and SMS sends, email opens, link clicks and user subscribe or unsubscribe events.
Data Views also hold user data such as forwarded emails, the status of your subscribers, and the bounce rate of a particular email. These data points get stored in different tables.
You may have used a few Data Views before – but, do you know that there are 23 different Data Views currently available in Marketing Cloud? The image below, from the sfmarketing.cloud blog, represents these Data Views more clearly:
Image source: Data Views in Salesforce Marketing Cloud, by Zuzanna Jarczynska. High-quality PDF available by visiting their article.
This other guide explains more about Data Views, and 5 essential Data Views you should know.
Now, let’s talk about some of the uncommon Data Views to give your skills a boost – and how you can use SQL Queries to access them.
Using BusinessUnitUnsubscribes Data View, you can find the unsubscriber of 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.
Using the Journey Data Views, you can check the Journey’s status, the creation date, the last modified date, and other general journey information.
Using UndeliverableSMS Data View, you can find the Marketing Cloud MobileConnect Subscribers’ messages that did not get delivered.
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.
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.
6. Bounce Magical Data View
During our research on Data Views, we came across this Stack Exchange answer, which talks about two Data Views that have not been mentioned in the official Documentation yet.
If you too are curious about these Data Views, Click Here.
Access Data Views: SQL Query Example
These dataviews are not accessible from the Marketing Cloud UI. Instead, you can access them by running an SQL query on these tables using an SQL Activity in the Automation Studio, or an Ampscript lookup.
Here is an example to get the list of all the subscribers who click on an email sent from your Marketing Cloud account. For this, we need the Job ID of the sent email (suppose the Job ID is ’13’ for this example).
The details are in the _Open Data View.
1. Create a Data Extension with the column names below, to store the data.
There are a few more columns available in the Data Views that we can use for this. We can add or remove these columns, based on the data you require.
2. 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:
FROM _OPEN WHERE JobID = 13;”
3. When we run this query, the results will appear in the Data Extension.
One important thing that you should know is that these Data Views only store the information from the past six months.
You can refer to the Salesforce Help documentation to get the column names of these Data Views.