Client CRM, lead source and portfolio media
Per-client stage map and source cascade
Funnel with cost per stage by client and channel
Client report, dashboard and AI agent
What it solves
- Cost per MQL, SQL and sale for each client, using the funnel definitions the client uses.
- The source of each lead decided by a single rule, not by whichever platform claims the most credit.
- A funnel that does not change size on its own when sales moves deals between pipelines.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- The client's CRM and media connected to Nekt
- Funnel definitions agreed with the client: which pipelines are included and what counts as MQL, SQL and sale
- The Paid media for the whole portfolio case, for spend by client and channel
What you get
- A table with one row per deal showing the furthest stage reached, source channel and campaign
- The monthly funnel by client and channel with CPL, cost per MQL, cost per SQL and CAC
- A per-client map from CRM stage to funnel stage, maintained as data
- An AI agent that answers funnel questions using each client's definitions
Where to activate
The month's funnel by channel with cost per stage, in the cohort and close views.
Answers funnel questions and explains a stage drop using that client's definitions.
MQLs and sales from the CRM sent to Meta and Google to optimize campaigns.
Each client has its own CRM and funnel, and the definitions must be validated with them. The good news: once the per-client map exists, the calculation is the same for everyone. 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 ad platform counts leads; the client counts sales. Between them sits the client's CRM, and every client uses a different CRM, set of pipelines and definition of MQL, SQL and sale. Many CRMs store only the current stage, the lead source arrives encoded or empty, and the same win can be a sale in one pipeline and only a qualification in another.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
A lead is a deal (or CRM lead) created in one of the pipelines agreed with the client, counted by creation date. Recruiting, outbound, events, base reactivation, company-wide orders and quality pipelines stay out. Integration test leads (test loss reason, title or contact containing test, asdf, qwerqwer) are excluded from the count.
Each client has a map from CRM stage to canonical stage: lead, MQL, SQL, opportunity, sale, lost. The order of precedence is: manual override in the map, then a keyword in the stage name evaluated from the most advanced to the least advanced, then the first stage of the pipeline becomes lead. A stage that matches nothing becomes lead and raises an alert; it never disappears.
Use stage history when it exists (hs_v2_date_entered_<stageId> in HubSpot, stage change events in Kommo). Without history, a deal reached stage N if it is won or if the order of its current stage is greater than or equal to N. A deal lost after SQL still counts as SQL.
Sale = deal won in the sales pipeline, by close date. A win in the pre-sales pipeline is a qualification (SQL), not a sale. In the HubSpot Lead object, qualified is hs_pipeline_stage_category = QUALIFIED. In Kommo, statuses 142 (won) and 143 (lost) are global and apply to any pipeline.
In HubSpot, the stages' metadata.isClosed can be true for all of them, including the top of the funnel. Derive the status (open, won, lost) from the stage id and probability: probability 1 is won, the loss stage is lost, everything else is open. In Pipedrive, won and lost are deal statuses, and the deal stays in the last stage it was in.
The first rule that matches sets the channel: manual override confirmed by the team, deal UTM, UTM inherited from the pre-sales lead, click ids (gclid, gbraid, wbraid or gad_source for Google; fbclid for Meta; li_fat_id for LinkedIn), the CRM Source field, organic, no source. In RD Station, traffic_source can arrive as encoded_ followed by base64 with the first and last session: decode it before concluding there is no tracking.
Meta's native form does not go through the website, so the deal arrives without UTMs. Use the form or ad id, or the ids in the first visit URL (hsa_ad, hsa_grp, hsa_cam and similar). Test each numeric id against the real ad_id, adset_id and campaign_id in the client's account, because the parameter position changes from client to client.
spend divided by all CRM leads.CPL, cost per MQL, cost per SQL and media CAC = paid spend for the period / stage count from paid channels. By channel, the channel's spend over the same channel's stage count. An unknown source can count as digital if the client decides so, but never as paid, because there is no attributable cost.
The cohort view counts by lead creation date and answers whether the month's media paid off; it is the view divided by the same month's spend. The close view counts by win date and answers how much came in during the month. The cohort for recent months keeps growing over the following weeks. When showing the close view, state how much came from leads created before the period.
Across CRM, marketing tool and forms, matching uses lowercase email without spaces, or phone with digits only and country code 55. When the client's policy requires it, use the email hash. A name is never a key.
How each CRM says a deal moved forward
The stage, win and loss signal changes from CRM to CRM.
Definitions
spend for the month / paid-source leads created in the monthspend for the month / paid-source MQLs or SQLs from the month's cohortspend for the period / paid-source sales from the period's cohortPitfalls that already happened
With leads defined as deals in the pre-sales pipeline, every qualified deal that left that pipeline dropped out of the count, and closed months lost leads.
Count creation and the furthest stage, not the pipeline the deal is in today.
In several clients' HubSpot portals, isClosed was true on every stage, including the top ones, and thousands of open deals showed up as closed.
Derive open, won and lost from the stage.
At a client with a pre-sales pipeline, the SDR's win, which means a qualified lead, was read as a sale and inflated the month's sales.
A sale requires the sales pipeline and a win.
Bulk import and integration events were most of the RD Station events at some clients, and a reactivation pipeline built from an import pushed CPL down.
Separate imports, integrations and reactivation before counting leads.
RD Station's traffic_source looked empty or unreadable, but it was base64. More than one agency rediscovered this on its own, after treating those leads as having no source.
Decode encoded_ before classifying a lead as having no source.
Each platform credits the conversion on the click date and does not discount the other platform, so the sum across platforms exceeds the CRM total.
Leads, MQLs and sales by channel come from the CRM; cost and clicks come from the platform.
Acceptance test
The case is done when every item passes.
- For one client and one closed month, the counts of leads, MQLs, SQLs and sales match the CRM funnel report with the same pipeline and date filters.
- At each stage, the count is never greater than the previous stage's count for the same cohort.
- The sum of leads by channel equals the month's total leads, with an explicit no-source row.
- The spend used in cost per stage equals the client's paid spend in the portfolio media table for the same month.
- Reprocessing a closed month after moving deals between pipelines does not change that month's lead count.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
Funnel from media to sale - Lead = deal created in one of the client's in-scope pipelines, by creation date. Out-of-scope pipelines and test leads do not count. - MQL, SQL and sale follow each client's stage map. Count the furthest stage reached, not the current stage. - Sale = won in the sales pipeline. A win in the pre-sales pipeline is an SQL. - Do not use HubSpot's metadata.isClosed. The stage status is derived. - Lead channel: manual override, deal UTM, UTM inherited from pre-sales, click id, Source field, organic, no source. In this order. - Cost per stage = paid spend / stage count from paid sources. Never divide by the CRM's total leads. - Cohort (creation) and close (win) views never add up. Cost per stage uses the cohort view. - Leads, MQLs and sales by channel come from the CRM; spend and clicks come from the platform. - If a client's funnel definitions have not been validated, say "funnel definitions not validated with the client". Per-client exceptions: - <client>: in-scope pipelines are <pipeline 1> and <pipeline 2>; <pipeline 3> is reactivation and stays out. - <client>: MQL = entry into the <name> pipeline; SQL = won in the pre-sales pipeline. - <client>: source "Desconhecido" (unknown) counts as digital, but not as paid.
Build the "Funnel from media to lead, MQL, SQL and sale" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/agencias/funil-midia-crm.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
With the source ad on every deal, rank creatives by SQLs and sales, not by leads.
The same ladder broken down by the client's product line, unit or clinic.
Conversation started at the top, with qualification coming from the customer service tool.