Real estate CRM funnel by pipeline

In Bitrix, the deal's stage_semantic_id says P (open), S (won) or F (lost). For leads, use semantics from status_list (S won, F lost, empty open). The stage name changes by pipeline and is only for detail.

category_id (Bitrix) or the pipeline group (Imobzi) separates buyer, owner listing acquisition, launches, rentals and the administrative closing pipeline. The ids are numbers that change by company: keep a pipeline to type mapping table and count each type separately.

UF_CRM_* fields of type enumeration store the option id. The text comes from items in bitrix_deal_fields, which Nekt stores as array<struct<ID, VALUE>> with uppercase keys. The deal source comes from source_id joined with status_list where entity_id = 'SOURCE'. Build a single dictionary with the Portuguese label of each field and use it in every table.

A field with ismultiple = true arrives as an array even when the team fills in a single value. Use the first element as the main value and record coverage: in a real case, a required field still came empty in about 20% of deals.

Fields with nearly identical labels coexist ('Tipo do Imóvel' required and 'Tipo de imóvel' legacy and nearly empty). Use the required one. When the rule moved to a new field, as with loss reason, merge with the new field first and the legacy one as fallback, plus a category that covers both taxonomies.

The deal's development comes from the custom field defined for it (for example, source product or development list). Tags, labels and deal names undercount heavily: in a real case, tag and name found just over half of the deals the canonical field found.

Explode the sale by broker: one row when solo, two when a partnership, each with its share. Ranking and commission use attributed VGV. Sales count is always the distinct count of deals.

The team can come from the sales owner, the listing agent or the user's department. Define the credited team with a fixed priority rule. The department comes from bitrix_users.uf_department (array) joined with bitrix_departments, with the team name before ' - '. A broker who falls into two distinct teams goes to a multiple teams group, not to both.

Launches usually count by contract signing (CCV, purchase and sale agreement) and third-party sales by creation when there is no CCV. If the rule changed over time, keep both: up to a given year, the first entry into won after a non-won stage; after that, the date field created for it. Convert Bitrix timestamps to America/Sao_Paulo before cutting the month.

The deal only stores its current stage. Use bitrix_deal_stage_history and bitrix_lead_stage_history: each row is a dated transition. Time in stage = next transition minus entry. A stage still open is measured up to today, so its average grows. Without native history, record a daily snapshot.