Consultas SQL (Queryable)

A aba Queryable de Email API›Analytics permite que você faça as suas próprias perguntas aos seus dados de envio, com um editor de apontar e clicar ou com SQL do ClickHouse somente leitura. Ela está disponível em Pay as you goProBusinessCustom. Os resultados aparecem como um gráfico e uma tabela, e você pode salvar consultas para a sua equipe.

Editor

O editor escreve a consulta para você e usa o período e os filtros Sending domain e API key da página.

Nem toda combinação se aplica. Group by country funciona com carregamentos e cliques, que têm um país; status, domain, credential, campaign e tag funcionam com as medidas de e-mail (envios, bounces, com falha, rejeitados, suprimidos, e-mails únicos e as duas taxas). Reclamações e tempo médio de entrega não são agrupados. Os grupos mostram IDs como dom_… e key_…; use SQL direto para juntar os nomes. Agrupar por tag normalmente retorna um único grupo (none).

Selecione Run para ver o resultado.

SQL direto

Ative Raw SQL para escrever SQL do ClickHouse no editor e selecione Run.

• As consultas precisam começar com SELECT ou WITH e só podem ler as tabelas listadas abaixo. Escritas, funções de tabela como url() ou s3() e tabelas system são bloqueadas.
• Toda tabela fica limitada automaticamente ao seu workspace. Você não precisa de uma condição workspace_oid.
• O período e os filtros da página não se aplicam ao SQL direto. Adicione a sua própria condição de tempo, como created_at >= now() - INTERVAL 30 DAY, ou as consultas percorrem todos os dados retidos.
• Cada FROM e JOIN precisa nomear uma tabela diretamente. Subconsultas entre parênteses são aceitas, mas não coloque um alias depois do nome de uma tabela (FROM emails_raw e) e não selecione a partir de uma consulta WITH nomeada. Em vez disso, qualifique as colunas com o nome da tabela, por exemplo sending_domains_dim.name.
• As consultas retornam no máximo 5.000 linhas e são interrompidas após 10 segundos. Uma consulta pode ter até 20.000 caracteres.

Para exibir o resultado em um gráfico, retorne uma coluna de tempo chamada t, uma coluna numérica chamada v e, opcionalmente, uma coluna group_key para ter uma série por valor. Qualquer outro formato é exibido como tabela. A tabela na página mostra as primeiras 100 linhas.

Consultas salvas

Digite um nome em Save as… e selecione Save query para guardar as configurações atuais do editor ou o SQL. As consultas salvas aparecem como botões acima dos resultados para todos no workspace. Selecione uma para carregá-la, ou selecione o × dela para excluí-la.

Tabelas

As tabelas _raw são guardadas por até 365 dias. As tabelas _daily contêm totais diários e seguem a retenção de análises do seu plano.

emails_raw

Uma linha por versão de cada e-mail. Cada mudança de status adiciona uma nova versão, então um e-mail aparece várias vezes. Agrupe por oid e use argMax(column, _peerdb_version) para ler o valor mais recente, como nos exemplos abaixo.

deliveries_raw

Uma linha por registro de entrega: cada tentativa e registros como retido ou suprimido.

loads_raw e clicks_raw

Uma linha por abertura ou clique.

events_raw

Uma linha por evento: oid (evt_…), type, data (o payload como string JSON; leia-o com funções como JSONExtractString(data, 'object', 'id')) e created_at.

Tabelas diárias

Some cnt com sum(cnt). As outras colunas são estados de agregação do ClickHouse: leia-as com uniqMerge(unique_emails), uniqMerge(unique_links), avgMerge(avg_time) e topKMerge(10)(countries).

Tabelas de dimensão

Faça join com elas para mostrar nomes em vez de IDs.

Consultas de exemplo

Taxa de bounce por domínio de envio, por dia

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

Escolha o gráfico Timeseries para ter uma linha por domínio.

Links mais clicados

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

As tabelas de análises armazenam o ID do link, não a URL. Para ver a URL de um link, abra um e-mail que tenha o clique e confira a aba Clicks dele, ou colete link.url dos webhooks email.clicked.

Percentis do tempo 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

Envios e aberturas por campanha

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

Erros

Erros de sintaxe do ClickHouse, por exemplo causados por um alias depois do nome de uma tabela, são exibidos como o ClickHouse os retorna.

Veja também