Data model
Every table is scoped by project_id. Client-generated ids (sessions, pageviews, events, video
plays) have composite keys (project_id, id), so a forged id can never touch another project.
The read-only SQL endpoint (POST /v1/projects/{project}/sql, MCP sql_query) sees exactly these
tables, filtered to the project by row-level security. GET /v1/projects/{project}/schema (MCP
schema_get) returns the live column list.
sessions
One row per session. Inputs come from the ingest; every aggregate is maintained by a single SQL recompute function, never by ad-hoc code.
| Columns | Meaning |
|---|---|
id, started_at, last_seen_at, duration_ms | identity and time (DB clock) |
visitor_hash, stitch | daily visitor hash (null for personal sessions) and how the session was linked |
entry_host, entry_page, entry_group, exit_page, exit_group | first and last canonical page and page group |
ref_host, channel, attr_from | referrer host, derived channel, attribution origin |
utm_source … utm_id, gclid, gbraid, wbraid, fbclid, li_fat_id, ttclid, msclkid | the 13 attribution keys of the entry pageview |
variant, device, browser, os, country, screen, lang | entry variant and coarse device data |
pageviews, engaged_ms, clicks, int_clicks, max_scroll_pct, avg_scroll_pct, fold_scrolled | engagement |
video_starts, video_watched_s, video_max_pct, video_cov_pct, video_completed | video aggregates (best single play) |
quiz_viewed, quiz_answers, quiz_contact, quiz_submitted, cta_click, whatsapp, calculator, form_submit, booked | built-in flags |
event_counts | {event name: count} for non-click events, at most 50 keys |
goals, goal_value, converted, bounced | goals reached, their value, conversion and bounce flags |
identified, confirmed_conversion | a lead / a non-held conversion is linked |
relevance, relevance_v | score and the model version it was computed with |
is_test, consent, personal | test traffic, consent at send time, personal page |
bounced = one pageview, under 10 s engaged, no interactive click, not converted, no WhatsApp click.
pageviews
One row per pageview: session_id, started_at, seq, nav (load, reload, back_forward,
spa, prerender, resume), host, page, page_group, variant, ref_host, viewport
(vw, vh – the fold), page_h, max_scroll_y, max_scroll_pct, scroll_marks (milestone times), sections
({name: [first visible ms, visible ms]}), engaged_ms, clicks, int_clicks, personal.
Scroll and fold statistics exclude reload, back_forward and resume, where the browser restores
the scroll position.
events
(pageview_id, n) per event with session_id, at (absolute time), t_ms (since pageview start),
name, props (jsonb), source (web or server). Clicks are events named click with kind,
tag, label, href, section, y. Server-side events use n ≥ 1000.
video_views
One row per play: video, src, format (landscape, portrait, unknown), duration_s,
watched_s, max_pct, buckets (100-bit coverage), ranges (watched ranges), completed
(ended and ≥ 90 % covered).
leads and conversions
leads: source (identify,api,import), form, session, hashed identifiers, plaintext only where the project allows it, traits, questionnaire answers, score/grade/summary, spam flag, attribution copy. Not readable throughsql_query(personal data) – useGET …/leadswithleads:read.conversions: definition, source, origin, event id, session, attribution copy, value, status per platform (uploads), attempts.sql_querysees it without hashes and transient request context.
Ad tables
ad_accounts, ad_entities (campaigns, ad sets, ads with names and URL hints), ad_metrics_daily
(spend, impressions, clicks, platform conversions per day and level, converted to the project
currency on read), ad_clicks (Google click view), session_ad_match (session → platform, account,
campaign, ad set, ad, match method).
Daily rollups
session_daily_stats aggregates sessions per local day and dimension group (channel, UTMs, device,
country, entry group) with histograms for exact medians. Overview and time series use it for ranges
longer than 90 days when the filter allows it; responses then say source.kind = "rollup" and list
approximate fields (visitors, duration medians).
Conventions
- Times are
timestamptz; days in reports are local days in the project time zone. - Rates in API responses are fractions 0–1; durations are seconds; scores 0–100.
- Test traffic is excluded unless
include_test=true.