What Are Data Views in Salesforce Marketing Cloud Engagement?

Data Views are system-managed tables that expose tracking, subscriber, journey, and automation information. You query them with SQL in Automation Studio to build reports, investigate sends, and create audiences based on recorded activity.

An audience data extension shows who was selected for a campaign. Tracking Data Views show the sends and interactions Marketing Cloud recorded. Comparing the two helps you investigate missing sends, bounces, engagement, and differences between campaign reports.

Data Views vs Data Extensions

Data extensions store the data you define. Data Views expose data maintained by Marketing Cloud.

Feature	Data Views	Data Extensions
Structure	Salesforce defines the available columns	You define fields, data types, and primary keys
Writing data	Read-only through SQL Query Activities	Populated through imports, queries, APIs, and scripts
Retention	Determined by the individual Data View	Configured for the data extension
Purpose	System activity, tracking, and subscriber information	Customer data, audiences, reporting outputs, and other custom datasets

You can join Data Views to your data extensions. For example, join click events to customer records to report engagement by membership tier.

That join uses the customer data available when the query runs. It does not reconstruct the customer’s tier at the time of the send. If you need historical attributes, capture them in an audience snapshot or configure send logging to retain the required send-time values.

Data Views used for email reporting

These views cover most email tracking queries:

Data View	What it contains
_Sent	Recorded email sends to subscribers
_Open	Recorded email open events
_Click	Recorded clicks, including link information
_Bounce	Bounce events, categories, and diagnostic information
_Unsubscribe	Unsubscribe events associated with email sends
_Complaint	Recorded spam complaints
_Job	Send-job information, including email name, subject, and send classification
_Subscribers	Email subscriber information and current status
_Journey	Journey names, versions, statuses, and related metadata
_JourneyActivity	Activities associated with journey versions

Automation reporting uses other views, including _AutomationInstance and _AutomationActivityInstance, to inspect automation runs and individual activity execution.

These views answer different questions. _Unsubscribe provides event information, while _Subscribers.Status describes the subscriber’s current email status. A current status does not provide a complete history of how that status changed.

What sends, opens, and clicks actually tell you

A record in _Sent means Marketing Cloud recorded a send to that subscriber. It does not prove that the message reached the inbox. Check _Bounce separately when investigating delivery.

A missing _Sent record also needs investigation. The subscriber might have been excluded before sending, the data might not yet be available, or the query might be running in the wrong business unit.

An open event does not prove that someone read the email. Email-client privacy features can inflate open metrics.

Clicks record access to tracked links, but they are not conclusive proof of human activity either. Security software can generate opens and clicks independently of the recipient.

Use these events as recorded tracking signals. Define what they mean for your report before turning them into engagement scores or journey conditions.

Querying Data Views in Automation Studio

A SQL Query Activity reads the selected Data Views and writes the results into a target data extension.

The workflow is:

1. Create a target data extension with fields matching the query output.
2. Write the SQL statement in a SQL Query Activity.
3. Select the target data extension and data action.
4. Run the activity and inspect the results.
5. Add it to an automation when the query is ready to run on a schedule.

Use Overwrite for a report that is rebuilt each time. Use Update when existing rows need to change as new tracking events arrive. Use Append when each execution produces new records that should be added to the target.

Appending the same rolling date range repeatedly creates duplicate report rows unless the process accounts for records already stored.

Choosing the correct join keys

Subscriber Key identifies the email subscriber. In Contact Builder, the same identifier is called Contact Key. These are not competing identity models.

SubscriberID is the internal identifier for the subscriber record. JobID, ListID, and BatchID identify the send context.

When matching sends to opens or clicks, use the send context as well as the subscriber identifier. Joining only on Subscriber Key can connect activity from different emails. Joining only on JobID and Subscriber Key can also mix records from separate batches.

For the example below, the joins use:

• JobID
• ListID
• BatchID
• SubscriberID

Also account for repeated events. If a recipient has two open rows and three click rows, joining both event tables directly can produce six combinations for that recipient’s send. Aggregate each event table before joining when the report needs one result per recipient send.

Example: sends with first open and first click

This query reports on sends from the previous seven days. It keeps recipients without recorded opens or clicks and adds the earliest available event timestamps.

Run it in the business unit that performed the sends.

SELECT
    s.JobID,
    s.ListID,
    s.BatchID,
    s.SubscriberID,
    s.SubscriberKey,
    j.EmailName,
    s.EventDate AS SentDate,
    o.FirstOpenDate,
    c.FirstClickDate,
    CASE
        WHEN o.FirstOpenDate IS NULL THEN 0
        ELSE 1
    END AS Opened,
    CASE
        WHEN c.FirstClickDate IS NULL THEN 0
        ELSE 1
    END AS Clicked
FROM _Sent s
LEFT JOIN _Job j
    ON j.JobID = s.JobID
LEFT JOIN (
    SELECT
        JobID,
        ListID,
        BatchID,
        SubscriberID,
        MIN(EventDate) AS FirstOpenDate
    FROM _Open
    WHERE EventDate >= DATEADD(day, -7, GETDATE())
    GROUP BY
        JobID,
        ListID,
        BatchID,
        SubscriberID
) o
    ON o.JobID = s.JobID
    AND o.ListID = s.ListID
    AND o.BatchID = s.BatchID
    AND o.SubscriberID = s.SubscriberID
LEFT JOIN (
    SELECT
        JobID,
        ListID,
        BatchID,
        SubscriberID,
        MIN(EventDate) AS FirstClickDate
    FROM _Click
    WHERE EventDate >= DATEADD(day, -7, GETDATE())
    GROUP BY
        JobID,
        ListID,
        BatchID,
        SubscriberID
) c
    ON c.JobID = s.JobID
    AND c.ListID = s.ListID
    AND c.BatchID = s.BatchID
    AND c.SubscriberID = s.SubscriberID
WHERE s.EventDate >= DATEADD(day, -7, GETDATE())

Create a non-sendable target data extension, such as rpt_EmailEngagementDaily, with these fields:

Fields	Data type
JobID, ListID, BatchID, SubscriberID	Number
SubscriberKey	Text, length 254
EmailName	Text, length 100
SentDate, FirstOpenDate, FirstClickDate	Date
Opened, Clicked	Number

Allow null values for EmailName, FirstOpenDate, and FirstClickDate.

The event subqueries return one aggregated row for each combination of send identifiers and subscriber. This prevents open and click rows from multiplying each other.

Opened and Clicked indicate whether a matching event is currently available. Summing these flags counts recipient sends with recorded engagement. It does not count total events or distinct people across all campaigns.

Use Overwrite to rebuild this rolling report. Sends disappear from its output once they fall outside the seven-day window. An archive needs a separate retention and update process.

The same approach can be extended with SQL patterns for tracking and audience data, including bounce flags, campaign classifications, and customer attributes.

Connecting email tracking to journeys

Use _JourneyActivity to associate an email tracking record with a journey activity.

The relationship is:

_Sent.TriggererSendDefinitionObjectID
    = _JourneyActivity.JourneyActivityObjectID

_JourneyActivity.VersionID
    = _Journey.VersionID

The spelling TriggererSendDefinitionObjectID is the actual tracking field name.

This journey activity relationship lets you attach journey names, versions, and activity names to email tracking results.

Keep VersionID in the reporting model when comparing versions. A journey name alone does not distinguish the versions that produced the sends. Email tracking also does not provide a complete record of every decision split, wait, or exit a contact passed through.

Business unit scope affects the results

Data Views do not all expose data at the same account level.

The _Job Data View returns email jobs for the current business unit. A query in the parent business unit should not be treated as a complete source of job metadata for every child business unit.

The _Subscribers Data View is enterprise-scoped and does not return local results for child business units in the same way.

For reporting across business units, define where each query runs and retain the source business unit identifier in the reporting output. Check scope before interpreting missing rows as missing sends.

Retention, refresh delays, and timestamps

Email tracking views such as _Sent, _Open, _Click, and _Bounce provide a six-month query window. Other Data Views have different retention rules, so check the documentation for each view you use.

The _Sent Data View does not update in real time. New records can take several minutes or multiple sends to appear. Deleted subscribers can also become unavailable in its results.

Clicks are processed asynchronously. The _Click documentation notes that the earliest click written to the database receives the unique-event flag. That is another reason to use MIN(EventDate) when you specifically need the earliest recorded click timestamp.

We have seen _Click updates delayed by more than 72 hours in our implementations. That is an observed delay, rather than a documented refresh interval. When investigating, distinguish between an interaction that happened late and an earlier interaction that became queryable late.

Email tracking timestamps use Central Standard Time without daylight saving adjustments. Convert timestamps consistently when combining tracking data with UTC-based systems or reports using local time.

Using tracking results in AMPscript and SSJS

For personalization, write the required tracking results into a normal data extension and retrieve those prepared values.

For example, a scheduled query can maintain:

• LastClickDate
• ClickedInLast14Days
• LastCampaignClicked
• DaysSinceLastRecordedEngagement

Email content can then retrieve those fields with AMPscript or SSJS.

This keeps tracking joins and aggregation in SQL, where they can run once for the audience. The email reads the resulting fields when it renders.

More complex personalization does not automatically require SSJS. Choose the language for the operation you need, and handle missing lookup results. The freshness of a prepared engagement field depends on when its automation last completed.

Why SQL results can differ from Tracking

Compare the metric definitions before changing the query.

Total events and unique recipients are different counts. Counting _Open rows measures recorded open events. Counting recipient sends with at least one open measures engagement once per send. Counting distinct Subscriber Keys across several jobs measures people across those jobs.

The date filter changes the question. Filtering _Sent.EventDate reports on a cohort of sends. Filtering _Click.EventDate reports on clicks occurring during a period, including clicks on older emails. Those reports should not be expected to match.

The denominator must be consistent. An audience count, sent count, and delivered count represent different stages. A rate calculated from the audience data extension can differ from one calculated from recorded sends and bounces.

Joins can remove records. An inner join to _Open excludes recipients without matching open events. An inner join to a customer table excludes subscribers whose identifier is missing from that table.

Tracking can still change. Late interactions and delayed availability can change the result after the first report runs.

Start with one JobID and a small set of subscribers. Inspect their send and event rows before comparing campaign-wide totals.

Query performance and longer-term reporting

SQL Query Activities time out after 30 minutes. Reduce the data processed before adding more joins.

Filter each large event source, select only the required columns, and aggregate repeated events before combining datasets. Split larger workloads into intermediate data extensions when a single query becomes difficult to maintain or complete.

For history beyond the tracking window, schedule queries that persist the required data into reporting data extensions or an external warehouse.

Use an overlapping refresh window to capture late events, and update existing report rows rather than appending duplicate copies. Size that window around the reporting requirement: a click today on an older email will be missed by a process that only revisits recent sends.

Keep the send identifiers, source business unit, event timestamps, and metric definitions with the reporting model. Historical engagement reports also need updates when later activity belongs to a send already stored in the archive.