Analytics report queries
What each dashboard report measures, which API it calls, and where the query lives in the repo. Written for the Builder team to verify numbers without reading the whole analytics stack.
website_event table (pageviews and custom events collected by the tracker via /api/collect). Filters like URL, device, or country are appended to the WHERE clause at request time.Queries used by the page builder
Dashboard widgets bind to a queryName. These eight names are registered in src/lib/dashboards/default-data-sources.ts.
| queryName | What it measures | API | Query source |
|---|---|---|---|
stats | Totals: pageviews, unique visitors, visits, bounces, total time. Optional period compare. | GET /api/analytics/websites/{id}/stats | getWebsiteStats.ts |
pageViews | Pageview count over time (chart series). | GET .../pageviews | getPageviewStats.ts |
sessions | Session count over time (same endpoint, sessions axis). | GET .../pageviews | getPageviewStats.ts |
topPages | Top URLs by view count. | GET .../metrics?type=url | getPageviewMetrics.ts |
referrers | Top referrers by visit count. | GET .../metrics?type=referrer | getPageviewMetrics.ts |
deviceBreakdown | Views grouped by device (desktop, mobile, tablet). | GET .../metrics?type=device | getPageviewMetrics.ts |
funnel | Ordered step conversion: how many sessions hit step 1, then 2, then 3 within a time window. | POST /api/analytics/reports/funnel | getFunnel.ts |
retention | Day-0 cohort size and return rate on day 1, 2, 3, etc. | POST /api/analytics/reports/retention | getRetention.ts |
Other console reports
The analytics UI exposes additional reports under /api/analytics/reports/*. Same pattern: one route, one query file.
| Report | What it measures | API | Query source |
|---|---|---|---|
| Insights | Breakdown of views, visitors, visits, bounces, and time by a chosen dimension (URL, country, browser, etc.). | POST /api/analytics/reports/insights | getInsights.ts |
| Goals | How often named goal events fired, optionally broken down. | POST /api/analytics/reports/goals | getGoals.ts |
| Journey | Common page-to-page paths (sankey-style flow). | POST /api/analytics/reports/journey | getJourney.ts |
| UTM | Traffic and conversions grouped by utm_source, utm_medium, utm_campaign. | POST /api/analytics/reports/utm | getUTM.ts |
| Revenue | Revenue attributed to events or user properties over a date range. | POST /api/analytics/reports/revenue | getRevenue.ts |
| Realtime | Active visitors and recent pageviews in the last ~30 minutes. | GET /api/analytics/realtime/{websiteId} | getRealtimeData.ts |
What the SQL does (short version)
Stats and time series: filter website_event by website_id and date range, group by session or visit, count rows and distinct sessions.
Top pages / referrers / device: same table, GROUP BY the dimension column (url_path, referrer, device), ORDER BY count DESC.
Funnel: for each step (URL or event), count sessions that hit step N after step N-1 within window minutes. Step list comes from the POST body.
Retention: assign each session to its first-visit day (cohort), then count how many of those sessions appear again on later days.
How to verify a number
- Note the widget
queryNameand params (websiteId, date range, filters). - Call the API row from the table above with the same params.
- Open the query source file under
src/analytics/queries/analytics/to see the exact ClickHouse SQL template. - Funnel and insights build SQL from request params, so compare against a concrete example request, not a single static query string.
Full endpoint reference: Analytics Console API.