Final Homework - Bike Sales Analysis

# Read the data
bike_data <- read_csv("bike_orderlines.csv")

# Convert order_date to Date type
bike_data <- bike_data %>%
  mutate(order_date = as.Date(order_date))

Total Sales Charts

Quarterly Sales Trends

# Aggregate to quarterly
quarterly_data <- bike_data %>%
  mutate(quarter = as.yearqtr(order_date)) %>%
  group_by(quarter) %>%
  summarise(revenue = sum(total_price)) %>%
  mutate(date = as.Date(quarter))

# Plot
ggplot(quarterly_data, aes(x = date, y = revenue)) +
  geom_line(color = "#2E5FA3", size = 1.2) +
  geom_point(color = "#2E5FA3", size = 2) +
  scale_y_continuous(labels = dollar_format(), 
                     breaks = seq(0, 8000000, 2000000),
                     limits = c(0, 8000000)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Total Sales",
       x = NULL,
       y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray90")
  )

Monthly Sales Trends

# Aggregate to monthly
monthly_data <- bike_data %>%
  mutate(month = floor_date(order_date, "month")) %>%
  group_by(month) %>%
  summarise(revenue = sum(total_price))

# Calculate moving average
monthly_data <- monthly_data %>%
  arrange(month) %>%
  mutate(ma = rollmean(revenue, k = 3, fill = NA, align = "center"))

# Plot
ggplot(monthly_data, aes(x = month)) +
  geom_ribbon(aes(ymin = revenue * 0.7, ymax = revenue * 1.3), 
              fill = "gray80", alpha = 0.5) +
  geom_point(aes(y = revenue), size = 1.5) +
  geom_line(aes(y = ma), color = "#2E5FA3", size = 1.2) +
  scale_y_continuous(labels = dollar_format(),
                     breaks = seq(0, 2500000, 500000),
                     limits = c(0, 2500000)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Total Sales",
       x = NULL,
       y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray90")
  )

Weekly Sales Trends

# Aggregate to weekly
weekly_data <- bike_data %>%
  mutate(week = floor_date(order_date, "week")) %>%
  group_by(week) %>%
  summarise(revenue = sum(total_price))

# Calculate moving average
weekly_data <- weekly_data %>%
  arrange(week) %>%
  mutate(ma = rollmean(revenue, k = 8, fill = NA, align = "center"))

# Plot
ggplot(weekly_data, aes(x = week)) +
  geom_ribbon(aes(ymin = revenue * 0.5, ymax = revenue * 1.5), 
              fill = "gray80", alpha = 0.5) +
  geom_point(aes(y = revenue), size = 0.8, alpha = 0.6) +
  geom_line(aes(y = ma), color = "#2E5FA3", size = 1.2) +
  scale_y_continuous(labels = dollar_format(),
                     breaks = seq(0, 800000, 200000),
                     limits = c(0, 850000)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Total Sales",
       x = NULL,
       y = "Revenue (USD)") +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray90")
  )

Category Sales Charts

Road

Quarterly

# Filter for Road bikes
road_data <- bike_data %>%
  filter(category_1 == "Road")

# Define categories and colors
road_categories <- c("Elite Road", "Endurance Road", "Triathlon", "Cyclocross")
road_colors <- c("#2E4057", "#C62828", "#00ACC1", "#9E9D24")

# Quarterly aggregation
road_quarterly <- road_data %>%
  mutate(quarter = as.yearqtr(order_date)) %>%
  group_by(quarter, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(date = as.Date(quarter))

# Create plots for each category
for (i in 1:length(road_categories)) {
  cat_data <- road_quarterly %>%
    filter(category_2 == road_categories[i])
  
  p <- ggplot(cat_data, aes(x = date, y = revenue)) +
    geom_line(color = road_colors[i], size = 1.2) +
    geom_point(color = road_colors[i], size = 2) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = road_categories[i], x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12),
      plot.background = element_rect(fill = "white"),
      panel.background = element_rect(fill = "white"),
      panel.grid = element_line(color = "gray90"),
      axis.text = element_text(color = "black"),
      axis.title = element_text(color = "black")
    )
  
  print(p)
}

Monthly

# Monthly aggregation
road_monthly <- road_data %>%
  mutate(month = floor_date(order_date, "month")) %>%
  group_by(month, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop")

# Add moving average for each category
road_monthly <- road_monthly %>%
  group_by(category_2) %>%
  arrange(month) %>%
  mutate(ma = rollmean(revenue, k = 3, fill = NA, align = "center")) %>%
  ungroup()

# Create plots for each category
for (i in 1:length(road_categories)) {
  cat_data <- road_monthly %>%
    filter(category_2 == road_categories[i])
  
  p <- ggplot(cat_data, aes(x = month)) +
    geom_ribbon(aes(ymin = revenue * 0.7, ymax = revenue * 1.3), 
                fill = "gray80", alpha = 0.5) +
    geom_point(aes(y = revenue), size = 1.5) +
    geom_line(aes(y = ma), color = road_colors[i], size = 1.2) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = road_categories[i], x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12),
      plot.background = element_rect(fill = "white"),
      panel.background = element_rect(fill = "white"),
      panel.grid = element_line(color = "gray90"),
      axis.text = element_text(color = "black"),
      axis.title = element_text(color = "black")
    )
  
  print(p)
}

Weekly

# Weekly aggregation
road_weekly <- road_data %>%
  mutate(week = floor_date(order_date, "week")) %>%
  group_by(week, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop")

# Add moving average for each category
road_weekly <- road_weekly %>%
  group_by(category_2) %>%
  arrange(week) %>%
  mutate(ma = rollmean(revenue, k = 8, fill = NA, align = "center")) %>%
  ungroup()

# Create plots for each category
for (i in 1:length(road_categories)) {
  cat_data <- road_weekly %>%
    filter(category_2 == road_categories[i])
  
  p <- ggplot(cat_data, aes(x = week)) +
    geom_point(aes(y = revenue), size = 0.8, alpha = 0.6) +
    geom_line(aes(y = ma), color = road_colors[i], size = 1.2) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = road_categories[i], x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12),
      plot.background = element_rect(fill = "white"),
      panel.background = element_rect(fill = "white"),
      panel.grid = element_line(color = "gray90"),
      axis.text = element_text(color = "black"),
      axis.title = element_text(color = "black")
    )
  
  print(p)
}

Mountain

Quarterly

# Filter for Mountain bikes
mountain_data <- bike_data %>%
  filter(category_1 == "Mountain")

# Define categories and colors
mountain_categories <- c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")
mountain_colors <- c("#2E4057", "#C62828", "#00ACC1", "#9E9D24", "#1E88E5")

# Quarterly aggregation
mountain_quarterly <- mountain_data %>%
  mutate(quarter = as.yearqtr(order_date)) %>%
  group_by(quarter, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(date = as.Date(quarter))

# Create plots for each category
for (i in 1:length(mountain_categories)) {
  cat_data <- mountain_quarterly %>%
    filter(category_2 == mountain_categories[i])
  
  p <- ggplot(cat_data, aes(x = date, y = revenue)) +
    geom_line(color = mountain_colors[i], size = 1.2) +
    geom_point(color = mountain_colors[i], size = 2) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = mountain_categories[i], x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12),
      plot.background = element_rect(fill = "white"),
      panel.background = element_rect(fill = "white"),
      panel.grid = element_line(color = "gray90"),
      axis.text = element_text(color = "black"),
      axis.title = element_text(color = "black")
    )
  
  print(p)
}

Monthly

# Monthly aggregation
mountain_monthly <- mountain_data %>%
  mutate(month = floor_date(order_date, "month")) %>%
  group_by(month, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop")

# Add moving average for each category
mountain_monthly <- mountain_monthly %>%
  group_by(category_2) %>%
  arrange(month) %>%
  mutate(ma = rollmean(revenue, k = 3, fill = NA, align = "center")) %>%
  ungroup()

# Create plots for each category
for (i in 1:length(mountain_categories)) {
  cat_data <- mountain_monthly %>%
    filter(category_2 == mountain_categories[i])
  
  p <- ggplot(cat_data, aes(x = month)) +
    geom_ribbon(aes(ymin = revenue * 0.7, ymax = revenue * 1.3), 
                fill = "gray80", alpha = 0.5) +
    geom_point(aes(y = revenue), size = 1.5) +
    geom_line(aes(y = ma), color = mountain_colors[i], size = 1.2) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = mountain_categories[i], x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12),
      plot.background = element_rect(fill = "white"),
      panel.background = element_rect(fill = "white"),
      panel.grid = element_line(color = "gray90"),
      axis.text = element_text(color = "black"),
      axis.title = element_text(color = "black")
    )
  
  print(p)
}

Weekly

# Weekly aggregation
mountain_weekly <- mountain_data %>%
  mutate(week = floor_date(order_date, "week")) %>%
  group_by(week, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop")

# Add moving average for each category
mountain_weekly <- mountain_weekly %>%
  group_by(category_2) %>%
  arrange(week) %>%
  mutate(ma = rollmean(revenue, k = 8, fill = NA, align = "center")) %>%
  ungroup()

# Create plots for each category
for (i in 1:length(mountain_categories)) {
  cat_data <- mountain_weekly %>%
    filter(category_2 == mountain_categories[i])
  
  p <- ggplot(cat_data, aes(x = week)) +
    geom_point(aes(y = revenue), size = 0.8, alpha = 0.6) +
    geom_line(aes(y = ma), color = mountain_colors[i], size = 1.2) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = mountain_categories[i], x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12),
      plot.background = element_rect(fill = "white"),
      panel.background = element_rect(fill = "white"),
      panel.grid = element_line(color = "gray90"),
      axis.text = element_text(color = "black"),
      axis.title = element_text(color = "black")
    )
  
  print(p)
}