Если вам знакома ситуация, когда нужно обработать заявку от клиента размером 150 или больше позиций, подобрав для каждой самую лучшую по цене из 20 прайс-листов по 10 000 + позиций в каждом, но вы не готовы тратить на это больше 30 минут, то добро пожаловать под кат.
Представьте себе следующую ситуацию: вы работаете в отделе продаж и вам поступает заявка на 150+ позиций какой-либо продукции из более или менее схожих категорий (например металлорежущий инструмент).
Скорее всего у вас уже есть десяток другой прайс-листов от ваших поставщиков, в каждом из которых возможно по 10 000 + позиций, и вы конечно хотите дать клиенту самое конкурентоспособное предложение.
Будет совершенно логично предположить, что с очень большой вероятностью, один и тот же товар у разных поставщиков может стоить по разному, а другой товар возможно будет вообще только у одного поставщика.
Получается, чтобы сделать лучшее предложение клиенту вам следует заняться поиском каждой позиции последовательно у всех поставщиков по ряду критериев (например найти самое дешевое предложение или предложение с подходящим количеством на складе).
Но есть один нюанс — так как все прайс-листы составляют разные люди, то почти в 80% случаев один и тот же товар они назовут хоть немного, но по разному.
Рисунок 1 — Разные варианта названий свёрел
Так что, если каждому товару не присвоен свой уникальный и общепринятый идентификатор во всех прайс-листах и во всех запросах от клиентов, то просто так использовать функцию ВПР и даже Lookup, к сожалению не получится, и вам, как бы это не было грустно, придется заниматься долгой и не очень интересной работой, которая затянется может на день, а может и на все два.
Когда я пришел на работу в отдел продаж одного российского завода — изготовителя металлорежущего инструмента, оказалось такие заявки приходят не по одному разу в день и чем быстрее клиент получает ответ, тем выше вероятность, что произойдет продажа.
И вот мне, как новенькому приносят пачку листов А4 на которых 11 шрифтом распечатана заявка из 1000+ позиций, а рядом подписаны цены ручкой (!). Да еще, около каждой указано с НДС она или без.
Как мне сказали — самое сложное уже сделали за меня, так что остается всего лишь перенести без ошибок 1000+ цен в электронный вид и выставить счет.
И вправду, “Совсем не трудно!” — подумал я, и начал бить по клавишам нумпада.
Спустя 25 минут монотонной работы и разглядывания не всегда понятного, в данном случае — женского почерка, я вдруг подумал: “Что — то здесь не так. Так быть точно не должно!”
Чтобы понять почему так не должно быть, и как быть должно на самом деле, я решил взглянуть на весь процесс поэтапно, и расспросив коллег, узнал о том как все устроено:
Страшнее слов не придумаешь. Хорошо что моя коллега из отдела снабжения не слышала — подумал я, и начал размышлять как устроить этот процесс так, чтобы глаза и руки сразу двух человек не стирались до мазолей, ещё и с нулевым результатом в итоге.
Следующую заявку мне дали обрабатывать уже самому. Я сделал тоже самое что и моя коллега, но электронную таблицу уже не распечатывал, а открыл в левой половине экрана, разместив в правой три нужных прайс-листа.
Начав с первой позиции, я нашел её во всех трех прайсах и скопировал ячейку с самой выгодной ценой в столбец справа. Повторив действие n раз, возникло ощущение, что я знаю как сделать эту работу в сотню раз быстрее!
Я подумал — Нужно просто свести все прайс-листы в одну большую таблицу, а в ячейку справа от позиции запроса сразу вывести самую лучшую цену, а рядом для проверки вывести название это самой позиции с лучшей ценой.
Отлично! В тот же вечер, я приступил к работе.
Вся проблема заключалась в том, чтобы объяснить системе какая позиция подходящая, а какая нет…
К этому моменту у меня был опыт создания системы на чистых формулах в Excel, которая берет названия товаров одежды на английском языке и переводит их в однотипные названия на русском.
Она определяет категорию, цвет, пол, бренд и т.д. и работает так: если в названии есть слово hat, то в русском названии появляется слово “шапка”, дальше если есть yellow или yell (некоторые писали так) — получаем название “желтая шапка”, тоже самое с остальными словами и в итоге получаем название — “Желтая мужская шапка Ski-doo”.
Тоже самое делается и с описанием, а потом загружается в интернет магазин. Таким образом, я с 90% точностью обрабатывал тысячи товаров. Основываясь на этом, я и начал работу над новой системой.
Первое что мне требовалось — это определение категории, например если в названии есть “Сверло” или ”сверла”, то присваивается категория “Сверло”, а дальше начинается определение его параметров.
Если слово “Сверло” было чаще всего написано в одном из 5 вариантов (Сверло, сверло, сверла, сверл., Свёрла), то параметры этого сверла каждый писал в меру своей компетентности, как на рисунке 1.
Следующий и самый сложный шаг — определить и привести к одному виду эти самые категории для каждой позиции.
Сделать это я решил с помощью регулярных выражений. Но как написать такое выражение, которое охватит максимум вариаций записи характеристик? Конечно одним выражением не обойтись, и для ускорения работы нужно понять какие структуры описания характеристик используют чаще всего, и охватить их в первую очередь.
Так как я знал, что мне придется делать тоже самое еще для десятка другого категорий, решил сразу это автоматизировать и занялся системой выявления наиболее часто используемых шаблонов в описании товаров.
Это тянет на отдельную статью, поэтому сразу опишу что получилось:
10 регулярных выражений, которые определяют от 1 до 5 цифровых параметров.
3 выражения, определяющих ГОСТ.
Остальные параметры, такие как марка металла или форма хвостовика, определял простым условием содержания текста в названии.
В итоге получилось 10 характеристик.
Я сразу провел первый тест, на сверлах и все сработало! я получил готовый список с типовым описанием всех позиций в прайс-листах.
Рисунок 2 — Типовое описание
Дальше сделал тоже самое для запроса — привел названия к типовому виду. Теперь, осталось только воспользоваться старым добрым ВПР, и вот спустя пару секунд у меня есть готовый список с самыми лучшими ценами и изначальными названиями подобранных позиций. Кроме этого у меня сразу была информация кто поставщик и сколько есть позиций на складе. Это полезно, в случае если случится продажа — я не забуду где какие цены взял.
Это был только первый шаг.
Так как у каждой категории свои характеристики, то для каждой нужен и свой шаблон их поиска. Потратив пару дней, я составил таблицу с шаблонами для 8 основных категорий, и смог правильно определить примерно 70% позиций. Но даже с такой точностью, я смог обрабатывать большинство заявок не только быстрее коллег, но и быстрее всех конкурентов.
Как только это увидели в отделе, все заявки полились рекой ко мне на проработку, и стало ясно — либо я поделюсь системой со всеми, либо вернусь к тому от чего хотел уйти — погрязну в монотонной работе.
Так как система работала на Excel и макросах и содержала ~100 000 позиций, то на ежедневное обновление уходило около 40 минут и при этом работать в таблицах было невозможно. Так что устанавливать один и тот же файл на каждый компьютер, я решил даже и не пытаться, не говоря уже о том, что исправлять ошибки и вносить изменения в систему пришлось бы у каждого пользователя отдельно. Нужно было быстро искать подходящий, легкий для переноса системы вариант, и я решил попробовал Google spreadsheets. Это должно было решить ряд проблем:
Все прайс-листы будут сводиться и обновляются в облаке по расписанию и к ним будут имеют доступ все пользователи.
Работать можно будет из любого места где есть интернет, хоть со смартфона.
Модернизировать и исправлять систему я смогу хоть откуда и сразу для всех пользователей.
В начале, я попробовал сделать все на таких же формулах, но когда таблица из 100 позиций обрабатывалась уже 3 минуты, я понял что от формул придется отказаться, и начал изучать документацию Google app script (gas) — это тот же js, только с добавлением функций от гугла.
После переноса всей логики в gas, я обнаружил следующую проблему: если в запросе была описана только одна характеристика, например только диаметр сверла, то в большинстве прайсов были диаметр х длина, а так как система определяет максимум параметров, то в выборе участвуют только позиции в которых количество описанных характеристик совпадает с их количеством в запросе. Это серьезная недоработка и ее нужно было исправлять.
Здесь я снова воспользовался регулярными выражениями и каждую строку из запроса которую только что преобразовал в строку с типовыми характеристиками, я превратил в регулярное выражение, где отсутствующая характеристика ‘*’ заменялась на ‘.*?’ и означала что система при поиске пропустит эту характеристику и перейдет к следующей.
Но здесь я столкнулся с новой проблемой — не всегда нужно выбрать именно самую недорогую позицию, иногда нужно выбрать самое длинное или короткое сверло, посмотреть какие вообще есть сверла с заданным диаметром, или у кого их больше всего на складе.
Здесь решение оказалось проще — делаю проход по всем строкам, собираю подходящие под шаблон позиции и вывожу их в выпадающий список, с указанием всей информации.
Рабочий лист теперь выглядит так:
Рисунок 3 — Лист для сопоставления заявки и прайсов
В первый столбец вставляется целиком запрос в табличном виде, во второй количество. В третий столбец автоматически попадают типизированные описания. В четвертом столбце в каждую строку добавляется раскрывающийся список из всех подходящих позиций, которые удалось найти системе, по умолчанию сортируются по возрастанию цены, но если нужно, можно переключить на сортировку по количеству на складе или поставщику.
Теперь осталось самое последнее — выставить счет. Для этого в столбцах справа формируется шаблон, который можно быстро загрузить в 1С:
Рисунок 4 — шаблон для загрузки в 1С
Шаблон содержит в себе исчерпывающую информацию — поставщика с его артикулом по позиции, название, количество, и цену. Отсюда же можно отправлять запросы на счета разным поставщикам, или создавать счет не заходя в 1С, кому как нужно.
Итог:
Обрабатывать заявки стали ~ в 20 раз быстрее (30 минут вместо 1 дня на заявку из 100 позиций), подбор по оптимальным ценам стал в разы эффективнее, так как теперь нет проблемы что кому-то лень перебирать все прайсы или делать запросы по всем поставщикам.
Планы на будущее:
Научиться использовать и перевести массивные вычисления на google engine, большие таблицы перевести в big data, а для быстрого выявления шаблонов написать нейросеть. Так что буду очень признателен, если кто-то поделится опытом об их использовании в комментариях.
Послесловие:
В процессе работы были написаны десятки полезных функций, которые могут быть полезными многим, кто уже работает в gas, и даже тем кто только собирается. Например: автоматическая загрузка файлов из Gmail или по ссылке на Gdrive и преобразование их в формат google таблиц, поиск и копирование строк из файлов на диске по слову в названии, и так далее. Их буду публиковать в следующих статьях.
В случае интереса к этой статье, подробнее опишу работу программы со вставками кода.
Спасибо за прочтение.
Представьте себе следующую ситуацию: вы работаете в отделе продаж и вам поступает заявка на 150+ позиций какой-либо продукции из более или менее схожих категорий (например металлорежущий инструмент).
Скорее всего у вас уже есть десяток другой прайс-листов от ваших поставщиков, в каждом из которых возможно по 10 000 + позиций, и вы конечно хотите дать клиенту самое конкурентоспособное предложение.
Будет совершенно логично предположить, что с очень большой вероятностью, один и тот же товар у разных поставщиков может стоить по разному, а другой товар возможно будет вообще только у одного поставщика.
Получается, чтобы сделать лучшее предложение клиенту вам следует заняться поиском каждой позиции последовательно у всех поставщиков по ряду критериев (например найти самое дешевое предложение или предложение с подходящим количеством на складе).
Но есть один нюанс — так как все прайс-листы составляют разные люди, то почти в 80% случаев один и тот же товар они назовут хоть немного, но по разному.
Рисунок 1 — Разные варианта названий свёрел
Так что, если каждому товару не присвоен свой уникальный и общепринятый идентификатор во всех прайс-листах и во всех запросах от клиентов, то просто так использовать функцию ВПР и даже Lookup, к сожалению не получится, и вам, как бы это не было грустно, придется заниматься долгой и не очень интересной работой, которая затянется может на день, а может и на все два.
Начало
Когда я пришел на работу в отдел продаж одного российского завода — изготовителя металлорежущего инструмента, оказалось такие заявки приходят не по одному разу в день и чем быстрее клиент получает ответ, тем выше вероятность, что произойдет продажа.
И вот мне, как новенькому приносят пачку листов А4 на которых 11 шрифтом распечатана заявка из 1000+ позиций, а рядом подписаны цены ручкой (!). Да еще, около каждой указано с НДС она или без.
Как мне сказали — самое сложное уже сделали за меня, так что остается всего лишь перенести без ошибок 1000+ цен в электронный вид и выставить счет.
И вправду, “Совсем не трудно!” — подумал я, и начал бить по клавишам нумпада.
Спустя 25 минут монотонной работы и разглядывания не всегда понятного, в данном случае — женского почерка, я вдруг подумал: “Что — то здесь не так. Так быть точно не должно!”
Чтобы понять почему так не должно быть, и как быть должно на самом деле, я решил взглянуть на весь процесс поэтапно, и расспросив коллег, узнал о том как все устроено:
- Заявка приходит по почте, обычно в формате .xls
- Ее распечатывают и отдают в отдел снабжения.
- Там милая девушка внимательно его изучает, открывает на мониторе необходимые прайсы и начинает подбирать.
Она примерно знает что и в каких прайс-листах искать и где скорее всего дешевле, но как бы хорошо она не ориентировалась во всем этом, за более чем 10 летний опыт ей не удалось бы выучить наизусть даже сотни цен на самые популярные позиции, хотя бы потому что они меняются как минимум раз в год. - Спустя пару дней, а иногда недель, заполненная таблица попадает в руки менеджера по продажам, то есть в мои. И начинается та самая работа, которой я только что занимался.
- Я выставляю счет, радостный звоню заказчику что бы об этом сообщить, а он мне в ответ:
“Большое спасибо за предложение, но я уже оплатил счет, который мне выставила другая компания два дня назад.”
Страшнее слов не придумаешь. Хорошо что моя коллега из отдела снабжения не слышала — подумал я, и начал размышлять как устроить этот процесс так, чтобы глаза и руки сразу двух человек не стирались до мазолей, ещё и с нулевым результатом в итоге.
Следующую заявку мне дали обрабатывать уже самому. Я сделал тоже самое что и моя коллега, но электронную таблицу уже не распечатывал, а открыл в левой половине экрана, разместив в правой три нужных прайс-листа.
Начав с первой позиции, я нашел её во всех трех прайсах и скопировал ячейку с самой выгодной ценой в столбец справа. Повторив действие n раз, возникло ощущение, что я знаю как сделать эту работу в сотню раз быстрее!
Решение
Я подумал — Нужно просто свести все прайс-листы в одну большую таблицу, а в ячейку справа от позиции запроса сразу вывести самую лучшую цену, а рядом для проверки вывести название это самой позиции с лучшей ценой.
Отлично! В тот же вечер, я приступил к работе.
Вся проблема заключалась в том, чтобы объяснить системе какая позиция подходящая, а какая нет…
К этому моменту у меня был опыт создания системы на чистых формулах в Excel, которая берет названия товаров одежды на английском языке и переводит их в однотипные названия на русском.
Она определяет категорию, цвет, пол, бренд и т.д. и работает так: если в названии есть слово hat, то в русском названии появляется слово “шапка”, дальше если есть yellow или yell (некоторые писали так) — получаем название “желтая шапка”, тоже самое с остальными словами и в итоге получаем название — “Желтая мужская шапка Ski-doo”.
Тоже самое делается и с описанием, а потом загружается в интернет магазин. Таким образом, я с 90% точностью обрабатывал тысячи товаров. Основываясь на этом, я и начал работу над новой системой.
Первое что мне требовалось — это определение категории, например если в названии есть “Сверло” или ”сверла”, то присваивается категория “Сверло”, а дальше начинается определение его параметров.
Если слово “Сверло” было чаще всего написано в одном из 5 вариантов (Сверло, сверло, сверла, сверл., Свёрла), то параметры этого сверла каждый писал в меру своей компетентности, как на рисунке 1.
Следующий и самый сложный шаг — определить и привести к одному виду эти самые категории для каждой позиции.
Сделать это я решил с помощью регулярных выражений. Но как написать такое выражение, которое охватит максимум вариаций записи характеристик? Конечно одним выражением не обойтись, и для ускорения работы нужно понять какие структуры описания характеристик используют чаще всего, и охватить их в первую очередь.
Так как я знал, что мне придется делать тоже самое еще для десятка другого категорий, решил сразу это автоматизировать и занялся системой выявления наиболее часто используемых шаблонов в описании товаров.
Это тянет на отдельную статью, поэтому сразу опишу что получилось:
10 регулярных выражений, которые определяют от 1 до 5 цифровых параметров.
3 выражения, определяющих ГОСТ.
Остальные параметры, такие как марка металла или форма хвостовика, определял простым условием содержания текста в названии.
В итоге получилось 10 характеристик.
Я сразу провел первый тест, на сверлах и все сработало! я получил готовый список с типовым описанием всех позиций в прайс-листах.
Рисунок 2 — Типовое описание
Дальше сделал тоже самое для запроса — привел названия к типовому виду. Теперь, осталось только воспользоваться старым добрым ВПР, и вот спустя пару секунд у меня есть готовый список с самыми лучшими ценами и изначальными названиями подобранных позиций. Кроме этого у меня сразу была информация кто поставщик и сколько есть позиций на складе. Это полезно, в случае если случится продажа — я не забуду где какие цены взял.
Это был только первый шаг.
Так как у каждой категории свои характеристики, то для каждой нужен и свой шаблон их поиска. Потратив пару дней, я составил таблицу с шаблонами для 8 основных категорий, и смог правильно определить примерно 70% позиций. Но даже с такой точностью, я смог обрабатывать большинство заявок не только быстрее коллег, но и быстрее всех конкурентов.
Как только это увидели в отделе, все заявки полились рекой ко мне на проработку, и стало ясно — либо я поделюсь системой со всеми, либо вернусь к тому от чего хотел уйти — погрязну в монотонной работе.
Так как система работала на Excel и макросах и содержала ~100 000 позиций, то на ежедневное обновление уходило около 40 минут и при этом работать в таблицах было невозможно. Так что устанавливать один и тот же файл на каждый компьютер, я решил даже и не пытаться, не говоря уже о том, что исправлять ошибки и вносить изменения в систему пришлось бы у каждого пользователя отдельно. Нужно было быстро искать подходящий, легкий для переноса системы вариант, и я решил попробовал Google spreadsheets. Это должно было решить ряд проблем:
Все прайс-листы будут сводиться и обновляются в облаке по расписанию и к ним будут имеют доступ все пользователи.
Работать можно будет из любого места где есть интернет, хоть со смартфона.
Модернизировать и исправлять систему я смогу хоть откуда и сразу для всех пользователей.
В начале, я попробовал сделать все на таких же формулах, но когда таблица из 100 позиций обрабатывалась уже 3 минуты, я понял что от формул придется отказаться, и начал изучать документацию Google app script (gas) — это тот же js, только с добавлением функций от гугла.
После переноса всей логики в gas, я обнаружил следующую проблему: если в запросе была описана только одна характеристика, например только диаметр сверла, то в большинстве прайсов были диаметр х длина, а так как система определяет максимум параметров, то в выборе участвуют только позиции в которых количество описанных характеристик совпадает с их количеством в запросе. Это серьезная недоработка и ее нужно было исправлять.
Здесь я снова воспользовался регулярными выражениями и каждую строку из запроса которую только что преобразовал в строку с типовыми характеристиками, я превратил в регулярное выражение, где отсутствующая характеристика ‘*’ заменялась на ‘.*?’ и означала что система при поиске пропустит эту характеристику и перейдет к следующей.
Но здесь я столкнулся с новой проблемой — не всегда нужно выбрать именно самую недорогую позицию, иногда нужно выбрать самое длинное или короткое сверло, посмотреть какие вообще есть сверла с заданным диаметром, или у кого их больше всего на складе.
Здесь решение оказалось проще — делаю проход по всем строкам, собираю подходящие под шаблон позиции и вывожу их в выпадающий список, с указанием всей информации.
Рабочий лист теперь выглядит так:
Рисунок 3 — Лист для сопоставления заявки и прайсов
В первый столбец вставляется целиком запрос в табличном виде, во второй количество. В третий столбец автоматически попадают типизированные описания. В четвертом столбце в каждую строку добавляется раскрывающийся список из всех подходящих позиций, которые удалось найти системе, по умолчанию сортируются по возрастанию цены, но если нужно, можно переключить на сортировку по количеству на складе или поставщику.
Теперь осталось самое последнее — выставить счет. Для этого в столбцах справа формируется шаблон, который можно быстро загрузить в 1С:
Рисунок 4 — шаблон для загрузки в 1С
Шаблон содержит в себе исчерпывающую информацию — поставщика с его артикулом по позиции, название, количество, и цену. Отсюда же можно отправлять запросы на счета разным поставщикам, или создавать счет не заходя в 1С, кому как нужно.
Итог:
Обрабатывать заявки стали ~ в 20 раз быстрее (30 минут вместо 1 дня на заявку из 100 позиций), подбор по оптимальным ценам стал в разы эффективнее, так как теперь нет проблемы что кому-то лень перебирать все прайсы или делать запросы по всем поставщикам.
Планы на будущее:
Научиться использовать и перевести массивные вычисления на google engine, большие таблицы перевести в big data, а для быстрого выявления шаблонов написать нейросеть. Так что буду очень признателен, если кто-то поделится опытом об их использовании в комментариях.
Послесловие:
В процессе работы были написаны десятки полезных функций, которые могут быть полезными многим, кто уже работает в gas, и даже тем кто только собирается. Например: автоматическая загрузка файлов из Gmail или по ссылке на Gdrive и преобразование их в формат google таблиц, поиск и копирование строк из файлов на диске по слову в названии, и так далее. Их буду публиковать в следующих статьях.
В случае интереса к этой статье, подробнее опишу работу программы со вставками кода.
Спасибо за прочтение.
