Data Views

Data is a collection of information that is arranged for further use. Similarly in Salesforce marketing cloud we can get various information using data views.

Data views are 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 the new Query Studio and populate the results into a new data extension.

Although, Salesforce Marketing cloud provides various data views. Here, we will discuss most frequently used data views.

1.  _Sent: Query this data view in Automation or Query studio to find the sent emails to subscribers from your Marketing Cloud account.
2. _Open: contains the information about the email opens in SFMC.
3. _Click: find data on email clicks from your campaign in SFMC.
4. _Bounce: find data on bounces from your email campaigns in Salesforce Marketing Cloud.
5. _Complaint: Salesforce Marketing Cloud provides a data view to view complaints data related to emails from our accounts. By querying this data view, we can get spam complaints from our subscribers about our email sends.
6. _Job: Query this data view in Automation or Query Studio to find data on Marketing Cloud email send jobs. Job data view is the only BU-specific data view – and has no reference to subscribers. Hence you will only see the jobs which were performed from the BU in which you query it. 

There are few point to keep in mind while query data view.

• While query data view in child BU you need to prepend them with ENT.
• The distinction between a data extension and a data view is that data views always start with an underscore(_).

How to create Data Extension from Data Views?

As I mentioned above, data views cannot be accessed or viewed from the UI, we need to create automation to fetch the data to use further.

Below are the steps to retrieve and save data from Data view to Data extension.

Step 1: Prepare Data Extension– Create different data extensions that will be storing data of sent, open, click, bounce, complaint and job  data views with all the  fields you need as per your business requirement. Make sure the attributes name and data type in target data extension should be same as data view you are querying, although you can select number of attributes according to your need.

Step 2: Create an Automation  –Next, we will create an automation, which will be populating the data extension with sent data view records.

Since, we are creating 5 SQL Query for 5 types of data views here we need to use JOIN in SQL Query (Join is used when you need to combine information from multiple data points.)

For example, we want EventDate, SubscriberKey, JobID from the _Sent data view and FromName, FromEmail, DeliveredTime, EmailName, and EmailSubject of same subscriber from _Job data view, we need to use JOIN keyword.

We will be using below queries in our automation to get the records.

SQL Query for _Sent:

Select s.EventDate, s.SubscriberKey, s.JobID, j.FromName, j.FromEmail, j.DeliveredTime, j.EmailName, j.EmailSubject 

from [_Job] j Inner Join [_Sent] s 

on j.JobID = s.JobID

SQL Query for _Open:

Select o.EventDate, j.FromName, o.SubscriberKey, o.JobID, j.FromEmail, j.DeliveredTime, j.EmailName, j.EmailSubject

from [_Job] j

Inner Join [_Open] o

on j.JobID = o.JobID

SQL Query for _Click:

Select cl.EventDate, j.FromName,cl.SubscriberKey, cl.JobID, j.FromEmail, j.DeliveredTime, j.EmailName, j.EmailSubject

from [_Job] j

Inner Join [_Click] cl

on j.JobID = cl.JobID

SQL Query for _Complaint:

Select c.EventDate, j.FromName,c.SubscriberKey, c.JobID, j.FromEmail, j.DeliveredTime, j.EmailName, j.EmailSubject
from [_Job] j
Inner Join [_Complaint] c
on j.JobID = c.JobID

SQL Query for _Bounce:

Select EventDate, SubscriberKey, JobID, BounceCategory, SMTPBounceReason
from _Bounce

Now we will proceed further to make automation, 

• Go to automation studio, click new automation button.
• Select Starting source as Schedule. The schedule defines the time frame in which this query is supposed to run in automation.
• From activities select SQL query and drag it in panel as shown in image.

• Click on the Choose button and click on the Create New Query Activity button.
• Add query properties like name, external key, and description and then click on the Next button. 

• Write  SQL Query according to your requirement. Here for reference I am using SQL Query for _Sent:

• Validate syntax to check for the mistakes in the query.
• Click on the Next button.
• After your query is finished and validated, you can move on to the next step. Here, you need to choose  target data extension(created in first step for sent data view).
• In action you can choose according to your business requirement. For reference I am using append here.

Action:-

• Appending data means adding new data to target data extension.
• Updating the data means matching data when it is found or adding new data if there is none.
• Overwriting means replacing or overwriting the current data in the target data extension.

• The last step is summary, which shows the summarized details about properties, target data extension, and SQL query.
• Click on the Finish button.
• follow above steps for rest  query activities for open, click, complaint and bounce data extension with the queries I mentioned above.

The final screen will look like this.

After clicking on the save button Run Once button will appear. 

• Click on Select All Activities button.
• After clicking the Select All Activities button, Run button will appear.
• Click on the Run button, the automation will run.

If there is no error, automation will run successfully and the data will be populated in the data extension.

 

If you’re looking for more information about Data Views and SQL queries in Marketing Cloud, check out this Salesforce documentation