Dotazovatelná analytika (SQL)

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 Pay as you goProBusinessCustom. 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.

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.

deliveries_raw

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

loads_raw a clicks_raw

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

events_raw

Jeden řádek pro každou událost: 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

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

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

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

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ů email.clicked.

Percentily doby doručení

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ě

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

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

Související