Skip to main content

​
The problem

In Post 02 and Post 03, we connected the data sources and answered where revenue comes from, how much each channel costs, and what the real ROI looks like. All useful. But still scattered across queries and one-off analyses. What was missing: a single view the entire team can open and understand. Sales, product, marketing, finance. Everyone reading the same number, in the same place.

​
What we did

We mapped the full conversion pipeline in HubSpot, from the first stage (workspace created) through every activation milestone to deal won. The key technique: instead of counting how many deals are currently at each stage (a snapshot), we used HubSpot’s hs_v2_date_entered_* fields to count how many deals ever passed through each stage. This gives a cumulative funnel view, which is far more useful for understanding conversion. We also extracted the average time deals spend at each stage (for won deals only), and built a monthly evolution showing deals created, converted, and lost over time.

​
What we found

The cliff

The biggest drop in the funnel sits between “Cloud Set Up” and “First Source Added”. 55% of users stop there. They create a workspace, configure the infrastructure, but never connect a data source.

After activation, 70% convert

Of the users who connected a source and ran their first pipeline successfully, nearly 70% became paying customers. The hard part is getting past the activation stage.

45.7 days to convert

Average time from workspace creation to deal won. The longest stage is “First Successful Run” at 12.9 days, where the user decides whether the product solves their problem.
Revenue values are indexed to avoid exposing absolute numbers. The practical effect is the same.

​
The takeaway

A deal that has been sitting in a stage for 30 days when the average for that stage is 4 days probably needs attention. Without this view, nobody would know. This is Stage 01 of the maturity model: visibility. The foundation for every decision that comes after.

​
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 analysis automatically. ⬇ Download Skill file (.md)

​
SQL — Pipeline overview

Discover all pipelines and their deal counts: 
SELECT
    p.pipelineid,
    p.label AS pipeline_label,
    COUNT(d.id) AS total_deals,
    SUM(CASE WHEN d.properties.hs_is_closed_won = 'true' THEN 1 ELSE 0 END) AS won,
    SUM(CASE WHEN d.properties.hs_is_closed_lost = 'true' THEN 1 ELSE 0 END) AS lost,
    ROUND(100.0 * SUM(CASE WHEN d.properties.hs_is_closed_won = 'true' THEN 1 ELSE 0 END)
          / NULLIF(COUNT(d.id), 0), 1) AS win_rate_pct
FROM `nekt_raw.hubspot_deal_pipelines` p
LEFT JOIN `nekt_raw.hubspot_deals` d
    ON d.properties.pipeline = p.pipelineid
GROUP BY 1, 2
ORDER BY total_deals DESC

​
SQL — Conversion funnel (cumulative)

Count how many deals ever entered each stage using hs_v2_date_entered_* fields. Replace the stage IDs with the ones from your HubSpot account (use the pipeline overview query above to discover them): 
SELECT 'Stage 1' AS stage, 0 AS stage_order,
    COUNT(DISTINCT CASE WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN id END) AS deals_entered
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'

UNION ALL
SELECT 'Stage 2', 1,
    COUNT(DISTINCT CASE WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN id END)
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'

-- Repeat for each stage in your pipeline

ORDER BY stage_order

​
SQL — Time in stage (won deals)

Average days each won deal spent at each stage. Replace stage IDs with yours: 
SELECT
    ROUND(AVG(CAST(properties.hs_v2_cumulative_time_in_STAGE1ID AS FLOAT64) / 86400000), 1) AS days_stage_1,
    ROUND(AVG(CAST(properties.hs_v2_cumulative_time_in_STAGE2ID AS FLOAT64) / 86400000), 1) AS days_stage_2,
    -- Repeat for each stage
    ROUND(AVG(CAST(properties.days_to_close AS FLOAT64)), 1) AS avg_total_days_to_close
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'true'

​
SQL — Monthly evolution

SELECT
    SUBSTR(properties.createdate, 1, 7) AS month,
    COUNT(*) AS deals_created,
    SUM(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 ELSE 0 END) AS won,
    SUM(CASE WHEN properties.hs_is_closed_lost = 'true' THEN 1 ELSE 0 END) AS lost,
    ROUND(SUM(CASE WHEN properties.hs_is_closed_won = 'true'
              THEN CAST(properties.amount AS FLOAT64) ELSE 0 END), 2) AS won_revenue
FROM `nekt_raw.hubspot_deals`
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.createdate >= '2025-04-01'
GROUP BY 1
ORDER BY 1

​
Variations

Add to the WHERE clause of any query above to see the funnel for a specific channel:
AND properties.custom_source = 'Website'
Or group by source to compare funnels across channels:
-- Add to SELECT: properties.custom_source AS source
-- Add to GROUP BY: properties.custom_source
Find deals that have been sitting in their current stage longer than expected:
SELECT
    properties.dealname,
    properties.dealstage,
    properties.custom_source,
    TRY_CAST(properties.amount AS DOUBLE) AS amount,
    properties.hs_v2_date_entered_current_stage,
    DATE_DIFF('day',
        TRY_CAST(properties.hs_v2_date_entered_current_stage AS TIMESTAMP),
        CURRENT_TIMESTAMP
    ) AS days_in_current_stage
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
  AND properties.hs_is_closed_won = 'false'
  AND DATE_DIFF('day',
        TRY_CAST(properties.hs_v2_date_entered_current_stage AS TIMESTAMP),
        CURRENT_TIMESTAMP
      ) > 30
ORDER BY days_in_current_stage DESC
Break down the conversion funnel by the month deals were created to see if newer cohorts convert better or worse:
SELECT
    SUBSTR(properties.createdate, 1, 7) AS cohort_month,
    COUNT(DISTINCT CASE WHEN properties.hs_v2_date_entered_STAGE1ID IS NOT NULL THEN id END) AS stage_1,
    COUNT(DISTINCT CASE WHEN properties.hs_v2_date_entered_STAGE2ID IS NOT NULL THEN id END) AS stage_2,
    -- Repeat for each stage
    COUNT(DISTINCT CASE WHEN properties.hs_v2_date_entered_WONID IS NOT NULL THEN id END) AS won
FROM nekt_raw.hubspot_deals
WHERE properties.pipeline = 'YOUR_PIPELINE_ID'
GROUP BY 1
ORDER BY 1

​
Implementation notes

  • Pipeline and stage IDs (803892128, 1182573922, etc.) 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 (how many passed through) rather than point-in-time counting (how many are there now).
  • hs_v2_cumulative_time_in_* is in milliseconds. Divide by 86,400,000 to convert to days.
  • properties.dealstage shows the current stage. For conversion funnels, use hs_v2_date_entered_* instead.
  • amount and days_to_close are varchars. Use TRY_CAST(... AS DOUBLE) or CAST(... AS FLOAT64).
  • Deals can skip stages (e.g., going directly from “First Source” to “Won”), so cumulative counts may not decrease linearly.

​
Next up

Now that there’s a clear view of what’s happening in the pipeline, the next step is diagnosing why. Which acquisition channels produce leads that advance through the funnel, and which ones stall out?

05 · Funnel + Attribution

Stage 02 · Diagnosis — Cross-referencing acquisition source with conversion rate by stage. When Marketing and Sales stop arguing about MQLs.