Как посчитать deal aging в SQL

Зачем deal aging

Открытая сделка 180 days в pipeline — обычно мертва, sales rep не закрыл и не updated. Aging выявляет stagnant deals, которые reduces forecast accuracy. Чем дольше deal в open — тем меньше вероятность закрытия. Clean pipeline regularly.

Aging buckets

• 0-30 days — fresh
• 31-60 days — normal cycle
• 61-90 days — needs attention
• 91-180 days — stale
• 180+ days — likely dead

Aging в SQL

WITH open_deals AS (
    SELECT
        deal_id,
        deal_size,
        rep_id,
        EXTRACT(EPOCH FROM (CURRENT_DATE - created_at)) / 86400 AS days_open
    FROM deals
    WHERE status = 'open'
)
SELECT
    CASE
        WHEN days_open <= 30  THEN '0-30 days'
        WHEN days_open <= 60  THEN '31-60 days'
        WHEN days_open <= 90  THEN '61-90 days'
        WHEN days_open <= 180 THEN '91-180 days'
        ELSE '180+ days'
    END AS aging_bucket,
    COUNT(*) AS deals,
    SUM(deal_size) AS pipeline_value,
    SUM(deal_size) * 100.0 / SUM(SUM(deal_size)) OVER () AS pct_of_pipeline
FROM open_deals
GROUP BY 1
ORDER BY MIN(days_open);

Healthy: 80% pipeline в 0-90 days. Если 40%+ в 180+ — pipeline cleanup.

Застрявшие в стадии

Deal стоит в одной стадии больше нормы:

SELECT
    deal_id,
    current_stage,
    EXTRACT(EPOCH FROM (CURRENT_DATE - last_stage_change)) / 86400 AS days_in_stage,
    typical_days_in_stage AS benchmark
FROM open_deals_with_stage_history
WHERE EXTRACT(EPOCH FROM (CURRENT_DATE - last_stage_change)) / 86400
      > typical_days_in_stage * 2
ORDER BY days_in_stage DESC;

Deal в «proposal» 60 days, при benchmark 20 — стоит cold-call или close-lost.

Алерт по rep

SELECT
    rep_id,
    COUNT(*) AS open_deals,
    COUNT(*) FILTER (WHERE days_open > 90) AS stale_deals,
    SUM(deal_size) FILTER (WHERE days_open > 90) AS stale_value,
    COUNT(*) FILTER (WHERE days_open > 90) * 100.0 / NULLIF(COUNT(*), 0) AS stale_pct
FROM open_deals_with_age
GROUP BY rep_id
HAVING COUNT(*) FILTER (WHERE days_open > 90) >= 3
ORDER BY stale_pct DESC;

Top reps с stale pipeline — coaching session.

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

Ошибка 1. Aging от deal_created. Иногда правильнее — от last_activity. Если есть recent touchpoints, deal живой.

Ошибка 2. Не учитывать deal stage. Discovery 90 days = норма (long enterprise). Proposal 90 days = stale. Stage-specific aging.

Ошибка 3. Все old deals = dead. Иногда enterprise deal 12+ months — реально перспективный. Sales rep knows context.

Ошибка 4. Не учитывать last activity. Even old deal может быть active (recent email exchange).

Ошибка 5. Auto-close без notification. Auto-clean pipeline без warning rep — кошмар. Always notify.

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

• Как посчитать sales cycle length в SQL
• Как посчитать pipeline velocity в SQL
• Как посчитать win-loss rate в SQL
• Как посчитать pipeline coverage в SQL

FAQ

Какой aging критичен?

Свыше 90 days в SMB. Свыше 180 в Enterprise.

Auto-close stale?

Quarterly clean — рекомендация. Auto-close с manager approval.

Aging vs стадия?

В каждой стадии типичный aging разный. Aging-by-stage точнее.

Stale в SMB vs Enterprise?

SMB 60 days = stale. Enterprise — до 12 месяцев норма.

Tracking last activity?

Yes — CRM logs (email, call, meeting). Aging от last activity, не от deal creation.