Bike Sales Trends by Category (2011-2015)

Load Libraries and Data

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

# Load data - adjust path if needed
# Option 1: If RDS file exists
if (file.exists('bike_orderlines.rds')) {
  bike_orderlines_wrangled_tbl <- readRDS('bike_orderlines.rds')
} else {
  # Option 2: Create from Excel files
  bikes_tbl <- read_excel("bikes.xlsx")
  bikeshops_tbl <- read_excel("bikeshops.xlsx")
  orderlines_tbl <- read_excel("orderlines.xlsx")
  
  bike_orderlines_wrangled_tbl <- left_join(orderlines_tbl, 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("\\.", "_"))
}

Sales Trends by Bike Category

# Prepare data for time series by category_2
sales_by_category2_time <- bike_orderlines_wrangled_tbl %>%
  select(order_date, category_2, total_price) %>%
  mutate(order_date = ymd(order_date)) %>%
  mutate(year_month = floor_date(order_date, unit = "month")) %>%
  group_by(year_month, category_2) %>%
  summarise(sales = sum(total_price), .groups = 'drop') %>%
  ungroup()

# Create the faceted time series plot
sales_by_category2_time %>%
  ggplot(aes(x = year_month, y = sales, color = category_2)) +
  
  # Add line and points
  geom_line(linewidth = 1) +
  geom_point(size = 2) +
  
  # Facet by category_2
  facet_wrap(~ category_2, scales = "free_y", ncol = 3) +
  
  # Format y-axis as currency in millions
  scale_y_continuous(labels = scales::dollar_format(scale = 1e-6, suffix = "M")) +
  
  # Use color palette
  scale_color_tq() +
  
  # Labels
  labs(
    x = "order_date",
    y = "sales"
  ) +
  
  # Theme
  theme_tq() +
  theme(
    legend.position = "bottom",
    legend.title = element_blank(),
    strip.background = element_rect(fill = "#2c3e50", color = "#2c3e50"),
    strip.text = element_text(color = "white", face = "bold", size = 11),
    axis.text.x = element_text(angle = 0, hjust = 0.5, size = 9),
    panel.spacing = unit(1, "lines")
  )

---

Data Source: Bike sales orderlines (2011-2015)
Chart Type: Faceted time series showing monthly sales trends by bike category