XCaliber Foods Dashboard and Gap Analysis
Your Name
2025-11-28
Load 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, unionlibrary(tidyr)
library(ggplot2)
library(tidytext)
library(wordcloud)## Loading required package: RColorBrewerlibrary(RColorBrewer)
library(gridExtra)##
## Attaching package: 'gridExtra'## The following object is masked from 'package:dplyr':
##
## combine##Step 1: Load and Inspect Data
# Read Excel file
data <- read_excel("file_show.xlsx")
# Clean column names (make R-friendly)
colnames(data) <- c("SubmissionID", "Friendliness", "Knowledge", "Quickness",
"QualityofProduct", "Communication", "Recommend",
"MajorThemes", "Commentsforimprovement")
# Inspect data
head(data)## # A tibble: 6 × 9
## SubmissionID Friendliness Knowledge Quickness QualityofProduct Communication
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 5721676825697… 5 5 3 5 5
## 2 5721664585696… 5 5 4 2 5
## 3 5721661975698… 5 5 5 5 5
## 4 5721660335695… 5 5 5 5 5
## 5 5721659575695… 5 5 5 5 5
## 6 5721659035697… 5 5 2 4 4
## # ℹ 3 more variables: Recommend <chr>, MajorThemes <chr>,
## # Commentsforimprovement <chr>colnames(data)## [1] "SubmissionID" "Friendliness" "Knowledge"
## [4] "Quickness" "QualityofProduct" "Communication"
## [7] "Recommend" "MajorThemes" "Commentsforimprovement"summary(data)## SubmissionID Friendliness Knowledge Quickness
## Length:17 Min. :4.000 Min. :3.000 Min. :1.000
## Class :character 1st Qu.:5.000 1st Qu.:5.000 1st Qu.:2.500
## Mode :character Median :5.000 Median :5.000 Median :4.000
## Mean :4.933 Mean :4.733 Mean :3.733
## 3rd Qu.:5.000 3rd Qu.:5.000 3rd Qu.:5.000
## Max. :5.000 Max. :5.000 Max. :5.000
## NA's :2 NA's :2 NA's :2
## QualityofProduct Communication Recommend MajorThemes
## Min. :2.0 Min. :1.000 Length:17 Length:17
## 1st Qu.:4.0 1st Qu.:3.000 Class :character Class :character
## Median :5.0 Median :4.000 Mode :character Mode :character
## Mean :4.4 Mean :3.867
## 3rd Qu.:5.0 3rd Qu.:5.000
## Max. :5.0 Max. :5.000
## NA's :2 NA's :2
## Commentsforimprovement
## Length:17
## Class :character
## Mode :character
##
##
##
## str(data)## tibble [17 × 9] (S3: tbl_df/tbl/data.frame)
## $ SubmissionID : chr [1:17] "5721676825697318812" "5721664585696050154" "5721661975698176769" "5721660335695607353" ...
## $ Friendliness : num [1:17] 5 5 5 5 5 5 5 5 4 5 ...
## $ Knowledge : num [1:17] 5 5 5 5 5 5 5 5 3 5 ...
## $ Quickness : num [1:17] 3 4 5 5 5 2 5 4 5 5 ...
## $ QualityofProduct : num [1:17] 5 2 5 5 5 4 5 5 5 5 ...
## $ Communication : num [1:17] 5 5 5 5 5 4 5 5 1 1 ...
## $ Recommend : chr [1:17] "Yes" "Yes" "Yes" "Yes" ...
## $ MajorThemes : chr [1:17] "Not same day" "Not same day" "Craft beer" "Other products" ...
## $ Commentsforimprovement: chr [1:17] "We only purchase beverages and high end canned meet. Excellent quality products but the time from order to shi"| __truncated__ "Some products it will not matter, but when I have baked goods that come later than expected, I am not satisfied." "Love the beverage price. Would be interested in craft beer." "Good service. Would be interested in other products if they are as cheap as the beverages." ...colSums(is.na(data))## SubmissionID Friendliness Knowledge
## 1 2 2
## Quickness QualityofProduct Communication
## 2 2 2
## Recommend MajorThemes Commentsforimprovement
## 2 2 2##Step 2: Summarize Numeric Metrics
data_summary <- data %>%
summarise(
Avg_Friendliness = mean(Friendliness, na.rm = TRUE),
Avg_Knowledge = mean(Knowledge, na.rm = TRUE),
Avg_Quickness = mean(Quickness, na.rm = TRUE)
)
# Convert to long format for plotting
data_long <- data_summary %>%
pivot_longer(everything(), names_to = "Metric", values_to = "Average")
data_summary## # A tibble: 1 × 3
## Avg_Friendliness Avg_Knowledge Avg_Quickness
## <dbl> <dbl> <dbl>
## 1 4.93 4.73 3.73data_long## # A tibble: 3 × 2
## Metric Average
## <chr> <dbl>
## 1 Avg_Friendliness 4.93
## 2 Avg_Knowledge 4.73
## 3 Avg_Quickness 3.73##Step 3: Visualize Customer Survey Metrics
avg_metrics_plot <- ggplot(data_long, aes(x=Metric, y=Average, fill=Metric)) +
geom_bar(stat="identity", width=0.6, show.legend=FALSE) +
ylim(0,5) +
geom_text(aes(label=round(Average,1)), vjust=-0.5, size=5) +
labs(title="Customer Service Metrics for XCaliber Foods",
x="Service Metric", y="Average Rating (1–5)") +
theme_minimal(base_size=14) +
theme(plot.title=element_text(hjust=0.5, face="bold"))
avg_metrics_plot
##Step 4: Analyze Recommendations and Major Themes
# Count recommendations
table(data$Recommend)##
## Maybe No Yes
## 3 1 11# Count major themes
table(data$MajorThemes)##
## Craft beer
## 1
## Fresh or canned
## 1
## Not same day
## 7
## Other products
## 1
## Poor communication
## 2
## Poor communication, not same day, craft beer
## 1
## Satisfied
## 1
## Try products, not same day
## 1# Group and summarize themes
theme_counts <- data %>%
group_by(MajorThemes) %>%
summarise(Count = n())
ggplot(theme_counts, aes(x=reorder(MajorThemes, -Count), y=Count, fill=MajorThemes)) +
geom_bar(stat="identity", show.legend=FALSE) +
geom_text(aes(label=Count), vjust=-0.5) +
labs(title="Survey Major Themes", x="Theme", y="Number of Responses") +
theme_minimal() +
theme(axis.text.x = element_text(angle=45, hjust=1),
plot.title = element_text(hjust=0.5, face="bold"))
##Step 5: Analyze Comments (Word Frequency)
# Prepare comments for text analysis
comments <- data %>%
select(Commentsforimprovement) %>%
filter(!is.na(Commentsforimprovement)) %>%
unnest_tokens(word, Commentsforimprovement) %>%
anti_join(stop_words)## Joining with `by = join_by(word)`# Count top words
top_words <- comments %>%
count(word, sort = TRUE) %>%
top_n(50, n)
# Bar plot
top_words_plot <- ggplot(top_words, aes(x = reorder(word, n), y = n, fill = n)) +
geom_col(show.legend = FALSE) +
coord_flip() +
labs(title = "Most Frequent Words in Comments", x = "Word", y = "Count") +
theme_minimal() +
theme(plot.title = element_text(hjust = 0.5, face = "bold"))
top_words_plot
# Optional: Wordcloud
set.seed(123)
wordcloud(words = top_words$word, freq = top_words$n,
max.words = 30, colors = brewer.pal(8, "Dark2"))
##Step 6: Gap Analysis
survey_gaps <- data_summary %>%
mutate(
Gap_Friendliness = 5 - Avg_Friendliness,
Gap_Knowledge = 5 - Avg_Knowledge,
Gap_Quickness = 5 - Avg_Quickness
)
# Convert to long format
survey_gaps_long <- survey_gaps %>%
pivot_longer(cols = starts_with("Gap"),
names_to = "Metric",
values_to = "Gap")
# Gap plot
gap_plot <- ggplot(survey_gaps_long, aes(x = Metric, y = Gap, fill = Metric)) +
geom_bar(stat = "identity", show.legend = FALSE, width = 0.6) +
geom_text(aes(label = round(Gap,2)), vjust=-0.5, size=5) +
labs(title = "Customer Service Metric Gaps", x="Metric", y="Gap from 5") +
ylim(0,5) +
theme_minimal(base_size = 14) +
theme(plot.title = element_text(hjust=0.5, face="bold"))
gap_plot
##Step 7: Top Concerns from Comments
# Filter key concerns
top_concerns <- top_words %>%
filter(word %in% c("delivery", "time", "slow", "product", "communication"))
# Plot top concerns
top_concerns_plot <- ggplot(top_concerns, aes(x=reorder(word, n), y=n, fill=n)) +
geom_col(show.legend=FALSE) +
coord_flip() +
labs(title="Top Customer Concerns", x="Word", y="Frequency") +
theme_minimal(base_size=14) +
theme(plot.title = element_text(hjust=0.5, face="bold"))
top_concerns_plot
##Step 8: Dashboard View
# Arrange all three plots
grid.arrange(avg_metrics_plot, gap_plot, top_concerns_plot, ncol=1)
##Step 9: Recommendations
recommendations <- data.frame(
Metric = c("Quickness", "Communication", "Delivery"),
Recommendation = c(
"Implement faster order processing and shipping procedures.",
"Improve clarity and timeliness of customer communication.",
"Optimize delivery schedules and track late shipments."
)
)
library(knitr)
kable(recommendations, caption = "Key Recommendations for XCaliber Foods")| Metric | Recommendation |
|---|---|
| Quickness | Implement faster order processing and shipping procedures. |
| Communication | Improve clarity and timeliness of customer communication. |
| Delivery | Optimize delivery schedules and track late shipments. |