Final Homework

Sales Data Analysis and Visualization

Total Sales Charts

Quarterly Sales Trends

# Aggregate sales by quarter
quarterly_sales_tbl <- bike_orderlines_tbl %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_quarter = floor_date(order_date, unit = "quarter")) %>%
  group_by(year_quarter) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Create the plot
quarterly_sales_tbl %>%
  ggplot(aes(x = year_quarter, y = sales)) +
  geom_line(color = palette_light()[1], size = 1.5) +
  geom_smooth(method = "loess", span = 0.3, se = FALSE, 
              color = palette_light()[1], size = 1) +
  scale_y_continuous(labels = dollar_format(), 
                     limits = c(0, NA)) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Total Sales - Quarterly Trends",
       subtitle = "Revenue trends aggregated by quarter",
       x = NULL,
       y = "Revenue (USD)") +
  theme_tq() +
  theme(
    plot.title = element_text(face = "bold", size = 16),
    plot.subtitle = element_text(size = 12),
    axis.text = element_text(size = 10)
  )

Monthly Sales Trends

# Aggregate sales by month
monthly_sales_tbl <- bike_orderlines_tbl %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_month = floor_date(order_date, unit = "month")) %>%
  group_by(year_month) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Create the plot
monthly_sales_tbl %>%
  ggplot(aes(x = year_month, y = sales)) +
  geom_point(color = "gray30", size = 2, alpha = 0.7) +
  geom_smooth(method = "loess", span = 0.2, 
              color = palette_light()[1], fill = "gray70", 
              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 - Monthly Trends",
       subtitle = "Revenue trends with smoothed pattern",
       x = NULL,
       y = "Revenue (USD)") +
  theme_tq() +
  theme(
    plot.title = element_text(face = "bold", size = 16),
    plot.subtitle = element_text(size = 12),
    axis.text = element_text(size = 10)
  )

Weekly Sales Trends

# Aggregate sales by week
weekly_sales_tbl <- bike_orderlines_tbl %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_week = floor_date(order_date, unit = "week")) %>%
  group_by(year_week) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Create the plot
weekly_sales_tbl %>%
  ggplot(aes(x = year_week, y = sales)) +
  geom_point(color = "gray30", size = 1.5, alpha = 0.5) +
  geom_smooth(method = "loess", span = 0.15, 
              color = palette_light()[1], fill = "gray70", 
              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 - Weekly Trends",
       subtitle = "High granularity view of revenue patterns",
       x = NULL,
       y = "Revenue (USD)") +
  theme_tq() +
  theme(
    plot.title = element_text(face = "bold", size = 16),
    plot.subtitle = element_text(size = 12),
    axis.text = element_text(size = 10)
  )

Category Sales Charts - Road

Road Categories - Quarterly

# Filter for Road categories and aggregate by quarter
road_quarterly_tbl <- bike_orderlines_tbl %>%
  filter(category_1 == "Road") %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_quarter = floor_date(order_date, unit = "quarter")) %>%
  group_by(category_2, year_quarter) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Define colors for each category
category_colors <- c(
  "Elite Road" = "black",
  "Endurance Road" = "#e74c3c",
  "Triathalon" = "#00CED1",
  "Cyclocross" = "#D4AF37"
)

# Create faceted plot
road_quarterly_tbl %>%
  ggplot(aes(x = year_quarter, y = sales, color = category_2)) +
  geom_line(size = 1.3) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y",
             strip.position = "top") +
  scale_color_manual(values = category_colors) +
  scale_y_continuous(labels = dollar_format(scale = 0.001, suffix = "K"),
                     expand = expansion(mult = c(0, 0.1))) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Sales By Category 2",
       x = NULL,
       y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    strip.background = element_rect(fill = "#2c3e50", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    panel.grid.major = element_line(color = "gray90"),
    panel.grid.minor = element_blank(),
    panel.spacing = unit(0.5, "lines"),
    legend.position = "none",
    axis.text = element_text(size = 9),
    plot.margin = margin(10, 10, 10, 10)
  )

Road Categories - Monthly

# Filter for Road categories and aggregate by month
road_monthly_tbl <- bike_orderlines_tbl %>%
  filter(category_1 == "Road") %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_month = floor_date(order_date, unit = "month")) %>%
  group_by(category_2, year_month) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Create faceted plot
road_monthly_tbl %>%
  ggplot(aes(x = year_month, y = sales, color = category_2)) +
  geom_point(color = "gray30", size = 1.5, alpha = 0.6) +
  geom_smooth(method = "loess", span = 0.2, aes(color = category_2),
              fill = "gray70", alpha = 0.3, size = 1.2) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y",
             strip.position = "top") +
  scale_color_manual(values = category_colors) +
  scale_y_continuous(labels = dollar_format(scale = 0.001, suffix = "K"),
                     expand = expansion(mult = c(0, 0.1))) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Sales By Category 2 - Monthly",
       x = NULL,
       y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    strip.background = element_rect(fill = "#2c3e50", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    panel.grid.major = element_line(color = "gray90"),
    panel.grid.minor = element_blank(),
    panel.spacing = unit(0.5, "lines"),
    legend.position = "none",
    axis.text = element_text(size = 9),
    plot.margin = margin(10, 10, 10, 10)
  )

Road Categories - Weekly

# Filter for Road categories and aggregate by week
road_weekly_tbl <- bike_orderlines_tbl %>%
  filter(category_1 == "Road") %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_week = floor_date(order_date, unit = "week")) %>%
  group_by(category_2, year_week) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Create faceted plot
road_weekly_tbl %>%
  ggplot(aes(x = year_week, y = sales, color = category_2)) +
  geom_point(color = "gray30", size = 1, alpha = 0.4) +
  geom_smooth(method = "loess", span = 0.1, aes(color = category_2),
              fill = "gray70", alpha = 0.3, size = 1.2) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y",
             strip.position = "top") +
  scale_color_manual(values = category_colors) +
  scale_y_continuous(labels = dollar_format(scale = 0.001, suffix = "K"),
                     expand = expansion(mult = c(0, 0.1))) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Sales By Category 2 - Weekly",
       x = NULL,
       y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    strip.background = element_rect(fill = "#2c3e50", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    panel.grid.major = element_line(color = "gray90"),
    panel.grid.minor = element_blank(),
    panel.spacing = unit(0.5, "lines"),
    legend.position = "none",
    axis.text = element_text(size = 9),
    plot.margin = margin(10, 10, 10, 10)
  )

Category Sales Charts - Mountain

Mountain Categories - Quarterly

# Filter for Mountain categories and aggregate by quarter
mountain_quarterly_tbl <- bike_orderlines_tbl %>%
  filter(category_1 == "Mountain") %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_quarter = floor_date(order_date, unit = "quarter")) %>%
  group_by(category_2, year_quarter) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Define colors for mountain categories
mountain_colors <- c(
  "Elite Mountain" = "black",
  "Sport Mountain" = "#e74c3c",
  "Trail" = "#00CED1",
  "Over Mountain" = "#D4AF37"
)

# Create faceted plot
mountain_quarterly_tbl %>%
  ggplot(aes(x = year_quarter, y = sales, color = category_2)) +
  geom_line(size = 1.3) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y",
             strip.position = "top") +
  scale_color_manual(values = mountain_colors) +
  scale_y_continuous(labels = dollar_format(scale = 0.001, suffix = "K"),
                     expand = expansion(mult = c(0, 0.1))) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Sales By Category 2",
       x = NULL,
       y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    strip.background = element_rect(fill = "#2c3e50", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    panel.grid.major = element_line(color = "gray90"),
    panel.grid.minor = element_blank(),
    panel.spacing = unit(0.5, "lines"),
    legend.position = "none",
    axis.text = element_text(size = 9),
    plot.margin = margin(10, 10, 10, 10)
  )

Mountain Categories - Monthly

# Filter for Mountain categories and aggregate by month
mountain_monthly_tbl <- bike_orderlines_tbl %>%
  filter(category_1 == "Mountain") %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_month = floor_date(order_date, unit = "month")) %>%
  group_by(category_2, year_month) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Create faceted plot
mountain_monthly_tbl %>%
  ggplot(aes(x = year_month, y = sales, color = category_2)) +
  geom_point(color = "gray30", size = 1.5, alpha = 0.6) +
  geom_smooth(method = "loess", span = 0.2, aes(color = category_2),
              fill = "gray70", alpha = 0.3, size = 1.2) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y",
             strip.position = "top") +
  scale_color_manual(values = mountain_colors) +
  scale_y_continuous(labels = dollar_format(scale = 0.001, suffix = "K"),
                     expand = expansion(mult = c(0, 0.1))) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Sales By Category 2 - Monthly",
       x = NULL,
       y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    strip.background = element_rect(fill = "#2c3e50", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    panel.grid.major = element_line(color = "gray90"),
    panel.grid.minor = element_blank(),
    panel.spacing = unit(0.5, "lines"),
    legend.position = "none",
    axis.text = element_text(size = 9),
    plot.margin = margin(10, 10, 10, 10)
  )

Mountain Categories - Weekly

# Filter for Mountain categories and aggregate by week
mountain_weekly_tbl <- bike_orderlines_tbl %>%
  filter(category_1 == "Mountain") %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_week = floor_date(order_date, unit = "week")) %>%
  group_by(category_2, year_week) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Create faceted plot
mountain_weekly_tbl %>%
  ggplot(aes(x = year_week, y = sales, color = category_2)) +
  geom_point(color = "gray30", size = 1, alpha = 0.4) +
  geom_smooth(method = "loess", span = 0.1, aes(color = category_2),
              fill = "gray70", alpha = 0.3, size = 1.2) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y",
             strip.position = "top") +
  scale_color_manual(values = mountain_colors) +
  scale_y_continuous(labels = dollar_format(scale = 0.001, suffix = "K"),
                     expand = expansion(mult = c(0, 0.1))) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(title = "Sales By Category 2 - Weekly",
       x = NULL,
       y = NULL) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    strip.background = element_rect(fill = "#2c3e50", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    panel.grid.major = element_line(color = "gray90"),
    panel.grid.minor = element_blank(),
    panel.spacing = unit(0.5, "lines"),
    legend.position = "none",
    axis.text = element_text(size = 9),
    plot.margin = margin(10, 10, 10, 10)
  )

Summary Statistics

# Overall sales summary
overall_summary <- bike_orderlines_tbl %>%
  summarise(
    total_revenue = sum(total_price),
    total_orders = n_distinct(order_id),
    avg_order_value = total_revenue / total_orders,
    total_quantity = sum(quantity)
  )

# Category summary
category_summary <- bike_orderlines_tbl %>%
  group_by(category_1, category_2) %>%
  summarise(
    revenue = sum(total_price),
    orders = n_distinct(order_id),
    quantity = sum(quantity)
  ) %>%
  ungroup() %>%
  arrange(desc(revenue))

# Display summaries
knitr::kable(overall_summary, 
             caption = "Overall Sales Summary",
             format.args = list(big.mark = ","))

Overall Sales Summary

total_revenue	total_orders	avg_order_value	total_quantity
71,032,330	2,000	35,516.17	20,172

knitr::kable(category_summary, 
             caption = "Sales by Category",
             format.args = list(big.mark = ","))

Sales by Category

category_1	category_2	revenue	orders	quantity
Mountain	Cross Country Race	19,224,630	1,130	3,683
Road	Elite Road	15,334,665	1,254	4,483
Road	Endurance Road	10,381,060	1,075	3,322
Mountain	Trail	9,373,460	994	2,569
Mountain	Over Mountain	7,571,270	714	1,514
Road	Triathalon	4,053,750	621	1,149
Road	Cyclocross	2,108,120	519	901
Mountain	Sport	1,932,755	863	2,177
Mountain	Fat Bike	1,052,620	277	374

Insights

This comprehensive sales analysis reveals:

1. Temporal Patterns: Sales show clear seasonal trends across quarterly, monthly, and weekly aggregations
2. Category Performance: Both Road and Mountain bike categories demonstrate distinct sales patterns
3. Product Mix: Multiple subcategories within each main category contribute to overall revenue
4. Trend Analysis: Smoothed trend lines help identify underlying patterns despite weekly volatility

The visualizations provide actionable insights for inventory planning, marketing strategies, and sales forecasting.

---

Analysis completed on 2025-12-23