Can small independent pharmacies compete with the big chains?
Background
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.
The AmerisourceBergen Challenge
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?
Data
Extracted, anonymized data from the AmerisourceBergen Point-of-Sale Data Warehouse.
The basic data set is AlexionDataSet(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 AlexionPOSTrans(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.
Other Rules
- The project submissions must entirely be the work of the project team. While faculty and other individuals can help review the submission, they should not contribute to the content of the report or the solution.
- Incomplete submissions will not be considered, so make sure you have all of your submission deliverables are in the submission package.
- The contest materials must be submitted by the due dates. Late submissions will not be accepted and no extensions will be given.
- Teams must be members of an AIS Student Chapter.
General Information
Resources
Get more data using the Temple Univerity Paley Library Guides
Paley Library has created an excellent set of guides that highlight where to find even more data for each challenge.The guide also will point you to additional resources about creating effective graphics.
Create graphics with Tableau and PowerBI
All Temple University students can get a free one-year license for Tableau! Tableau is a leading, easy to use visual analytics tool. Sign up on Tableau’s website and you’ll receive an activation key. Once you install the software, start with their Tableau Training and Tutorials.
Microsoft PowerBI also allows you to create visualizations through a user-friendly interface. Their product is alsofree for download. They also have a nice “getting started” tutorial.
Map geospatial data
-
Google Fusion Tables is simple. You need a regular – non-Temple – Google account.
-
Carto creates great looking maps. A free-tier is available.
Create infographics
- Compose and edit using Piktochart or Infogram.
- Find great examples on Cool Infographics, Daily Infographic, and the Guardian. Updated frequently.
Get public data
-
Data.gov is the Federal Government’s open data initiative. Hundreds of data sets.
- The Pew Research Center has a number of data sets on different social and technology topics.
- Socrata is an open data repository with data, mostly from government sources.
-
The City of Philadelphia posts their public data to OpenDataPhilly.
Lynda resources
Lynda.com hosts software video tutorials. Acces to the following resources may require an account.
Judging
How entries will be evaluated
All entries will be evaluated by the judges in two categories: visualization and analysis.
The specific criteria for each category are:
Graphic
- Clarity (how well the graphic stands on its own without additional explanation)
- Novelty/creativity (originality of thought; surprising way of approaching the data)
- Insight (graphic aids understanding of the data)
- Utility (ability of the graphic to aid decision making)
Analysis
- Relevance (analysis relates to the problem statement)
- Completeness (degree to which the analysis answers the stated question)
- Depth (sophistication of the analysis)
- Consistency (conclusions consistent with the analysis)
Your entry will be disqualified if…
- It is submitted after the deadline.
- The attachments won’t open or are in the wrong file format.
- Your interactive graphic won’t run.
- You don’t specify the challenge you are addressing.
- Team member names and college name are not on both the graphic and the description.