# Statistiques interrogeables en SQL

> Interrogez vos données d’envoi avec le générateur Queryable ou en SQL ClickHouse brut, découvrez les tables disponibles et les limites, et partez d’exemples de requêtes.

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 Business, Custom. 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.

| Contrôle | Options |
| --- | --- |
| **Measure** | `sends`, `bounces`, `complaints`, `loads`, `clicks`, `unique loads`, `unique clicks`, `unique emails`, `failed`, `rejected`, `suppressed`, `avg delivery time`, `bounce rate`, `complaint rate` |
| **Time grain** | `hour`, `day`, `week` |
| **Group by** | `none`, `status`, `domain`, `credential` (clé API), `campaign`, `tag`, `country` |
| **Chart type** | **Timeseries**, **Pie**, **Stat**, **Table** |

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.

| Colonne | Description |
| --- | --- |
| `oid` | ID de l’e-mail (`em_…`). |
| `type` | `outgoing` pour les e-mails envoyés, `inbound` pour les e-mails reçus. |
| `status` | Le statut de l’e-mail, par exemple `delivered` ou `bounced`. |
| `rcpt_to`, `mail_from`, `subject`, `message_id` | Enveloppe et objet. |
| `sending_domain_oid`, `credential_oid` | ID du domaine d’envoi (`dom_…`) et ID de la clé API (`key_…`). |
| `campaign_oid`, `automation_oid` | Renseignés pour les envois de campagnes et d’automatisations. |
| `tag` | Généralement vide. |
| `spam_score`, `inspected` | Score Rspamd, et indication que l’e-mail a été évalué ou non. |
| `size` | Taille du message en octets. |
| `loaded`, `clicked` | Heure de la première ouverture et du premier clic, ou `NULL`. |
| `created_at`, `updated_at` | Horodatages en UTC. |
| `_peerdb_version` | Numéro de version ; plus il est élevé, plus la version est récente. |

### deliveries_raw

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

| Colonne | Description |
| --- | --- |
| `oid`, `email_oid` | ID de la livraison et e-mail auquel elle appartient. |
| `status` | Par exemple `delivered`, `attempted`, `bounced`, `held`, `suppressed`. |
| `output` | La réponse du serveur destinataire, par exemple `250 2.0.0 OK`. |
| `details` | La description du résultat par Emailit. |
| `time` | La durée de la tentative, la valeur utilisée par **Time to inbox**. |
| `sent_with_ssl` | Indique si la connexion utilisait TLS. |
| `timestamp`, `created_at` | Moment de la tentative. |

### loads_raw et clicks_raw

Une ligne par ouverture ou clic.

| Colonne | Description |
| --- | --- |
| `oid`, `email_oid` | ID du chargement ou du clic et e-mail auquel il appartient. |
| `link_oid` | `clicks_raw` uniquement : l’ID du lien cliqué (`link_…`). Les URL des liens ne sont pas stockées dans les tables de statistiques. |
| `ip_address`, `country`, `city`, `user_agent` | Provenance de l’ouverture ou du clic. |
| `timestamp`, `created_at` | Moment où il a eu lieu. |

### events_raw

Une ligne par [événement](/fr/docs/logs/events/) : `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

| Table | Colonnes |
| --- | --- |
| `events_daily` | `day`, `type`, `cnt`, `unique_emails` |
| `deliveries_daily` | `day`, `status`, `cnt`, `avg_time` |
| `loads_daily` | `day`, `cnt`, `unique_emails`, `countries` |
| `clicks_daily` | `day`, `cnt`, `unique_emails`, `unique_links` |

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

| Table | Colonnes |
| --- | --- |
| `sending_domains_dim` | `oid`, `name`, `created_at` |
| `campaigns_dim` | `oid`, `name`, `status`, `created_at` |
| `workspaces_dim` | `oid`, `name`, `plan_id`, `created_at` |

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

```sql
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

```sql
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](/fr/docs/webhooks/event-types/) `email.clicked`.

### Percentiles du délai de livraison

```sql
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

```sql
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

| Message | Cause |
| --- | --- |
| Only SELECT queries are allowed | La requête ne commence pas par `SELECT` ou `WITH`. |
| Table "…" is not allowed | Un `FROM` ou un `JOIN` nomme autre chose qu’une table autorisée, par exemple une requête `WITH` nommée. |
| Query must read an allowlisted analytics table | La requête ne comporte aucun `FROM` portant sur une table autorisée. |
| External tables and table functions are not allowed | La requête utilise une fonction de table comme `url()`. |
| Queries cannot target another workspace | Une condition `workspace_oid = '…'` désigne un autre espace de travail. |

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

  - [Statistiques avancées](/fr/docs/analytics/advanced/)
  - [Événements](/fr/docs/logs/events/)

---
Source: https://emailit.com/fr/docs/analytics/queryable/