Case Study
Streamlining Order Processing for a Leading Publishing Company
Client Overview
The client is a major player in the publishing and media industry, specializing in direct-to-consumer sales through multiple popular book clubs. With a large network of readers and subscribers throughout the U.S., they distribute books, magazines, and other media. At the time of this project, their business model relied on efficient order processing and seamless integration of data from multiple sales channels into legacy systems for order fulfillment.
The Challenge
The client wanted to accept subscription orders from several different websites. However, the processing and fulfillment of those orders depended on legacy mainframe systems, and the company hadn’t fully adapted to web-based technology. They didn’t have in-house resources dedicated to integrating these new web orders into their existing systems, and the programmers we worked with were primarily experienced in mainframe environments.
Without an efficient solution, the client risked delays in order fulfillment and missed opportunities for revenue growth through web-based sales channels.
They needed a way to gather order data from multiple sources, run error checks to flag any orders that couldn’t be processed, and translate the orders into a consistent file format for delivery to their mainframe system via SFTP. This was new business for them, and it required a solution capable of reliably handling around 1,000 orders per night.
Solution
The solution consisted of several components:
-
Administrative interface — We onboarded new suppliers as needed and managed existing ones through an administrative interface.
-
Order intake process — We imported orders into a relational database.
-
Error checking and error reporting — During the import process and once orders were in the database, we ran a series of rule checks on the orders and flagged any errors for later reporting.
-
Exporting of orders — From the database, we exported orders into a file format that their mainframe system could process.
-
Delivery of orders and reports — Output data was delivered via SFTP to their system.
Technology Stack
The backend processing was built using Perl and MySQL, with either Perl or PHP used for the internal web-based administrative interface.
Perl:
-
Text Processing Power: Perl is known for its exceptional capabilities in handling and processing text, making it ideal for processing large volumes of order data and error-checking multiple file formats. If we approached this project today, we would likely use Python due to its more active community and broader support.
-
Automation and Batch Processing: Perl’s scripting nature made it well-suited for automating batch jobs, such as nightly order processing, ensuring a smooth, hands-off workflow.
-
Stability: Perl is a stable and mature language with a long history in system administration and web development, ensuring reliability for a project that needed to run consistently over several years.
MySQL:
-
Relational Database Management: MySQL provides a robust, relational database management system that’s efficient for handling structured data like customer orders.
-
Scalability: While the order volume was around 1,000 orders per night, MySQL is scalable enough to handle increasing amounts of data should the business grow.
-
Integration with Perl: MySQL integrates seamlessly with Perl, allowing for efficient querying, data manipulation, and storage.
-
Reliability and Performance: MySQL was (and still is) known for its speed and reliability, making it an excellent choice for managing order data in real-time and ensuring fast processing times.
Overall, the combination of Perl and MySQL allowed for a flexible, reliable, and scalable solution that fit the project’s needs at the time, particularly for processing, error checking, and integrating with legacy systems.
Implementation
This project was completed before cloud computing became mainstream, so we used a Linux-based server hosted at a co-location facility, with redundant power and networking to ensure reliable service. System monitoring software was employed to alert us of any infrastructure issues, and email reports provided daily updates on the success or failure of order processing and delivery.
The batch system was scheduled to run nightly, processing around 1,000 orders each night. Project milestones were set to track progress, and we coordinated closely with the client’s team, particularly their mainframe programmers, to ensure seamless integration.
Results
The result was a reliable order processing system that allowed the client to take orders from previously unavailable sources. We provided ongoing support, ensuring the system remained dependable over the years, not just a one-time solution. The client was pleased with the results and the efficiency the system brought to their operations.
This solution enabled the client to process new orders seamlessly, significantly reducing errors and increasing operational efficiency. Our continued support ensured the system remained a critical asset to their business, adapting to their needs as they evolved.
Additionally, this solution positioned the client for future growth by streamlining order intake and giving them the flexibility to scale their web-based sales channels with ease.
Conclusions and Lessons Learned
-
Modular design is crucial, as it allows for easier integration of new suppliers or data sources.
-
Regression testing is essential to ensure that any changes to the system don’t inadvertently introduce new issues.
-
Logging and error reporting are critical components of any system. They help diagnose and correct issues quickly, whether they arise from data transmission, format changes, or other unexpected problems.
-
Sometimes legacy systems can be inflexible when it comes to data formats. Having a more flexible system preprocess the data and then normalize it into their format can be a significant advantage.