Instructor analytics: enrollments, earnings, completion, quiz distributions & lesson drop-off

[steryereo]

Problem Statement

As an instructor on Cadence, I can create courses, edit curriculum, and see a flat list of my courses with raw lesson and student counts — but I have no real insight into how my courses are performing. I can't tell at a glance how many people are enrolled across everything I teach, how much money I've actually made, whether students are finishing my courses, how they're scoring on my quizzes, or where in a course students give up. The current student roster page shows per-student rows, but it answers "how is this one person doing?" — not "how is this course doing?" I'm flying blind on the decisions that matter: what to fix, what to promote, and what's working.

Solution

Give instructors an analytics layer at two altitudes:

1. A portfolio overview on the existing "My Courses" page (/instructor) — a stats strip showing total enrollments, total unique students, total earnings, and average completion across all my courses, so the first thing I see is the health of my whole teaching business.

2. A per-course analytics page at /instructor/:courseId/analytics — completion rate and average progress, a per-quiz score distribution (A–F), and a lesson-completion funnel that highlights the lessons where students drop off, so I can diagnose and improve a specific course.

All metrics are read-only, derived from existing data (enrollments, purchases, lesson progress, quiz attempts). No charting dependency is added — headline numbers plus hand-rolled CSS bars (matching the progress bars already used on the roster page).

User Stories

1. As an instructor, I want to see the total number of enrollments across all my courses, so that I understand the overall reach of my teaching.
2. As an instructor, I want to see the number of unique students across all my courses (deduplicated), so that I know how many distinct people I've taught, not just enrollment events.
3. As an instructor, I want to see how many new enrollments I got in the last 30 days, so that I can sense recent momentum.
4. As an instructor, I want to see my total gross earnings across all courses, so that I understand how much money my courses have generated.
5. As an instructor, I want to see my earnings from the last 30 days, so that I can gauge recent income.
6. As an instructor, I want earnings displayed in dollars (converted from stored cents), so that the numbers are immediately readable.
7. As an instructor, I want my $0 / free courses to show $0 cleanly rather than an error, so that the overview is trustworthy.
8. As an instructor, I want the portfolio overview to appear on my existing "My Courses" page, so that I don't have to navigate somewhere new to get the big picture.
9. As an instructor, I want a per-course "Analytics" link in the course editor header (next to "Students"), so that I can drill into a specific course's performance.
10. As an instructor, I want to see the completion rate of a course (the percentage of enrolled students who have completed 100% of its lessons), so that I know whether students are finishing.
11. As an instructor, I want to see the average progress percentage across enrolled students, so that I have a softer companion metric when few students have hit 100%.
12. As an instructor, I want the completion rate computed from actual lesson progress rather than an unreliable "completed" flag, so that the number reflects reality.
13. As an instructor, I want to see, for each quiz in a course, a distribution of student scores grouped into A–F grade buckets, so that I can tell which quizzes are too hard or too easy.
14. As an instructor, I want each student counted once per quiz using their best attempt, so that students who retried many times don't skew the distribution.
15. As an instructor, I want to see each quiz's average score and pass rate alongside its distribution, so that I get a quick summary in addition to the histogram.
16. As an instructor, I want pass rate based on whether students actually passed (the recorded result), so that it matches what students experienced.
17. As an instructor, I want a lesson-completion funnel listing my lessons in course order, so that I can see how completion declines as the course progresses.
18. As an instructor, I want the funnel to highlight the lessons with the biggest drop in completions from the previous lesson, so that I can pinpoint where students give up.
19. As an instructor, I want the funnel to cover all lessons (not just video lessons), so that I get a complete picture of the course.
20. As an instructor, I want analytics to include all my courses regardless of status (draft, published, archived), with the status shown for context, so that historical and in-progress courses are still represented.
21. As an instructor, I want clear empty states (no enrollments, no quizzes, no lessons), so that an empty course doesn't look broken.
22. As an instructor, I want all rates and percentages to never show NaN or errors when denominators are zero, so that the page is always sane.
23. As an instructor, I want to only see analytics for my own courses (admins may see any), so that data stays appropriately private.
24. As an instructor, I want a tooltip/note explaining that enrolled counts and revenue can differ (coupons, free enrollment), so that I'm not confused when they don't line up.
25. As an admin, I want to view any course's analytics page, so that I can support instructors and audit performance.

Implementation Decisions

New deep module — analyticsService

• A new, read-only service that encapsulates all analytics aggregation behind a simple, typed interface. This is the one deep, isolated-testable unit; the routes are thin presentation wrappers around it.
• Proposed interface (positional or object params per project convention):
  • getInstructorEarnings(instructorId) → per-course and portfolio earnings totals (lifetime + last-30-days), in cents.
  • getInstructorEnrollmentSummary(instructorId) → total enrollments, unique student count, last-30-days enrollments.
  • getCourseCompletionStats(courseId) → completion rate (% of enrolled students at 100% lesson progress) and average progress %.
  • getQuizGradeDistribution(courseId) → per quiz: A–F bucket counts (best attempt per student), average score, pass rate.
  • getLessonFunnel(courseId) → lessons ordered by module position then lesson position, each with completion count and drop-off delta from the prior lesson.
• Implemented with set-based Drizzle aggregation (GROUP BY / COUNT / SUM, typed sql<number>), following the precedent of enrollmentService.getEnrollmentCountForCourse. Fully typed — no any, and explicitly not copying the raw-SQL/any style of the existing getQuizStats.

Metric definitions

• Earnings = gross SUM(purchases.pricePaid) for the instructor's courses, displayed dollars (cents ÷ 100). No net/fee/refund modeling (no data exists for it). pricePaid already reflects PPP discounts and team/multi-seat totals, so summing it is correct.
• Enrollments = counts from the enrollments table. Enrolled count and revenue intentionally may not match (coupon redemptions, free enrollment, seed data).
• Completion rate = % of enrolled students whose lesson progress is 100% (via the existing progress calculation, lessons only / quizzes excluded). It is not based on enrollments.completedAt, because that column is never set by the running app. Companion metric: average progress %.
• Quiz distribution = per quiz, one data point per student (their best attempt), bucketed by the existing A–F grade scheme. Pass rate uses the stored passed boolean on attempts.
• Drop-off = lesson-completion funnel (definition Bump hono from 4.11.8 to 4.12.21 #1), built on reliable lesson-progress data, covering all lesson types.

Surfaces

• New route /instructor/:courseId/analytics with its own loader, error boundary, and hydrate fallback — kept separate from the large course-editor route file. Loader calls analyticsService and enforces the existing auth rule (instructor owns the course, or admin; otherwise 403).
• The existing /instructor index loader is extended to compute and render a portfolio stats strip, scoped to the courses returned by getCoursesByInstructor.
• The course-editor header gains an "Analytics" link next to the existing "Students" link.

Presentation

• No charting library added. Distributions and the funnel are rendered with CSS/div-width bars, matching the progress-bar pattern already in the roster page.
• All courses included regardless of status; status badge shown for context.
• Explicit empty states: no enrollments → suppress completion/funnel with a message; no quizzes → hide the quiz section; no lessons → hide the funnel; $0 earnings → show "$0". Divide-by-zero guarded everywhere (return 0, never NaN).

Schema / API

• No schema changes. No new tables or columns. enrollments.completedAt is intentionally left untouched (wiring it up is a separate concern).
• No new HTTP/API endpoints; data flows through React Router loaders.

Testing Decisions

• What makes a good test here: tests assert the externally observable behavior of analyticsService — given a known set of seeded enrollments, purchases, lesson progress, and quiz attempts, the functions return the correct aggregate numbers. Tests should not assert on internal query structure or implementation details, only on returned values.
• Module under test: analyticsService only. Routes/loaders are thin wrappers and are not tested, matching the project's existing convention of service-only tests.
• Prior art: the colocated *.test.ts files already present for every service (e.g. enrollmentService.test.ts, progressService.test.ts, purchaseService.test.ts) using vitest. The new analyticsService.test.ts follows the same setup/seeding style.
• Emphasis on edge cases: zero enrollments, zero quiz attempts, divide-by-zero guards, free / $0 courses, best-attempt deduplication in the quiz distribution, lesson ordering across multiple modules for the funnel, and the last-30-days windowing.

Out of Scope

• Time-series / trend charts and any charting dependency.
• Video-level drop-off analysis using videoWatchEvents (deferred to a future enhancement; v1 uses the lesson-completion funnel).
• Net revenue, platform fees, payouts, and refund accounting (no data model exists).
• Wiring up enrollments.completedAt / automatically marking enrollments complete.
• Route/loader/component tests.
• Exporting analytics (CSV/PDF) or emailing reports.

Further Notes

• A pre-existing inconsistency was noted: scoring code (getScore / computeResult) passes quizzes at a hardcoded score > 0.7, while quizzes.passingScore is a configurable per-quiz field that the scoring code ignores. This PRD sidesteps it by using the stored passed boolean for pass-rate reporting rather than recomputing.
• The existing getQuizStats(quizId) already returns total attempts / avg / high / low / pass rate over all attempts; the new distribution function differs intentionally (best-attempt-per-student, A–F bucketed) and is the better basis for instructor-facing reporting.
• Coding standards live in the coding-standards skill (.claude/skills/coding-standards/); the relevant topics for this work are schema/queries, routing, validation, auth, testing, and TypeScript conventions (object params, no any, prices in cents).