Компания, которая делает СМС-рассылки для бизнеса, раз в месяц выставляет клиентам счёт. Менеджер брал из биллинга выгрузку на 27–31 тысячу строк по одному клиенту и вручную сводил её по тарифам операторов, а правила этого сведения целиком знал один специалист. За пару дней я с Claude Code восстановил эти правила по его готовым расчётам и собрал программу в одном HTML-файле. Реальную выгрузку она считает за 0,1–0,4 секунды, а на втором клиенте итог совпал с ручным расчётом до копейки.
Что было на входе
На старте у меня было три файла. Первый: выгрузка биллинга, где на каждое сообщение приходится строка с номером получателя, оператором, типом сообщения, статусом доставки и временем. В выгрузках, с которыми я работал, было 27 300, 31 569 и 30 241 строка за месяц по одному клиенту. Второй: прайс оператора-агрегатора на 244 строки, у каждого оператора связи там своя схема. Третий: ручной расчёт специалиста. Вот выгрузка, вот что должно получиться. Второй такой расчёт, уже по другому клиенту, пришёл позже и многое поменял.
Документации по правилам не было. Было объяснение специалиста в одну фразу, если пересказать без цен: три сервисных СМС на один номер стоят фиксированную сумму за абонента, шестое сообщение добавляет к ней вторую сумму, а с 21-го платить не нужно. Всё остальное он держал в голове и в своих таблицах.
Как восстановить правила, которых нет на бумаге
По примерам. Берёшь выгрузку и готовый ручной результат по ней, выдвигаешь гипотезу о правиле, пересчитываешь выгрузку и сравниваешь с эталоном построчно. Совпало, значит правило подтверждено. Не совпало, значит нужна следующая гипотеза.
Эту часть я делал с Claude Code: он предлагал гипотезу, пересчитывал десятки тысяч строк и показывал, где цифры расходятся с эталоном.
Сначала я проверил самое простое: считается ли тариф за уникального абонента. Мы посчитали уникальные номера отдельно по каждому оператору и типу сообщений и получили ровно те же числа, что у специалиста: 735, 479, 432. Значит, фраза про «три СМС на номер» описывает реальную схему, просто неполно.
Дальше правила вытаскивались по одному:
| Правило | Как выяснили |
|---|---|
| Тариф считается по уникальному абоненту, отдельно для каждого оператора и типа сообщений | число уникальных номеров совпало с расчётом специалиста |
| Пакеты накопительные: абонент с 7 сообщениями платит и за «1–5», и за «6–20» | «плюс» в объяснении специалиста и совпадение числа абонентов, дошедших до 6-го и 21-го сообщения |
| В прайсе три режима: пакет на абонента, цена за сообщение и цена по месячному объёму | разбор прайса по операторам |
| Сообщения без типа относятся к основному типу своего оператора | только так во втором примере сошлись 4 334, 4 482 и 3 767 абонентов |
| Тип трафика определяется по коду в скобках | биллинг пишет «Рекламные(R)» и «Авторизационные(A8)», в справочнике 15 типов |
Одна находка к тарифам отношения не имела, но меня удивила. У одного клиента оператор отклонил 57% рекламных СМС в июле и почти все в сентябре. Специалист такие сообщения в счёт этому клиенту не включал, а у другого клиента отклонённая реклама в счёт входит. Какие статусы доставки тарифицировать, до сих пор открытый вопрос.
Почему одного примера мало
По одному примеру стройная версия выглядит правильной. У меня так случилось дважды.
Первый раз с ценами. По первому клиенту цена выглядела как «прайс плюс 3% наценки» и совпадала до копейки на двух значениях. Я принял это за правило. Второй клиент показал другую картину: у каждого клиента свои цены без НДС, у разных операторов они отклоняются от прайса по-разному, цена с НДС считается формулой, а в счёт входит ещё абонентская плата за имена отправителя и шаблоны сообщений. Наценка в 3% оказалась совпадением. Тарифы пришлось вынести в отдельный файл на каждого клиента, а в результат добавить таблицу абонплаты.
Второй раз с Билайном. В прайсе у него есть схема «Первые шаги», и её описание читалось двумя способами. Разница между трактовками в счёте доходила до десятков абонентов, а первый пример не давал выбрать. Спор решили данные второго клиента. Правильной оказалась такая трактовка: у абонентов с одним-двумя сообщениями своя цена, а при трёх и больше вместо неё начисляется пакет. Она дала 52, 12 и 9 абонентов, и все три числа совпали с расчётом специалиста.
Итог второго клиента сошёлся полностью: 22 строки трафика, 7 строк абонплаты и сумма до копейки, вместе с формулами в ячейках. Второй пример сработал как тестовая выборка. Правила, выведенные по первому, на нём либо подтверждались, либо разваливались.
Ручной расчёт тоже ошибается
У первого клиента 21 строка из 24 совпала с ручным расчётом точно. Три расходились на одно сообщение, и каждый раз ошибка была в ручном расчёте:
| У специалиста | По выгрузке |
|---|---|
| 6 118 | 6 119 |
| 115 | 114 |
| 1 930 | 1 931 |
На одной строке это мелочь. Но считал человек с опытом, единственный, кто знает правила, и в одном месячном расчёте он ошибся трижды. Тридцать тысяч строк руками без ошибок не сводит никто.
Я тоже отличился. В отчёте после первого раунда я написал «22 совпадения из 24» вместо 21 и поймал это только при перепроверке. Поэтому каждую версию программы я сверял с ручными расчётами построчно. На глаз в этой задаче не проверяется ничего.
Почему не Python и не макрос VBA
У менеджеров стандартный Windows и Excel, а устанавливать программы им нельзя. Узнал я это не сразу.
Первый прототип я сделал на Python, с тарифами в отдельном файле, и он уже давал 21 совпадение из 24. Потом выяснилось, что Python у пользователей нет. Я не спросил заранее, что стоит на их компьютерах, и это мой косяк: прототип пришлось переписывать.
Макрос VBA тоже отпал. Его нельзя было собрать и проверить, а у пользователей он упирается в предупреждения безопасности: с 2022 года Microsoft в свежих версиях Office по умолчанию блокирует макросы в файлах из интернета, вложения из почты тоже.
В итоге программа стала одной HTML-страницей. Менеджер открывает её двойным щелчком в Edge, выбирает выгрузку и скачивает готовый Excel с расчётом. В интернет страница ничего не отправляет, данные клиентов остаются на компьютере менеджера. Сторонних библиотек в ней нет. Файл xlsx по устройству представляет собой zip-архив с XML внутри, поэтому чтение и запись Excel удалось написать на встроенных возможностях браузера.
| Показатель | Значение |
|---|---|
| Размер программы: один HTML-файл со встроенным шаблоном тарифов | около 114 КБ |
| Исходный код: ядро расчёта, интерфейс, сборка, генератор тарифов, тесты | около 1 900 строк |
| Автоматических проверок | 19 |
| Реальная выгрузка, около 30 тысяч строк | 0,1–0,4 с |
| 1 млн строк, почти предел листа Excel в 1 048 576 строк | около 2,8 с |
| Проверка чтения Excel: сверено ячеек и найдено расхождений | 194 287 и 0 |
Что нужно менеджеру, чтобы обойтись без программиста
Две вещи: менять правила в привычном Excel и получать от программы внятное предупреждение, когда она чего-то не знает. Из этого выросли четыре решения:
- Тарифы лежат в обычных Excel-таблицах, по файлу на клиента. Цены, схемы и правила менеджер меняет сам, а программа запоминает, какой файл тарифов относится к какому клиенту.
- Цена с НДС и суммы в результате записаны формулами, как в ручном расчёте. Файл можно проверить и поправить руками.
- Если тарифа нет, тип незнакомый или выгрузка от другого клиента, строка не пропадает молча. Программа ставит «НЕТ ТАРИФА» и объясняет, что сделать.
- Инструкция встроена прямо в страницу.
Предупреждения пригодились на сентябрьской выгрузке, где часть строк ушла в «НЕТ ТАРИФА». Причин нашлось две. Тип «Сервисные(Инф-ые)(I)» с двумя парами скобок программа не узнала, и это была моя недоработка. Остальные строки посчитались по тарифам другого клиента: программа об этом предупредила, но невнятно. В третьей версии тип определяется по коду из справочника на 15 значений, предупреждение называет файл тарифов и отправителя из выгрузки, а для абонплаты появилась подсказка, сколько имён и шаблонов есть в выгрузке.
Зачем нейросеть, если в программе её нет
В самой программе нейросети нет. Это расчёт по жёстким правилам, и на одной и той же выгрузке он обязан каждый раз давать один и тот же результат. Нейросеть понадобилась раньше: перебрать гипотезы, пересчитать под каждую десятки тысяч строк, найти расхождения с эталоном и написать код с тестами. На это ушло пара дней и три раунда доработок.
В разборе прайсов поставщиков нейросеть работает каждый день, потому что прайсы приходят в свободной форме и разбирать их приходится как текст. Выгрузка биллинга устроена строго, у каждого столбца понятный смысл, и считать её должен обычный код. Языковые задачи я отдаю нейросети, расчёты остаются за детерминированным кодом.
Что ещё не решено
Версия 3 сейчас у заказчика на тестировании. Программу я проверял в браузере и на соответствие формату Excel, а проверка в настоящем Excel на Windows пока за заказчиком. Открытых вопросов четыре:
- какие статусы доставки включать в счёт: у двух клиентов правило разное;
- объёмные скидки операторов применяются ко всему объёму или ступенчато;
- как каждый месяц определять число имён и шаблонов для абонплаты;
- как тарифицировать типы с «8» (A8, I8 и другие): в выгрузках их пока не было.
Правила, которые жили в голове одного специалиста, теперь записаны в файлах тарифов и во встроенной инструкции, и каждое подтверждено сверкой с его же расчётами. Для такой работы от заказчика мне понадобились две вещи: выгрузка и готовый ручной результат по ней, лучше по двум разным клиентам. Первый пример даёт гипотезы, второй их проверяет.