Analytics report queries
What each analytics report measures and which API endpoint returns it, so you can pull the same numbers the dashboard shows and verify them yourself.
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 names are registered in src/lib/dashboards/default-data-sources.ts and each maps to an analytics API call.
| 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 |
conversions | Conversion rate for the period, with previous-period comparison. | GET .../basics/conversionrate | default-data-sources.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.