# Dotazovatelná analytika (SQL)

> Dotazujte se na data o odesílání pomocí nástroje Queryable nebo surového ClickHouse SQL, seznamte se s dostupnými tabulkami a limity a začněte od ukázkových dotazů.

Karta **Queryable** v **Email API → Analytics** vám umožňuje klást vlastní otázky nad daty o odesílání, buď v nástroji pro tvorbu dotazů ovládaném myší, nebo v ClickHouse SQL jen pro čtení. Je dostupná v tarifech Business, Custom. Výsledky se zobrazí jako graf a tabulka a dotazy si můžete uložit pro celý tým.

## Tvorba dotazů

Nástroj pro tvorbu dotazů za vás dotaz napíše a použije časový rozsah stránky a filtry **Sending domain** a **API key**.

| Ovládací prvek | Možnosti |
| --- | --- |
| **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 klíč), `campaign`, `tag`, `country` |
| **Chart type** | **Timeseries**, **Pie**, **Stat**, **Table** |

Ne každá kombinace dává smysl. **Group by** `country` funguje s načteními a prokliky, které mají zemi; `status`, `domain`, `credential`, `campaign` a `tag` fungují s metrikami e-mailů (sends, bounces, failed, rejected, suppressed, unique emails a obě míry). Stížnosti a průměrná doba doručení se neseskupují. Skupiny zobrazují ID, například `dom_…` a `key_…`; pro připojení názvů použijte surové SQL. Seskupení podle `tag` obvykle vrátí jedinou skupinu `(none)`.

Výsledek zobrazíte tlačítkem **Run**.

## Surové SQL

Zapněte **Raw SQL**, napište do editoru ClickHouse SQL a vyberte **Run**.

- Dotazy musí začínat na `SELECT` nebo `WITH` a mohou číst jen tabulky uvedené níže. Zápisy, tabulkové funkce jako `url()` nebo `s3()` a tabulky `system` jsou zablokované.
- Každá tabulka je automaticky omezená na váš workspace. Podmínku na `workspace_oid` nepotřebujete.
- **Časový rozsah a filtry stránky se na surové SQL nevztahují.** Přidejte vlastní časovou podmínku, například `created_at >= now() - INTERVAL 30 DAY`, jinak dotazy projdou všechna uchovávaná data.
- Každé `FROM` a `JOIN` musí tabulku pojmenovat přímo. Poddotazy v závorkách jsou v pořádku, ale za název tabulky nepište alias (`FROM emails_raw e`) a nevybírejte z pojmenovaného dotazu `WITH`. Místo toho sloupce kvalifikujte názvem tabulky, například `sending_domains_dim.name`.
- Dotazy vrátí nejvýše 5 000 řádků a zastaví se po 10 sekundách. Dotaz může mít až 20 000 znaků.

Pokud chcete výsledek zobrazit v grafu, vraťte časový sloupec s názvem `t`, číselný sloupec s názvem `v` a volitelně sloupec `group_key`, podle jehož hodnot vznikne samostatná řada. Výsledek v jakémkoli jiném tvaru se zobrazí jako tabulka. Tabulka na stránce ukazuje prvních 100 řádků.

## Uložené dotazy

Do pole **Save as…** zadejte název a vyberte **Save query**. Uloží se tím aktuální nastavení nástroje pro tvorbu dotazů, nebo SQL. Uložené dotazy se všem ve workspace zobrazují jako tlačítka nad výsledky. Kliknutím dotaz načtete, kliknutím na jeho **×** ho smažete.

## Tabulky

Tabulky `_raw` se uchovávají až 365 dní. Tabulky `_daily` obsahují denní souhrny a řídí se dobou uchovávání analytiky ve vašem tarifu.

### emails_raw

Jeden řádek pro každou verzi každého e-mailu. Každá změna stavu přidá novou verzi, takže se jeden e-mail objeví několikrát. Seskupte podle `oid` a nejnovější hodnotu čtěte pomocí `argMax(column, _peerdb_version)`, jako v příkladech níže.

| Sloupec | Popis |
| --- | --- |
| `oid` | ID e-mailu (`em_…`). |
| `type` | `outgoing` pro odeslanou poštu, `inbound` pro přijatou. |
| `status` | Stav e-mailu, například `delivered` nebo `bounced`. |
| `rcpt_to`, `mail_from`, `subject`, `message_id` | Obálka a předmět. |
| `sending_domain_oid`, `credential_oid` | ID odesílací domény (`dom_…`) a ID API klíče (`key_…`). |
| `campaign_oid`, `automation_oid` | Vyplněné u e-mailů z kampaní a automatizací. |
| `tag` | Obvykle prázdné. |
| `spam_score`, `inspected` | Skóre z Rspamd a údaj, jestli se e-mail hodnotil. |
| `size` | Velikost zprávy v bajtech. |
| `loaded`, `clicked` | Čas prvního otevření a prvního prokliku, nebo `NULL`. |
| `created_at`, `updated_at` | Časová razítka v UTC. |
| `_peerdb_version` | Číslo verze; vyšší je novější. |

### deliveries_raw

Jeden řádek pro každý záznam o doručení: každý pokus a záznamy jako zadržení nebo zablokování.

| Sloupec | Popis |
| --- | --- |
| `oid`, `email_oid` | ID doručení a e-mail, ke kterému patří. |
| `status` | Například `delivered`, `attempted`, `bounced`, `held`, `suppressed`. |
| `output` | Odpověď přijímajícího serveru, například `250 2.0.0 OK`. |
| `details` | Popis výsledku od Emailitu. |
| `time` | Jak dlouho pokus trval; z této hodnoty vychází graf **Time to inbox**. |
| `sent_with_ssl` | Jestli spojení používalo TLS. |
| `timestamp`, `created_at` | Kdy pokus proběhl. |

### loads_raw a clicks_raw

Jeden řádek pro každé otevření nebo proklik.

| Sloupec | Popis |
| --- | --- |
| `oid`, `email_oid` | ID načtení nebo prokliku a e-mail, ke kterému patří. |
| `link_oid` | Jen `clicks_raw`: ID prokliknutého odkazu (`link_…`). URL odkazů se v tabulkách analytiky neukládají. |
| `ip_address`, `country`, `city`, `user_agent` | Odkud otevření nebo proklik přišel. |
| `timestamp`, `created_at` | Kdy nastal. |

### events_raw

Jeden řádek pro každou [událost](/cs/docs/logs/events/): `oid` (`evt_…`), `type`, `data` (obsah události jako řetězec JSON; čtěte ho funkcemi jako `JSONExtractString(data, 'object', 'id')`) a `created_at`.

### Denní tabulky

| Tabulka | Sloupce |
| --- | --- |
| `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` |

Sloupec `cnt` sčítejte pomocí `sum(cnt)`. Ostatní sloupce jsou agregační stavy ClickHouse: čtěte je pomocí `uniqMerge(unique_emails)`, `uniqMerge(unique_links)`, `avgMerge(avg_time)` a `topKMerge(10)(countries)`.

### Dimenzní tabulky

| Tabulka | Sloupce |
| --- | --- |
| `sending_domains_dim` | `oid`, `name`, `created_at` |
| `campaigns_dim` | `oid`, `name`, `status`, `created_at` |
| `workspaces_dim` | `oid`, `name`, `plan_id`, `created_at` |

Připojte je, pokud chcete místo ID zobrazit názvy.

## Ukázkové dotazy

### Míra nedoručení podle odesílací domény po dnech

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

Zvolte graf **Timeseries** a pro každou doménu dostanete jednu křivku.

### Nejčastěji prokliknuté odkazy

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

Tabulky analytiky ukládají ID odkazu, ne jeho URL. URL odkazu zjistíte tak, že otevřete e-mail s prokliknutím a podíváte se na jeho kartu **Clicks**, nebo že budete sbírat `link.url` z [webhooků](/cs/docs/webhooks/event-types/) `email.clicked`.

### Percentily doby doručení

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

### Odeslání a otevření podle kampaně

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

## Chyby

| Zpráva | Příčina |
| --- | --- |
| „Only SELECT queries are allowed“ | Dotaz nezačíná na `SELECT` ani `WITH`. |
| „Table "…" is not allowed“ | `FROM` nebo `JOIN` uvádí něco, co není povolená tabulka, například pojmenovaný dotaz `WITH`. |
| „Query must read an allowlisted analytics table“ | Dotaz neobsahuje `FROM` s povolenou tabulkou. |
| „External tables and table functions are not allowed“ | Dotaz používá tabulkovou funkci, například `url()`. |
| „Queries cannot target another workspace“ | Podmínka `workspace_oid = '…'` uvádí jiný workspace. |

Syntaktické chyby ClickHouse, například kvůli aliasu za názvem tabulky, se zobrazí tak, jak je ClickHouse vrátí.

## Související

  - [Pokročilá analytika](/cs/docs/analytics/advanced/)
  - [Události](/cs/docs/logs/events/)

---
Zdroj: https://emailit.com/cs/docs/analytics/queryable/