FDB Final Homework
Purevjav Odkhuu
2025-12-22
# Import essential data manipulation and visualization libraries
library(readxl)
library(dplyr)
library(lubridate)
library(ggplot2)
library(scales)
library(RColorBrewer)# Load bike sales dataset
bike_orderlines_1_ <- read_excel("bike_orderlines.xlsx")
# Transform date variables
bike_orderlines_1_ <- bike_orderlines_1_ %>%
mutate(order_date = as.Date(order_date))
# Create temporal aggregation features
bike_orderlines_1_ <- bike_orderlines_1_ %>%
mutate(
year = year(order_date),
quarter = quarter(order_date),
year_quarter = as.Date(paste0(year, "-", (quarter-1)*3 + 1, "-01")),
year_month = floor_date(order_date, "month"),
year_week = floor_date(order_date, "week")
)Part 1: Comprehensive Sales Overview
This section examines aggregate revenue patterns across different temporal granularities to understand business performance dynamics.
1.1 Quarterly Revenue Examination
Quarterly analysis reveals macro-level business cycles and long-term growth trajectories.
# Compute quarterly revenue aggregates
quarterly_sales <- bike_orderlines_1_ %>%
group_by(year_quarter) %>%
summarise(revenue = sum(total_price)) %>%
arrange(year_quarter)
# Generate quarterly visualization
ggplot(quarterly_sales, aes(x = year_quarter, y = revenue)) +
geom_line(color = "#059669", size = 2.2, lineend = "round") +
geom_point(color = "#047857", size = 4.5, alpha = 0.8) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA),
breaks = seq(0, 8000000, 1000000)) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(
title = "Quarterly Revenue Performance Metrics (2011-2015)",
subtitle = "Macro-level sales trajectory analysis",
x = "Fiscal Year",
y = "Aggregate Revenue (USD)"
) +
theme_minimal(base_size = 15) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", size = 19, color = "#1F2937"),
plot.subtitle = element_text(hjust = 0.5, size = 13, color = "#4B5563"),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#E5E7EB", size = 0.5),
panel.background = element_rect(fill = "#E0F2FE", color = NA),
plot.background = element_rect(fill = "#E0F2FE", color = NA),
axis.title = element_text(face = "bold", size = 13),
axis.text = element_text(size = 11)
)
1.2 Monthly Revenue Distribution
Monthly granularity provides detailed insights into seasonal demand patterns and mid-term fluctuations.
# Generate monthly revenue summaries
monthly_sales <- bike_orderlines_1_ %>%
group_by(year_month) %>%
summarise(revenue = sum(total_price)) %>%
arrange(year_month)
# Visualize monthly trends with smoothing
ggplot(monthly_sales, aes(x = year_month, y = revenue)) +
geom_point(size = 3, color = "#0891B2", alpha = 0.7) +
geom_smooth(method = "loess", se = TRUE, color = "#059669",
fill = "#BAE6FD", alpha = 0.45, size = 2) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") +
labs(
title = "Monthly Revenue Trends and Forecasting Model",
subtitle = "Seasonality patterns with statistical smoothing",
x = "Calendar Month",
y = "Monthly Revenue (USD)"
) +
theme_minimal(base_size = 15) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", size = 19, color = "#1F2937"),
plot.subtitle = element_text(hjust = 0.5, size = 13, color = "#4B5563"),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#E5E7EB", size = 0.5),
axis.text.x = element_text(angle = 45, hjust = 1, size = 11),
panel.background = element_rect(fill = "#E0F2FE", color = NA),
plot.background = element_rect(fill = "#E0F2FE", color = NA),
axis.title = element_text(face = "bold", size = 13)
)
1.3 Weekly Revenue Volatility
Weekly data captures high-frequency variations and helps identify operational anomalies.
# Calculate weekly revenue metrics
weekly_sales <- bike_orderlines_1_ %>%
group_by(year_week) %>%
summarise(revenue = sum(total_price)) %>%
arrange(year_week)
# Create high-resolution weekly chart
ggplot(weekly_sales, aes(x = year_week, y = revenue)) +
geom_point(size = 1.5, alpha = 0.5, color = "#0891B2") +
geom_smooth(method = "loess", se = TRUE, color = "#059669",
fill = "#BAE6FD", alpha = 0.45, span = 0.15, size = 1.8) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") +
labs(
title = "Weekly Sales Volatility and Trend Analysis",
subtitle = "High-frequency revenue data revealing short-term dynamics",
x = "Week Period",
y = "Weekly Revenue (USD)"
) +
theme_minimal(base_size = 15) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", size = 19, color = "#1F2937"),
plot.subtitle = element_text(hjust = 0.5, size = 13, color = "#4B5563"),
panel.grid.minor = element_blank(),
axis.text.x = element_text(angle = 45, hjust = 1, size = 11),
panel.background = element_rect(fill = "#E0F2FE", color = NA),
plot.background = element_rect(fill = "#E0F2FE", color = NA),
axis.title = element_text(face = "bold", size = 13)
)
Part 2: Segmented Category Analytics
Detailed performance breakdown by primary category and subcategory segments.
2.1 Road Bikes Performance Dashboard
Road bikes segment encompasses four specialized subcategories with distinct market characteristics.
Quarterly Subcategory Breakdown
# Process road bikes quarterly metrics
road_quarterly <- bike_orderlines_1_ %>%
filter(category_1 == "Road") %>%
group_by(year_quarter, category_2) %>%
summarise(revenue = sum(total_price), .groups = "drop") %>%
arrange(year_quarter) %>%
mutate(category_2 = factor(category_2,
levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")))
# Define orange color gradient palette
road_colors <- c("#EA580C", "#F97316", "#FB923C", "#FDBA74")
# Create comprehensive quarterly analysis
ggplot(road_quarterly, aes(x = year_quarter, y = revenue, color = category_2)) +
geom_line(size = 2, lineend = "round") +
geom_point(size = 2.5, alpha = 0.8) +
scale_color_manual(values = road_colors) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
labs(
title = "Road Bikes - Quarterly Performance by Subcategory",
subtitle = "Detailed revenue analysis across Elite, Endurance, Triathalon, and Cyclocross segments",
x = "Fiscal Year",
y = "Category Revenue (USD)"
) +
theme_minimal(base_size = 13) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 17),
plot.subtitle = element_text(hjust = 0.5, color = "white", size = 12),
plot.background = element_rect(fill = "#0F172A", color = NA),
panel.background = element_rect(fill = "#F1F5F9", color = NA),
strip.background = element_rect(fill = "#1E293B", color = NA),
strip.text = element_text(color = "white", face = "bold", size = 13),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#CBD5E1", size = 0.4),
legend.position = "none",
axis.text = element_text(color = "black", size = 11),
axis.title = element_text(face = "bold", size = 12)
)
Monthly Trend Evolution
# Generate road bikes monthly dataset
road_monthly <- bike_orderlines_1_ %>%
filter(category_1 == "Road") %>%
group_by(year_month, category_2) %>%
summarise(revenue = sum(total_price), .groups = "drop") %>%
arrange(year_month) %>%
mutate(category_2 = factor(category_2,
levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")))
# Visualize monthly patterns
ggplot(road_monthly, aes(x = year_month, y = revenue, color = category_2)) +
geom_point(size = 1.5, alpha = 0.6) +
geom_smooth(method = "loess", se = TRUE, fill = "#FDBA74", alpha = 0.35, size = 1.7) +
scale_color_manual(values = road_colors) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
labs(
title = "Road Bikes - Monthly Revenue Trends with Statistical Smoothing",
subtitle = "Seasonal patterns and demand fluctuations across subcategories",
x = "Calendar Month",
y = "Monthly Revenue (USD)"
) +
theme_minimal(base_size = 13) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 17),
plot.subtitle = element_text(hjust = 0.5, color = "white", size = 12),
plot.background = element_rect(fill = "#0F172A", color = NA),
panel.background = element_rect(fill = "#F1F5F9", color = NA),
strip.background = element_rect(fill = "#1E293B", color = NA),
strip.text = element_text(color = "white", face = "bold", size = 13),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#CBD5E1", size = 0.4),
legend.position = "none",
axis.text = element_text(color = "black", size = 11),
axis.title = element_text(face = "bold", size = 12)
)
Weekly Fluctuation Patterns
# Prepare road bikes weekly analysis
road_weekly <- bike_orderlines_1_ %>%
filter(category_1 == "Road") %>%
group_by(year_week, category_2) %>%
summarise(revenue = sum(total_price), .groups = "drop") %>%
arrange(year_week) %>%
mutate(category_2 = factor(category_2,
levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")))
# Generate high-frequency analysis
ggplot(road_weekly, aes(x = year_week, y = revenue, color = category_2)) +
geom_point(size = 1.1, alpha = 0.5) +
geom_smooth(method = "loess", se = FALSE, span = 0.15, size = 1.7) +
scale_color_manual(values = road_colors) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
labs(
title = "Road Bikes - Weekly Sales Volatility Dashboard",
subtitle = "High-resolution data revealing micro-trends and anomalies",
x = "Week Period",
y = "Weekly Revenue (USD)"
) +
theme_minimal(base_size = 13) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 17),
plot.subtitle = element_text(hjust = 0.5, color = "white", size = 12),
plot.background = element_rect(fill = "#0F172A", color = NA),
panel.background = element_rect(fill = "#F1F5F9", color = NA),
strip.background = element_rect(fill = "#1E293B", color = NA),
strip.text = element_text(color = "white", face = "bold", size = 13),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#CBD5E1", size = 0.4),
legend.position = "none",
axis.text = element_text(color = "black", size = 11),
axis.title = element_text(face = "bold", size = 12)
)
2.2 Mountain Bikes Performance Dashboard
Mountain bikes category features five specialized subcategories catering to diverse terrain requirements.
Quarterly Subcategory Analysis
# Calculate mountain bikes quarterly metrics
mountain_quarterly <- bike_orderlines_1_ %>%
filter(category_1 == "Mountain") %>%
group_by(year_quarter, category_2) %>%
summarise(revenue = sum(total_price), .groups = "drop") %>%
arrange(year_quarter) %>%
mutate(category_2 = factor(category_2,
levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")))
# Define pink/magenta color scheme
mountain_colors <- c("#BE185D", "#DB2777", "#EC4899", "#F472B6", "#FBCFE8")
# Create detailed quarterly visualization
ggplot(mountain_quarterly, aes(x = year_quarter, y = revenue, color = category_2)) +
geom_line(size = 2, lineend = "round") +
geom_point(size = 2.5, alpha = 0.8) +
scale_color_manual(values = mountain_colors) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
labs(
title = "Mountain Bikes - Quarterly Performance Breakdown",
subtitle = "Comprehensive revenue analysis for Cross Country, Trail, Over Mountain, Sport, and Fat Bike categories",
x = "Fiscal Year",
y = "Category Revenue (USD)"
) +
theme_minimal(base_size = 13) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 17),
plot.subtitle = element_text(hjust = 0.5, color = "white", size = 12),
plot.background = element_rect(fill = "#0F172A", color = NA),
panel.background = element_rect(fill = "#F1F5F9", color = NA),
strip.background = element_rect(fill = "#1E293B", color = NA),
strip.text = element_text(color = "white", face = "bold", size = 13),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#CBD5E1", size = 0.4),
legend.position = "none",
axis.text = element_text(color = "black", size = 11),
axis.title = element_text(face = "bold", size = 12)
)
Monthly Performance Tracking
# Process mountain bikes monthly data
mountain_monthly <- bike_orderlines_1_ %>%
filter(category_1 == "Mountain") %>%
group_by(year_month, category_2) %>%
summarise(revenue = sum(total_price), .groups = "drop") %>%
arrange(year_month) %>%
mutate(category_2 = factor(category_2,
levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")))
# Visualize monthly trends
ggplot(mountain_monthly, aes(x = year_month, y = revenue, color = category_2)) +
geom_point(size = 1.5, alpha = 0.6) +
geom_smooth(method = "loess", se = TRUE, fill = "#FBCFE8", alpha = 0.35, size = 1.7) +
scale_color_manual(values = mountain_colors) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
labs(
title = "Mountain Bikes - Monthly Demand Patterns with Forecasting",
subtitle = "Seasonal revenue trends and statistical smoothing across all segments",
x = "Calendar Month",
y = "Monthly Revenue (USD)"
) +
theme_minimal(base_size = 13) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 17),
plot.subtitle = element_text(hjust = 0.5, color = "white", size = 12),
plot.background = element_rect(fill = "#0F172A", color = NA),
panel.background = element_rect(fill = "#F1F5F9", color = NA),
strip.background = element_rect(fill = "#1E293B", color = NA),
strip.text = element_text(color = "white", face = "bold", size = 13),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#CBD5E1", size = 0.4),
legend.position = "none",
axis.text = element_text(color = "black", size = 11),
axis.title = element_text(face = "bold", size = 12)
)
Weekly Dynamics Exploration
# Generate mountain bikes weekly dataset
mountain_weekly <- bike_orderlines_1_ %>%
filter(category_1 == "Mountain") %>%
group_by(year_week, category_2) %>%
summarise(revenue = sum(total_price), .groups = "drop") %>%
arrange(year_week) %>%
mutate(category_2 = factor(category_2,
levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")))
# Create weekly volatility analysis
ggplot(mountain_weekly, aes(x = year_week, y = revenue, color = category_2)) +
geom_point(size = 1.1, alpha = 0.5) +
geom_smooth(method = "loess", se = FALSE, span = 0.15, size = 1.7) +
scale_color_manual(values = mountain_colors) +
scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
labs(
title = "Mountain Bikes - Weekly Revenue Volatility Metrics",
subtitle = "High-frequency analysis identifying short-term patterns and operational insights",
x = "Week Period",
y = "Weekly Revenue (USD)"
) +
theme_minimal(base_size = 13) +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 17),
plot.subtitle = element_text(hjust = 0.5, color = "white", size = 12),
plot.background = element_rect(fill = "#0F172A", color = NA),
panel.background = element_rect(fill = "#F1F5F9", color = NA),
strip.background = element_rect(fill = "#1E293B", color = NA),
strip.text = element_text(color = "white", face = "bold", size = 13),
panel.grid.minor = element_blank(),
panel.grid.major = element_line(color = "#CBD5E1", size = 0.4),
legend.position = "none",
axis.text = element_text(color = "black", size = 11),
axis.title = element_text(face = "bold", size = 12)
)
Analysis Period: 2011-2015 | Report Generated: 2025-12-22