Case study · Healthcare / aesthetic clinic
Patient retention & cross-sell from a system with no API
For a Swiss aesthetic clinic. A booking system with no API brought into BigQuery through a Drive → AppSheet/Apps Script pipeline, modelled in dbt, showing which treatments bring patients back and how fast.
Google DriveAppSheetApps ScriptBigQuerydbtLooker Studio
0:00 / 0:45
What the animation shows, scene by scene:
- No API: The clinic's booking software has no API; the only way out is a CSV export.
- Questions: Do patients come back? After which treatment? How soon? Basic questions with no data to answer them.
- Pipeline: Staff drop the CSV in a Google Drive folder; AppSheet and Apps Script load it into BigQuery, and dbt builds a retention mart and a cross-sell mart. Nobody touches SQL.
- Retention: A timeline from first visit to 3, 6 and 12 months, with retention measured at each point for cosmetic treatments, botox, filler and laser.
- Due back: From the last visit and treatment, the typical time to come back tells the clinic when each patient is due, so reminders go out on time.
- Follow-up: Flows from surgery to cosmetic treatments, filler, laser and botox: which treatment leads to the next and how soon, tracked per treatment.
32% of patients return within 3 months, 55% within 12
Time to return per treatment, so reminders go out at the right moment
Surgery follow-up rate fell from ~42% to ~23%: a revenue opportunity
Part of a data platform for the clinic: CRM, accounting and bookings (this page).
The problem
- Bookings, patient profiles and payments live in the clinic’s booking software. No API: the only way out is a CSV export.
- The clinic couldn’t answer basic customer questions: do patients come back? After which treatment? Which treatment leads to the next one, and how soon?
What I delivered
- Ingestion without an API: staff drop the CSV export into a Google Drive folder; an AppSheet app with Apps Script loads it into BigQuery. No manual uploads into the warehouse, nobody touching SQL.
- dbt models: cleaned appointments → a retention mart (3-, 6- and 12-month retention per treatment category and year) and a cross-sell mart (first treatment → next treatment, with the days in between).
- Looker Studio customer section: retention overview, yearly comparison, up-selling and down-selling flows (Sankey), and an operations analysis.
Results
- Retention: 32% of patients return within 3 months, 46% within 6, 55% within 12.
- Retention per treatment (3 → 6 → 12 months):
- Cosmetic treatments 60% → 66% → 70%: the most loyal patients.
- Botox 6% → 36% → 53%: low at 3 months, high at 12. Patients return on the treatment cycle, so retention windows must match the treatment, not one company-wide number.
- Filler 13% → 24% → 35%, laser 16% → 21% → 26%.
- Time to return (average months until a returning patient’s next visit): cosmetic 1.9, laser 3.8, filler 5.3, botox 5.5. This tells the clinic when to send the reminder for each treatment.
- Up-selling paths with timing: cosmetic → botox 12% (avg 58 days), cosmetic → filler 10% (63 days), laser → botox 6% (45 days). Marketing gets a when, not only a what.
- Surgery patients: operations grew from 105 (2023) to 192 (2025), +83%. After surgery, 36% move to cosmetic treatments, 19% to filler, 18% to laser, 16% to botox. But the share of surgery patients who return for non-surgical treatments fell from ~42% (2022) to ~23% (2025): a clear follow-up opportunity.
- 12-month retention was stable at 58–60% (2022–2024). The 2025 figure is still incomplete, because late-2025 patients haven’t had 12 months yet.