---
id: imobiliario/imoveis-em-pauta-captacao
industry: Real estate
usage: common
effort: medium
grain: Property x status change (event); property x month for the portfolio snapshot
---

# Active listings and acquisition

How many properties came on and off the market this month, how much VGV (gross sales value) is available and who sourced it, with the status history the CRM does not keep.

## Executive summary

**For:** Brokerages selling and renting third-party properties that need to track their available property portfolio and listing acquisition.

**What it solves**

- The brokerage knows how many properties came on and off the market each month, even though the CRM only stores today's status.
- Active-listing VGV counts only the brokerage's own listings, without new-development units or partner portfolios.
- Properties duplicated across branches and ghost records stop inflating the portfolio.

**Why it matters:** The active portfolio is the brokerage's inventory: no good listings, no sales. Acquisition is a target for agents and branches, and it can only be enforced if inflow is measured the same way every month. The same definition of available feeds the website, the listing portals and the support agent.

**Business questions it answers**

- How many properties came on and off the market this month, by branch and listing agent?
- How much VGV do we have on the market today, by neighborhood and property type?
- Which neighborhoods have high demand and little supply in the portfolio?
- Why are properties leaving the market: sale, owner withdrawal or another reason?
- How many properties are on the website with no owner on record?
- How long does a property stay on the market until it sells?

**What you need**

- A property CRM connected (Vista, Bitrix24 with a property SPA, CasaSoft or Imobzi)
- The list of statuses that count as active and as removed, approved by management
- The neighborhood to branch table, if the brokerage has several branches

**What you get**

- An event table with every status change of every property
- A monthly portfolio snapshot with active stock and VGV
- Inflows and outflows by month, branch, listing agent and reason
- Issue lists: no owner, no price, not on the website, duplicates

## Detailed

### The problem

The property CRM only shows today's status. When a property comes off the market, the date and reason are lost, and nobody can say how many came on or off last month. The API table also carries old records, the same apartment shows up sourced by two branches and the price arrives as text. Active-listing VGV gets inflated and the acquisition target turns into an argument.

### Business rules

1. **Record the status history yourself.** At every load, compare each property's status with the last recorded one and write a row only when it changes: code, previous status, new status and date. Date it with the version time of the source table (the timestamp of the table's last write), not the time the transformation ran. When the CRM has native history, such as stage_history on the Bitrix24 property SPA, use it. (Avoid: Counting monthly inflows from the current status and the record's update date.)
2. **Active is a closed list of statuses.** Active for sale = an active sale status (for example, 'Venda' (sale), 'Venda e Aluguel' (sale and rent), 'Em negociação' (under negotiation)) and exibir_no_site = 'Sim'. Under negotiation stays active. Removal is the first change to a status outside the list. Intermediate statuses, such as pending, are the brokerage's call: declare whether they stay active. Website, portals and AI agent use the same list. (Avoid: Each dashboard with its own status filter.)
3. **Going active has one official date.** In Vista, the listing date is the activity record with subject 'Colocado para venda' (put up for sale). In Bitrix24, it is the first entry into the active-listings stage in the property SPA stage_history (the earliest date). Without either, it is the first recorded change to an active status. Re-entry after a removal counts as a new inflow, flagged. (Avoid: Using the property's data_cadastro as the date it went active.)
4. **Active-listing VGV only from the brokerage's own portfolio.** Sum the sale price only of properties sourced by the brokerage itself. Excluded: new-development units (category 'Empreendimento'), which have their own inventory, and properties from partner or other-brand portfolios registered in the same CRM, identified by the sourcing branch. Show these groups in their own columns, never added to the brokerage's VGV. (Avoid: Summing valor_venda of every property with a sale status.)
5. **Price arrives as text.** valor_venda comes empty, with the currency glued on ('450000|BRL') or in Brazilian format with dots and commas. Split on '|', turn empty into null and convert to a number before summing. A property without a price stays out of VGV and goes onto an issue list, not as a zero hidden in the average. (Avoid: Direct CAST from text, with empty becoming zero.)
6. **Use the live portfolio, not the API table.** The API property table carries old records, tests and properties that already left. In a real case, the raw table had almost three times more rows than the active portfolio. Define the live portfolio by the status list and website display, or by the current property list when the CRM offers one, and use only it for stock and VGV. The raw table is for enriching with attributes. (Avoid: Counting stock from the raw property table.)
7. **One property per normalized address.** The same property can be registered by several branches or franchises, with different codes. Normalize the street, strip leading zeros from the number, extract the apartment number from the address complement and use block, neighborhood, city, state and category. Codes that land on the same address form one unique property, with the list of codes alongside. (Avoid: One code, one property.)
8. **Branch comes from the neighborhood.** When the brokerage has several branches, the responsible branch comes from a neighborhood to branch table maintained by management, not from the free-text field on the record. A new neighborhood without a match shows as 'No branch' until classified. To compare supply and demand, group categories into Apartments, Houses, Lots, Commercial and Other. (Avoid: Branch from the text typed on the property record.)
9. **CRM migration with a compatible key.** When the portfolio moves to a new CRM (for example, from Vista to Bitrix24), create a compatible code: use the external id that holds the old code and, without it, the new id with a prefix. Join both CRMs' history on that key, with the origin marked, so the monthly series has no step in the migration month. (Avoid: Starting the series from zero in the new CRM.)
10. **Acquisition belongs to the listing agent, not the current agent.** Inflows count for the property's listing agent, recorded at entry. The responsible agent may change later and does not take the acquisition. Institutional users (the brokerage's name, 'Lançamentos' (new developments), removal markers) stay out of the ranking. In the owner acquisition pipeline, leads lost because 'the property is already listed' do not count as lost acquisitions. (Avoid: Acquisition ranking by today's responsible agent.)

### How to read the property status

Example of a closed list; names vary by CRM and must be approved by the brokerage.

- **Venda, Venda e Aluguel + exibir_no_site = 'Sim'** → Active for sale
- **Em negociação** → Stays active
- **Aluguel + exibir_no_site = 'Sim'** → Active for rent
- **Pendente** → Brokerage's call, declared
- **Vendido, Alugado** → Removed by a closed deal
- **Suspenso, Retirado, Inativo** → Removed without a deal
- **categoria = 'Empreendimento'** → Out of brokerage VGV: new-development inventory
- **partner sourcing branch** → Out of brokerage VGV: third-party portfolio
- **exibir_no_site = 'Não'** → Off the website, portals and agent
- **codigo_proprietario empty or '0'** → Record issue

### Definitions

| Metric | Definition |
|---|---|
| Inflows | distinct properties in the brokerage portfolio with an official entry date in the month, excluding the new-development category, by branch and listing agent |
| Outflows | distinct properties whose first change to a non-active status fell in the month, by reason or destination status |
| Active stock | distinct active properties on the last day of the month, from the monthly snapshot of the live portfolio |
| Portfolio net change | inflows minus outflows in the month |
| Active-listing VGV | sum of the numeric sale price of active properties in the brokerage portfolio on the cutoff date, excluding new developments and partners |
| Time on market | days between official entry and removal; properties still active measured up to today and flagged |
| Properties without owner | properties with exibir_no_site = 'Sim' and codigo_proprietario empty or '0' |
| Acquisitions by listing agent | inflows in the month attributed to the listing agent recorded at entry, excluding institutional users |

### Pitfalls

- **History that starts today.** The daily snapshot was only switched on when leadership asked for the number, and the first year had no inflows or outflows. How to avoid: Switch history on right away; for the past, rebuild from activity records or stage_history and mark it as rebuilt.
- **Change on the wrong day.** The transformation ran hours after the load, and changes made at night landed on the next day. How to avoid: Date the event by the version time of the source table.
- **Portfolio three times larger.** Stock was counted on the raw API table, with ghost and old properties. How to avoid: Count only the live portfolio and use the raw table for attributes.
- **Same apartment in two branches.** A property sourced by two branches of the network entered active-listing VGV twice. How to avoid: Group by normalized address with the apartment extracted from the complement.
- **Step at migration.** After the CRM switch, the whole portfolio showed up as new inflows in the migration month. How to avoid: Compatible key across CRMs and history joined with the origin marked.
- **Property on the website with no owner.** Properties shown on the website had no owner on record, and the agent had no one to call when an offer came in. How to avoid: Daily list of record issues for management.

### Acceptance test

- [ ] For each branch, stock at the end of the previous month + inflows - outflows = stock at the end of the month.
- [ ] Today's active stock equals the count of properties with an active status and exibir_no_site = 'Sim' in the CRM (zero difference).
- [ ] No code appears in two unique properties, and active-listing VGV does not add the same address twice.
- [ ] Every outflow has a date and a reason or destination status.
- [ ] Active-listing VGV does not include properties in the new-development category or from partner portfolios.

### Context document

```text
Active listings and acquisition
- Active for sale = status 'Venda', 'Venda e Aluguel' or 'Em negociação' and exibir_no_site = 'Sim'. The brokerage's official list is below; do not invent statuses.
- Inflows and outflows come from the status event table, never from the current status. If the requested month is before the history starts, say so.
- Entry date: activity record 'Colocado para venda' (put up for sale) in Vista, or first entry into the active stage in stage_history (Bitrix24).
- Outflow = first change to a non-active status. Report the reason or destination status.
- Active-listing VGV (gross sales value) = sum of the numeric sale price of the brokerage's own portfolio. Excluded: category 'Empreendimento' (new development) and partner portfolios.
- valor_venda arrives as text ('value|BRL' or empty): a property without a price is an issue, not zero.
- Unique property = normalized address (street, number without leading zeros, apartment, block, neighborhood, city, category).
- Branch = neighborhood to branch table.
- Acquisition counts for the listing agent recorded at entry, excluding institutional users.
Client-specific exceptions:
- <client>: active statuses = <list>; '<status>' stays active by management decision.
- <client>: sourcing branch <branch> is a partner portfolio and stays out of the brokerage VGV.
- <client>: status history available from <date>.
```

### Variations

- **Rentals:** The same logic with rental statuses: rented is removal by a closed deal and the portfolio measures the total available rent value.
- **Managed properties:** For short-stay or rental management, a daily snapshot with activation, deactivation and churn dates for each property under management.
- **Supply vs. demand:** Cross the active portfolio with buyer leads by neighborhood, type and price range to steer acquisition.