Каким командам назначаешь KPI?

Назначение KPI различным командам: стратегия Data Analyst

Как Data Analyst, моя роль не просто подсчитывать метрики, а помогать организации определять и отслеживать KPI для каждого подразделения. Это требует глубокого понимания бизнес-целей и способности переводить стратегию в измеримые показатели.

1. Product/Разработка (Engineering Team)

Основная цель: Качество продукта, стабильность, развитие функционала.

Ключевые KPI

-- Uptime и доступность
SELECT 
    DATE(created_at) as date,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as uptime_percentage
FROM health_checks
GROUP BY DATE(created_at)
HAVING uptime_percentage >= 99.9;  -- SLA: 99.9% uptime

-- Скорость загрузки страницы (Performance)
SELECT 
    page,
    AVG(load_time_ms) as avg_load_time,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY load_time_ms) as p95_load_time
FROM page_metrics
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY page;

-- Количество критических ошибок
SELECT 
    DATE(logged_at) as date,
    COUNT(*) as critical_errors
FROM error_logs
WHERE severity = 'critical'
GROUP BY DATE(logged_at);

-- Время обработки задач (Sprint velocity)
SELECT 
    sprint_id,
    COUNT(CASE WHEN status = 'done' THEN 1 END) as completed_tasks,
    SUM(story_points) as completed_points
FROM jira_tasks
GROUP BY sprint_id;

Целевые значения:

• Uptime: ≥ 99.9%
• P95 load time: < 2 сек
• Критические ошибки: < 5 в день
• Sprint velocity: ± 10% от планового

2. Sales/Продажи

Основная цель: Увеличение выручки, расширение клиентской базы.

Ключевые KPI

-- Выручка и её динамика
SELECT 
    DATE_TRUNC('month', order_date)::date as month,
    SUM(amount) as monthly_revenue,
    LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_month_revenue,
    ROUND(
        (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date))) * 100.0
        / LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)), 2
    ) as mom_growth_percent
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;

-- Pipeline value
SELECT 
    manager_id,
    SUM(CASE 
        WHEN stage = 'proposal' THEN amount * 0.30
        WHEN stage = 'negotiation' THEN amount * 0.60
        WHEN stage = 'closing' THEN amount * 0.90
        ELSE 0
    END) as weighted_pipeline
FROM deals
WHERE status != 'lost'
GROUP BY manager_id;

-- Win rate (процент выигранных сделок)
SELECT 
    manager_id,
    COUNT(CASE WHEN status = 'won' THEN 1 END) as deals_won,
    COUNT(*) as total_deals,
    ROUND(COUNT(CASE WHEN status = 'won' THEN 1 END) * 100.0 / COUNT(*), 2) as win_rate_percent
FROM deals
WHERE DATE(closed_date) >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY manager_id
ORDER BY win_rate_percent DESC;

-- Средний размер сделки
SELECT 
    manager_id,
    AVG(amount) as avg_deal_size,
    COUNT(*) as deals_count,
    SUM(amount) as total_revenue
FROM deals
WHERE status = 'won'
GROUP BY manager_id;

Целевые значения:

• Выручка: +20% YoY
• Win rate: > 30%
• Среднее время цикла сделки: < 60 дней
• CAC (Customer Acquisition Cost): < $500

3. Marketing

Основная цель: Генерация лидов, повышение осведомленности, снижение CAC.

Ключевые KPI

-- Источники лидов и их качество
SELECT 
    utm_source,
    utm_campaign,
    COUNT(*) as leads,
    COUNT(CASE WHEN qualified = true THEN 1 END) as qualified_leads,
    ROUND(
        COUNT(CASE WHEN qualified = true THEN 1 END) * 100.0 / COUNT(*), 2
    ) as qualification_rate,
    ROUND(SUM(deal_amount) / COUNT(*), 2) as avg_conversion_value
FROM leads
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY utm_source, utm_campaign
ORDER BY leads DESC;

-- CAC по источникам
SELECT 
    utm_source,
    SUM(marketing_spend) as total_spend,
    COUNT(DISTINCT customer_id) as new_customers,
    ROUND(SUM(marketing_spend) / COUNT(DISTINCT customer_id), 2) as cac
FROM marketing_spend m
JOIN customers c ON m.utm_source = c.acquisition_source
WHERE DATE(m.date) >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY utm_source
HAVING COUNT(DISTINCT customer_id) > 0
ORDER BY cac ASC;

-- Engagement метрики
SELECT 
    campaign_id,
    COUNT(*) as emails_sent,
    COUNT(CASE WHEN opened = true THEN 1 END) as opens,
    ROUND(COUNT(CASE WHEN opened = true THEN 1 END) * 100.0 / COUNT(*), 2) as open_rate,
    COUNT(CASE WHEN clicked = true THEN 1 END) as clicks,
    ROUND(COUNT(CASE WHEN clicked = true THEN 1 END) * 100.0 / COUNT(*), 2) as ctr
FROM email_campaigns
GROUP BY campaign_id
ORDER BY open_rate DESC;

Целевые значения:

• Lead generation: 500 лидов/месяц
• CAC: < $200
• Qualification rate: > 20%
• Email open rate: 25-30%
• CTR: 2-5%

4. Customer Success/Support

Основная цель: Удовлетворение клиентов, снижение churn, retention.

Ключевые KPI

-- Churn rate
SELECT 
    DATE_TRUNC('month', churn_date)::date as month,
    COUNT(*) as churned_customers,
    LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', churn_date)) as prev_month_customers,
    ROUND(
        COUNT(*) * 100.0 / LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', churn_date)), 2
    ) as churn_rate_percent
FROM customers
WHERE status = 'churned'
GROUP BY DATE_TRUNC('month', churn_date)
ORDER BY month DESC;

-- NPS (Net Promoter Score)
SELECT 
    EXTRACT(MONTH FROM survey_date) as month,
    COUNT(CASE WHEN score >= 9 THEN 1 END) as promoters,
    COUNT(CASE WHEN score >= 7 AND score <= 8 THEN 1 END) as passives,
    COUNT(CASE WHEN score <= 6 THEN 1 END) as detractors,
    COUNT(*) as total,
    ROUND(
        (COUNT(CASE WHEN score >= 9 THEN 1 END) - COUNT(CASE WHEN score <= 6 THEN 1 END)) * 100.0
        / COUNT(*), 2
    ) as nps_score
FROM nps_surveys
GROUP BY EXTRACT(MONTH FROM survey_date)
ORDER BY month DESC;

-- Время обработки тикета
SELECT 
    support_tier,
    COUNT(*) as total_tickets,
    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600) as avg_resolution_hours,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600) as p95_resolution_hours
FROM support_tickets
WHERE resolved_at IS NOT NULL
GROUP BY support_tier;

-- Retention по когортам
WITH cohort_data AS (
    SELECT 
        DATE_TRUNC('month', signup_date)::date as cohort_month,
        customer_id,
        DATE_TRUNC('month', CURRENT_DATE)::date as current_month,
        FLOOR((EXTRACT(EPOCH FROM (CURRENT_DATE - signup_date)) / (30 * 86400))) as months_alive
    FROM customers
)
SELECT 
    cohort_month,
    SUM(CASE WHEN months_alive >= 0 THEN 1 ELSE 0 END) as m0,
    SUM(CASE WHEN months_alive >= 1 THEN 1 ELSE 0 END) as m1,
    SUM(CASE WHEN months_alive >= 3 THEN 1 ELSE 0 END) as m3,
    SUM(CASE WHEN months_alive >= 6 THEN 1 ELSE 0 END) as m6,
    SUM(CASE WHEN months_alive >= 12 THEN 1 ELSE 0 END) as m12
FROM cohort_data
GROUP BY cohort_month
ORDER BY cohort_month DESC;

Целевые значения:

• Churn rate: < 5% в месяц
• NPS: > 50
• Время обработки: < 4 часов (L1), < 24 часов (L2)
• Retention 12 месяцев: > 80%
• Customer satisfaction: > 4.5/5.0

5. HR/Кадры

Основная цель: Развитие команды, снижение текучести.

Ключевые KPI

-- Текучесть кадров
SELECT 
    EXTRACT(MONTH FROM leave_date) as month,
    COUNT(*) as employees_left,
    LAG(COUNT(*)) OVER (ORDER BY EXTRACT(MONTH FROM leave_date)) as employees_at_start,
    ROUND(
        COUNT(*) * 100.0 / LAG(COUNT(*)) OVER (ORDER BY EXTRACT(MONTH FROM leave_date)), 2
    ) as turnover_rate_percent
FROM employees
WHERE status = 'inactive'
GROUP BY EXTRACT(MONTH FROM leave_date)
ORDER BY month DESC;

-- Заполнение должностей
SELECT 
    department,
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_employees,
    (SELECT COUNT(*) FROM job_openings WHERE department = employees.department) as open_positions,
    AVG(days_to_fill) as avg_days_to_hire
FROM employees
GROUP BY department;

-- Engagement score
SELECT 
    employee_id,
    name,
    department,
    AVG(engagement_score) as avg_engagement,
    COUNT(training_hours) as training_hours_completed
FROM employee_surveys e
JOIN employees emp ON e.employee_id = emp.id
GROUP BY employee_id, name, department
ORDER BY avg_engagement DESC;

Целевые значения:

• Turnover rate: < 10% в год
• Time to hire: < 45 дней
• Employee satisfaction: > 4.0/5.0
• Training hours/employee: > 40 часов в год

6. Finance/Бухгалтерия

Основная цель: Контроль расходов, прибыльность, денежный поток.

Ключевые KPI

-- Burn rate и runway
SELECT 
    DATE_TRUNC('month', transaction_date)::date as month,
    SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as monthly_burn,
    LAG(SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END)) 
        OVER (ORDER BY DATE_TRUNC('month', transaction_date)) as prev_month_burn,
    (SELECT balance FROM bank_accounts WHERE account_type = 'operating' LIMIT 1) / 
    AVG(SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END)) 
        OVER (ORDER BY DATE_TRUNC('month', transaction_date) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    as runway_months
FROM financial_transactions
GROUP BY DATE_TRUNC('month', transaction_date)
ORDER BY month DESC;

-- Маржинальность по продуктам
SELECT 
    product_id,
    SUM(revenue) as total_revenue,
    SUM(cogs) as total_cogs,
    (SUM(revenue) - SUM(cogs)) as gross_profit,
    ROUND((SUM(revenue) - SUM(cogs)) * 100.0 / SUM(revenue), 2) as gross_margin_percent
FROM financial_results
GROUP BY product_id
ORDER BY gross_margin_percent DESC;

-- MRR/ARR
SELECT 
    DATE_TRUNC('month', invoice_date)::date as month,
    SUM(amount) as mrr,
    SUM(amount) * 12 as arr,
    LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', invoice_date)) as prev_month_mrr
FROM invoices
WHERE status = 'paid'
GROUP BY DATE_TRUNC('month', invoice_date)
ORDER BY month DESC;

Целевые значения:

• Gross margin: > 60%
• Runway: > 18 месяцев
• MRR growth: +10% MoM
• Operating expense ratio: < 50% от revenue

7. Как назначать KPI: процесс

# Фреймворк SMART для KPI
def create_smart_kpi(goal):
    kpi = {
        'Specific': 'Конкретная, измеримая цель',
        'Measurable': 'Количественно определяемая метрика',
        'Achievable': 'Реалистичная, достижимая цель',
        'Relevant': 'Связанная с бизнес-целями компании',
        'Time-bound': 'С четким сроком (неделя, месяц, квартал)'
    }
    return kpi

# Пример плохого KPI
bad_kpi = "Продажи должны быть хорошими"

# Пример хорошего KPI
good_kpi = {
    'metric': 'Win rate для Sales',
    'target': '35% ± 2%',
    'period': 'Ежемесячно',
    'consequence': 'Бонус 20% при достижении',
    'owner': 'Head of Sales'
}

8. Общие ошибки при назначении KPI

1. Too many KPIs — нужно 3-5 ключевых метрик, а не 20
2. Противоречивые KPI — Sales ускоряет цикл, Support удлиняет (разные цели)
3. Не обновляемые KPI — цели должны меняться с условиями бизнеса
4. Неправильные стимулы — KPI создают поведение, которое вознаграждается
5. Отсутствие baseline — нужно знать, откуда начинаем

Инструменты для отслеживания

• Dashboard в Tableau/PowerBI — мониторинг в реальном времени
• Email рассылка — еженедельные/ежемесячные отчеты
• SQL alerts — автоматические уведомления при отклонениях
• OKR (Objectives & Key Results) — более гибкий фреймворк

Как Data Analyst: Мы не просто подсчитываем метрики, мы помогаем организации понять, что измерять и почему это важно для стратегии.