Automation – the monster in my nightmares

When I embarked on my first data role over a decade ago, my organisation was supported by a team of 30 business analysts responsible for generating insight reports by extracting a month’s worth of transaction data from an Oracle database, conducting analysis, creating visualisations, and documenting observed trends. They used a custom web interface with different parameters to extract the data from the database. Unfortunately, this entire process, from start to finish, took a gruelling month for a single analyst to generate an insight report, leading to a monotonous cycle that repeated month after month.

Originally published at LinkedIn Pulse on 19 June 2023.

Photo by Antoni Shkraba from pexels.com

Fortunately, a glimmer of recognition regarding the potential for automation emerged, and an astute decision was made to second a database developer from the IT department to construct parameterised SQL queries. These queries prompted users to input specific parameters, such as date ranges, enabling analysts to extract data from the database using SQL Developer. Additionally, a proficient Excel user had devised a template file with embedded macros. Analysts would simply paste the extracted data into this file, allowing Excel to generate charts based on the data. The analysts would then document their observations on trends in a designated cell and print the worksheet as a PDF file, which was subsequently shared with senior stakeholders. This revamped process significantly reduced the time required to a single day, representing a remarkable improvement over the previous month-long timeframe. The performance had increased thirtyfold.

Initially, the organisation was constrained by its resources and could only generate reports for approximately 30 countries, rather than the intended 40. However, with the introduction of automation, they were able to assign two analysts to handle 20 countries each, while the remaining analysts concentrated on creating reports for different industry sectors and transaction types. By redistributing tasks, analysts were able to shift their focus towards providing need-based insights rather than being bogged down by repetitive, mundane duties. The demand for their work continued to grow, necessitating data that their standard user interface could no longer produce.

Around this time, I joined the organisation as a data mining analyst. Although my primary responsibility was extracting data from databases using SQL, since my role did not fall under the IT department's purview, the organisation decided to incorporate the term “data mining” into the analyst's standard job title. During my initial probationary month, I was handed one of the automated reports and tasked with writing SQL queries to generate the required information, primarily to familiarise myself with the organisation’s database schema. I had at my disposal SQL Developer and RStudio, with R serving as the backend.

Extracting the necessary data from the Oracle database proved time-consuming due to its sluggish performance. To enhance query efficiency, I utilise ‘hints’ and other optimisation strategies. Subsequently, I recreated the visualisation using R. Through discussions with the analysts, I noticed a pattern— their double-axis bar and line charts were similar across different reports. Leveraging this insight, I encapsulated the steps required to generate such charts into a single function that incorporates the necessary parameters.

Given the autonomy I enjoyed from my manager, I used Sweave to create a LaTeX file that mirrored the report's structure. Using conditional statements, I extracted trends directly from the charts and seamlessly incorporated them into the report. By the end of my second probationary month, I had developed an R workflow capable of reading SQL statements from a .sql file, substituting parameters with values from a configuration file, and generating the insight report with a single Rscript command. While the automation ran, I could actively engage with my colleagues. The time required to generate a single report had dramatically decreased to approximately 30 minutes. Subsequently, I further enhanced the configuration file to facilitate the generation of all 40 reports with a single command. Although the process exceeded my 8-hour workday, I would leave the computer running when I departed the office, only to return the following day to find all reports neatly organised in the designated folder.

When I presented my achievements to my manager, he emphasised the need for an easy-to-use user interface for the analysts, even though it only required a single command to execute. Unfortunately, obtaining new tools within the organisation was challenging due to bureaucratic hurdles and limited options. However, I stumbled upon a glimmer of hope when I discovered that the JDK was installed on my computer, though it lacked an IDE. Leveraging Java Swing, I developed a simple user interface within Wordpad. This user interface allowed analysts to read and write configuration files and execute the R script. During script execution, an indeterminate progress bar would display, followed by a dialogue box upon completion.

Impressed by these developments, my manager arranged a showcase for the organisation’s senior executives. During the presentation, I demonstrated my automation engine’s capabilities and even generated a report in real time. The executives were astounded not only by the speed and ease with which the reports were generated without human intervention, but also by the potential to automate all the different report types that previously took analysts days or even months to produce.

Consequently, I was tasked with automating reports across various industry sectors, which I successfully completed in under two months. The outcome was met with great satisfaction. Despite my relatively short tenure of fewer than six months, my manager successfully advocated for my promotion, resulting in a two-level pay increase. Unfortunately, this automation rendered the 30 analysts redundant. Furthermore, my manager and the manager of those analysts received an email notification indicating that their positions were under review due to the reduced workforce requirements. My manager, having accrued substantial entitlements over his extended tenure, decided to accept a lump sum payment and depart from the organisation.

Over the next few years, I had the opportunity to automate various analytics use cases for the organisation:

• customer churn analysis,

• customer behaviour and segmentation,

• latent demand,

• sentiment analysis from tweets,

• risk assessment and management,

• … and many more!

My automation engine seamlessly accommodated different configurations, enabling it to leverage the appropriate SQL scripts to extract data from the terabytes of records available. It efficiently generated visual analytics and provided concise commentary with pre-worded content. Meanwhile, other analysts could still contribute their valuable human-level commentary during their presentations.

But I didn’t stop there—I even developed an automated user interface specifically designed to generate SQL scripts for data extraction from the database. This innovative solution not only streamlined the entire process but also made it remarkably accessible, opening up a realm of possibilities for further enhancing the automation engine. Whether it was tackling new use cases or empowering analysts, this advanced interface became an invaluable tool, enabling them to work with unparalleled efficiency and productivity.

While I had focused on delivering value to executives, I had neglected to fully assess the broader consequences of my work. Although I wasn’t in a management or leadership role, it was indeed my first leadership failure.

In my relentless pursuit of advancing analytics, I dedicated my time not only to developing novel tools but also to fostering innovation within the field. Some of these techniques include:

• heat-series, a fusion of time-series analysis and heat mapping, which possesses the extraordinary ability to display hundreds of line charts at once, enabling a comprehensive visualisation of data,

• clustered heat-series, an extension of heat-series that allows for the clustering of lines within the graph based on their shared characteristics. By identifying similar patterns and trends, this technique provides deeper insights into the data and enhances its interpretability.

• transaction network analysis, a social network analysis inspired graph analytics using transaction data instead of social data that enables us to uncover hidden relationships and gain a comprehensive understanding of transactional dynamics,

• displacement analysis, a geospatial analysis of people’s movement through the location of their transactions, which facilitates a deeper understanding of customer behaviour and preferences,

• fuzzy entity resolution, a fuzzy logic-based entity matching capability to match entity data collected from disparate sources that account for varying degrees of similarity.

These transformative analysis techniques consistently contributed to improving automated reports. However, despite my pleas for faster big data tools and modern dashboard capabilities, my organisation remained resistant to change. The preference for printed reports over dynamic dashboards persisted, inhibiting progress.

Driven by my insatiable hunger for learning and growth, I eventually made the difficult decision to leave the organisation. During a subsequent job interview, I was asked about the impact of the changes I had brought to the work of the 30 analysts in my previous role. This inquiry served as a profound realisation of the impact of full-scale automation on these individuals. While I had focused on delivering value to executives, I had neglected to fully assess the broader consequences of my work. Although I wasn’t in a management or leadership role, it was indeed my first leadership failure.

My learnings

Job displacement and workforce impact. My automation efforts resulted in the redundancy of 30 business analysts. While automation increased efficiency and productivity, it also had consequences for the individuals whose roles were replaced by automation. This highlights the ethical dilemma of balancing the benefits of automation with the potential negative effects on employees, such as job loss or significant changes to their roles.

Managerial implications. My manager also faced consequences as his position was reviewed due to reduced workforce requirements. This raises questions about management's responsibility for navigating the impact of automation on their teams and addressing potential job displacement.

Ethical responsibility. Automation leaders should fully assess the broader consequences of their work. This raises ethical questions about the responsibility of individuals involved in automation projects to consider the potential impacts on others and ensure that advancements are rooted in the well-being and professional fulfilment of employees.

Transparency and communication. It is important to reassure and maintain transparency with the individuals affected by automation projects. This underscores the need for clear, open communication to address concerns, manage expectations, and foster a culture of acceptance and enthusiasm for automation.

Ethical considerations in automation design. My experience forces me to prioritise automation projects that enhance the work experience rather than render roles obsolete, highlighting the importance of designing automation systems with a human-centred approach. Considering the well-being and professional fulfilment of employees should be a crucial ethical consideration in the design of automation.

Resistance to change. Some individuals may resist automation due to uncertainty about the changes it brings. This resistance to change can have ethical implications if automation is not introduced in a way that reassures employees and maintains their well-being and professional fulfilment.

Balancing short-term gains and sustainable growth. Automation projects should prioritise the well-being of operational personnel rather than solely focusing on short-term revenue gains. This highlights the importance of considering the long-term effects of automation on individuals and the organisation.

Transparency and reassurance. Organisations need to be transparent and provide reassurance to employees when implementing automation. This approach fosters a culture of acceptance and enthusiasm towards automation, mitigating potential ethical concerns related to fear, uncertainty, and resistance.

At the outset of each new automation endeavour, I prioritise reassuring the ground crew that the changes are intended to enhance their work experience, rather than render their roles obsolete. By fostering this sense of security, individuals consistently embrace automation and welcome the positive transformations it brings.

In conclusion

As I delve into the ethical implications of automation, I become increasingly aware of its potential effects on job displacement, employee well-being, and transparency. This realisation emphasises my organisation’s responsibility to navigate the transition with care and to offer support to those affected by automation. I understand that automation should be implemented to enhance employees’ work experience rather than replace them entirely. By prioritising their well-being and ensuring transparency throughout the process, we can ethically navigate the challenges of automation.

Since then, I have engaged in numerous automation projects, always ensuring that automation simplifies the lives of operational personnel rather than replaces them. At the outset of each new automation endeavour, I prioritise reassuring the ground crew that the changes are intended to enhance their work experience, rather than render their roles obsolete. By fostering this sense of security, individuals consistently embrace automation and welcome the positive transformations it brings.

Reflecting upon these experiences, my two key takeaways are:

1. Automation is not universally embraced, and some may exploit it in ways that prioritise short-term revenue gains over sustainable growth.

2. People’s resistance to change stems not from an inherent aversion to new ideas but rather from the uncertainty that accompanies such changes. By providing reassurance and maintaining transparency, organisations can foster a culture of acceptance and enthusiasm towards automation.

These lessons have profoundly shaped my approach to automation projects, where I strive to ensure that advancements are rooted in the well-being and professional fulfilment of the individuals involved.