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) | |
| 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.