Load Required Packages

# Data manipulation
library(readxl)
library(dplyr)
library(tidyr)
library(lubridate)

# Visualization
library(ggplot2)
library(scales)
library(RColorBrewer)
library(patchwork)

Data Import and Preparation

# Read Excel file 
bike_data <- read_excel("bike_orderlines.xlsx")

# Data preprocessing
bike_data <- bike_data %>%
  mutate(
    order_date = as.Date(order_date),
    year_val = year(order_date),
    quarter_val = quarter(order_date),
    month_val = month(order_date),
    qtr_date = floor_date(order_date, "quarter"),
    month_date = floor_date(order_date, "month"),
    week_date = floor_date(order_date, "week")
  )

# Display data structure
glimpse(bike_data)
## Rows: 15,644
## Columns: 19
## $ order_date     <date> 2011-01-07, 2011-01-07, 2011-01-10, 2011-01-10, 2011-0…
## $ order_id       <dbl> 1, 1, 2, 2, 3, 3, 3, 3, 3, 4, 5, 5, 5, 5, 6, 6, 6, 6, 7…
## $ order_line     <dbl> 1, 2, 1, 2, 1, 2, 3, 4, 5, 1, 1, 2, 3, 4, 1, 2, 3, 4, 1…
## $ quantity       <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1…
## $ price          <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570,…
## $ total_price    <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570,…
## $ model          <chr> "Jekyll Carbon 2", "Trigger Carbon 2", "Beast of the Ea…
## $ category_1     <chr> "Mountain", "Mountain", "Mountain", "Mountain", "Road",…
## $ category_2     <chr> "Over Mountain", "Over Mountain", "Trail", "Over Mounta…
## $ frame_material <chr> "Carbon", "Carbon", "Aluminum", "Carbon", "Carbon", "Ca…
## $ bikeshop_name  <chr> "Ithaca Mountain Climbers", "Ithaca Mountain Climbers",…
## $ city           <chr> "Ithaca", "Ithaca", "Kansas City", "Kansas City", "Loui…
## $ state          <chr> "NY", "NY", "KS", "KS", "KY", "KY", "KY", "KY", "KY", "…
## $ year_val       <dbl> 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2…
## $ quarter_val    <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ month_val      <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ qtr_date       <date> 2011-01-01, 2011-01-01, 2011-01-01, 2011-01-01, 2011-0…
## $ month_date     <date> 2011-01-01, 2011-01-01, 2011-01-01, 2011-01-01, 2011-0…
## $ week_date      <date> 2011-01-02, 2011-01-02, 2011-01-09, 2011-01-09, 2011-0…

Part 1: Overall Sales Performance

1.1 Quarterly Revenue Analysis

# Compute quarterly aggregates
qtr_summary <- bike_data %>%
  group_by(qtr_date) %>%
  summarise(
    total_revenue = sum(total_price),
    .groups = "drop"
  )

# Create visualization
ggplot(qtr_summary, aes(x = qtr_date, y = total_revenue)) +
  geom_line(color = "#1E3A8A", size = 1.8, lineend = "round") +
  geom_point(color = "#3B82F6", size = 3.5) +
  scale_y_continuous(
    labels = scales::dollar_format(),
    limits = c(0, NA),
    expand = expansion(mult = c(0, 0.1))
  ) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(
    title = "Quarterly Sales Performance",
    subtitle = "Revenue trends from 2011 to 2015",
    x = NULL,
    y = "Revenue (USD)"
  ) +
  theme_minimal(base_size = 13) +
  theme(
    plot.title = element_text(size = 16, face = "bold", hjust = 0.5),
    plot.subtitle = element_text(hjust = 0.5, color = "gray30"),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray80"),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA)
  )

1.3 Weekly Sales Distribution

# Weekly aggregation
week_summary <- bike_data %>%
  group_by(week_date) %>%
  summarise(total_revenue = sum(total_price), .groups = "drop")

# Plot weekly data - BRIGHT COLORS
ggplot(week_summary, aes(x = week_date, y = total_revenue)) +
  geom_point(alpha = 0.6, color = "#FF6B6B", size = 2) +  # Bright red points
  geom_smooth(
    method = "loess",
    se = TRUE,
    span = 0.2,
    color = "#4ECDC4",  # Bright teal line
    fill = "#95E1D3",
    alpha = 0.3,
    size = 1.5
  ) +
  scale_y_continuous(labels = dollar_format()) +
  scale_x_date(date_breaks = "6 months", date_labels = "%Y-%m") +
  labs(
    title = "Weekly Sales Dynamics",
    subtitle = "High-frequency revenue fluctuations",
    x = NULL,
    y = "Revenue (USD)"
  ) +
  theme_classic(base_size = 13) +
  theme(
    plot.title = element_text(size = 16, face = "bold", hjust = 0.5),
    plot.subtitle = element_text(hjust = 0.5, color = "gray30"),
    axis.text.x = element_text(angle = 45, hjust = 1),
    panel.grid.major = element_line(color = "gray80"),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA)
  )

Part 2: Category-Specific Analysis

2.1 Road Bikes - Quarterly Performance

# Prepare Road category data
road_qtr <- bike_data %>%
  filter(category_1 == "Road") %>%
  group_by(qtr_date, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(category_2 = factor(
    category_2,
    levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")
  ))

# Create bright color palette
bright_colors <- c("#FF6B6B", "#4ECDC4", "#45B7D1", "#FFA07A")

# Visualization
ggplot(road_qtr, aes(x = qtr_date, y = revenue, group = 1)) +
  geom_line(aes(color = category_2), size = 1.5, show.legend = FALSE) +
  scale_color_manual(values = bright_colors) +
  scale_y_continuous(labels = dollar_format()) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  facet_wrap(~category_2, ncol = 1, scales = "free_y") +
  labs(
    title = "Road Bikes - Quarterly Sales by Subcategory",
    x = NULL,
    y = "Revenue"
  ) +
  theme_bw(base_size = 12) +
  theme(
    plot.title = element_text(size = 15, face = "bold", hjust = 0.5),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA),
    strip.background = element_rect(fill = "#D1D5DB"),
    strip.text = element_text(color = "black", face = "bold", size = 11),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray80")
  )

2.2 Road Bikes - Monthly Trends

# Monthly road bike data
road_month <- bike_data %>%
  filter(category_1 == "Road") %>%
  group_by(month_date, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(category_2 = factor(
    category_2,
    levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")
  ))

# Create bright color palette for road bikes
road_bright_colors <- c(
  "Elite Road" = "#FF6B6B",      # Bright red
  "Endurance Road" = "#4ECDC4",  # Bright teal
  "Triathalon" = "#45B7D1",      # Bright blue
  "Cyclocross" = "#FFA07A"       # Bright coral
)

# Plot - BRIGHT COLORS AND VISIBLE POINTS
ggplot(road_month, aes(x = month_date, y = revenue)) +
  geom_point(aes(color = category_2), alpha = 0.7, size = 1.8, show.legend = FALSE) +
  geom_smooth(
    aes(color = category_2),
    method = "loess",
    se = FALSE,
    span = 0.2,
    size = 1.5,
    show.legend = FALSE
  ) +
  scale_color_manual(values = road_bright_colors) +
  scale_y_continuous(labels = dollar_format()) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  facet_wrap(~category_2, ncol = 1, scales = "free_y") +
  labs(
    title = "Road Bikes - Monthly Sales Patterns",
    x = NULL,
    y = "Revenue"
  ) +
  theme_bw(base_size = 12) +
  theme(
    plot.title = element_text(size = 15, face = "bold", hjust = 0.5),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA),
    strip.background = element_rect(fill = "#D1D5DB"),
    strip.text = element_text(color = "black", face = "bold", size = 11),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray80")
  )

2.3 Road Bikes - Weekly Fluctuations

# Weekly road bike data
road_week <- bike_data %>%
  filter(category_1 == "Road") %>%
  group_by(week_date, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(category_2 = factor(
    category_2,
    levels = c("Elite Road", "Endurance Road", "Triathalon", "Cyclocross")
  ))

# Create plot - BRIGHT COLORS
ggplot(road_week, aes(x = week_date, y = revenue)) +
  geom_point(aes(color = category_2), alpha = 0.6, size = 1.5, show.legend = FALSE) +
  geom_smooth(
    aes(color = category_2),
    method = "loess",
    se = FALSE,
    span = 0.2,
    size = 1.5,
    show.legend = FALSE
  ) +
  scale_color_manual(values = road_bright_colors) +
  scale_y_continuous(labels = dollar_format()) +
  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 Dynamics",
    x = NULL,
    y = "Revenue"
  ) +
  theme_bw(base_size = 12) +
  theme(
    plot.title = element_text(size = 15, face = "bold", hjust = 0.5),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA),
    strip.background = element_rect(fill = "#D1D5DB"),
    strip.text = element_text(color = "black", face = "bold", size = 11),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray80")
  )

2.4 Mountain Bikes - Quarterly Performance

# Mountain bike quarterly data
mountain_qtr <- bike_data %>%
  filter(category_1 == "Mountain") %>%
  group_by(qtr_date, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(category_2 = factor(
    category_2,
    levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")
  ))

# Create bright color palette for mountain bikes
mountain_bright_colors <- c("#FF6B6B", "#4ECDC4", "#45B7D1", "#FFA07A", "#96CEB4")

# Visualization
ggplot(mountain_qtr, aes(x = qtr_date, y = revenue, group = 1)) +
  geom_line(aes(color = category_2), size = 1.5, show.legend = FALSE) +
  scale_color_manual(values = mountain_bright_colors) +
  scale_y_continuous(labels = dollar_format()) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  facet_wrap(~category_2, ncol = 1, scales = "free_y") +
  labs(
    title = "Mountain Bikes - Quarterly Sales by Subcategory",
    x = NULL,
    y = "Revenue"
  ) +
  theme_bw(base_size = 12) +
  theme(
    plot.title = element_text(size = 15, face = "bold", hjust = 0.5),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA),
    strip.background = element_rect(fill = "#D1D5DB"),
    strip.text = element_text(color = "black", face = "bold", size = 11),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray80")
  )

2.5 Mountain Bikes - Monthly Trends

# Monthly mountain bike data
mountain_month <- bike_data %>%
  filter(category_1 == "Mountain") %>%
  group_by(month_date, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(category_2 = factor(
    category_2,
    levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")
  ))

# Create bright color palette for mountain bikes
mountain_bright_colors_named <- c(
  "Cross Country Race" = "#FF6B6B",  # Bright red
  "Trail" = "#4ECDC4",               # Bright teal
  "Over Mountain" = "#45B7D1",       # Bright blue
  "Sport" = "#FFA07A",               # Bright coral
  "Fat Bike" = "#96CEB4"             # Bright mint green
)

# Plot - BRIGHT COLORS AND VISIBLE POINTS
ggplot(mountain_month, aes(x = month_date, y = revenue)) +
  geom_point(aes(color = category_2), alpha = 0.7, size = 1.8, show.legend = FALSE) +
  geom_smooth(
    aes(color = category_2),
    method = "loess",
    se = FALSE,
    span = 0.2,
    size = 1.5,
    show.legend = FALSE
  ) +
  scale_color_manual(values = mountain_bright_colors_named) +
  scale_y_continuous(labels = dollar_format()) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  facet_wrap(~category_2, ncol = 1, scales = "free_y") +
  labs(
    title = "Mountain Bikes - Monthly Sales Patterns",
    x = NULL,
    y = "Revenue"
  ) +
  theme_bw(base_size = 12) +
  theme(
    plot.title = element_text(size = 15, face = "bold", hjust = 0.5),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA),
    strip.background = element_rect(fill = "#D1D5DB"),
    strip.text = element_text(color = "black", face = "bold", size = 11),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray80")
  )

2.6 Mountain Bikes - Weekly Fluctuations

# Weekly mountain bike data
mountain_week <- bike_data %>%
  filter(category_1 == "Mountain") %>%
  group_by(week_date, category_2) %>%
  summarise(revenue = sum(total_price), .groups = "drop") %>%
  mutate(category_2 = factor(
    category_2,
    levels = c("Cross Country Race", "Trail", "Over Mountain", "Sport", "Fat Bike")
  ))

# Create plot - BRIGHT COLORS
ggplot(mountain_week, aes(x = week_date, y = revenue)) +
  geom_point(aes(color = category_2), alpha = 0.6, size = 1.5, show.legend = FALSE) +
  geom_smooth(
    aes(color = category_2),
    method = "loess",
    se = FALSE,
    span = 0.2,
    size = 1.5,
    show.legend = FALSE
  ) +
  scale_color_manual(values = mountain_bright_colors_named) +
  scale_y_continuous(labels = dollar_format()) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  facet_wrap(~category_2, ncol = 1, scales = "free_y") +
  labs(
    title = "Mountain Bikes - Weekly Sales Dynamics",
    x = NULL,
    y = "Revenue"
  ) +
  theme_bw(base_size = 12) +
  theme(
    plot.title = element_text(size = 15, face = "bold", hjust = 0.5),
    plot.background = element_rect(fill = "#F5F5F5", color = NA),
    panel.background = element_rect(fill = "#F5F5F5", color = NA),
    strip.background = element_rect(fill = "#D1D5DB"),
    strip.text = element_text(color = "black", face = "bold", size = 11),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_line(color = "gray80")
  )