# 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))

Executive Summary

This analysis examines bike sales data across different time periods (quarterly, monthly, and weekly) for two main categories: Road bikes and Mountain bikes. The dataset includes multiple subcategories within each main category, allowing for detailed trend analysis.

Key Findings:

Total Sales Charts

Category Sales Charts

Road Bikes

Road bikes represent a significant portion of our sales portfolio, with four distinct subcategories serving different customer segments and riding styles.

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("#8B4513", "#D2691E", "#CD853F", "#BC8F8F")

# 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.5) +
    geom_point(color = road_colors[i], size = 3) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = paste(road_categories[i], "- Quarterly Sales"), 
         x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12, face = "bold", color = "#654321"),
      plot.background = element_rect(fill = "#F5F5DC"),
      panel.background = element_rect(fill = "#F5F5DC"),
      panel.grid = element_line(color = "#D3D3D3"),
      axis.text = element_text(color = "#654321"),
      axis.title = element_text(color = "#654321")
    )
  
  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 = "#DEB887", alpha = 0.4) +
    geom_point(aes(y = revenue), size = 2, color = road_colors[i]) +
    geom_line(aes(y = ma), color = road_colors[i], size = 1.5) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = paste(road_categories[i], "- Monthly Sales"), 
         x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12, face = "bold", color = "#654321"),
      plot.background = element_rect(fill = "#F5F5DC"),
      panel.background = element_rect(fill = "#F5F5DC"),
      panel.grid = element_line(color = "#D3D3D3"),
      axis.text = element_text(color = "#654321"),
      axis.title = element_text(color = "#654321")
    )
  
  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 = 1, alpha = 0.5, color = road_colors[i]) +
    geom_line(aes(y = ma), color = road_colors[i], size = 1.5) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = paste(road_categories[i], "- Weekly Sales"), 
         x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12, face = "bold", color = "#654321"),
      plot.background = element_rect(fill = "#F5F5DC"),
      panel.background = element_rect(fill = "#F5F5DC"),
      panel.grid = element_line(color = "#D3D3D3"),
      axis.text = element_text(color = "#654321"),
      axis.title = element_text(color = "#654321")
    )
  
  print(p)
}

Mountain Bikes

Mountain bikes represent our diverse off-road cycling segment, with five specialized subcategories designed for different terrain and riding preferences.

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("#8B4513", "#D2691E", "#CD853F", "#BC8F8F", "#A0522D")

# 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.5) +
    geom_point(color = mountain_colors[i], size = 3) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = paste(mountain_categories[i], "- Quarterly Sales"), 
         x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12, face = "bold", color = "#654321"),
      plot.background = element_rect(fill = "#F5F5DC"),
      panel.background = element_rect(fill = "#F5F5DC"),
      panel.grid = element_line(color = "#D3D3D3"),
      axis.text = element_text(color = "#654321"),
      axis.title = element_text(color = "#654321")
    )
  
  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 = "#DEB887", alpha = 0.4) +
    geom_point(aes(y = revenue), size = 2, color = mountain_colors[i]) +
    geom_line(aes(y = ma), color = mountain_colors[i], size = 1.5) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = paste(mountain_categories[i], "- Monthly Sales"), 
         x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12, face = "bold", color = "#654321"),
      plot.background = element_rect(fill = "#F5F5DC"),
      panel.background = element_rect(fill = "#F5F5DC"),
      panel.grid = element_line(color = "#D3D3D3"),
      axis.text = element_text(color = "#654321"),
      axis.title = element_text(color = "#654321")
    )
  
  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 = 1, alpha = 0.5, color = mountain_colors[i]) +
    geom_line(aes(y = ma), color = mountain_colors[i], size = 1.5) +
    scale_y_continuous(labels = dollar_format()) +
    scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
    labs(title = paste(mountain_categories[i], "- Weekly Sales"), 
         x = NULL, y = "Revenue (USD)") +
    theme_minimal() +
    theme(
      plot.title = element_text(hjust = 0.5, size = 12, face = "bold", color = "#654321"),
      plot.background = element_rect(fill = "#F5F5DC"),
      panel.background = element_rect(fill = "#F5F5DC"),
      panel.grid = element_line(color = "#D3D3D3"),
      axis.text = element_text(color = "#654321"),
      axis.title = element_text(color = "#654321")
    )
  
  print(p)
}

Conclusion

This comprehensive analysis provides insights into bike sales patterns across different timeframes and categories. The visualizations reveal important trends that can inform inventory management, marketing strategies, and business planning decisions.