Как я построила аналитику сроков ремонта и превратила даты из CRM в понятный дашборд

Контекст

Финансовому директору нужна была аналитика сроков многоэтапного процесса ремонта: какие этапы занимают больше всего времени, какие сделки выходят за ожидаемые сроки и какие станции систематически работают дольше.

Вместе мы определили, какие даты из amoCRM подходят для обозначения начала и конца интересующих нас интервалов.

При выборе полей нужно было учитывать не только их смысл, но и качество заполнения. Подходящих системных дат было немного, поэтому для анализа требовались и даты, которые коллеги проставляли вручную. По этим полям оставались вопросы: насколько регулярно их заполняют и соответствуют ли указанные даты реальным событиям.

Поэтому частью задачи стала проверка самих данных: прежде чем делать выводы о сроках, нужно было понять, насколько можно доверять датам, на которых строится расчёт.

Коммерческие данные и названия внутренних сущностей в статье не раскрываются. Описание процесса и примеры приведены в обобщённом виде. Для будущей воспроизводимой версии кейса планируется использовать синтетические данные.

Задача

Подготовить датасет одним SQL-запросом и собрать в DataLens отчёт, который позволит:

• измерять длительность отдельных интервалов сделки и полного цикла;
• сравнивать сроки с целевыми значениями бизнеса и медианной длительностью;
• сравнивать станции и кураторов;
• находить сделки с задержками и подозрительными датами для дальнейшей проверки.

В анализ входили как завершённые, так и текущие сделки. Главное условие — наличие обеих граничных дат рассматриваемого интервала. Сама сделка при этом могла ещё находиться в работе.

Целевые сроки определил бизнес. Они были заданы не для всех интервалов: отсутствие целевого срока не приравнивалось к нулю.

Модель интервалов

Процесс охватывал обработку заявки, выдачу направления на ремонт, получение результатов осмотра, согласование ремонта, передачу лимита станции, выполнение работ и последующее оформление документов.

Для анализа я представила его как набор интервалов с датами начала и окончания. Отдельно рассматривался период от выдачи автомобиля до выставления акта страховой компании — он охватывал несколько интервалов документооборота.

Суммарный показатель «Полный цикл» рассчитывался от получения направления на ремонт до выставления акта страховой компании. Он не включал первичную обработку заявки до получения направления.

Такая структура позволяла смотреть и на отдельные участки процесса, и на более длинный путь сделки.

SQL-логика

Датасет для отчёта я подготовила одним SQL-запросом, разделённым на последовательные блоки CTE.

Сначала собрала информацию о сделках и их атрибутах из нескольких таблиц. Даты и дополнительные признаки хранились отдельными строками — с помощью условной агрегации я привела их к одной строке на сделку, обработала пустые значения и преобразовала даты.

Отдельной задачей было определение станции. Она могла быть указана в разных полях, текстовым названием или идентификатором справочника. Я задала приоритет источников, связала идентификаторы с названиями и сохранила информацию о происхождении значения, чтобы результат можно было проверить.

Затем через UNION ALL преобразовала даты сделки в общую структуру: одна строка — один интервал конкретной сделки. Для каждого интервала задавались начало, окончание и, при наличии, целевой срок.

Длительность рассчитывалась как разность календарных дат, без учёта времени суток. В SQL также формировались признаки отрицательной длительности и превышения целевого срока.

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

В DataLens я настроила итоговые показатели, проверки будущих дат, фильтры и отображение результатов.

Проверки качества данных

Я предпочитала сохранять подозрительные записи с флагами, чтобы их можно было проверить, а не удалять из датасета.

Интервалы без одной из граничных дат не попадали в итоговый датасет отчёта. Отрицательные длительности сохранялись с отдельным признаком, но исключались из расчётов среднего и медианы по всем интервалам.

В DataLens была настроена проверка дат из будущего. Здесь пригодилось обсуждение смысла полей с бизнесом: дата, похожая по названию на фактическое начало работ, оказалась ожидаемой. Для соответствующего интервала проверку будущих дат отключили.

Общий флаг внимания объединял отрицательные длительности, подозрительные будущие даты и превышение целевого срока. При этом превышение срока само по себе не означало ошибку данных — это другая причина проверить сделку.

Пометка записи и её участие в показателях были отдельными правилами. Например, флаг будущей даты помогал найти запись для проверки, но сам по себе не исключал её из расчётов длительности.

Анализ отклонений

Для анализа использовались два ориентира:

• целевой срок, установленный бизнесом;
• базовая медианная длительность соответствующего интервала, рассчитанная в SQL.

На дашборде отображались количество и доля превышений, а в детализации — величина превышения в днях. Если длительность не превышала ориентир, величина превышения принималась равной нулю. Для интервалов без целевого срока отдельно показывалось, что он не задан.

Базовая медиана рассчитывалась по выборке SQL и не менялась при переключении фильтров дашборда. Средние и медианы, рассчитанные в самом DataLens, могли отражать уже выбранный пользователем срез. Это разные показатели: один служит фиксированным ориентиром, другие описывают текущую выборку.

При разборе запроса обнаружилось, что первоначально флаги превышения использовали общую медиану всех интервалов. Расчёт был исправлен: теперь каждый интервал сравнивается со своей базовой медианой.

BI-дашборд

Отчёт состоял из трёх вкладок.

Динамика таймингов по этапам

Первая вкладка показывала изменение длительности интервалов во времени, среднюю и медианную длительность полного цикла, распределение сделок по статусам и сводную таблицу по интервалам.

Рядом были пояснения к границам интервалов и целевым срокам. Отдельные графики и таблицы сохраняли обзор за весь период независимо от селектора «Отчётный период» — это было указано в подписях.

Рейтинги

На второй вкладке можно было сравнивать станции и кураторов по количеству сделок, средней и медианной длительности, количеству и доле превышений.

Дополнительно был представлен рейтинг из 15 станций по выбранному интервалу. Количество сделок отображалось рядом с показателями длительности, чтобы при сравнении был виден объём данных.

Аномалии и качество данных

Третья вкладка объединяла показатели отрицательных длительностей, дат в будущем и превышений сроков, а также количество и долю сделок, требующих внимания.

Фильтр по типу проблемы позволял перейти к нужным записям. В детализации отображались идентификатор сделки, интервал, участники процесса, исходные даты, длительность, целевой срок и отклонение.

Дополнительные сводки по кураторам, станциям и интервалам помогали увидеть, где сосредоточены записи для проверки.

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

Что оказалось сложным

Больше всего работы потребовал сам SQL-запрос. Нужно было связать разрозненные атрибуты CRM, определить источник названия станции и построить общую модель интервалов, пригодную для разных представлений в отчёте.

Сложность была и в согласовании правил: какие даты использовать, что считать допустимым значением, какие записи сохранять для проверки и какие исключать из конкретных показателей.

Отдельного внимания потребовало разделение расчётов между SQL и DataLens. Показатели, стоящие рядом в таблице, должны иметь понятные основания для сравнения. Проверка медиан показала, почему важно смотреть не только на формулу отдельного поля, но и на весь путь расчёта — от исходных записей до итогового счётчика.

Результат

Главная ценность отчёта — возможность находить сделки с сильными задержками, которые не были очевидны при обычной работе в интерфейсе amoCRM.

По идентификатору оператор может открыть сделку и проверить, действительно ли она задержалась на этапе или причина в неверной дате. Если задержка подтверждается — посмотреть задачи и ответственных, напомнить о следующем действии и помочь продвинуть сделку дальше.

Финансовый директор уже использовал отчёт: мы обсуждали задержки, разбирали отдельные сделки и просили коллег уточнить даты.

Дашборд также помог увидеть этапы, на которых сделки задерживаются особенно сильно. Красный индикатор — повод разобраться, что мешает работе. Возможно, процесс устроен неудобно, в системе не хватает нужного поля или часть ручных действий можно автоматизировать.

Например, если автомобиль долго ждёт выдачи, стоит проверить, как клиенту сообщают о готовности машины. Возможно, здесь помогло бы автоматическое напоминание с просьбой забрать автомобиль.

инженеры не всегда делятся своей болью и иногда эту боль мы видим в виде красных индикаторов на дашборде и только после этого, разобравшись, мы можем им улучшить рабочий процесс и дело пойдет быстрее

Сравнение станций показало, с какими участниками связаны более длительные сроки и больше аномалий, а какие показывают короткие сроки и меньше проблем. Это дополнительная информация для оценки и приоритизации сотрудничества. Но задержка или аномалия требует разбора причин — она не доказывает автоматически, что проблема возникла по вине станции.

Отдельную оценку влияния отчёта на сроки ремонта или финансовый результат мы не проводили. Подтверждённый результат — его использование для поиска задержек, проверки данных и обсуждения конкретных сделок.

Стек

PostgreSQL, SQL, CTE, условная агрегация, объединение таблиц, UNION ALL, расчёт медианы через PERCENTILE_CONT, Yandex DataLens.