Abfragbare Analysen mit SQL

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: Pay as you goProBusinessCustom. 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.

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.

deliveries_raw

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

loads_raw und clicks_raw

Eine Zeile pro Öffnung oder Klick.

events_raw

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

Tagestabellen

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

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

Beispielabfragen

Bounce-Rate pro Versanddomain und Tag

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

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 für email.clicked.

Perzentile der Zustelldauer

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

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

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

Siehe auch