Skip to main content

​
The problem

In Post 02, 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

36% without attribution

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.

110× multiplier

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.

573 vs 4,372 runs

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

​
Variations

See which individual customers have the largest gap between CRM deal value and real recurring revenue:
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:
-- 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:
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.

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

04 · Pipeline Overview

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