Lean Manufacturing at an Advertising Agency: Robotize the daily routine work of employees with AutoIt

It all started with the task of creating a report on mutual settlements with customers and suppliers. There was a prospect to collect all the data by hand. A whole department of 7 girls did not know how to approach this, given that the base consisted of more than 180 thousand lines and was maintained in MS Access.

What are we talking about

In one large advertising agency there is a “zoo”. In the sense of the "zoo" of various information systems, which were created over the years by the staff themselves, trying to somehow automate the manual work. This article will focus on automating the work of the Media Acquisition Control Department (abbreviated as “CC”). This is the unit controlling which site how much money is paid and whether there are overpayments / underpayments. Important people for business, to put it simply. It so happened that since 2010, this department has its own database of these transactions, closing documents and contracts. The database was created by an advanced manager, so it was originally created on Access 2007, with a bunch of macros, but without forms. Data storage is of course in the database file itself.
')
It all started with the task that the accounting department set for the short-circuit: upload information on all closing documents within a certain period. Roughly speaking, I needed a sign of 20 columns, including the number and date of the contract with the supplier, the status of payments for the project, the presence of acts and the like. An ordinary report that could turn the lives of 7 employees of the KZ department into hell. And the thing is that when you try to unload the necessary information, the database is hopelessly hung. Access plates were not designed to display tens of thousands of records. The engine did not anticipate queries with a dozen LEFT JOINs. Indices did not help. The head of the department came to work every day, opened the base and could not even approach the manual reunion of a tablet of a couple of tens of thousands of lines. Bookkeeping, meanwhile, set a fairly tight deadline and waited for data. Employees, leaving their children and husbands, were preparing to spend the night in the office.

Lean

After such a sad introduction, let's talk a little about theory and control. So, the advertising agency sells the service. Not an advertisement. Advertising is only a subject for conversation, something we can do well. But they pay us for what we will quickly and within the framework of the commission do for the client all the dirty work and even better than he expects. The client also expects rational proposals from us to improve his campaigns, on the best ways to achieve his indicators. Those indicators with which he reports to his leadership. It goes without saying that the client expects perfect document flow, flawless closures and phenomenal accuracy in meeting deadlines on them. Returning to our girls, I still do not understand how they provided all this with the help of a normal base on a normal accessory. But that's not the point. In short, quality service takes time. And here we will deal a little with the concept of lean production, since it is in practice directly related to the problem being analyzed here.

Lean manufacturing (in English, “lean”), in simple terms, is a way of producing a product in which the share of losses tends to zero. This concept is a creative redevelopment of Toyota’s experience. The basic idea is to identify losses in typical areas such as defective products, unjustified delays in warehouses, over-processing, downtime, shrinkage / outflow, and so on. According to some sources , the savings from the introduction of the concept of lean in large enterprises amount to billions of rubles. You ask me, what does this have to do with a service company, such as an advertising agency? The most direct.

We sell the service. For quality service takes time. Time and people are our resource. And if people sit for days and copy-paste in ekselki, then this is a loss. Losses of time and, over time, of people. Inefficient production service, if you want. It is calculated by multiplying the hourly wage rate by the number of hours spent unnecessarily. Suppose an employee of the purchasing control department receives conditional 40 thousand rubles a month for 160 hours of work. Or 250 rubles per hour. She instead of clicking on one button and get the desired report in 1 minute, copy-paste within a couple of weeks. And not one employee, but the whole department: 250 (rate) x 8 (hours per day) x 10 (working days) x 7 (girls). We get 140 thousand rubles spent on copy-paste. Losses for quality service are generally difficult to calculate, because when a report is compiled by copy-paste, the rest of the work is either worth it or it becomes unclear how.

Half-speed solution accelerating manual work

Let's move from theory to practice. So, there is a task not feasible with standard tools: unload a report. In addition, in the future there is a need to somehow put the process of creating a report on the stream, and preferably by the procurement control department itself. Below is a description of the decision, however, I would not want it to be perceived as a ready-made recipe from the Toaster , but rather as a concept for working with all such tasks. Even more. Ideally, it would be necessary to analyze all the processes of the company and identify the steps that can be automated using the model proposed below.

To solve the above two tasks, we needed: MS SQL Server 2012 Express , AutoIt , PowerQuery .

Since Access does not allow you to make complex queries from tables containing hundreds of thousands of rows, it was decided to use heavy artillery, namely MS SQL Server 2012 Express. The base installation is described in sufficient detail by the vendor, so I will not dwell on this. From Access, the corresponding tables (in which the data for the report) were exported manually through an ODBC connection previously created :


Next, we hung the indexes on all the tables we exported, which made it possible to increase the query processing speed by 4-6 times:


Then they wrote a multi-line query with a bunch of LEFT JOIN, GROUP BY and WHERE. I will not give details here, but they are not significant. Principle is important. After processing the request, do Ctrl + a (select all results). Next, go to the pre-prepared exelcuka and paste the data Ctrl + v. We put in order the formats, save, send to the accounting department The process took not two weeks, but 10 minutes. But one problem: only a very well-trained specialist can fulfill it. And any problems (for example, the error of exporting the table due to the date in the wrong format) turn into those still dances with a tambourine (any format errors are solved through export to Excel and import it already in MS SQL). In short, these crutches had to be automated.

The right decision with AutoIt

And here comes the all-powerful AutoIt. This is an extremely potential thing if the company has at least dozens of employees. There are a number of actions that a certain percentage of people perform: open timeshits on time, run the development / debugging environment (IDE + base + window with git + backup done), open the accounting system for the desired list of projects and similar things, which seem to be minor, but Dozens of minutes of working time are consumed by hundreds of people. In sum, this turns out to be a huge waste in vain, which can be quite entrusted to a software robot. But back to our problem.

So, it is necessary to automate the process described above, and there should be only two actions: start the process, get the finished report. It would be possible to write a certain program that connects first to Access, unloads data from it, then fills it in MS SQL and runs the necessary query, the results of which are then unloaded into the resulting Excel. It would take weeks of development, catching bugs. It was decided to use a ready-made application infrastructure, which will be managed by the AutoIt script. Uploading the finished report was entrusted to PowerQuery, since it was sharpened for such tasks.

In order for everything to work, it was necessary to make a macro in the source Access file in advance, which exports the data to MS SQL:

Public Function TransferKz() '      MSSQL '   Dim Tables(1 To 11) As String Dim i As Integer Tables(1) = "table1" Tables(2) = " table2" Tables(3) = " table3" '… For i = 1 To 11 DoCmd.TransferDatabase acExport, "ODBC Database", "ODBC;DSN=kz;", acTable, Tables(i), Tables(i) Next i MsgBox "Tables exported" End Function 

In addition, an Excel file has been prepared in advance that contains the PowerQuery model, which unloads the necessary data from the database. Here this model will not be considered. But you can get acquainted with the principles of PowerQuery here and here .

When you run the AutoIt script, the following happens:

1. A window is displayed with a warning that you cannot touch the keyboard and mouse while the script is running:
   
   MsgBox (0, "We are starting", "During the work of the script, you should not press any buttons and open windows. It is better to just move away from the computer for 1-2 minutes."
   
   
2. A MS Access file is copied containing the data we need:
   
   

    FileCopy($file_path_from, $file_path_to, $FC_OVERWRITE + $FC_CREATEPATH) 

   
   
3. MS SQL Management Studio starts:
   
   

    Run($ssms_path) WinWaitActive("  ") Send($sql_server_address & "{ENTER}") WinWaitActive("Microsoft SQL Server Management Studio") 

   
   
4. All tables in the short-circuit database are deleted. $ small_sleep - a variable containing the pause time before the commands. It is necessary because otherwise, applications sometimes do not have time to finally work out the command before running the following:
   
   

    Send("^{o}") Sleep($small_sleep) Send($sql_dir & $sql_file_Query_drop_tables) Sleep($small_sleep) Send("{ENTER}") Sleep($small_sleep) Send("{F5}") Sleep($small_sleep) ;    Send("^{F4}{TAB}{ENTER}") 

   
   
5. The Access file received in step 2 is launched:
   
   

    ;  Access        Run( $db_path & " /x TransferKz" ) ; : «Tables exported» WinWaitActive("[CLASS:#32770]") ;  Access Send("!+{F4}",0) 

   
   
6. Create indexes on the tables just exported:
   
   

    ;   ,    WinActivate("Solution1 - Microsoft SQL Server Management Studio") Send("^{o}") Sleep($small_sleep) Send($sql_file_indexes) Sleep($small_sleep) Send("{ENTER}") Sleep($small_sleep) Send("{F5}") Sleep($small_sleep) ;  MSSQL Management Studio Send("!+{F4}",0) 

   
   
7. Excel starts up containing the model for uploading data from MS SQl we need:
   
   

    ShellExecute($file_ excel) 

The AutoIt script is finished. Now you just need to click on the “Update” button in the open Excel file and wait for the required report to be received. The time it takes to complete the script from a mouse click to a report upload - 3.5 minutes of which 2 are pauses between steps.

findings

As casually mentioned above, we worked through the issue of solving the problem in the following ways: by radically rewriting the base of the purchasing control department from scratch, or by moving the tables to MS SQL and continuing to use MS Access as a frontend. The main obstacle to the implementation of these plans is time. Yes, you can make a base on 1C, having spent on it two or three months, money for the developer. 1C is not suitable for a holistic solution of accounting and workflow in our agency. Another system was chosen, so somehow or other we will have to redo everything from scratch soon on a new platform. The implementation of the above set of methods took 3 days. 2 of which were spent on testing an already running system and debugging a query in MS SQL. No professional developer had a hand in the code above. Everything was done on their own, in their free time from other projects. The company stayed with their money. Time is saved for something more useful than copy-paste. Lean manufacturing in action.