Behavioral Metrics Dictionary

The single agreed definition of NanaSelect's behavioral-analytics numbers (issue #349, part of capability #346). Numbers must be trustworthy before anyone acts on them, so every metric is defined once, here, and has one executable form — a change to a definition is a change to both this page and the code it names.

The one-line version: every metric is a summary of genuine-human session store rows — bots, internal/staff traffic, legacy- unclassified rows, and test fixtures are filtered out before any counting, and the funnel stages mean exactly what this page says they mean, in the dashboards and in the pre-aggregated rollup alike.

This is a reference doc — definitions, not a walkthrough. For how the snapshot became the analytics backbone see session-store.md; for the consent gating that governs whether a row exists at all, see privacy-consent.md. For the additive interaction-grain event stream (time-on-question, answer churn, CTA click, the engaged signal) that complements these aggregate funnel metrics, see behavioral-events.md.

Trustworthy base: the genuine-human filter

Before any metric is counted, the base set of sessions is narrowed to genuine- human traffic by the single shared filter genuineHumanTraffic() (src/lib/server/analytics/traffic-filter.ts, applied by every analytics query). It is the query-time complement of #348's write-time classification — it consumes the set-once selection_sessions.client_class signal and never re-derives classification from a User-Agent (the UA is never stored; see session-store.md).

Row	client_class	Counted?	Why
Genuine visitor	human	✅ yes	The population every metric is about.
Crawler / automation	bot	❌ no	Non-human; #348 flags it at write time from the UA.
Staff / internal	internal	❌ no	Demos and QA must not skew the funnel; #348 flags it from the internal-marker cookie.
Legacy / unclassified	NULL	❌ no (default)	Created before #348 — origin unknown. Excluded by default: unknown ≠ trustworthy.
Test fixture (seed-… id)	any	❌ no (default)	Seeded straight into the store with no request, so no classifier ever saw it.

Two documented, deliberate decisions:

• Legacy NULL rows are excluded. The filter's core is client_class = 'human', which in SQL is NULL-safe (a NULL makes the equality NULL, not true), so unclassified rows drop out naturally. The undercount is bounded and transient — NULL rows predate #348 and self-purge within the 90-day session TTL (#116). A raw-volume view that genuinely wants unknowns can pass includeUnknown to count NULL as its own bucket — never as human, and never widening bot/internal.
• Test fixtures are excluded via an id NOT LIKE 'seed-%' clause — the gap a write-time UA classifier cannot see, since fixtures are injected without a request. In production this is a no-op (no seed- rows exist); it only removes seeded noise from dev metrics. A dev surface that wants to exercise against seed data passes includeTestSessions (the admin tracks page does this, so its per-track rollup stays visible in dev).

The pure predicate isGenuineHumanRow mirrors this SQL exactly and is unit-tested without a database, so the filter's behaviour can never silently drift from its definition — the precise failure this requirement exists to prevent.

Funnel stages

Each stage is defined over the genuine-human base above. The executable form is src/lib/server/analytics/metrics.ts (pure predicates + summarizeFunnel); the pre-aggregated rollup expresses the identical definitions as SQL CASE/EXISTS aggregates, kept 1:1 with the predicates.

Metric	Definition	Source column / signal	metrics.ts	SQL mirror (rollup.ts)
started	Every genuine-human session.	the filtered row itself	rows.length	COUNT(*)
engaged	Answered ≥ 1 factor (a recorded skip counts as an interaction).	answers_json non-empty	isEngaged	answers_json IS NOT NULL AND answers_json != '{}'
completed	Reached results.	completed_at set	isCompleted	completed_at IS NOT NULL → COUNT(completed_at)
converted	Became a known lead — a durable Eloqua association exists.	an eloqua_session_contacts row	isConverted	EXISTS (SELECT 1 FROM eloqua_session_contacts WHERE session_id = id)
abandoned	Engaged but never completed — started answering, dropped out before results. Completing later removes a row from abandoned (stages don't double-count).	engaged ∧ ¬completed	isAbandoned	the engaged CASE AND completed_at IS NULL

Notes on the edges:

• engaged counts a recorded skip (a null option value) because a skip is a deliberate interaction with the stepper; only a missing or empty answers_json is "never engaged".
• converted is deliberately the durable lead fact (eloqua_session_contacts), not the transient eloqua_sync_queue (which is purged after sync/abandonment) — so conversion is stable across the sync lifecycle and survives PII purge.
• abandoned and completed are mutually exclusive by construction; engaged is their union plus any (rare) completed-without-recorded-answers row.

Derived rates

Each rate is a fraction in [0, 1] of started, safe-divided (a zero denominator yields 0, never NaN, so an empty period renders cleanly). Rates are derived at read time, never stored — changing a rate's definition needs no rollup recompute.

Rate	Formula
completion rate	completed / started
conversion rate	converted / started — share of genuine visitors who became leads
engagement rate	engaged / started
abandonment rate	abandoned / started

Rollup & the D1 performance budget

Admin analytics must stay fast on D1, so the funnel is pre-aggregated into analytics_session_rollup (schema in src/lib/server/db/schema.ts; recompute/read in src/lib/server/analytics/rollup.ts) rather than scanned live.

• Shape. One row per (day, track_slug) — day is the session-creation calendar day (date(created_at), UTC) — holding the five counts above.
• Read budget. A dashboard reads only the rollup: at most days × tracks rows for a window, a single indexed scan of a small table. It never scans selection_sessions at request time. The unbounded work (the full aggregate over sessions) happens in the recompute, amortized to a scheduled/on-demand job.
• Derived, never a source of truth. recomputeRollup rebuilds any day range idempotently (delete-then-insert from the current genuine-human base), so a re-run reproduces identical rows, and a purged or backfilled base is reflected on the next recompute — a stale row for a now-empty day is cleared, not left behind. computed_at records when each row was last rebuilt (staleness checks).
• Same filter, same definitions. The recompute's WHERE is the shared genuineHumanTraffic() predicate, and its CASE/EXISTS aggregates are the 1:1 SQL mirror of the metrics.ts stage predicates — the rollup can only ever hold the same numbers a live summary would.

Track / persona-path effectiveness (issue #354)

Which entry door converts and completes best — the measurement foundation A/B experimentation (#355) reads from. No new aggregation: the per-track funnel counts already live in analytics_session_rollup (keyed [day, track_slug]); this is the per-track comparison folded from them.

Per track, over the rollup rows in range (genuine-human by the rollup's own aggregate WHERE — this layer never re-filters):

Per-track measure	Definition
sessions (started)	genuine-human sessions that entered through this track — the comparison volume.
completion rate	completed / started (the funnel completionRate, folded by summarizeRollupCounts).
conversion rate	converted / started (the funnel conversionRate) — the headline "this entry door works".

Tracks rank best entry door first: conversion rate desc, then completion rate desc, then volume (started) desc, then slug asc — so the strongest door leads, with volume and name as deterministic tie-breaks. summarizeTrackTrend folds the same rows into a per-day/track series for the trendable view. Only time segments this lens (the rollup's session-grain dimensions are day + track), so it honours the from/to range and ignores the single-track filter — it is a comparison across tracks.

The metric surface #355 reads. readTrackEffectiveness returns the ranked per-track comparison, the per-day/track trend, and the overall funnel in one call — the agreed definition of "track conversion" A/B experimentation compares variants with, rather than re-deriving it.

Per-factor drop-off (issue #350)

The session funnel answers how many abandoned; per-factor drop-off answers which question they abandoned at — the single highest-leverage view for retuning the guided flow. It ships coarse on the snapshot (per decision #357): no new capture. The interaction-grain cut (time-on-question, back-navigation, hesitation) is a v2 follow-on gated on the event stream (#358).

Derived from the snapshot. answers_json records only what was answered (factorId → optionId | null), not per-interaction events — so "how far did this visitor get?" is the furthest factor they interacted with, read against the ordered factor list for their flow: a track's factor_refs_json ask-order for a single-track view, or globalFactorOrder (src/lib/server/admin/factors.ts) for the all-tracks view. A recorded skip (a null value) counts as an interaction — they saw that question and moved on — consistent with engaged/countAnswers.

For each factor fᵢ in that ordered list, over the considered genuine-human sessions (already narrowed by the same genuineHumanTraffic() filter, plus any track / application / time segment):

Per-factor stage	Definition
viewed	reached fᵢ — interacted at position ≥ i, or completed (a completed visitor saw every question). A never-engaged session (no answers, not completed) contributes nothing: per-factor drop-off measures where engaged visitors stop, which the started → engaged step already frames.
answered	gave a genuine (non-null) answer to fᵢ.
advanced	progressed past fᵢ — interacted beyond position i, or completed.
droppedHere	viewed − advanced — reached fᵢ and never got past it. Completed visitors always advance, so this is exactly the incomplete sessions whose furthest-reached factor is fᵢ (their "last-answered factor").

Per-factor rate	Formula
drop rate	droppedHere / viewed — comparable across factors regardless of how many reached them; the highest-abandonment factor is the max, surfaced first.
answer rate	answered / viewed — of those who reached it, how many gave a real (non-skip) answer.

Both rates are safe-divided (0 when viewed is 0, never NaN) — the same discipline as the funnel rates.

No rollup — aggregated live over the snapshot. analytics_session_rollup has no per-factor dimension, so per-factor drop-off is not pre-aggregated. The read path (analytics/factor-dropoff-read.ts) scans genuine-human selection_sessions for the segment and folds them through the pure summarizeFactorDropoff (analytics/factor-dropoff.ts). The scan is bounded by the segment (track / application / time range) and reads only the columns the summary needs.

Recommendation quality — the feedback loop (issue #352)

Recommended-vs-viewed-vs-engaged is a closed loop that retunes the core product: when a rule's recommendation is routinely ignored, the loop is telling you which rule to retune. It ships on the snapshot — no new capture — and, like per-factor drop-off, sharpens once the interaction-grain event stream (#358) lands.

recommended vs viewed vs engaged. Per session the guided flow records a recommendation snapshot (recommendation_json: { recommendedSystemId, alternativeSystemIds, ruleId }) and the surfaced shortlist (systems_viewed_json). Because the shortlist is by construction[recommendedSystemId, ...alternativeSystemIds], "viewed" today means results were surfaced at all, not which system the visitor preferred. The genuinely behavioral engaged signal — click-through / compare / quote intent on a specific system — depends on #358 and is not captured yet:

Dimension	Snapshot definition
recommended	the rule fired and picked recommendedSystemId (the firing ruleId).
viewed	results surfaced (systems_viewed_json non-empty) — the recommendation was actually shown, not abandoned mid-flow.
accepted	of viewed, the session converted (became a known lead — the converted funnel predicate). The strongest post-results "acted on the recommendation" signal the snapshot carries; it sharpens to engaged with the recommended system once #358 lands.
engaged	click / compare / quote on the recommended system. Reserved — always absent until #358; the summary reports engagedCaptured: false so the admin view labels it "coming soon" rather than a misleading zero.

Per firing rule, over the considered genuine-human sessions (narrowed by the same genuineHumanTraffic() filter, plus any track / time segment):

Per-rule count	Definition
fired	genuine-human sessions this rule produced the recommendation for.
viewed	of fired, sessions whose results were surfaced.
accepted	of viewed, sessions that converted to a known lead.

Per-rule rate	Formula
acceptance rate	accepted / viewed — safe-divided (0 when viewed is 0), comparable across rules regardless of firing volume.

Retuning candidates. A rule is a retuning candidate when it has enough signal (fired ≥ minFiredForCandidate, default 5 — a 1-fire 0%-accept rule is noise) and its acceptance is at or below a ceiling (default 0.5). Candidates rank worst-acceptance first, tie-broken by firing volume, so the highest-impact underperformer leads and links straight to its right-sizing editor.

No rollup — aggregated live over the snapshot. Like per-factor drop-off, there is no rollup dimension for firing rule, so the read path (analytics/recommendation-quality-read.ts) scans genuine-human rows that carry a firing rule and folds them through the pure summarizeRecommendationQuality (analytics/recommendation-quality.ts), bounded by the segment.

Demand intelligence — answer distributions (issue #353)

Not how far prospects get (the funnel) or where they stop (drop-off), but what they actually want — per factor, the distribution of the options they chose, so the most-requested sizes / applications / budget tiers / performance priorities feed roadmap and positioning. It ships on the snapshot — aggregated live over answers_json, no new capture and no rollup dimension.

A demand signal. answers_json maps factorId → optionId | null. Per factor a session contributes exactly one signal:

Signal	Definition
response	a genuine (non-null) answer to the factor — a vote for that optionId. This is the demand.
skip	a recorded null answer — an interaction ("saw it, wanted none"), tracked as skipped but never folded into any option's share (indecision ≠ preference).
absent	the factor was never reached — contributes nothing to that factor.

Per option, over the considered genuine-human sessions (narrowed by the same genuineHumanTraffic() filter, plus any track / application / budget / time segment):

Per-option measure	Definition
count	genuine-human sessions that chose this option as their answer to the factor.
share	count / factor responses — safe-divided (0 when responses is 0), comparable within a factor regardless of how many answered it.

Options rank most-requested first (count desc, tie-broken by optionId); factors rank by engagement (responses desc, tie-broken by factorId), so the options and factors buyers care about most lead.

Segmentation & trend (AC-02). Track and time bound the SQL scan directly. Application (residential-commercial) and budget (budget) are answer-value segments — they live inside answers_json, so they filter in memory over the already-bounded scan (a session with no genuine answer for the segmenting factor is dropped, exactly as an application-sliced drop-off does). summarizeDemandTrend buckets the same votes by date(created_at) into long-format (day, factorId, optionId, count) points; skips and un-bucketable rows are excluded.

Export-friendly (AC-04, feeds #356). toDemandExportRows flattens the summary into tidy long-format rows (factorId, optionId, count, share, responses), sorted deterministically — a BI-ready shape the data-export requirement (#356) consumes without re-deriving shares.

No rollup — aggregated live over the snapshot. Like per-factor drop-off and recommendation quality, there is no rollup dimension for per-option demand, so the read path (analytics/demand-intelligence-read.ts) scans genuine-human rows and folds them through the pure summarizeDemand (analytics/demand-intelligence.ts), bounded by the segment.

Where this is enforced

Concern	Code
Genuine-human filter (SQL + pure mirror)	src/lib/server/analytics/traffic-filter.ts
Funnel definitions (pure)	src/lib/server/analytics/metrics.ts
Track-effectiveness definitions (pure)	src/lib/server/analytics/track-effectiveness.ts (#354)
Track-effectiveness read surface (rollup fold)	src/lib/server/analytics/track-effectiveness-read.ts (#354)
Per-factor drop-off definitions (pure)	src/lib/server/analytics/factor-dropoff.ts
Per-factor drop-off read path (live scan)	src/lib/server/analytics/factor-dropoff-read.ts
Recommendation-quality definitions (pure)	src/lib/server/analytics/recommendation-quality.ts (#352)
Recommendation-quality read path (live scan)	src/lib/server/analytics/recommendation-quality-read.ts (#352)
Demand-intelligence definitions (pure)	src/lib/server/analytics/demand-intelligence.ts (#353)
Demand-intelligence read path (live scan)	src/lib/server/analytics/demand-intelligence-read.ts (#353)
Rollup recompute / read	src/lib/server/analytics/rollup.ts
Rollup table	analytics_session_rollup in src/lib/server/db/schema.ts
Write-time classification (upstream)	src/lib/server/client-classification.ts (#348)
Tests pinning the definitions	analytics/traffic-filter.spec.ts, analytics/metrics.spec.ts, analytics/track-effectiveness.spec.ts, analytics/factor-dropoff.spec.ts, analytics/recommendation-quality.spec.ts, analytics/demand-intelligence.spec.ts

Any dashboard that reports these metrics must cite this dictionary and consume these helpers — it must not re-define a stage or re-implement the filter inline, which is exactly how "trustworthy" quietly stops being true.

Content effectiveness (#458)

The content→outcome funnel over the event stream (genuine-human sessions only, same traffic filter as every table above). One executable form: summarizeContentEffectiveness in src/lib/server/analytics/content-effectiveness.ts; read half joins content_view × cta_click events per session.

Number	Definition
Shown	Distinct sessions in which the content item surfaced (content_view, deduped per session).
Hand-offs	Of those sessions, how many also clicked any hand-off CTA (cta_click, any kind).
Rate	Hand-offs ÷ shown.
Enrichment priority	A row with hand-offs > 0 that is thin on the app side — a corpus item with no accepted facet tags, or a project with no ingested imagery — capped to the top 8 by hand-offs. The feedback list for the nanawall.com content team.