Huit requêtes SQL prêtes à l’emploi pour Marketing Cloud

Une enseigne de bricolage lance une campagne de réactivation depuis la business unit de sa marque en ligne. La population est calculée en SQL : clients sans clic ni ouverture depuis un an, moins les désabonnés trouvés dans _Unsubscribe. Elle sert à l’envoi, mais elle part aussi vers le centre d’appels, chargé de rappeler les clients à forte valeur.

L’envoi se passe bien, parce que Marketing Cloud écarte de lui-même les abonnés désabonnés lors d’un envoi commercial. L’export, lui, ne bénéficie d’aucun filtre. Le centre d’appels joint des clients désabonnés depuis plus de six mois, donc absents de _Unsubscribe, et d’autres qui s’étaient désabonnés de la seule business unit de la marque. Plusieurs s’en plaignent, cette fois par écrit.

En parallèle, le marketing s’étonne que la population soit si réduite. Une partie des clients inactifs « ouvre » chaque email, parce que leur messagerie Apple précharge les images, et quelques adresses professionnelles « cliquent » sur tous les liens quelques secondes après l’envoi, parce qu’un antivirus vérifie les liens avant le destinataire. Les huit requêtes qui suivent répondent à ces deux problèmes : une table d’exclusion que tous les canaux partagent, et un score d’engagement qui ne se laisse pas tromper par les machines.

Ce que l’envoi exclut tout seul

Lors d’un envoi commercial, Marketing Cloud n’envoie pas aux abonnés désabonnés. Une table d’exclusion calculée en SQL sert donc surtout à trois usages : compter une population juste avant l’envoi, alimenter des exports ou d’autres canaux, et appliquer des règles propres à l’entreprise, comme une pause après une plainte.

Pourquoi _Unsubscribe ne suffit pas

La ligne la plus coûteuse de ce tableau est la troisième. Beaucoup de ciblages se contentent d’une jointure externe sur _Unsubscribe, parce que c’est la source la plus visible et la plus facile à écrire. Elle ne couvre que six mois d’événements : toute personne désabonnée avant reste dans la population, et le calcul se dégrade tout seul à mesure que la base vieillit. Le statut de l’abonné, lui, ne s’efface pas au bout de six mois.

Les statuts en attente et en erreur devraient être écartés d’un envoi commercial au même titre que les désabonnés ; confirmez le comportement exact sur votre compte plutôt que de le supposer, car c’est cette hypothèse qui décide du contenu de vos exports. Une classification d’envoi transactionnelle, elle, ignore les désabonnements commerciaux. Un email de réactivation est commercial : vérifiez que la classification de l’envoi l’est aussi. Pour les exclusions que vous voulez appliquer à tous les envois sans exception, Email Studio propose des listes de suppression, partageables entre business units en Enterprise 2.0.

Quatre sources, quatre tables

Les requêtes s’enchaînent dans une automation quotidienne, une par étape, toutes en mode Overwrite. Les tables préfixées Excl_ ont SubscriberKey pour clé primaire. Les commentaires en tête de chaque requête indiquent sa cible et ses dépendances : gardez-les, ils tiennent lieu de documentation le jour où quelqu’un d’autre reprend le chantier.

/* R1. Cible : Excl_Statut (Overwrite), clé SubscriberKey
   Source : statut entreprise. Préfixe ENT. depuis une BU enfant. */
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')

Le préfixe n’est pas une coquetterie. Sans lui, exécutée depuis une business unit enfant, cette requête réussit et ne renvoie rien : la table d’exclusion est vide et aucun désabonné n’est écarté des exports.

/* R2. Cible : Excl_Desabo_BU (Overwrite, data extension partagée)
   À exécuter dans le compte parent. 123456789 = MID de la BU. */
SELECT
    u.SubscriberKey,
    'unsubscribed_bu'  AS Motif,
    u.UnsubDateUTC     AS DateMotif
FROM _BusinessUnitUnsubscribes AS u
WHERE u.BusinessUnitID = 123456789

Celle-ci s’exécute dans le compte parent et écrit dans une data extension partagée, lisible par la business unit de la marque. Prévoyez le dossier de partage et les droits associés avant de planifier l’automation, sinon la requête tourne dans le vide.

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

/* R4. Cible : Excl_Hard_Bounces (Overwrite), clé SubscriberKey
   Hard bounces des 90 derniers jours.
   Contrôlez le libellé exact de la catégorie sur votre compte. */
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

Une plainte entraîne ici une exclusion, même si le statut de l’abonné est redevenu actif entre-temps. C’est un choix d’entreprise, pas une règle de la plateforme : assumez-le et écrivez-le dans votre documentation de ciblage.

Le coût des écritures déconseillées

R1 s’appuie sur un IN et R2 sur une égalité portant sur une colonne qui n’est probablement pas indexée. Les deux figurent dans les écritures que Salesforce recommande d’éviter, et je les garde quand même : sur une table d’exclusion recalculée une fois par jour, hors de tout créneau d’envoi, le coût reste raisonnable. Relevez tout de même leur durée pendant les premières semaines, surtout si votre base dépasse quelques millions d’abonnés.

Une seule table d’exclusion

R5 réunit les quatre tables et ne garde qu’un motif par clé, selon un ordre de priorité. C’est la seule table que les ciblages doivent lire.

/* R5. Cible : Exclusions (Overwrite), clé SubscriberKey
   Dépend de R1 à R4. Priorité : BU, statut, plainte, 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

Conserver le motif et sa date permet de répondre à la question que pose tôt ou tard le délégué à la protection des données : pourquoi cette personne n’a-t-elle pas été contactée, et depuis quand ? La même table sert au comptage, aux exports et aux autres canaux, ce qui garantit que tout le monde applique la même règle.

Les clics humains et le score

Salesforce rappelle que le suivi des ouvertures dépend du chargement d’une image, et que les ouvertures comme les clics enregistrés peuvent provenir d’un outil de sécurité qui suit les liens avant le destinataire. Un article de support va plus loin : les systèmes de suivi ne savent pas distinguer un clic humain d’un clic de machine. R6 applique le filtre le plus simple qui tienne, en écartant les clics survenus moins de 100 secondes après l’envoi correspondant.

/* R6. Cible : Clics_Humains (Overwrite)
   Clé : JobID, ListID, BatchID, SubscriberID, EventDate
   Sources : historiques d’envois et de clics, 12 derniers mois. */
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)

Le seuil de 100 secondes vient d’un article de support de Salesforce consacré aux clics des logiciels antispam, qui le présente comme ajustable. Il écarte au passage quelques clics humains très rapides : le compromis est assumé, et il vaut mieux perdre un vrai lecteur que de classer comme engagée une passerelle de sécurité.

Le score d'engagement sur douze mois

R7 calcule ensuite, par abonné et sur douze mois, le nombre d’envois, la dernière ouverture et le dernier clic humain, puis attribue un segment. Chaque source est agrégée avant la jointure, ce qui limite le volume manipulé.

/* R7. Cible : Score_Engagement (Overwrite), clé SubscriberKey
   Dépend de R6. Une ouverture seule ne suffit pas à être « 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

Les trois agrégats viennent de sous-requêtes sur les historiques d’envois, d’ouvertures et de clics humains, reliées par SubscriberKey en jointure externe. L’ordre des WHEN compte, puisque le premier vrai l’emporte. Le segment Ouvreur_Seul isole les contacts dont l’engagement repose uniquement sur des ouvertures, fiables ou non : ils ne sont ni engagés, ni inactifs, et c’est à vous de décider s’ils reçoivent la relance, idéalement après un test sur un échantillon. Les libellés sont écrits sans accent pour éviter les surprises lors des comparaisons et des exports.

Images bloquées et ouvertures manquantes

Le biais fonctionne dans les deux sens. Les images bloquées produisent l’effet inverse de la protection d’Apple : des lectures réelles qui ne laissent aucune ouverture enregistrée. Cela conforte le choix de faire reposer le segment engagé sur le clic, qui reste visible quelles que soient les images. Salesforce a suivi le même raisonnement dans ses fonctionnalités Einstein, avec un taux d’engagement qui combine clics et ouvertures au lieu de s’appuyer sur les seules ouvertures.

Sur un compte volumineux, mesurez la durée de cette requête avant de la planifier. Trois agrégations sur douze mois d’historique peuvent justifier un découpage en tables intermédiaires.

La population à réactiver

R8 croise les clients, le score et la table d’exclusion. C’est cette table, et elle seule, qui part vers l’envoi comme vers le centre d’appels.

/* R8. Cible : Population_A_Reactiver (Overwrite), clé ClientId
   Dépend de R5 et R7. Lue par l'envoi et par l'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

Le LEFT JOIN suivi de x.SubscriberKey IS NULL ne garde que les clients absents de la table d’exclusion. Changer de canal ne change rien à la règle : le fichier envoyé au centre d’appels sort de la même requête que l’audience de l’email, avec les mêmes exclusions appliquées au même moment.

Si vous venez d’Adobe Campaign

Dans Adobe Campaign Classic, les règles de filtrage des typologies écartent les destinataires en quarantaine ou en liste noire au moment de l’analyse de la diffusion, et les exclusions apparaissent dans le rapport d’analyse. Le mécanisme est réutilisable : une typologie se pose sur toutes les diffusions qui la référencent.

Marketing Cloud applique les statuts à l’envoi, et les listes de suppression couvrent une partie du besoin, mais il n’existe pas de jeu de règles réutilisable équivalent aux typologies. La table Exclusions en tient lieu, à une condition près, qui est aussi sa faiblesse : chaque ciblage doit penser à la lire. Les quarantaines de Campaign, elles, correspondent ici au statut en attente et à _Bounce, avec une différence notable, puisque les événements de _Bounce disparaissent après six mois.

Le réflexe à garder

Tout ce qui sort de Marketing Cloud sans passer par un envoi sort sans filtre. Export vers un centre d’appels, fichier pour un prestataire SMS, alimentation d’un CRM : demandez à chaque fois quelle table d’exclusion a été lue, et depuis quand elle est recalculée.

Questions fréquentes