Case Study
Enabling Seamless 360-degree view and Sales Planning for a Global Pharmaceutical Company
Overview

The client is a leading biopharmaceutical company, with presence in over 100+ global markets. Currently handling over 50 MN customer data records at a global coverage of over 4 markets (EU, JAPAC, US and ICON-Intercontinental region), it wanted to maximize the impact of its sales and marketing efforts and create interactive dashboards for enhanced business insights.

The Challenge

While trying to meet its crucial needs, the client faced the following challenges:

  • The client’s system used Oracle database, which couldn’t handle the massive incoming data volume. Handling business data from multiple sources was difficult due to the lack of a single source of truth.
  • The client’s existing ad hoc manual reporting processes had to be replaced, as they hampered top management’s visibility into sales insights, rep interactions, marketing reach, brand performance and customer territory management.

Due to the above reasons, the client wanted to break away from its legacy architecture, which used rigid and siloed data sources (CRM, Database and flat files) to gather business data. In addition, this system was also not quite conducive for near real-time advanced analytics.

Our Solution

To address the above challenges, Agilisium designed an integrated 360-degree view of product movement, customer behaviour, geography and sales progression, to optimize its sales cycle and planning process. A stringent re-platform strategy was implemented, as follows:

1) The entire customer data was shifted from Oracle to Redshift Analytics Data Warehouse. Our in-house experts architected, designed and delivered an elastically scalable-Cloud-based Big Data solution, with AWS S3 Data Lake as a single source of truth. This solution had the following features:

  • Followed a Cloud One Strategy to help the client in progressively transitioning to an Analytics Ready Big Data platform, with a scalable Reporting environment.
  • Used a Massively Parallel Processing (MPP) Analytics Data Warehouse in Redshift to support downstream BI & Advanced Analytics and enable fast execution of complex analytical processes.
  • Established a Metadata-driven generic framework using Snap logic Integrator at the client side, to automate its key processes: business data validation, cleansing and further ingestion into Amazon S3 and Redshift.
  • Automate manual data validation / correction, and business rules application.

2) Tableau was implemented as an on-demand reporting tool for graphical reports and visualization, to provide better BI experiences and visibility into customer insights and analyses. As an additional feature, a user and country-based security system was established using AWS IAM (Identity and Access Management) Services and Policies in both S3 Data Lake and Redshift. This was able to provide seamless BI Reporting Services.

Results and Benefits
  • Provision for a Holistic 360-Degree view on product movement and sales progression, with reduced dependency on manual ah-hoc operations.
  • Quicker Insights: Business leaders can now gain enhanced customer insights through self-service BI, with access to data at the lowest granular levels.
  • 2 times faster territory transition: The proposed Metadata driven generic framework helped in the transition of customer data into new territories or geographic locations in half the time than planned, for seamless onboards of the territory-wise data into the newer platforms.
  • The proposed architecture could also support a scalable Advanced Analytics ready Big Data Platform, to analyse the behaviour and usage patterns of the incoming business data and adopt suitable sales strategies.