Skip to main content

​
The problem

In Post 04, we built the full pipeline view: deals per stage, conversion rate between stages, monthly evolution. Everyone reading the same number. That view works. But an aggregated funnel hides a problem that only shows up when you cross the data with the source of each deal. A 15% win rate across the funnel can mean two very different things:
  • One channel converting at 50% and another at 2%, averaging out
  • All channels converting at roughly the same rate
The first case has a clear next step (double down on the channel that converts, fix or cut the one that doesn’t). The second has a different problem (the funnel itself, not the source). You can’t tell which is which from the aggregate.

​
What we did

We grouped deals by acquisition channel and crossed each one with the funnel stages, calculating the percentage of deals from each channel that reached each stage. Two technical decisions made the analysis usable:
  1. Use HubSpot’s automatic source attribution, not manual fields. We started with custom_source (filled by the team) and only 16% of deals had it set. Switching to hs_analytics_source gave us 100% coverage, since HubSpot fills it automatically based on tracking.
  2. Break OFFLINE into its sub-types. The OFFLINE value of hs_analytics_source groups three very different things: API integrations, batch imports, and manually-created deals. Without splitting them, opposite behaviors get bundled into the same row.
The result is a heatmap: rows are channels, columns are funnel stages, cells show how many deals from each channel reached each stage. Where a row collapses early, the channel has an activation problem. Where it collapses late, the channel qualifies well but doesn’t close.

​
What we found

The Nekt funnel is product-led, but the same diagnosis applies to a sales-led funnel (Lead → MQL → SQL → Opportunity → Won). The pattern of “different channels behave completely differently in the same funnel” holds in both cases.

The break point

For organic signups, 69% of deals stop at the same stage. Not spread across the funnel. One specific cliff. Without channel breakdown, this looks like generic friction.

22× gap between channels

Same funnel, same product. Some channels converted at 57%, others at 2.6%. The aggregate win rate (15%) hid both extremes.

Volume vs activation

Two channels brought 70 deals between them. Five reached the activation stage. One closed. High volume at the top doesn’t mean high quality through the funnel.
The findings change when you frame the funnel by channel. A channel can look strong in deal count and weak in conversion, or the opposite. Both matter, and you need both views to make budget decisions.

​
The takeaway

A single funnel hides multiple funnels. Channels behave differently in the same pipeline because the users they bring have different intent, different context, and different reasons to convert. The diagnostic question changes from “what’s our conversion rate” to “what’s our conversion rate, by channel, at each stage.” That’s where the real next steps come from. This is Stage 02 of the maturity model: diagnosis. Knowing where the funnel breaks, and for whom.

​
The Skill

Download the file below and add it to your Claude Skills. It will query your HubSpot pipeline data at Nekt and generate the funnel-by-channel analysis automatically. ⬇ Download Skill file (.md)

​
SQL — Channel × stage heatmap

Group deals by acquisition channel and count how many reached each funnel stage. Replace the pipeline ID and stage IDs with the ones from your HubSpot account (use the pipeline overview query to discover them): 
SELECT
    CASE
        WHEN properties.hs_analytics_source = 'OFFLINE'
             AND properties.hs_analytics_source_data_1 = 'INTEGRATION' THEN 'Self-Serve / API'
        WHEN properties.hs_analytics_source = 'OFFLINE'
             AND properties.hs_analytics_source_data_1 IN ('IMPORT', 'CRM_UI') THEN 'Sales-Assisted'
        WHEN properties.hs_analytics_source = 'DIRECT_TRAFFIC' THEN 'Direct Traffic'
        WHEN properties.hs_analytics_source IN ('SOCIAL_MEDIA', 'PAID_SOCIAL') THEN 'Social'
        WHEN properties.hs_analytics_source = 'PAID_SEARCH' THEN 'Paid Search'
        WHEN properties.hs_analytics_source = 'ORGANIC_SEARCH' THEN 'Organic Search'
        WHEN properties.hs_analytics_source IN ('OTHER_CAMPAIGNS', 'REFERRALS') THEN 'Campaigns/Referrals'
        ELSE properties.hs_analytics_source
    END AS channel,
    COUNT(*) AS total,
    COUNT(CASE WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1 END) AS reached_stage_1,
    COUNT(CASE WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 1 END) AS reached_stage_2,
    COUNT(CASE WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 1 END) AS reached_stage_3,
    COUNT(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 END) AS won,
    ROUND(100.0 * COUNT(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 END)
          / NULLIF(COUNT(*), 0), 1) AS win_rate_pct
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
GROUP BY 1
ORDER BY total DESC

​
SQL — Stage-to-stage conversion by channel group

Compare how each channel progresses between consecutive stages. Useful to spot which channels stall at qualification vs. which stall at closing: 
WITH funnel AS (
    SELECT
        CASE
            WHEN properties.hs_analytics_source_data_1 = 'INTEGRATION' THEN 'Self-Serve / API'
            WHEN properties.hs_analytics_source_data_1 IN ('IMPORT', 'CRM_UI') THEN 'Sales-Assisted'
        END AS channel,
        COUNT(*) AS total,
        COUNT(CASE WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN 1 END) AS s1,
        COUNT(CASE WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN 1 END) AS s2,
        COUNT(CASE WHEN properties.hs_v2_date_entered_STAGE3ID IS NOT NULL THEN 1 END) AS s3,
        COUNT(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 END) AS won
    FROM `nekt_raw.hubspot_deals`
    WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
      AND properties.hs_analytics_source = 'OFFLINE'
    GROUP BY 1
)
SELECT
    channel, total, s1, s2, s3, won,
    ROUND(100.0 * s2 / NULLIF(s1, 0), 1) AS s1_to_s2_pct,
    ROUND(100.0 * s3 / NULLIF(s2, 0), 1) AS s2_to_s3_pct,
    ROUND(100.0 * won / NULLIF(total, 0), 1) AS win_rate_pct
FROM funnel
WHERE channel IS NOT NULL

​
SQL — Monthly conversion trend by channel

Track whether the conversion rate of a specific channel is improving or worsening over time: 
SELECT
    SUBSTR(properties.createdate, 1, 7) AS month,
    COUNT(*) AS deals_created,
    COUNT(CASE WHEN properties.hs_v2_date_entered_KEYSTAGEID IS NOT NULL THEN 1 END) AS reached_key_stage,
    ROUND(100.0 * COUNT(CASE WHEN properties.hs_v2_date_entered_KEYSTAGEID IS NOT NULL THEN 1 END)
          / NULLIF(COUNT(*), 0), 1) AS conversion_rate_pct,
    COUNT(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 END) AS won
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_analytics_source = 'YOUR_CHANNEL'  -- e.g., 'DIRECT_TRAFFIC'
  AND properties.createdate >= FORMAT_DATE('%Y-%m-01', DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH))
GROUP BY 1
ORDER BY 1

​
Variations

See which pages are driving the highest-quality traffic:
SELECT
    properties.hs_analytics_source_data_1 AS landing_page,
    COUNT(*) AS deals,
    COUNT(CASE WHEN properties.hs_v2_date_entered_KEYSTAGEID IS NOT NULL THEN 1 END) AS reached_key_stage,
    COUNT(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 END) AS won
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_analytics_source = 'DIRECT_TRAFFIC'
GROUP BY 1
ORDER BY deals DESC
Compare conversion across paid campaigns:
SELECT
    properties.deal_utm_source AS utm_source,
    properties.deal_utm_medium AS utm_medium,
    properties.deal_utm_campaign AS utm_campaign,
    COUNT(*) AS deals,
    COUNT(CASE WHEN properties.hs_v2_date_entered_KEYSTAGEID IS NOT NULL THEN 1 END) AS reached_key_stage,
    ROUND(100.0 * COUNT(CASE WHEN properties.hs_v2_date_entered_KEYSTAGEID IS NOT NULL THEN 1 END)
          / NULLIF(COUNT(*), 0), 1) AS conversion_rate_pct,
    COUNT(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 END) AS won
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.deal_utm_source IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY deals DESC
List deals from a specific channel that never reached a critical stage so the team can reach out:
SELECT
    properties.dealname,
    properties.dealstage,
    properties.createdate,
    DATE_DIFF('day',
        TRY_CAST(properties.hs_v2_date_entered_current_stage AS TIMESTAMP),
        CURRENT_TIMESTAMP
    ) AS days_in_stage
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_analytics_source = 'YOUR_CHANNEL'
  AND properties.hs_v2_date_entered_KEYSTAGEID IS NULL
  AND properties.hs_is_closed_won = 'false'
ORDER BY days_in_stage DESC

​
Implementation notes

  • Why hs_analytics_source instead of custom_source? custom_source is filled manually and tends to have low coverage (in our case, only 16% of deals had it set). hs_analytics_source is filled automatically by HubSpot for 100% of deals, based on tracking behavior. For channel-by-stage analysis, coverage matters more than precision.
  • OFFLINE needs to be split. The OFFLINE value of hs_analytics_source groups three different cases: INTEGRATION (deal created via API), IMPORT (batch import, usually historical), and CRM_UI (manually created by a salesperson). Without splitting them, opposite behaviors get bundled into the same row.
  • Imported deals enter the funnel at later stages. Deals registered manually or via batch import are often created already at intermediate stages, skipping the early ones. Low percentages in early stages for those channels don’t mean drop-off, they just didn’t go through.
  • The methodology applies to any funnel. This walkthrough uses our PLG funnel (workspace, setup, first source, first run, won) as the example, but the same channel × stage cross works for traditional funnels (Lead, MQL, SQL, Opportunity, Won). Only the stage IDs change.
  • Pipeline and stage IDs are specific to each HubSpot account. Use the pipeline overview query to discover yours.
  • hs_v2_date_entered_* records when a deal entered each stage, even if it has since moved forward. This enables cumulative counting across the funnel.

​
Next up

With a clear diagnosis of where the funnel breaks and for which channels, the next step is using these conversion patterns to build the first lead scoring model. No machine learning required, just the historical data we already have.

06 · Lead Scoring

Stage 03 · Predictive — Lead prioritization based on historical conversion rate, not gut feeling.