> ## 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 Attribution
> The full revenue cycle: CRM + billing + ads. LTV by channel, acquisition cost, and real ROI per source.
## The problem
In [Post 02](/use-cases/revops-series/revenue-source-audit), we answered where deals come from and which channels convert best. Useful — but incomplete.
The CRM tracks deal value at the moment of closing. Billing tracks what the customer actually pays over time. These are rarely the same number. Until you connect the two, you can't answer the real question: **which channels generate the most recurring revenue?**
***
## What we did
We connected two additional sources — Stripe (billing) and ad platforms (Meta Ads, Google Ads, LinkedIn Ads) — and crossed them with HubSpot deal data.
The join path: HubSpot deal won → product organization (via workspace slug) → Stripe customer → charges. Ad spend is aggregated by platform and mapped to the `Paid` acquisition channel.
***
## What we found
35 of 97 paying customers have no acquisition channel in the CRM. They likely entered before the CRM was in place — but they represent **62% of all recurring revenue**. Any budget decision without this data is a guess.
One customer entered as a \$745 deal in the CRM. In Stripe, they've generated over \$82k in recurring revenue. The deal value in HubSpot? Still \$745 today. CRM deal values are unreliable proxies for actual revenue.
Partner Referral has 10 clients averaging **573 runs** in the product. LinkedIn Organic has 11 clients with **4,372 runs**. The channel that converts best isn't always the one that retains best.
The pattern: channels that seem "slow" — organic content, referrals, founder networks — are the ones generating the most recurring revenue per customer. The "fast" channels generate impressions but with lower conversion and retention.
Revenue values are indexed 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
The CRM tells you who converted. Billing tells you who actually pays. Ads tell you how much you paid to acquire a customer. When you connect the three, the ranking changes — channels that looked mediocre by deal value turn out to be the most valuable by recurring revenue, and vice versa.
This is what completing Stage 00 looks like: **the full picture of where revenue actually comes from.**
***
## The Skill
Download the file below and add it to your Claude Skills. It will query your HubSpot and Stripe data at Nekt and generate the analysis automatically.
⬇ Download Skill file (.md)
### SQL — Revenue Attribution (CRM × Billing)
If you prefer to run the query directly in the Explorer or adapt it to your schema:
```sql theme={null}
WITH won_deals AS (
SELECT DISTINCT
properties.workspaceslug AS slug,
COALESCE(NULLIF(properties.custom_source, ''), '(no attribution)') AS source,
CAST(properties.amount AS FLOAT64) AS deal_amt
FROM `nekt_raw.hubspot_deals`
WHERE properties.hs_is_closed_won = 'true'
AND properties.workspaceslug IS NOT NULL
AND properties.workspaceslug != ''
),
org_mapping AS (
SELECT slug, stripe_customer_id
FROM `nekt_trusted.app_postgres_organizations_organization`
WHERE stripe_customer_id IS NOT NULL
AND stripe_customer_id != ''
AND slug IS NOT NULL
),
matched AS (
SELECT w.slug, w.source, w.deal_amt, o.stripe_customer_id
FROM won_deals w
JOIN org_mapping o ON w.slug = o.slug
),
stripe_rev AS (
SELECT customer,
COUNT(*) AS charges,
ROUND(SUM(amount) / 100.0, 2) AS revenue
FROM `nekt_trusted.stripe_charges`
WHERE status = 'succeeded' AND paid = true
GROUP BY 1
)
SELECT
m.source AS channel,
COUNT(DISTINCT m.slug) AS customers,
ROUND(SUM(m.deal_amt), 2) AS crm_deal_value,
COALESCE(SUM(s.charges), 0) AS stripe_charges,
ROUND(COALESCE(SUM(s.revenue), 0), 2) AS stripe_revenue,
ROUND(COALESCE(SUM(s.revenue), 0) / NULLIF(COUNT(DISTINCT m.slug), 0), 2) AS avg_ltv,
ROUND(COALESCE(SUM(s.revenue), 0) / NULLIF(SUM(m.deal_amt), 0), 1) AS multiplier
FROM matched m
LEFT JOIN stripe_rev s ON m.stripe_customer_id = s.customer
GROUP BY 1
ORDER BY stripe_revenue DESC
```
```sql theme={null}
WITH won_deals AS (
SELECT DISTINCT
properties.workspaceslug AS slug,
COALESCE(NULLIF(properties.custom_source, ''), '(no attribution)') AS source,
TRY_CAST(properties.amount AS DOUBLE) AS deal_amt
FROM nekt_raw.hubspot_deals
WHERE properties.hs_is_closed_won = 'true'
AND properties.workspaceslug IS NOT NULL
AND properties.workspaceslug != ''
),
org_mapping AS (
SELECT slug, stripe_customer_id
FROM nekt_trusted.app_postgres_organizations_organization
WHERE stripe_customer_id IS NOT NULL
AND stripe_customer_id != ''
AND slug IS NOT NULL
),
matched AS (
SELECT w.slug, w.source, w.deal_amt, o.stripe_customer_id
FROM won_deals w
JOIN org_mapping o ON w.slug = o.slug
),
stripe_rev AS (
SELECT customer,
COUNT(*) AS charges,
ROUND(SUM(amount) / 100.0, 2) AS revenue
FROM nekt_trusted.stripe_charges
WHERE status = 'succeeded' AND paid = true
GROUP BY 1
)
SELECT
m.source AS channel,
COUNT(DISTINCT m.slug) AS customers,
ROUND(SUM(m.deal_amt), 2) AS crm_deal_value,
COALESCE(SUM(s.charges), 0) AS stripe_charges,
ROUND(COALESCE(SUM(s.revenue), 0), 2) AS stripe_revenue,
ROUND(COALESCE(SUM(s.revenue), 0) / NULLIF(COUNT(DISTINCT m.slug), 0), 2) AS avg_ltv,
ROUND(COALESCE(SUM(s.revenue), 0) / NULLIF(SUM(m.deal_amt), 0), 1) AS multiplier
FROM matched m
LEFT JOIN stripe_rev s ON m.stripe_customer_id = s.customer
GROUP BY 1
ORDER BY stripe_revenue DESC
```
### Variations
See which individual customers have the largest gap between CRM deal value and real recurring revenue:
```sql theme={null}
SELECT
w.source AS channel,
w.slug AS customer,
w.deal_amt AS deal_value_crm,
s.revenue AS stripe_revenue,
ROUND(s.revenue / NULLIF(w.deal_amt, 0), 1) AS multiplier
FROM matched w
LEFT JOIN stripe_rev s ON w.stripe_customer_id = s.customer
WHERE s.revenue > 0
ORDER BY s.revenue DESC
LIMIT 20
```
Cross with product metrics to see which channels produce customers that stick and use the product:
```sql theme={null}
-- Requires additional join with customer_metrics
-- Add to SELECT:
SUM(CASE WHEN cm.subscription_status = 'active' THEN 1 ELSE 0 END) AS billing_active,
SUM(CASE WHEN cm.subscription_status IN ('churned','canceled') THEN 1 ELSE 0 END) AS churned,
ROUND(AVG(cm.runs_count), 0) AS avg_runs,
ROUND(AVG(cm.sources_count), 1) AS avg_sources
```
Aggregate spend from Meta Ads, Google Ads, and LinkedIn Ads to calculate CAC and ROI for the Paid channel:
```sql theme={null}
WITH ads_spend AS (
SELECT 'Meta Ads' AS platform,
ROUND(SUM(spend), 2) AS total_spend
FROM nekt_raw.facebook_ads_mktadsinsights
UNION ALL
SELECT 'Google Ads' AS platform,
ROUND(SUM(cost_micros) / 1000000.0, 2) AS total_spend
FROM nekt_raw.google_ads_campaign_performance
UNION ALL
SELECT 'LinkedIn Ads' AS platform,
ROUND(SUM(cost_in_local_currency), 2) AS total_spend
FROM nekt_raw.linkedin_ads_campaign_analytics
)
SELECT platform, total_spend
FROM ads_spend
ORDER BY total_spend DESC
```
Map `custom_source = 'Paid'` to the aggregated spend. CAC = total\_spend / customers won via Paid. ROI = stripe\_revenue / total\_spend. For granular attribution (which platform generated which deal), cross UTMs (`deal_utm_source`) with campaign data.
## Watch the walkthrough (PT-BR)
### Implementation notes
* `amount` in Stripe is in cents (BIGINT) — divide by 100.
* `amount` in HubSpot is a varchar — use `TRY_CAST(... AS DOUBLE)` or `CAST(... AS FLOAT64)`.
* Stripe charges may include multiple currencies (BRL and USD) — the sum aggregates without currency conversion.
* Customers without `custom_source` in the CRM appear as "(no attribution)" — this is one of the main findings.
* Google Ads `cost_micros` is in micros (BIGINT) — divide by 1,000,000.
* The Ads → CRM channel mapping is by convention: `custom_source = 'Paid'` groups all paid media spend. For granular attribution, cross UTMs (`deal_utm_source`) with campaign data.
***
## Next up
With this foundation in place, we're out of the chaos stage. Next: the first pipeline dashboard the whole company reads the same way. Conversion funnel, time in stage, and monthly evolution.
**Stage 01 · Visibility** — Deals by stage, conversion rates, and revenue by period. The first dashboard everyone reads the same way.