Final Homework - Bike Sales Analysis

library(ggplot2)
library(dplyr)
library(lubridate)
library(readr)
library(scales)

# Read the CSV file
bike_data <- read_csv("bike_orderlines.csv")
bike_data$order_date <- as.Date(bike_data$order_date)

# Weekly total sales
weekly_total <- bike_data %>%
  mutate(week = floor_date(order_date, "week")) %>%
  group_by(week) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Monthly total sales
monthly_total <- bike_data %>%
  mutate(month = floor_date(order_date, "month")) %>%
  group_by(month) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Quarterly total sales
quarterly_total <- bike_data %>%
  mutate(quarter = floor_date(order_date, "quarter")) %>%
  group_by(quarter) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Weekly by category
weekly_cat <- bike_data %>%
  mutate(week = floor_date(order_date, "week")) %>%
  group_by(week, category_2) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Monthly by category
monthly_cat <- bike_data %>%
  mutate(month = floor_date(order_date, "month")) %>%
  group_by(month, category_2) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Quarterly by category
quarterly_cat <- bike_data %>%
  mutate(quarter = floor_date(order_date, "quarter")) %>%
  group_by(quarter, category_2) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Weekly by category_1 (Road/Mountain)
weekly_cat1 <- bike_data %>%
  filter(!is.na(category_1)) %>%
  mutate(week = floor_date(order_date, "week")) %>%
  group_by(week, category_1) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Monthly by category_1
monthly_cat1 <- bike_data %>%
  filter(!is.na(category_1)) %>%
  mutate(month = floor_date(order_date, "month")) %>%
  group_by(month, category_1) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

# Quarterly by category_1
quarterly_cat1 <- bike_data %>%
  filter(!is.na(category_1)) %>%
  mutate(quarter = floor_date(order_date, "quarter")) %>%
  group_by(quarter, category_1) %>%
  summarise(Sales = sum(total_price), .groups = "drop")

Total Sales Charts

Quarterly Sales Trends

ggplot(quarterly_total, aes(x = quarter, y = Sales)) +
  geom_point(size = 2.5, alpha = 0.7) +
  geom_smooth(method = "loess", se = TRUE, color = "blue", fill = "lightblue", alpha = 0.3, span = 0.4) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Quarterly Sales Trends\nTotal Sales", x = NULL, y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    panel.grid.minor = element_blank()
  )

Monthly Sales Trends

ggplot(monthly_total, aes(x = month, y = Sales)) +
  geom_point(size = 2, alpha = 0.6) +
  geom_smooth(method = "loess", se = TRUE, color = "blue", fill = "lightblue", alpha = 0.3, span = 0.3) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Monthly Sales Trends\nTotal Sales", x = NULL, y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    panel.grid.minor = element_blank()
  )

Weekly Sales Trends

ggplot(weekly_total, aes(x = week, y = Sales)) +
  geom_point(size = 1.5, alpha = 0.5) +
  geom_smooth(method = "loess", se = TRUE, color = "blue", fill = "lightblue", alpha = 0.3, span = 0.2) +
  scale_y_continuous(labels = dollar_format(), limits = c(0, NA)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Weekly Sales Trends\nTotal Sales", x = NULL, y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    panel.grid.minor = element_blank()
  )

Category Sales Charts (Road)

Road - Quarterly

road_cats_q <- quarterly_cat %>% 
  filter(category_2 %in% c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross"))

cat_colors <- c("Elite Road" = "gray30", "Endurance Road" = "red", 
                "Triathalon" = "cyan3", "Cyclocross" = "tan")

ggplot(road_cats_q, aes(x = quarter, y = Sales, color = category_2, fill = category_2)) +
  geom_point(size = 3, alpha = 0.8) +
  geom_smooth(method = "loess", se = FALSE, size = 1.5, span = 0.5) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_color_manual(values = cat_colors) +
  scale_fill_manual(values = cat_colors) +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Category Sales Charts\nRoad\nQuarterly", subtitle = "Sales By Category 2", x = NULL, y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    strip.background = element_rect(fill = "darkslategray", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 10),
    legend.position = "none",
    panel.grid.minor = element_blank()
  )

Road - Monthly

road_cats_m <- monthly_cat %>% 
  filter(category_2 %in% c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross"))

ggplot(road_cats_m, aes(x = month, y = Sales, color = category_2, fill = category_2)) +
  geom_point(size = 2, alpha = 0.6) +
  geom_smooth(method = "loess", se = TRUE, alpha = 0.2, span = 0.3) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_color_manual(values = cat_colors) +
  scale_fill_manual(values = cat_colors) +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  labs(title = "Category Sales Charts\nRoad\nMonthly", subtitle = "Sales By Category 2", x = NULL, y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    strip.background = element_rect(fill = "darkslategray", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 10),
    legend.position = "none",
    panel.grid.minor = element_blank()
  )

Road - Weekly

road_cats_w <- weekly_cat %>% 
  filter(category_2 %in% c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross"))

ggplot(road_cats_w, aes(x = week, y = Sales, color = category_2, fill = category_2)) +
  geom_point(size = 1, alpha = 0.5) +
  geom_smooth(method = "loess", se = TRUE, alpha = 0.2, span = 0.2) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_color_manual(values = cat_colors) +
  scale_fill_manual(values = cat_colors) +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  labs(title = "Category Sales Charts\nRoad\nWeekly", subtitle = "Sales By Category 2", x = NULL, y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    strip.background = element_rect(fill = "darkslategray", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 10),
    legend.position = "none",
    panel.grid.minor = element_blank()
  )

Category Sales Charts (Mountain)

Mountain - Quarterly

mtn_cats_q <- quarterly_cat %>% 
  filter(category_2 %in% c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike"))

mtn_colors <- c("Cross Country Race" = "darkslategray", "Trail" = "turquoise", 
                "Over Mountain" = "turquoise", "Sport" = "tan", "Fat Bike" = "lightblue")

ggplot(mtn_cats_q, aes(x = quarter, y = Sales, color = category_2, fill = category_2)) +
  geom_point(size = 3, alpha = 0.8) +
  geom_smooth(method = "loess", se = FALSE, size = 1.5, span = 0.5) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_color_manual(values = mtn_colors) +
  scale_fill_manual(values = mtn_colors) +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Category Sales Charts\nMountain\nQuarterly", subtitle = "Sales By Category 2", x = NULL, y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    strip.background = element_rect(fill = "darkslategray", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 10),
    legend.position = "none",
    panel.grid.minor = element_blank()
  )

Mountain - Monthly

mtn_cats_m <- monthly_cat %>% 
  filter(category_2 %in% c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike"))

ggplot(mtn_cats_m, aes(x = month, y = Sales, color = category_2, fill = category_2)) +
  geom_point(size = 2, alpha = 0.6) +
  geom_smooth(method = "loess", se = TRUE, alpha = 0.2, span = 0.3) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_color_manual(values = mtn_colors) +
  scale_fill_manual(values = mtn_colors) +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  labs(title = "Category Sales Charts\nMountain\nMonthly", subtitle = "Sales By Category 2", x = NULL, y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    strip.background = element_rect(fill = "darkslategray", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 10),
    legend.position = "none",
    panel.grid.minor = element_blank()
  )

Mountain - Weekly

mtn_cats_w <- weekly_cat %>% 
  filter(category_2 %in% c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike"))

ggplot(mtn_cats_w, aes(x = week, y = Sales, color = category_2, fill = category_2)) +
  geom_point(size = 1, alpha = 0.5) +
  geom_smooth(method = "loess", se = TRUE, alpha = 0.2, span = 0.2) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_color_manual(values = mtn_colors) +
  scale_fill_manual(values = mtn_colors) +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  scale_x_date(date_breaks = "2 years", date_labels = "%Y") +
  labs(title = "Category Sales Charts\nMountain\nWeekly", subtitle = "Sales By Category 2", x = NULL, y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    strip.background = element_rect(fill = "darkslategray", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 10),
    legend.position = "none",
    panel.grid.minor = element_blank()
  )

Summary

cat("Total Revenue:", dollar(sum(bike_data$total_price)), "\n")

## Total Revenue: $71,032,330

cat("Date Range:", format(min(bike_data$order_date), "%Y-%m-%d"), "to", 
    format(max(bike_data$order_date), "%Y-%m-%d"), "\n\n")

## Date Range: 2011-01-07 to 2015-12-25

bike_data %>%
  group_by(category_2) %>%
  summarise(Revenue = sum(total_price), .groups = "drop") %>%
  arrange(desc(Revenue)) %>%
  mutate(Revenue = dollar(Revenue)) %>%
  knitr::kable(caption = "Revenue by Category")

Revenue by Category

category_2	Revenue
Cross Country Race	$19,224,630
Elite Road	$15,334,665
Endurance Road	$10,381,060
Trail	$9,373,460
Over Mountain	$7,571,270
Triathalon	$4,053,750
Cyclocross	$2,108,120
Sport	$1,932,755
Fat Bike	$1,052,620