Active listings and acquisition

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.