> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Funnel Analysis by Channel

> Cross acquisition channel with funnel stage to find where deals stall, and which sources bring users that actually convert. Works for any funnel: PLG, sales-led, or hybrid.

## The problem

In [Post 04](/use-cases/revops-series/pipeline-overview), 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.

<CardGroup cols={3}>
  <Card title="The break point" icon="arrow-trend-down">
    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.
  </Card>

  <Card title="22× gap between channels" icon="arrows-left-right">
    Same funnel, same product. Some channels converted at **57%**, others at **2.6%**. The aggregate win rate (15%) hid both extremes.
  </Card>

  <Card title="Volume vs activation" icon="filter-circle-xmark">
    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.**
  </Card>
</CardGroup>

<Tip>
  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.
</Tip>

***

## 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.

<a href="https://gist.github.com/pedroceriotti/70172fd3da84a9709fc99d144cf4dc77" download="funnel-by-channel.md" target="_blank" style={{display: 'inline-flex', alignItems: 'center', gap: '8px', padding: '10px 18px', background: '#f0fdf4', border: '1px solid #bbf7d0', borderRadius: '16px', color: '#16a34a', fontWeight: 600, fontSize: '0.9em', textDecoration: 'none'}}>
  ⬇ Download Skill file (.md)
</a>

### 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](/use-cases/revops-series/pipeline-overview) to discover them):

<Tabs>
  <Tab title="Nekt Express / GCP (BigQuery)">
    ```sql theme={null}
    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
    ```
  </Tab>

  <Tab title="AWS (Amazon Athena)">
    ```sql theme={null}
    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
    ```
  </Tab>
</Tabs>

### 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:

<Tabs>
  <Tab title="Nekt Express / GCP (BigQuery)">
    ```sql theme={null}
    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
    ```
  </Tab>

  <Tab title="AWS (Amazon Athena)">
    ```sql theme={null}
    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
    ```
  </Tab>
</Tabs>

### SQL — Monthly conversion trend by channel

Track whether the conversion rate of a specific channel is improving or worsening over time:

<Tabs>
  <Tab title="Nekt Express / GCP (BigQuery)">
    ```sql theme={null}
    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
    ```
  </Tab>

  <Tab title="AWS (Amazon Athena)">
    ```sql theme={null}
    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 >= DATE_FORMAT(DATE_ADD('month', -12, CURRENT_DATE), '%Y-%m-01')
    GROUP BY 1
    ORDER BY 1
    ```
  </Tab>
</Tabs>

### Variations

<AccordionGroup>
  <Accordion title="Drill down on direct traffic by landing page">
    See which pages are driving the highest-quality traffic:

    ```sql theme={null}
    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
    ```
  </Accordion>

  <Accordion title="Detail paid campaigns by UTM">
    Compare conversion across paid campaigns:

    ```sql theme={null}
    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
    ```
  </Accordion>

  <Accordion title="Find deals stuck before a key stage">
    List deals from a specific channel that never reached a critical stage so the team can reach out:

    ```sql theme={null}
    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
    ```
  </Accordion>
</AccordionGroup>

## Watch the walkthrough (PT-BR)

<iframe width="560" height="315" src="https://www.youtube.com/embed/fEw1SExc4b0?si=o6puXwZZaeYLVaKu" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen />

### 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](/use-cases/revops-series/pipeline-overview) 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.

<Card title="06 · Lead Scoring" icon="ranking-star" href="/use-cases/revops-series/lead-scoring">
  **Stage 03 · Predictive** — Prioritize open deals using 6 factors from CRM history. No ML, just SQL.
</Card>