Analytics — Behavior

Auth

Every endpoint calls requireOwnerOrAdmin(orgId, clerkId):

1. MembershipsService.requireMembership (must hold an active membership).
2. membership.role ∈ { owner, admin }, else 403 Only owners and admins can view analytics.

Revenue

GET /analytics/revenue/summary

Returns:

{
  mrrInCents: number;
  mrrDeltaPct: number | null;       // null when previous month had zero
  collectedThisMonthInCents: number;
  collectedDeltaPct: number | null;
  outstandingInCents: number;
  activeSubscriptions: number;
}

• MRR sums subscriptions.price_in_cents / INTERVAL_MONTHS[interval] over active non-deleted subscriptions.
• INTERVAL_MONTHS = { weekly: 0.2308, monthly: 1, quarterly: 3, yearly: 12 }.
• Collected this month = payment_transactions status = 'succeeded' for this org with created_at in the current month.
• Outstanding = sum of subscriptions.debt_amount_in_cents (active, non-deleted).
• Deltas compare to last month’s collected / MRR.

GET /analytics/revenue/trend?months=12

Returns RevenueTrendPoint[] — month-start bucket, label, and total cents collected.

GET /analytics/revenue/plan-distribution

Per-plan { planId, planName, interval, activeSubscriptions, monthlyRevenueInCents }.

Members

GET /analytics/members/summary

{
  total: number;
  active: number;
  activatedActive: number; // active AND has Clerk account — distinguishes real users from CSV imports
  newThisMonth: number;
  newDeltaPct: number | null;
  churnedThisMonth: number;
  churnedDeltaPct: number | null;
}

The activatedActive field is the engagement denominator most other surfaces should use — CSV-imported placeholders that have never logged in pollute “active” otherwise.

GET /analytics/members/activation

{ total, imported, invited, activated }:

• imported — no Clerk user AND no pending invitation (typically CSV).
• invited — pending invitations row.
• activated — has a Clerk account.

GET /analytics/members/growth?months=6

Monthly active-member counts over months months. Used by the line chart on the members tab.

GET /analytics/members/at-risk?sample=5

{ count, sample: AtRiskMember[] }. Past-due subscription members with a sample capped at sample.

Classes

GET /analytics/classes/popular?days=30&limit=10

Top class types by booking count over the past days.

GET /analytics/classes/utilization?days=30

Returns { overall, byClassType }. Capacity fill rate per class type plus the aggregate.

Workouts

GET /analytics/workouts/summary

{ assignedMtd, completedMtd, prsMtd, completionRate, ... }. Source: workout_assignments, workout_results, personal_records.

Coach overview

GET /analytics/coaches/overview

Per-coach: assigned athletes, recent activity, programming load. Drives the dashboard’s “coach health” view.

CSV export

GET /analytics/export?tab=<tab>

Synchronous. Allowed tab values:

• revenue-summary → 1-row CSV of the summary object.
• revenue-trend → trend points.
• plan-distribution → per-plan rows.
• members-summary → 1-row CSV.
• members-growth → trend points.
• popular-classes → top-N list.
• class-utilization → byClassType[] rows (filename: class-utilization-by-type-<date>.csv).
• workouts-summary → 1-row CSV.

tab outside the allowed set → 400.

CSV builder:

• Headers union of all row keys (in insertion order).
• Values JSON-stringified for objects.
• Cells with ", ,, newline are wrapped in double-quotes with " escaped.
• Content-Type: text/csv; charset=utf-8.
• Content-Disposition: attachment; filename="<tab>-<YYYY-MM-DD>.csv".

Spotter agent surface

apps/api/src/ai/agent/tools/leaves/analytics.tools.ts defines twelve analytics actions, each a thin wrapper over a service method:

• revenue_summary, revenue_trend, members_summary, members_growth, at_risk_members, popular_classes, class_utilization, coach_overview, org_insights (delegates to InsightsService), plan_distribution, members_activation, workouts_summary.

All marked sideEffects: read and confirm: never. Visible to staff roles only (owner | admin | coach). The coach role only sees the tools that make sense for coaches at the prompt layer; server-side role gates run inside each underlying service.

Performance

• All queries are point-in-time SQL aggregations.
• Index dependencies: subscriptions(membership_id, status, deleted_at), payment_transactions(organization_id, created_at, status), memberships(organization_id, status, role), class_sessions(organization_id, starts_at), bookings(class_session_id, status).
• No caching layer. Acceptable today on orgs <5k members.

Failure modes