Foresight Pharmaceuticals Sales Analysis

Analysis of Foresight Pharmaceuticals wholesale-retail sales data using PostgreSQL

Moriam Adesola Adegbite
8 min readNov 3, 2022

Introduction

Foresight Pharmaceuticals is one of the leading Pharmaceutical Manufacturing companies with a global presence. Their markets are divided into different regions across the world. One of those regions manages the German and Poland Markets. Although the company does not sell directly to customers, instead, they work with a couple of distributors in all their regions. Each distributor is bound by an agreement to share their Sales Data with the manufacturing company. Thus, it enables them to gain insights up to the retail level.

About the dataset

The dataset was provided by Foresight BI. It consists of 18 columns and 254,082 rows. The dataset contains transaction reports from 2017 to 2020 and has information on the products, distributors, distribution channels and customers, sales, and sales representatives. All data for this project are saved in the table called pharm. The appearance is as shown below.

Business Questions

The following are the business questions I will be providing an answer to by analyzing the data:

1. What are the total sales and returns from 2017 to 2020 and in each year?

2. What quantities of products were sold and returned each year?

3. What are the total sales for each product class?

4. Which sales channel and sub-channel have the highest amount of sales?

5. Who are the top and bottom 5 distributors?

6. Who are the top 10 customers?

7. Which sales rep, manager, and sales team have the highest sales?

8. What is the total sales per country?

Data exploration

The following queries were written to get a good understanding of the data.

  • Table

The columns in the dataset are distributors, customer name, city, country, latitude, longitude, channel, sub-channel, product name, product class, quantity, price, sales, sales rep, manager, and sales team. Each row represents a transaction.

  • Product classes

The classes of products sold are analgesics, antipiretics, antimalarials, antibiotics, antiseptics, and mood stabilizers.

  • Total number of products and per product class

There are 240 unique pharmaceutical product names in the data. Out of this, forty-nine (49) are antiseptics, forty-four (44) are mood stabilizers, and forty-two (42) are analgesics, while the rest are thirty-five (35) each.

  • Countries the pharmaceutical products are distributed to

The data has products distributed to 2 countries; Germany and Poland.

  • Number of cities per country

The pharmaceutical products were distributed to 549 cities in Germany and 200 cities in Poland.

  • Number of distributors

Foresight Pharmaceuticals has 29 unique distributors.

  • Number of customers

There are 751 unique customers purchasing pharmaceutical products

  • Number of customers to each distributor

The result shows that customers can buy from any of the distributors. Koss, Erdman, Welch-Langworth, and Gerlach LLC had the highest number of customers (551). Kris LLC had the lowest (48) number of customers.

  • Distribution channels and sub-channels

The pharmaceutical products were sold via two channels (hospital and pharmacy), which are further subdivided into two sub-channels each. The sub-channels for hospital sales are government and private, while institution and retail are the sub-channel for pharmacy sales.

Data cleaning

To clean the data, I checked for null values and zero quantity or sales values.

a. Check for null values.

The result shows that table has no null values.

b. Check if the quantity, price, and sales columns have zero values.

The result shows that 27 rows have zero quantity and sales. These rows will be deleted.

c. Delete rows with zero quantity and sales

d. The latitude and longitude columns are not needed for this analysis. They are therefore deleted.

e. Number of rows and columns after data cleaning

After cleaning the data, I was left with 254,055 rows.

While exploring the data, I noticed there were rows with negative quantity and sales values. Then I checked for the number of rows with positive and negative quantities/sales. Although the reason for the negative values was not specified. I assumed they were transactions where products were returned.

The result shows that there are 251,422 rows with positive sales values (actual sales) while 2,633 rows have negative values (returned products)

Data analysis

1. What are the total sales and returns from 2017 to 2020 and in each year?

a. Total sales

The ROUND() function was used because the quantity column has both integer and decimal numbers.

b. Sales and returns

The following query was written to determine the total amount of sold items (excluding the amount returned) and the total amount returned from 2017 to 2020.

c. Sales per year

d. Sales and returns per year

The total sales accrued is 11.79B, which should have summed up to 11.95B if products were not returned. Thus, as of result of the cases of returned pharmaceutical products, 146.5M was refunded to customers. The sales recorded in 2017, 2018, 2019, and 2020 were 2.7B, 3.51B, 2.93B, and 2.66B. Therefore, the highest sales were recorded in 2018 while the lowest was recorded in 2020. Also, the year 2020 recorded the highest amount of refunds while it occurred the least in 2017.

2. What quantities of products were sold and returned each year?

a. Total quantity of products sold i.e. the aggregate number of products sold after deducting the ones that were returned.

b. Total quantity sold and returned

c. Quantity sold per year

d. Total quantity sold and returned per year

From 2017 to 2020, the total quantity of pharmaceutical products sold by Foresight BI is 28,678,779 while 357,440 units were returned. Like the sales values, the year 2018 recorded the highest quantity sold while 2020 had the least. The total quantity of products returned in each year is in ascending order 2017, 2019, 2018, and 2020.

3. What are the total sales for each product class?

The top two sales were from analgesics and antiseptics while the bottom two sales were from antibiotics and antimalarials.

4. Which sales channel and sub-channel have the highest amount of sales?

From the hospital channel, more sales were gotten from government hospitals than from private hospitals while for the pharmacy channel, more sales were gotten from retail pharmacies than from institutional pharmacies.

5. Who are the top and bottom 5 distributors?

a. Top 5 distributors

b. Bottom 5 distributors

The top 5 distributors are Gerlach LLC, Koss, Erdman, Kozey-Emmerich, and Romaguera-Fay. The bottom 5 distributors are Hansen Group Pharm, Kris LLC, Rogahn-Klein, Carter-Conn, and Schuppe Inc.

6. Who are the top 10 customers?

The top 10 customers include Mraz-Kutch Pharma Plc, Parker, Green and Emmerich Pharma Plc, Zemlak-Witting, Moen, Murazik and Smith Pharm, Mills Inc Pharmaceutical Ltd, Streich PLC, Gleichner, Bahringer and Morar Pharmaceutical Limited, Reichel Inc, Romaguera, Moen and Hagenes Pharmacy, Torphy, Pfeffer and Jakubowski Pharm.

7. Which sales rep, manager, and sales team have the highest sales?

a. Top 3

b. Bottom 3

Jimmy Grey of Charlie's sales team accrued the highest amount of sales while Alan of Alfa's team ranked lowest. Additional information that was derived included that the sales reps with the second and third rankings are in the Bravo and Delta team, respectively, while the bottom three sales reps belonged to the Alfa team.

8. What is the total sales per country?

Germany has a total sales of 11.11B while Poland has a total sales of 680M.

To interact with the Tableau dashboard, click here.

Conclusion

Based on the analysis done, I can conclude that:

1. Foresight Pharmaceuticals generated a total sales of 11.79B and only 5.77% of the sales were generated from Poland.

2. The highest sales were generated in 2018. However, sales decreased persistently from 2018 to 2020.

3. Analgesics was the company’s top-selling product while Antimalarial was the least.

4. Although Antimalarials were the 4th most purchased product in terms of quantity, they generated the least sales.

5. In terms of the channels and sub-channels of distribution, more sales were generated from Government hospitals than Private hospitals, and from Retail pharmacies than Institutional pharmacies.

To view my SQL codes, click here.

--

--

Responses (1)