The data you cannot buy later.
One table, written every time a deal changes stage. It costs almost nothing to capture now and it is impossible to backfill — if it is not in the schema before the first real deal, the first two years simply do not exist. What it buys you is below, built on two years of simulated deals.
Median days in each stage
189 registered deals. The bar is the median; the number on the right is the 75th percentile — the slow quarter.
Intake is the only stage you control, and it is the worst one
Nine days is the median to get documents in, but a quarter of applicants take 24 days or more. Registration itself takes 38 days and that belongs to the attorneys and the deeds office — you cannot touch it. Submission to offer is four days. So of a 58-day median, the only stretch your software can shorten is the first nine, and the tail on it is where deals die. That is an argument for the per-item upload list and the broker's reject-and-resend, and this table is how you would know whether they worked.What never registered
47 deals that started and did not finish.
Volume is not value
Sort by whichever column you think matters, then look at what it does to the order.
The finding that pays for the table
Pam Golding Claremont sends you the most deals by a distance — 61 — and converts 36% of them. Rawson Southern Suburbs sends fewer than half that and converts 64%. Per deal sent, Rawson is worth R9 000 and Pam Golding R5 049. Just Property Wynberg sends 47 deals a year and at R3 574 per deal against roughly R2 800 of consultant time to work one, that relationship is close to costing you money. Nobody knows that without this table, and everybody assumes the opposite, because volume is what you notice.Two years of submissions, by lender
Approval rate, how fast they come back, and what they actually priced at.
This is what feeds the pre-flight rules
Every decline carries a reason. Aggregated, those reasons stop being bad news and become policy you can act on before you submit — which is exactly what the bank-packs module already does, except that today its rules are written by hand. With this table they would be derived from your own outcomes and would update themselves.Decline reasons, aggregated
96 declines across five lenders.
Every analysis above comes from this
Append-only. Rows are never updated and never deleted — that is what makes it evidence.
Four things to get right, and they are all cheap now
Append-only. Never update a row. The current stage of a deal is derived from the last event, not stored separately — two sources of truth will drift.Record the actor. Who moved it, not just that it moved. Half the value is per-consultant.
Record the organisation. Every table in a multi-tenant system needs it, and an analytics table that leaks across brokerages is worse than no analytics table.
Separate occurred_at from recorded_at. A consultant catching up on Monday for something that happened on Friday will otherwise corrupt every duration you ever measure.
And one thing not to do
Do not let this table hold personal information. It holds a deal id, a stage and a timestamp. The moment somebody adds an applicant's name or income to it "for convenience", a two-year analytics archive becomes a two-year POPIA liability with no retention justification. Join to the deal when you need a name.Prototype. 189 registered deals, 47 lost, five agencies and five lenders, all simulated across a two-year window. The schema is real and is what I would build. Rates shown as a margin to prime are illustrative and every lender prices per applicant.