Skip to main content

​
The problem

Monday morning. The alignment meeting starts.
  • Sales says: $340k in the pipeline.
  • Head of Marketing says: $290k in qualified leads.
  • Finance says: $318k in revenue.
Three numbers, three tools, three different truths. Two hours go by trying to reconcile. No one can explain the difference, and the meeting ends without the decision it was supposed to produce. This is a data architecture problem. Each tool captures a slice of reality. The CRM sees deals. Marketing sees leads. Finance sees what already closed. The result: every week someone spends hours building a spreadsheet to answer questions that should take seconds.

​
What we did

We connected HubSpot to our data warehouse and ran the first real analysis: where each deal comes from, what the conversion rate is per channel, and how much revenue each source actually generated. No filters. No time range. A full view of the historical pipeline.

​
What we found

Best win rate

Partner Referral — 50% win rate. The most efficient channel, and likely the least prioritized in terms of time investment.

Highest volume, lowest return

Conferences/Events — The channel with the most leads and the worst conversion rate. Every event generates excitement about volume, but the data tells a different story. Leads are not revenue.

Warning sign

Paid — 4% win rate. Once you factor in acquisition cost (covered in later posts), this channel likely doesn’t pay for itself. Before this analysis, no one had that number on the table.
Revenue values are indexed (best-performing channel = 100) to avoid exposing absolute numbers. The practical effect is the same — you see which channels generate the most revenue relative to each other.

​
Watch the walkthrough

​
The takeaway

None of these conclusions required AI. They required having the data unified for the first time. This is what exiting Stage 00 delivers: the first reliable answer to the most basic RevOps question — where does our revenue actually come from? Not prediction. Not AI. Just data.

​
The Skill

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

​
SQL

If you prefer to run the query directly in the Explorer or adapt it to your schema: 
SELECT
  properties.custom_source AS channel,
  COUNT(*) AS total_deals,
  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,
  SUM(CASE WHEN properties.hs_is_closed_won = 'false'
       AND properties.hs_is_closed_lost = 'false' THEN 1 ELSE 0 END) AS open,
  ROUND(
    100.0 * SUM(CASE WHEN properties.hs_is_closed_won = 'true' THEN 1 ELSE 0 END) / COUNT(*), 1
  ) AS win_rate_pct,
  ROUND(SUM(CAST(properties.amount AS FLOAT64)), 0) AS pipeline_total,
  ROUND(SUM(CASE WHEN properties.hs_is_closed_won = 'true'
       THEN CAST(properties.amount AS FLOAT64) ELSE 0 END), 0) AS revenue_won,
  ROUND(AVG(CAST(properties.days_to_close AS FLOAT64)), 1) AS avg_days_to_close
FROM `nekt_raw.hubspot_deals`
WHERE properties.custom_source IS NOT NULL
  AND properties.custom_source != ''
GROUP BY 1
ORDER BY revenue_won DESC

​
Variations

WHERE properties.custom_source IS NOT NULL
  AND SUBSTR(properties.createdate, 1, 7) >= '2025-01'

WHERE properties.pipeline = 'default'

-- Add to SELECT:
properties.dealstage AS current_stage
-- Add to GROUP BY:
GROUP BY 1, properties.dealstage

​
Implementation notes

  • properties is a struct — fields are accessed directly without JSON parsing.
  • amount is a varchar — use TRY_CAST(... AS DOUBLE) to avoid errors on null values.
  • hs_is_closed_won and hs_is_closed_lost are strings ('true' / 'false'), not booleans.
  • Deals imported from Pipedrive appear with custom_source = NULL — excluded by the filter.

​
Next up

Now that there’s visibility into where revenue comes from, the next step is turning pipeline data into a dashboard the whole team reads the same way — and stop debating which number is right.

03 · Pipeline Overview

Stage 01 · Visibility — Deals by stage, conversion rates, and revenue by period. The first panel everyone reads the same way.