Standardizing Data with 3rd Party Sources
The Pharma Company collects data from a 3rd party Market Research and Analysis company. The 3rd party company gathers prescription information from veterinary clinics across Canada. They then compile this data and present it back to companies using an on-line reporting tool. This reporting tool provides sales, unit and dosage information by Classification, (A group of products in the animal health industry a product may belong to one and only one classification), Products, Company and Geography (National, Provincial, Territory, and Postal Code [first two characters]).
The Vetrinary division in the Pharma company subscribes to this service from the 3rd part Marketer. However, for reporting and analysis of MarketShare data, this on-line tool has been found to be very cumbersome. Therefore the Vetrinary division of the Pharma company would like to bring the data in house and use this to perform any Market Share Analysis. This would allow for more timely and flexible reporting regarding Vetrinary Market Share.
Challenges were encountered working with the 3rd Party Market research company. During the daily manual download of data, the Pharma Company found that the data being collected often contained errors. These error included data gaps, improper table formats and errors with column formats. When errors were found, the whole data download process was repeated often with similar or new errors.
The data collected was sourced from the 3rd party data as well as local source systems. (ERP, spreadsheets and other list data) Having the data in disparate source systems slowed the delivery of the information to the end users.
The solution included:
- Create a Veterinary Market-Share Analysis Data Mart
- Work with the 3rd Party Marketing company and The Pharma Company to develop a simple, robust ETL process to load the monthly data into the data mart
- Ensure process accounts for territory definition changes. VM Territories defined by FSA, 3rd party currently only provides data down to first two characters of postal code
- Create a cube for analysis and simple report generation
- Documentation and Training: System operation documentation (includes procedures, recommended run schedules, flowcharts) as it relates to the Data extracts and transformation processes; Required functional and business documentation.
- Include auditing processes to validate data sourced from the 3rd party Marketer.
Results and Benefits
The Veterinary Division estimates that the sales analyst and the sales managers spend 1 – 2 days downloading and manipulating data from the 3rd Party Marketer online reporting tool each month. Therefore, automating the download and creating some standard reports saved between 4 – 8 person days of cost for The Veterinary Division each month.
Data delivery was more timely and accurate. Audit reports and queries validated the ETL at import and any errors were promptly identified and sent back to the 3rd Party Marketer for the quick fix.