Turning Manual Ops Into a 10-Minute Task
The BEFORE Process
I worked for a marketing company who's job it was to update a major restaurant's nutrition information with ingredients, UOMs, and caloric content. We would get a new spreadsheet full of updates we needed to apply to the database to update the website's display for 8m customers.
This process typically took around 10 business days (2 weeks) to complete all the changes. We updated this I believe every 6 months or so. A lot of manual work, checking, re-checking, typing very carefully to maintain the dwindling data integrity without introducing new issues. Very picky old system that had to be handled a certain way so it would correctly feed the iOS app. Legacy code and DB setup. Very tedious and exhausting to complete.
My Approach To Improve
After completing this a handful of times I saw that there were common assumptions I could make that would shortcut the time we needed to complete this process, and automate many of the repetitive tasks. This would of course increase the validity of data (no human error) and allow easier checking of final results as well (verified against the original source of truth (the spreadsheet).
Tools I Used
The best tools I had at that time was PHP as the scripting language for specific tasks, and a temporary MYSQL DB to help check and manipulate data to speed things along.
The Solution
I wrote some logic in PHP to ingest the spreadsheet data, match all the fields against common enums per category, and applied transforms for specific labels and description content, and then piped that into the final database only after it was tested, reviewed on a dashboard for quality and ready for production.
Essentially the fix was to let the computer do as much processing as it could, have a human verify its work when done, and then automatically apply it to the target system, without the tedium of checking things one by one.
The Result
The process with my script and DB system would take only a 10 minute run to process the data and display the final values. We could check it all on a web page and make adjustments to anything that was off, and then it was around 30 seconds to push to the prod DB. This saved devs hassle, the company money and time, and the customer issues at around a 90%+ rate. Not a bad outcome in the end. It is one of the projects I am most proud of to date and it was my original thought to even work on it. This is the kind of thing I love to do.