Case Study – Timely Weekly Sales Reports
Challenge: Deliver weekly sales reports to sales executives in a timely manner.
Background
This one is going to date me, but even all these years later, I’m proud of the solution. Back in the day when reports were physically printed and delivered to executives, I was working as a consultant for one of the major record label’s distribution organization.
Problem
The executives were in New York, but the data center was in Los Angeles. On Mondays, the reports were physically printed and sent by FedEx to New York. If they missed the morning deadline, the reports wouldn’t arrive until Tuesday afternoon or even Wednesday morning. The execs hated this and asked for a way to get the reports on Monday.
Solution
The solution came in three parts:
- After doing some research, I discovered that the reports could be printed to a file using a virtual printer. It was a formatted text file, which looked like the report.
- I found a way to automatically create an email and insert it into the corporate email system with the CSV file attached and instructions on how to open the file.
- I wrote code which parsed the file, looking for keywords and numbers and extracted all the relevant information into a CSV file (comma separated value), which could be opened by any spreadsheet program with perfect columns of numbers and a lot of the extraneous text removed.
For…reasons…I had to set up a desktop computer to physically turn on every Monday morning at 3am, which auto-executed the print-to-file, the parsing program, and the email insertion.
I also wrote admin software so that if the report changed or the email addresses of the execs changed, it could be managed without digging into the file parsing code.
Worked like a charm. The execs loved it.
Postscript
As the data center was in the process of upgrading to more modern hardware and software, I assumed that my little solution would only have a life of 2-3 years. Five (5) years later, they tracked me down because it had stopped working, the execs weren’t getting their reports, and they were furious. Fortunately, after they protested for a while that they hadn’t changed anything, I helped them find the culprit. They had changed the hardware and software for the printers, so the virtual printer wasn’t configured to print-to-file. Once we got that fixed, it immediately started working again.