As I accelerated the work of the sales department more than 20 times
If you are familiar with the situation when you need to process a request from a client with a size of 150 or more positions, selecting the best price for each of the 20 price lists of 10,000 + positions in each, but you are not ready to spend more than 30 minutes on this, then welcome welcome under cat.
Imagine the following situation: you work in the sales department and you receive an application for 150+ positions of any products from more or less similar categories (for example, metal-cutting tools).
Most likely, you already have a dozen other price lists from your suppliers, each of which may have 10,000+ items, and of course you want to give the customer the most competitive offer.
It will be quite logical to assume that with very high probability, the same product from different suppliers may cost differently, and another product may be generally only from one supplier.
')
It turns out that in order to make the best offer to the client, you should search for each position consistently from all suppliers on a number of criteria (for example, find the cheapest offer or offer with the right amount in stock).
But there is one nuance - since all price lists are different people, in almost 80% of cases they will call the same product at least a little, but in different ways.
Figure 1 - Different variants of the names
So, if each product is not assigned its own unique and generally accepted identifier in all price lists and in all requests from customers, then just use the CRF function and even the Lookup, unfortunately will not work, and you, no matter how sad it would be, will have to do a long and not very interesting work, which can be delayed for a day, and maybe for all two.
When I came to work in the sales department of one Russian manufacturer of metal-cutting tools, it turned out that such applications do not come once a day and the sooner the client gets an answer, the higher the likelihood that the sale will occur.
And to me, as a newcomer, they bring a pack of A4 sheets on which an application of 1000+ positions is printed out in 11 fonts, and the prices are signed with a pen (!). Yes, about each indicated with or without VAT.
As I was told, the most difficult things have already been done for me, so it only remains to transfer 1000+ prices to an electronic form without errors and to invoice.
Indeed, “Not at all difficult!” - I thought, and began to hit the keys of the numpad.
After 25 minutes of monotonous work and gazing is not always clear, in this case - the female handwriting, I suddenly thought: “Something is wrong here. So it definitely shouldn't be! ”
To understand why this should not be, and how it should be in fact, I decided to look at the whole process step by step, and asking my colleagues, I learned how it all works:
Worse than words you can imagine. It’s good that my colleague from the supply department didn’t hear - I thought, and I began to think how to arrange this process so that the eyes and hands of two people could not be erased before the mazol, even with zero result in the end.
The next application I was given to process already myself. I did the same thing as my colleague, but I did not print the spreadsheet, but opened it in the left half of the screen, placing three price lists in the right half.
Starting from the first position, I found it in all three prices and copied the cell with the best price in the column to the right. After repeating the action n times, I got the feeling that I know how to do this work a hundred times faster!
I thought - You just need to reduce all the price lists into one big table, and right away bring the best price to the cell to the right of the position request, and bring the name of the position with the best price to the right to check.
Fine! That same evening, I set to work.
The whole problem was to explain to the system which position is suitable and which is not ...
At this point, I had the experience of creating a system on pure formulas in Excel, which takes the names of garments in English and translates them into similar names in Russian.
It defines the category, color, gender, brand, etc. and it works like this: if the name has the word hat, then the Russian name has the word “hat”, then if there is yellow or yell (some wrote this way), we get the name “yellow hat”, the same with the rest of the words and as a result we get the name - “Yellow Ski-doo men's hat”.
The same is done with the description, and then downloaded to the online store. Thus, I processed thousands of products with 90% accuracy. Based on this, I began work on a new system.
The first thing I needed was a category definition, for example, if the name contains “Drill” or ”drills”, then the category “Drill” is assigned, and then the definition of its parameters begins.
If the word “Drill” was most often written in one of 5 options (Drill, drill, drill, drill., Drill), then everyone wrote the parameters of this drill to the best of their competence, as in Figure 1.
The next and most difficult step is to define and bring to one kind these categories for each position.
I decided to do this with regular expressions. But how to write an expression that will cover the maximum variation of the recording characteristics? Of course, one expression is indispensable, and in order to speed up the work, it is necessary to understand which characterization description structures are used most often, and cover them first.
Since I knew that I would have to do the same for a dozen other categories, I decided to automate it immediately and took up the system for identifying the most frequently used templates in the product description.
This draws on a separate article, so I will immediately describe what happened:
10 regular expressions that define from 1 to 5 digital parameters.
3 expressions defining GOST.
The remaining parameters, such as the metal grade or the shape of the shank, were determined by a simple condition for the content of the text in the title.
The result was 10 characteristics.
I immediately conducted the first test, on drills and everything worked! I received a ready list with a typical description of all items in the price lists.
Figure 2 - Typical Description
Then I did the same for the request - I brought the names to a type view. Now, it remains only to use the good old CDF, and now, after a couple of seconds, I have a ready list with the best prices and the original names of the selected positions. In addition, I immediately had information about who the supplier is and how many items there are in the warehouse. This is useful if there is a sale - I will not forget where the prices are.
This was only the first step.
Since each category has its own characteristics, each also needs its own search pattern. After spending a couple of days, I made a table with templates for 8 main categories, and was able to correctly determine approximately 70% of the positions. But even with such accuracy, I was able to process the majority of applications not only faster than colleagues, but also faster than all competitors.
As soon as they saw this in the department, all the applications flowed in to me for elaboration, and it became clear - either I would share the system with everyone, or return to what I wanted to leave - get bogged down in monotonous work.
Since the system worked on Excel and macros and contained ~ 100,000 positions, it took about 40 minutes to update every day and it was impossible to work in the tables. So, installing the same file on each computer, I decided not to even try, not to mention that each user would have to correct errors and make changes to the system separately. It was necessary to quickly search for a suitable, easy to transfer system option, and I decided to try Google spreadsheets. This should solve a number of problems:
All price lists will be reduced and updated in the cloud on a schedule and all users will have access to them.
It will be possible to work from any place where there is Internet, even from a smartphone.
I can upgrade and correct the system at least from where and at once for all users.
In the beginning, I tried to do everything on the same formulas, but when the table of 100 positions was processed for 3 minutes, I realized that the formulas would have to be abandoned, and I began to study the Google documentation. The app script (gas) is the same js, only with the addition functions from google.
After transferring all the logic to gas, I discovered the following problem: if only one characteristic was described in the request, for example, only the drill diameter, then most of the prices had a diameter x length, and since the system determines the maximum parameters, then only positions in the selection Which number of the described characteristics coincides with their number in the request. This is a serious flaw and it needed to be corrected.
Here I used regular expressions again and each line from the query I just converted to a line with typical characteristics, I turned into a regular expression where the missing characteristic '*' was replaced with '. *?' and it meant that the system will skip this characteristic during the search and move on to the next one.
But here I am faced with a new problem - it is not always necessary to choose the most inexpensive position, sometimes you need to choose the longest or shortest drill bit, see what kind of drills have a given diameter, or who has the most in stock.
Here the solution turned out to be simpler - I make a pass through all the lines, collect suitable positions for the template and bring them to the drop-down list, indicating all the information.
The worksheet now looks like this:
Figure 3 - Sheet for matching applications and prices
The entire query is inserted into the first column in a tabular form, into the second quantity. The third column automatically gets typed descriptions. In the fourth column, a drop-down list is added to each row from all suitable positions that the system was able to find, sorted by default by price, but if necessary, you can switch to sorting by quantity in stock or supplier.
Now the last thing to do is to invoice. For this, a template is formed in the columns on the right, which can be quickly loaded into 1C:
Figure 4 - Template for loading in 1C
The template contains comprehensive information - the supplier with its article number, title, quantity, and price. From here you can send requests to accounts to different suppliers, or create an account without going to 1C, as you need.
Total:
Processing applications became ~ 20 times faster (30 minutes instead of 1 day for an application from 100 positions), selection at optimal prices has become several times more efficient, since now there is no problem that someone is too lazy to go through all prices or make inquiries for all suppliers.
Future plans:
Learn how to use and translate massive calculations on a google engine, translate large tables into big data, and write a neural network to quickly identify patterns. So I would be very grateful if someone shares their experience about using them in the comments.
Afterword:
In the process, dozens of useful functions were written that can be useful to many who are already working in gas, and even to those who are just going to. For example: automatically downloading files from Gmail or a link to Gdrive and converting them into google table format, searching and copying strings from files on a disk by a word in the title, and so on. They will be published in the following articles.
In the case of interest in this article, I will describe in more detail how the program works with code inserts.
Thanks for reading.
Imagine the following situation: you work in the sales department and you receive an application for 150+ positions of any products from more or less similar categories (for example, metal-cutting tools).
Most likely, you already have a dozen other price lists from your suppliers, each of which may have 10,000+ items, and of course you want to give the customer the most competitive offer.
It will be quite logical to assume that with very high probability, the same product from different suppliers may cost differently, and another product may be generally only from one supplier.
')
It turns out that in order to make the best offer to the client, you should search for each position consistently from all suppliers on a number of criteria (for example, find the cheapest offer or offer with the right amount in stock).
But there is one nuance - since all price lists are different people, in almost 80% of cases they will call the same product at least a little, but in different ways.
Figure 1 - Different variants of the names
So, if each product is not assigned its own unique and generally accepted identifier in all price lists and in all requests from customers, then just use the CRF function and even the Lookup, unfortunately will not work, and you, no matter how sad it would be, will have to do a long and not very interesting work, which can be delayed for a day, and maybe for all two.
Start
When I came to work in the sales department of one Russian manufacturer of metal-cutting tools, it turned out that such applications do not come once a day and the sooner the client gets an answer, the higher the likelihood that the sale will occur.
And to me, as a newcomer, they bring a pack of A4 sheets on which an application of 1000+ positions is printed out in 11 fonts, and the prices are signed with a pen (!). Yes, about each indicated with or without VAT.
As I was told, the most difficult things have already been done for me, so it only remains to transfer 1000+ prices to an electronic form without errors and to invoice.
Indeed, “Not at all difficult!” - I thought, and began to hit the keys of the numpad.
After 25 minutes of monotonous work and gazing is not always clear, in this case - the female handwriting, I suddenly thought: “Something is wrong here. So it definitely shouldn't be! ”
To understand why this should not be, and how it should be in fact, I decided to look at the whole process step by step, and asking my colleagues, I learned how it all works:
- The application comes by mail, usually in .xls format.
- It is printed and given to the supply department.
- There, a lovely girl studies him carefully, opens the necessary price lists on the monitor and begins to select.
She knows about what and in what price lists to look for and where it is most likely cheaper, but no matter how well she was not oriented in all this, for more than 10 years of experience she would not have been able to memorize even hundreds of prices for the most popular positions, at least because they change at least once a year. - After a couple of days, and sometimes weeks, the completed table falls into the hands of a sales manager, that is, into mine. And the work that I have just been doing begins.
- I issue an invoice, I am glad to call the customer to inform about it, and he responds to me:
“Thank you very much for the offer, but I already paid the invoice that another company issued to me two days ago.”
Worse than words you can imagine. It’s good that my colleague from the supply department didn’t hear - I thought, and I began to think how to arrange this process so that the eyes and hands of two people could not be erased before the mazol, even with zero result in the end.
The next application I was given to process already myself. I did the same thing as my colleague, but I did not print the spreadsheet, but opened it in the left half of the screen, placing three price lists in the right half.
Starting from the first position, I found it in all three prices and copied the cell with the best price in the column to the right. After repeating the action n times, I got the feeling that I know how to do this work a hundred times faster!
Decision
I thought - You just need to reduce all the price lists into one big table, and right away bring the best price to the cell to the right of the position request, and bring the name of the position with the best price to the right to check.
Fine! That same evening, I set to work.
The whole problem was to explain to the system which position is suitable and which is not ...
At this point, I had the experience of creating a system on pure formulas in Excel, which takes the names of garments in English and translates them into similar names in Russian.
It defines the category, color, gender, brand, etc. and it works like this: if the name has the word hat, then the Russian name has the word “hat”, then if there is yellow or yell (some wrote this way), we get the name “yellow hat”, the same with the rest of the words and as a result we get the name - “Yellow Ski-doo men's hat”.
The same is done with the description, and then downloaded to the online store. Thus, I processed thousands of products with 90% accuracy. Based on this, I began work on a new system.
The first thing I needed was a category definition, for example, if the name contains “Drill” or ”drills”, then the category “Drill” is assigned, and then the definition of its parameters begins.
If the word “Drill” was most often written in one of 5 options (Drill, drill, drill, drill., Drill), then everyone wrote the parameters of this drill to the best of their competence, as in Figure 1.
The next and most difficult step is to define and bring to one kind these categories for each position.
I decided to do this with regular expressions. But how to write an expression that will cover the maximum variation of the recording characteristics? Of course, one expression is indispensable, and in order to speed up the work, it is necessary to understand which characterization description structures are used most often, and cover them first.
Since I knew that I would have to do the same for a dozen other categories, I decided to automate it immediately and took up the system for identifying the most frequently used templates in the product description.
This draws on a separate article, so I will immediately describe what happened:
10 regular expressions that define from 1 to 5 digital parameters.
3 expressions defining GOST.
The remaining parameters, such as the metal grade or the shape of the shank, were determined by a simple condition for the content of the text in the title.
The result was 10 characteristics.
I immediately conducted the first test, on drills and everything worked! I received a ready list with a typical description of all items in the price lists.
Figure 2 - Typical Description
Then I did the same for the request - I brought the names to a type view. Now, it remains only to use the good old CDF, and now, after a couple of seconds, I have a ready list with the best prices and the original names of the selected positions. In addition, I immediately had information about who the supplier is and how many items there are in the warehouse. This is useful if there is a sale - I will not forget where the prices are.
This was only the first step.
Since each category has its own characteristics, each also needs its own search pattern. After spending a couple of days, I made a table with templates for 8 main categories, and was able to correctly determine approximately 70% of the positions. But even with such accuracy, I was able to process the majority of applications not only faster than colleagues, but also faster than all competitors.
As soon as they saw this in the department, all the applications flowed in to me for elaboration, and it became clear - either I would share the system with everyone, or return to what I wanted to leave - get bogged down in monotonous work.
Since the system worked on Excel and macros and contained ~ 100,000 positions, it took about 40 minutes to update every day and it was impossible to work in the tables. So, installing the same file on each computer, I decided not to even try, not to mention that each user would have to correct errors and make changes to the system separately. It was necessary to quickly search for a suitable, easy to transfer system option, and I decided to try Google spreadsheets. This should solve a number of problems:
All price lists will be reduced and updated in the cloud on a schedule and all users will have access to them.
It will be possible to work from any place where there is Internet, even from a smartphone.
I can upgrade and correct the system at least from where and at once for all users.
In the beginning, I tried to do everything on the same formulas, but when the table of 100 positions was processed for 3 minutes, I realized that the formulas would have to be abandoned, and I began to study the Google documentation. The app script (gas) is the same js, only with the addition functions from google.
After transferring all the logic to gas, I discovered the following problem: if only one characteristic was described in the request, for example, only the drill diameter, then most of the prices had a diameter x length, and since the system determines the maximum parameters, then only positions in the selection Which number of the described characteristics coincides with their number in the request. This is a serious flaw and it needed to be corrected.
Here I used regular expressions again and each line from the query I just converted to a line with typical characteristics, I turned into a regular expression where the missing characteristic '*' was replaced with '. *?' and it meant that the system will skip this characteristic during the search and move on to the next one.
But here I am faced with a new problem - it is not always necessary to choose the most inexpensive position, sometimes you need to choose the longest or shortest drill bit, see what kind of drills have a given diameter, or who has the most in stock.
Here the solution turned out to be simpler - I make a pass through all the lines, collect suitable positions for the template and bring them to the drop-down list, indicating all the information.
The worksheet now looks like this:
Figure 3 - Sheet for matching applications and prices
The entire query is inserted into the first column in a tabular form, into the second quantity. The third column automatically gets typed descriptions. In the fourth column, a drop-down list is added to each row from all suitable positions that the system was able to find, sorted by default by price, but if necessary, you can switch to sorting by quantity in stock or supplier.
Now the last thing to do is to invoice. For this, a template is formed in the columns on the right, which can be quickly loaded into 1C:
Figure 4 - Template for loading in 1C
The template contains comprehensive information - the supplier with its article number, title, quantity, and price. From here you can send requests to accounts to different suppliers, or create an account without going to 1C, as you need.
Total:
Processing applications became ~ 20 times faster (30 minutes instead of 1 day for an application from 100 positions), selection at optimal prices has become several times more efficient, since now there is no problem that someone is too lazy to go through all prices or make inquiries for all suppliers.
Future plans:
Learn how to use and translate massive calculations on a google engine, translate large tables into big data, and write a neural network to quickly identify patterns. So I would be very grateful if someone shares their experience about using them in the comments.
Afterword:
In the process, dozens of useful functions were written that can be useful to many who are already working in gas, and even to those who are just going to. For example: automatically downloading files from Gmail or a link to Gdrive and converting them into google table format, searching and copying strings from files on a disk by a word in the title, and so on. They will be published in the following articles.
In the case of interest in this article, I will describe in more detail how the program works with code inserts.
Thanks for reading.
Source: https://habr.com/ru/post/348250/
All Articles