Introduction

This project analyzes a 20 million row supply chain dataset to uncover insights on supplier performance, delivery delays, and inventory optimization.

Goals:

Identify delivery bottlenecks
Product-level performance
Recommend improvements to reduce costs and delays

1. Load Libraries

library(data.table)   # fast data handling
library(dplyr)        # data manipulation
library(ggplot2)      # visualizations
library(skimr)        # quick data overview
library(arrow)        # efficient file format
library(knitr)        # nice tables
library(stringi)
library(scales)
library(lubridate)
library(rworldmap)
library(GGally)
knitr::opts_chunk$set(echo = TRUE)

Create a new column without diacritics

supply_chain\(Order.Country <- stri_trans_general(supply_chain\)Order.Country.Map, “Latin-ASCII”)

descriptionDataCoSupplyChain <- read.csv(“data/DescriptionDataCoSupplyChain.csv”)

tokenized_access_logs <- read.csv(“data/tokenized_access_logs.csv”)

Quick overview

skim_without_charts(supply_chain)

R Markdown

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.

# Add new column with country translated Spanish to English using excel VLOOKUP
supply_chain <- read.csv("data/DataCoSupplyChainDataset.csv", fileEncoding = "latin1")

# Create a new column without diacritics
supply_chain$Country_clean <- stri_trans_general(supply_chain$Order.Country.Map, "Latin-ASCII")

# Now Order.Date is a POSIXct datetime, proper date type.
# Convert the column to datetime
supply_chain$Order.Date <- mdy_hm(supply_chain$Date.Order)

# Filtering the data
region_summary <- supply_chain %>%
  group_by(Order.Region) %>%
  summarise(total_profit = sum(Order.Profit.Per.Order, na.rm = TRUE)) %>%
  arrange(desc(total_profit))

#delivery_perf
delivery_perf <- supply_chain %>%
  mutate(
    delay_days = Days.for.shipping..real. - Days.for.shipment..scheduled.
  ) %>%
  select(Order.Id, Market, Customer.Country,Order.Country.Map , Order.State, Product.Name, Category.Name,
         Days.for.shipping..real., Days.for.shipment..scheduled., delay_days,
         Delivery.Status, Late_delivery_risk)

# Customer country
bottlenecks_customer_country <- delivery_perf %>%
  group_by(Customer.Country) %>%
  summarise(
    avg_real = mean(Days.for.shipping..real., na.rm = TRUE),
    avg_scheduled = mean(Days.for.shipment..scheduled., na.rm = TRUE),
    avg_delay = mean(delay_days, na.rm = TRUE),
    late_ratio = mean(Late_delivery_risk, na.rm = TRUE),
    orders = n(),
    .groups = "drop"
  ) %>%
  arrange(desc(avg_delay))

# Order country
bottlenecks_order_country <- delivery_perf %>%
  group_by(Order.Country.Map) %>%
  summarise(
    avg_real = mean(Days.for.shipping..real., na.rm = TRUE),
    avg_scheduled = mean(Days.for.shipment..scheduled., na.rm = TRUE),
    avg_delay = mean(delay_days, na.rm = TRUE),
    late_ratio = mean(Late_delivery_risk, na.rm = TRUE),
    orders = n(),
    .groups = "drop"
  ) %>%
  arrange(desc(avg_delay))



head(bottlenecks_customer_country, 10)

## # A tibble: 2 × 6
##   Customer.Country avg_real avg_scheduled avg_delay late_ratio orders
##   <chr>               <dbl>         <dbl>     <dbl>      <dbl>  <int>
## 1 EE. UU.              3.49          2.93     0.568      0.549 111146
## 2 Puerto Rico          3.50          2.94     0.563      0.548  69373

head(bottlenecks_order_country, 10)

## # A tibble: 10 × 6
##    Order.Country.Map avg_real avg_scheduled avg_delay late_ratio orders
##    <chr>                <dbl>         <dbl>     <dbl>      <dbl>  <int>
##  1 Bhutan                6             4         2         1          5
##  2 Eritrea               6             4         2         1          2
##  3 Laos                  5.83          4         1.83      1          6
##  4 Slovenia              3.5           1.67      1.83      0.833      6
##  5 Estonia               3.83          2.28      1.55      0.966     29
##  6 Equatorial Guinea     3.5           2         1.5       1          2
##  7 Luxembourg            4.62          3.12      1.5       0.688     16
##  8 Moldova               4.13          2.72      1.41      0.692     78
##  9 Costa Rica            3.94          2.64      1.31      0.694     36
## 10 Mongolia              3.66          2.38      1.28      0.658     79

# Order country
bottlenecks_order_market <- delivery_perf %>%
  group_by(Market) %>%
  summarise(
    avg_real = mean(Days.for.shipping..real., na.rm = TRUE),
    avg_scheduled = mean(Days.for.shipment..scheduled., na.rm = TRUE),
    avg_delay = mean(delay_days, na.rm = TRUE),
    late_ratio = mean(Late_delivery_risk, na.rm = TRUE),
    orders = n(),
    .groups = "drop"
  ) %>%
  arrange(desc(avg_delay))

#Standardize names so they match the map data.
bottlenecks_order_country$Order.Country.Map <- recode(
  bottlenecks_order_country$Order.Country.Map,
  "United States of America" = "USA",
  "United States" = "USA"
)

Insights

“We analyzed delivery delays by both customer location and order location. The order country gave more meaningful differentiation, so we used it for bottleneck detection.”

ggplot(bottlenecks_order_market, aes(x = reorder(Market, avg_delay), y = avg_delay)) +
  geom_col(fill = "tomato") +
  labs(title = "Average Delivery Delay by Country",
       x = "Market", y = "Average Delay (days)") +
  coord_flip() +
  theme_minimal()

Market-Level Insights

From the market-level delivery delay analysis, we observe that certain markets consistently experience higher average shipping delays. These markets, shown as the tallest bars in the chart, may indicate logistical challenges or inefficiencies. Focusing on these markets could help improve overall delivery performance and reduce the risk of late shipments.

Additionally, markets with higher delays often correspond to elevated late delivery ratios, suggesting persistent performance issues rather than isolated incidents.

top_countries <- bottlenecks_order_country %>%
  arrange(desc(avg_delay)) %>%
  slice_head(n = 15)

ggplot(top_countries, aes(x = reorder(Order.Country.Map, avg_delay), y = avg_delay)) +
  geom_col(fill = "tomato") +
  coord_flip() +
  labs(title = "Top 15 Countries by Average Delivery Delay",
       x = "Country", y = "Average Delay (days)") +
  theme_minimal()

Insights

After analyzing the data, we observed the following: “Countries with higher avg_delay also tend to have elevated late_ratio, suggesting consistent logistical inefficiency rather than isolated incidents.”

# Get the world map data
world_data <- map_data("world")

# Join your summarized data with map data
map_perf <- world_data %>%
  left_join(bottlenecks_order_country, by = c("region" = "Order.Country.Map"))

# Plot average delay by country
ggplot(map_perf, aes(long, lat, group = group, fill = avg_delay)) +
  geom_polygon(color = "white") +
  scale_fill_viridis_c(option = "plasma", na.value = "grey90") +
  coord_fixed(1.3) +  # make wider or narrower (1.0–1.5 typical)
  theme_minimal() +
  labs(title = "Average Delivery Delay by Country",
       fill = "Avg Delay (days)")

kable(summary(bottlenecks_order_country))

Order.Country.Map	avg_real	avg_scheduled	avg_delay	late_ratio	orders
Length:155	Min. :0.000	Min. :0.000	Min. :-1.0000	Min. :0.0000	Min. : 1.0
Class :character	1st Qu.:3.290	1st Qu.:2.782	1st Qu.: 0.3972	1st Qu.:0.5000	1st Qu.: 38.0
Mode :character	Median :3.487	Median :2.928	Median : 0.5604	Median :0.5538	Median : 210.0
NA	Mean :3.460	Mean :2.916	Mean : 0.5437	Mean :0.5309	Mean : 1164.6
NA	3rd Qu.:3.626	3rd Qu.:3.105	3rd Qu.: 0.6768	3rd Qu.:0.5990	3rd Qu.: 866.5
NA	Max. :6.000	Max. :4.000	Max. : 2.0000	Max. :1.0000	Max. :30800.0

# Use country-level summary for correlation plot
ggpairs(bottlenecks_order_country[, c("avg_real", "avg_scheduled", "avg_delay", "late_ratio")])

Country-Level Insights

The ggpairs plot reveals several key patterns:

Countries with higher actual shipping times (avg_real) generally experience higher average delays (avg_delay), indicating consistent inefficiencies in certain regions.
Countries with high avg_delay also tend to have a higher late_ratio, meaning delays often result in late deliveries.
Most countries cluster around low delay values, but a few outliers with very high delays indicate critical bottlenecks that should be prioritized for improvement.

product_perf <- delivery_perf %>%
  group_by(Product.Name) %>%
  summarise(
    avg_real = mean(`Days.for.shipping..real.`, na.rm = TRUE),
    avg_scheduled = mean(`Days.for.shipment..scheduled.`, na.rm = TRUE),
    avg_delay = mean(delay_days, na.rm = TRUE),
    late_ratio = mean(Late_delivery_risk, na.rm = TRUE),
    orders = n(),
    .groups = "drop"
  ) %>%
  arrange(desc(avg_delay))

top_products <- product_perf %>% slice_max(avg_delay, n = 15)

ggplot(top_products, aes(x = reorder(Product.Name, avg_delay), y = avg_delay)) +
  geom_col(fill = "steelblue") +
  coord_flip() +
  labs(
    title = "Top 15 Products by Average Delivery Delay",
    x = "Product",
    y = "Average Delay (days)"
  ) +
  theme_minimal()

ggplot(product_perf, aes(x = avg_delay, y = late_ratio, size = orders)) +
  geom_point(alpha = 0.7, color = "tomato") +
  labs(
    title = "Product Performance: Delay vs Late Delivery Ratio",
    x = "Average Delay (days)",
    y = "Late Delivery Ratio",
    size = "Number of Orders"
  ) +
  theme_minimal()

Insights

Products with high avg_delay are likely causing customer dissatisfaction.

High late_ratio indicates persistent delays for these products.

Products with many orders but low delays are reliable.

This product-level view can approximate supplier evaluation if each product comes from a distinct supplier.

Recommendations and Improvements to Reduce Costs and Delays

Focus on High-Delay Markets

Observation: Some markets consistently have higher average delivery delays.

Recommendation:
- Investigate logistics partners or carriers servicing these markets.
- Consider alternative shipping providers or consolidating shipments to reduce delays.
- Prioritize improving infrastructure or warehouse distribution in these regions.
Target Countries with Persistent Delays

Observation: Certain countries have both high avg_delay and high late_ratio.

Recommendation:
- Work with suppliers in those countries to improve shipping times.
- Consider regional warehousing closer to high-demand countries to reduce transit time.
- Implement predictive shipping schedules for these regions.
Product-Level Optimization

Observation: Some products consistently cause delays and late deliveries.

Recommendation:
- Identify if delays are caused by supplier inefficiencies, inventory shortages, or packaging issues.
- Re-evaluate suppliers for delayed products or diversify suppliers to avoid single points of failure.
- Optimize inventory levels for high-delay products to reduce stockouts and rush shipping costs.
Reduce Late Delivery Risk

Observation: High late_ratio indicates persistent delays, not random occurrences.

Recommendation:
- Implement early warning alerts when shipments are likely to be late.
- Reassess scheduled shipping times and increase buffer if consistently underestimated.
- Use historical delay data to optimize delivery scheduling.
Improve Data & Process Tracking

Observation: Some delay patterns may not be fully explained by current data.

Recommendation:
- Collect real-time shipment tracking to better understand where delays occur (supplier, carrier, customs, etc.).
- Use data dashboards for operational monitoring and faster decision-making.
Cost Reduction Strategies

Observation: Shipping costs can be high in certain markets or for specific products.

Recommendation:
- Consolidate shipments by region to reduce freight costs.
- Negotiate better rates with reliable carriers in high-delay markets.
- Prioritize fast-moving or high-value products for quicker delivery, while optimizing slower-moving item

Supply Chain Data Analysis

Daniel Amorim

2025-09-29

Introduction

1. Load Libraries

Create a new column without diacritics

Quick overview

R Markdown

Insights

Market-Level Insights

Insights

Country-Level Insights

Insights

Recommendations and Improvements to Reduce Costs and Delays