Analytics — Behavior

Auth

All routes are authenticated beneath /organizations/:orgId/analytics. The controller calls requireAnalyticsReader: MembershipsService.requireMembership, then PermissionsService.enforce(orgId, role, 'analytics', 'view'). Owner/admin may read; coach/member and non-members are denied.

Definitions

One vocabulary across analytics, the payments dashboard (and its platform-admin copy), the insights, the members screen and the agent tools. A noun means the same set of people, or the same sum of money, wherever it is printed. guardian_only seats are excluded from every row. Member predicates live in apps/api/src/memberships/member-status-predicates.ts (alias-aware: pass MEMBERSHIPS_M for raw SQL written FROM memberships m); money predicates in apps/api/src/payments/services/revenue-metrics.sql.ts.

Recurring plans only, likewise: plan distribution, presale committed monthly (getPresaleCommitment, committedMonthlyInCents) and the payments recurring-charges (standing orders) list.

Analytics CSV exports carry no per-metric definition column; this table and the UI help text are the definitions.

Basic reports (TKN-24)

These are separate projections in AnalyticsReportsService inside AnalyticsModule. Existing AnalyticsService summaries and eight legacy export tabs below are not replaced.

Queries

• Report data routes and report exports check membership and analytics:view, as every analytics route does; there is no per-organization availability gate.
• GET /attendance/report?from=YYYY-MM-DD&to=YYYY-MM-DD&classTypeId=<uuid> and GET /revenue/report?from=YYYY-MM-DD&to=YYYY-MM-DD&method=<payment-method> require from/to; the class/method filters are optional.
• Dates are inclusive org-local calendar dates, ordered from <= to, with at most 366 days. Bounds are local midnight through the next local midnight, DST-aware and capped at now for observed data. Invalid dates/ranges, class UUIDs, or methods return 400.
• GET /members/cohorts?months=6&asOf=YYYY-MM-DD defaults to six months through today. months must be an integer 1–12; optional asOf is capped at today. Invalid inputs return 400, not legacy permissive defaults.

Studio dashboard

GET /dashboard returns six KPIs with timezone, generation time, month, and explicit week bounds. Optional weekStartsOn=0|1 (default Sunday) names the week start; the web passes the same value its schedule uses:

1. Active members: trainingMemberCondition (definitions table; not the legacy activatedActive count).
2. Revenue MTD: actual completed/refunded receipts minus refunds, separately per currency; not recurring revenue or MRR.
3. Classes this week: published, non-deleted sessions in the full org-local week, including upcoming sessions.
4. Attendance MTD: attended/booked for ended, published, non-deleted sessions, using the attendance denominator below.
5. New members MTD: Joined (history) — joinedInWindowCondition — over the month to date (definitions table); previous.newMembers the same over last month’s equal span.
6. Active members with no attended booking this month.

previous repeats revenue, new members, attendance rate and members without a visit for the previous month up to the same day of month, so the web can show month-to-date change badges that compare equal spans.

Attendance, class ranking, and capacity

GET /attendance/report returns totals and class rows ordered by booked count descending, then name and ID. Only ended, published, non-deleted sessions starting within the selected window participate.

• Booked = non-deleted bookings with status confirmed, attended, or no_show; cancelled/waitlisted bookings are excluded. Confirmed bookings are unmarked; attendance rate is attended / booked × 100.
• Historical membership joins enforce organization ownership only. A member ending/deleting their membership or now holding a staff role does not erase a real visit.
• Finite positive session capacity is summed once per session, including empty sessions. Utilization is weighted capacityBooked / summedCapacity × 100, not an average of session percentages. Unlimited (null) and non-positive capacities are excluded from that ratio; unlimited sessions are counted separately. Overbooking may exceed 100%.
• Undefined rates are null, not zero. Current externalSessionOccupancy is not a historical attendance source.
• members names who came: activeMembers is the active members now (definitions table), visitors the distinct memberships with an attended booking in the window, withoutVisit the active members with none, and visitsPerActiveMember attended ÷ active members. The dashboard repeats withoutVisit for the month to date as membersWithoutVisit. daily lists booked and attended per org-local date already started in the window, zero-padded; slots lists attended bookings and sessions by org-local weekday and start hour for the busiest-hours grid.

Revenue reports

GET /revenue/report returns totals, actual payment-method breakdowns, and monthly trend rows, each separated by currency without conversion or cross-currency sums. It reuses canonical receipts.sql.ts: completed/refunded transactions, refunds signed negative, metadata.tokenOnly excluded, and settlement time COALESCE(completed_at, updated_at) rather than creation time.

Collected and refunded cents are shown separately; net cents may be negative. Monthly rows expose previousNetInCents for the prior full month under the same method filter. Partial selected/current months have partial=true and deltaPct=null; full-month deltas are also null when the prior net is non-positive.

Membership cohorts

Monthly cohorts use member joinedAt as their denominator, excluding invitation shells. Retained means not ended/deleted by the observation end. Lifecycle end is deletion or end_date, with no updated_at fallback. Trains members only; shells nobody joined (joined_at null), cancelled rows with no end_date (import-carried inactive members) and departures that predate their import (source='import' AND end_date <= created_at) are left out.

monthly gives joined, left and memberships alive at each month end over the cohort window (the last month ends at the observation day). summary sits on the same lifecycle basis over the cohort window: memberships alive at the observation day, joined, left, net change, mean tenure of those who left, and the share of members who joined 90 to 180 days before the observation day and were still alive 90 days after joining (retained90Pct over retained90Sample).

The response declares basis='membership_lifecycle': this is a lifecycle estimate, not paid retention. There is no suspension/reactivation or historical paid-subscription ledger. Empty/future cells have null retained/rate values; the current observation month is marked partial.

Web and report CSV

/[lang]/dashboard/analytics renders task-focused areas, not per-projection navigation tabs. TKN-24 supplies the first three areas and seven projections; Trials is the fourth:

Only the selected area’s report dataset is fetched; its tables share that result rather than fetching once per projection. The reports-only Studio snapshot has its own /dashboard request, and Attendance loads class-picker options separately. Shared notes, empty/error states, and generation metadata appear once per area.

The sidebar entry, the four areas, and the StudioKpiSnapshot inside reports require analytics:view. Unauthorized users make no report data requests and see the no-access state. OverviewV2 mounts neither the Studio snapshot nor a report link and does not initiate their requests; existing overview panels remain unchanged.

URL state and filters

The URL is the source of truth, including initial navigation, reload, and browser back/forward. A fully pinned example is:

/en/dashboard/analytics?tab=attendance&from=2026-09-01&to=2026-09-09&classTypeId=11111111-1111-4111-8111-111111111111&method=cash&months=6&asOf=2026-09-09

• tab selects an attendance, revenue, retention, or trials area. Missing or unknown areas normalize to Attendance.
• Attendance uses from, to, and optional classTypeId; Revenue uses from, to, and optional method; Retention uses months and asOf; Trials uses from and to (class dates). Other areas’ filters remain in the URL when switching, but are not sent to an unrelated dataset or export.
• Missing dates default to the organization’s current month start/today; months defaults to six and asOf to org-local today. router.replace pins these implicit defaults and the area into the URL without adding a history entry. This reproduces the selected period, not a frozen dataset.
• Area/filter edits use router.push; back/forward restores controls, selected area, queries, and matching CSV filters from current search parameters, not stale local state.
• The selected area’s shared schema validates the full relevant input. Invalid/inverted/overlong ranges, invalid class UUIDs or payment methods, and invalid cohort inputs stay in the URL with a localized error; no selected-area dataset request or usable export is issued until corrected. This guard does not suppress the independent snapshot or class-options request. A valid historical class UUID absent from current picker options remains a usable filter and is explicitly labeled rather than reset to all classes.

English, Hebrew/RTL, and Russian labels and formatting are supported. Loading/error states never relabel cached previous-filter results as current. Each projection exports the same filtered dataset through the existing /export endpoint, retaining all seven identifiers above. Report CSV uses stable headers even for empty data, UTF-8 BOM, CRLF, proper quote/comma/newline escaping, and formula protection for dangerous text cells. Numeric negative money stays numeric; null values are empty cells. The eight legacy identifiers and their formatting below are unchanged.

Accounting audit deferrals

Report organization and action routing do not change accounting semantics. Canonical receipts.sql.ts was already the source for report revenue and Studio receipts: actual money movement, signed refunds, token-only exclusion, and settlement timestamp. Sharing those expressions does not make all money APIs interchangeable: currency shape, time windows, price basis, and historical reconstruction still differ.

Existing money APIs, agent summary tools, and legacy exports remain available with their current contracts. None of these deferred concerns is claimed fixed by basic reports.

Summary 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;
  packsSold: { count: number; amountInCents: 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 and scheduled revenue exists; null otherwise
}

• MRR sums the chargeable price (chargeableAmountInCents: negotiated > intro > list) / INTERVAL_MONTHS[interval] over active non-deleted subscriptions on recurring plans (RECURRING_PLAN, plans.type = 'subscription'). The prior-month MRR behind the delta uses the same plan filter.
• INTERVAL_MONTHS = { weekly: 0.2308, monthly: 1, quarterly: 3, semi_annual: 6, yearly: 12 }.
• committedMonthlyInCents is the same sum over scheduled recurring 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. committedFrom is present only while the opening/first-charge day is ahead and the scheduled total is positive. 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.
• packsSold = Packs sold this month (definitions table), a slice of collected; the overview’s revenue panel names it under the collected headline.
• Outstanding and active subscriptions: definitions table.
• 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 } over active subscriptions on recurring plans only; a punch card, drop-in or course never appears. activeSubscriptions counts subscriptions, not members. monthlyRevenueInCents normalizes plan list price × active subscription count, not each subscription’s chargeable MRR; that distinction is a deferred audit concern above.

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;
}

active and activatedActive read trainingMemberCondition, in one pass (COUNT(*) and COUNT(*) FILTER (WHERE clerk_id IS NOT NULL)); newThisMonth reads newMemberCondition; newDeltaPct compares it with last month’s joinedInWindowCondition (Joined (history)), so a month’s baseline does not shrink as its joiners leave. Example: September 10 joined and 3 of them have since cancelled, October 8 joined → -20%, not +14.3%. churnedThisMonth is Left. total is every roster row (member seats that train, not deleted, any status). activatedActive is the legacy summary’s engagement discriminator. It is not the denominator for TKN-24 booked attendance or membership lifecycle cohorts.

GET /analytics/members/activation

{ total, imported, invited, lapsed, activated } over the live roster (member seats that train, not deleted, membership not cancelled; managed members excluded):

• imported — no Clerk user AND no pending invitation (typically CSV).
• invited — pending invitations row.
• lapsed — every invitation expired or was revoked (an invitation lapse, unrelated to the Lapsed churn metric).
• activated — has a Clerk account.

GET /analytics/members/growth?months=6

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

• joined — Joined (history) by month (definitions table).
• churned — Left by month (definitions table): status='cancelled', end_date in the month, joined_at not null, trains, deleted records included, and not source='import' AND end_date <= created_at.
• active — the historical active member at month-end (definitions table).

end_date is written by every cancellation path and cleared by every re-activation; MEMBERSHIP_ENDED_AT = end_date.

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, totalDebtInCents, sample: AtRiskMember[] }. count is the at-risk set (definitions table), the same members ?paymentStatus=at_risk lists and members.at_risk_billing counts. sample is capped at sample, ordered by the member’s own outstanding balance then name; each row carries reasons (past_due, debt, or has_debt_amount when only a balance flags them). totalDebtInCents is the org’s Outstanding, not the sum of the sample: it includes a former member who still owes.

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.

Legacy CSV export

GET /analytics/export?tab=<tab>

The eight existing synchronous tabs (new report tabs are described above):

• revenue-summary → 1-row CSV of the summary object; packsSold becomes two columns in its place, packsSoldCount and packsSoldAmountInCents.
• 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.

Legacy CSV builder (not the new analyticsReportCsv helper):

• 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.

Their summary strings use the nouns of the definitions table: members_summary returns counts, at_risk_members is billing-based, plan_distribution counts recurring subscriptions, revenue_summary lists exactly the fields the summary returns (no refund totals).

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).
• The member predicates correlate every subquery on the membership id alone (a subscription’s organization is reached through its own membership row) and keep the active member’s subqueries at the top level, so Postgres plans them as semi- and anti-joins. A subquery that also read m.organization_id, or sat under an OR, ran as a SubPlan per membership row; its estimated cost crossed jit_above_cost (100k) at about a thousand members and every dashboard read paid JIT compilation. active-member-plan.int.spec.ts keeps the members-summary active count below that threshold on a 2,500-member org.
• Measured on a synthetic org (PG 16, default JIT; ~1.3 subscriptions per member, ledger rows, two bookings per member): the members-summary active count costs ~2k at 5k members and runs in ~50 ms at 20k (was 287k / 1.1M cost, ~600 ms with JIT); the attendance people query ~65 ms at 20k (was ~1.3 s); the space-stats presence join ~80 ms (was ~1.2 s). Attendance computes active and without-visit in one scan; space-stats materializes the active set once before joining bookings.
• No caching layer; no production-scale latency claim follows from these local measurements.

Failure modes

Managed members

Member counts include only memberships.participation = 'trains', so guardian_only adults (members only to manage a child) are excluded everywhere (rosterMemberCondition, trainingMemberCondition, atRiskMemberCondition, newMemberCondition, joinedInWindowCondition in apps/api/src/memberships/member-status-predicates.ts; sumOutstanding in apps/api/src/payments/services/revenue-metrics.sql.ts; reports lifecycle). Children who train are counted. Ungated. Spec: managed-members/behavior.md §4.1.