Portland Plant KPI Tracker

Portland KPI Report

Load required libraries

# Load required libraries
library(readxl)        
library(dplyr)         

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(ggplot2)       
library(tidyr)         
library(stringr)       
library(scales)        
library(gridExtra)     

## 
## Attaching package: 'gridExtra'

## The following object is masked from 'package:dplyr':
## 
##     combine

library(knitr)        
library(rmarkdown)    
library(lubridate)     

## 
## Attaching package: 'lubridate'

## The following objects are masked from 'package:base':
## 
##     date, intersect, setdiff, union

library(RColorBrewer)  

Load requiered excel data base

# Load the dataset
bd <-read_excel("/Users/sebastianespi/Downloads/DATA BASE sebastian - KPI portalnd report.xlsx")
bd

## # A tibble: 105 × 8
##     Week Name    Category   Acknowledgements `Late Ack >3days` `TTS < 10 days`
##    <dbl> <chr>   <chr>                 <dbl>             <dbl>           <dbl>
##  1    25 Team    Percentage               18                10              61
##  2    25 Ivey    Percentage               19                10              16
##  3    25 Corrina Percentage               16                89               4
##  4    26 Team    QTY                     319                30              56
##  5    26 Team    Percentage               17                 9               2
##  6    26 Ivey    QTY                     195                20              17
##  7    26 Ivey    Percentage               19                10               1
##  8    26 Corrina QTY                     124                10              39
##  9    26 Corrina Percentage               16                 8               3
## 10    27 Team    QTY                     348                30              56
## # ℹ 95 more rows
## # ℹ 2 more variables: `Invoice past due` <dbl>, `W-OTD (OOR)` <chr>

Exploratory Analisis of the data base

# Clean the dataset - your data is already in the right format
bd_clean <- bd %>%
  mutate(
    # Convert percentage columns to numeric (already clean numeric values)
    `Acknowledgements_%` = case_when(
      Category == "Percentage" ~ as.numeric(`Acknowledgements`),
      TRUE ~ NA_real_
    ),
    
    `Late Ack >3days_%` = case_when(
      Category == "Percentage" ~ as.numeric(`Late Ack >3days`),
      TRUE ~ NA_real_
    ),
    
    `TTS < 10 days_%` = case_when(
      Category == "Percentage" ~ as.numeric(`TTS < 10 days`),
      TRUE ~ NA_real_
    ),
    
    `Invoice past due_%` = case_when(
      Category == "Percentage" ~ as.numeric(`Invoice past due`),
      TRUE ~ NA_real_
    ),
    
    `W-OTD (OOR)_%` = case_when(
      Category == "Percentage" ~ as.numeric(`W-OTD (OOR)`),
      TRUE ~ NA_real_
    ),
    
    # Convert quantity columns to numeric
    `Acknowledgements_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`Acknowledgements`),
      TRUE ~ NA_real_
    ),
    
    `Late Ack >3days_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`Late Ack >3days`),
      TRUE ~ NA_real_
    ),
    
    `TTS < 10 days_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`TTS < 10 days`),
      TRUE ~ NA_real_
    ),
    
    `Invoice past due_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`Invoice past due`),
      TRUE ~ NA_real_
    ),
    
    # Parse W-OTD (OOR) quantity (numerator/denominator format)
    `W-OTD (OOR)_Numerator` = case_when(
      Category == "QTY" & str_detect(`W-OTD (OOR)`, "/") ~ as.numeric(str_extract(`W-OTD (OOR)`, "^[0-9]+")),
      TRUE ~ NA_real_
    ),
    `W-OTD (OOR)_Denominator` = case_when(
      Category == "QTY" & str_detect(`W-OTD (OOR)`, "/") ~ as.numeric(str_extract(`W-OTD (OOR)`, "[0-9]+$")),
      TRUE ~ NA_real_
    )
  )

## Warning: There was 1 warning in `mutate()`.
## ℹ In argument: `W-OTD (OOR)_% = case_when(...)`.
## Caused by warning:
## ! NAs introduced by coercion

# Separate data by category (QTY vs Percentage)
bd_percentages <- bd_clean %>% 
  filter(Category == "Percentage") %>%
  select(Week, Name, `Acknowledgements_%`, `Late Ack >3days_%`, `TTS < 10 days_%`, 
         `Invoice past due_%`, `W-OTD (OOR)_%`)

bd_quantities <- bd_clean %>% 
  filter(Category == "QTY") %>%
  select(Week, Name, `Acknowledgements_QTY`, `Late Ack >3days_QTY`, `TTS < 10 days_QTY`, 
         `Invoice past due_QTY`, `W-OTD (OOR)_Numerator`, `W-OTD (OOR)_Denominator`)

cat("Data cleaning completed successfully!\n")

## Data cleaning completed successfully!

cat("Percentage data points:", nrow(bd_percentages), "\n")

## Percentage data points: 54

cat("Quantity data points:", nrow(bd_quantities), "\n\n")

## Quantity data points: 51

# Display sample of cleaned data to verify
cat("Sample of Percentage Data:\n")

## Sample of Percentage Data:

print(head(bd_percentages))

## # A tibble: 6 × 7
##    Week Name    `Acknowledgements_%` `Late Ack >3days_%` `TTS < 10 days_%`
##   <dbl> <chr>                  <dbl>               <dbl>             <dbl>
## 1    25 Team                      18                  10                61
## 2    25 Ivey                      19                  10                16
## 3    25 Corrina                   16                  89                 4
## 4    26 Team                      17                   9                 2
## 5    26 Ivey                      19                  10                 1
## 6    26 Corrina                   16                   8                 3
## # ℹ 2 more variables: `Invoice past due_%` <dbl>, `W-OTD (OOR)_%` <dbl>

# Create color palette for consistent theming
team_colors <- c("Team" = "#2E86AB", "Ivey" = "#A23B72", "Corrina" = "#F18F01")

GRAPH 1: Acknowledgements Percentage Analysis

graph_ack_pct <- ggplot(bd_percentages, aes(x = Week, y = `Acknowledgements_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "Acknowledgements Rate Analysis by Team Member",
    subtitle = "Weekly acknowledgement percentage performance (Week 25-42)",
    x = "Week Number",
    y = "Acknowledgements Rate (%)",
    color = "Team Member",
    caption = "Higher percentages indicate better acknowledgement performance"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_ack_pct)

Acknowledgements Rate: This graph shows the efficiency in processing acknowledgements for each team member. Higher percentages indicate better performance in acknowledgement processing.

GRAPH 2: Late Acknowledgements (>3 days) Percentage Analysis

graph_late_pct <- ggplot(bd_percentages, aes(x = Week, y = `Late Ack >3days_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "Late Acknowledgements Rate Analysis (>3 days)",
    subtitle = "Percentage of acknowledgements delayed by more than 3 days",
    x = "Week Number",
    y = "Late Acknowledgements Rate (%)",
    color = "Team Member",
    caption = "Lower percentages indicate better performance"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_late_pct)

Late Acknowledgements (>3 days): This graph tracks delays in acknowledgement processing, where lower percentages are better. Spikes in this graph may indicate system issues, increased volume, or process bottlenecks.

GRAPH 3: Time to Ship (<10 days) Percentage Analysis

graph_tts_pct <- ggplot(bd_percentages, aes(x = Week, y = `TTS < 10 days_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "Time to Ship Performance Analysis (<10 days)",
    subtitle = "Percentage of orders shipped within 10 days",
    x = "Week Number",
    y = "TTS < 10 days Rate (%)",
    color = "Team Member",
    caption = "Higher percentages indicate better shipping performance"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_tts_pct)

Time to Ship (<10 days): This graph measures shipping efficiency, where higher percentages mean more orders are shipped within 10 days, which is excellent for customer satisfaction.

GRAPH 4: Invoice Past Due Percentage Analysis

graph_invoice_pct <- ggplot(bd_percentages, aes(x = Week, y = `Invoice past due_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "Invoice Past Due Rate Analysis",
    subtitle = "Percentage of invoices that are past due",
    x = "Week Number",
    y = "Invoice Past Due Rate (%)",
    color = "Team Member",
    caption = "Lower percentages indicate better invoice management"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_invoice_pct)

Invoice Past Due: This graph monitors billing and payment management, where lower percentages are better. High percentages may suggest billing process issues, customer payment problems, or credit management challenges that require immediate attention.

GRAPH 5: W-OTD (OOR) Percentage Analysis

graph_wotd_pct <- ggplot(bd_percentages, aes(x = Week, y = `W-OTD (OOR)_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "W-OTD (OOR) Performance Analysis",
    subtitle = "Open Order Report / On-Time Delivery percentage",
    x = "Week Number",
    y = "W-OTD (OOR) Rate (%)",
    color = "Team Member",
    caption = "Higher percentages indicate better order management"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_wotd_pct)

W-OTD (OOR) Performance: This graph tracks open order reports and on-time delivery performance, where higher percentages indicate better order management and delivery success.

GRAPH 6: Acknowledgements Quantity Analysis

graph_ack_qty <- ggplot(bd_quantities, aes(x = Week, y = `Acknowledgements_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Acknowledgements Volume Analysis by Team Member",
    subtitle = "Weekly quantity of acknowledgements processed",
    x = "Week Number",
    y = "Number of Acknowledgements",
    fill = "Team Member",
    caption = "Shows actual workload and processing volume"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_ack_qty)

Graphs 6-10 - Volume Analysis: These bar charts show actual workload volumes for each team member, helping understand the scale of operations and workload distribution

GRAPH 7: Late Acknowledgements Quantity Analysis

graph_late_qty <- ggplot(bd_quantities, aes(x = Week, y = `Late Ack >3days_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Late Acknowledgements Volume Analysis (>3 days)",
    subtitle = "Number of acknowledgements delayed by more than 3 days",
    x = "Week Number",
    y = "Number of Late Acknowledgements",
    fill = "Team Member",
    caption = "Shows actual volume of delayed acknowledgements"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_late_qty)

GRAPH 8: Time to Ship Quantity Analysis

graph_tts_qty <- ggplot(bd_quantities, aes(x = Week, y = `TTS < 10 days_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Time to Ship Volume Analysis (<10 days)",
    subtitle = "Number of orders shipped within 10 days",
    x = "Week Number",
    y = "Number of Orders Shipped <10 days",
    fill = "Team Member",
    caption = "Shows actual volume of fast shipments"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_tts_qty)

GRAPH 9: Invoice Past Due Quantity Analysis

graph_invoice_qty <- ggplot(bd_quantities, aes(x = Week, y = `Invoice past due_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Invoice Past Due Volume Analysis",
    subtitle = "Number of invoices that are past due",
    x = "Week Number",
    y = "Number of Past Due Invoices",
    fill = "Team Member",
    caption = "Shows actual volume of overdue invoices"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )
print(graph_invoice_qty)

GRAPH 10: W-OTD (OOR) Volume Analysis

graph_wotd_qty <- ggplot(bd_quantities, aes(x = Week, y = `W-OTD (OOR)_Numerator`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "W-OTD (OOR) Open Orders Volume Analysis",
    subtitle = "Number of open orders reported by team member",
    x = "Week Number",
    y = "Number of Open Orders",
    fill = "Team Member",
    caption = "Shows actual volume of open orders"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )
print(graph_wotd_qty)