Automation has been an industry buzzword since it was first used to describe the current colloquial understanding of the word – ie, make a robot or computer do the work instead of a human – in 1948 by the vice president of the ford motor co. In some cases, automation can mean having a human do less work such as “automatic washing machines” where a human must still transfer the laundry and add the correct amount of detergent, bleach, and softener. In others, it can mean black boxing (hiding all the nitty gritty bits from the human user) an entire process, like digital alarm clocks, where you just tell the device what you want, and it performs the activity without much additional input in perpetuity.

Regardless, the first step to automating a task is the same – determine if the process is automatable. Stated a different way, the first step is to determine whether the process is repetitive and can be described precisely. Any process that is repetitive and precise is, to some extent automatable. The best way I have found to check this is to make a flow chart. Once the flow chart has been created, often you will see places in your process that can be improved and holes that are only obvious once you’ve gained a new perspective. During the automation process is a fantastic time to fix those issues. I will take you through the process of automating a simple report as an example.

To create a weekly KPI report, a developer runs several queries to determine successful product delivery rates, sales growth by region, the top five sales associates over all, and top five most common items sold, then inputs the results into an excel spreadsheet. She then takes some information from the companies google analytics account and places the SEO information into a line graph in the excel sheet. She also by hand performs a “sanity check” on individual associate sales, to determine if there are any obvious errors.

To automate the report, she creates the following flow chart to describe the current process:

The above chart is a good indicator that the report is automatable to some extent, but she realizes that “check data for reliability” was not precise enough. She realized that if she were to give this flow chart to a coworker, they likely would not be able to repeat that step the way she does, from her description. The next time she wrote the report manually, she paid particular attention to how she checked the data and wrote it down her steps. In doing so, she saw the process written down for the first time, and noticed ways to improve the process and wrote her new steps into SQL queries.

The developer then noticed that gathering data from an external source could be difficult. She researched APIs that would work with Google Analytics and a reporting software, and from that, to use PowerBI for her reporting system due to its compatibility with so many external data sources with a simple google analytics API. She also liked that the board members would have constant access to the report, and receive fewer emails, since they had previously mentioned the quantity of emails they receive as a pain point.

Finally, she decided that she wanted easier and automated access to historical data in the future, so she determined that part of the final product should be a series of regularly updating historical tables of KPI data.

Her new flow chart looked like this:

To summarize the process, the developer:

  1. Created a flow chart, and determined that the process could be effectively described using a flow chart, adding detail to the next iteration where necessary
  2. Reexamined the old process to see what improvements could be made
  3. Found tools such as SQL, the Google Analytics API and PowerBI to automate each step of the process
  4. Ensured the tools were compatible
  5. Created a project plan in the form of a final flow chart, which included each tool she planned to use for each step

In the end, she had an improved process that delivers the same result more quickly and reliably, and greater flexibility of the board chooses to add more metrics to the KPI report in the future. Each part of this report can now be done automatically, giving the developer back hours of her time each week, and ultimately saving the company money.