Queryable SQL

The Queryable tab of Email API›Analytics lets you ask your own questions of your sending data, either with a point-and-click builder or with read-only ClickHouse SQL. It’s available on Pay as you goProBusinessCustom. Results show as a chart and a table, and you can save queries for your team.

Builder

The builder writes the query for you and uses the page’s date range, Sending domain and API key filters.

Not every combination applies. Group by country works with loads and clicks, which have a country; status, domain, credential, campaign and tag work with the email measures (sends, bounces, failed, rejected, suppressed, unique emails and the two rates). Complaints and average delivery time aren’t grouped. Groups show IDs such as dom_… and key_…; use raw SQL to join names. Grouping by tag usually returns a single (none) group.

Select Run to see the result.

Raw SQL

Turn on Raw SQL to write ClickHouse SQL in the editor, then select Run.

• Queries must start with SELECT or WITH and can only read the tables listed below. Writes, table functions such as url() or s3(), and system tables are blocked.
• Every table is automatically limited to your workspace. You don’t need a workspace_oid condition.
• The page’s date range and filters don’t apply to raw SQL. Add your own time condition, such as created_at >= now() - INTERVAL 30 DAY, or queries scan all retained data.
• Each FROM and JOIN must name a table directly. Subqueries in parentheses are fine, but don’t put an alias after a table name (FROM emails_raw e) and don’t select from a named WITH query. Qualify columns with the table name instead, for example sending_domains_dim.name.
• Queries return at most 5,000 rows and stop after 10 seconds. A query can be up to 20,000 characters.

To chart the result, return a time column named t, a numeric column named v, and optionally a group_key column for one series per value. Any other shape shows as a table. The table on the page shows the first 100 rows.

Saved queries

Type a name in Save as… and select Save query to store the current builder settings or SQL. Saved queries appear as buttons above the results for everyone in the workspace. Select one to load it, or select its × to delete it.

Tables

The _raw tables are kept for up to 365 days. The _daily tables hold daily totals and follow your plan’s analytics retention.

emails_raw

One row per version of each email. Every status change adds a new version, so an email appears several times. Group by oid and use argMax(column, _peerdb_version) to read the latest value, as in the examples below.

deliveries_raw

One row per delivery record: each attempt, and records such as held or suppressed.

loads_raw and clicks_raw

One row per open or click.

events_raw

One row per event: oid (evt_…), type, data (the payload as a JSON string; read it with functions such as JSONExtractString(data, 'object', 'id')) and created_at.

Daily tables

Sum cnt with sum(cnt). The other columns are ClickHouse aggregate states: read them with uniqMerge(unique_emails), uniqMerge(unique_links), avgMerge(avg_time) and topKMerge(10)(countries).

Dimension tables

Join them to show names instead of IDs.

Example queries

Bounce rate by sending domain per day

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

Choose the Timeseries chart to get one line per domain.

Top clicked 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

The analytics tables store the link ID, not the URL. To see a link’s URL, open an email that has the click and check its Clicks tab, or collect link.url from email.clicked webhooks.

Delivery time percentiles

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

Sends and opens per campaign

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

Errors

ClickHouse syntax errors, for example from an alias after a table name, are shown as returned by ClickHouse.

Related