top of page
Depression.bmp
SE-Image-Freight-Forwarding.jpg
International_Shipping_Graphic.png

Logistics Efficiency and Sales Insights

   This dataset is made up of three tables that help us understand sales performance, how products are distributed, and how efficient our warehouses are. I used this dataset in one of my projects for an interview, and though the data is made-up, it's relevant for a company in the global logistics and transportation industry (name omitted for privacy).

  • Product Table:

     This table gives details about each product, including its weight, volume, and whether it's classified as a "Parcel" or "Cargo." This information is important because it affects shipping costs and how products are stored and handled.

  • Sales Data Table:

       This table tracks all sales across different countries and warehouses. It includes important details like how many products were sold, where they were sold, and the cost of goods sold (COGs). This helps us see which countries are generating the most revenue and where our costs are highest.

  • Warehouse Mapping Table:

    This small table links the warehouse IDs from the sales data to their actual names and locations. It helps us know where products are being shipped from and compare the performance of each warehouse.

dataset.bmp

Dataset Overview

Number of Rows:

  • Product Mapping: 432 rows

  • Sales Data: 1,132,937 rows

  • Warehouse Mapping: 4 rows

​

Number of Columns:

  • Product Mapping: 4

  • Sales Data: 7

  • Warehouse Mapping: 2

 

Key Columns:

Product Mapping Dataset:

  • ProductNumber: Unique identifier for each product.

  • Weight_kg: The weight of each product in kilograms.

  • Volume_m3: The volume of each product in cubic meters.

  • Category: Classification of the product (Parcel or Cargo).

​

Sales Data Dataset:

  • Date: Date of the transaction.

  • Country: Country where the sale occurred.

  • ProductNumber: Reference to the product sold.

  • Quantity: Number of units sold.

  • DocType: Document type (Order or Invoice).

  • COGs: Cost of goods sold for the transaction.

  • WarehouseId: Warehouse that dispatched the product.

​

Warehouse Mapping Dataset:

  • WarehouseID: Unique identifier for each warehouse.

  • Warehouse: Name or location of the warehouse.

(Task 1): Costs of Goods Sold (COGs) for Invoices by Year, Month, Country, Warehouse, and Category

Findings:

  • COGs by Year:

   The highest COGs were recorded in 2021, which saw a total of over 200 million units. The costs then dropped significantly in 2022 and continued decreasing into 2023 and 2024.

  • COGs by Country:

     Slovakia (SK) was the leading country, contributing 23.54% (93 million) to the total COGs. Hungary (HU) followed with 19.12% (76 million). Other countries like Poland (PL) and Romania (RO) contributed less.

  • COGs by Category:

     The majority of the costs came from "Parcel" products (92.4%), while "Cargo" contributed just 7.6%.

  • COGs by Warehouse:

     The Bratislava warehouse handled 60% of the total COGs, followed by Amazon at 33.3%. Hamburg and Bucurest had smaller contributions.


Details: 
   This visualization answers Question 1: "Visualize the COGs of Invoices per year, month, country, warehouse, and category." The bar charts for year and month provide time trends, while the pie charts for country, category, and warehouse show the breakdown of COGs across these dimensions.

capture_241012_115136.png

(Task 2): Year-over-Year Development of Orders by Country

Findings:

  • Year-over-Year Trends:

      Slovakia (SK) consistently contributed the most in terms of COGs across all years. Other countries like Romania (RO), Hungary (HU), and Austria (AT) showed notable contributions, though there was a visible decline in COGs from 2021 to 2024.

  • Overall Decline:

     There was a significant decline in COGs from 2021 to 2024 across all countries. Most notably, the peak in 2021 was followed by a decrease in 2022, with even fewer costs recorded in 2023 and 2024.

​

Details:

  • This visualization is connected to Question 2: "Show year-over-year development of Orders by country, while looking at COGs." The bar chart allows you to see how each country’s orders, and their associated costs, evolved over the years. It visually highlights the countries that were most active and how the ordering patterns changed year over year.

capture_241012_115846.png

(Task 3): Delivery Costs by Month

Findings:

  • Highest Delivery Costs:
    November 2021 saw the highest delivery costs, exceeding 2 million units. This indicates a surge in shipments during this period, likely due to seasonal demand.

  • Steady Decline:
    Delivery costs gradually decreased throughout 2022, stabilizing at lower levels during 2023 and 2024. By 2024, delivery costs were significantly reduced.

​

Details:
     This visualization answers Question 3: "What are our delivery costs per month based on product weight?" The bar chart breaks down monthly delivery costs, calculated using the rule that products over 32 kg cost €10 to deliver and products under 32 kg cost €4. This helps track delivery expenses over time and spot any seasonal trends.

capture_241012_120115.png

(Task 4): Uninvoiced Orders and Unfulfilled COGs by Country and Warehouse

Findings:

  • Unfulfilled COGs by Country:

     Bulgaria (BG) had the highest unfulfilled COGs, accounting for 15.76% of the total (5 million units). Other countries like Croatia (HR) and Romania (RO) also showed significant levels of unfulfilled COGs.

  • Unfulfilled COGs by Warehouse:

     The Amazon warehouse was responsible for 94.52% of the unfulfilled COGs (27 million units), while Bucurest and Hamburg contributed much smaller portions. Bratislava did not contribute at all.

​

Details:

  • This visualization is linked to Question 4: "Find out if there are orders that weren’t invoiced and the unfulfilled COGs by country and warehouse."​ The pie charts highlight which countries and warehouses had the highest levels of unfulfilled orders, giving insight into potential operational inefficiencies or problems within certain regions.

capture_241012_121437.png

Key findings and Outcomes

  • 2021 had the highest COGs, with significant drops in 2022 and beyond.

  • Slovakia (SK) contributed the most to COGs, followed by Hungary (HU).

  • "Parcel" products accounted for 92% of total COGs.

  • The Bratislava warehouse handled 60% of all COGs.

  • Orders and COGs dropped year-over-year, with a sharp decline from 2021 to 2024.

  • Delivery costs peaked in November 2021 and decreased steadily afterward.

  • Amazon warehouse had 94% of unfulfilled COGs, while Bulgaria had the highest uninvoiced orders.

bottom of page