Analytics platform for a proptech SaaS — built from zero
Sole data hire at a B2B proptech SaaS: ingestion, a dbt warehouse, a metric layer, four dashboards, alerting and predictive models. One person, end to end.
- 150+
- metrics, each defined once in git
- 100+
- dbt models, tests gate every build
- 3
- ML models: risk, churn, payments
The company is not named on purpose, and the figures that describe its size are left out. What is below is the engineering.
The brief
A B2B SaaS for property management companies, with a mobile app for residents and an admin platform for staff. When I joined, analytics meant manual SQL, spreadsheets and reports built on request. Within months I owned the analytics direction as the only data hire, reporting straight to the CEO, and I still do.
The warehouse
Production PostgreSQL replicates into BigQuery, and a dbt project of over a hundred models and several hundred tests sits on top of it, split across five domains: finance, operations, surveys, product and technical. Reporting that used to run a day behind now runs on a 15-minute cycle.
The part that took longest was not the modelling. It was finding out what the numbers already meant.
What the audit found
Before dbt there were BigQuery views and Looker Studio custom SQL, and the definitions inside them had drifted apart. Some of what turned up:
- Four different mechanisms for excluding deactivated properties, running at the same time: three hardcoded lists of different lengths and one query matching on names as strings. They disagreed, so two cards on the same page counted different portfolios.
- An analytics-SDK device ID was being treated as a person. It identifies an app installation, not a human, and “visitors” had been overstated by more than a fifth for as long as the chart existed.
- Duplicate rows in the raw event table, under one percent, from a replay during replication. They inflated every event count and were found by a uniqueness test, not by eye.
- A conversion funnel drifting toward 100% because its denominator event was removed in an app release while the numerator kept growing.
- A completion timestamp empty on roughly one closed request in five for two earlier years, so the “completed” series for those years was about a fifth too low.
- A headline collection-rate metric that read several times lower than the business expected. Next-period accruals were leaking into the source ledger. I rebuilt it with finance as three defined KPIs plus a receivables formula, and wrote the root cause down.
Each is now either fixed in a model or labelled in the registry as a known issue, with the reason written out, so the next person does not rediscover it.
The registry
More than 150 metrics live as YAML in git, each with a definition, grain, source, owner and caveats. The count is not the point. Coverage is enforced at build time: every card on every dashboard carries a registry entry, and the build fails on a card without one or on a label duplicated between domains.
That rule caught a real collision. A satisfaction card had silently inherited the help text of a different metric about ticket-closing scores. The check notices that an entry exists, not that it is about the same thing, so the fix was to make labels unique across the whole registry rather than per domain.
The dashboards
Four domains and about twenty pages, replacing Looker Studio. They are served from static exports of the dbt marts: no API server and no database behind the site, because at this size there does not need to be one. Dictionary encoding keeps the payload small; the largest aggregate went from 6.1 MB to 0.5 MB.
Access has two independent axes, role for what you can do and scope for what you can see, after the first version tried to encode both in a role and needed a new role for every combination.
Automation
A daily refresh runs without a human: Cloud Scheduler fires a GitHub Actions run, which builds dbt, exports the marts, regenerates the written commentary and redeploys. End to end it takes six to eight minutes. The commentary is generated by an LLM, and every number in it is checked against the query it came from before it ships.
The schedule lives in Cloud Scheduler rather than in GitHub’s own cron
because I measured GitHub’s: it ran 40–58 minutes late on good days, and on two
days did not run at all, with nothing red in the UI, because a run that never
starts cannot fail. The dashboard now says so itself, with a freshness banner
computed in calendar days. The version before it counted hours and could not
see a 24-hour gap, which is exactly the gap it existed to catch.
Agents
An MCP server sits over the metric layer, and an LLM analytics agent answers through it, so it can only use metrics that have a definition. Every number it states is checked against the query it came from. A second, proactive analyst agent works like a member of the team: it watches all the metrics around the clock and flags anomalies without being asked. The guard behind the first one is open source as metrics-mcp.
Models and alerting
- Payment-behaviour scoring, validated out of time, with features strictly point-in-time, so the score never sees anything that was not yet known on the day it is meant to predict.
- Churn early warning: a lexicon over request texts, a campaign detector and a friction model per property. Checked against properties that actually left the portfolio, it flags most of them where the obvious rule flags about a third.
- Infrastructure risk: which building system is likely to fail, and when.
- A handful of Cloud Functions posting to Slack: unassigned emergencies past an hour, backlog audits, an abuse detector and a daily digest for management. They are built to stay silent when things are fine; the story of making them quiet is in this post.
What I would do differently
I spent too long in a long-lived branch. The nightly job builds from main, so
every night it quietly rebuilt the marts from the older models sitting there
and overwrote mine. Work in a branch was not just undeployed, it was being
erased. Merge early when one dataset is shared by one source of truth.
And two substantial audits nearly died uncommitted in git worktrees, found only during a branch cleanup. One was a full tracking audit. The other was the analysis that cut replication volume from just over the plan’s limit to about 8% of it by finding two specific defects, rather than by moving vendors.
- BigQuery
- dbt
- PostgreSQL
- Python
- Next.js
- TypeScript
- Cloud Functions
- Cloud Scheduler
- GitHub Actions