Как посчитать Sales Cycle Length в SQL

Зачем Sales Cycle Length

Sales Cycle — calendar days от lead created до closed-won. Driver of capacity (deals per quarter × pipeline) и planning. Long cycle → need bigger pipeline coverage.

Формула

Sales Cycle Length (days) = closed_at - lead_created_at

Average + median + distribution — все нужны.

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

SELECT
    DATE_TRUNC('quarter', closed_at) AS quarter,
    COUNT(*) AS deals,
    AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400) AS avg_cycle_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400) AS median_cycle_days,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400) AS p90_cycle_days
FROM opportunities
WHERE outcome = 'won'
  AND closed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

По stages

Time in каждом stage funnel:

WITH stage_times AS (
    SELECT
        opp_id,
        stage,
        MIN(TIMESTAMP) AS stage_entered,
        LEAD(MIN(TIMESTAMP)) OVER (PARTITION BY opp_id ORDER BY MIN(TIMESTAMP)) AS next_stage_entered
    FROM stage_transitions
    GROUP BY opp_id, stage
)
SELECT
    stage,
    COUNT(*) AS deals,
    AVG(EXTRACT(EPOCH FROM (next_stage_entered - stage_entered)) / 86400) AS avg_days_in_stage
FROM stage_times
WHERE next_stage_entered IS NOT NULL
GROUP BY stage
ORDER BY MIN(stage_entered);

Top stage by avg_days_in_stage → bottleneck.

По segments

SELECT
    customer_segment,
    deal_size_bucket,  -- SMB/Mid/Enterprise
    COUNT(*) AS deals,
    AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400) AS avg_cycle_days,
    AVG(acv) AS avg_acv,
    AVG(acv) / NULLIF(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400), 0) AS revenue_per_day
FROM opportunities
WHERE outcome = 'won'
  AND closed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY customer_segment, deal_size_bucket
ORDER BY avg_cycle_days;

Revenue per day — efficient measure. SMB faster, smaller. Enterprise slower, bigger.

Cycle vs Deal Size

Усредняя, large deals = long cycle. Correlation:

SELECT
    CORR(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400, acv) AS r,
    COUNT(*) AS deals
FROM opportunities
WHERE outcome = 'won'
  AND closed_at >= CURRENT_DATE - INTERVAL '12 months';

r 0.4-0.6 typical (positive, moderate).

Velocity trend

SELECT
    DATE_TRUNC('month', closed_at) AS month,
    COUNT(*) AS deals,
    AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400) AS avg_cycle,
    AVG(acv) / NULLIF(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400), 0) AS dollars_per_day
FROM opportunities
WHERE outcome = 'won'
  AND closed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Sales velocity = $/day. Higher = faster sales operation.

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

Ошибка 1. Avg vs Median. One 18-month enterprise deal pulls avg. Median honest.

Ошибка 2. Lead vs Opportunity. Sales Cycle обычно opp_created → close. MQL → SQL → close longer.

Ошибка 3. Closed-Lost не counted. Only won deals — biased. Time-to-loss could be longer.

Ошибка 4. Pause / stalled deals. Open 200 days, paused for 100 — real working time 100 days.

Ошибка 5. Seasonal effects. Enterprise procurement Q4 — fast or slow? Compare like-for-like quarters.

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

• Как посчитать deal velocity в SQL
• Как посчитать win rate в SQL
• Как посчитать pipeline coverage в SQL
• Как посчитать deal size в SQL

FAQ

Какой Sales Cycle ok?

SMB: 7-30 days. Mid-Market: 30-90 days. Enterprise: 90-365+ days. Зависит от sector.

Reduce cycle как?

1. Better lead qualification. 2) Faster демо. 3) Pricing transparency. 4) Self-serve trial. 5) Procurement automation.

Lost deals в cycle?

Track «time to lost» отдельно. Same length как won? Different signal.

Stalled deal definition?

No activity 30+ days. Re-engage or close-lost.

Cycle vs win rate?

Trade-off часто. Short cycle, low win rate (rush). Long cycle, high win rate (careful qualification).