Analisi interrogabili in SQL

La scheda Queryable di Email API›Analytics ti permette di porre le tue domande ai dati di invio, con un generatore di query a selezione oppure con SQL ClickHouse in sola lettura. È disponibile nei piani Pay as you goProBusinessCustom. I risultati vengono mostrati come grafico e come tabella, e puoi salvare le query per il tuo team.

Generatore

Il generatore scrive la query per te e usa l’intervallo di date e i filtri Sending domain e API key della pagina.

Non tutte le combinazioni sono valide. Group by country funziona con caricamenti e clic, che hanno un paese; status, domain, credential, campaign e tag funzionano con le misure sulle email (invii, bounce, non riuscite, rifiutate, soppresse, email uniche e i due tassi). Segnalazioni e tempo medio di consegna non si raggruppano. I gruppi mostrano ID come dom_… e key_…; per unire i nomi usa SQL diretto. Il raggruppamento per tag di solito restituisce un solo gruppo (none).

Seleziona Run per vedere il risultato.

SQL diretto

Attiva Raw SQL per scrivere SQL ClickHouse nell’editor, poi seleziona Run.

• Le query devono iniziare con SELECT o WITH e possono leggere solo le tabelle elencate più sotto. Scritture, funzioni di tabella come url() o s3() e tabelle system sono bloccate.
• Ogni tabella è limitata automaticamente al tuo workspace. Non serve una condizione su workspace_oid.
• L’intervallo di date e i filtri della pagina non si applicano all’SQL diretto. Aggiungi una tua condizione temporale, come created_at >= now() - INTERVAL 30 DAY, altrimenti le query scansionano tutti i dati conservati.
• Ogni FROM e JOIN deve nominare direttamente una tabella. Le sottoquery tra parentesi vanno bene, ma non mettere un alias dopo il nome di una tabella (FROM emails_raw e) e non selezionare da una query WITH con nome. Qualifica invece le colonne con il nome della tabella, ad esempio sending_domains_dim.name.
• Le query restituiscono al massimo 5000 righe e si interrompono dopo 10 secondi. Una query può contenere fino a 20.000 caratteri.

Per mostrare il risultato in un grafico, restituisci una colonna temporale chiamata t, una colonna numerica chiamata v e, facoltativamente, una colonna group_key per avere una serie per ogni valore. Qualsiasi altra forma viene mostrata come tabella. La tabella nella pagina mostra le prime 100 righe.

Query salvate

Scrivi un nome in Save as… e seleziona Save query per memorizzare le impostazioni correnti del generatore o l’SQL. Le query salvate compaiono come pulsanti sopra i risultati per tutti i membri del workspace. Selezionane una per caricarla, oppure seleziona la sua × per eliminarla.

Tabelle

Le tabelle _raw vengono conservate fino a 365 giorni. Le tabelle _daily contengono i totali giornalieri e seguono la conservazione delle analisi del piano.

emails_raw

Una riga per ogni versione di ciascuna email. Ogni cambio di stato aggiunge una nuova versione, quindi un’email compare più volte. Raggruppa per oid e usa argMax(column, _peerdb_version) per leggere il valore più recente, come negli esempi più sotto.

deliveries_raw

Una riga per ogni record di consegna: ogni tentativo e record come quelli delle email trattenute o soppresse.

loads_raw e clicks_raw

Una riga per ogni apertura o clic.

events_raw

Una riga per ogni evento: oid (evt_…), type, data (il payload come stringa JSON; leggilo con funzioni come JSONExtractString(data, 'object', 'id')) e created_at.

Tabelle giornaliere

Somma cnt con sum(cnt). Le altre colonne sono stati di aggregazione di ClickHouse: leggile con uniqMerge(unique_emails), uniqMerge(unique_links), avgMerge(avg_time) e topKMerge(10)(countries).

Tabelle di dimensione

Uniscile con un join per mostrare i nomi al posto degli ID.

Query di esempio

Tasso di bounce per dominio di invio al giorno

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

Scegli il grafico Timeseries per avere una linea per dominio.

Link più cliccati

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

Le tabelle delle analisi memorizzano l’ID del link, non l’URL. Per vedere l’URL di un link, apri un’email che ha ricevuto il clic e controlla la sua scheda Clicks, oppure raccogli link.url dai webhook email.clicked.

Percentili del tempo di consegna

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

Invii e aperture per campagna

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

Errori

Gli errori di sintassi di ClickHouse, ad esempio per un alias dopo il nome di una tabella, vengono mostrati così come li restituisce ClickHouse.

Vedi anche