Statistiques interrogeables en SQL

L’onglet Queryable de Email API›Analytics vous permet de poser vos propres questions à vos données d’envoi, soit avec un générateur visuel, soit en SQL ClickHouse en lecture seule. Il est disponible sur Pay as you goProBusinessCustom. Les résultats s’affichent sous forme de graphique et de tableau, et vous pouvez enregistrer des requêtes pour votre équipe.

Générateur

Le générateur écrit la requête à votre place et utilise la plage de dates et les filtres Sending domain et API key de la page.

Toutes les combinaisons ne s’appliquent pas. Group by country fonctionne avec les chargements et les clics, qui ont un pays ; status, domain, credential, campaign et tag fonctionnent avec les mesures portant sur les e-mails (envois, rebonds, en échec, rejetés, bloqués, e-mails uniques et les deux taux). Les plaintes et la durée moyenne de livraison ne sont pas regroupées. Les groupes affichent des ID comme dom_… et key_… ; utilisez le SQL brut pour joindre les noms. Le regroupement par tag renvoie généralement un seul groupe (none).

Sélectionnez Run pour afficher le résultat.

SQL brut

Activez Raw SQL pour écrire du SQL ClickHouse dans l’éditeur, puis sélectionnez Run.

• Les requêtes doivent commencer par SELECT ou WITH et ne peuvent lire que les tables listées ci-dessous. Les écritures, les fonctions de table comme url() ou s3() et les tables system sont bloquées.
• Chaque table est automatiquement limitée à votre espace de travail. Vous n’avez pas besoin de condition workspace_oid.
• La plage de dates et les filtres de la page ne s’appliquent pas au SQL brut. Ajoutez votre propre condition temporelle, comme created_at >= now() - INTERVAL 30 DAY, sinon les requêtes parcourent toutes les données conservées.
• Chaque FROM et chaque JOIN doit nommer directement une table. Les sous-requêtes entre parenthèses sont acceptées, mais ne placez pas d’alias après un nom de table (FROM emails_raw e) et ne sélectionnez pas depuis une requête WITH nommée. Qualifiez plutôt les colonnes avec le nom de la table, par exemple sending_domains_dim.name.
• Les requêtes renvoient au maximum 5 000 lignes et s’arrêtent au bout de 10 secondes. Une requête peut compter jusqu’à 20 000 caractères.

Pour afficher le résultat sous forme de graphique, renvoyez une colonne temporelle nommée t, une colonne numérique nommée v et, si vous le souhaitez, une colonne group_key pour obtenir une série par valeur. Tout autre format s’affiche sous forme de tableau. Le tableau de la page affiche les 100 premières lignes.

Requêtes enregistrées

Saisissez un nom dans Save as… et sélectionnez Save query pour enregistrer les réglages actuels du générateur ou le SQL. Les requêtes enregistrées apparaissent sous forme de boutons au-dessus des résultats, pour tous les membres de l’espace de travail. Sélectionnez-en une pour la charger, ou sélectionnez son × pour la supprimer.

Tables

Les tables _raw sont conservées jusqu’à 365 jours. Les tables _daily contiennent les totaux quotidiens et suivent la durée de conservation des statistiques de votre forfait.

emails_raw

Une ligne par version de chaque e-mail. Chaque changement de statut ajoute une nouvelle version : un e-mail apparaît donc plusieurs fois. Regroupez par oid et utilisez argMax(column, _peerdb_version) pour lire la dernière valeur, comme dans les exemples ci-dessous.

deliveries_raw

Une ligne par enregistrement de livraison : chaque tentative, ainsi que les enregistrements comme retenu ou bloqué.

loads_raw et clicks_raw

Une ligne par ouverture ou clic.

events_raw

Une ligne par événement : oid (evt_…), type, data (le payload sous forme de chaîne JSON ; lisez-le avec des fonctions comme JSONExtractString(data, 'object', 'id')) et created_at.

Tables quotidiennes

Additionnez cnt avec sum(cnt). Les autres colonnes sont des états d’agrégation ClickHouse : lisez-les avec uniqMerge(unique_emails), uniqMerge(unique_links), avgMerge(avg_time) et topKMerge(10)(countries).

Tables de dimensions

Joignez-les pour afficher des noms plutôt que des ID.

Exemples de requêtes

Taux de rebond par domaine d’envoi et par jour

SELECT
  toDate(latest.first_seen) AS t,
  sending_domains_dim.name AS group_key,
  countIf(latest.status = 'bounced') / count() AS v
FROM (
  SELECT
    oid,
    min(created_at) AS first_seen,
    argMax(status, _peerdb_version) AS status,
    argMax(sending_domain_oid, _peerdb_version) AS sending_domain_oid
  FROM emails_raw
  WHERE type = 'outgoing'
    AND created_at >= now() - INTERVAL 30 DAY
  GROUP BY oid
) AS latest
LEFT JOIN sending_domains_dim ON sending_domains_dim.oid = latest.sending_domain_oid
GROUP BY t, group_key
ORDER BY t, group_key

Choisissez le graphique Timeseries pour obtenir une courbe par domaine.

Liens les plus cliqués

SELECT
  link_oid,
  count() AS clicks,
  uniqExact(email_oid) AS emails_clicked
FROM clicks_raw
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY link_oid
ORDER BY clicks DESC
LIMIT 20

Les tables de statistiques stockent l’ID du lien, pas son URL. Pour voir l’URL d’un lien, ouvrez un e-mail qui comporte le clic et consultez son onglet Clicks, ou récupérez link.url dans les webhooks email.clicked.

Percentiles du délai de livraison

SELECT
  toDate(timestamp) AS t,
  count() AS deliveries,
  quantile(0.5)(toFloat64(time)) AS p50,
  quantile(0.95)(toFloat64(time)) AS p95,
  quantile(0.99)(toFloat64(time)) AS p99
FROM deliveries_raw
WHERE status = 'delivered'
  AND time IS NOT NULL
  AND timestamp >= now() - INTERVAL 14 DAY
GROUP BY t
ORDER BY t

Envois et ouvertures par campagne

SELECT
  campaigns_dim.name AS campaign,
  count() AS sent,
  countIf(latest.loaded IS NOT NULL) AS opened,
  countIf(latest.clicked IS NOT NULL) AS clicked
FROM (
  SELECT
    oid,
    argMax(campaign_oid, _peerdb_version) AS campaign_oid,
    argMax(loaded, _peerdb_version) AS loaded,
    argMax(clicked, _peerdb_version) AS clicked
  FROM emails_raw
  WHERE type = 'outgoing'
    AND created_at >= now() - INTERVAL 90 DAY
  GROUP BY oid
) AS latest
INNER JOIN campaigns_dim ON campaigns_dim.oid = latest.campaign_oid
GROUP BY campaign
ORDER BY sent DESC

Erreurs

Les erreurs de syntaxe ClickHouse, par exemple dues à un alias après un nom de table, s’affichent telles que ClickHouse les renvoie.

Voir aussi