Proposal: Player-Tier KPIs

Status — integration note, not a new design

Zoinkwiz authored the tier / audience / badge data-model (quests-data-model) without Supabase context. This page is the other half: what the live database actually holds today, so his metrics can bind to real columns. It adopts his model (rookie → reliable → pro, player_tier_state, responsiveness as keystone) and only fills the part he left to Marco — the values, thresholds, and the anchor — plus flags the gaps that need new plumbing. Nothing here is shipped.

The keystone, grounded

Zoinkwiz's keystone metric is responsiveness — lead time from a quest going live to validated data arriving:

responsiveness = first_validated_at − quests.starts_at

Anchor decision (Marco): starts_at. It doubles as the org-level "timeline-range" answer for Vincenzo & Alex. But raw starts_at is unfair to a player who simply wasn't there — so it must be paired with an exposure signal: did the launcher open while the quest was live and the quest go undone? That distinguishes slow from absent.

The exposure signal does not exist yet

There is no "launcher opened / last seen" timestamp on the user in the schema today (last_seen_at exists only on rate-limit and nvenc tables). "Exposed but not done" is net-new telemetry — see Gaps. Until it lands, responsiveness can only be measured for players who did record.

KPIs → where they bind today

His player_tier_state carries three live metrics. First, what each maps to in the real schema and whether it's computable now:

player_tier_state field	Bind to (live schema)	Status
responsiveness_p50_h	first_validated_at − quests.starts_at, p50 per player	⚠️ blocked — no validation timestamp, no quests table yet
validated_hours	eligible recorded hours (sum(duration_seconds) where uploadable + eligible)	✅ proxy now (86% of duration_seconds filled)
completion_rate	uploadable / total sessions per player	✅ computable today

The full metric catalogue

Every column the view computes today, grouped by axis. This is the complete signal set behind the tier — responsiveness is the keystone, but these are what's available right now and what the rules below actually use.

Axis 1 — Engagement (volume / habit / breadth). Drives how much a player shows up in the value loop.

View column	Meaning	In the tier?
sessions	All recording attempts	context
eligible_sessions	Sessions on an enabled eligible game	context
eligible_uploadable_sessions	Eligible and uploadable — the true "did a quest" count	✅ yes
eligible_uploadable_30d	Same, last 30 days — the live engagement number	✅ primary
hours / validated_hours_proxy	Total vs eligible recorded hours (validated_hours proxy)	✅ yes
distinct_eligible_games	Catalogue breadth	context
active_days / active_days_30d	Distinct days recorded (all-time / 30d)	✅ primary (30d)
tenure_days	First→last session span	context
days_since_last	Recency — active vs dormant	✅ decay

Axis 2 — Quality / Trust. Scales engagement and caps it. Volume alone is never enough (proven below).

View column	Meaning	In the tier?
completion_rate	uploadable / total — usable share	✅ gate (≥0.5 / ≥0.8)
eligible_ratio	Share of activity inside the value loop (keeps free-play out)	✅ yes
failure_rate	failed / total — separates "slow" from "broken setup"	signal
vac_banned / game_bans / trust_gate_ok	Steam ban status → hard cap	✅ hard gate

Axis 3 — Social / Growth. Rare, high-signal — reserved for the top tier / a future pro+ boost.

View column	Meaning	In the tier?
lobby_sessions	Sessions recorded in a party / co-op lobby	boost
referrals_made	Sign-ups this player drove	boost

Acquisition signals — reference only, deliberately not in the tier.

View column	Meaning	Why excluded
network_value_ref	Friend-network value (headhunter signal)	Acquisition, not contribution — high-network users are often dormant
steam_hours_ref / steam_level_ref	Steam profile depth	"Real gamer" prior, useful for a starting tier, not progression

Why acquisition signals stay out

network_value & friends belong to the acquisition domain (headhunter / Growth & Network), not the player-tier. The live data has high-network users with zero recent activity — folding them in would rank dormant non-players above active contributors. They ride along in the view as *_ref columns purely so both numbers sit side by side.

What the live data revealed

Running the proxy KPIs against the real cohort (36 users who ever recorded) surfaced things a context-free design can't see — these shape the rules:

• Volume ≠ value. One account: 394 sessions at 0.28 completion (a test rig); another: 31 sessions at 0.71 failure. A volume-only ladder crowns the two worst producers. → Volume is always gated by completion_rate.
• Two unrelated "high values". Active power-users (high completion, recent) vs high-network dormants (network_value huge, 0 sessions in 30 days). Different concepts; only the first is a player-tier.
• The trust gate is real. A VAC-banned account had network_value ≈ 730k.
• Decay is mandatory. Several top-by-history users have sessions_30d = 0. A highest-ever tier parks them at the top forever; a 30-day window doesn't.

Threshold values (Marco owns these)

Illustrative player_tiers.thresholds, on a 30-day window. Calibrate as the base grows — the shape is durable, the numbers aren't.

Tier	thresholds (conceptual)
rookie	≥1 eligible + uploadable session ever. (Trust-gated: VAC / game-ban → capped here.)
reliable	completion_rate ≥ 0.5 · ≥10 eligible+uploadable / 30d · ≥3 active days / 30d
pro	completion_rate ≥ 0.8 · (≥10 active days / 30d or ≥40 eligible+uploadable / 30d) · trust-gate OK

Against the live cohort this lands Zoinkwiz, Ornateblack55, Mettws as pro (all completion ≥ 0.94), and correctly keeps the 0.28 / 0.48 high-volume accounts out of pro — the failure mode the gate exists for.

What exists vs what needs plumbing

The actual integration checklist:

Need	State today	Action
Tier / quest / badge / audience tables	None exist (information_schema confirms)	Land Zoinkwiz's model as a migration in playroll-cpp/supabase/migrations.
Validation timestamp (first_validated_at)	server_verified_at is 0% populated	Write it on the validation event — without it, responsiveness is uncomputable.
Launcher-open / exposure signal	Does not exist on the user	Net-new telemetry (heartbeat or last_seen_at on playroll_users).
validated_hours, completion_rate	✅ Proxy-computable from recording_sessions	Use the companion view until the real columns exist.

The view, live — v_player_tier_kpis

A read-only view already lives in the public schema computing every currently-computable KPI per user plus a proposed_tier (aligned to the rookie | reliable | pro ladder). It writes nothing, is safe to drop, and is not in migration history on purpose — it's a proposal aid. The canonical tier artifact lands as a tracked migration once the model is agreed.

Snapshot of every active user, straight from the view:

As-of 2026-06-18 — a static snapshot

These are real rows from the view at one point in time, pasted in (the docs site is statically built and has no live DB connection). Re-run the query and refresh this table to update. valid_hrs is the validated_hours proxy (eligible recorded hours); true responsiveness is not in here yet — it needs the validation timestamp from the plumbing gaps.

Player	Tier	Sessions	Elig+up / 30d	Active days / 30d	Completion	Valid hrs	Days since last
Zoinkwiz	pro	93	88	9	0.95	1.7	0.9
Ornateblack55	pro	82	71	13	0.99	7.4	0.9
Mettws	pro	53	45	7	0.94	4.3	0.9
brondix	reliable	72	42	8	0.64	3.7	1.9
Jonathan Appleseed	reliable	31	31	6	1.00	2.2	0.3
marco_testa	rookie	394	76	15	0.28	14.2	4.1
denise.bnmilano	rookie	171	54	11	0.48	17.8	2.0
Cruciani1v9	rookie	31	8	7	0.29	3.1	7.9
Noodles	rookie	42	0	0	1.00	163.1	67.8
jack.lo.squartatore	rookie	21	3	1	0.95	1.9	8.1

Two things the snapshot proves at a glance:

• Volume / hours alone are not the tier. marco_testa has the most sessions (394) but lands rookie on a 0.28 completion; Noodles has 163 validated hours but is rookie — dormant (68 days idle, 0 in the 30-day window). The gate and the decay are doing their job.
• pro is exactly the active + high-completion set — and the two big low-completion accounts (0.28 / 0.48) correctly never reach it.

---

Open items (for the thread with Zoinkwiz / Vincenzo / Alex): the exposure signal (how we record "launcher open during live quest"); where first_validated_at gets written; whether post-training hours count toward validated_hours; and the initial promotion thresholds above. · See also Data Model — Overview.