Problem Statement
Instructors on Cadence currently have no insight into how their students are engaging with their courses beyond raw enrollment counts and a per-student progress view on the student roster page. They cannot answer basic questions like: Are students completing my course? Which lessons lose the most students? Who is struggling with quizzes? Is my revenue growing? This blind spot prevents instructors from improving their course content and identifying at-risk students early.
Solution
A new analytics dashboard at the course level that surfaces four key metrics: course completion rate, lesson-by-lesson drop-off (with video engagement), students at risk due to low quiz scores, and monthly revenue trend. Each metric is computed from existing data — no new tracking or schema changes.
User Stories
-
As an instructor, I want to see the overall completion rate for my course (average student progress, percentage completed, never-started count), so that I can gauge how well students are progressing through my content.
-
As an instructor, I want to see a lesson-by-lesson breakdown of what fraction of enrolled students have reached each lesson, so that I can identify where students drop off en masse.
-
As an instructor, I want to see the average max video watch position for lessons that contain video, so that I can identify specific video segments where students lose interest.
-
As an instructor, I want to see a table of students whose best quiz score across any quiz is below 60%, so that I can intervene with struggling students before they fall too far behind.
-
As an instructor, I want to see monthly revenue bars with a cumulative total for my course, so that I can track sales trends and evaluate the impact of pricing changes.
-
As an instructor, I want to see all four metrics on a single page, so that I can get a holistic picture of course health at a glance.
-
As an instructor, I want to navigate to analytics from the course editor page and from the instructor dashboard, so that I can quickly access analytics without hunting for them.
Implementation Decisions
-
New route at /instructor/:courseId/analytics — consistent with the existing per-course route pattern (/instructor/:courseId/students).
-
New service analyticsService — a single service encapsulating all four analytics queries. Keeps route files thin and follows the project convention of business logic in services.
-
No schema changes — all four metrics are computed from existing tables: enrollments, lesson_progress, lessons, modules, video_watch_events, quiz_attempts, purchases.
-
Recharts as the charting library for bar charts (lesson drop-off, revenue) and line charts (cumulative revenue). No chart library exists in the project today; Recharts is the recommended addition for its declarative React API and community adoption.
-
Page layout: summary cards (4 headline numbers) → completion section → lesson drop-off (bars + video overlay) → at-risk students table → revenue trend (monthly bars + cumulative line).
-
Navigation: link added in the course editor page (alongside the existing "Students" link) and a direct "Analytics" button on dashboard course cards.
-
No new database columns or tables — all queries are aggregations of existing data.
Completion rate metrics:
- Average student progress: mean of
calculateProgress across all enrolled students.
- Completion percentage: fraction of enrolled students with non-null
enrollment.completed_at.
- Never-started count: students with 0% progress despite being enrolled past a grace period.
Lesson drop-off:
- For each lesson (ordered by module/position), compute what fraction of enrolled students have a
lesson_progress record with status completed.
- For lessons with video, compute the average max
position_seconds across all video_watch_events.
At-risk students:
- For each enrolled student, get their best
score from quiz_attempts across all quizzes in the course.
- Flag students where the best score is < 60%, regardless of the individual quiz's
passing_score.
- Also flag students who have not attempted any quiz yet.
Revenue trend:
- Group
purchases by month using created_at.
- Sum
price_paid per month (prices stored in cents, displayed formatted).
- Compute cumulative total alongside monthly bars.
Testing Decisions
-
Single test seam: analyticsService.test.ts — the analytics service is the only meaningful unit to test. The route is a thin pass-through wrapper and does not warrant its own test file.
-
What makes a good test: Tests should seed known data into the test DB, call each analytics function, and assert the shape and values of the returned aggregates. They should not mock internal query logic — only the DB is mocked via the existing createTestDb + seedBaseData pattern.
-
Prior art: progressService.test.ts and enrollmentService.test.ts follow the same pattern — beforeEach creates a fresh test DB + seed, helpers insert domain-specific data, and tests assert computed values against known state.
-
Coverage targets: each analytics function gets tests for basic expected values, empty/no-data cases, mixed states (some students progressed, some not), and edge cases (course with no enrollments, no quizzes, no purchases).
Out of Scope
- Instructor dashboard aggregate cards (cross-course totals) — deferred to a follow-up.
- Comparisons to other courses or benchmarks.
- Download/export of analytics data (CSV, PDF).
- Real-time or live-updating analytics.
- Historical cohort analysis (e.g., comparing this month's students to last month's).
- Push notifications or alerts for at-risk students.
- Lesson-level per-question analysis.
Further Notes
- The video engagement metric (average max watch position) is a secondary axis on the drop-off chart, not a standalone section. It only appears for lessons that have a non-null
video_url.
- Revenue is displayed using the existing
formatPrice() utility from ~/lib/utils.
- The service follows the project convention of positional parameters (object params for >1 same-type params) and uses
~/db/schema imports.
- If page performance degrades with many students, a materialized summary table can be introduced later — no premature optimization.
Problem Statement
Instructors on Cadence currently have no insight into how their students are engaging with their courses beyond raw enrollment counts and a per-student progress view on the student roster page. They cannot answer basic questions like: Are students completing my course? Which lessons lose the most students? Who is struggling with quizzes? Is my revenue growing? This blind spot prevents instructors from improving their course content and identifying at-risk students early.
Solution
A new analytics dashboard at the course level that surfaces four key metrics: course completion rate, lesson-by-lesson drop-off (with video engagement), students at risk due to low quiz scores, and monthly revenue trend. Each metric is computed from existing data — no new tracking or schema changes.
User Stories
As an instructor, I want to see the overall completion rate for my course (average student progress, percentage completed, never-started count), so that I can gauge how well students are progressing through my content.
As an instructor, I want to see a lesson-by-lesson breakdown of what fraction of enrolled students have reached each lesson, so that I can identify where students drop off en masse.
As an instructor, I want to see the average max video watch position for lessons that contain video, so that I can identify specific video segments where students lose interest.
As an instructor, I want to see a table of students whose best quiz score across any quiz is below 60%, so that I can intervene with struggling students before they fall too far behind.
As an instructor, I want to see monthly revenue bars with a cumulative total for my course, so that I can track sales trends and evaluate the impact of pricing changes.
As an instructor, I want to see all four metrics on a single page, so that I can get a holistic picture of course health at a glance.
As an instructor, I want to navigate to analytics from the course editor page and from the instructor dashboard, so that I can quickly access analytics without hunting for them.
Implementation Decisions
New route at
/instructor/:courseId/analytics— consistent with the existing per-course route pattern (/instructor/:courseId/students).New service
analyticsService— a single service encapsulating all four analytics queries. Keeps route files thin and follows the project convention of business logic in services.No schema changes — all four metrics are computed from existing tables:
enrollments,lesson_progress,lessons,modules,video_watch_events,quiz_attempts,purchases.Recharts as the charting library for bar charts (lesson drop-off, revenue) and line charts (cumulative revenue). No chart library exists in the project today; Recharts is the recommended addition for its declarative React API and community adoption.
Page layout: summary cards (4 headline numbers) → completion section → lesson drop-off (bars + video overlay) → at-risk students table → revenue trend (monthly bars + cumulative line).
Navigation: link added in the course editor page (alongside the existing "Students" link) and a direct "Analytics" button on dashboard course cards.
No new database columns or tables — all queries are aggregations of existing data.
Completion rate metrics:
calculateProgressacross all enrolled students.enrollment.completed_at.Lesson drop-off:
lesson_progressrecord with statuscompleted.position_secondsacross allvideo_watch_events.At-risk students:
scorefromquiz_attemptsacross all quizzes in the course.passing_score.Revenue trend:
purchasesby month usingcreated_at.price_paidper month (prices stored in cents, displayed formatted).Testing Decisions
Single test seam:
analyticsService.test.ts— the analytics service is the only meaningful unit to test. The route is a thin pass-through wrapper and does not warrant its own test file.What makes a good test: Tests should seed known data into the test DB, call each analytics function, and assert the shape and values of the returned aggregates. They should not mock internal query logic — only the DB is mocked via the existing
createTestDb+seedBaseDatapattern.Prior art:
progressService.test.tsandenrollmentService.test.tsfollow the same pattern —beforeEachcreates a fresh test DB + seed, helpers insert domain-specific data, and tests assert computed values against known state.Coverage targets: each analytics function gets tests for basic expected values, empty/no-data cases, mixed states (some students progressed, some not), and edge cases (course with no enrollments, no quizzes, no purchases).
Out of Scope
Further Notes
video_url.formatPrice()utility from~/lib/utils.~/db/schemaimports.