Eight ready-to-use SQL queries for Marketing Cloud

A DIY retailer launches a reactivation campaign from the business unit of its online brand. The audience is built in SQL: customers with no click and no open for a year, minus the unsubscribes found in _Unsubscribe. It feeds the send, but it also goes to the call centre, which has to ring the high-value customers.

The send itself goes well, because Marketing Cloud drops unsubscribed subscribers on a commercial send without being asked. The export gets no filter at all. The call centre reaches customers who unsubscribed more than six months ago, and are therefore missing from _Unsubscribe, plus others who had unsubscribed from the brand business unit only. Several of them complain, this time in writing.

Meanwhile the marketing team wonders why the audience is so small. Some inactive customers appear to open every email, because their Apple mail client preloads images, and a few corporate addresses appear to click every link seconds after the send, because an antivirus checks the links before the recipient does. The eight queries below answer both problems: one exclusion table that every channel shares, and an engagement score that machines cannot fool.

What the send excludes on its own

On a commercial send, Marketing Cloud does not deliver to unsubscribed subscribers. An exclusion table built in SQL therefore serves three purposes above all: counting an audience just before the send, feeding exports and other channels, and applying company rules such as a pause after a complaint.

Why _Unsubscribe is not enough

The costliest line in that table is the third one. Plenty of audiences settle for an outer join on _Unsubscribe, because it is the most visible source and the easiest to write. It covers six months of events: anyone who unsubscribed before that window stays in the audience, and the calculation degrades on its own as the database ages. A subscriber status, by contrast, does not expire after six months.

Held and bounced statuses should be kept out of a commercial send in the same way as unsubscribes; confirm the exact behaviour on your own account rather than assuming it, because that assumption decides what lands in your exports. A transactional send classification, for its part, ignores commercial unsubscribes. A reactivation email is commercial: check that the send classification says so too. For exclusions you want applied to every send without exception, Email Studio offers suppression lists, shareable across business units in Enterprise 2.0.

Four sources, four tables

The queries run in a daily automation, one per step, all in Overwrite mode. The Excl_ tables use SubscriberKey as their primary key. The comment at the top of each query states its target and its dependencies: keep them, they are the documentation on the day somebody else picks up the work.

/* R1. Target: Excl_Statut (Overwrite), key SubscriberKey
   Source: enterprise status. ENT. prefix from a child BU. */
SELECT
    s.SubscriberKey,
    s.Status                                          AS Motif,
    COALESCE(s.DateUnsubscribed, s.DateUndeliverable) AS DateMotif
FROM ENT._Subscribers AS s
WHERE s.Status IN ('unsubscribed', 'held', 'bounced')

The prefix is not a flourish. Without it, run from a child business unit, this query succeeds and returns nothing: the exclusion table is empty and no unsubscribed contact is kept out of the exports.

/* R2. Target: Excl_Desabo_BU (Overwrite, shared data extension)
   Run in the parent account. 123456789 = MID of the BU. */
SELECT
    u.SubscriberKey,
    'unsubscribed_bu'  AS Motif,
    u.UnsubDateUTC     AS DateMotif
FROM _BusinessUnitUnsubscribes AS u
WHERE u.BusinessUnitID = 123456789

This one runs in the parent account and writes to a shared data extension that the brand business unit can read. Set up the sharing folder and the matching rights before you schedule the automation, otherwise the query runs for nothing.

/* R3. Target: Excl_Plaintes (Overwrite), key SubscriberKey */
SELECT
    c.SubscriberKey,
    'complaint'       AS Motif,
    MAX(c.EventDate)  AS DateMotif
FROM _Complaint AS c
GROUP BY c.SubscriberKey

/* R4. Target: Excl_Hard_Bounces (Overwrite), key SubscriberKey
   Hard bounces over the last 90 days.
   Check the exact category label on your own account. */
SELECT
    b.SubscriberKey,
    'hard_bounce'     AS Motif,
    MAX(b.EventDate)  AS DateMotif
FROM _Bounce AS b
WHERE b.EventDate >= DATEADD(day, -90, CAST(GETDATE() AS DATE))
  AND b.BounceCategory = 'Hard bounce'
GROUP BY b.SubscriberKey

A complaint triggers an exclusion here, even if the subscriber status has gone back to active in the meantime. That is a company decision, not a platform rule: own it, and write it into your targeting documentation.

The cost of the discouraged patterns

R1 relies on an IN and R2 on an equality against a column that is probably not indexed. Both sit among the patterns Salesforce recommends avoiding, and I keep them anyway: on an exclusion table rebuilt once a day, outside any send window, the cost stays reasonable. Measure how long they take over the first few weeks all the same, especially if your database runs past a few million subscribers.

A single exclusion table

R5 merges the four tables and keeps one reason per key, following a priority order. It is the only table your targeting queries should read.

/* R5. Target: Exclusions (Overwrite), key SubscriberKey
   Depends on R1 to R4. Priority: BU, status, complaint, bounce. */
SELECT x.SubscriberKey, x.Motif, x.DateMotif
FROM (
    SELECT
        u.SubscriberKey, u.Motif, u.DateMotif,
        ROW_NUMBER() OVER (
            PARTITION BY u.SubscriberKey
            ORDER BY u.Priorite
        ) AS Rang
    FROM (
        SELECT SubscriberKey, Motif, DateMotif, 1 AS Priorite FROM Excl_Desabo_BU
        UNION ALL
        SELECT SubscriberKey, Motif, DateMotif, 2 FROM Excl_Statut
        UNION ALL
        SELECT SubscriberKey, Motif, DateMotif, 3 FROM Excl_Plaintes
        UNION ALL
        SELECT SubscriberKey, Motif, DateMotif, 4 FROM Excl_Hard_Bounces
    ) AS u
) AS x
WHERE x.Rang = 1

Keeping the reason and its date answers the question your data protection officer will ask sooner or later: why was this person not contacted, and since when? The same table serves the counts, the exports and the other channels, which guarantees that everyone applies the same rule.

Human clicks and the score

Salesforce points out that open tracking depends on an image loading, and that recorded opens and clicks alike can come from a security tool that follows links ahead of the recipient. One support article goes further: tracking systems cannot tell a human click from a machine click. R6 applies the simplest filter that holds, discarding clicks recorded less than 100 seconds after the matching send.

/* R6. Target: Clics_Humains (Overwrite)
   Key: JobID, ListID, BatchID, SubscriberID, EventDate
   Sources: send and click history, last 12 months. */
SELECT
    c.JobID, c.ListID, c.BatchID, c.SubscriberID,
    c.SubscriberKey,
    c.EventDate
FROM Historique_Clics AS c
INNER JOIN Historique_Envois AS s
    ON  s.JobID        = c.JobID
    AND s.ListID       = c.ListID
    AND s.BatchID      = c.BatchID
    AND s.SubscriberID = c.SubscriberID
WHERE c.EventDate >= DATEADD(month, -12, CAST(GETDATE() AS DATE))
  AND c.EventDate >= DATEADD(second, 100, s.EventDate)

The 100 second threshold comes from a Salesforce support article on clicks generated by antispam software, which presents it as adjustable. It also discards a few very fast human clicks: the trade-off is deliberate, and losing a real reader beats counting a security gateway as engaged.

The engagement score over twelve months

R7 then calculates, per subscriber and over twelve months, the number of sends, the last open and the last human click, before assigning a segment. Each source is aggregated ahead of the join, which keeps the volume handled down.

/* R7. Target: Score_Engagement (Overwrite), key SubscriberKey
   Depends on R6. An open alone is not enough to be 'Engage'. */
CASE
    WHEN k.DernierClic >= DATEADD(day, -90, CAST(GETDATE() AS DATE))
        THEN 'Engage'
    WHEN e.PremierEnvoi >= DATEADD(day, -90, CAST(GETDATE() AS DATE))
        THEN 'Nouveau'
    WHEN k.DernierClic IS NOT NULL       THEN 'Tiede'
    WHEN o.DerniereOuverture IS NOT NULL THEN 'Ouvreur_Seul'
    WHEN e.NbEnvois >= 5                 THEN 'Inactif'
    ELSE 'Indetermine'
END AS Segment

The three aggregates come from subqueries on the send, open and human click histories, joined on SubscriberKey with outer joins. The order of the WHEN clauses matters, since the first true one wins. The Ouvreur_Seul segment isolates contacts whose engagement rests on opens alone, reliable or not: they are neither engaged nor inactive, and it is for you to decide whether they receive the campaign, ideally after a test on a sample. The labels are written without accents to avoid surprises in comparisons and exports.

Blocked images and missing opens

The bias works both ways. Blocked images produce the opposite effect to Apple protection: real reads that leave no recorded open behind. That supports the choice of resting the engaged segment on clicks, which stay visible whatever happens to images. Salesforce followed the same reasoning in its Einstein features, with an engagement rate that combines clicks and opens instead of relying on opens alone.

On a large account, measure how long this query runs before you schedule it. Three aggregations over twelve months of history can justify splitting the work into intermediate tables.

The reactivation audience

R8 joins the customers, the score and the exclusion table. That table, and that table alone, goes to the send and to the call centre.

/* R8. Target: Population_A_Reactiver (Overwrite), key ClientId
   Depends on R5 and R7. Read by the send and by the export. */
SELECT
    c.ClientId,
    c.Email,
    c.Prenom,
    c.Telephone,
    sc.Segment,
    sc.NbEnvois
FROM Clients AS c
INNER JOIN Score_Engagement AS sc
    ON sc.SubscriberKey = c.ClientId
LEFT JOIN Exclusions AS x
    ON x.SubscriberKey = c.ClientId
WHERE sc.Segment = 'Inactif'
  AND x.SubscriberKey IS NULL

The LEFT JOIN followed by x.SubscriberKey IS NULL keeps only the customers absent from the exclusion table. Changing channel changes nothing about the rule: the file sent to the call centre comes out of the same query as the email audience, with the same exclusions applied at the same moment.

If you are coming from Adobe Campaign

In Adobe Campaign Classic, typology filtering rules remove recipients in quarantine or on the blocklist when the delivery is analysed, and the exclusions appear in the analysis report. The mechanism is reusable: one typology applies to every delivery that references it.

Marketing Cloud applies statuses at send time, and suppression lists cover part of the need, but there is no reusable rule set equivalent to typologies. The Exclusions table stands in, with one condition that is also its weakness: every targeting query has to remember to read it. Campaign quarantines map here to the held status and to _Bounce, with one notable difference, since _Bounce events disappear after six months.

The habit worth keeping

Anything that leaves Marketing Cloud without going through a send leaves unfiltered. An export to a call centre, a file for an SMS provider, a feed into a CRM: ask every time which exclusion table was read, and how recently it was rebuilt.

Frequently asked questions