Consultas SQL

La pestaña Queryable de Email API›Analytics te permite plantear tus propias preguntas sobre tus datos de envío, con un generador visual o con SQL de ClickHouse de solo lectura. Está disponible en Pay as you goProBusinessCustom. Los resultados se muestran en un gráfico y una tabla, y puedes guardar consultas para tu equipo.

Generador

El generador escribe la consulta por ti y usa el periodo y los filtros Sending domain y API key de la página.

No todas las combinaciones son válidas. Group by country funciona con las cargas y los clics, que tienen país; status, domain, credential, campaign y tag funcionan con las métricas de emails (envíos, rebotes, fallidos, rechazados, bloqueados, emails únicos y las dos tasas). Las quejas y el tiempo medio de entrega no se agrupan. Los grupos muestran ID como dom_… y key_…; usa SQL directo para añadir los nombres con un join. Agrupar por tag suele devolver un único grupo (none).

Selecciona Run para ver el resultado.

SQL directo

Activa Raw SQL para escribir SQL de ClickHouse en el editor y selecciona Run.

• Las consultas deben empezar por SELECT o WITH y solo pueden leer las tablas que se indican más abajo. Las escrituras, las funciones de tabla como url() o s3() y las tablas system están bloqueadas.
• Cada tabla se limita automáticamente a tu espacio de trabajo. No necesitas una condición workspace_oid.
• El periodo y los filtros de la página no se aplican al SQL directo. Añade tu propia condición de tiempo, como created_at >= now() - INTERVAL 30 DAY, o las consultas recorrerán todos los datos conservados.
• Cada FROM y cada JOIN debe nombrar una tabla directamente. Las subconsultas entre paréntesis están permitidas, pero no pongas un alias después del nombre de una tabla (FROM emails_raw e) ni selecciones de una consulta WITH con nombre. En su lugar, cualifica las columnas con el nombre de la tabla, por ejemplo sending_domains_dim.name.
• Las consultas devuelven como máximo 5000 filas y se detienen a los 10 segundos. Una consulta puede tener hasta 20.000 caracteres.

Para mostrar el resultado en un gráfico, devuelve una columna de tiempo llamada t, una columna numérica llamada v y, opcionalmente, una columna group_key para obtener una serie por valor. Cualquier otra forma se muestra como tabla. La tabla de la página muestra las 100 primeras filas.

Consultas guardadas

Escribe un nombre en Save as… y selecciona Save query para guardar la configuración actual del generador o el SQL. Las consultas guardadas aparecen como botones encima de los resultados para todos los miembros del espacio de trabajo. Selecciona una para cargarla o selecciona su × para eliminarla.

Tablas

Las tablas _raw se conservan hasta 365 días. Las tablas _daily contienen totales diarios y siguen la retención de analítica de tu plan.

emails_raw

Una fila por cada versión de cada email. Cada cambio de estado añade una versión nueva, así que un email aparece varias veces. Agrupa por oid y usa argMax(column, _peerdb_version) para leer el último valor, como en los ejemplos de más abajo.

deliveries_raw

Una fila por cada registro de entrega: cada intento y registros como los de emails retenidos o bloqueados.

loads_raw y clicks_raw

Una fila por cada apertura o clic.

events_raw

Una fila por cada evento: oid (evt_…), type, data (el payload como cadena JSON; léelo con funciones como JSONExtractString(data, 'object', 'id')) y created_at.

Tablas diarias

Suma cnt con sum(cnt). Las demás columnas son estados de agregación de ClickHouse: léelas con uniqMerge(unique_emails), uniqMerge(unique_links), avgMerge(avg_time) y topKMerge(10)(countries).

Tablas de dimensiones

Únelas con un join para mostrar nombres en lugar de ID.

Consultas de ejemplo

Tasa de rebote por dominio de envío y día

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

Elige el gráfico Timeseries para obtener una línea por dominio.

Enlaces con más clics

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

Las tablas de analítica guardan el ID del enlace, no la URL. Para ver la URL de un enlace, abre un email que tenga el clic y consulta su pestaña Clicks, o recoge link.url de los webhooks email.clicked.

Percentiles del tiempo de entrega

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

Envíos y aperturas por campaña

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

Errores

Los errores de sintaxis de ClickHouse, por ejemplo por un alias después del nombre de una tabla, se muestran tal como los devuelve ClickHouse.

Ver también