Kelas Sekejap · internal · for the engineering team
What the /cohort dashboard should measure to show how students actually use Kelas Sekejap — what's working, what's not, and who needs attention — organised as six questions, with the data source and effort for every metric.
Grounded in the current code: app/cohort/page.tsx (spm-prod), handlers/spm/cohortReport.ts, the Drizzle schema, and the PostHog events in lib/track.ts. Written 27 Aug 2026, during the focus group and four weeks before the consumer launch.
The report currently shows four totals — participants, pre-check done, started practising, post-check done — and a roster with school, form, pre-check status, lessons completed, days active, and last seen. That was the right readout for week one: it answers "did they get in the door?"
It cannot yet answer the questions that matter for the rest of the study and for the September launch: are they coming back, where do they get stuck, and are they actually speaking? Three blind spots in the current numbers are worth naming because they quietly distort what you see:
login_activities is only written by the old app's streak service, and SPM bootstrap stamps nothing.Metrics are grouped by the question they answer, because a dashboard organised by question stays useful when the numbers change. Every metric names its source and its cost:
The single highest-value addition: an activation funnel with conversion between steps. You already learned this lesson the hard way on 19 Aug — 12 of 14 students finished the pre-check but only 5 ever practised. The leak wasn't the pre-check, it was the transition after it (fixed in PR #110 by sending "Start Day 1" straight into the lesson). A funnel makes every future leak visible without a manual investigation.
Show each step as a count and a % of the previous step, plus median time between steps. "Signed up → first lesson > 24 h" is a different problem (activation) than "Day 1 done → never returned" (retention), and the funnel separates them.
| Metric | Definition · why it matters | Source | Effort |
|---|---|---|---|
| Activation rate | % of signups who complete ≥1 real lesson (excluding the Speaking Check) within 48 h. The one onboarding number to track weekly. In September this becomes the top of the trial→paid funnel. | spm_profiles + spm_lesson_completions |
query only |
| Step conversion + time-to-step | Count, % of previous step, and median hours between each funnel step above. Finds the leak while it's happening instead of two weeks later. | spm_lesson_completions, practice_item_sessions |
query only |
| Tried-but-not-saved | Students with attempts but no completion, per lesson — the existing "tried 3×, not saved" pre-check pattern, generalised to every lesson. This exact shape caught the iOS capture bug. Make it a standing panel, not a one-off. | practice_item_sessions vs spm_lesson_completions |
query only |
Engagement is the study's first outcome and the launch's economics. One habit metric should lead the dashboard; the rest support it.
Days in the week with ≥1 completed lesson, shown as a distribution (0 / 1–2 / 3–4 / 5+ days), not an average. A cohort where ten students practise daily and ten quit looks "fine" as an average and alarming as a distribution. This is also the number a paying parent is buying: my child practises most days.
| Metric | Definition · why it matters | Source | Effort |
|---|---|---|---|
| Retention curve | % of each signup-week cohort active on D1, D3, D7, D14 after signup.
The launch metric. Building it now on the focus group means the query and chart are proven before trial cohorts arrive in September. The old app's /api/admin/metrics/retention handler already has this pattern to borrow. |
spm_lesson_completions (better after the bootstrap stamp, below) |
query only |
| Active today / this week | Students with any server-visible activity in the window, shown against cohort size. The daily pulse. Needs the bootstrap "last seen" stamp to mean opened the app rather than finished a lesson. | bootstrap stamp → spm_profiles.last_seen_at |
small backend |
| Dormancy list | Students inactive 3+ and 7+ days, with days-since-last-seen. The actionable version of retention — this is who Rebekah messages and who the teacher nudges. Triage-first, matching the teacher-dashboard philosophy. | same as above | query only |
| Program-day reached | Histogram of students by furthest day completed (D1…D30) and calendar days taken to get there. Shows pace and spread: is the cohort moving through the course or pooling at a wall? Also tells you when the post-check window (after Day 21) opens per student. | spm_lesson_completions.day |
query only |
| Time-of-day pattern | Completions by hour (MYT) × day of week, as a small heatmap. Directly sets nudge timing and tells you whether practice is school-time (teacher-driven) or evening (self-driven) — a different product depends on which. | spm_lesson_completions.completed_at |
query only |
| Streak health | Distribution of current streaks; streak breaks per week.
The real streak lives only in localStorage today (known TODO: lastPracticeAt on bootstrap). Until then PostHog's streak_milestone / streak_break_shown give a directional read. |
backend streak TODO; interim: PostHog | small backend |
"What's not working" is almost always a specific lesson, not a vague mood. The content diagnostics below find it by day and by lesson type — and the daily feedback students already submit tells you why in their own words.
| Metric | Definition · why it matters | Source | Effort |
|---|---|---|---|
| Drop-off by program day | Of students who completed day N, % who went on to complete day N+1. The wall-finder. A dip at one day number is a content problem you can fix this week; a smooth decay is a habit problem with different remedies. | spm_lesson_completions.day |
query only |
| Completion by lesson type | Started→completed rate per kind (Part 1 / Part 2 / Part 3 / listening / vocab). Answers "is listening pulling its weight?" and whether long-turn lessons scare students off, per form — F1 and F5 will not behave alike. | practice_item_sessions + completions; PostHog lesson_abandoned |
query only |
| Retry rate per lesson | Attempts per completion, per lesson id; flag outliers. High retries + eventual completion = productive struggle. High retries + abandonment = broken or too-hard content. The distinction is the fix. | spm_exercise_attempts, sessions |
query only |
| Listening item accuracy | % correct per listening question.
You already found answer-position bias once by checking; per-item accuracy makes bad items surface themselves. Item-level results ride in the attempt/session payloads and PostHog listen_*_checked events. |
spm_exercise_attempts; PostHog |
query only |
| Day feedback, surfaced | The enjoyed / better / other free-text answers, shown next to that day's drop-off number, with a response-rate count.
This table is already collecting the "why" behind every dip and nobody is reading it in context. |
spm_feedback |
query only |
This is a speaking app; the focus group exists to show real English improvement. Usage metrics can hide the difference between a student speaking for five minutes and one typing three words.
| Metric | Definition · why it matters | Source | Effort |
|---|---|---|---|
| Spoke vs typed | % of exercise sections answered by voice, per student and per lesson type.
A speaking app quietly used as a typing app is the most important red flag this dashboard could catch. inputMode is already stored in each attempt's sections. |
spm_exercise_attempts .sections[].inputMode |
query only |
| Answer length over time | Median transcript word count for Part 2 long-turn answers, week over week. The cheapest longitudinal proxy for fluency growth: fuller answers over the 30 days is visible improvement, from data already on disk. | spm_exercise_attempts .sections[].transcript |
query only |
| Pre vs post Speaking Check | Completion pairing (who has both), confidence self-rating delta (the 1–10 answer is stored per attempt), and — for the study — blind scores from the focus-group-tools pipeline. The study's primary outcome. The dashboard's job is the pairing and the confidence delta; rubric scoring stays in the blind pipeline where it belongs. | practice_item_attempts .validation_result (A/B version, confidence, per-task timings) |
query only |
| Speaking minutes | Recorded speaking time per student per week.
The honest intensity metric — but audio durations aren't captured today. client_metrics was reserved for exactly this (wpm, durations, input mode) and is written nowhere. One client PR starts the clock; the pre/post timings already in validation_result are the interim proxy. |
spm_exercise_attempts .client_metrics — reserved, empty |
new instrumentation |
The iOS capture bug taught the pattern: students who "tried 3×, not saved" were the only visible symptom. Product-health metrics are the smoke detectors — boring until the day they aren't.
| Metric | Definition · why it matters | Source | Effort |
|---|---|---|---|
| Save-failure rate | % of lesson completions where the backend write failed — the client already tracks backend_saved: false on every lesson_completed event.
Progress lost silently is the fastest way to lose a 15-year-old's trust. |
PostHog lesson_completed |
query only |
| Upload failures | speaking_check_upload_failed / _retried counts, split by device/OS (PostHog has device context; the DB does not).
The iOS bug, caught on day one next time. |
PostHog | query only |
| Coach failure rate + latency | % of attempts with coach_status = 'failed', and p50/p95 coach_latency_ms.
Coach feedback is the reward moment of every exercise; a slow or failing coach is invisible in usage counts and corrosive to the experience. |
spm_exercise_attempts |
query only |
The "more details about the students" ask. Two changes: sharpen the roster, and add a per-student drill-down — everything below reads from tables that already exist.
spm_lesson_completions + spm_exercise_attempts.audioRef links for researchers), input mode, and the coach feedback they received.spm_feedback entry, plus their pre-check confidence answer.practice_item_sessions.started_at/ended_at.Privacy stays as-is: researcher-gated on the server, and any surface that shows names, emails, or transcripts keeps the ph-mask class so PII never reaches session replay.
Most of this document is SQL, not product work. The store is richer than the dashboard suggests:
| Already in Postgres | What it yields |
|---|---|
| spm_lesson_completions | Per-lesson completions with day, XP, coins, timestamp → funnel, retention, pace, drop-off, activity dots. |
| spm_exercise_attempts | Full transcripts, prompts, audioRef, inputMode, coach feedback + status + latency → spoke/typed, answer length, retries, coach health, drill-down. |
| practice_item_sessions | started_at/ended_at/status → durations and server-side abandonment; speaking-check sessions tagged via score→spmLessonId. |
| practice_item_attempts .validation_result | Speaking Check A/B task version, 1–10 confidence, per-task timings → confidence delta, interim speaking-time proxy. |
| spm_feedback | Daily enjoyed / better / other free text → the "why" behind every dip. |
| spm_profiles, spm_class_members | School, form, teacher, class membership → all segment cuts. |
| PostHog (identity fixed) | Signup/lesson/speaking-check funnels, lesson_abandoned, screen_viewed, device/OS context. Use PostHog for in-lesson behaviour; keep the dashboard on Postgres for the roster and study numbers. |
spm_profiles.last_seen_at), one write in the bootstrap handler. Unblocks true active/dormant, honest retention, and the already-planned backend streak (lastPracticeAt). Highest leverage change on this page.client_metrics. The column was reserved for recording durations, WPM, and input mode and has never been written. One client PR starts collecting speaking minutes.spm_profiles.school is free text — "Catholic High" and "SMJK Katholik PJ" are the same school. Any per-school cut needs a small mapping table first — the current cohort already spans four schools.speaking-check-* pseudo-lessons everywhere, as the report already does.Deliberately not on this page: cumulative totals (they only go up — use rates and cohort curves), XP/coin sums as engagement (they measure reward emission, not behaviour), and league standings (an incentive surface, not a health metric).
All query-only: activation funnel strip · post-check column + due/overdue status · days-since-last-seen sort + triage chips · drop-off by program day · day feedback surfaced next to its day · tried-but-not-saved panel. Plus the one small backend PR: the bootstrap last_seen_at stamp — every week without it is a week of activity data lost to the study.
Signup-week retention curves (D1/D7/D14) · practising-days-per-week distribution · spoke-vs-typed and answer-length trends · session durations · coach + upload health panel · client_metrics collection. The trial→paid funnel will sit on top of these cohort curves once payments exist.
School rollups on normalised names · aggregate-first layout once the public roster outgrows a single table (a few hundred rows stops being a dashboard) · CSV export for the research pipeline.