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.