--- id: agencias/funil-midia-crm industry: Marketing agencies usage: widely used effort: high grain: One row per deal for attribution; client x channel x campaign x month for the funnel --- # Funnel from media to lead, MQL, SQL and sale What each lead, MQL, SQL and sale cost for every client, measured in the client's own CRM and not in the ad manager. ## Executive summary **For:** Agencies that need to show clients opportunities and sales, not just platform leads. **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. **Why it matters:** Clients do not renew because of CPL, they renew because of sales. When the funnel comes from the CRM with cost per stage, the agency defends the budget with numbers the client's sales team recognizes. With the wrong definitions, the report shows cheap leads that never turn into sales. **Business questions it answers** - How many MQLs and SQLs came from paid media this month, and at what cost? - What is client X's paid media CAC for the quarter? - Which channel brings the cheapest lead that turns into a sale? - Why did the qualification rate drop in August? - Which campaigns generate leads that sales discards? - Has March's media spend already paid off in sales? **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 ## Detailed ### The problem 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. ### Business rules 1. **A lead is a deal created in scope.** 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. (Avoid: Count every deal in every CRM pipeline.) 2. **The ladder is declared per client, as data.** 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. (Avoid: One MQL and SQL definition for every client, hardcoded.) 3. **Count the furthest stage reached.** Use stage history when it exists (hs_v2_date_entered_ 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. (Avoid: Count only deals sitting in the SQL stage today.) 4. **A win is a sale only in the sales pipeline.** 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. (Avoid: Treat every won status as a sale.) 5. **Derive stage status, do not read it from isClosed.** 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. (Avoid: Use isClosed to separate open from closed.) 6. **The source comes from a single cascade.** 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. (Avoid: Read the source only from the Source field or the last UTM.) 7. **Lead Ads link by id, not by UTM.** 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. (Avoid: Read utm_content as the ad for every client.) 8. **Cost per stage only with paid 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. (Avoid: Paid spend divided by all CRM leads.) 9. **Cohort and close views never add up.** 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. (Avoid: Cost per sale with sales by close date and the month's budget.) 10. **People are matched by a normalized key.** 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. (Avoid: Match by contact name or unnormalized email.) ### How each CRM says a deal moved forward The stage, win and loss signal changes from CRM to CRM. - **HubSpot · deals** → Date entered each stage (`hs_v2_date_entered_`) - **HubSpot · pipeline stages** → isClosed is unreliable, derive from the stage (`metadata.probability, stageId`) - **HubSpot · Lead object** → Qualified in pre-sales (`hs_pipeline_stage_category = QUALIFIED`) - **Kommo** → Global won and lost; history in the events stream (`status_id 142 and 143`) - **Pipedrive** → Won and lost are deal statuses, not stages (`status won / lost`) - **RD Station CRM** → Cohort by creation, sale by close (`created_at, closed_at, win`) - **Bitrix24 · leads** → Converted and discarded (`STATUS_SEMANTIC_ID S / F`) - **RD Station Marketing** → Encoded event source (`traffic_source encoded_`) ### Definitions | Metric | Definition | |---|---| | Lead | deal created in one of the client's in-scope pipelines, excluding tests, by creation date, in the month | | MQL | lead that reached the stage mapped as MQL (usually, entry into the pre-sales pipeline), by cohort | | SQL | lead that reached the SQL stage or was won in the pre-sales pipeline, by cohort | | Sale | deal won in the sales pipeline; in the cohort view by creation date, in the close view by win date | | CPL | paid spend for the month / paid-source leads created in the month | | Cost per MQL and per SQL | paid spend for the month / paid-source MQLs or SQLs from the month's cohort | | Media CAC | paid spend for the period / paid-source sales from the period's cohort | | Qualification rate | SQL / leads from the same cohort, by client and channel | ### Pitfalls - **A funnel that shrinks backwards.** 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. How to avoid: Count creation and the furthest stage, not the pipeline the deal is in today. - **Every stage closed.** In several clients' HubSpot portals, isClosed was true on every stage, including the top ones, and thousands of open deals showed up as closed. How to avoid: Derive open, won and lost from the stage. - **A win that was not a sale.** 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. How to avoid: A sale requires the sales pipeline and a win. - **Imported base counted as leads.** 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. How to avoid: Separate imports, integrations and reactivation before counting leads. - **Leads with no source that had a source.** 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. How to avoid: Decode encoded_ before classifying a lead as having no source. - **CRM versus platform.** 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. How to avoid: Leads, MQLs and sales by channel come from the CRM; cost and clicks come from the platform. Validated on real data: In Nekt's own HubSpot, every deal stage comes with metadata.isClosed set to true, including stages with a probability between 0 and 1, which are open. Anyone who separates open from closed using that field closes the entire funnel. ### Acceptance test - [ ] 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. ### Context document ```text 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: - : in-scope pipelines are and ; is reactivation and stays out. - : MQL = entry into the pipeline; SQL = won in the pre-sales pipeline. - : source "Desconhecido" (unknown) counts as digital, but not as paid. ``` ### Variations - **Best creative by stage:** With the source ad on every deal, rank creatives by SQLs and sales, not by leads. - **Funnel by unit:** The same ladder broken down by the client's product line, unit or clinic. - **WhatsApp funnel:** Conversation started at the top, with qualification coming from the customer service tool.