Lead journey to contract

The deal's lead_id is often empty. Resolve it in three tiers, in this order: 1) the deal's direct lead_id; 2) the first lead of the same contact_id created before the deal; 3) a lead with the same normalized contact name, created up to 730 days before the deal, keeping the most recent. Store a column saying whether the link was recovered, to measure quality.

Map each lead status and each deal stage to a numbered macro stage (qualification, contact, viewing, offer, won, offer accepted, contract, signing, contract signed). The milestone date is the earliest entry date in the history for any stage in that macro stage. Always sort by the macro stage number, never by name.

On the lead, won (CONVERTED in Bitrix24) means it became a deal. Contract signed is a deal stage, after offer accepted, drafting and signing. Close rate uses contract signed over leads; deal conversion rate uses won leads over leads.

The journey is read by the lead's qualification date: the March leads and how far each one got. Time between two milestones exists only when both dates exist; incomplete journeys stay out of the average, they do not count as zero. For monthly output (how many viewings happened in March), use the event table by event date, and never mix the two readings in the same chart.

In the stage event, time in stage is the next entry minus the current entry. The stage a deal is in today is measured up to the current date, so its average grows every day. Show the duration of completed stages separately from the age of deals still in the stage.

The entry date into the current stage comes from the stage history. Per-stage date fields filled by automation can support it, checked against the history. The deal's last-updated date never works: any field edit changes it.

The back-office pipeline (data validation, draft contract, signing of the CCV, i.e. purchase and sale agreement, financing, deed, ITBI transfer tax and registration) has very different deadlines. A real example: data validation within 1 day, analyst validation within 2 hours, drafting within 8 hours, draft under review by the parties within 2 days, CCV signing within 1 day, financing signing within 90 days, deed and registration within 30 days each, cash sale annotations within 45 days. Stages that depend only on third parties may have no deadline. Show the status (late or on time) and how far past the deadline it is.

Bitrix24 stores dates in the server time zone. Convert to America/Sao_Paulo before cutting the day and before calculating deadlines in hours. In a stage with a 2-hour deadline, a wrong time zone marks everything as late.

Stalled means an open deal with days in the current stage above that stage's limit. Use the limit configured in the CRM when it exists (Imobzi stores stagnant_days per pipeline stage) and a declared default when it is empty (for example, 14 days). Won and lost never enter the alert.

Summary tables by team or source store the average already calculated. When combining several teams, calculate the weighted average from the events or individual journeys. An average of averages distorts when teams have different volumes.