Case study · Healthcare / aesthetic clinic
Revenue analytics from accounting data
For a Swiss aesthetic clinic. The clinic's accounting data (bexio) automated into BigQuery and mapped from ledger accounts to business meaning, so revenue is split by business unit, treatment category and treatment in one report.
bexio APIn8nBigQuerydbtLooker Studio
0:00 / 0:45
What the animation shows, scene by scene:
- Ledger: A ledger page of journal entries and invoices, organised by accounting logic, not by how the clinic runs.
- The question: Management asked for revenue per business unit, per treatment category and per treatment. That split lived in people's heads and manual exports.
- Pipeline: The bexio API flows through n8n into BigQuery, then through dbt staging into one revenue mart.
- Meaning: Every booking is classified by its ledger account into business unit, treatment category and treatment. Categories include operation, injections, laser, cosmetic, consultation, IV drips, skincare and thread lift.
- Report: Year-over-year bars per category and a month by year heatmap for planning surgeon capacity and campaigns.
- Result: Revenue by business unit, category and treatment, 2019 to 2026 in one model, and inquiry to quotation to revenue in one report.
Revenue 2019 → 2026 in one model
Every booking classified by business unit, category and treatment
Inquiry → quotation → revenue in one report with the CRM warehouse
Part of a data platform for the clinic: CRM, accounting (this page) and bookings.
The problem
- Revenue lived in bexio (Swiss accounting software) as journal entries and invoices, organised by accounting logic, not by how the clinic runs its business.
- Management wanted revenue per business unit, per treatment category and per treatment. That split didn’t exist in the accounting system; it lived in people’s heads and in manual exports.
What I delivered
- Ingestion (n8n): scheduled pulls from the bexio API (accounts, account groups, journal, invoices) into BigQuery raw tables.
- dbt models: staging models per source table → one revenue mart.
- Business logic in SQL: every booking is classified by its ledger account number and mapped to its business meaning: business unit, treatment category (operation, injections, laser, cosmetic treatments, consultation, IV drips, skincare, thread lift) and treatment. The chart of accounts became the clinic’s business model in data.
- Looker Studio sales section: overview with period comparison, sales per business unit, sales per treatment category (year over year), sales per treatment, and a month × year heatmap of average sales.
Results
- Revenue from 2019 → 2026 in one model, split by business unit: the three largest units carry 54%, 34% and 9.5% of it.
- Top treatments by revenue are visible at a glance: dermal filler, breast augmentation, liposuction, botox, breast lift, blepharoplasty and laser.
- Growth per category year over year, for surgery, injections and every other category.
- Seasonality: the month × year heatmap shows average sales per month and category, for planning surgeon capacity and campaigns.
- Combined with the CRM warehouse, the clinic sees the full path from inquiry → quotation → revenue in one report.