> ## 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.
# Revenue Source Audit
> Going from Chaos to Visibility — where each deal comes from, conversion rate by channel, and how much revenue each source actually generated.
## 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
**Partner Referral** — 50% win rate. The most efficient channel, and likely the least prioritized in terms of time investment.
**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.
**Paid** — 4% win rate. Once you factor in acquisition cost ([covered in the next post](/use-cases/revops-series/revenue-attribution)), this channel doesn't pay for itself — ROI of 0.4×. 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.
***
## 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:
```sql theme={null}
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
```
```sql theme={null}
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(TRY_CAST(properties.amount AS DOUBLE)), 0) AS pipeline_total,
ROUND(SUM(CASE WHEN properties.hs_is_closed_won = 'true'
THEN TRY_CAST(properties.amount AS DOUBLE) ELSE 0 END), 0) AS revenue_won,
ROUND(AVG(TRY_CAST(properties.days_to_close AS DOUBLE)), 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
```sql theme={null}
WHERE properties.custom_source IS NOT NULL
AND SUBSTR(properties.createdate, 1, 7) >= '2025-01'
```
```sql theme={null}
WHERE properties.pipeline = 'default'
```
```sql theme={null}
-- Add to SELECT:
properties.dealstage AS current_stage
-- Add to GROUP BY:
GROUP BY 1, properties.dealstage
```
## Watch the walkthrough (PT-BR)
### 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 connecting billing and ad spend to see the full picture — LTV by channel, acquisition cost, and real ROI per source.
**Stage 00 · Chaos (pt. 2)** — The full revenue cycle: CRM + billing + ads. Which channels actually pay for themselves?