Instructor Analytics Dashboard

[jacksang6]

Instructor Analytics Dashboard — PRD

Problem Statement

Instructors on the Cadence platform have no way to see how their courses are performing beyond raw enrollment counts on the course list page. They cannot answer basic business questions like "which course earns the most?", "are students actually finishing?", or "how many students have dropped off?" without manually checking each course's student roster one by one.

Solution

Add a dedicated analytics page at /instructor/analytics that shows instructors 6 key performance indicators (KPIs) across all their courses, plus a per-course breakdown table for comparison. The page is accessible via a new "Analytics" sidebar navigation item.

User Stories

1. As an instructor, I want to see my total revenue across all courses, so that I know how much I'm earning from the platform
2. As an instructor, I want to see my total number of students across all courses, so that I understand my overall reach
3. As an instructor, I want to see the average completion rate across all my courses, so that I can gauge whether students are actually finishing my content
4. As an instructor, I want to see the drop-off rate across all my courses, so that I can identify courses where students are disengaging
5. As an instructor, I want to see the average rating across all my courses, so that I know how students feel about my content quality
6. As an instructor, I want to see the quiz pass rate across all my courses, so that I can tell if my assessments are appropriately calibrated
7. As an instructor, I want to see each of these 6 metrics broken down per course in a table, so that I can compare performance across courses
8. As an instructor, I want the per-course table sorted by revenue descending, so that my most commercially important courses appear first
9. As an instructor, I want to click a course name in the table to jump to its editor, so that I can take action on underperforming courses without navigating manually
10. As an instructor, I want completion rate and drop-off rate columns to include mini progress bars, so that I can visually scan the table for problem courses
11. As an instructor, I want to access the analytics page from the sidebar, so that I can check my metrics from anywhere in the app
12. As an instructor, I want to see a "no courses" empty state if I haven't created any courses yet, so that the page isn't confusing
13. As an admin, I want to access the analytics page, so that I can view analytics for courses I instruct (same as instructor view)
14. As an unauthenticated user, I want to be redirected to log in, so that analytics data is protected
15. As a student, I want to be denied access to the analytics page, so that instructor data remains private

Implementation Decisions

New route: instructor.analytics.tsx

• URL: /instructor/analytics
• Loader: authenticates user, verifies Instructor or Admin role, calls analyticsService.getInstructorAnalytics(userId), returns aggregated + per-course data
• No action (read-only page)
• Error boundary: handles 401 (not logged in), 403 (not instructor/admin), and generic errors, consistent with existing instructor routes

New service: analyticsService.ts

Single function getInstructorAnalytics(instructorId) returns:

{
  totalRevenue: number,        // cents, SUM(purchases.pricePaid) for instructor's courses
  totalStudents: number,       // COUNT(DISTINCT enrollments.userId) across instructor's courses
  avgCompletionRate: number,   // 0-100, average of per-student completion percentages
  dropoffRate: number,         // 0-100, % of enrolled students who are "dropped off"
  avgRating: number,           // 0-5, rounded to 1 decimal, COALESCE to 0 if no reviews
  quizPassRate: number,        // 0-100, % of quiz attempts where passed=true
  courses: [{
    id: number,
    title: string,
    slug: string,
    status: CourseStatus,
    revenue: number,           // cents
    studentCount: number,
    completionRate: number,    // 0-100
    dropoffRate: number,       // 0-100
    avgRating: number,         // 0-5, 1 decimal
    quizPassRate: number,      // 0-100
  }]
}

Courses array sorted by revenue descending.

Drop-off rate definition

A student is considered "dropped off" if either:

• They have zero lessonProgress records with status = 'completed' for the course (never started), OR
• They have some completed lessons but their most recent lessonProgress.completedAt is more than 30 days ago (started but abandoned)

Drop-off rate = (dropped-off students / total enrolled students) × 100, rounded to nearest integer.

Completion rate calculation

Per-student completion = calculateProgress(userId, courseId, false, false) (reuses existing progressService function).

Average completion rate = mean of all per-student completion percentages, rounded to nearest integer.

Quiz pass rate calculation

Count all quizAttempts where the quiz belongs to a lesson in one of the instructor's courses. Pass rate = (attempts with passed = true / total attempts) × 100, rounded to nearest integer. Returns 0 if no attempts exist.

Revenue calculation

SUM(purchases.pricePaid) for all purchases where purchases.courseId is in the instructor's course IDs. Stored in cents (integer), displayed as dollars.

Rating calculation

COALESCE(ROUND(AVG(rating), 1), 0) across all courseReviews for the instructor's courses. Review count also included for context.

Sidebar navigation

Add a new NavItem entry to the navItems array in sidebar.tsx:

{
  label: "Analytics",
  to: "/instructor/analytics",
  icon: <BarChart3 className="size-4" />,  // from lucide-react
  roles: [UserRole.Instructor, UserRole.Admin],
}

Placed after the existing "My Courses" item.

Admin access

Admin users can access the page. The loader uses the current user's ID as instructorId, so an admin sees analytics for courses where they are the instructor (same logic as existing instructor routes). Full cross-instructor analytics is out of scope for this PRD.

UI layout

1. Breadcrumb: Home / Analytics
2. Page header: "Analytics" title + subtitle "Track your course performance"
3. KPI cards row: 6 cards in a responsive grid (3 columns on desktop, 2 on tablet, 1 on mobile). Each card shows: icon, label, value. No trends or sparklines, no trends.
4. Per-course table: Card wrapping a table with columns: Course (clickable link), Revenue, Students, Completion Rate (number + mini bar), Drop-off Rate (number + mini bar), Avg Rating, Quiz Pass Rate. Sorted by revenue descending.
5. Empty state: When instructor has no courses, show a centered message with icon and "Create your first course" CTA linking to /instructor/new.
6. HydrateFallback: Skeleton loading state matching the KPI cards + table layout.

Mini progress bar component

Reuse the existing progress bar pattern from instructor.$courseId.students.tsx — a small colored bar inside a muted track, with the percentage number beside it. Applied to completion rate and drop-off rate columns.

Price display

Revenue values stored in cents, displayed as formatted dollars using the existing formatPrice utility from app/lib/utils.ts.

No schema changes

All metrics are computed from existing tables (purchases, enrollments, lessonProgress, courseReviews, quizAttempts). No new tables or columns needed.

Testing Decisions

Test seam: analyticsService.ts

Single service function getInstructorAnalytics(instructorId) is the sole test seam. All 6 metrics are tested through this one function.

Test file: analyticsService.test.ts

Follows the existing project pattern:

• Uses vitest with describe/it/expect
• Uses createTestDb() for in-memory SQLite with real migrations
• Uses seedBaseData() for base user/instructor/category/course
• Mocks ~/db via vi.mock to inject test DB (same as all other service tests)
• Seeds additional data per test (purchases, enrollments, lesson progress, reviews, quiz attempts)

What makes a good test

• Test external behavior (metric values), not implementation details (SQL queries)
• Each metric gets its own describe block with multiple scenarios:
  • Zero data (no purchases, no enrollments, no reviews, no quiz attempts) → metric is 0
  • Single course with data → correct calculation
  • Multiple courses → correct aggregation
  • Edge cases: course with no lessons (completion = 0), student with no progress (drop-off), all students completed (drop-off = 0)
• Drop-off rate tests specifically cover: never-started students, recently-active students, stale students (>30 days), and mixed populations

Prior art

• enrollmentService.test.ts — same mock pattern, same seed helpers
• progressService.test.ts — same pattern, tests calculateProgress with various completion states
• purchaseService.test.ts — same pattern, tests purchase creation and retrieval

Route-level testing

Not included in this PRD. The route loader is a thin wrapper around the service function + auth checks. Service-level tests provide the coverage.

Out of Scope

• Time-based trends (month-over-month, weekly) — KPI cards show current snapshot only
• Charts or visualizations beyond mini progress bars
• Per-student drill-down from the analytics page (already available via student roster)
• Admin view of all instructors' data (admin sees only their own instructor courses)
• Filtering or date range selection on the analytics page
• Exporting analytics data (CSV, PDF)
• Email notifications for metric thresholds
• Course comparison or benchmarking against platform averages
• Video watch time analytics (data exists in videoWatchEvents but not included in this PRD)
• Revenue breakdown by country or PPP tier

Further Notes

• The quizScoringService.ts currently uses a raw better-sqlite3 connection (new Database("data.db")) for getQuizStats. The new analytics service should use the Drizzle db instance consistently, not raw SQL, to maintain the project convention and testability.
• The 30-day drop-off threshold is a constant that should be defined once in the analytics service and documented. It can be made configurable in a future iteration.
• The sidebar navItems array currently uses roles: [UserRole.Instructor] for "My Courses". The new "Analytics" item uses roles: [UserRole.Instructor, UserRole.Admin] to match the route's access policy. This is consistent with how admin routes are already handled.

[DaDucking]

Implemented in commit 42fa3ca on D1Grill branch.

What was done:

• Added getInstructorAnalytics(instructorId) service function computing 6 KPIs: total revenue, total students, avg completion rate, drop-off rate, avg rating, and quiz pass rate
• Updated instructor analytics route to allow Admin role access (alongside Instructor)
• Enhanced dashboard with 6 KPI cards and per-course table showing completion rate and drop-off rate with mini progress bars, quiz pass rate, and clickable course links to the editor
• Added 20+ comprehensive tests covering all metrics, edge cases, and instructor isolation

Key decisions:

• Drop-off defined as: 0 lessons completed OR last activity >30 days ago
• Quiz pass rate via quizAttempts→quizzes→lessons→modules JOIN chain
• Aggregate KPIs weighted by enrollment count
• New standalone function (not modifying existing interfaces) to avoid breaking admin analytics

[DaDucking]

Implemented in commit 42fa3ca on D1Grill branch.

What was done:

• Added getInstructorAnalytics(instructorId) service function computing 6 KPIs: total revenue, total students, avg completion rate, drop-off rate, avg rating, and quiz pass rate
• Updated instructor analytics route to allow Admin role access (alongside Instructor)
• Enhanced dashboard with 6 KPI cards and per-course table showing completion rate and drop-off rate with mini progress bars, quiz pass rate, and clickable course links to the editor
• Added 20+ comprehensive tests covering all metrics, edge cases, and instructor isolation

Key decisions:

• Drop-off defined as: 0 lessons completed OR last activity >30 days ago
• Quiz pass rate via quizAttempts→quizzes→lessons→modules JOIN chain
• Aggregate KPIs weighted by enrollment count
• New standalone function (not modifying existing interfaces) to avoid breaking admin analytics

Ready to close — all tests pass (1292/1292), typecheck clean.