Comprehensive Guide to Data Views in Salesforce Marketing Cloud

If “data is the new oil” and “the customer is king”, you need a way to keep the king happy. And you can do exactly that with Salesforce Marketing Cloud’s data views. Marketing Cloud data views are system-generated tables or data extensions (DEs) that store information about subscribers and how they have interacted with your content (sent via SFMC) over the past six months. You can then use this information to understand customers and their behaviors, identify their needs, and refine your messaging with personalized content that improves their brand engagement.

Keep reading to learn more about SFMC data views and how you can view them by running an SQL to leverage data in order to keep query in SFMC Automation Studio.

Setting the Stage: What Are Salesforce Marketing Cloud Data Views?

Your SFMC email specialist likely already works with data extensions in SFMC. A DE is a table with fields of data about your contacts. Permissions, sharing, and other functionalities of DEs live in SFMC Email Studio.

A Marketing Cloud data view is a type of DE that holds subscriber information. To use data views, they must be enabled in your SFMC account. Once this is done, you can do all of the following with Salesforce data views:

• Join them with each other 
• Join them with other DEs
• Reference them in AMPscript, such as in Lookup functions
• View subscribers’ behavioral information and information about emails forwarded, links clicked, email bounce rate – all for the previous six months of interactions

Some data views may contain duplicate information since they are treated as logs rather than as normalized tables. SFMC includes four “hidden” data views:

• _MobileAddress and _MobileSubscription: Hold data related to Mobile Connect
• _PushAddress and _PushTag: Hold data related to Mobile Push

Important: You cannot access data views directly from the SFMC UI. To view and use data views, you must create SQL queries in SFMC Automation Studio. See this section to learn more about creating SQL queries on data views.

5 Frequently Used Data Views

There are 20+ SFMC data views. 

Data views are easy to identify since they have intuitive names, such as _Job, _Sent, _Open, and so on.

Tip: Work with the basic use cases and scenarios first before exploring the more advanced options.

Let’s take a look at five of the most commonly used data views:

1. Job data view

By querying this data view in Automation Studio, you can find data on SFMC email send jobs. It doesn’t categorize the results by send method. Some of the indexed fields you will see by querying this data view are:

Name	Description	Data type	DE data type
JobID	The job ID number for the email send	int	Number
EmailID	The email ID for the job	Int	Number
FromName	The from name in the email send	nvarchar(130)	Text
BccEmail	Any email address send in the BCC field	varchar(100)	Email
CreatedDate	The date the job was created	smalldatetime	Date
IsMultipart	Whether the job was sent as multipart MIME or not	bit	Boolean

2. Sent data view

Querying this data view in Automation Studio will enable you to find the subscribers who were sent emails from your SFMC account, and review who received a specific email and when. Records are available dating back six months from the day the query runs.

Some of the indexed fields you will see by querying this data view are:

Name	Description	Data type	DE data type
JobID	The job ID number for the email send	int	Number
EventDate	The date the send took place	datetime	Date
SubscriberKey	The subscriber key for the affected subscriber	nvarchar(254)	Text

3. Open data view

Find email opens for your SFMC account by querying this data view in Automation Studio. If a subscriber opens an email twice, two records will be created in this data view, each with the open date and time. You may have to deduplicate the information to find unique opens by each subscriber.

Some of the indexed fields you will see by querying this data view are:

Name	Description	Data type	DE data type
AccountID	Your account ID number	int	Number
EventDate	The date the open took place	datetime	Date
SubscriberKey	The subscriber key for the affected subscriber	nvarchar(254)	Text
IsUnique	Whether the event is unique or repeated	bool	Boolean

The IsUnique field can have misleading and duplicate information. However, you can apply aggregation over the SubscriberKey to find the number of unique opens for each email.

4. Click data view

If you query this data view in Automation Studio, you can view click data for emails from your SFMC account. _Click behaves similarly to _Open, so if a subscriber clicks on the same content twice, you will see two records in the data view, , each with the open date and time.

Some of the indexed fields you will see by querying this data view are:

Name	Description	Data type	DE data type
AccountID	Your account ID number	int	Number
ListID	The list ID number for the list used in the send	Int	Number
SubscriberKey	The subscriber key for the affected subscriber	nvarchar(254)	Text
EventDate	The date the click took place	datetime	Date
IsUnique	Whether the event is unique or repeated	bool	Boolean

As with the _Open data view, the IsUnique field in _Click can also have duplicate information. Follow the same aggregation technique to find the number of unique clicks for each email.

5. Bounce data view

View bounce data for your SFMC emails by querying this data view in Automation Studio. Of course, it’s not enough to simply view this data. You must also do a deep-dive to identify bounce causes and then act appropriately to reduce the bounce rate.

Some of the indexed fields you will see by querying this data view are:

Name	Description	Data type	DE data type
AccountID	Your account ID number	Int	Number
ListID	The list ID number for the list used in the send	int	Number
SubscriberID	The subscriber key for the affected subscriber. It is a unique ID for each subscriber record.	Int	Number
SubscriberKey	The subscriber key (primary key) for the affected subscriber	nvarchar(254)	Text
EventDate	The date the bounce took place	datetime	Date
IsUnique	Whether the event is unique or repeated	bit	Boolean
BounceSubcategory	The subcategory of the bounce	nvarchar(50)	Text
Domain	The domain at which the bounce occurred	varchar(128)	Text

How to Query a Data View in SFMC with SQL

Now let’s see how to query a data view with SQL in SFMC. As an example, we will select the _Subscribers data view. By querying _Subscribers in Automation Studio, you can find the subscribers in your SFMC account as well as their current statuses (but not their attributes).

If your access to data views has been enabled in your account, you can start by creating a target DE. This is where the information will be stored. The DE also needs to contain three fields to capture subscribers’ details: First Name, Last Name, Email Address.

Follow these steps to create the data view and query it using SQL:

• Go to Automation Studio to create an SQL Query Activity
• Select the DE that contains your contacts’ information (if you don’t know this already, ask your SFMC email specialist)
• Choose the fields you want to see in your target DE
• Rename the fields if you want
• Choose the data view “_Subscribers” and field “EmailAddress”
• Choose the FROM criteria and select your initial DE
• Choose the relationship between the DE and the data view using JOINS
• Choose your target DE (you should have created this already – see above)
• Save and Run your query
• Review the result in the target DE

Good to know

To access more than the last 6 months of tracking data, mirror data views in your SFMC account by creating a scheduled automation. It will regularly run SQL queries on data views and populate the results into a DE.

Also good to know

You can use SFMC data views in AMPscript. Simply reference them like other DEs. 

Wrapping Up

As an email marketer, you already know the benefits of email templates in Salesforce. But you may not know that understanding and working with data views can also benefit your email marketing program. The subscriber information in data views will help you segment your audience for future campaigns. It will also help you understand campaign performance and identify optimization opportunities.

We hope you found this article useful. If you need more information or supporting leveraging data views in Salesforce Marketing Cloud, contact Email Uplers.