# Abfragbare Analysen mit SQL

> Fragen Sie Ihre Versanddaten mit dem Queryable-Editor oder eigenem ClickHouse-SQL ab, lernen Sie die verfügbaren Tabellen und Limits kennen und starten Sie mit Beispielabfragen.

Im Tab **Queryable** unter **Email API → Analytics** stellen Sie Ihren Versanddaten eigene Fragen, entweder mit einem Editor per Mausklick oder mit ClickHouse-SQL mit reinem Lesezugriff. Er ist in diesen Tarifen verfügbar: Business, Custom. Ergebnisse erscheinen als Diagramm und Tabelle, und Sie können Abfragen für Ihr Team speichern.

## Abfrage-Editor

Der Editor schreibt die Abfrage für Sie und verwendet den Zeitraum sowie die Filter **Sending domain** und **API key** der Seite.

| Steuerelement | Optionen |
| --- | --- |
| **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` (API-Schlüssel), `campaign`, `tag`, `country` |
| **Chart type** | **Timeseries**, **Pie**, **Stat**, **Table** |

Nicht jede Kombination greift. **Group by** `country` funktioniert mit Ladevorgängen und Klicks, die ein Land haben; `status`, `domain`, `credential`, `campaign` und `tag` funktionieren mit den E-Mail-Kennzahlen (Versände, Bounces, fehlgeschlagene, abgelehnte und gesperrte E-Mails, eindeutige E-Mails und die beiden Raten). Beschwerden und die durchschnittliche Zustelldauer werden nicht gruppiert. Gruppen zeigen IDs wie `dom_…` und `key_…`; um Namen zu verknüpfen, verwenden Sie eigenes SQL. Gruppieren nach `tag` liefert meist eine einzige Gruppe `(none)`.

Wählen Sie **Run**, um das Ergebnis zu sehen.

## Eigenes SQL

Aktivieren Sie **Raw SQL**, um ClickHouse-SQL im Editor zu schreiben, und wählen Sie dann **Run**.

- Abfragen müssen mit `SELECT` oder `WITH` beginnen und können nur die unten aufgeführten Tabellen lesen. Schreibzugriffe, Tabellenfunktionen wie `url()` oder `s3()` und `system`-Tabellen sind gesperrt.
- Jede Tabelle ist automatisch auf Ihren Workspace beschränkt. Sie brauchen keine Bedingung auf `workspace_oid`.
- **Zeitraum und Filter der Seite gelten nicht für eigenes SQL.** Fügen Sie eine eigene Zeitbedingung hinzu, etwa `created_at >= now() - INTERVAL 30 DAY`, sonst durchsuchen Abfragen alle aufbewahrten Daten.
- Jedes `FROM` und `JOIN` muss eine Tabelle direkt nennen. Unterabfragen in Klammern sind erlaubt, aber setzen Sie keinen Alias hinter einen Tabellennamen (`FROM emails_raw e`) und fragen Sie nicht aus einer benannten `WITH`-Abfrage ab. Qualifizieren Sie Spalten stattdessen mit dem Tabellennamen, zum Beispiel `sending_domains_dim.name`.
- Abfragen liefern höchstens 5.000 Zeilen und brechen nach 10 Sekunden ab. Eine Abfrage darf bis zu 20.000 Zeichen lang sein.

Um das Ergebnis als Diagramm darzustellen, geben Sie eine Zeitspalte namens `t`, eine numerische Spalte namens `v` und optional eine Spalte `group_key` für eine Reihe pro Wert zurück. Jede andere Form wird als Tabelle angezeigt. Die Tabelle auf der Seite zeigt die ersten 100 Zeilen.

## Gespeicherte Abfragen

Geben Sie unter **Save as…** einen Namen ein und wählen Sie **Save query**, um die aktuellen Editor-Einstellungen oder das SQL zu speichern. Gespeicherte Abfragen erscheinen für alle im Workspace als Schaltflächen über den Ergebnissen. Wählen Sie eine aus, um sie zu laden, oder ihr **×**, um sie zu löschen.

## Tabellen

Die `_raw`-Tabellen werden bis zu 365 Tage aufbewahrt. Die `_daily`-Tabellen enthalten Tagessummen und folgen der Aufbewahrungsdauer für Analysen in Ihrem Tarif.

### emails_raw

Eine Zeile pro Version jeder E-Mail. Jede Statusänderung fügt eine neue Version hinzu, daher erscheint eine E-Mail mehrfach. Gruppieren Sie nach `oid` und lesen Sie mit `argMax(column, _peerdb_version)` den neuesten Wert, wie in den Beispielen unten.

| Spalte | Beschreibung |
| --- | --- |
| `oid` | E-Mail-ID (`em_…`). |
| `type` | `outgoing` für gesendete E-Mails, `inbound` für empfangene E-Mails. |
| `status` | Der E-Mail-Status, z. B. `delivered` oder `bounced`. |
| `rcpt_to`, `mail_from`, `subject`, `message_id` | Envelope und Betreff. |
| `sending_domain_oid`, `credential_oid` | ID der Versanddomain (`dom_…`) und ID des API-Schlüssels (`key_…`). |
| `campaign_oid`, `automation_oid` | Gesetzt bei Versänden aus Kampagnen und Automatisierungen. |
| `tag` | Meist leer. |
| `spam_score`, `inspected` | Rspamd-Score und ob die E-Mail bewertet wurde. |
| `size` | Größe der Nachricht in Bytes. |
| `loaded`, `clicked` | Zeitpunkt der ersten Öffnung und des ersten Klicks oder `NULL`. |
| `created_at`, `updated_at` | Zeitstempel in UTC. |
| `_peerdb_version` | Versionsnummer; höher ist neuer. |

### deliveries_raw

Eine Zeile pro Zustelldatensatz: jeder Versuch sowie Datensätze wie zurückgehalten oder gesperrt.

| Spalte | Beschreibung |
| --- | --- |
| `oid`, `email_oid` | Zustell-ID und die E-Mail, zu der sie gehört. |
| `status` | Z. B. `delivered`, `attempted`, `bounced`, `held`, `suppressed`. |
| `output` | Die Antwort des Empfangsservers, z. B. `250 2.0.0 OK`. |
| `details` | Beschreibung des Ergebnisses durch Emailit. |
| `time` | Wie lange der Versuch gedauert hat; der Wert hinter **Time to inbox**. |
| `sent_with_ssl` | Ob die Verbindung TLS verwendet hat. |
| `timestamp`, `created_at` | Wann der Versuch stattfand. |

### loads_raw und clicks_raw

Eine Zeile pro Öffnung oder Klick.

| Spalte | Beschreibung |
| --- | --- |
| `oid`, `email_oid` | ID des Ladevorgangs oder Klicks und die E-Mail, zu der er gehört. |
| `link_oid` | Nur `clicks_raw`: die ID des geklickten Links (`link_…`). Link-URLs werden in den Analysetabellen nicht gespeichert. |
| `ip_address`, `country`, `city`, `user_agent` | Woher die Öffnung oder der Klick kam. |
| `timestamp`, `created_at` | Wann sie oder er stattfand. |

### events_raw

Eine Zeile pro [Event](/de/docs/logs/events/): `oid` (`evt_…`), `type`, `data` (der Payload als JSON-String; lesen Sie ihn mit Funktionen wie `JSONExtractString(data, 'object', 'id')`) und `created_at`.

### Tagestabellen

| Tabelle | Spalten |
| --- | --- |
| `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` |

Summieren Sie `cnt` mit `sum(cnt)`. Die übrigen Spalten sind ClickHouse-Aggregatzustände: Lesen Sie sie mit `uniqMerge(unique_emails)`, `uniqMerge(unique_links)`, `avgMerge(avg_time)` und `topKMerge(10)(countries)`.

### Dimensionstabellen

| Tabelle | Spalten |
| --- | --- |
| `sending_domains_dim` | `oid`, `name`, `created_at` |
| `campaigns_dim` | `oid`, `name`, `status`, `created_at` |
| `workspaces_dim` | `oid`, `name`, `plan_id`, `created_at` |

Verknüpfen Sie sie per JOIN, um Namen statt IDs anzuzeigen.

## Beispielabfragen

### Bounce-Rate pro Versanddomain und Tag

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

Wählen Sie das Diagramm **Timeseries**, um eine Linie pro Domain zu erhalten.

### Meistgeklickte Links

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

Die Analysetabellen speichern die Link-ID, nicht die URL. Um die URL eines Links zu sehen, öffnen Sie eine E-Mail mit diesem Klick und prüfen Sie ihren Tab **Clicks**, oder sammeln Sie `link.url` aus den [Webhooks](/de/docs/webhooks/event-types/) für `email.clicked`.

### Perzentile der Zustelldauer

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

### Versände und Öffnungen pro Kampagne

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

## Fehler

| Meldung | Ursache |
| --- | --- |
| Only SELECT queries are allowed | Die Abfrage beginnt nicht mit `SELECT` oder `WITH`. |
| Table "…" is not allowed | Ein `FROM` oder `JOIN` nennt etwas, das keine erlaubte Tabelle ist, etwa eine benannte `WITH`-Abfrage. |
| Query must read an allowlisted analytics table | Die Abfrage hat kein `FROM` mit einer erlaubten Tabelle. |
| External tables and table functions are not allowed | Die Abfrage verwendet eine Tabellenfunktion wie `url()`. |
| Queries cannot target another workspace | Eine Bedingung `workspace_oid = '…'` nennt einen anderen Workspace. |

ClickHouse-Syntaxfehler, etwa durch einen Alias hinter einem Tabellennamen, werden so angezeigt, wie ClickHouse sie zurückgibt.

## Siehe auch

  - [Erweiterte Analysen](/de/docs/analytics/advanced/)
  - [Events](/de/docs/logs/events/)

---
Quelle: https://emailit.com/de/docs/analytics/queryable/