Analytics Metrics (Staff)¶

Reference for the 30-day backoffice metric tiles — engagement, conversion, demo, email-only, booking rate with/without Rose — and the staff-only stats_valid_from cutoff used to rescue them when a tracking-config fix happens mid-window.

This page is for Rose staff investigating metric questions. Client-facing copy lives in the schema's ui:description (rendered inline in the backoffice form) and in the Client Documentation. For deeper backoffice-vs-PostHog discrepancy investigations, see the rose-metric-discrepancy skill in .agents/skills/.

What the tiles show¶

The Dashboard tiles are read from the materialized view mv_client_stats_30d. Each row is one client, computed across all of that client's domains.

Tile	Numerator	Denominator	Source
Engagement %	Unique persons who started a conversation	Unique persons whose widget displayed for >10s	`mv_conversation_visitors` / `mv_widget_visitors`
Conversion %	Persons who booked (in-chat) OR captured email (in-chat) OR fired an external `client_form_submitted` / `email_captured` / `demo_booked` event tied to their chat	Conversation visitors	`conversations` + `session_events`
Demo %	Persons with `conversations.demo_booked = TRUE`	Conversation visitors	`conversations`
Email-only %	Persons with `conversations.email_captured = TRUE` AND NOT `demo_booked`	Conversation visitors	`conversations`
Booking rate WITH Rose	Form submitters who also chatted in-window	Form submitters who chatted	`session_events` ∩ `mv_conversation_visitors`
Booking rate WITHOUT Rose	Form submitters who saw the widget but never chatted	Widget-displayed visitors who never chatted	`session_events` ∩ `mv_widget_visitors` (excluding chatters)

Definitive view body: supabase/migrations/20260507190000_add_stats_valid_from_to_mv_client_stats_30d.sql. The MV refreshes nightly (and on-demand via REFRESH MATERIALIZED VIEW mv_client_stats_30d).

Window math¶

For each client, the start of each metric window is the GREATEST of:

NOW() - INTERVAL '30 days' — the rolling 30d floor
The client's first observed engagement / conversion / form event (so brand-new clients aren't shown as "0%" before they have data)
analytics.stats_valid_from — the per-domain cutoff (see below). Aggregated as MAX across a client's domains, so the most-recently-fixed domain caps the whole client.
For form_submission_start only: '2026-04-02T09:00:00Z' — the global floor for client_form_submitted events (events before this are not reliable).

NULL cutoffs collapse cleanly through GREATEST(), so clients without stats_valid_from set keep the full 30d behaviour.

When to use `stats_valid_from`¶

The 30d window is rolling, so any pre-fix garbage events stay in the numerators / denominators until they age out naturally. That's a problem when a tracking-config bug overcounts forms or undercounts conversions — the "real" post-fix metric is buried under up to 30 days of bad data.

Set stats_valid_from whenever you change any of these and want the dashboard to reflect the post-fix world immediately:

analytics.form_tracking_pages — narrowed a wildcard, added/removed a page
analytics.thank_you_pages — same
analytics.form_detection_strategies — toggled superform, hubspot, network_observer, dom_observer, post_message, cta_page_form, thankyou_page
analytics.conversion_filters — form_id whitelist, url_include/exclude
Anything that changes which events the widget fires (or which ones the MV counts as conversions)

You do not need to set it for cosmetic config changes (CTA copy, agent prompts, qualification questions) — those don't affect the events the MV reads.

How to set it¶

The field lives in Settings → Website Agent → Analytics → Stats Valid From (staff-only — non-staff users don't see it; gated by x-access: staff on the schema).

Apply your tracking-config fix and save.
Open the same Analytics page, find Stats Valid From.
Click the picker and either pick a date + UTC time, or click Set to now to use the moment of the fix.
Save the config. The next nightly MV refresh (or a manual REFRESH MATERIALIZED VIEW mv_client_stats_30d) will reflect it.

How to clear it¶

Open the picker again and click Clear. The value persists as null and the window reverts to the rolling 30d floor on the next refresh.

Don't leave a stale stats_valid_from set forever — once pre-fix events have aged past the rolling 30d floor, the cutoff is doing nothing useful and is just one more thing to remember. A reasonable rule of thumb: clear it ~30 days after you set it, unless you have a reason to keep it.

What it doesn't do¶

It does not delete events from session_events / conversations. The data is preserved; only the MV's window math ignores it.
It does not affect PostHog dashboards. PostHog has its own date filters. If you also need PostHog to skip the same window, set the dashboard's date filter manually.
It does not apply to per-conversation pages (Conversations, Visitors, Accounts) — those show raw rows, not aggregates. It only affects the 30d MV tiles.

Worked example¶

Augment's form_tracking_pages was changed from https://checkout.augment.org/* (wildcard, catches login forms) to a tighter pattern on 2026-04-20T07:38:52Z. Before the fix, Augment showed conversion_rate ≈ 24% (inflated by login form submits attributed as conversions). After the fix, new events are clean, but the rolling 30d window still has 30 days of pre-fix events.

Setting stats_valid_from = 2026-04-20T07:38:52Z on the analytics config for augment.org caps all four windows (engagement / conversion / form / email) at that timestamp. After the next refresh, the conversion tile drops to the post-fix value (~8%), reflecting reality. ~30 days later, the cutoff and the rolling floor coincide and you can clear it.

Schema: schemas/configs/website/analytics.schema.json (stats_valid_from property)
Migrations: supabase/migrations/20260507190000_add_stats_valid_from_to_mv_client_stats_30d.sql, 20260508124627_safe_cast_stats_valid_from_in_mv_client_stats_30d.sql
Skill: .agents/skills/rose-metric-discrepancy/ — backoffice-vs-PostHog discrepancy investigation playbook (Pattern 2 in references/known-patterns.md covers the broad-wildcard case)
Linear: IX-2913, IX-2928
Related pages: Data Sources, Visitor Data Flow

Analytics RPC authoring¶

When writing or modifying an analytics RPC that returns conversion metrics (email_captured, demo_booked, conversion rate, funnel step counts, etc.):

Do not aggregate conversations.email_captured / conversations.demo_booked alone. These flags are flipped only by the in-chat dialog supervisor (ixchat). They miss every conversion that happens via an embedded form, Calendly link, HubSpot widget, or any CTA-driven out-of-chat path — which on most clients is the dominant conversion channel (see IX-2969: 4,273 client_form_submitted events vs 75 c.demo_booked flags for lemlist.com over 30 days).
Always join session_events with last-touch attribution. Canonical conversion event is client_form_submitted (PostHog raw: rw_client_form_submitted); also consider email_captured and demo_booked event rows. The pattern is established in get_conversation_funnel and mv_client_stats_30d — reuse it.
Domain normalization is mandatory. conversations.site_domain is stored without www. while session_events.site_domain often has it. Wrap both sides with extract_root_domain(lower(...)) on every join — otherwise the join silently returns zero matches.
Count distinct conversations, not message rows. count(*) FILTER (...) over messages double-counts when a single conversation produces multiple matching rows (e.g. clicks several dynamic questions). Use count(DISTINCT c.id) FILTER (...).
page_url on a client_form_submitted row is the page at/after submit — often a thank-you page, not the form/offer page. $current_url / rw_page_url snapshot where the visitor lands; a redirect-after-submit form records its confirmation page (a payfit resource download fires on /fr/ressources/… but records /fr/remerciement/). So filtering conversions by offer-page URL silently misses them. For excluding junk conversion forms in conversion_filters, prefer form_id_whitelist (stable, present on every CFS row, page-independent) over url_exclude / url_include (IX-3534c: payfit resource downloads slipped a /ressources/ exclude because they recorded /remerciement/). Same trap when backfilling page_url from visitor_page_views — take the last pageview at/before submit, never look ahead (see scripts/backfill/ix3534_backfill_cfs_page_url.sql).
form_id_whitelist only filters events that CARRY a form_id — thank-you-page / post_request_capture rows have form_id IS NULL and always pass (pennylane: self-serve /success signups count as conversions despite a new_ec_contact-only whitelist). A whitelist does not mean "only that form".

Use this normalization for analytics joins only, never for authorization; access checks must resolve the exact domain or registered alias.

Minimum template for the session-events join:

LEFT JOIN LATERAL (
  SELECT
    bool_or(se.event_name IN ('email_captured','client_form_submitted')) AS email_captured,
    bool_or(se.event_name IN ('demo_booked','client_form_submitted'))    AS demo_booked
  FROM session_events se
  WHERE se.session_id = c.session_id
    AND extract_root_domain(lower(se.site_domain)) = extract_root_domain(lower(p_domain))
    AND se.event_at BETWEEN <anchor_ts> AND <anchor_ts> + interval '24 hours'
) attr ON TRUE

Audit existing RPCs against this rule before adding a new one. Known gaps as of IX-2969: get_dynamic_question_stats, get_page_conversation_stats, get_conversations_over_time all flag-only — they should be rewritten when touched. See .agents/skills/rose-metric-discrepancy/references/known-patterns.md Pattern 12 for the full diagnostic case study.

Profiling analytics RPCs¶

When a Home/backoffice analytics RPC times out for a big client (e.g. skello.io):

Measure the real path first — browser wall-clock as the logged-in staff user, on the largest tenant (rank by row count), not MCP buffers on the tenant named in the ticket. Operator index builds cost 5–40 min each; don't spend one to test a hypothesis.

MCP measurements run with RLS off. supabase_read_only_user has rolbypassrls and no authenticated membership: it can only bypass policy cost, never reproduce it.
Per-row RLS on a gated table usually costs more than the plan. House fix: SECURITY DEFINER RPC with one up-front is_super_admin() OR has_domain_access(p_domain), body scoped by domain_alias_group().
Index-only scan needs the bare column in INCLUDE when the key is an expression over it, plus a fresh visibility map (VACUUM, and lower autovacuum_vacuum_scale_factor on hot tables).
Scope probes to the rows returned, not the tenant's traffic — domain-wide ranges scale with client volume and die on the biggest one.
EXPLAIN the body under a generic plan, not with literals. A plpgsql variable (alias_roots) is a parameter, so the RPC can run a generic plan that a literal-array EXPLAIN never shows. SET plan_cache_mode = force_generic_plan; PREPARE q(text[], …) AS <body>; EXPLAIN (ANALYZE, BUFFERS) EXECUTE q(…) works in one MCP call. col = ANY($1) is costed as 10 index descents there and can flip the probe to a worse index; CROSS JOIN unnest($1) plus an equality keeps it (IX-4605: 7,375 → 3,955 buffers, 4.7 GB index → 18 MB partial index).
Verify a rewrite with md5 over full result sets, old vs new, several tenants × date ranges.
Suspect stale planner stats before adding indexes. A ~50×-off row estimate (EXPLAIN est 33 vs actual 1,539) makes the planner seq-scan session_events/conversations instead of using the indexes that already exist. ANALYZE public.conversations; ANALYZE public.session_events; ANALYZE public.visitors; ANALYZE public.visitor_sessions; can take a card from timeout to sub-200ms with zero code change (IX-3577: journey card timeout → 196 ms after ANALYZE alone; the new index was correct but unused until stats were fresh).
Runtime wildly disproportionate to output rows → look for rescan-per-row before blaming data volume: a rows=1 misestimate on person × session_events joins makes the planner re-execute an inlined CTE/subquery once per outer row (EXPLAIN signature: Join Filter: over a full CTE/subquery scan with no hash or Materialize node). Fix = correlated EXISTS on an indexed path, or AS MATERIALIZED on the CTE. Case study: supabase/migrations/20260720092125_*.sql (experiment mart, 25-45min → 18s).
EXPLAIN (ANALYZE) on an RPC shows only the Function Scan total — the inner plan is hidden. To find the hot node, inline the function body as a plain query and EXPLAIN that, or enable auto_explain.

The RPCs are gated (is_backoffice_admin() OR has_domain_access()), so the SQL-editor role hits Permission denied for domain. Impersonate an active admin inside a rolled-back txn:

BEGIN;
SET LOCAL statement_timeout = '120s';
SELECT set_config('request.jwt.claims',
  json_build_object('sub',(SELECT user_id::text FROM backoffice_users
                           WHERE is_admin AND is_active LIMIT 1),
                    'role','authenticated')::text, true);
SET LOCAL ROLE authenticated;  -- without this RLS never runs
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM public.get_conversation_journey(...);
ROLLBACK;

Bump statement_timeout above the default, but the SQL editor still caps total round-trip at ~60 s (gateway) regardless — heavy EXPLAIN ANALYZE must finish under that or run via psql direct.

Editing RETURNS TABLE analytics RPCs¶

These large PL/pgSQL functions fail at call time, not CREATE time — a bad edit deploys clean and then throws on every invocation (whole backoffice page goes blank). Recurring traps:

RETURNS TABLE columns are in-scope PL/pgSQL variables. A bare account_id / country / total_count in a CTE or sub-SELECT collides with the OUT column → column reference "x" is ambiguous at runtime. Qualify every column ref in CTEs (vr.account_id, counts.country), never bare.
ON CONFLICT can't be qualified — rename the OUT column, don't try to qualify. If a RETURNS TABLE OUT column name matches a target-table column in INSERT … ON CONFLICT (col, …), you get column reference "col" is ambiguous at call time, and the "qualify every ref" fix above does NOT apply — conflict-target lists reject table qualification. Rename the OUT column (and any same-named CTE column) so nothing shadows the table column. Fails at cron/call time only. (IX-4095: snapshot capture RETURNS TABLE (source_table …) broke every nightly run.)
Don't add a column with SELECT t.*, newcol. * appends newcol last, but if the RETURNS TABLE signature puts it elsewhere (e.g. total_count after it) you get structure of query does not match function result type … column N. List columns explicitly in signature order.
Aggregate/window result is numeric, not the column's bigint. SUM(...) / PERCENTILE_CONT feeding a RETURNS TABLE ... bigint column throws 42804 … Returned type numeric does not match expected type bigint in column N — at call time only. Cast to the declared type (SUM(x)::BIGINT) or declare the column NUMERIC.

Verify by calling the function (SELECT … FROM get_x_stats_with_intent(...)), not just re-creating it. A raw-SQL test of the inlined body does NOT enforce the RETURNS TABLE types, so it passes while the wrapper throws — and you often can't call the RPC from the Supabase MCP (it's SECURITY DEFINER granted to authenticated only; the MCP role lacks EXECUTE and can't SET ROLE authenticated). So cast aggregate return types defensively rather than trusting a body-only test.

Experiment holdout and exposure validation¶

When checking that an A/B holdout is clean (control never sees Rose, treatment does), query PostHog raw events, not the Supabase mart: - Assignment + gate: rw_posthog_initialized — rw_staff_experiment_assignment ∈ control|treatment, rw_staff_experiment_id, and the display gate rw_display_allowed (control is always false). (rw_client_experiment_* for domain experiments.) - Rose actually shown: rw_widget_impression. - PostHog raw events/properties keep the rw_ prefix; the Supabase session_events mirror strips it.

GOTCHA — validate at the session level (join on rw_session_id), never the person level. rw_widget_impression carries no assignment, so joining impressions to a person's last assignment counts cross-session leakage (a visitor who was treatment earlier, then control) and falsely shows control seeing Rose. The same-session join collapses it to the noise floor (IX-3518: person-join showed 797 "control" persons with impressions; same-session was 17 of 11,464 = 0.15%).

Arm comparisons: condition only on PRE-treatment covariates (entry page, referrer, UTM, time). Any variable Rose can influence — engagement/bounce, chat usage, prior-exposure signals — differs by construction across arms; segmenting or gating on it manufactures fake effects (3 instances in IX-4055, incl. a retracted z≈2.8 cell). Post-mortem: docs/analyses/ix-4055-experiment-reading-learnings.md.
bounced in the experiment mart = "no engagement" (≥2 pageviews OR message_sent OR any CTA click OR converted — see 20260720151539_ix4055_engaged_bounce_definition.sql). Since message_sent only exists where Rose runs, cross-arm bounce deltas are partly definitional — fine as description, never a causal segment.