--- id: imobiliario/funil-crm-por-pipeline industry: Real estate usage: widely used effort: medium grain: Deal with pipeline, stage and decoded fields; stage transition for the journey --- # Real estate CRM funnel by pipeline Open, won and lost deals in each pipeline with readable CRM fields, VGV (gross sales value) by broker without double counting partnerships, and the real time spent in each stage. ## Executive summary **For:** Brokerages selling third-party properties, new launches and rentals that use Bitrix24, Vista, Imobzi or Moskit. **What it solves** - Managers see open, won and lost deals in each pipeline without opening the CRM. - Custom fields become readable text, and the development behind each deal stops being undercounted. - VGV, VGC (gross commission value) and sales by broker and team match the real number, even with partnership sales. **Why it matters:** A real estate CRM stores almost every business rule in codes: field ids, option ids and semantic letters. Without a mapping, each dashboard reinvents the rule and the numbers diverge between leadership and teams. It is the foundation for broker targets, commissions and the media-to-sale funnel. **Business questions it answers** - How many deals are open, won and lost in each pipeline this month? - How much VGV and VGC did each broker and each team deliver against target? - At which stage does each team lose the most clients? - How long does a lead take to reach the visit, the proposal and the signed contract? - Which deals above a given value have been stalled for more than N days? - How many owner listings came in per broker this quarter? **What you need** - The CRM connected to Nekt, with the field metadata tables and the status list - The map of CRM pipelines (buyer, owner, launches, rentals, closing) - The target spreadsheet by team and period, if there are targets **What you get** - A table per deal with pipeline, phase group and readable fields - A sales by broker table with share, for ranking and commission - A stage transitions table with the time spent in each one - An AI agent that answers questions about the funnel using your house definitions ## Detailed ### The problem The CRM stores pipeline, stage and custom fields as codes: a numeric category_id, UF_CRM_* fields with option ids and a semantic letter per stage. Each brokerage uses different ids, and legacy fields with similar labels coexist with the new ones. Anyone who filters by deal name or sums partnership sales per broker delivers doubled VGV and undercounted developments. ### Business rules 1. **Phase group comes from stage semantics.** 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. (Avoid: Classifying won and lost by stage name.) 2. **One pipeline per line of business.** 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. (Avoid: Adding owner listings to buyer leads in one lead total.) 3. **Decode fields with the dictionary.** UF_CRM_* fields of type enumeration store the option id. The text comes from items in bitrix_deal_fields, which Nekt stores as array> 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. (Avoid: Showing the option id or decoding field by field in each query.) 4. **Multi-value fields use the first value.** 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. (Avoid: Exploding the array and counting the deal several times.) 5. **Pick the current field, not the legacy one.** 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. (Avoid: Taking the first field that shows up when searching by label.) 6. **Development only from the canonical field.** 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. (Avoid: Filtering development by deal name or by tag.) 7. **A partnership sale is one sale.** 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. (Avoid: Adding the full VGV to each broker in the partnership.) 8. **Team has a single rule.** 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. (Avoid: Counting the sale for the team of every filled field.) 9. **The official sale date is declared.** 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. (Avoid: Using closedate or date_modify as the sale date.) 10. **Time per stage comes from history.** 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. (Avoid: Calculating time per stage with the deal's date_modify.) ### How to read pipeline and stage Stage semantics and pipeline type, in Bitrix24. - **stage_semantic_id = P** → Open deal (`P`) - **stage_semantic_id = S** → Won deal (`S`) - **stage_semantic_id = F** → Lost deal (`F`) - **Lead: empty semantics in status_list** → Open lead (`entity_id = 'STATUS'`) - **Buyer pipeline** → Demand: a lead who wants to buy (`category_id = `) - **Owner pipeline** → Acquisition: a property for the portfolio (`category_id = `) - **Launches pipeline** → Off-plan unit sale (`category_id = `) - **Rentals pipeline** → Rental (`category_id = `) - **Closing or admin pipeline** → Closed sale and post-sale (`category_id = `) - **Loss reason 'duplicate or error'** → Excluded from all counts (`loss reason`) ### Definitions | Metric | Definition | |---|---| | Leads captured | distinct deals created in the pipeline in the period, by pipeline type (buyer, owner, launches, rentals) | | Active leads | distinct deals with an open phase group on the cutoff date, by pipeline | | Won and lost | distinct deals with semantics S or F, by the date they entered that semantics | | Sales | distinct count of won deals in the closing pipeline, by official sale date, excluding withdrawals | | VGV | sum of the property sale value of sales in the period; for broker ranking, sum of VGV attributed by share | | VGC | sum of the brokerage's commission on sales in the period; broker and listing agent commissions stay in their own columns | | Time in stage | days between entering the stage and the next transition; an open stage is measured up to today and flagged | | Days to signing | days between lead creation and entry into signed contract, per converted lead | | Target attainment | actual VGV or VGC / team target for the same year, month and vertical | ### Pitfalls - **Stages vanish from the funnel.** A stage with no deals in the period does not show up in a simple count, and the funnel chart ends up with gaps. How to avoid: Cross join every stage of each pipeline with the count and show zero. - **Two CRMs at once.** After a migration, the old and new CRM coexist with their own status lists and ids. How to avoid: Decode each one with its own status_list and only union afterwards, with the origin flagged. - **Duplicates in the lead total.** Lost deals with the reason 'duplicate or error' inflated leads and dragged down conversion. How to avoid: Remove that reason from every count. - **Institutional user in the ranking.** Users such as 'Lançamentos', 'Administrativo' or the brokerage's name showed up as brokers with sales. How to avoid: Keep a list of institutional users out of the ranking. - **Wrong month at the turn.** Bitrix records in the server time zone, and sales signed on the evening of the last day fell into the next month. How to avoid: Convert to America/Sao_Paulo before cutting the month. - **Manual fix gets lost.** Leadership corrects the date of a specific sale on the side, and the fix stays hidden in the transformation code with no one knowing why. How to avoid: Store fixes in an exceptions table per deal, with the reason. ### Acceptance test - [ ] For one pipeline and one closed month, won, lost and open match the CRM's own filter (zero difference). - [ ] The sum of VGV attributed by broker equals the month's total VGV, and the number of sales is the distinct count of deals. - [ ] No option field appears as a numeric id in the final table. - [ ] Every stage of each pipeline appears in the funnel, including those with zero. - [ ] The sum of stage times for a closed deal equals the days between creation and closing. ### Context document ```text Real estate CRM funnel - Phase group comes from semantics: stage_semantic_id P = open, S = won, F = lost. Stage name is detail only. - Each pipeline (category_id) has a type: buyer, owner (listing acquisition), launches, rentals, closing. Never add different types into one lead total. - UF_CRM_* option fields are decoded with the field dictionary (items from bitrix_deal_fields). Multi-value field: the first value is the main one. - Deal source: source_id joined with status_list where entity_id = 'SOURCE'. - Development only from the canonical field. Never from tag or deal name. - Lost deals with the reason 'duplicate or error' are excluded from every count. - Sales = distinct count of deals. Broker ranking uses VGV (gross sales value) attributed by share; partnerships do not double count. - The credited team follows a single priority rule per sale. - Official sale date: CCV (purchase and sale agreement) signing for launches; for third-party sales, per the declared rule. Timestamps converted to America/Sao_Paulo. - Time per stage comes from stage history. An open stage is measured up to today and raises the average: warn when the answer includes open stages. Client exceptions: - : pipelines , , , , . - : development field = ; broker field = . - : official sale date up to = first entry into won; after that = . - : institutional users excluded from the ranking: . ``` ### Variations - **Vista, Imobzi and Moskit:** Vista: stage and pipeline in vista_crm_negocios and visits in the calendar (completed visit). Imobzi: native pipeline groups and stagnation days. Moskit: options in custom_field_options. - **Visits by broker:** Real visits = visit type activities marked as completed, by broker and month, with distinct clients alongside. - **Post-sale and SLA:** Same method in the administrative pipeline: time between data validation, draft, CCV signing and issuing the commission invoice.