Independent pharmacies typically generate the majority of the business’ revenue from prescriptions filled by the pharmacy. As the owners/operators are busy filling prescriptions, this can result in the “front-end” of the Pharmacy where over-the-counter medicines, health and beauty products, and snacks and drinks are sold to receive less attention. As small businesses, the pharmacy may experience limitations around staffing, retailing and marketing knowledge, or management expertise that preclude them from effectively running the front-end and maximizing their revenue potential.
In addition, independent pharmacies are often competing with mass merchants (like Target or Walmart), grocery stores, and chain drugstores (such as CVS or Rite Aid) who can offer a broader selection of over-the-counter (OTC) products. These may include milk, bread, toys, candy etc. where the independent pharmacies have a harder time accessing these products and having space for them in the pharmacy.
Your challenge is to recommend actions that the Independent Pharmacy owner/operator can take that will allow them to increase their revenue from the front-end of the store, therefore improving the contribution of this part of the store to profit.
Your analysis should address one or more of the following questions:
- Are there specific products that a pharmacy should always have in stock? How do we identify those products?
- Are there certain products or product categories that are not generating sufficient revenue and therefore should not be carried?
- Are there product categories not being carried that should be? How do geographic or economic variables factor into the products a pharmacy should carry?
Extracted, anonymized data from the AmerisourceBergen Point-of-Sale Data Warehouse.
The basic data set is ABDataSet(WithDataSetMaster).xlsx. This is an Excel file that includes these tabs:
- Data Set Master: Data Dictionary of field names & definitions for the other tabs
- PHRMCY MASTER: Pharmacy Master with set of Pharmacy IDs (surrogate keys), de-identified Pharmacy names, State Cd & Zip 3 Cd
- PROD MASTER: Product Master
- MAJOR PROD CAT: Major Category Codes
- PROD CAT: Product Category Codes
- PROD SUB CAT: Product Sub-Category Codes
- PROD SEG: Product Segment Codes
- POS TRANS: Point-of-Sales transactions with Sales Dates of for six months, from 2016-01-01 through 2016-06-30 (915,744 records)
The POS TRANS tab should be your starting point, using the unique identifiers to look up values in (or join with) the other tables.
You can do the entire analysis with this data set.
If you want an even larger data set to work with, you can download ABPOSTrans(Big).zip. This is a comma-delimited text file that includes a full year of POS transactions, from 2015-07-01 thru 2016-06-30 (1,801,645 million records). This effectively replaces the POS TRANS tab of the above Excel file; use the rest of the tabs in the Excel file for the rest of the data set.
Note: This file is too big for Excel, but you can use other software (R, SPSS, SAS) to do your analysis.
The Temple University Library has created a guide for this challenge to help you find even more data.