Skip to content
$_ setuptracking

SQL Recipes — Querying Lab

Pillar: querying your own analytics warehouse — the SQL recipes for Plausible CE’s ClickHouse, Matomo’s MariaDB, PostHog’s ClickHouse, and Umami’s Postgres. The reports your dashboard doesn’t ship with, written as runnable SQL you can paste into the database directly.

Self-hosting analytics means you own the database. The interesting reports — cohort retention by traffic source, custom funnel drop-off including in-app touches, revenue-per-session by content cluster — usually don’t appear in the dashboard UI. They live in SQL, and the SQL is approachable once you’ve seen it laid out.

Where this pillar is right now

The first three recipes are in writing. Each will follow the same shape: schema overview (the tables and what columns to actually care about), one concrete reporting question, the SQL that answers it, and the “now generalize this” note at the end. Voice: assume you can read SQL but haven’t reverse-engineered Plausible’s events_v2 table yet.

Recipes shipping next

  • Plausible CE — content cluster retention by traffic source. The single most-asked-for report I’ve seen on the Plausible community forum. Joins events_v2 to itself with a 30-day window, groups by utm_source + content category. Runs against ClickHouse on a CX22 in <200 ms.
  • Matomo — funnel drop-off including off-site touches. Reaches into matomo_log_link_visit_action + matomo_log_action. Replaces the paid Funnels plugin’s basic case for free.
  • PostHog — feature-flag cohort divergence. ClickHouse SQL that compares two flag-experiment cohorts on a metric you didn’t plan for upfront, retroactively.
  • Umami — pageviews-vs-events bridging. The Postgres pattern for treating Umami’s events table as a session-augmenting layer instead of as a separate stream.
  • Cross-tool — building a daily funnel CSV from any of the above. Cron + SQL + a single CSV file you can email weekly. Useful when the dashboard isn’t where stakeholders are.
  • Install recipes — you need a working install before any SQL recipe runs. Start there.
  • Custom events & goals — the SQL recipes assume you’ve modelled events. Pillar 4 covers the modelling discipline.
  • Cookieless tracking — when identifiers rotate daily, multi-day cohort SQL gets interesting. Some of the next-up recipes show the pattern.