Pharmacy Sales analysis (December)

Background

I worked on analyzing raw sales data for December from a community pharmacy to identify trends and provide valuable insights for the pharmacy. A community pharmacy, also known as a retail pharmacy, is the most common type of pharmacy that offers the public access to medications and health advice. These pharmacies typically offer a range of pharmaceutical services, including dispensing, point-of-care tests, consultations, and complementary products and services in their supermarket section.

I used the following steps in analyzing the data:

Data cleaning

The raw data was extracted from the company’s software in an Excel format, so I did some of the cleaning in Excel and some finishing touches in PowerBI. The cleaning process involved Editing column headings; and calculations to get the profit made on each sale by subtracting the cost price from the selling price; there were a lot of products so I created categories based on the products on the sheet, divided the products into the following categories: Supermarket, Vitamins & Supplements, Sexual health, Baby & child care, Pain, ENT & Allergy, Skin, and Chronic health; Spelling uniformity for product names etc. Then imported the cleaned data in PowerBi and double-checked it before analysis.

Data analysis + Insights

A picture of the dashboard can be found at the end of this write-up.

Insights

The products were categorized into the following:

Supermarket: Non-pharmacy products, wound dressing

Vitamins and Supplements: All the supplements and multivitamins, blood tonics

Sexual health: Condoms, pregnancy test strips, lubricants, and hormone medications

Baby and child care: Children supplements and drugs Pain — Analgesics, antiulcer, muscarinic, and antacids

ENT and Allergy: Ear & Eye drops and allergy medications

Skin: All topical medications

Chronic health: Antihypertensives, Antidiabetics, Anticholesterol/ Lipids, CNS medications

The most profit was generated from the supermarket product (supermarket sales resulted in half of the profit generated, 2.42M), it was also the most sold category. This could be attributed to the time of the month the data was collated, December.

The top 5 pharmacy products generating the most sales include; Vitamins and supplements, Sexual Health, Antibiotics/Antifungal/Antiviral, and Pain. These top 5 pharmacy products were not directly proportional to the quantity sold. That is, the quantity sold does not affect the profit generated for pharmacy products. It was also observed that antihypertensives were sold the highest.

Recommendations

• Top 10 or Top 5 pharmacy products generating the most profit should be well positioned on the shop floor for customers to easily see and pick.
• Vitamins and supplements are good ways to increase the basket size since they also generate more profit and it’s easy to encourage more customers to buy based on their complaints and conditions with the appropriate knowledge. Especially the sexual health supplements, which can go together with the products under the sexual health category since it is one of the most sold products in that environment.
• The medical devices and tests category in the pharmacy section can be explored more to boost the profit from the pharmacy unit. Compared to the other pharmacy categories that mostly contain drugs, exploring the medical devices and tests category through improving knowledge, awareness, and available services to customers. Also, self-test kits for sexual health such as HIV self-test kits, ovulation kits, or partnering with nearby labs to encourage STI testing can be explored to improve sales.
• Programs and outreaches for hypertensive patients would help to retain these customers and even bring more people to the pharmacy since antihypertensives are sold the most.

Limitations

• Only the December data was analyzed which cannot give a holistic picture of the sales performance and trends of the pharmacy
• It was difficult to analyze the data provided for the other months because of the inconsistency in the product names and spelling for such a large dataset. Also, because the products were not grouped into categories from the software used, several new products were sold in different months adding to the inconsistency in the datasets.
• There was no information on other factors that could have affected the increase in sales of some products such as discounts, promo, or marketing campaigns.