Develop an independent Sales Information System updated daily with information from over 30 Pharma Wholesalers

Business Imperative:
In 2008, a large multinational pharmaceutical company using a third party logistics service provider distributor started experiencing the granularity of its sales data becoming increasingly coarse. This was coupled with a lengthening lag period of sales information via the syndicated audit reports. Furthermore sales into informal buying groups were skewing area sales data (IMS Brick©) resulting in certain representative s not receiving their fair share of the sales incentives payouts.


Complication:
A new type of dispensing: mail order dispensing was also skewing sales data into the IMS brick of the few large mail order dispensing sites. As the volumes of sales increased via these mail order sites, the percentage of useable sales information decreased. For the oncology products one mail order dispensary was responsible for such a significant proportion of the sales that it became embarrassing for sales management to incentivise sales team fairly.

The Project:
Build a data warehouse containing detailed sales information by product pack by final seller or dispensed site, and include the site of the prescription originator/ prescribing physician, where this was different to the IMS brick of the dispenser.
Utilising only in-house technical staff, a project team was established.
The user / sales department specification was vague; however ease of use and speed to access the sales information was critical.
The Final Seller Data (FSD) sales information had to be made available to all aspects of the company. Each sales area had differing sales areas complicating the aggregation algorithms. The data formats from the various sources had to be standardised. The data analyst built a technical data format specification per type of final seller: i.e. the mail-order dispensary standard was
different to that of a wholesaler.
Data contracts with all the wholesalers had been in place for years, and not used. After about six months of negotiating and assisting wholesaler to provide data in the format specified, the data downloads were lifted of a secure and encrypted ftp site.

Solution:
The data warehouse was built utilising SQL 2005 on a Ralph Kimball methodology of data warehouse architecture.
Once all the data history had been scrubbed, it was loaded into the data warehouse for testing.
ETL processes were built, SOPs documented, finalised and signed off, and stored for future reference. Lagging by about 2 months was the development of the ”front end”. Various options were explored and test front ends built for review. There are many Business Intelligence software applications available, at varying costs.  Eventually, we chose to use Microsoft SharePoint to which Microsoft had developed a seamless interface to SQL 2005.
We developed a customised SharePoint view of the sales data per representative, regional sales manager, Sales directors, and Marketing area.

Impact/Outcome:
Business Intelligence information was delivered to the desktops of all decision makers in the company.
After 1 year of development, the IT department’s business analysts , without the use of costly external consultants delivered a Business Intelligence sales information system that tracked the sales of each product and aggregated by category, sales team and regional area into totals using SharePoint as a front end. All performances were RAG-indexed and compared to budget, and
previous year’s performance. Most importantly, Sales information by final seller / prescriber was automatically updated on a daily basis, rather than a 4-6 week wait for syndicated data, allowing the company to react faster to sales changes.
The FSD was an extremely good and inexpensive future barometer of sales trends by utilising prescriber data from the mail-order pharmacies; we could see trends developing before the sellers of prescriber data could deliver the data to the company.