Overview

This analysis visualizes bike sales trends across different product categories from 2011 to 2015, using quarterly and monthly aggregations.

Load Libraries

library(tidyverse)
library(tidyquant)
library(lubridate)
library(readxl)

Load and Wrangle Data

# Load Excel files from Downloads
bikes_tbl <- read_excel("C:/Users/dell/Downloads/bikes.xlsx")
bikeshops_tbl <- read_excel("C:/Users/dell/Downloads/bikeshops.xlsx")
orderlines_tbl <- read_excel("C:/Users/dell/Downloads/orderlines (1).xlsx")

# Wrangle the data
bike_orderlines_wrangled_tbl <- orderlines_tbl %>%
  left_join(bikes_tbl, by = c("product.id" = "bike.id")) %>%
  left_join(bikeshops_tbl, by = c("customer.id" = "bikeshop.id")) %>%
  separate(description, into = c('category_1', 'category_2', 'frame_material'), sep = ' - ') %>%
  separate(location, into = c('city', 'state'), sep = ', ', remove = FALSE) %>%
  mutate(total_price = price * quantity) %>%
  select(-...1, -location) %>%
  select(contains('date'), contains('id'), contains('order'), 
         quantity, price, total_price, everything()) %>%
  rename(order_date = order.date) %>%
  set_names(names(.) %>% str_replace_all("\\.", "_"))

Prepare Data for Visualization

# Calculate sales by category_2 and quarter
sales_by_category_time_tbl <- bike_orderlines_wrangled_tbl %>%
  select(order_date, category_2, total_price) %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(order_quarter = floor_date(order_date, unit = "quarter")) %>%
  group_by(order_quarter, category_2) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()

# Define colors for each category
category_colors <- c(
  "Cross Country Race" = "#2C3E50",
  "Elite Road" = "#E74C3C",
  "Trail" = "#27AE60",
  "Endurance Road" = "#BDC3C7",
  "Over Mountain" = "#5DADE2",
  "Triathalon" = "#34495E",
  "Sport" = "#A9DFBF",
  "Cyclocross" = "#F1948A",
  "Fat Bike" = "#F39C12"
)

Quarterly Sales by Category

# Create the faceted plot (quarterly data)
plot <- sales_by_category_time_tbl %>%
  ggplot(aes(x = order_quarter, y = sales, color = category_2, group = category_2)) +
  
  # Geometries
  geom_line(linewidth = 1) +
  geom_point(size = 2) +
  
  # Facet wrap by category_2
  facet_wrap(~category_2, ncol = 3, scales = "free_y") +
  
  # Color scale
  scale_color_manual(values = category_colors) +
  
  # Format y-axis as millions with dollar signs
  scale_y_continuous(
    labels = scales::label_dollar(scale = 1e-6, suffix = "M", accuracy = 0.1)
  ) +
  
  # Format x-axis to show years
  scale_x_date(
    date_breaks = "1 year",
    date_labels = "%Y"
  ) +
  
  # Labels
  labs(
    x = "order_date",
    y = "sales",
    title = "Quarterly Sales by Bike Category (2011-2015)"
  ) +
  
  # Theme
  theme_minimal(base_size = 11) +
  theme(
    # Dark header for facets
    strip.background = element_rect(fill = "#2C3E50", color = NA),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    
    # Grid and panels
    panel.grid.major = element_line(color = "gray90", linewidth = 0.3),
    panel.grid.minor = element_blank(),
    panel.border = element_rect(color = "gray80", fill = NA, linewidth = 0.5),
    
    # Remove legend
    legend.position = "none",
    
    # Axis formatting
    axis.title = element_text(size = 10),
    axis.text = element_text(size = 9),
    axis.text.x = element_text(angle = 0, hjust = 0.5),
    
    # Plot margins
    plot.margin = margin(10, 10, 10, 10)
  )

print(plot)

Monthly Sales by Category

# Calculate sales by month
sales_by_category_month_tbl <- bike_orderlines_wrangled_tbl %>%
  select(order_date, category_2, total_price) %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(order_month = floor_date(order_date, unit = "month")) %>%
  group_by(order_month, category_2) %>%
  summarise(sales = sum(total_price)) %>%
  ungroup()
# Plot with monthly data
plot_monthly <- sales_by_category_month_tbl %>%
  ggplot(aes(x = order_month, y = sales, color = category_2, group = category_2)) +
  geom_line(linewidth = 0.8) +
  geom_point(size = 1.5) +
  facet_wrap(~category_2, ncol = 3, scales = "free_y") +
  scale_color_manual(values = category_colors) +
  scale_y_continuous(
    labels = scales::label_dollar(scale = 1e-6, suffix = "M", accuracy = 0.1)
  ) +
  scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
  labs(
    x = "order_date", 
    y = "sales", 
    title = "Monthly Sales by Bike Category (2011-2015)"
  ) +
  theme_minimal(base_size = 11) +
  theme(
    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", linewidth = 0.3),
    panel.grid.minor = element_blank(),
    panel.border = element_rect(color = "gray80", fill = NA, linewidth = 0.5),
    legend.position = "none",
    axis.title = element_text(size = 10),
    axis.text = element_text(size = 9),
    plot.margin = margin(10, 10, 10, 10)
  )

print(plot_monthly)