Salesforce Marketing Cloud Data Views: _Open

The _Open data view in Salesforce Marketing Cloud (SFMC) tracks when subscribers open the emails you send. This crucial view provides detailed information about email open behavior, which is key to measuring engagement and optimizing email strategies.

This blog explores the structure of the _Open data view, use cases, benefits, and example SQL queries to help marketers and analysts leverage it for better campaign insights.

---

What is the _Open Data View?

The _Open data view logs every instance when an email is opened by a subscriber. It supports both batch and triggered sends and retains data for up to six months.

Key Benefits of _Open Data View

• Measure Engagement: Understand how many subscribers opened your emails.
• Unique vs. Total Opens: Analyze distinct vs. repeated engagement.
• Optimize Subject Lines: Track performance based on open rates.
• Trigger Re-engagement: Identify who isn’t opening and retarget them.

---

Fields in _Open Data View & Their Uses

Field	Type	Description & Use Case
AccountID	Number	ID of the account from which the email was sent. Useful for enterprise tracking.
OYBAccountID	Number	ID of the parent business unit. Important for multi-BU setups.
JobID	Number	Unique identifier for the email send. Joinable with _Sent, _Click, etc.
ListID	Number	ID of the list associated with the email send. Helps in list-level performance tracking.
BatchID	Number	Identifies the batch in which the subscriber was sent the email. Useful for large sends.
SubscriberID	Number	System-generated ID for the subscriber. Used for backend processing.
SubscriberKey	Text(254)	Unique identifier of the subscriber (email or external ID). Core field for tracking.
EventDate	Date	Timestamp of when the email was opened. Enables engagement timeline analysis.
Domain	Text(128)	Email domain (e.g., gmail.com). Helps analyze domain-based engagement.
IsUnique	Boolean	Indicates if the open was the first by that subscriber for that send. Vital for open rate metrics.
TriggererSendDefinitionObjectID	Text(36)	ID of the triggered send definition. Ties opens back to automation.
TriggeredSendCustomerKey	Text(36)	Unique customer key for the triggered send. Used for tracking performance of triggered sends.

---

When and Why to Use _Open Data View?

When to Use?

• To measure campaign performance through open rates.
• To identify engaged vs. unengaged subscribers.
• When building re-engagement or retargeting journeys.
• When analyzing engagement by domain, job, or time.

Why Use It?

• Performance Tracking: Pinpoint which emails draw the most attention.
• Behavioral Targeting: Use open behavior to create custom journeys.
• Audience Segmentation: Separate active from inactive contacts.
• Content Testing: Evaluate subject line and timing effectiveness.

---

10 Example Scenarios and Queries

1. Retrieve Unique Opens in the Last 7 Days

SELECT SubscriberKey, EventDate, JobID 
FROM _Open 
WHERE EventDate >= DATEADD(day, -7, GETDATE()) 
AND IsUnique = 1

Use Case: Weekly engagement report.

2. Count of Total vs. Unique Opens

SELECT IsUnique, COUNT(*) AS OpenCount 
FROM _Open 
GROUP BY IsUnique

Use Case: Compare unique vs. repeat open behavior.

3. Open Activity by Domain

SELECT Domain, COUNT(*) AS Opens 
FROM _Open 
GROUP BY Domain

Use Case: Evaluate engagement by ISP (e.g., Gmail, Yahoo).

4. Top Engaged Subscribers This Month

SELECT SubscriberKey, COUNT(*) AS TotalOpens 
FROM _Open 
WHERE EventDate >= DATEADD(month, -1, GETDATE()) 
GROUP BY SubscriberKey 
ORDER BY TotalOpens DESC

Use Case: Identify highly engaged subscribers.

5. Join _Sent and _Open for Open Rate Calculation

SELECT s.SubscriberKey, s.JobID, s.EventDate AS SentDate, o.EventDate AS OpenDate 
FROM _Sent s 
LEFT JOIN _Open o 
ON s.SubscriberKey = o.SubscriberKey AND s.JobID = o.JobID

Use Case: Calculate open rates.

6. Track Opens from a Specific Job

SELECT SubscriberKey, EventDate 
FROM _Open 
WHERE JobID = 987654

Use Case: Evaluate performance of a single campaign.

7. Find Subscribers Who Opened Multiple Times

SELECT SubscriberKey, COUNT(*) AS OpenCount 
FROM _Open 
GROUP BY SubscriberKey 
HAVING COUNT(*) > 1

Use Case: Segment high-engagement audience.

8. Engagement Trends Over Time

SELECT CAST(EventDate AS DATE) AS OpenDate, COUNT(*) AS OpenCount 
FROM _Open 
GROUP BY CAST(EventDate AS DATE)

Use Case: Identify day-over-day trends.

9. Track Opens by Triggered Send Key

SELECT SubscriberKey, TriggeredSendCustomerKey, EventDate 
FROM _Open 
WHERE TriggeredSendCustomerKey = 'Onboarding_Email1'

Use Case: Track performance of automated emails.

10. Identify Subscribers Who Never Opened Emails

SELECT s.SubscriberKey 
FROM _Sent s 
LEFT JOIN _Open o 
ON s.SubscriberKey = o.SubscriberKey AND s.JobID = o.JobID 
WHERE o.SubscriberKey IS NULL

Use Case: Build a re-engagement or suppression list.

---

Conclusion

The _Open data view is essential for measuring and analyzing email engagement within Salesforce Marketing Cloud. It provides deep insight into who is opening your emails, when, and how often, allowing you to optimize subject lines, send times, and targeting strategies.

Combine _Open with _Sent, _Click, and _Bounce to build a holistic understanding of subscriber behavior.