> For the complete documentation index, see [llms.txt](https://docs.kula.digital/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.kula.digital/whats-in-your-data/mindbody.md). # Mindbody — ontology map **Source ids:** `com.mindbody` (operational) and `com.mindbody.billing` (sales) · **Role:** booking + membership + point-of-sale system of record · **Access:** Public API v6, per-site Mindbody is the most complete single source we ingest for a studio: it knows the schedule, who attended, what they bought, and on what terms. Where it falls down is *membership structure* — there is no memberships endpoint, so a member's plan has to be inferred. Note the two source ids. Operational entities land under `com.mindbody`; sales land under `com.mindbody.billing`. A query filtering `source = 'com.mindbody'` on `commerce.sale` returns nothing. ## Coverage at a glance | MBO entity | Canonical home | Grain | History | Freshness | | -------------------- | --------------------------------------------------------- | --------------------------------------- | -------------------------------------------------------------------------------- | --------- | | `/site/locations` | `people.location` | one per site | full | each poll | | `/staff/staff` | `people.staff` | one per staff member | **\~12 months from the direct pull** (older instructors backfilled from classes) | each poll | | `/sale/services` | `commerce.plan` | one per pricing option | full | each poll | | `/site/categories` | `bookings.category` | one per class/service category | full | each poll | | `/sale/contracts` | `commerce.plan` | one per contract item | full, per location | each poll | | `/sale/products` | `commerce.product` | one per retail product | full | each poll | | `/client/clients` | `people.member` | one per client | full | each poll | | `/sale/sales` | `commerce.sale` (+ `commerce.payment`) | **one row per sale line**, not per sale | date-windowed | each poll | | `/class/classes` | `bookings.class_session` (+ staff/location stubs) | one per scheduled class | date-windowed | each poll | | `/class/classvisits` | `bookings.attendance` (+ plan stubs, member plan refresh) | one per visit | date-windowed, fetched per class | each poll | ## What we cannot get **Memberships as a vendor enrolment record.** MBO v6 has **no `/sale/memberships` endpoint** — it 404s on live sites. What a studio calls a "membership" is represented two ways, and we pull both: `services` (sellable pricing options) and `contracts` (recurring agreements). Both map into `commerce.plan`. There is no vendor object that says "this member holds this membership from this date to this date", so: * **Membership start/end dates, freezes and holds are not available.** There is no enrolment record to read them from. * **A member who never attends has no plan on file.** Nothing to infer from. What we *do* have instead is better than it sounds — see below. **A trustworthy member status *****from MBO*****.** MBO does not reliably tell us when a member lapses. We work around it by deriving the status from behaviour nightly — see [below](#mbos-member-status-is-not-trustworthy) — so `people.member.status` *is* dependable; MBO's own flag is not, and nothing recovers the moment a member actually stopped attending. **Status history before we started capturing it.** MBO exposes only the member's *current* status — there is no change log and no effective-dated history, so "when did she go inactive?" is unanswerable for anything that happened before Kula began synthesising the event itself. From that date forward it *is* answerable: every status, membership-type, plan and suspension change is captured in `people.member_status_event` (`list_member_status_changes`, or the `status_history` block on `get_member_context`). Each member also carries one `is_baseline = true` origin row recording what we first saw — that is an observation, not a change, so filter it out before counting. Establish the capture start before charting a series: ```sql SELECT min(detected_at) AS capture_started FROM people.member_status_event; ``` **Appointments / 1:1 sessions.** Not supported. `bookings.appointment` is empty for MBO studios — nothing about PT sessions, 1:1 bookings or appointment revenue can be answered from this source. **Door access / facility entry.** No MBO endpoint. `bookings.facility_entry` is empty. **An "all visits" feed.** There is no endpoint that lists visits directly. Visits are reached by listing classes in a window, then calling `classvisits` per class id. Consequence: **a visit is only ever ingested if its class was ingested first.** A gap in the class window is also a gap in attendance, and attendance for a period cannot be back-filled without re-pulling that period's classes. **Currency.** MBO carries no currency on sales — each site is single-currency. We stamp a configured default (`MBO_DEFAULT_CURRENCY`, AUD on the AU cell). If no currency is configured the ingestor **skips payment emission entirely** rather than write an invalid row, so `commerce.payment` can legitimately be empty even where `commerce.sale` is full. **Timezone on schedule times.** MBO returns naive datetimes with no zone. They are interpreted using the studio's configured IANA timezone. A studio with a missing or wrong timezone setting will have class times shifted. ## Identity and join keys | Thing | MBO id | Canonical | | --------- | --------------------------------- | ------------------------------------------- | | Client | `Id` (also `UniqueId`) | `people.member.source_external_id` | | Staff | `Id` | `people.staff.source_external_id` | | Location | `Id` | `people.location.source_external_id` | | Class | `Id` | `bookings.class_session.source_external_id` | | Visit | `Id`, with `ClassId` + `ClientId` | `bookings.attendance` | | Sale line | `{Sale.Id}:{SaleDetailId}` | `commerce.sale.source_external_id` | **MBO attendance carries a real client id**, so attendance joins cleanly to `people.member` — no name matching is involved. Visits also carry a rich embedded `Service` object, which is where the plan attribution on a visit comes from — keyed on `Service.ProductId`, not `Service.Id` (the latter is a per-purchase instance and would explode the plan table). ## MBO's member status is not trustworthy `people.member.status` comes straight from MBO's `Active` / `IsProspect` flags. **MBO does not reliably update it when a member lapses**, and re-checking every member against the MBO API is too expensive to do at studio scale. So the field says what MBO last claimed, not what is true. How wrong it gets: one live MBO org had **2,748 members marked `active` whose most recent class was in 2025 or earlier**, 808 of them not seen since 2024. Roughly half that member base was carrying a status that said otherwise — and, because the plan is stamped from the last visit and never cleared, a plan name to match. **Never treat `status = 'active'` as "currently a member" on MBO without checking behaviour.** Any count of active members, members-on-plan, or revenue-per-active-member reads high — often by a factor approaching two. ### Derive it from activity instead The reliable signal is data we already hold: a class visit (`bookings.attendance`) or a payment (`commerce.sale` under `com.mindbody.billing`). A member with neither in the last 30 days has effectively lapsed, whatever MBO says. ```sql -- Members MBO calls active, ranked by how long they've actually been gone SELECT m.source_external_id, m.plan_name, (SELECT max(a.occurred_at) FROM bookings.attendance_guarded a WHERE a.source = 'com.mindbody' AND a.member_id = m.source_external_id) AS last_visit, (SELECT max(s.occurred_at) FROM commerce.sale_guarded s WHERE s.source = 'com.mindbody.billing' AND s.member_id = m.source_external_id) AS last_sale FROM people.member m WHERE m.source = 'com.mindbody' AND m.status = 'active' ORDER BY last_visit NULLS FIRST LIMIT 100; ``` Two exclusions matter when you apply this. A member with `is_booking_suspended` is on a **deliberate hold**, not lapsed. And a member whose `member_since` is inside the window simply hasn't had time to attend yet. ### We correct it automatically, every night Kula does not leave `status` as MBO reports it. A nightly job re-derives it for every MBO org: > **A member marked `active` is switched to `inactive` when they have had no class visit AND no payment for 30 days.** Both conditions must hold — a member who is still being billed stays active even if they haven't attended, and a member who attends stays active even if nothing has been charged in the window. It also runs in reverse. **The moment real activity reappears — a visit or a payment inside the window — the member is switched back to `active`** and the field is handed back to MBO. A member who takes two months off and returns is corrected in both directions without anyone intervening. With one important qualification: **only a member MBO still calls `active` comes back as `active`.** If MBO has said something real in the meantime — `cancelled`, `suspended`, `prospect` — that verdict already won (the correction only ever suppresses a *stale* `active`), and handing the field back leaves MBO's value untouched. Someone who cancels their membership and then buys a single drop-in class stays `cancelled`; they are not resurrected as an active member by the visit. Two deliberate exclusions stop it doing harm: * **A member on a hold (`is_booking_suspended`) is never swept.** A deliberate pause is not a lapse. * **A member who joined inside the window is never swept.** Someone who signed up three weeks ago and hasn't booked yet is new, not lapsed. **This override outranks MBO.** The derived value is stored in `people.member.status_override` — a column the ingest projections do not own — and the MBO clients projection consults it, so a restate can no longer overwrite a derived `inactive` with MBO's stale `active`. Earlier versions of this correction were undone by the next nightly restate; that is fixed. A vendor value *other* than `active` still wins immediately. If MBO reports a member cancelled or suspended, that is positive evidence of a real change and it takes effect regardless of the override. ### Not every "active" member is a member — some are just enquiries After the correction runs, a cohort remains marked `active` with **no plan, no attendance and no payment**. These are not a data fault and not lapsed members: they are **people who registered interest and never converted** — a walk-in who gave their details, a web enquiry, someone who created an account and never booked. They stay `active` on purpose. The rule never sweeps a member whose `member_since` falls inside the window, because someone who joined three weeks ago and hasn't booked yet is new, not lapsed. Once they age past the window with still no activity, the next nightly run sweeps them like anyone else. MBO doesn't help distinguish them: its `IsProspect` flag is `false` on these records and `Active` is `true`, so they arrive looking exactly like paying members. The combination of *active + no plan + no activity* is what identifies them. ```sql -- Registered interest, never converted SELECT count(*) FILTER (WHERE member_since >= now() - interval '30 days') AS still_in_grace, count(*) FILTER (WHERE member_since < now() - interval '30 days') AS older_unconverted, count(*) AS total FROM people.member m WHERE m.source = 'com.mindbody' AND m.status = 'active' AND m.current_plan_id IS NULL AND NOT EXISTS (SELECT 1 FROM bookings.attendance a WHERE a.source = m.source AND a.member_id = m.source_external_id) AND NOT EXISTS (SELECT 1 FROM commerce.sale s WHERE s.member_id = m.source_external_id); ``` **Count them separately from members.** Folding them into an active-member number overstates the business; calling them churn overstates the problem. They are a lead list — and a useful one, because a studio that accumulates hundreds of unconverted enquiries has a conversion problem worth naming. `still_in_grace` is this month's crop; `older_unconverted` should be near zero once the nightly rule has been running, since it sweeps them on age. ### Reading it `status` is the corrected value, so ordinary queries and every tool (`list_at_risk_members` included) get the truthful answer with no special handling. When you need to know *which* answer you're looking at: ```sql SELECT status, -- the value in force status_override, -- non-null ⇒ derived, outranking MBO status_override_rule, -- which rule asserted it status_override_at -- when it last did FROM people.member WHERE source = 'com.mindbody' AND source_external_id = $1; ``` `status_override IS NULL` means you are seeing MBO's own claim. MBO's raw client payload is always retained in `source_extras` if you need to compare. **The rule refuses to run on untrustworthy data.** Reading "no activity" as "lapsed" is only valid while attendance is actually arriving — if the MBO connector broke, the same logic would mark a healthy studio's entire member base inactive 30 days later. So the job checks first that attendance is current (something within 7 days) and deeper than the window, and **skips the org entirely** otherwise. If a studio's statuses look stale, check `get_system_status` — a skipped org is usually a broken connector, not a broken rule. Three things worth saying out loud when you report on this: * **The 30-day window is a judgement call, not a fact.** If a studio defines lapsed differently, say which window produced your numbers. * **A corrected count will be lower than MBO's own dashboard**, sometimes dramatically. That's the point — but an operator comparing the two deserves to be told why they differ rather than left to assume one is broken. ### The most valuable question this raises An "active" member who hasn't attended in months but **is still being billed** is live revenue at acute churn risk. That's a very different finding from a stale record nobody cleaned up, and the two are trivial to tell apart: ```sql -- Of the long-absent "active" members, how many are still paying? SELECT count(*) FILTER (WHERE EXISTS ( SELECT 1 FROM commerce.sale_guarded s WHERE s.source = 'com.mindbody.billing' AND s.member_id = m.source_external_id AND s.occurred_at >= now() - interval '90 days')) AS still_billed, count(*) AS long_absent_active FROM people.member m WHERE m.source = 'com.mindbody' AND m.status = 'active' AND NOT EXISTS (SELECT 1 FROM bookings.attendance a WHERE a.source = 'com.mindbody' AND a.member_id = m.source_external_id AND a.occurred_at >= now() - interval '180 days'); ``` Note that `list_at_risk_members` caps its look-back at 365 days, so members absent longer than that fall outside it entirely. For the long tail, this SQL is the right instrument. ## Every visit records the plan it was drawn against This is MBO's compensation for having no enrolment record, and it is worth more than a static membership field. Each visit names the pricing option the member consumed, and that lands in **two** places: * **`bookings.attendance.plan_source_external_id`** — a loose FK to `commerce.plan`, on every single attendance row. * **`people.member.current_plan_id` / `plan_name`** — refreshed from the member's most recent visit, applied behind a `plan_as_of` watermark so out-of-order restates converge on their latest *visit* rather than the latest vendor edit. So two things are answerable that a plain membership field could not answer: 1. **A member's current plan**, kept live as they attend. 2. **Their plan history** — because the plan is stamped per visit, you can see exactly when someone moved from an intro pack to a membership, or upgraded mid-cycle. Their *effective* plan timeline is reconstructable from attendance even though MBO exposes no enrolment dates. Two caveats to state when you use it: * It is the plan **as at each visit**, not a vendor-asserted enrolment. A member who stopped attending carries the plan they last attended on — read `status` for whether they're current. * `plan_source_external_id` is **mutable**. Operators reclassify which plan covered a class when someone upgrades mid-cycle or a wrong assignment is corrected, so a snapshot of this column can change retroactively. Read `bookings.attendance.updated_at` if you need to detect that. ## Counting traps **Sales are per line, not per sale.** One MBO sale with three purchased items becomes three `commerce.sale` rows. Counting rows counts line items. Revenue is correct because each line carries its own total; **transaction count is not** — count `DISTINCT` on the sale id portion of `source_external_id`. **A contract sale can split across rows.** Reconcile via `source` / `source_external_id` rather than assuming one row per agreement. A sale line with `item_type = 'contract'` should be looked up in `commerce.plan` for its full terms. **`classpass` does not mean ClassPass.** On MBO, `commerce.plan.category = 'classpass'` — and the `membership_type` derived from it — means the pricing option has a **session count**: a 10-pack, a 20-pack, a class pass. Unlimited options get `'membership'`. It says nothing about the ClassPass aggregator. The two are easy to confuse and the mistake is expensive. One live org shows 7,898 `classpass` against 1,592 `membership` — that is a studio whose members mostly buy packs, **not** a studio overrun by an aggregator. Real aggregator revenue is identified by the [ClassPass export](/whats-in-your-data/classpass.md) and by sale provenance, never by this field. It also has a quiet consequence: `list_at_risk_members` filters `membership_type IN ('membership','intro')`, so pack members are excluded from the at-risk board by design. In a pack-heavy studio that removes most of the member base — worth saying explicitly rather than presenting a short list as the whole picture. **Aggregator flows arrive here.** ClassPass and MBO-Online bookings land as ordinary MBO sales and visits. They should be tagged and reported separately from headline studio revenue — a ClassPass visit is worth a fraction of a direct booking, and the true payout only appears if the studio also uploads their [ClassPass export](/whats-in-your-data/classpass.md). **Attendance keeps multiple rows per (member, session) on purpose.** The lifecycle — booked, cancelled, re-booked, attended — is signal we want. Fix double counting at the **read** layer by taking the latest status, never by de-duplicating at ingest. **Instructors older than \~12 months come from class records, not the staff pull.** Every class embeds its full instructor object, so we emit fill-only staff rows from classes to backfill them. Those rows are sparser than the direct pull. Don't read a thin staff row as "this instructor left". **`SignedIn: true` means attended.** MBO's `AppointmentStatus` field is unreliable for class visits; the visit's own signed-in/no-show flags are the truth. This is already handled at transform, but matters if you're reading `ingest.raw_record` directly. ## Questions this source can and can't answer **Can answer well** * Full class schedule, capacity, utilisation, instructor per class * Who attended, who no-showed, who late-cancelled — with real member ids * Revenue by line item, by product, by pricing option, by date * Retail vs service revenue split * What pricing options and contracts exist, at what price and interval * Retention and at-risk analysis on real attendance history * **Which plan a member is currently on**, and **when they changed plans** — from the plan stamped on each visit * **Who is genuinely still a member** — `status` is corrected nightly from activity rather than trusting MBO's flag * **Who registered interest and never converted** — active, no plan, no activity; a lead list, not a member count **Cannot answer from MBO alone** * **The moment a member actually lapsed** *(MBO never reports it; our nightly rule detects it 30 days after their last visit or payment, so the date is a detection date, not a cancellation date)* * The *contractual* start or end date of a membership *(no enrolment record — you can see when they started attending on a plan, which is not the same thing)* * Membership freeze/hold periods *(same)* * A plan for a member who has never attended *(nothing to infer from)* * 1:1 appointments and PT sessions *(not supported)* * Door access / walk-ins *(no API)* * Current plan for a member who hasn't attended recently *(inferred from last visit)* * True ClassPass payout *(MBO records the booking; the payout is in the ClassPass export)* ## Recipes — what works well MBO is the most tool-friendly source we have: real member ids on attendance, real money, real categories. Almost every purpose-built tool works properly here. ### Who's lapsing → `list_at_risk_members` Buckets active members by days since last visit, pause-aware, excluding drop-in and ClassPass members. On MBO this is high quality because attendance carries a real client id. Don't hand-roll it, and don't use `list_quadrant_members` — quadrants are graph enrichment, not recency. ### Schedule performance → `get_class_utilisation` → `get_time_slot_detail` The day-of-week × hour heatmap, then the per-class drill-down for a chosen weekday and hour window. Both read the guarded views, exclude cancelled and zero-capacity sessions, and bucket in studio-local time — which matters on MBO, whose datetimes are naive and interpreted with the studio timezone. ### Instructor performance → `get_teacher_performance` Pass `staff_ref` (name) and `include_summary: true`. Set `include_subs` when you want sessions where they assisted, not just led. Remember instructors older than \~12 months arrive as sparse rows backfilled from class records — a thin staff row is not evidence someone left. ### Revenue → SQL on `commerce.sale`, minding the grain and the source Sales land under `com.mindbody.billing`, **not** `com.mindbody`, and one row is one **line**, not one sale: ```sql -- Revenue and true transaction count by month SELECT date_trunc('month', occurred_at) AS month, sum(total) AS revenue, count(*) AS line_items, count(DISTINCT split_part(source_external_id, ':', 1)) AS transactions FROM commerce.sale_guarded WHERE source = 'com.mindbody.billing' GROUP BY 1 ORDER BY 1; ``` Net revenue subtracts `commerce.refund` — refunds are their own rows, never negative sales. ### Split aggregator revenue out ClassPass and MBO-Online flows arrive as ordinary MBO sales. Report them separately from headline membership revenue, and if the studio also uploads their [ClassPass export](/whats-in-your-data/classpass.md), the true payout is there rather than in MBO. ### A member's plan history → SQL on `bookings.attendance` The plan is stamped per visit, so plan changes are a `DISTINCT ON` away. This is the closest thing MBO gives to a membership timeline: ```sql -- When did this member change plans? One row per plan spell. WITH spells AS ( SELECT a.occurred_at, a.plan_source_external_id AS plan_id, lag(a.plan_source_external_id) OVER (ORDER BY a.occurred_at) AS prev_plan_id FROM bookings.attendance_guarded a WHERE a.source = 'com.mindbody' AND a.member_id = $1 AND NULLIF(a.plan_source_external_id, '') IS NOT NULL ) SELECT s.occurred_at AS changed_at, p.name AS moved_to FROM spells s LEFT JOIN commerce.plan p ON p.source = 'com.mindbody' AND p.source_external_id = s.plan_id WHERE s.prev_plan_id IS DISTINCT FROM s.plan_id ORDER BY s.occurred_at; ``` ```sql -- Plan mix across the member base, from the live plan on each member SELECT COALESCE(p.name, m.current_plan_id, '(none on file)') AS plan, count(*) AS members FROM people.member m LEFT JOIN commerce.plan p ON p.source = 'com.mindbody' AND p.source_external_id = m.current_plan_id WHERE m.source = 'com.mindbody' GROUP BY 1 ORDER BY 2 DESC; ``` Say "the plan they attended on" rather than "their membership" — it's an effective plan, not a contractual one, and members who stopped attending carry their last one. ### One member's money → `get_member_payments` Combines `commerce.sale` with any standalone gateway payments not linked to a sale, with a `record_kind` column discriminating the two. Better than querying either table alone. ### Retention → `get_retention_curve` Cohort × period survival for members on a recurring membership. Bucket by `signup_month` (default), `plan`, or `location`. Class-packs, drop-ins, intros and comps are excluded by design — on MBO that exclusion is meaningful, because pricing options and contracts are mixed in `commerce.plan`. ### Acquisition cost → `get_cac_by_cohort` Works if the studio also has Meta or GA4 connected for spend. The cohort denominator is members whose **first attended class** was in that month — which MBO supports properly. ### Is the data current → `get_system_status` Reports each connected source as current / stale / failing with a plain-language "current as of" date. Check it before concluding anything is missing. ### What doesn't work on MBO `bookings.appointment` and `bookings.facility_entry` are empty — appointments aren't supported, and there's no access API. Membership start/end/hold dates don't exist, so anything phrased as "when did their membership begin" has to be answered from their first sale or first visit instead, with that substitution stated. ## Where this lives in the code | Concern | Path | | ------------------------------------------------------------------- | ---------------------------------------------------------------- | | Entity catalogue, endpoints, paging modes | `services/ingestors/mbo/internal/mboclient/specs.go` | | Fan-out transforms (classes, visits, sales, contracts) | `services/ingestors/mbo/internal/transform/` | | 1:1 SQL projections (clients, staff, locations, products, services) | `services/intelligence/internal/ingest/project/templates_mbo.go` | | Operator-facing connect guide | [Mindbody connector](/your-data-sources/mindbody.md) |