Pipeline view (funnel)

Build one row per deal visit to a stage, with entry and exit dates, from the CRM history: propertiesWithHistory.dealstage in HubSpot (it comes newest record first, so sort by date), deal_flow in Pipedrive, OpportunityHistory in Salesforce, status change events in Kommo, stage_history in Bitrix24, firstTimeIn per phase in Pipefy. If the CRM keeps no history, save a daily snapshot of stages from today or capture changes by webhook. In HubSpot, hs_v2_date_entered_<stageId> keeps only the last entry into the stage: it cannot be used as the first entry or to build visits.

Classify every pipeline before counting: acquisition funnel, customer records, operations queue, portfolio, post-sale, recruiting or events. Only acquisition funnels enter conversion. In a real case, counting a customer pipeline together with sales produced a false win rate of about 91%. Also exclude archived and draft pipelines. A partner pipeline, or any pipeline with more than one won stage, stays out until someone declares which stage is the win.

A deal reached stage N if it entered N or any later stage of the same funnel. A deal that skipped a stage counts in the skipped stage, with the date of the first later stage. A lead created in a stage by automation shows up in history only with its exit: use the exit date as the entry. The order is the official one, declared by the team: side stages (no-show, paused, nurturing) are not progress and stay out of the order. The CRM display order is only a starting point, and stage probability does not always rise with position.

The loss stage does not move a deal forward. The loss counts at the last active stage before it. Status comes from the CRM semantics (won, lost, open), never from the stage name, and a stage named after a sales rep is not a stage. In a real case, treating the move to Lost as a step forward created more than a thousand false progressions. A loss that comes straight from another pipeline, or a deal created already lost, has no active stage in the funnel: count it separately as a loss with no stage, not at the first stage.

In HubSpot, the stages' metadata.isClosed arrives as true for all of them through the connector, including open ones. Derive closed and won from the stage probability (1.0 won, 0.0 lost) or from hs_is_closed_won and hs_is_closed on the deal. In Bitrix24, use stage_semantic_id; in Kommo, statuses 142 (won) and 143 (lost) apply to any funnel.

History holds repeated records of the same stage with no real change. Mark the start of a visit only when the stage differs from the previous record of the same deal; otherwise the last entry date gets inflated. For the funnel, use the first entry into each stage; for time, add up all visits.

Cohort funnel: start from deals created in the month and follow how far each one got. Monthly production: each stage counted in the month of its own event. Both readings are valid, but never filter the cohort by the date of a later stage, and never add creation month to close month.

For a deal still open, time in the current stage is measured up to today and grows every day. Show time in completed stages separately from the age of deals still in the stage, or the average rises on its own. Time is only measured in open stages: time sitting in Won or Lost is not funnel time. Convert dates to America/Sao_Paulo only once: Bitrix24 stores in the server time zone, other CRMs in UTC.

A deal moved to another pipeline leaves the original funnel and changes the past if the count uses the current pipeline. Assign each visit to the pipeline of the stage where it happened. In many CRMs, won deals are moved to an active customers pipeline right after: a win counts by the visit to the won stage, not by the current stage.

History keeps ids of stages that were deleted or recreated in the CRM and no longer appear in the pipeline's stage list. Without a mapping, those visits drop out of the funnel or end up with no pipeline. Map each old stage to its current equivalent, or declare that it stays out, and check that no visit is left without a stage.