Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

37.7 KB
Raw Blame

Web analytics query serving: the strategy ladder

How a web analytics query gets served: every strategy in the current stack, the order they're tried in, and the exact conditions that route a request down each path. Source of truth for the code: products/web_analytics/backend/hogql_queries/. For deep precompute internals (schemas, bucketing, insert variants), see products/web_analytics/PRECOMPUTATION.md.

Every strategy tags its ClickHouse queries with a query_type that lands in system.query_log → log_comment.query_type, so the tag reference at the bottom doubles as a triage tool.

The serving tiers

Cheapest first. A request walks down this ladder and stops at the first tier whose conditions it satisfies.

Tier What it is Typical latency
0. Result cache Django/HogQL cached response for the exact query fingerprint ~ms
1. Lazy precompute Hourly UTC buckets (vitals: team-tz daily) on the aux cluster, built by background daily-UTC jobs, TTL-refreshed ~80–200ms
2. Preaggregated tables (deprecated) Daily preagg tables, modifier-gated; no new enrollments — retained only for the largest existing customers until lazy precompute fully replaces it ~100ms–1s
3. Live fast paths Session-id-set (filtered) and no-join (unfiltered) shapes that avoid the full events↔sessions join ~1–20s
4. Full join The original events↔sessions join; always works, slowest ~5–60s

The preaggregated all-time range includes the hourly bucket containing the first event. Explicit date ranges keep their requested lower bounds.

Request flow

request
  │
  ▼
[0] Django result cache ──hit──▶ return cached (~ms)
  │ miss/stale
  ▼
[1] Lazy precompute gate
  │   enrollment: org flag `web-analytics-precompute-toggle` (or env allowlist);
  │     per-query toggle defaults to opt-out for enrolled teams (#72645) —
  │     only an explicit useWebAnalyticsPrecompute: false rejects
  │   shape: family dispatch (see per-runner tables), no conversion goal,
  │     no sampling, integer timezone, `sessionsV2JoinMode` ≠ uuid,
  │     range ≤ MAX_PRECOMPUTE_DAYS, filters events-evaluable
  │     (restricted teams: single exact $host only)
  │   freshness: all day-buckets fresh per TTL band ──▶ serve *_lazy_query (~80–200ms)
  │     expired within the SWR grace (default 4h) ──▶ serve stale + enqueue revalidation
  │ miss (NEVER builds inline — enqueues debounced background warm)
  ▼
[2] Preaggregated tables (deprecated)
  │   useWebAnalyticsPreAggregatedTables modifier + supported shape
  │ not enrolled / unsupported
  ▼
[3] Session-id-set fast path (filtered queries)
  │   team on WEB_ANALYTICS_SESSION_ID_SET_TEAM_IDS or the
  │     `web-analytics-session-id-set` rollout flag, filters events-evaluable;
  │   preflight selectivity query first (*_session_id_set_preflight)
  │ unfiltered / not allowlisted / preflight fails
  ▼
[4] No-join fast path (unfiltered queries)
  │   no property filters, no conversion goal, no session-table fields;
  │   WEB_ANALYTICS_NO_JOIN_TEAM_IDS + rollout % (100% on Cloud)
  │   (stats table: any simple or first-pageview breakdown without bounce
  │     rate or session fields, filters and conversion goals included)
  │ needs session fields with filters
  ▼
[5] Full events↔sessions join (unconditional fallback)

Conversion goals on the overview run ahead of this ladder as two independent reads: the goal-less overview (which climbs the ladder above for visitors) plus a scan of goal events only (web_overview_conversion_goal_query). The joined shape grouped every pageview session in range to count the goal, so its memory scaled with traffic; the split scales with conversions. Visitors therefore equal the goal-less visitors card, and a session with a goal event but no pageview or screenview no longer counts as a visitor. Legacy sessions v1 and queries with session or cohort filters keep the joined shape.

The one-way rule (#72959): user-facing reads never build precompute buckets inline — run_inserts is true only for background-warming requests. A miss costs one live-path serve; the background warm makes the next identical request a bucket hit. The dashboard "enqueues precompute" as a side effect; it never waits on it.

Requests that select native JSON events keep that table choice in precompute job identities and queued revalidation payloads. Native and legacy revalidations use separate debounce keys, so a trial cannot suppress the project's normal rebuild. Builders inherit the running query's table choice; an explicit query modifier takes precedence over the staff cookie.

Marketing search performance

Search metrics and PostHog landing-page conversions load in separate requests. The table shows search metrics while conversion columns load, and a conversion error leaves those metrics visible with its own query ID and retry action. Both requests use the same normalized landing URLs and filters. The conversion request still uses the search query runner, so it repeats the source aggregation before calculating attribution.

MarketingAnalyticsSearchQuery reads synced ad-platform tables through HogQL and the query result cache, independently of the web-event serving tiers above. Google Ads requires the keyword and keyword_stats tables for keywords, and landing_page_stats for landing pages; Bing Ads supports keywords through keyword_performance_report. Google Search Console uses search_analytics_by_query or search_analytics_by_page for aggregate views, with search_analytics_by_query_page as a fallback and for exact query-to-page and page-to-query details. The query only selects one GSC table per source, so syncing both aggregate and detailed tables does not multiply metrics. The integration and channel filters keep paid and organic rows separate. GSC does not report spend or conversions; these values remain null. Organic position is weighted by impressions in each period, while CTR uses summed clicks divided by summed impressions. Traffic shows position instead of cost for organic-only selections; mixed selections can add position with the Show position checkbox. Comparison colors show increases in impressions as positive and increases in cost, CPC, CPA or position as negative. Query and page breakdowns can omit low-volume queries and differ from property totals. The Search performance section sits below the campaign table in Ad performance, behind marketing-analytics-organic-keywords in both dashboards. It shares the integration, date and comparison filters with the campaign table. The integration filter and Add source menu list Google Search Console separately under Organic search. Empty filtered results offer Clear filters; unfiltered views suggest connecting missing Google Ads or Google Search Console sources. Connected sources show separate setup messages for disabled tables, tables awaiting a first successful sync, failed or paused syncs, and stale data. Search performance excludes a table when its last successful sync is older than twice its configured sync interval. The source list includes each table's sync frequency so this check uses the table's schedule. An unrecognized sync interval is returned as null, which keeps the source list available and skips the cadence check for that table. GSC prefers the dedicated query or page table, then falls back to a ready query-and-page table with a notice that totals can differ. If neither table is ready, the source is excluded instead of displaying zero traffic and misleading period comparisons. Query and page details use the same readiness checks. New Google Ads connections preselect keyword, keyword_stats, and landing_page_stats; Bing Ads preselects keyword_performance_report. New GSC connections preselect the web query, page, and query-and-page tables; non-web search types remain opt-in. These defaults do not change the saved table selection of an existing source. Source discovery loads every page of connected integrations before applying the filter. The date and comparison controls select the current and comparison periods. Organic query and page details retain the selected integration sources. Paid keyword and page details use the available GSC sources to find organic results for the same text or URL. Cached results are partitioned by warehouse table, view, and source permissions. Metrics group targeted keywords by platform, match type and account currency; spend is never added across currencies. Conversions retain the ad platform's attribution, while CTR, CPC and CPA use the summed metrics in each period. Comparison includes keywords present in either period and applies the top-100 limit after matching the periods. The query type tag is marketing_analytics_search_query.

Source connection links use returnLabel=Marketing analytics, including search setup suggestions and details. The warehouse source connect completed event records this label after the creation API succeeds, so connections started here can be attributed to Marketing analytics.

Per-runner dispatch

WebOverviewQuery (web_overview.py)

# Strategy Conditions Tag
0 Conversion-goal split Conversion goal + sessions v2/v3 + events-evaluable filters; visitors from the goal-less dispatch below, conversions from goal events web_overview_conversion_goal_query (+ visitors' tag)
0b Dimensional webQueryModifiers.useDimensionalTables + tier-2 shape + no property access rules + READY dimensional jobs cover the range web_overview_dimensional_query
1 Lazy precompute Shared gate only — overview has no extra shape restrictions web_overview_lazy_query
2 Preaggregated (deprecated) Modifier on + no conversion goal web_overview_preaggregated_query
3 Session-id-set Filtered + allowlisted + preflight passes (sets sessionIdPushdown) web_overview_session_id_set_query
4 No-join Unfiltered, no conversion goal web_overview_no_join_query
5 Full join Fallback (conversion goals with legacy sessions v1, session filters, or cohort filters land here) web_overview_query

WebStatsTableQuery (stats_table.py) — three lazy families, tried in order

# Strategy Conditions Tag
0 Dimensional webQueryModifiers.useDimensionalTables + tier-4 shape + no property access rules + READY dimensional jobs cover the range stats_table_dimensional*
1 Paths lazy breakdownBy ∈ {Page, InitialPage} + includeBounceRate; rejects avg-time-on-page, scroll depth, unsupported orderBy; top-K 10,000 paths/day cap for high-cardinality teams web_stats_paths_lazy_query
2 Frustration lazy breakdownBy = FrustrationMetrics; rejects unsupported orderBy web_stats_frustration_lazy_query
3 Simple-breakdown lazy ~18 supported breakdowns (DeviceType, Browser, OS, Country, Region, City, Viewport, Timezone, Language, ExitPage, InitialChannelType, InitialReferringDomain/URL, InitialUTM_*); rejects bounce rate, avg time, scroll depth web_stats_lazy_query
4 Preaggregated (deprecated) Modifier on + no avg-time-on-page + no conversion goal stats_table_preaggregated*
5 Session-id-set Page breakdown ± avg time, filtered + allowlisted + preflight stats_table_session_id_set_path_bounce[_and_avg_time]
6 No-join Unfiltered path-bounce and path-bounce+avg-time; any simple or first-pageview breakdown without bounce rate or session fields (filters and conversion goals ride the single events scan) stats_table_no_join_*
7 Full join Fallback per shape stats_table_path_bounce, stats_table_entry_bounce, stats_table_channel_type, stats_table_simple_breakdown, …

Both runners skip the dimensional tables when the rows cannot match the live result:

  • The team has property access rules or the query filters out test accounts.
  • The current or compare period does not start on a UTC hour boundary, or ends inside an hour before now.
  • Either period starts at or before the events retention floor.
  • The team reads sessions v3 and either period starts before the region's v3 backfill start (SESSIONS_V3_COVERAGE_START_BY_REGION). The writer does not build those days either, and the coverage check skips them.
  • The team has custom channel rules and the query breaks down or filters by channel type.
  • The request overrides a bounce or session modifier (DIMENSIONAL_SEMANTIC_MODIFIER_FIELDS) with a value the team's builds do not use.

The jobs' identity includes the team's values of those modifiers. A change to the team's bounce or session settings gives new jobs, so reads fall back until the writer rebuilds the window.

The dimensional tables subclass EventsRetentionTable, as events does. The printer floors every read of them at the events retention floor on period_bucket, so a direct HogQL query cannot read rows past retention either. A new table that stores aggregates of events should subclass it too.

Traffic metrics alongside conversion goals

WebStatsTableQuery.includeTrafficMetrics adds sessions and retains pageviews alongside conversion columns. Traffic visitors and sessions require a pageview or screenview; goal-only sessions contribute to conversions without increasing the traffic denominator. The option defaults to off, preserving existing callers.

Queries with this option bypass the simple-breakdown and paths lazy caches and use the supported live or preaggregated execution path. Event and action conversion goals can carry property filters, which restrict conversions rather than traffic.

Goals, vitals, external clicks

Runner Tier 1 Fallback Notes
WebGoalsQuery Lazy (web_goals_lazy_query) Live (web_goals_query) Needs actions configured; no fast-path shapes exist
WebVitalsPathBreakdownQuery Lazy Live (web_vitals_path_breakdown_query) Requires day-aligned range; exempt from integer-timezone gate
WebExternalClicksTableQuery — Live (external_clicks_query) Live-only; no precompute family

Lazy precompute freshness (summary)

Full details in PRECOMPUTATION.md; the operative numbers:

Day age TTL
Today (0d) 4h
Yesterday (1d) 6h
2–7d 5d
8–14d 7d
15–21d 10d
22–35d 12–14d
36d+ 90d
  • Stale-while-revalidate: 4h grace by default, set by WEB_ANALYTICS_PRECOMPUTE_STALE_GRACE_SECONDS (read per call, so a new env value applies once the process restarts; must stay under the framework's 48h ClickHouse expiry buffer, which the executor enforces). User reads inside the grace get the stale row instantly (tagged precompute_stale=true) with a Celery revalidation enqueued (10-min debounce). Background warmers and forced refreshes are never served stale — they are the refresh.
  • Session settling: 24h forward pad on event scans, matching the SDK session length cap.
  • OOM protection: a team that OOMs during a build gets Redis-pinned for 14 days to 1-day insert windows.
  • Max range: MAX_PRECOMPUTE_DAYS; wider requests are permanently live.
  • Framework rationale (freshness, stale grace, settling, OOM pin): see design decisions.

Session-grain precompute schema

web_sessions_dimensional_preaggregated preserves individual sessions and person identity for attribution reads. Its sharded storage table lives on the aux cluster; distributed tables on aux and data nodes point to it. This schema is a prerequisite for the session writer and reader; creating it does not enable either path.

session_id_v7 uses UInt128, matching the raw Sessions v2 and v3 tables and the numeric representation in events.$session_id_uuid. Writers must preserve that representation and only materialize valid UUIDv7 sessions; a null or invalid ID must not become a shared zero-valued ID. Sessions v1 and arbitrary string IDs require the live query path unless a separate compatible precompute path is available. The writer, reader, and HogQL schema must use session_id_v7 consistently before this precompute path is enabled. The table uses TTL toDateTime(expires_at) with whole-part expiry; the lazy computation executor includes the in-flight reader buffer in expires_at.

Background warming systems

Four writers keep buckets warm; user reads only ever consume.

System Trigger tag When What it does
Eager baseline warmer (Dagster, eager_web_analytics_precompute.py) webAnalyticsEagerBaselineWarming Hourly at :53 Pre-warms the fixed dashboard matrix (overview, goals, vitals, one stats query per breakdown) over a trailing 28d window for flag-enrolled teams (cap 200, 45-min cycle budget)
Hourly demand warmer (Dagster, cache_warming.py) webAnalyticsQueryWarming Hourly at :03 Selects hot shapes from query_log (kind Web%, ≥2 hits in 2 days; raw-path shapes keep a ≥10 bar), expands sub-30d ranges to −30d, replays via an 8-worker pool with the opt-in injected
Warm-behind on miss (background warming request) On any user-read miss Debounced rebuild of exactly the shape that missed; self-heals first-hit misses in ~30–60s
Stale revalidation webAnalyticsStaleRevalidation On stale-grace serves Refreshes expired buckets after serving the stale copy

The demand warmer delays each team's first eligible work by a stable offset within ten minutes of its shard starting. Manual runs have no release delay. Worker capacity can delay a team beyond its release time; the offset is not a completion deadline. The eager and demand jobs can still overlap if either pass runs long.

Flags and team allowlists

Gate Type Controls
web-analytics-precompute-toggle Org feature flag Lazy precompute enrollment; enrolled teams read by default (opt-out). Evaluated locally, fails closed
WEB_ANALYTICS_LAZY_PRECOMPUTE_TEAM_IDS Env allowlist Flag-independent precompute enrollment (shared with Dagster warmers, where flag evaluation is unreliable)
WEB_ANALYTICS_LAZY_PRECOMPUTE_UNRESTRICTED_TEAM_IDS Env allowlist Lifts the single-$host filter-shape restriction — any filter combo becomes precomputable (own cache key each)
WEB_ANALYTICS_SESSION_ID_SET_TEAM_IDS Env allowlist Filtered fast path (live tier)
web-analytics-session-id-set Team rollout flag Filtered fast path enrollment without the env allowlist — either gate admits the team
WEB_ANALYTICS_NO_JOIN_TEAM_IDS + rollout % Env + percentage Unfiltered fast path — 100% on Cloud
useWebAnalyticsPrecompute Per-query field User opt-out switch (WebAnalyticsMenu toggle); explicit false always wins
useWebAnalyticsPreAggregatedTables Query modifier Preaggregated-tables tier (deprecated — largest existing customers only, no new enrollments)
webQueryModifiers.useDimensionalTables Per-query field Tries the dimensional tables first; a gap or a failure falls back to the normal dispatch

query_type tag reference

Suffix conventions: *_lazy_query = bucket read (served from precompute), *_lazy_insert = bucket build (background only), *_preflight = selectivity probe.

Family Precompute Live tags
Overview web_overview_lazy_query/insert, web_overview_preaggregated_query, web_overview_dimensional_query web_overview_conversion_goal_query, web_overview_no_join_query, web_overview_session_id_set_query (+_preflight), web_overview_query
Stats table web_stats_paths_lazy_*, web_stats_frustration_lazy_*, web_stats_lazy_*, stats_table_preaggregated*, stats_table_dimensional* stats_table_no_join_* (incl. stats_table_no_join_first_pageview_attribution), stats_table_session_id_set_* (+_preflight), stats_table_path_bounce[_and_avg_time], stats_table_entry_bounce, stats_table_channel_type, stats_table_first_pageview_attribution, stats_table_frustration_metrics, stats_table_simple_breakdown
Goals web_goals_lazy_query/insert web_goals_query
Vitals web_vitals_paths_lazy_query/insert web_vitals_path_breakdown_query
External clicks — external_clicks_query

Reading a slow tile

Find the request in query_log and check query_type. A *_lazy_query taking seconds is a bucket-read problem (rare). A fast-path or full-join tag on an enrolled team means the lazy gate rejected the query (filters, avg-time-on-page, range over MAX_PRECOMPUTE_DAYS, opt-out) or the buckets weren't fresh — in which case a background warm is already in flight and the next identical request should hit.

Conversion goal property filters accept event, person, session and cohort filters. Unsupported filter types fail query validation. includeTrafficMetrics also retains session counts for page breakdowns with bounce rate or average time on page, including the join-free strategies.

Identifying a failed marketing query

Marketing Analytics query errors show a query ID when the request has one. Use that ID to find the failed request in the query log. Search performance uses the same error banner, including in query and landing-page details. The error's query ID takes precedence over the current request ID; a previous successful response is not a source for the error ID. Errors outside the query path, such as configuration failures, may have no query ID.

Campaign breakdown columns

Marketing analytics saves campaign column selection, sorting, and pinned columns in browser storage for each project. A fresh visit restores those preferences without saving query results or draft conversion goals. Explicit column options in a shared URL take precedence over saved preferences, including links to Ad performance. Changing tabs or dashboard filters preserves those column options in the URL. Reset to defaults clears the custom selection, sorting, and pins for later visits.

Conversion recordings

See Marketing analytics conversion recordings for row selection, session attribution, and replay behavior.

Marketing metric chart

The standalone metric chart receives prepared series, ISO date labels, a selected breakdown key, and callbacks. The query-owning caller switches between total and breakdown data and supplies error content, including the failed query ID and retry action. Chart clicks select the nearest line and return the raw breakdown identity so selections can match table rows even when display labels differ. An empty string is a selectable breakdown key; null clears selection. Percentage series contain fractions: a value of 0.42 displays as 42.0% in the axis and tooltip. The chart keeps existing data visible while refreshing and replaces it with the supplied error if the refresh fails. Storybook covers loading, refreshing, empty results, errors, and a 520 px scene. The component does not activate the five-section dashboard or change its queries.

Marketing analytics suggestion

The Sources table can show a dismissible Marketing analytics suggestion behind web-analytics-marketing-cross-sell. The gate precedes the query and connection loaders, so disabled users incur no additional requests. The Channel and all existing UTM table views (source, medium, campaign, content, term, and combined source/medium/campaign) share the dashboard's Channels data node, including its date range, filters, test-account exclusion, comparison and query cache. Channel therefore adds no analytics request; a direct visit to a UTM table may load Channels once through the normal optimized query runner. The suggestion requires a recognized paid channel with visitors in the current period. Organic source names and comparison-only traffic do not qualify. This is positive evidence from the returned top channels, not an exhaustive census: paid traffic below the table limit or under an arbitrary custom channel name may not trigger the suggestion.

Connection metadata loads only after that evidence exists, without mounting the Marketing analytics dashboard or running its report queries. An enabled native ad integration or an existing external source mapping leads to Marketing analytics; otherwise the link opens source setup when that interface is enabled, or the existing dashboard onboarding flow. Sync health remains the destination's responsibility, so a failed integration does not prompt a duplicate connection. These table views share the same copy: Connect ad sources for projects without a connection, or Analyze in Marketing analytics for connected projects. Loading and failed metadata requests leave the suggestion hidden. The destination keeps the date range; Web analytics property filters are not forwarded because Marketing analytics uses a different filter schema. An explicit open end date clears any end date saved during a previous Marketing analytics visit. Dismissal persists per project in the browser.

Paid traffic experiment

web-analytics-marketing-cross-sell-v2 splits teams with paid traffic into control and test. The flag is read only after the Channels response shows paid traffic, so teams without paid traffic are never exposed. control keeps the suggestion above. test changes three surfaces:

Surface Shows when Leads to
Quantified card under the Sources table Paid traffic meets the threshold, no ad source is connected, and the card is not snoozed The wizard for the top detected ad platform, or source setup
"See ad spend" link next to the row label, shown on row hover, in the Channel and UTM source tables A paid channel row, or a UTM source that maps to an enabled ad platform Marketing analytics when connected, otherwise the platform wizard or source setup
Banner above the dashboard A session filter on a paid $channel_type or a mapped $entry_utm_source The same destination as the row action

The threshold is at least 50 paid visitors and a 3% share of the top channel rows. The flag payload can override it with min_visitors and min_share. Platform detection loads the UTM source table node only for teams that see the quantified card. It maps each utm_source through the default and custom source mappings that Marketing analytics uses. The platform wizard finishes with "Return to Marketing analytics", which opens Marketing analytics with the Web analytics date range. "Not now" on the card or the banner snoozes both for 30 days per project. A second dismissal hides them for good.

Cross-sell attribution

web analytics marketing cross sell clicked records cross_sell_id, cross_sell_clicked_at (Unix milliseconds), team_id, the table breakdown, and whether ad sources were already connected. It also records the surface: cross_sell_placement (sources_table, table_row or filter_hint), cross_sell_variant, and the targeted platform. web analytics marketing cross sell shown fires once per session for each project, placement and variant, with the paid visitor count, paid share and detected platforms where known. Row actions do not report shown. web analytics marketing cross sell dismissed records the surface and the dismissal count. web analytics marketing cross sell source created records the same attribution plus source_id and source_type, only after the source creation API succeeds. The model is the first Advertising source created after the latest click within 24 hours, in the same browser tab, project and identified user. Session storage preserves the click across a same-tab OAuth redirect and reload; a successful connection consumes it. A failed connection retains it for retry. Non-ad connections and flag-off users do not read or consume it. Storage restrictions, switching devices or tabs, and later connections can leave conversions unattributed. These events use the existing analytics SDK and add no eligibility queries.

Join the attributed source_id to source sync usage and the project's billing customer to estimate its share of billed Data warehouse revenue. Use billable usage and actual invoice amounts, including free allowances and adjustments; a created source or a click is not revenue. Deduplicate source IDs before allocating revenue and keep acquisition (has_connected_sources = false) separate from expansion. This is click attribution, not proof of incremental revenue. Measure incrementality with a randomized holdout at the billing-customer level so projects from one customer do not appear in both groups.

Campaign breakdown display

Campaign breakdown results have a maximum height of 36rem and scroll within the table area. The search, grouping, and column controls remain above the scroll area.