Solution for data analytics in pharmaceutical industry: 30% reduction in data processing time

Our team effectively restructured and optimized the back end and front end of the pharma network system, enabling better integration and analytical processes for data analytics in the pharmaceutical industry.

Customer

Industry

Healthcare

Region

Client since

2023

Our client is a leading pharmaceutical conglomerate, operating one of the largest pharmacy chains globally, with over 8,000 establishments. Their network extends across various countries, mainly focusing on the European region. Detailed information about the client cannot be disclosed under the provisions of the NDA.

Challenge

Enhancing solution for data analytics in pharmaceutical industry

Our client offers a broad range of medicinal products at competitive prices through both physical outlets and an online platform. They faced a multifaceted challenge centered around data management and system functionality within their pharmaceutical network web platform.

Primarily, their existing infrastructure, involving a combination of a 1C system and a large database, was inadequate for their evolving analytical and operational needs. Analysts struggled with inefficient processes, relying on manual Excel-based calculations for sales planning and distribution indexing. This cumbersome approach led to errors, time delays, and limited data access data for non-analytical staff. Additionally, the client’s API for specific calculations was lost, necessitating the recreation of tables and procedures.

The client expected a solution that would streamline their data processing, enhance accessibility for all staff, and improve overall efficiency in managing their extensive pharmacy network.

Solution

Improved data management and analytical capabilities of the pharmaceutical network system

In a major upgrade to the pharmaceutical network’s data management and analytical capabilities, Innowise transitioned their processes from traditional, Excel-based systems to a database-driven reporting environment. This entailed a redevelopment of both the back-end and front-end systems, focusing on data migration, process optimization, and the development of custom modules for data analytics in the pharmaceutical industry.

Data migration and process optimization

To improve the efficiency of the client’s data management, we embarked on migrating their analytical processes from Excel-based spreadsheets to dedicated database frameworks for analytics. This migration focused on creating scalable solutions that weren’t confined to specific products or pharmacy networks. Here’s how we achieved this:

Data mapping and cleansing: We mapped data from the existing Excel sheets to the new database structure, ensuring that every piece of information was accurately transferred and cleansed for consistency.
Procedure development: Utilizing SQL, we developed a series of stored procedures and functions in the database. These were designed to automate the analytical calculations previously done manually in Excel.
Optimization techniques: To handle the vast data volumes, we applied SQL optimization techniques. This included indexing, query tuning, and batch processing, guaranteeing high performance and timely data retrieval.

Sales plan and distribution index development

The development of the Sales Plan and Distribution Index modules was a critical part of our solution, aimed at providing the client with real-time insights:

Sales plan module: This module was designed to analyze historical sales data over a three-month period and use this to forecast future sales targets. We implemented complex algorithms that could adapt to various market conditions and product demand patterns.
Distribution Index module: To understand product popularity and sales trends across pharmacies, we created a Distribution Index. This involved analyzing sales, purchasing, and inventory data to identify which products were performing well in which locations, allowing for more strategic inventory management.

API and database development

We developed a new API and database architecture to ensure seamless data flow from the old system to the client’s portal:

Database design: The new database was structured to support large-scale data while maintaining high performance. It included tables for sales, inventory, and user data, with relationships optimized for easy querying.
API development: The API, built using Python and Flask, interfaced between the database and the client’s portal. It was designed to handle requests for data retrieval and processing, sending back pre-calculated and formatted data for the managers’ use.

Custom solutions: API recreation and integration

The recreation of the lost API posed a challenge, requiring a deep understanding of the client’s previous system and the intended calculations:

API reconstruction: We started by understanding the intended functionality of the lost API. Through discussions with the client and analysis of available documentation, we reverse-engineered the required features.
Development and testing: Using Python, we developed a new set of APIs replicating the lost functionalities. We rigorously tested APIs to ensure accuracy and reliability.
Integration with Existing Systems: The newly developed APIs were then seamlessly integrated into the client’s existing system to work in tandem with the overall architecture.

Technologies

Back end

Python, Flask

Front end

JavaScript, React.js

Databases

Microsoft SQL Server

API Development

Python, Flask

Optimization techniques

SQL Query Optimization, Indexing

Python Unit Testing Frameworks

Project management

Jira

VCS

Git

Continuous Integration/Deployment

Jenkins

Data analysis and processing

SQL, Python Pandas

Process

Our commitment to the Agile methodology enabled a transparent, iterative, and collaborative process. We kicked off the project by crafting a detailed Vision and Scope document, which served as our roadmap. During the development process, we divided our work into the following stages:

Discovery and planning: During the initial stage, our objective was to gain a deep understanding of the client’s specific requirements. We developed a plan, outlining the solution’s blueprint and setting a foundation for the subsequent stages.
Data analysis and back-end development: Here, our focus shifted to the technical foundation of the project. The team examined the client’s data landscape, analyzing the existing structures and recommending improvements. Parallelly, we started back-end development, employing Python and SQL. This stage was crucial in laying the digital infrastructure for smooth data management and processing.
Testing: Post back-end development, we initiated a testing phase, focusing on back-end and front-end components. This stage included unit, integration, and user acceptance tests to ensure system stability. Further, testing was conducted regularly throughout the subsequent stages.
Front-end development and integration: In this final stage, the spotlight was on the user experience. Our front-end developers crafted an intuitive and responsive user interface. The culmination of this stage was the integration of the front end with the back end.

Team

Project Manager

Back-end Developers

Front-end Developer

QA Engineer

Database Administrator

Data Analyst

Results

Redesigned system of data analytics in the pharmaceutical industry and a 50% increase in sales reporting efficiency

The implementation of the redesigned system of data analytics in the pharmaceutical industry made a significant impact on the client’s network operations. By transitioning from manual, Excel-based processes to a database-driven system, we achieved significant improvements in efficiency and tangible benefits for pharmacy staff:

Enhanced inventory management for pharmacies: The Sales Plan and Distribution Index modules provide real-time data on pharmaceutical sales trends and inventory needs to optimize their stock levels and ensure that high-demand medications are always available.
Streamlined reporting for pharmacy staff: The automated data processing workflows ease the reporting burden on pharmacy staff – they can now generate sales and inventory reports twice as fast.
Improved sales strategies: With insights from the new modules with data analytics in the pharmaceutical industry, the client can make more informed decisions regarding which medications to promote or order more of.
Easier information access for managers: The integration of the new API and database system streamlined information flow. Pharmacy managers and staff can easily access data from sales figures to stock levels.
Faster data integration: Data integration tools allow for the rapid consolidation of information from various sources. This reduced the time and effort needed for data compilation and ensured that the employees had the latest information at their fingertips.

Currently, our team remains engaged in the ongoing project development. We are currently focused on creating specialized modules like the Inventory Management Module with predictive analytics for reduced instances of overstocking or stockouts and the Pharmacy Performance Analytics Module to analyze the performance of individual pharmacies within the network.

Project duration

January 2023 - Ongoing

50%

increase in sales reporting efficiency

30%

reduction in data processing time

Related cases

Contact us!

Book a call or fill out the form below and we’ll get back to you once we’ve processed your request.

What happens next?

Having received and processed your request, we will get back to you shortly to detail your project needs and sign an NDA to ensure the confidentiality of information.

After examining requirements, our analysts and developers devise a project proposal with the scope of works, team size, time, and cost estimates.

We arrange a meeting with you to discuss the offer and come to an agreement.

We sign a contract and start working on your project as quickly as possible.