Case study · Healthcare / aesthetic clinic
CRM sales warehouse: Pipedrive → BigQuery
For a Swiss aesthetic clinic. An automated, incremental and deduplicated Pipedrive → BigQuery pipeline, modelled in dbt, with a Looker Studio sales report covering inquiries, quotations and conversion per treatment and customer type.
Pipedrive APIn8nBigQuerydbtLooker Studio
0:00 / 0:45
What the animation shows, scene by scene:
- Manual exports: A CRM box feeds spreadsheet after spreadsheet: every sales report started with a manual export.
- Messy deals: Custom fields stored as IDs, stages changing over time, and the same deal exported twice, which broke the reports.
- Daily sync: The Pipedrive API flows through n8n into BigQuery every day. A sync_state bookmark means each run only fetches deals changed since the last one.
- One version: Append-only deal history becomes one latest version per deal in dbt, with field IDs translated into treatment, zone, surgeon and customer type.
- Sales report: A Looker Studio report on inquiries and quotations: week, month and year over year, conversion rate, filters for date, surgeon and treatment.
- Result: Sales history from Oct 2020 to 2026 in one place: about 4.8K inquiries and 1.5K quotations, no more manual exports.
6 years of sales history in one place (Oct 2020 → 2026)
~4.8K inquiries and ~1.5K quotations, no more manual exports
Existing patients convert at 54.0% vs 45.2% for new ones
Part of a data platform for the clinic: CRM (this page), accounting and bookings. Together they show the path from inquiry to quotation to revenue to returning patient.
The problem
- All sales data lived in Pipedrive. Reporting meant manual exports to spreadsheets.
- Inconsistent deal data: custom fields stored as IDs, stages and options changing over time.
- Duplicate records when the same deal was exported more than once, which broke the reporting logic.
- No view of which treatments or customer types actually converted, or how demand moved week to week.
What I delivered
- Ingestion (n8n): a daily scheduled workflow calls the Pipedrive API with pagination and pulls deals, deal fields, field options and stages into BigQuery.
- Incremental sync with a bookmark: a
sync_statetable stores the last sync point. Each run only fetches deals changed since then, appends them toraw_dealsand logs the row count. - Warehouse (BigQuery): raw tables kept as an append-only history.
- Deduplication and modelling (dbt):
latestmodels keep one current version per deal, field and option. Custom field IDs are translated into readable labels (treatment, zone, surgeon, customer type). - Looker Studio sales report: inquiries (overview, week over week, month over month, year over year), quotations (overview, conversion rate, month over month) and customer analysis. Every page has a side-by-side period comparison and filters for date, surgeon and treatment.
Results
- 6 years of sales history in one place (Oct 2020 → 2026): ~4.8K inquiries and ~1.5K quotations, with no more manual exports.
- Conversion visible per segment for the first time:
- Inquiry conversion 51.1%, quotation win rate 51.9%.
- Existing patients convert better than new ones: 54.0% vs 45.2%, while 81% of quotations go to new patients.
- Conversion by treatment ranges from ~44% to 100%.
- Demand by treatment and zone: 45% of inquiries are for the face.
- Trend tracking: weekly inquiries roughly doubled, from ~10 a week (2021) to 20–30 a week (2025–26), with spikes visible the week they happen.