Final Homework Bike Sales Analysis

library(readxl)
library(dplyr)
library(lubridate)
library(ggplot2)
library(scales)
library(RColorBrewer)

# Load the data
bike_orderlines_1_ <- read_excel("~/Bike/bike_orderlines (1).xlsx")

# Convert order_date to Date format
bike_orderlines_1_ <- bike_orderlines_1_ %>%
  mutate(order_date = as.Date(order_date))

# Create time period variables
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")
  )

Total Sales Charts

Quarterly Sales Trends

# Aggregate by quarter
quarterly_sales <- bike_orderlines_1_ %>%
  group_by(year_quarter) %>%
  summarise(revenue = sum(total_price)) %>%
  arrange(year_quarter)

# Plot quarterly sales
ggplot(quarterly_sales, aes(x = year_quarter, y = revenue)) +
  geom_line(color = "#10B981", size = 1.2) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Total Sales", x = "", y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    panel.grid.minor = element_blank(),
    panel.background = element_rect(fill = "white", color = NA),
    plot.background = element_rect(fill = "white", color = NA)
  )

Monthly Sales Trends

# Aggregate by month
monthly_sales <- bike_orderlines_1_ %>%
  group_by(year_month) %>%
  summarise(revenue = sum(total_price)) %>%
  arrange(year_month)

# Plot monthly sales with smoothing
ggplot(monthly_sales, aes(x = year_month, y = revenue)) +
  geom_point(size = 1.5, color = "#059669", alpha = 0.6) +
  geom_smooth(method = "loess", se = TRUE, color = "#10B981", fill = "#6EE7B7", alpha = 0.3, size = 1.2) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Total Sales", x = "", y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    panel.grid.minor = element_blank(),
    panel.background = element_rect(fill = "white", color = NA),
    plot.background = element_rect(fill = "white", color = NA)
  )

Weekly Sales Trends

# Aggregate by week
weekly_sales <- bike_orderlines_1_ %>%
  group_by(year_week) %>%
  summarise(revenue = sum(total_price)) %>%
  arrange(year_week)

# Plot weekly sales with smoothing
ggplot(weekly_sales, aes(x = year_week, y = revenue)) +
  geom_point(size = 1, alpha = 0.4, color = "#059669") +
  geom_smooth(method = "loess", se = TRUE, color = "#10B981", fill = "#6EE7B7", alpha = 0.3, span = 0.15, size = 1.2) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Total Sales", x = "", y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    panel.grid.minor = element_blank(),
    panel.background = element_rect(fill = "white", color = NA),
    plot.background = element_rect(fill = "white", color = NA)
  )

Category Sales Charts

Road

Quarterly

# Filter for Road bikes and aggregate by quarter and category_2
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)

# Define categories order
road_quarterly <- road_quarterly %>%
  mutate(category_2 = factor(category_2, 
                              levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")))

# Create green color palette
road_colors <- brewer.pal(4, "Greens")

# Plot with facets
ggplot(road_quarterly, aes(x = year_quarter, y = revenue, color = category_2)) +
  geom_line(size = 1.2) +
  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 = "Sales By Category 2", x = "", y = "") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 14),
    plot.background = element_rect(fill = "#3d5268", color = NA),
    panel.background = element_rect(fill = "white", color = NA),
    strip.background = element_rect(fill = "#3d5268", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 12),
    panel.grid.minor = element_blank(),
    legend.position = "none",
    axis.text = element_text(color = "black")
  )

Monthly

# Filter for Road bikes and aggregate by month
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)

# Factor categories
road_monthly <- road_monthly %>%
  mutate(category_2 = factor(category_2, 
                              levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")))

# Plot with facets
ggplot(road_monthly, aes(x = year_month, y = revenue, color = category_2)) +
  geom_point(size = 1, alpha = 0.5) +
  geom_smooth(method = "loess", se = TRUE, fill = "#6EE7B7", alpha = 0.3, size = 1.2) +
  scale_color_manual(values = road_colors) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  labs(title = "Sales By Category 2", x = "", y = "") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 14),
    plot.background = element_rect(fill = "#3d5268", color = NA),
    panel.background = element_rect(fill = "white", color = NA),
    strip.background = element_rect(fill = "#3d5268", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 12),
    panel.grid.minor = element_blank(),
    legend.position = "none",
    axis.text = element_text(color = "black")
  )

Weekly

# Filter for Road bikes and aggregate by week
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)

# Factor categories
road_weekly <- road_weekly %>%
  mutate(category_2 = factor(category_2, 
                              levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")))

# Plot with facets
ggplot(road_weekly, aes(x = year_week, y = revenue, color = category_2)) +
  geom_point(size = 0.8, alpha = 0.4) +
  geom_smooth(method = "loess", se = FALSE, span = 0.15, size = 1.2) +
  scale_color_manual(values = road_colors) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  labs(title = "Sales By Category 2", x = "", y = "") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 14),
    plot.background = element_rect(fill = "#3d5268", color = NA),
    panel.background = element_rect(fill = "white", color = NA),
    strip.background = element_rect(fill = "#3d5268", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 12),
    panel.grid.minor = element_blank(),
    legend.position = "none",
    axis.text = element_text(color = "black")
  )

Mountain

Quarterly

# Filter for Mountain bikes and aggregate by quarter
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)

# Define mountain categories
mountain_quarterly <- mountain_quarterly %>%
  mutate(category_2 = factor(category_2, 
                              levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")))

# Create green color palette
mountain_colors <- brewer.pal(5, "Greens")

# Plot with facets
ggplot(mountain_quarterly, aes(x = year_quarter, y = revenue, color = category_2)) +
  geom_line(size = 1.2) +
  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 = "Sales By Category 2", x = "", y = "") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 14),
    plot.background = element_rect(fill = "#3d5268", color = NA),
    panel.background = element_rect(fill = "white", color = NA),
    strip.background = element_rect(fill = "#3d5268", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 12),
    panel.grid.minor = element_blank(),
    legend.position = "none",
    axis.text = element_text(color = "black")
  )

Monthly

# Filter for Mountain bikes and aggregate by month
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)

# Factor categories
mountain_monthly <- mountain_monthly %>%
  mutate(category_2 = factor(category_2, 
                              levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")))

# Plot with facets
ggplot(mountain_monthly, aes(x = year_month, y = revenue, color = category_2)) +
  geom_point(size = 1, alpha = 0.5) +
  geom_smooth(method = "loess", se = TRUE, fill = "#6EE7B7", alpha = 0.3, size = 1.2) +
  scale_color_manual(values = mountain_colors) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  labs(title = "Sales By Category 2", x = "", y = "") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 14),
    plot.background = element_rect(fill = "#3d5268", color = NA),
    panel.background = element_rect(fill = "white", color = NA),
    strip.background = element_rect(fill = "#3d5268", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 12),
    panel.grid.minor = element_blank(),
    legend.position = "none",
    axis.text = element_text(color = "black")
  )

Weekly

# Filter for Mountain bikes and aggregate by week
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)

# Factor categories
mountain_weekly <- mountain_weekly %>%
  mutate(category_2 = factor(category_2, 
                              levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")))

# Plot with facets
ggplot(mountain_weekly, aes(x = year_week, y = revenue, color = category_2)) +
  geom_point(size = 0.8, alpha = 0.4) +
  geom_smooth(method = "loess", se = FALSE, span = 0.15, size = 1.2) +
  scale_color_manual(values = mountain_colors) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  labs(title = "Sales By Category 2", x = "", y = "") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", color = "white", size = 14),
    plot.background = element_rect(fill = "#3d5268", color = NA),
    panel.background = element_rect(fill = "white", color = NA),
    strip.background = element_rect(fill = "#3d5268", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 12),
    panel.grid.minor = element_blank(),
    legend.position = "none",
    axis.text = element_text(color = "black")
  )