# Queryable SQL

> Query your sending data with the Queryable builder or raw ClickHouse SQL, learn the available tables and limits, and start from example queries.

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 Business, Custom. 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.

| Control | Options |
| --- | --- |
| **Measure** | `sends`, `bounces`, `complaints`, `loads`, `clicks`, `unique loads`, `unique clicks`, `unique emails`, `failed`, `rejected`, `suppressed`, `avg delivery time`, `bounce rate`, `complaint rate` |
| **Time grain** | `hour`, `day`, `week` |
| **Group by** | `none`, `status`, `domain`, `credential` (API key), `campaign`, `tag`, `country` |
| **Chart type** | **Timeseries**, **Pie**, **Stat**, **Table** |

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.

| Column | Description |
| --- | --- |
| `oid` | Email ID (`em_…`). |
| `type` | `outgoing` for sent mail, `inbound` for received mail. |
| `status` | The email status, for example `delivered` or `bounced`. |
| `rcpt_to`, `mail_from`, `subject`, `message_id` | Envelope and subject. |
| `sending_domain_oid`, `credential_oid` | Sending domain ID (`dom_…`) and API key ID (`key_…`). |
| `campaign_oid`, `automation_oid` | Set for campaign and automation sends. |
| `tag` | Usually empty. |
| `spam_score`, `inspected` | Rspamd score, and whether the email was scored. |
| `size` | Message size in bytes. |
| `loaded`, `clicked` | Time of the first open and first click, or `NULL`. |
| `created_at`, `updated_at` | Timestamps in UTC. |
| `_peerdb_version` | Version number; higher is newer. |

### deliveries_raw

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

| Column | Description |
| --- | --- |
| `oid`, `email_oid` | Delivery ID and the email it belongs to. |
| `status` | For example `delivered`, `attempted`, `bounced`, `held`, `suppressed`. |
| `output` | The receiving server's reply, for example `250 2.0.0 OK`. |
| `details` | Emailit's description of the result. |
| `time` | How long the attempt took, the value behind **Time to inbox**. |
| `sent_with_ssl` | Whether the connection used TLS. |
| `timestamp`, `created_at` | When the attempt happened. |

### loads_raw and clicks_raw

One row per open or click.

| Column | Description |
| --- | --- |
| `oid`, `email_oid` | Load or click ID and the email it belongs to. |
| `link_oid` | `clicks_raw` only: the clicked link's ID (`link_…`). Link URLs aren't stored in the analytics tables. |
| `ip_address`, `country`, `city`, `user_agent` | Where the open or click came from. |
| `timestamp`, `created_at` | When it happened. |

### events_raw

One row per [event](/docs/logs/events/): `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

| Table | Columns |
| --- | --- |
| `events_daily` | `day`, `type`, `cnt`, `unique_emails` |
| `deliveries_daily` | `day`, `status`, `cnt`, `avg_time` |
| `loads_daily` | `day`, `cnt`, `unique_emails`, `countries` |
| `clicks_daily` | `day`, `cnt`, `unique_emails`, `unique_links` |

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

| Table | Columns |
| --- | --- |
| `sending_domains_dim` | `oid`, `name`, `created_at` |
| `campaigns_dim` | `oid`, `name`, `status`, `created_at` |
| `workspaces_dim` | `oid`, `name`, `plan_id`, `created_at` |

Join them to show names instead of IDs.

## Example queries

### Bounce rate by sending domain per day

```sql
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

```sql
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](/docs/webhooks/event-types/).

### Delivery time percentiles

```sql
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

```sql
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

| Message | Cause |
| --- | --- |
| Only SELECT queries are allowed | The query doesn't start with `SELECT` or `WITH`. |
| Table "…" is not allowed | A `FROM` or `JOIN` names something that isn't an allowed table, such as a named `WITH` query. |
| Query must read an allowlisted analytics table | The query has no `FROM` an allowed table. |
| External tables and table functions are not allowed | The query uses a table function such as `url()`. |
| Queries cannot target another workspace | A `workspace_oid = '…'` condition names a different workspace. |

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

## Related

  - [Advanced analytics](/docs/analytics/advanced/)
  - [Events](/docs/logs/events/)

---
Source: https://emailit.com/docs/analytics/queryable/