Анализ данных о звонках

Решение

Анализ данных о длительности звонков в call-центре

Проведу комплексный анализ с использованием статистических методов и визуализаций.

---

1. План анализа

Этап 1: Описательная статистика

SELECT 
  COUNT(*) AS total_calls,
  MIN(duration_seconds) AS min_duration,
  MAX(duration_seconds) AS max_duration,
  AVG(duration_seconds)::INT AS avg_duration,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median_duration,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY duration_seconds) AS q1,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration_seconds) AS q3,
  STDDEV(duration_seconds)::INT AS std_dev,
  VARIANCE(duration_seconds)::INT AS variance,
  SKEWNESS(duration_seconds) AS skewness,
  KURTOSIS(duration_seconds) AS kurtosis
FROM calls;

Метрики:

• MIN/MAX — выбросы
• Mean/Median — показывают асимметричность распределения
• Q1/Q3 — размах основной массы данных
• STDDEV — вариативность
• Skewness — асимметрия распределения
• Kurtosis — острота распределения

Этап 2: Анализ по датам

SELECT 
  call_date,
  EXTRACT(DOW FROM call_date) AS day_of_week,
  COUNT(*) AS calls_count,
  AVG(duration_seconds)::INT AS avg_duration,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_seconds) AS p95_duration,
  MAX(duration_seconds) AS max_duration
FROM calls
GROUP BY call_date
ORDER BY call_date;

Это выявит сезонность и тренды.

Этап 3: Анализ по операторам

SELECT 
  operator_id,
  COUNT(*) AS handled_calls,
  AVG(duration_seconds)::INT AS avg_duration,
  STDDEV(duration_seconds)::INT AS std_dev,
  MIN(duration_seconds) AS min_duration,
  MAX(duration_seconds) AS max_duration,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_seconds) AS p95
FROM calls
GROUP BY operator_id
ORDER BY avg_duration DESC;

---

2. Предположения о распределении данных

Характеристики реальных call-центров:

Тип распределения: Логнормальное (Lognormal Distribution)

Почему логнормальное?

• Множество коротких звонков (ошибочные наборы, отказы)
• Некоторое количество средних звонков (обычные запросы)
• Малое количество очень длинных звонков (сложные вопросы, консультации)
• Нет отрицательных значений (длительность > 0)
• Правосторонняя асимметрия (длинный хвост справа)

Ожидаемые характеристики:

Категории звонков:

Длительность (сек) | Тип | % звонков | Примеры
---|---|---|---
0-30 | Короткие | 25-35% | Ошибочный номер, hang-up
30-180 | Средние | 50-60% | Типичный запрос, простая помощь
180-600 | Длинные | 10-15% | Консультация, сложный вопрос
600+ | Очень длинные | <5% | Выбросы, ошибочные записи

---

3. Методы проверки гипотез о распределении

Метод 1: Визуализация (SQL + построение графиков)

-- Гистограмма с интервалами
WITH call_distribution AS (
  SELECT 
    CASE 
      WHEN duration_seconds < 30 THEN '0-30'
      WHEN duration_seconds < 60 THEN '30-60'
      WHEN duration_seconds < 120 THEN '60-120'
      WHEN duration_seconds < 300 THEN '120-300'
      WHEN duration_seconds < 600 THEN '300-600'
      ELSE '600+'
    END AS duration_bucket,
    COUNT(*) AS call_count
  FROM calls
  GROUP BY duration_bucket
)
SELECT 
  duration_bucket,
  call_count,
  ROUND(100.0 * call_count / SUM(call_count) OVER (), 2) AS percentage,
  RPAD('█', CAST(call_count::NUMERIC / MAX(call_count) OVER () * 40 AS INT), '█') AS bar_chart
FROM call_distribution
ORDER BY 
  CASE WHEN duration_bucket = '0-30' THEN 1
       WHEN duration_bucket = '30-60' THEN 2
       WHEN duration_bucket = '60-120' THEN 3
       WHEN duration_bucket = '120-300' THEN 4
       WHEN duration_bucket = '300-600' THEN 5
       ELSE 6
  END;

Метод 2: Тест Шапиро-Уилка (Shapiro-Wilk Test)

SQL для подготовки данных:

SELECT 
  duration_seconds,
  LOG(duration_seconds) AS log_duration
FROM calls
ORDER BY RANDOM()
LIMIT 5000;  -- Тест работает лучше на выборке

Python для проведения теста:

from scipy.stats import shapiro, lognorm

# Читаем данные из SQL
durations = [...]  # из SQL
log_durations = np.log(durations)

# Тест на нормальность (исходных данных)
stat1, p_value1 = shapiro(durations)
print(f"Shapiro-Wilk (исходные): статистика={stat1:.4f}, p-value={p_value1:.4e}")
# Ожидание: p-value < 0.05 → распределение не нормальное

# Тест на нормальность (логарифмированных данных)
stat2, p_value2 = shapiro(log_durations)
print(f"Shapiro-Wilk (логарифмированные): статистика={stat2:.4f}, p-value={p_value2:.4e}")
# Если p-value > 0.05, значит логарифмированные данные нормальны → исходные логнормальны

Метод 3: Тест Колмогорова-Смирнова (Kolmogorov-Smirnov)

from scipy.stats import kstest, lognorm

# Подгон параметров логнормального распределения
durations = np.array([...])
shape, loc, scale = lognorm.fit(durations)

# KS-тест: сравниваем с логнормальным распределением
stat, p_value = kstest(durations, 
                       lambda x: lognorm.cdf(x, shape, loc, scale))

print(f"KS-тест: статистика={stat:.4f}, p-value={p_value:.4e}")
if p_value > 0.05:
    print("✓ Данные соответствуют логнормальному распределению")
else:
    print("✗ Данные отличаются от логнормального распределения")

Метод 4: Визуализация Q-Q Plot

import matplotlib.pyplot as plt
from scipy import stats

durations = np.array([...])
log_durations = np.log(durations)

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# Q-Q plot для исходных данных
stats.probplot(durations, dist="lognorm", plot=axes[0])
axes[0].set_title('Q-Q Plot: Исходные данные vs Логнормальное')

# Q-Q plot для логарифмированных данных
stats.probplot(log_durations, dist="norm", plot=axes[1])
axes[1].set_title('Q-Q Plot: Логарифмированные данные vs Нормальное')

plt.tight_layout()
plt.show()

Интерпретация: Если точки близко к линии → распределение соответствует модели.

---

4. Метрики для оценки эффективности операторов

Метрика 1: Average Handle Time (AHT)

SELECT 
  operator_id,
  AVG(duration_seconds)::INT AS aht_seconds,
  AVG(duration_seconds) / 60 AS aht_minutes
FROM calls
WHERE call_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY operator_id
ORDER BY aht_seconds;

Интерпретация:

• Слишком низкий AHT (< 2 мин) → торопится, может быть низкое качество
• Нормальный AHT (3-8 мин) → оптимальная работа
• Слишком высокий AHT (> 15 мин) → неэффективность или сложные вопросы

Метрика 2: Call Volume (Объём звонков)

SELECT 
  operator_id,
  COUNT(*) AS total_calls,
  COUNT(*) * 1.0 / (EXTRACT(DAY FROM MAX(call_date) - MIN(call_date)) + 1) AS calls_per_day,
  COUNT(*) * 1.0 / (8 * (EXTRACT(DAY FROM MAX(call_date) - MIN(call_date)) + 1)) AS calls_per_hour
FROM calls
WHERE call_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY operator_id
ORDER BY total_calls DESC;

Интерпретация:

• Высокий объём + нормальный AHT = эффективный оператор
• Низкий объём + высокий AHT = может быть проблема

Метрика 3: Call Duration Consistency (Консистентность)

SELECT 
  operator_id,
  COUNT(*) AS call_count,
  AVG(duration_seconds)::INT AS avg_duration,
  STDDEV(duration_seconds)::INT AS std_dev,
  ROUND(STDDEV(duration_seconds) / AVG(duration_seconds) * 100, 2) AS coefficient_of_variation,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY duration_seconds) AS q1,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration_seconds) AS q3,
  (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration_seconds) - 
   PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY duration_seconds)) AS iqr
FROM calls
WHERE call_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY operator_id
ORDER BY coefficient_of_variation DESC;

Интерпретация:

• CV < 50% → консистентный оператор (предсказуемый результат)
• CV > 100% → непредсказуемый (может быть новичок)

Метрика 4: Call Quality Score (Качество)

SELECT 
  operator_id,
  COUNT(*) AS total_calls,
  COUNT(CASE WHEN duration_seconds < 30 THEN 1 END) AS very_short_calls,
  COUNT(CASE WHEN duration_seconds > 1200 THEN 1 END) AS very_long_calls,
  ROUND(100.0 * COUNT(CASE WHEN duration_seconds BETWEEN 120 AND 600 THEN 1 END) / COUNT(*), 2) AS normal_duration_pct,
  ROUND(
    100.0 * (COUNT(*) - COUNT(CASE WHEN duration_seconds < 30 THEN 1 END) - COUNT(CASE WHEN duration_seconds > 1200 THEN 1 END)) / COUNT(*),
    2
  ) AS quality_score
FROM calls
WHERE call_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY operator_id
ORDER BY quality_score DESC;

Интерпретация:

• Score > 90% → высокое качество
• Score 80-90% → хорошее качество
• Score < 80% → требует внимания

Метрика 5: Performance Index (Комбинированный индекс)

WITH operator_metrics AS (
  SELECT 
    operator_id,
    COUNT(*) AS call_count,
    AVG(duration_seconds)::INT AS avg_duration,
    STDDEV(duration_seconds)::INT AS std_dev,
    COUNT(CASE WHEN duration_seconds BETWEEN 60 AND 600 THEN 1 END)::NUMERIC / COUNT(*) AS quality_ratio
  FROM calls
  WHERE call_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY operator_id
),
ranked_metrics AS (
  SELECT 
    operator_id,
    call_count,
    avg_duration,
    quality_ratio,
    PERCENT_RANK() OVER (ORDER BY call_count DESC) AS volume_rank,
    PERCENT_RANK() OVER (ORDER BY avg_duration ASC) AS efficiency_rank,
    PERCENT_RANK() OVER (ORDER BY quality_ratio DESC) AS quality_rank
  FROM operator_metrics
)
SELECT 
  operator_id,
  call_count,
  avg_duration,
  ROUND(quality_ratio * 100, 2) AS quality_pct,
  ROUND((volume_rank * 0.3 + efficiency_rank * 0.3 + quality_rank * 0.4) * 100, 2) AS performance_index
FROM ranked_metrics
ORDER BY performance_index DESC;

Веса:

• Volume (30%) — нужны производительные операторы
• Efficiency (30%) — нужно быстро решать проблемы
• Quality (40%) — качество важнее всего

---

5. Анализ выбросов

Выявление экстремальных звонков

SELECT 
  call_id,
  operator_id,
  duration_seconds,
  call_date,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_seconds) OVER () AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_seconds) OVER () AS p99,
  CASE 
    WHEN duration_seconds > PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_seconds) OVER () THEN 'Extreme'
    WHEN duration_seconds > PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_seconds) OVER () THEN 'High'
    WHEN duration_seconds < 30 THEN 'Short'
    ELSE 'Normal'
  END AS outlier_category
FROM calls
ORDER BY duration_seconds DESC
LIMIT 100;

Почему нужен анализ выбросов:

• Ошибочные записи (звонок забыли отключить)
• Одновременно несколько звонков
• Система помех или багов
• Действительно сложные случаи

---

6. Сезонность и тренды

SELECT 
  DATE_TRUNC('week', call_date) AS week,
  EXTRACT(DOW FROM call_date) AS day_of_week,
  TO_CHAR(DATE_TRUNC('week', call_date), 'Day') AS day_name,
  COUNT(*) AS call_count,
  AVG(duration_seconds)::INT AS avg_duration,
  STDDEV(duration_seconds)::INT AS std_dev
FROM calls
GROUP BY DATE_TRUNC('week', call_date), EXTRACT(DOW FROM call_date)
ORDER BY week, day_of_week;

Ожидаемые паттерны:

• Понедельник: высокий объём (выходной)
• Вторник-четверг: нормальный объём
• Пятница: снижение объёма
• Выходные: минимальный объём

---

Итоговая рекомендация

Используй логнормальное распределение как модель для:

• Прогнозирования нагрузки
• Планирования ресурсов
• Выявления аномалий
• Установления справедливых метрик

Стандартные пороги:

• AHT среднего оператора: 5-7 минут
• Качество: > 85% звонков 60-600 секунд
• Выбросы: > 95-го перцентиля требуют анализа