Can small independent pharmacies compete with the big chains?

Background

amerisourcebergen_cmyk-300x781Independent 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

  • Submission Instructions

    What you should submit

    • The story: A graphic that reflects the major findings of your data analysis.
      • It can be a static image or interactive (such as JavaScript).
      • It can be in the form of charts, diagrams, or an infographic.
    • The background: A brief description, one single-spaced page or less, describing what’s great about your analysis and your graphic.
      • Highlight how your analysis helped you arrive at your conclusions.
      • Highlight how your graphic illustrates your key findings.

    How you should submit

    • Email your entry to analyticschallenge@temple.edu by 11:59 P.M. on March 1, 2017.
      • Make sure it’s clear which of the three challenges you’re addressing.
    • Attach the graphic and the description as separate files to a single email message with the subject “Entry for Analytics Challenge”.
    • The following must be clearly displayed on the graphic and on the one page-description: (1) the name of the challenge (Alexion, Amerisourcebergen, or Merck), (2) each team member’s name and college/university name.
    • Convert static graphics to PDF format. The one-page description must also be in PDF format. There are free tools that do this, such as PDF Creator.
    • Interactive graphics must be self-contained and run without special software on any Windows 10 PC. Web-based graphics must run using the Chrome browser.

    What happens after you submit

    • You will receive a confirmation email acknowledging your entry.
    • Finalists will be notified by email on or around March 6, 2017.
    • Finalists will present their work in front of the judging panel at the AIS SCLC April 13-15, 2017.

  • Timeline

    • All preliminary submissions must be received no later than March, 1 2017
    • Finalists will be announced on or around March 6, 2017
    • The winner will be revealed at the Student Chapters Leadership Conference between April 13-15, 2017

  • Prizes

    There will be awards in two categories: graphic and analysis. The judges will evaluate each entry on both categories. Prizes will be awarded for each category separately and an entry can only win in a single category.

    Graphic

    First place – $2,000
    Second place – $1,000
    Third place – $500

    Analysis

    First place – $2,000
    Second place – $1,000
    Third place – $500

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

Get public data

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.