Stage and owner history in the CRM
Declared metric, official date and role at the time
Funnel by cohort and by production, with credit per person
Dashboard, ranking, AI agent and alerts
What it solves
- Every dashboard shows the same number of MQLs, SQLs and sales.
- Each stage has an official date, even when the deal skipped a stage or went backward.
- SDRs and closers get credit for what they did, even if the deal changed owner later.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- CRM connected to Nekt with stage and owner history
- An agreed definition of MQL, SQL, demo and sale, with exclusions
- Targets by month and by person or channel, in a spreadsheet or table
What you get
- A stage events table, with date, author and order
- Funnel by cohort and by monthly production, without mixing the two
- SDR and closer ranking with credit at the time of the event
- An AI agent that answers funnel questions with the official definitions
Where to activate
Funnel by cohort and by production side by side, with each stage's definition visible.
Answers how many SQLs the team generated, or how far we are from the target, using the metrics table.
Inbound lead with no first touch within N business hours, and a deal stuck in a stage beyond the limit.
The history already comes from the connector. The work is agreeing on definitions with the team and mapping stages, roles and exclusions. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
The CRM shows where each deal is now, not when it passed through each stage or who was handling it that day. Each dashboard reimplements the MQL definition its own way and the numbers diverge in the leadership meeting. Counting by current state misses deals that skipped a stage, counts lost deals as progress and gives the sale to the last owner.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
Declare in a metrics table what counts as MQL, SAL, SQL, demo and sale: the stage or event that marks it, the official date, the source table, the role that gets the credit and the exclusions. Dashboards, reports and the AI agent read from that table. Keep translation separate from rules: saying Closed Won and Ganho are the same stage is mapping; saying Ganho is the sale milestone is a metric definition.
A stage's date is the first time the record entered it or any later stage, read from the change history (propertiesWithHistory.dealstage in HubSpot, leadhistory and opportunityhistory in Salesforce, deal_flow in Pipedrive). The ready-made date-entered fields store the last entry: if the deal returned to the stage, the date moves forward. Use the ready-made field only as a check.
A record has an SQL if it has a meeting scheduled date or the date of any stage after it (meeting held, proposal, contract, won). A deal that went straight to contract did schedule, even without the intermediate record. When the lead date is missing (lead created directly as an opportunity), the cohort's anchor date is the opportunity's. Prospecting stages that exist in only one pipeline (for example, an outreach attempt in field sales) stay out of the funnel until the deal reaches the first common stage.
Moving to Lost never counts as progress, even if Lost comes later in the pipeline order. Stages like cold lead and no-show leave the conversion table but don't block progression: a deal that went through no-show and later closed still counts as scheduled and won. Deal status comes from the won and closed flags (won; otherwise closed is lost; otherwise open), never from the stage name.
History includes repeated rows with the same value as the previous row (sync, merge, reprocessing). Mark as the start of a visit only the row whose stage differs from the previous row of the same record. Time in stage runs from entry to the next real change; re-entries become separate visits, and the total per stage sums the visits.
Cohort: the population is defined by the lead's creation date, and each stage is counted for those leads, on any date. Production: each stage is counted in the month of its own event, without deduplicating by lead. Answer with the view the question asks for and say which one you used. Never filter the cohort table by SQL or sale date: that creates a hybrid view that is neither KPI.
A record has different roles at the same time: the SDR, who qualifies, and the closer or AE, who closes. Keep a timeline for each role (history of hubspot_owner_id and of the SDR field) and attribute each stage to whoever held the role on the event date. Freeze the person's sales role (SDR, closer, team, region) the first time they appear, or use the job title history, so a promotion doesn't rewrite the past. If the team has a portfolio reassignment window at the start of the month, define the month's owner as the owner at the end of that window.
Counting every lead as an MQL inflates the number about threefold. An MQL is a lead that meets the ICP based on answers declared at entry (segment, size, volume), or the lead minus those discarded by a closed list of loss reasons. If fit is recalculated later including those who already qualified, it becomes circular: for quality at arrival, use only the declared fields. SQL may not be a subset of MQL, and the MQL to SQL funnel can exceed 100%; decide the rule and say so in the answer.
Upgrades and expansion of the base go into new revenue from the portfolio, never into acquisition SQLs, demos or sales. A meeting booked by an AI agent or by automation doesn't count as scheduled until a human confirms it. Changes made by integration or automation users stay out of per-person activity counts. Archived or merged deals (hs_merged_object_ids) are removed, and a close date earlier than the creation date is treated as a data entry error.
Contracted value is a deal metric. Breaking it down by product line is for mix, never for summing revenue as if each line were a deal. For targets, the same spreadsheet often has the total, the channel breakdown and the product breakdown on the same rows: filter one scope before summing. Actuals go into the month of the official sale date, and foreign currency is converted at a declared rate.
Stage, official date and who gets the credit
Metrics table template. Adjust names and exclusions to your CRM.
Definitions
Pitfalls that already happened
The definition lived inside a BI measure, was rewritten in another dashboard and the numbers diverged in front of leadership.
One declared metrics table, read by every dashboard and by the agent.
Disqualification depended on a reason mapping in a spreadsheet. When a new reason appeared, it didn't disqualify and the lead kept counting as an SQL, with no error at all.
A test that lists unmapped reasons and alerts, instead of falling into a silent ELSE.
Opportunity field tracking was only turned on mid-year, and earlier months looked like they had no stage changes. The native opportunity history, which records stage without depending on configuration, recovered about three times more changes.
Check when each history source starts and use the one that covers the period.
Timestamps arrive in UTC and were converted in one layer and again in the next, pushing evening events to the wrong day.
Convert to the local time zone only once, in the first cleaned layer, and document it.
In a field sales pipeline with prospecting stages before the meeting, counting every deal as an SQL more than doubled the number.
SQL by the monotonic test, never by the pipeline's deal count.
The targets table had total, channel and product on the same rows, and summing without a filter gave three times the real target.
A scope column on the target and a mandatory filter before summing.
Acceptance test
The case is done when every item passes.
- For a closed month, MQLs, SQLs and sales by SDR in the production view match the count done in the CRM using the same official dates (zero difference, or explained by an archived or merged deal).
- No deal has a sale without an SQL: every record with a won date has an SQL date less than or equal to it.
- In every deal that re-entered a stage, the official date is the first entry and the number of visits is greater than 1.
- The sum of attainment by channel, filtering the channel scope, ties out with the total-scope attainment (difference below 1%, or explained by a deal credited to two channels).
- No activity row has an integration or automation user as its author.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
B2B sales funnel - The definitions of MQL, SAL, SQL, demo and sale are in the metrics table. Don't improvise equivalents. - Official stage date = first entry into it or a later stage, taken from history. The hs_v2_date_entered_<stageId> fields store the last entry; don't use them as the official date. - SQL = has a meeting scheduled date or the date of any later stage. Never test only the scheduling date. - Lost is not progress. No-show and cold lead stay out of conversion, without blocking those who advanced later. - Deal status: won by the won flag; closed without a win is lost; everything else is open. Never by the stage name. - MQL = declared ICP fit. Not every lead is an MQL. SQL may not be a subset of MQL: say so if conversion exceeds 100%. - Upgrades and expansion don't count as acquisition SQLs, demos or sales. - A meeting booked by AI or automation only counts as scheduled after human confirmation. - Credit goes to whoever held the role (SDR or closer) on the event date, never to the current owner. - Cohort (by lead creation) and production (by each stage's date) are different views. Say which one you are using. Never filter the cohort by SQL date. - Response time and time in stage as an SLA are measured in business days or business hours, in the local time zone. - Revenue belongs to the deal. Targets: filter one scope (total, channel, product, person) before summing. Per-client exceptions: - <client>: the <name> pipeline has prospecting stages before SQL; deals sitting in them don't enter the funnel. - <client>: the portfolio is reassigned until day <n> of the month; the month's owner is the owner on that day. - <client>: amounts in <currency> converted at the fixed rate of <x>.
Build the "B2B funnel with stage history" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/saas/funil-b2b-historico-etapas.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.
Variations
Two CRMs in the same period: one row per deal with the source system and a migration cutoff date, without counting the same deal twice.
A weekly snapshot of open deals with forecast category; compare two snapshots by filtering both to the quarter in effect at the start of the week.
The same event structure for sign-up, activation and subscription, with the product in place of the CRM.