The data model behind firm analytics that reconcile hours to cash: the realization waterfall, the three-system join that powers it, and the two accounting quirks that make naive versions double-count. Public excerpt; the full teardown lives in the Builder library.
Firm analytics is a deceptively hard join problem. Time lives in one system, invoices in another, cash in a third — and the entire product is making them reconcile. The dashboards at the end are ordinary BI; the waterfall model underneath is the build.
Join 1 — Time to matters. Every hour entry resolves to a matter, a timekeeper, and a work type. The trap is silent orphans: entries against closed, merged, or miscoded matters that quietly vanish from rollups and make utilization look worse than it is. Orphans get a queue, not a shrug.
Join 2 — Matters across systems. The practice management system, the billing platform, and accounting each have their own matter identifiers — and at most firms, no canonical crosswalk exists until you build one. This mapping table is the least glamorous artifact of the build and the one everything else stands on.
Join 3 — Invoices to cash. Payments arrive partial, lumped across invoices, and occasionally misapplied. An allocation policy — explicit, documented, consistently applied — is what makes billed-to-collected realization a number instead of an argument.
The model — a realization waterfall. Hours worked → hours billed (write-downs live here) → amounts invoiced (discounts live here) → cash collected (write-offs and lag live here). Every stage transition is measured, by attorney, client, matter type, and practice area, trended over time. The waterfall is the whole point: firms don't leak profit in one place, they leak a few points at each stage, and only a stage-by-stage model makes each leak visible and attributable.
Write-downs counted twice. The billing platform recorded some write-downs as adjustments on the invoice and others — depending on which partner did it — as negative time entries. The naive model subtracted both, overstating the leak and pointing the firm at a problem 40% smaller than reported. The fix: classify every adjustment path in the source system first, and reconcile the waterfall's total against accounting's revenue before showing anyone anything. If your waterfall doesn't tie to the P&L, it isn't analytics yet — it's an allegation.
"Utilization" meant three different denominators. Available hours, target hours, worked hours — the firm's partners each assumed a different one, and the first dashboard silently picked. Same lesson every analytics build teaches: the definition, rendered next to the metric, agreed before launch. Contested numbers don't get a second meeting.
The full teardown — the waterfall model spec, the adjustment-classification checklist per billing platform, the matter-crosswalk pattern, and the P&L reconciliation procedure — lives in the Builder library.
Implementation detail, checklists, and the parts we'd rather not have public — for members.
Unlock with Builder