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;
  committedMonthlyInCents: number;  // FIT-287 presale: `scheduled` subs at their chargeable price
  committedFrom: string | null;     // `YYYY-MM-DD` (org tz) = opens_at while still ahead; null when open
}

• MRR sums the chargeable price (chargeableAmountInCents: negotiated > intro > list) / INTERVAL_MONTHS[interval] over active non-deleted subscriptions.
• INTERVAL_MONTHS = { weekly: 0.2308, monthly: 1, quarterly: 3, yearly: 12 }.
• committedMonthlyInCents is the same sum over scheduled subscriptions — sold, card on file, first charge waiting for opening day. Kept apart from MRR because nothing is billing yet; the overview’s revenue panel draws it as the projection from committedFrom. A presale gym otherwise reads “₪0 recurring” while holding every founding member’s card.
• Collected this month = net receipts per apps/api/src/payments/services/receipts.sql.ts, the single definition shared with the payments page: status IN ('completed','refunded'), refunds (type='refund') signed negative, metadata.tokenOnly rows (presale ₪0/₪1 card validations) excluded, windowed on COALESCE(completed_at, updated_at). Regression watch: the overview once summed status='completed' by created_at, which counted a ₪1,713 refund as August’s best month.
• Outstanding = sum of subscriptions.debt_amount_in_cents (status debt, non-deleted).
• Deltas compare to last month’s collected / MRR.

GET /analytics/revenue/trend?months=12

Returns RevenueTrendPoint[] — month-start bucket, label, and net cents received that month (same receipt rule as the summary; a month can be negative after a refund). Every month in the window is present, zero-padded.

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

Exactly months rows (YYYY-MM, org-local), each { joined, churned, active }:

• joined — joined_at in the month.
• churned — the membership ended in the month.
• active — snapshot at month-end: joined by then, not deleted, not ended.

“Ended” is COALESCE(end_date, CASE WHEN status='cancelled' THEN updated_at END). end_date is the column meant for it but no cancellation path writes it; the cancellation is the row’s last write, so updated_at is its date. Before this, churn was always 0 and a cancelled member stayed on the active line forever.

Regression watch: month-end bounds are 23:59:59.999 local. The tz-offset probe must carry the milliseconds, or the bound lands one second into the next month and generate_series emits a phantom future bucket (+0 joined for next month on the overview).

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