Analytics — Data Model

Legacy analytics and TKN-24 basic projections are read-only over operational tables; TKN-24 adds no migration.

Tables read

Table	Used in
`subscriptions`	MRR, plan distribution, outstanding debt, at-risk members.
`payment_transactions`	Collected MTD, revenue trend.
`memberships`	Members summary, growth, activation, at-risk, coach overview.
`users`	At-risk sample, members activation (Clerk linkage).
`invitations`	Members activation (`invited` bucket).
`class_sessions`	Popular classes, class utilization.
`class_types`	Same.
`bookings`	Same.
`plans`	Plan distribution.
`workouts`, `workout_assignments`, `workout_results`, `personal_records`	Workouts summary.
`organizations`	Timezone normalization where required (currency, locale).
`leads`, `organization_leads`	Org-scoped recorded creation history; current unarchived work queue.
`lead_status_events`, `lead_activity_events`	v1 intake/status/contact evidence, not legacy reconstructed history.
`conversations`, `conversation_messages`	Actor-backed one-to-one sends without acceptance by cutoff; present-tense unread inbound for current work.

Existing summary computed fields

MRR

For every active non-deleted subscription:


mrr_contribution = subscription.price_in_cents / INTERVAL_MONTHS[subscription.interval]

INTERVAL_MONTHS:

Interval	Months
`weekly`	0.2308
`monthly`	1
`quarterly`	3
`yearly`	12

The constant lives in analytics.service.ts.

Member activation buckets

imported — users.clerk_id IS NULL AND no pending invitation.
invited — invitations.status = 'pending'.
activated — users.clerk_id IS NOT NULL.

activatedActive is the intersection of active membership ∧ Clerk-linked user, excluding import placeholders. This legacy discriminator is not the new attendance or cohort denominator.

Class utilization

bookings count / class_session.capacity averaged across sessions in the window. Sessions with capacity = null are excluded.

Trend buckets

Date-bucketed by date_trunc('month', x) for monthly trends. Labels are localized at render time on the client.

Basic report projections

Shared request/response contracts live in libs/shared/src/lib/schemas/analytics.schema.ts. Date windows use inclusive org-local dates, at most 366 days, converted to DST-correct midnight bounds and capped at now for observed data. Responses include timezone and generation time; cohort horizons are integers 1–12, default six.

Projection	Source and definition
Studio active/new members	Current `memberships.role='member'`; active count requires active/non-deleted. New count uses MTD `joined_at`, excludes deleted rows and invitation shells.
Studio weekly classes	`class_sessions` → `class_types` → `programs` for org ownership; published/non-deleted sessions across the full local Sunday–Saturday week.
Attendance/ranking	Ended published/non-deleted sessions in the start-date window. Non-deleted bookings with `confirmed`, `attended`, `no_show` form booked; attended/booked is the rate. Membership joins enforce org ownership, not current status/role, preserving historical visits of ended/deleted members and current staff.
Capacity	Sum each finite positive session capacity once, including empty sessions; `capacityBooked / capacity × 100` is weighted. Unlimited/null and non-positive capacity are excluded from that ratio; unlimited sessions remain separately counted. Overbooking is not capped to 100%; undefined rates are null. Current `externalSessionOccupancy` is not history.
Revenue / Studio MTD receipts	`payment_transactions` under canonical `receipts.sql.ts`: completed/refunded actual receipts, negative refunds, no `metadata.tokenOnly`, settlement at `COALESCE(completed_at, updated_at)`. Separate collected/refunded/net cents and actual methods per currency; no FX merging or MRR inference. Partial selected/current months have null MoM delta while exposing the prior full month’s net under the same method filter.
Member cohorts	Join-month `memberships.joined_at` denominator for trains member rows excluding invitation shells. Retained means not ended/deleted by observation end; deletion and `end_date` establish lifecycle end (no `updated_at` fallback). Shells nobody joined (`joined_at` null), cancelled rows with no `end_date` and departures that predate their import (`source='import' AND end_date <= created_at`) are left out. Empty/future cells are null and current observations partial.

Cohorts declare basis='membership_lifecycle': no suspension/reactivation or paid-subscription history is reconstructed. They are lifecycle estimates, not paid retention.

Indexes assumed

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)
workout_assignments(organization_id, scheduled_for)

Drops to these will produce visible latency regressions on the analytics tab.

CSV outputs

The shared GET /organizations/:orgId/analytics/export?tab=<tab> route retains these eight legacy CSV shapes and formatting:

Tab	Rows
`revenue-summary`	1 row (the summary object flattened; `packsSold` as `packsSoldCount`, `packsSoldAmountInCents`).
`revenue-trend`	N month rows `{ bucket, label, totalInCents }`.
`plan-distribution`	N plan rows `{ planId, planName, interval, activeSubscriptions, monthlyRevenueInCents }`.
`members-summary`	1 row.
`members-growth`	N month rows.
`popular-classes`	N rows.
`class-utilization`	`byClassType[]` rows.
`workouts-summary`	1 row.

New gated report tabs use the same filtered projections as the web workspace:

Tab	Projection
`attendance-report`	Attendance class rows.
`class-ranking`	Ranked class attendance rows.
`class-capacity`	Class capacity rows.
`revenue-report`	Currency totals.
`revenue-methods`	Currency/method rows.
`revenue-monthly`	Currency/month rows with comparison metadata.
`member-cohorts`	Cohort observation cells.

analyticsReportCsv supplies stable headers even when empty, UTF-8 BOM, CRLF, quote/comma/newline escaping, and formula-safe text. Numeric negative receipts stay numeric; null cells are empty. Legacy CSV formatting is unchanged.