Data Overload: A Tale of Transformation the Pipeline of big Delivery Company

How did we solve the client's data overload and streamline data management?

Summary:

Client

NDA

Country

NDA
Project scope and technology

resource-draining data entry, data warehouse,
streamlining data management, report generation

Industry

Logistics and International Shipping
Tech Stack

- MS PowerShell versions 5 and 7 for scripting automation
- MS SQL Server for database management
- Master data and flat files stored in Comet, the sales system
- IBM Workspace technology
- IBM WK interface.

Work duration

8 months

Ideamotive developed a sales data management system for a Logistics Corporation that included data ingestion from multiple sources (ELT process) with a modern data stack architecture. It reduced manual and semi-automated data entry by up to 99% and cut the time to insight for the management team from days to seconds.

Setting up a data warehouse and structuring database management allowed each employee to quickly understand the process, decrease the number of errors and speed up revenue analysis.

All that has its effect in numbers. The reports are now generated 40-50% faster on average, while some of them became 100% automatic. Considering the magnitude of data transformed by our Client each month, that saves hundreds of man-hours for their employees.

Challenge – Data Extraction Time, Automation, Various Sources:

Client's sales and pricing department processes financial and revenue reports, customer acquisition data, and internal sales reports. However, manual data entry from various sources resulted in wasted time and slower workflow. Data arrived in various formats each day such as emails, spreadsheets, or database files. At the beginning of the month, a designated person always had to be on site to retrieve the Excel files using selfmade scripts and then run the macros, which used to take 2 days. Some reports had to be generated daily. It took one person 1 hour to 1.5 hours to download and load the data.

Previous attempts to automate the data with custom Python scripts were only a short-term solution and failed for large data sets, leading to data management issues. This resulted in poor data quality, occasional errors and less time to spend on more important tasks.

Client sought to reduce data extraction time from days to minutes. They required a unified data management system and an experienced team to establish efficient procedures and user-friendly data warehouses.

Ideamotive seamlessly combined their technical expertise with a strong understanding of our business needs. Their data manager provided clear documentation and easily communicated insights to me and our leadership, keeping everyone on the same page.

Pricing & Sales Analysis Manager

at Client Company

Our role:

Our client was struggling with limitations in its analytical resources, primarily because of unstructured data processes, lack of documentation, and lengthy Python scripts that risked data loss. The need to maintain seamless sales processes with their implications for payments, invoicing, and protection of sensitive data was readily apparent.

The client sought the expertise of an external specialist, not just for learning purposes, but to provide accurate feedback, spearhead automation initiatives, and strengthen processes with robust data protection measures.

Ideamotive responded to that need and provided data engineering experts who audited data management procedures for Client’s sales and pricing department and then introduced a data engineering pipeline.

The team needed an experienced leader who, according to the client's requirements, had enterprise experience in developing a modern data stack. We found the right candidate from our specialist database within a day. After only one interview with Client, the specialist was accepted because he met all the requirements.

When the data engineers joined the customer’s team, project leader divided the work into 3 phases:

Phase 1 - Audit

For six weeks, the data engineers assessed the state of the client's data models and processes, examined knowledge distribution and data processing.

Stage 2 - Collaboration

After the audit, it was clear what areas had to be enhanced - automating data entry, creating fixed workflows, minimizing data inconsistencies, and improving the production of financial reports. The team was very helpful in explaining the current processes and the administration.

Stage 3 - Solution

Ideamotive implemented changes and processes for data management. A common format for source data was established and a data warehouse to store data in one location. The team adopted an ETL process that helped to migrate data from external and internal systems, and now serves as a hub of real-time information about the flow of data in the organization.

It was critical to understand the scope of the problem - not a single line of code was written during the audit for the first 6 weeks. We simply analyzed the state of knowledge distribution and how it could be brought together in one place.

Database Programmer and Pricing, Controlling and Sales Data Manager

Ideamotive

Our impact:

Due to stringent security measures, the customer had a defined list of approved frameworks and tools. Ideamotive adhered to this list, focusing on native tools and automating essential processes within the designated tech-stack to align with the project's scope.

Critical issues in the customer's operations were addressed by Ideamotive. Manual transcription problems and data inconsistencies were eliminated, guaranteeing accurate and reliable information. Streamlined processes dramatically reduced data extraction time from days to seconds.

What's more, we have improved access to databases stored in different locations, making it intuitive and fast to find the data the sales department works with every day. The new database is now the single source of truth for the sales department.

Thanks to the creation of a data model, Client now uses a modern data stack with a set of data practices and an API for the analytics team. Most importantly, automation has made it easier to create reports, further driving Client’s operational efficiency when it comes to analyzing billing data, transportation data, and financial results.

Working with Ideamotive has gone very smoothly, with no unpleasant situations - we've dealt with everything even remotely problematic as it happened.

Team Leader Data Analytics

at Client Company