UrbanStack the analytics function

The brief

UrbanStack (formerly DIM 9000) is a proptech SaaS for property management companies — 17 residential complexes, 142 active buildings, around 36,000 resident accounts. When I joined as an intern in June 2025, analytics meant manual SQL, spreadsheets and reports built on request. By October I owned the analytics direction as the only data hire, and I still do.

The warehouse

Production Postgres replicates into BigQuery; on top of it sits a dbt project of 124 models and 376 tests across five domains — finance, operations, surveys, product and technical — materialising roughly 38 million rows into four serving datasets. The source replica alone is 28.5 million rows.

The part that took the 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 from each other. Some of what turned up:

• Four different mechanisms for excluding deactivated buildings, running at the same time — a hardcoded list of 10 UUIDs, a list of 11, a list of 3, and one query matching on complex name plus building number as strings. They disagreed, so two cards on the same page counted different portfolios.
• amplitude_id was being treated as a person. It is an app installation: 20,064 devices across 13,919 phones. “Visitors” had been overstated by 22–25% for as long as the chart existed.
• 64,626 exact duplicate rows in the raw event table — 0.48%, from a replay during replication, inflating every event count. Found by a uniqueness test, not by eye.
• A conversion funnel walking toward 100% because its denominator event was removed in an app release and the numerator kept growing.
• completed_at empty on 17–20% of closed tickets for 2022–23, so the “completed” series for those years was a fifth too low.
• A ticket type that is effectively never set — 990 of 164,201 records — underneath a dashboard panel that ranked buildings by it.

Each of those 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

155 metrics — 70 operational, 51 product, 18 financial, 16 technical — live as YAML in git, with definition, grain, source, owner and caveats. The point is not the count; it is that the coverage is enforced at build time. All 209 cards across the dashboards carry 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, 20 pages, ~38k lines of TypeScript, replacing Looker Studio. They are served from static exports of 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 is two independent axes — role for what you can do, 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 has now committed 53 consecutive updates: 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 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, started at 17:23 on one, 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 also 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.

Models and alerting

• Payment scoring — AUC 0.910 on the full portfolio, 0.879 on accounts with no debt at the time of scoring, where the top decile concentrates future defaulters 6.3× over the base rate. Out-of-time split, features strictly point-in-time.
• Churn early warning — a lexicon over 164k ticket texts, a campaign detector, and a friction model scoring 125 buildings. Validated against 13 buildings that actually left the portfolio: it flags 11 of them, where the obvious rule flags 4.
• Infrastructure risk — AUC 0.876 across building systems; 0.904 for heating against a 0.699 baseline.
• Five Cloud Functions pushing to Slack: unassigned emergencies past 60 minutes, 14- and 30-day backlog audits, an aggression detector, a daily digest for management.

What I would do differently

I spent too long working 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. Neither was small: one was the full tracking audit, the other the analysis that cut replication row usage from 5.17 million a month against a 5 million limit down to an unavoidable floor of about 400,000 — 8% of the plan — by finding two specific defects rather than by moving vendors.