Marketing Cloud data views: what they actually tell you

An ecommerce team is preparing a reactivation campaign. The brief fits on one line: every subscriber who has neither opened nor clicked in nine months. An integrator writes the query against _Open and _Click with a 270 day window, tests it on a handful of known subscribers, and the audience goes for sign-off.

Marketing expected a hundred and twenty thousand people. The query returns forty-eight thousand. Checking a sample, the team finds customers who have been inactive for only seven months, and none of the heavy sleepers known to have read nothing for two years, all of them on an iPhone.

The query has no syntax error. It rests on three false assumptions: that data views go back nine months, that an open proves a read, and that a click proves a human.

The depth stops at six months

System data views are the only SQL source for sends, opens, clicks, bounces and unsubscribes in Marketing Cloud Engagement. They feed exclusions, engagement scores and a good share of the dashboards. The interface never reminds you of their limits.

Data view scope by business unit

Two points of scope are worth noting. Event data views are not available at enterprise level: each business unit reads its own sends, opens and clicks. The opposite applies to _BusinessUnitUnsubscribes, which can only be read from the parent account, so its contents have to be copied into a shared data extension before child business units can use them.

Four columns tie an event to its send

An event attaches to a specific send through JobID, ListID, BatchID and SubscriberID. That is the join Salesforce uses in its own click filtering example. The SubscriberKey is present everywhere and serves to join your data extensions, but it is not enough to tie an open to the send that produced it.

Joining on it alone produces a silent cartesian product: every open by a subscriber attaches to every send they received. With twenty sends and ten opens over six months, one subscriber weighs two hundred rows, and the open rate per campaign becomes unreadable.

SELECT
    s.JobID,
    COUNT(*)            AS NbOuvertures
FROM _Sent AS s
INNER JOIN _Open AS o
    ON  o.JobID        = s.JobID
    AND o.ListID       = s.ListID
    AND o.BatchID      = s.BatchID
    AND o.SubscriberID = s.SubscriberID
WHERE o.IsUnique = 1
GROUP BY s.JobID

Counting an open only once

The filter on IsUnique counts a subscriber once even when they open the same email five times. These data views hold every occurrence, not only the first: without that filter, and without COUNT(DISTINCT SubscriberKey), an open rate can climb above 100%.

EventDate in US central time

One last point of care, on dates: data views store their timestamps in US central standard time, with no daylight saving, so UTC minus six hours all year round. A click stamped 3am in _Click is 9am in London in winter and 10am in summer.

What an open proves, and what it does not

An open is the loading of a tracking image. A click is a pass through a redirect URL. Neither says who triggered the event, and Salesforce puts it plainly: its tracking systems have no way of knowing whether an open or a click is human or automated.

Apple Mail Privacy and antivirus software

Two mechanisms distort the figures. Apple Mail Privacy Protection, when the user turns it on, loads remote content in the background without the message being read: the open is recorded shortly after delivery. Antivirus software and mail gateways, very common among business customers, follow links to scan them before delivery: the click is recorded with no open, often within a minute of the send.

In a knowledge base article, Salesforce suggests discarding clicks that happen less than 100 seconds after the send, noting that the threshold needs tuning. To decide which signal to use, this grid covers most cases.

On the GDPR side, picking inactive for six months as the point to stop contacting someone is defensible. Applying that rule with automated opens amounts to never applying it to Apple Mail users.

Rebuilding the audience honestly

The rework takes two queries. The first counts the sends received over the window that is genuinely available, because a subscriber who has received nothing in six months is not inactive: they are out of scope.

SELECT
    s.SubscriberKey,
    COUNT(*)            AS NbEnvois
FROM _Sent AS s
WHERE s.EventDate >= DATEADD(day, -180, GETDATE())
GROUP BY s.SubscriberKey

Discarding clicks that come too fast

The second attaches each click to its send through the four keys and discards those that follow the send too closely.

SELECT
    s.SubscriberKey,
    MAX(c.EventDate)    AS DernierClic
FROM _Sent AS s
INNER JOIN _Click AS 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, -180, GETDATE())
  AND DATEDIFF(second, s.EventDate, c.EventDate) >= 100
GROUP BY s.SubscriberKey

The filter on _Sent caps the volume read, and the GROUP BY guarantees one row per key, a requirement for writing into a data extension whose primary key is the SubscriberKey. A very fast human click will be discarded by mistake: that is the price of the filter, acceptable for a reactivation audience. Measure the threshold on your own sends before adopting the Salesforce figure.

The final audience then reads off those two columns: at least six sends received, no filtered click. Keep the last open as context for the marketing team, never as an exclusion criterion. Nothing in the data views exposes the user agent or the technical origin of an open, so nothing lets you cleanly isolate the automated ones.

If you are coming from Adobe Campaign

_Sent maps to the delivery logs, _Open and _Click to the tracking logs. The difference lies in the depth. In Adobe Campaign, the cleanup workflow purges those logs according to durations set at install time, which your team can extend if the database copes. In Marketing Cloud, the six months are not negotiable: beyond that, you archive into your own data extensions.

Another difference shows up daily: events carry no direct link to a recipient record. You get identifiers, SubscriberID and SubscriberKey, and it is up to you to join them to your tables. The first ties the event to its send, the second ties the event to your reference data. Confusing the two is the most common mistake on early queries.

The habit worth keeping

Before writing a window into a query, check that it fits inside the retention. Before declaring anyone inactive, check that they received something. And before building a score on opens, remember that for part of your audience, a piece of software produces them.

Frequently asked questions