Как посчитать Lead Velocity Rate в SQL

Зачем LVR

LVR = % growth qualified leads MoM. Jason Lemkin (SaaStr): «LVR predicts future revenue». Если LVR 10%/month → revenue grows ~10% в 90 days (lag period).

Формула

LVR = (qualified_leads_this_month - qualified_leads_last_month) / qualified_leads_last_month × 100%

Базовый расчёт

WITH monthly_leads AS (
    SELECT
        DATE_TRUNC('month', qualified_at) AS month,
        COUNT(*) AS qualified_leads
    FROM leads
    WHERE qualified = TRUE
      AND qualified_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    month,
    qualified_leads,
    LAG(qualified_leads) OVER (ORDER BY month) AS prev_month,
    (qualified_leads - LAG(qualified_leads) OVER (ORDER BY month))::NUMERIC * 100
    / NULLIF(LAG(qualified_leads) OVER (ORDER BY month), 0) AS lvr_pct
FROM monthly_leads
ORDER BY month;

По source

SELECT
    source,
    DATE_TRUNC('month', qualified_at) AS month,
    COUNT(*) AS leads,
    LAG(COUNT(*)) OVER (PARTITION BY source ORDER BY DATE_TRUNC('month', qualified_at)) AS prev_month_leads,
    (COUNT(*) - LAG(COUNT(*)) OVER (PARTITION BY source ORDER BY DATE_TRUNC('month', qualified_at)))::NUMERIC * 100
    / NULLIF(LAG(COUNT(*)) OVER (PARTITION BY source ORDER BY DATE_TRUNC('month', qualified_at)), 0) AS source_lvr_pct
FROM leads
WHERE qualified = TRUE
  AND qualified_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY source, DATE_TRUNC('month', qualified_at)
ORDER BY source, month;

Channels с consistent positive LVR — scale spend. Negative — investigate.

LVR vs Revenue Growth

LVR predictive (lead → revenue lag = sales cycle):

WITH lvr_monthly AS (
    SELECT
        DATE_TRUNC('month', qualified_at) AS month,
        COUNT(*) AS leads
    FROM leads
    WHERE qualified = TRUE
    GROUP BY 1
),
revenue_monthly AS (
    SELECT
        DATE_TRUNC('month', closed_at) AS month,
        SUM(acv) AS revenue
    FROM closed_won_deals
    GROUP BY 1
),
joined AS (
    SELECT
        l.month AS lead_month,
        l.leads,
        r.revenue AS revenue_90d_later
    FROM lvr_monthly l
    JOIN revenue_monthly r ON r.month = l.month + INTERVAL '3 months'
)
SELECT
    lead_month,
    leads,
    revenue_90d_later,
    CORR(leads::NUMERIC, revenue_90d_later::NUMERIC) OVER () AS correlation
FROM joined;

LVR этого месяца ~ revenue через 90 days.

Trend

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', qualified_at) AS month,
        COUNT(*) AS leads
    FROM leads
    WHERE qualified = TRUE
      AND qualified_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    month,
    leads,
    (leads - LAG(leads) OVER (ORDER BY month))::NUMERIC * 100 / NULLIF(LAG(leads) OVER (ORDER BY month), 0) AS lvr,
    -- 3-month avg для smoothing
    AVG((leads - LAG(leads) OVER (ORDER BY month))::NUMERIC * 100 / NULLIF(LAG(leads) OVER (ORDER BY month), 0))
    OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS lvr_3m_avg
FROM monthly;

Частые ошибки

Ошибка 1. Qualified vs raw leads. Raw leads — vanity. Qualified — predictive. Use MQL / SQL.

Ошибка 2. Definition of «qualified». Changed → LVR jumps. Stable definition critical.

Ошибка 3. Seasonality. December → leads drop holidays. YoY > MoM для seasonal businesses.

Ошибка 4. LVR negative — emergency? One bad month — noise. Sustained negative — investigate.

Ошибка 5. Channel mix changes. Pause one channel → LVR drops. Looks bad, but intentional.

Связанные темы

• Как посчитать win rate в SQL
• Как посчитать sales cycle length в SQL
• Как посчитать MQL to SQL conversion в SQL
• Как посчитать pipeline coverage в SQL

FAQ

Какой LVR ok?

10-20% MoM — healthy. 30%+ — hypergrowth. 0 — stagnant. Negative — declining demand.

LVR vs MoM revenue growth?

LVR — leading indicator (90 days lag). Revenue — lagging.

Why focus on LVR?

Sees demand pipeline before money arrives. Marketing performance signal.

MQL vs SQL для LVR?

SQL более qualified. MQL — earlier indicator. Track both.

LVR per quarter?

Yes — smoother. MoM noisy.