BelleHealth runs a dual-brand operation — a nationwide GLP-1 telehealth service (semaglutide, tirzepatide, NAD+) and a Sculpt body-contouring footprint across six physical clinics (Draper, OKC, Bountiful, Idaho Falls, Boise, Tulsa). Patient data lived in silos: intake forms on one platform, charges in Stripe and ChargeDesk, SMS journeys in another tool, consult bookings per location, refill tracking in spreadsheets. Every question that crossed systems — "what's our 60-day renewal rate on 3-month tirzepatide?", "what's cost-per-lead at Boise this week?", "who's due for a dosage check text?" — required a human to open five tabs and stitch the answer together.
They needed a single operational source of truth — an Airtable-native data platform that could unify patient records, orders, payments, and refills; power SMS drip sequences and renewal campaigns; and roll all of it up into executive KPIs split by brand and by clinic.
We built Airtable as the operational backbone — a purpose-built constellation of 11+ interconnected bases, each owning a slice of the business: a main patient/order hub, a drip-campaign engine, a renewals tracker, a Stripe checkout mirror, a Bask platform migration base, and executive dashboards for BMG-wide KPIs and BelleHealth weekly reporting.
Formulas do the heavy lifting: Refill Date computes from Completed At + plan duration − 21 days to trigger refill messaging automatically. Dose to Send (Auto) resolves the correct dosage from medication + price + plan. BMI Screen flags risk cohorts at intake. Rollups and lookups keep the patient 360° view live. Renewal rates are cohorted at 30/45/60/90-day windows. Multi-location clinic performance — sales, consults, leads, ad spend, revenue/consult, cost/consult, cost/lead — rolls up to a single company-wide Sales Conversion dashboard.
01 — Unified Patient & Order Hub
One patient, every order, every intake field, linked.
The main Belle Data Dashboard holds the master Patients table and the transactional Orders table, linked via multipleRecordLinks. Every order captures a full medical intake — 20+ screening fields including MTC, MEN2, pregnancy status, allergies, blood thinners, previous GLP-1 history, exercise activity, height/weight/BMI — alongside full UTM attribution. Rollups surface order counts, recent plan type, and last order date at the patient level.
Patients Master
Name, DOB, contact, status, linked orders, lifetime rollups
Orders Ledger
Medication, plan, amounts, dosage, status, refill flags
Medical Screening
20+ intake fields captured per order for clinical safety
UTM Attribution
Source, medium, campaign, term, content on every order
02 — Payment & Revenue Reconciliation
Stripe, ChargeDesk, and adjustments — reconciled in one view.
A ChargeDesk table mirrors every gateway transaction — charge ID, subscription ID, customer metadata, refund status, payment method brand. Stripe Payment Links powers the pre-paid refill infrastructure with full checkout metadata, including promo codes and order IDs carried through as metadata. A dedicated Order Price Adjustments table handles manual reconciliation exceptions, and a Logistics Issues table with AI-generated attachment summaries tracks shipping and fulfillment blockers.
ChargeDesk Mirror
Transactions, refunds, gateways, payment methods
Stripe Payment Links
Checkout sessions, invoices, metadata, promo codes
Price Adjustments
Manual reconciliation workflow with assignees & status
Logistics Issues
Shipping blockers, AI attachment summaries, assignees
03 — Refill & Renewal Automation Engine
Formulas calculate it. Automations send it. Patients refill.
The renewal engine is the commercial heart of the platform. A Refill Date formula computes 21 days before each plan lapses. A dedicated Renewals base and a Bask Adoption base track refill eligibility, SMS sequencing, and platform migration status — with an Outreach Eligible formula gating who gets contacted and an Outreach Batch formula sequencing sends. Dosage check text triggers and Follow-Up Message progressions handle the clinical side. Renewal rates are computed at 30/45/60/90-day windows for cohort analysis.
Refill Date Formula
Completed At + plan duration − 21 days, per order
Renewals Base
Cohort month, true refill date, SMS step, batch gating
Dosage Check Flow
Text triggers, sent dates, RN notes captured inline
Cohort Renewal Rates
30 / 45 / 60 / 90-day windows computed weekly
04 — Drip Campaigns & Lead Nurture
Every abandoned cart, every lead source, every SMS step.
The Drip Campaign base tracks abandoned carts and new lead sources with full intake context preserved, so nurture messaging can reference the actual cart the patient left behind. A 3-Day Sale Table supports promo-code-driven flash campaigns. Next-SMS-Date scheduling, Drip Status progression, and Message sequencing let marketing run multi-step journeys without touching the main operational base. Portal Announcement handles one-off segmented broadcasts to active patients by plan type or refill window.
New Lead Sources
Abandoned carts with full intake, drip status, next SMS
3-Day Sale Table
Promo-code flash campaigns with SMS status tracking
Portal Announcements
Segmented broadcasts by plan type and refill window
Lead-to-Order Conversion
daysToConvert, orderAmount, status for funnel analysis
05 — Multi-Location Executive Dashboard
Six clinics, two brands, one rollup.
The Executive Dashboard base carries per-location sales, consults, leads, and ad spend for Draper, OKC, Bountiful, Idaho Falls, Boise, and Tulsa — with formula fields computing Revenue/Consult, Cost/Consult, and Cost/Lead for each city. A BMG Dashboard combines Sculpt (body contouring) and BelleHealth (telehealth) revenue side-by-side against weekly goals. Company Sales Conversion surfaces ROAS, sales-to-goal, and consult-to-lead conversion — everything leadership needs to run the weekly L10 without a single spreadsheet.
Per-Location Sales
Revenue, ad spend, consults, leads for 6 cities weekly
Unit Economics
Revenue/Consult, Cost/Consult, Cost/Lead per location
BMG Combined View
Sculpt + Health revenue vs goal, gross margin tracked
ROAS & Goal Tracking
Company-wide conversion, goal deltas, weekly ROAS
06 — KPI Command Center (Rocks)
AOV, LTV, churn, renewals, support, AI — one weekly record.
The Rocks: KPIs base is the single-row-per-week board that drives EOS-style accountability. AOV, LTV, renewal rates across four cohort windows, first-response and resolution times (office vs off-hours), AI resolution rate (Beacon, Fin AI), human escalation percentage, affiliate revenue as a share of weekly total, and conversations-per-$10k of order value — all co-located with documented Metric Definitions for every KPI (definition, data source, owner, target). No leadership ambiguity, no KPI drift.
AOV & LTV
Weekly Final Amount ÷ Orders; LTV annualized from plan length
Support SLAs
First-response and resolution time, office vs off-hours
AI Deflection
AI Resolution %, Human Escalation %, Unsolved ratio
Metric Definitions
Every KPI documented — source, owner, target, notes