Keeping data view history beyond six months

A health insurer wants to win back its inactive members, defined as those who have neither opened nor clicked in twelve months. The targeting query reads _Open and _Click directly. The first campaign goes out to 40% of the base, a figure that surprises everyone in the marketing team.

The explanation fits in one line: data views only hold six months, so any member who was active eight months ago looks inactive. The team then creates an Historique_Ouvertures table, fed every night in Append mode with the previous day's opens. Three months later, three anomalies surface. The automation was rerun by hand on the day of an incident, and that day's opens appear twice. It was suspended for ten days in August, and those ten days are missing. And opens recorded just before midnight do not always show up.

None of these three anomalies raises an error. In testing, with one run a day and no interruption, everything looked right.

Six months, on a sliding window

The reference pages for _Sent, _Open, _Click, _Bounce, _Unsubscribe and _Complaint all state a retention of six months. The window slides: every day, the oldest events drop off. A history therefore has to be fed continuously, before the data leaves the window. If the automation stays stopped for more than six months, the events from that period are gone for good.

Inherited time zone and deleted subscribers

Two further characteristics matter when designing the tables. Data view dates are stored in North American Central Standard Time, with no daylight saving, and your history tables inherit that zone. A deleted subscriber no longer appears in the data view while they remain in your history: the gap is normal and should not be corrected.

The Number type of a data extension accepts an integer up to roughly 2.1 billion. It suits current identifiers, but the columns documented as bigint could in theory go past that bound. Watch the maximum JobID value in your history tables, and plan for a Text type if it gets close.

A key that identifies the event

A history table fed in Update mode needs a primary key that identifies an event in a stable way. If the same row comes back on a second pass, it replaces the old one instead of adding to it. That mechanism, and only that one, makes a load safe to replay.

A non nullable, bounded key

Every column of the key must be non nullable, and the full key must stay under 1,700 bytes, the limit given by the SQL reference. That is why the URL of a click, long and sometimes empty, is stored as a plain attribute. SubscriberKey is kept alongside the key, since it is what joins to your data extensions.

Load against a marker, not a clock

Reading six months of _Open every night is slow and pointless. You load only what is new, using a marker stored in a small parameter table: the date of the last event already loaded. Three rules make it reliable. The marker is the date of the last event present in the history, not the automation run time. You pull the starting point back by an overlap window, 24 hours for example, to catch events that arrived late in the data view. And the marker only moves forward after a successful load, in a separate step.

The target cannot be read

One platform constraint shapes the rest of the design: the Query Activity documentation states that the target data extension cannot be a data extension used in the query. You therefore cannot exclude the rows already present by reading the history you are feeding. The composite key and Update mode take the place of that.

/* Historique_Ouvertures, Update mode */
SELECT
    x.JobID, x.ListID, x.BatchID, x.SubscriberID,
    x.SubscriberKey,
    x.EventDate,
    GETDATE() AS DateChargement
FROM (
    SELECT
        o.JobID, o.ListID, o.BatchID, o.SubscriberID,
        o.SubscriberKey,
        o.EventDate,
        ROW_NUMBER() OVER (
            PARTITION BY o.JobID, o.ListID, o.BatchID, o.SubscriberID
            ORDER BY o.EventDate
        ) AS Rang
    FROM _Open AS o
    INNER JOIN Parametres_Traitement AS p
        ON p.NomTraitement = 'HISTO_OUVERTURES'
    WHERE o.IsUnique = 1
      AND o.EventDate >= DATEADD(hour, -p.ChevauchementHeures, p.DerniereDateEvenement)
) AS x
WHERE x.Rang = 1

The bound is worked out on the parameter, not on EventDate: the data view column stays bare, as the performance guidance asks. The subquery with ROW_NUMBER() guarantees a single row per key, even if a data anomaly returned two unique opens for the same send. For clicks, add EventDate to the partition; for sends, read _Sent without the condition on IsUnique, which does not exist there.

Move the marker forward, then keep watch

A separate query recalculates the marker from what has just been loaded. It reads the history tables and writes to the parameter table, so it does not read its own target.

/* Parametres_Traitement, Update mode */
SELECT
    'HISTO_OUVERTURES'  AS NomTraitement,
    MAX(h.EventDate)    AS DerniereDateEvenement,
    GETDATE()           AS DateMaj
FROM Historique_Ouvertures AS h
WHERE h.DateChargement >= DATEADD(hour, -6, GETDATE())
HAVING MAX(h.EventDate) IS NOT NULL

Why the HAVING protects the marker

The filter on DateChargement looks only at the rows written by the current pass. Since the overlap window reloads the most recent event of the previous day, the maximum obtained cannot fall below the old marker. The HAVING protects the case where nothing was loaded: without it, the query would return a row with an empty marker, and the next load would find nothing at all. As written, it returns no row and the marker stays where it is.

The column that holds the overlap duration is absent from the selection. An Update mode write is expected to leave the columns missing from the SELECT unchanged; check that on your account before going live, or add the column to the query with its value.

Ordering the automation steps

The nightly automation chains one query per step: sends, opens, clicks, then the marker update last. If a step fails before the last one, the marker does not move and the next pass resumes in the right place. Schedule it at night, at an offset time, and well before the automations that read the history tables.

Retention and record of processing

That leaves retention. Set a retention policy on each history table, deleting individual records beyond two years for instance, and document the choice in your record of processing activities. Engagement history is personal data: its retention period is justified by use, not by available space.

If you are coming from Adobe Campaign

In Adobe Campaign Classic, delivery and tracking logs stay in the database until the technical cleanup workflow purges them, with periods you set in the deployment wizard. History exists by default, and the real question is when to purge it.

In Marketing Cloud it works the other way round: the six month window is fixed, and anything you want to keep longer has to be copied by you. The marker stored in the parameter table plays the role of a workflow instance variable kept from one run to the next, except that you write it, read it and watch it yourself.

The habit worth keeping

Before writing any twelve month engagement query, ask when the history was set up and whether it has been interrupted. A table that starts three months ago does not answer the question asked, and nobody will notice as long as the figure it produces stays plausible.

Frequently asked questions