Historiser les data views au-delà de six mois

Une mutuelle santé veut relancer ses adhérents inactifs, définis comme ceux qui n’ont ni ouvert ni cliqué depuis douze mois. La requête de ciblage lit directement _Open et _Click. La première campagne part vers 40 % de la base, un chiffre qui surprend tout le monde au service marketing.

L’explication tient en une ligne : les data views ne contiennent que six mois, et tout adhérent actif il y a huit mois apparaît comme inactif. L’équipe crée alors une table Historique_Ouvertures, alimentée chaque nuit en mode Append avec les ouvertures de la veille. Trois mois plus tard, trois anomalies remontent. L’automation a été relancée à la main un jour d’incident, et les ouvertures de ce jour figurent deux fois. Elle a été suspendue dix jours en août, et ces dix jours manquent. Enfin, les ouvertures enregistrées juste avant minuit n’apparaissent pas toujours.

Aucune de ces trois anomalies ne produit d’erreur. En recette, avec une exécution par jour et aucune interruption, tout semblait correct.

Six mois, et une fenêtre qui glisse

Les pages de référence de _Sent, _Open, _Click, _Bounce, _Unsubscribe et _Complaint indiquent toutes une conservation de six mois. La fenêtre est glissante : chaque jour, les événements les plus anciens disparaissent. Un historique doit donc être alimenté en continu, avant que les données sortent de la fenêtre. Si l’automation reste arrêtée plus de six mois, les événements de la période sont perdus pour de bon.

Fuseau hérité et abonnés supprimés

Deux autres caractéristiques comptent au moment de concevoir les tables. Les dates des data views sont stockées en heure standard du centre des États-Unis, sans changement d’heure, et vos historiques hériteront de ce fuseau. Un abonné supprimé, lui, n’apparaît plus dans la data view, alors qu’il reste dans votre historique : l’écart est normal et il ne faut pas le corriger.

Le type Number d’une data extension accepte un entier jusqu’à environ 2,1 milliards. Il convient aux identifiants actuels, mais les colonnes documentées en bigint peuvent en théorie dépasser cette borne. Surveillez la valeur maximale de JobID dans vos historiques, et prévoyez un type Text si elle s’en approche.

Une clé qui identifie l’événement

Une table d’historique alimentée en mode Update a besoin d’une clé primaire qui identifie un événement de façon stable. Si la même ligne revient lors d’un second passage, elle remplace l’ancienne au lieu de s’ajouter. C’est ce mécanisme, et lui seul, qui rend un chargement rejouable.

Une clé non nullable et bornée

Chaque colonne de la clé doit être non nullable, et la clé complète doit rester sous 1 700 octets, limite indiquée par la référence SQL. C’est pourquoi l’URL d’un clic, longue et parfois vide, est stockée comme simple attribut. SubscriberKey est conservée à côté de la clé, puisque c’est elle qui sert aux jointures avec vos data extensions.

Charger sur un repère, pas sur une horloge

Relire six mois de _Open chaque nuit est lent et inutile. On ne charge que le nouveau, en s’appuyant sur un repère stocké dans une petite table de paramètres : la date du dernier événement déjà chargé. Trois règles le rendent fiable. Le repère est la date du dernier événement présent dans l’historique, pas l’heure d’exécution de l’automation. On recule le point de départ d’une fenêtre de chevauchement, 24 heures par exemple, pour rattraper les événements arrivés en retard dans la data view. Et le repère n’avance qu’après un chargement réussi, dans une étape distincte.

La cible ne peut pas être lue

Une contrainte de la plateforme oriente le reste de la conception : la documentation de la Query Activity précise que la data extension cible ne peut pas être une data extension utilisée dans la requête. On ne peut donc pas exclure les lignes déjà présentes en lisant l’historique que l’on alimente. La clé composite et le mode Update remplacent ce besoin.

/* Historique_Ouvertures, mode Update */
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

Le calcul de la borne porte sur le paramètre, pas sur EventDate : la colonne de la data view reste nue, comme le demandent les recommandations de performance. La sous-requête avec ROW_NUMBER() garantit une seule ligne par clé, même si une anomalie de données renvoyait deux ouvertures uniques pour le même envoi. Pour les clics, on ajoute EventDate à la partition ; pour les envois, on lit _Sent sans condition sur IsUnique, qui n’y existe pas.

Faire avancer le repère, puis surveiller

Une requête distincte recalcule le repère à partir de ce qui vient d’être chargé. Elle lit les historiques et écrit dans la table de paramètres, donc elle ne lit pas sa propre cible.

/* Parametres_Traitement, mode Update */
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

Pourquoi le HAVING protège le repère

Le filtre sur DateChargement ne regarde que les lignes écrites par le passage du jour. Comme la fenêtre de chevauchement recharge l’événement le plus récent de la veille, le maximum obtenu ne peut pas être inférieur à l’ancien repère. Le HAVING protège le cas où rien n’a été chargé : sans lui, la requête renverrait une ligne avec un repère vide, et le chargement suivant ne trouverait plus rien du tout. Ici, elle ne renvoie aucune ligne et le repère reste inchangé.

La colonne qui porte la durée de chevauchement ne figure pas dans la sélection. On s’attend à ce qu’une écriture en mode Update laisse inchangées les colonnes absentes du SELECT ; vérifiez-le sur votre compte avant la mise en production, sinon ajoutez la colonne à la requête avec sa valeur.

Ordonner les étapes de l'automation

L’automation quotidienne enchaîne une requête par étape : envois, ouvertures, clics, puis la mise à jour des repères en dernier. Si une étape échoue avant la dernière, le repère n’avance pas et le passage suivant reprend au bon endroit. Planifiez-la la nuit, à une heure décalée, et bien avant les automations qui lisent les historiques.

Rétention et registre des traitements

Reste la durée de conservation. Réglez une politique de rétention sur chaque historique, par exemple la suppression des enregistrements individuels au-delà de deux ans, et documentez le choix dans votre registre des traitements. Un historique d’engagement est une donnée personnelle : sa durée se justifie par l’usage, pas par la place disponible.

Si vous venez d’Adobe Campaign

Dans Adobe Campaign Classic, les logs de diffusion et de tracking restent en base jusqu’à leur purge par le workflow technique de nettoyage, avec des délais que vous réglez dans l’assistant de déploiement. L’historique existe par défaut, et la vraie question est de savoir quand le purger.

Dans Marketing Cloud, c’est l’inverse : la fenêtre de six mois est fixe, et tout ce que vous voulez garder plus longtemps doit être copié par vos soins. Le repère stocké dans la table de paramètres joue le rôle d’une variable d’instance de workflow que l’on conserverait d’une exécution à l’autre, à ceci près que vous l’écrivez, vous le lisez et vous le surveillez vous-même.

Le réflexe à garder

Avant d’écrire la moindre requête d’engagement sur douze mois, demandez quand l’historique a été mis en place et s’il a connu des interruptions. Une table qui démarre il y a trois mois ne répond pas à la question posée, et personne ne s’en apercevra tant que le chiffre obtenu restera plausible.

Questions fréquentes