Real estate: funnel, portfolio, and commissions
The standard for bringing CRM, portals, contracts and finance together in one model unique, monitoring the sales and rental funnel, property portfolio, commissions and default.
- See typical real estate sources
- Model funnel, portfolio and commissions
- Have an end-to-end implementation checklist
In a real estate agency, the data that matters is spread across systems that they almost never talk to each other: the lead is in the CRM, the ad is in the portal, the contract is in a folder and the rent is in the finance department. Each system tells a piece of the same story, and none alone answers the questions the business asks every day. This playbook shows the standard that Nekt uses to join CRM, portals, contracts and financial in a unique model and consumables, monitoring the sales and rental funnel, portfolio of properties, commissions and defaults without the errors that distort these numbers.
1. Context: what a real estate agency needs to measure
A handful of metrics guide virtually every decision in a real estate. They answer different questions, but they support each other same well-modeled base.
- Fundraising and sales/rental funnel: the path of lead to the closed deal, including lead, visit, proposal and closure. It shows where the operation loses people and which stage gets stuck.
- Average time until selling or renting: how many days one property takes from the time it enters the portfolio until it closes. It's the sign of health of the portfolio and the price charged.
- Active real estate portfolio and status: what is available, reserved, sold or rented, by type and by channel of capture. It's the real estate company's stock.
- Commissions per broker: how much each broker generated and will receive. Guides remuneration and shows who is performing.
- Rental default: how much of the portfolio lease is in arrears. It is the cash leak and affects the transfer to the owner.
- Occupancy rate: the proportion of the portfolio lease that is actually rented. Measures the efficiency of the operation of rent.
Each points to a different lever: the funnel measures conversion, the average time measures the liquidity of the portfolio, the status measures the stock, the commissions connect sales to remuneration, and default measures the leak. The problem is that the data is spread across CRM, portals and financial, and without a modeled base each area calculates in its own way, no one closes the number. This is the work that modeling at Nekt resolves.
2. Typical sources for a real estate agency
Most real estate agencies have their data divided between CRM, advertising portals, contracts and the financial system. Each source delivers a piece of the puzzle, and the value appears when they fit together. come together in a shaped layer. They all enter raw Bronze.
- Real estate CRM (e.g. Kenlo, Jetimob, Vista). A source of truth about the lead and the property: who entered, which property interested, responsible broker, funnel stage, and registration of properties with type, value and status. It is the heart of the model.
- Advertising portals (e.g. ZAP, VivaReal, OLX). From where the leads come from and where the properties are exposed: origin of the contact, which properties generate the most interest, advertising time. It is half of the fundraising bill.
- Sales and rental contracts. The closed deal: parties, property, value, term, agreed commission, and in the case of lease to month-to-month payment situation. This is what closes the funnel and feeds commissions and defaults.
- Financial and billing system. The money of truth: rent received and in arrears, transfers to the owner, commissions paid to brokers. Brings default and commission carried out.
- Website leads. The contact that arrives directly through the website of real estate, often outside the portals. You need to enter same funnel so as not to be invisible.
CRM and portals often have overlapping information about the same lead and the same property. Define from the beginning what the source of truth for each field: typically the CRM sends the property registration and at the funnel stage, the portal sends the origin of the lead, and finance dictates what was actually paid. Documenting this avoids conflicts when setting up a funnel, commissions and default.
3. Modeling recommended at Gold
The objective is to reach a few tables in the layer Gold, each with a clear granularity and a question it answers. The raw sources of CRM, portals and finance enter the Bronze, cleaning and standardization takes place in Silver (leads, properties and clean contracts), and the final business model lives in Gold: a funnel view by lead/property, a portfolio table per property, commissions per broker, and rental contracts with payment status.
- CRM Leads and Real Estate
- Portal contacts, contracts, charges
- As it came, unedited
- Values and dates still as text
- Typed values, truth dates
- One table per entity
- Deduplicated leads across portals
- Source of truth defined by field
- Funnel by lead/property
- Portfolio by property with status and days on market
- Broker commissions, rental with payment
- This is where dashboard and MCP read from
Funnel per lead/property (one line per lead)
The vision that shows the conversion. Each line is a lead, with the property of interest, the broker, the origin (portal or website) and the current stage in the funnel (lead, visit, proposal, closing). This is where the fees come from of conversion of each step and the time between them.
Property portfolio (one line per property)
The real estate company's stock. Each line is a property, with type, status current status, funding channel, days on market and value. It is the table that responds to what is stationary, what is spinning quickly and how it is distributed the portfolio.
Commissions per broker (one line per commission)
Each line is a commission generated by a closed deal: broker, property, contract, commission amount and status (receivable, paid). Adding by broker, you have the production of each one.
Lease contracts (one line per contract)
Each line is an active rental contract, with property, tenant, owner, monthly amount and payment status for the period. It's from where default, occupation and transfer come out.
One line per property, with type, status, capture channel, days in market, value and broker already resolved:
| property | type | status | canal_captacao | days_in_market | value | broker |
|---|---|---|---|---|---|---|
| IM-3012 | Apartment | available | ZAP | 18 | R$620,000 | Ana Prado |
| IM-3018 | Home | reserved | Website | 42 | R$890,000 | Rui Melo |
| IM-3025 | Apartment | available | VivaReal | 112 | R$445,000 | Ana Prado |
| IM-3031 | Commercial room | rented | Indication | 7 | R$ 3,200 | Lea Dias |
| IM-3040 | Home | sold | OLX | 63 | R$730,000 | Rui Melo |
Filtering by status equal to available and
dias_no_mercado above 90, you immediately find the
properties stopped. Grouping by corretor, see the
portfolio distribution. Grouping by
canal_captacao, discover where the properties that come from
spin faster, all from a single table.
Nekt's MCP creates the queries and transformation notebooks for you from natural language. Ask “turn CRM raw leads and portals in a deduplicated Silver" or "set up a Gold portfolio per property with days on market and status", and it generates the transformation. This speeds up A LOT to assemble the Bronze, Silver and Gold: you describe what you want, review and adjust, instead of writing every step from scratch.
4. Traps
Three pitfalls appear in almost every data project. real estate. They don't break the pipeline, what's worse: they deliver plausible but wrong numbers. It's worth knowing all three before model.
The same interested party usually arrives through more than one portal at the same time. time: he fills out the form on ZAP, then on VivaReal, and even send a message via the website. If you count each contact as a lead new, the top of the funnel inflates and the conversion rate collapses artificially. It is necessary deduplicate leads in Silver, combining the same person's contacts (by phone, email or document) in a single entry, and keeping which portals it came. Without this, the funnel measures form volume, not real people.
Selling and leasing are different businesses, with different metrics and cycles. Sales have a cycle long, percentage commission on a high value and a time. The lease has a short cycle, recurring revenue month to month, default and transfer to the owner. If you combine the two in same funnel or commission table without separating, the averages are meaningless: an average time that mixes 90 days of sale with 10 days rental does not describe either. Maintain distinction explicit, with a business type column, and calculate the metrics separately.
In projects and buildings, it is easy to confuse the property (the enterprise as a whole) with the unit (the specific apartment for sale). If the portfolio counts the project as a single line, you lose the status granularity per unit, and a building with 30 apartments, 28 of which were sold and 2 stopped, appears as a single property of ambiguous status. Model the right granularity: one line per tradable unit, with the enterprise as a dimension, to that days on market and status make sense on a unit-by-unit basis.
5. Use case: funnel and portfolio dashboard + AI agent
With the funnel, portfolio, commissions and rental tables in Gold, two consumptions appear almost for free on the same model.
A funnel and portfolio dashboard read directly from Gold tables: the conversion of each stage of the funnel, the properties stopped (available for many days on the market), the default of rents, occupancy rate and production per broker. How leads already come deduplicated and sale is separated from rental, the numbers close and the manager immediately sees where the operation stalls and where cash leaks.
An AI agent, via MCP, answers questions in natural language on the same tables: "which properties are stopped for more than 90 days", "which broker converted the most this month", "how much of the lease portfolio is in default". Instead of waiting an analyst sets up the query, whoever needs the answer asks and receives the number at the time.
What makes the agent trustworthy is the Semantic Layer describing these tables: what is each column, that the lead is already deduplicated, that sales and rental are separate types, meaning each portfolio status. Without this description, the AI guesses and gets it wrong. With it, the AI responds with the same rules that the dashboard uses, and the two match. One modeling, two consumptions that never contradict each other.
A medium-sized real estate agency captured leads through three portals and through the website, with the registration of properties in CRM and rentals in financial. The reported funnel seemed huge until we realized that the the same interested party entered several times through different portals, and that sales and leasing were added together in the same account. When deduplicating leads at Silver, separate sales from leases and build a portfolio per property with days on the market in Gold, the funnel and the portfolio they became numbers that the board finally trusted. On top of same Gold tier, they plugged in a funnel and portfolio dashboard and a agent who answers questions about idle properties and production of broker on a daily basis.
6. Implementation checklist
The end-to-end path, in the order that usually makes sense. Each step is based on the previous one, so it's worth following it from top to bottom.