Final Homework
Sales Data Analysis and Visualization
Egshiglen Baatar
2025-12-23
Total Sales Charts
Quarterly Sales Trends
# Aggregate sales by quarter
quarterly_sales_tbl <- bike_orderlines_tbl %>%
mutate(order_date = ymd(order_date),
year_quarter = floor_date(order_date, unit = "quarter")) %>%
group_by(year_quarter) %>%
summarise(sales = sum(total_price), .groups = "drop")
# Create the plot
quarterly_sales_tbl %>%
ggplot(aes(x = year_quarter, y = sales)) +
geom_line(color = palette_light()[1], linewidth = 1.5) +
geom_smooth(method = "loess", span = 0.3, se = FALSE,
color = palette_light()[2], linewidth = 1) +
scale_y_continuous(labels = label_dollar(),
limits = c(0, NA),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Total Sales - Quarterly Trends",
subtitle = "Revenue trends aggregated by quarter 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, color = "gray40"),
axis.text = element_text(size = 10),
panel.grid.minor = element_blank()
)
Monthly Sales Trends
# Aggregate sales by month
monthly_sales_tbl <- bike_orderlines_tbl %>%
mutate(order_date = ymd(order_date),
year_month = floor_date(order_date, unit = "month")) %>%
group_by(year_month) %>%
summarise(sales = sum(total_price), .groups = "drop")
# Create the plot
monthly_sales_tbl %>%
ggplot(aes(x = year_month, y = sales)) +
geom_point(color = "gray30", size = 2.5, alpha = 0.6) +
geom_smooth(method = "loess", span = 0.2,
color = palette_light()[1], fill = palette_light()[1],
alpha = 0.2, linewidth = 1.3) +
scale_y_continuous(labels = label_dollar(),
limits = c(0, NA),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Total Sales - Monthly Trends",
subtitle = "Revenue trends with smoothed pattern highlighting seasonality",
x = NULL,
y = "Revenue (USD)") +
theme_tq() +
theme(
plot.title = element_text(face = "bold", size = 16),
plot.subtitle = element_text(size = 12, color = "gray40"),
axis.text = element_text(size = 10),
panel.grid.minor = element_blank()
)
Weekly Sales Trends
# Aggregate sales by week
weekly_sales_tbl <- bike_orderlines_tbl %>%
mutate(order_date = ymd(order_date),
year_week = floor_date(order_date, unit = "week")) %>%
group_by(year_week) %>%
summarise(sales = sum(total_price), .groups = "drop")
# Create the plot
weekly_sales_tbl %>%
ggplot(aes(x = year_week, y = sales)) +
geom_point(color = "gray40", size = 1.5, alpha = 0.4) +
geom_smooth(method = "loess", span = 0.15,
color = palette_light()[1], fill = palette_light()[1],
alpha = 0.2, linewidth = 1.3) +
scale_y_continuous(labels = label_dollar(),
limits = c(0, NA),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Total Sales - Weekly Trends",
subtitle = "High granularity view revealing short-term fluctuations and patterns",
x = NULL,
y = "Revenue (USD)") +
theme_tq() +
theme(
plot.title = element_text(face = "bold", size = 16),
plot.subtitle = element_text(size = 12, color = "gray40"),
axis.text = element_text(size = 10),
panel.grid.minor = element_blank()
)
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),
year_quarter = floor_date(order_date, unit = "quarter")) %>%
group_by(category_2, year_quarter) %>%
summarise(sales = sum(total_price), .groups = "drop")
# Define colors for each category
category_colors <- c(
"Elite Road" = "#2C3E50",
"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(linewidth = 1.4) +
facet_wrap(~ category_2, ncol = 1, scales = "free_y",
strip.position = "top") +
scale_color_manual(values = category_colors) +
scale_y_continuous(labels = label_dollar(scale = 0.001, suffix = "K"),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Road Bike Sales by Category - Quarterly",
subtitle = "Sales performance across Road bike subcategories",
x = NULL,
y = "Revenue") +
theme_minimal() +
theme(
plot.title = element_text(hjust = 0.5, size = 15, face = "bold"),
plot.subtitle = element_text(hjust = 0.5, size = 11, color = "gray40"),
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.spacing = unit(0.6, "lines"),
legend.position = "none",
axis.text = element_text(size = 9),
axis.text.y = element_text(size = 8),
plot.margin = margin(15, 15, 15, 15)
)
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),
year_month = floor_date(order_date, unit = "month")) %>%
group_by(category_2, year_month) %>%
summarise(sales = sum(total_price), .groups = "drop")
# 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.5) +
geom_smooth(method = "loess", span = 0.2, aes(color = category_2),
fill = "gray70", alpha = 0.25, linewidth = 1.3, se = TRUE) +
facet_wrap(~ category_2, ncol = 1, scales = "free_y",
strip.position = "top") +
scale_color_manual(values = category_colors) +
scale_y_continuous(labels = label_dollar(scale = 0.001, suffix = "K"),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Road Bike Sales by Category - Monthly",
subtitle = "Monthly performance with trend lines revealing seasonality",
x = NULL,
y = "Revenue") +
theme_minimal() +
theme(
plot.title = element_text(hjust = 0.5, size = 15, face = "bold"),
plot.subtitle = element_text(hjust = 0.5, size = 11, color = "gray40"),
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.spacing = unit(0.6, "lines"),
legend.position = "none",
axis.text = element_text(size = 9),
axis.text.y = element_text(size = 8),
plot.margin = margin(15, 15, 15, 15)
)
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),
year_week = floor_date(order_date, unit = "week")) %>%
group_by(category_2, year_week) %>%
summarise(sales = sum(total_price), .groups = "drop")
# Create faceted plot
road_weekly_tbl %>%
ggplot(aes(x = year_week, y = sales, color = category_2)) +
geom_point(color = "gray30", size = 1, alpha = 0.3) +
geom_smooth(method = "loess", span = 0.1, aes(color = category_2),
fill = "gray70", alpha = 0.25, linewidth = 1.3, se = TRUE) +
facet_wrap(~ category_2, ncol = 1, scales = "free_y",
strip.position = "top") +
scale_color_manual(values = category_colors) +
scale_y_continuous(labels = label_dollar(scale = 0.001, suffix = "K"),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Road Bike Sales by Category - Weekly",
subtitle = "High-resolution view of weekly sales fluctuations",
x = NULL,
y = "Revenue") +
theme_minimal() +
theme(
plot.title = element_text(hjust = 0.5, size = 15, face = "bold"),
plot.subtitle = element_text(hjust = 0.5, size = 11, color = "gray40"),
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.spacing = unit(0.6, "lines"),
legend.position = "none",
axis.text = element_text(size = 9),
axis.text.y = element_text(size = 8),
plot.margin = margin(15, 15, 15, 15)
)
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),
year_quarter = floor_date(order_date, unit = "quarter")) %>%
group_by(category_2, year_quarter) %>%
summarise(sales = sum(total_price), .groups = "drop")
# Define colors for mountain categories
mountain_colors <- c(
"Elite Mountain" = "#2C3E50",
"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(linewidth = 1.4) +
facet_wrap(~ category_2, ncol = 1, scales = "free_y",
strip.position = "top") +
scale_color_manual(values = mountain_colors) +
scale_y_continuous(labels = label_dollar(scale = 0.001, suffix = "K"),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Mountain Bike Sales by Category - Quarterly",
subtitle = "Sales performance across Mountain bike subcategories",
x = NULL,
y = "Revenue") +
theme_minimal() +
theme(
plot.title = element_text(hjust = 0.5, size = 15, face = "bold"),
plot.subtitle = element_text(hjust = 0.5, size = 11, color = "gray40"),
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.spacing = unit(0.6, "lines"),
legend.position = "none",
axis.text = element_text(size = 9),
axis.text.y = element_text(size = 8),
plot.margin = margin(15, 15, 15, 15)
)
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),
year_month = floor_date(order_date, unit = "month")) %>%
group_by(category_2, year_month) %>%
summarise(sales = sum(total_price), .groups = "drop")
# 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.5) +
geom_smooth(method = "loess", span = 0.2, aes(color = category_2),
fill = "gray70", alpha = 0.25, linewidth = 1.3, se = TRUE) +
facet_wrap(~ category_2, ncol = 1, scales = "free_y",
strip.position = "top") +
scale_color_manual(values = mountain_colors) +
scale_y_continuous(labels = label_dollar(scale = 0.001, suffix = "K"),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Mountain Bike Sales by Category - Monthly",
subtitle = "Monthly performance with trend lines revealing seasonality",
x = NULL,
y = "Revenue") +
theme_minimal() +
theme(
plot.title = element_text(hjust = 0.5, size = 15, face = "bold"),
plot.subtitle = element_text(hjust = 0.5, size = 11, color = "gray40"),
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.spacing = unit(0.6, "lines"),
legend.position = "none",
axis.text = element_text(size = 9),
axis.text.y = element_text(size = 8),
plot.margin = margin(15, 15, 15, 15)
)
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),
year_week = floor_date(order_date, unit = "week")) %>%
group_by(category_2, year_week) %>%
summarise(sales = sum(total_price), .groups = "drop")
# Create faceted plot
mountain_weekly_tbl %>%
ggplot(aes(x = year_week, y = sales, color = category_2)) +
geom_point(color = "gray30", size = 1, alpha = 0.3) +
geom_smooth(method = "loess", span = 0.1, aes(color = category_2),
fill = "gray70", alpha = 0.25, linewidth = 1.3, se = TRUE) +
facet_wrap(~ category_2, ncol = 1, scales = "free_y",
strip.position = "top") +
scale_color_manual(values = mountain_colors) +
scale_y_continuous(labels = label_dollar(scale = 0.001, suffix = "K"),
expand = expansion(mult = c(0, 0.1))) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(title = "Mountain Bike Sales by Category - Weekly",
subtitle = "High-resolution view of weekly sales fluctuations",
x = NULL,
y = "Revenue") +
theme_minimal() +
theme(
plot.title = element_text(hjust = 0.5, size = 15, face = "bold"),
plot.subtitle = element_text(hjust = 0.5, size = 11, color = "gray40"),
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.spacing = unit(0.6, "lines"),
legend.position = "none",
axis.text = element_text(size = 9),
axis.text.y = element_text(size = 8),
plot.margin = margin(15, 15, 15, 15)
)
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)
) %>%
mutate(across(c(total_revenue, avg_order_value), ~dollar(.x, accuracy = 0.01)))
# 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),
.groups = "drop"
) %>%
arrange(desc(revenue)) %>%
mutate(
revenue_formatted = dollar(revenue, accuracy = 0.01),
pct_of_total = percent(revenue / sum(revenue), accuracy = 0.1)
)
# Display summaries
knitr::kable(overall_summary,
caption = "Overall Sales Summary",
format.args = list(big.mark = ","),
align = c("r", "r", "r", "r"))| total_revenue | total_orders | avg_order_value | total_quantity |
|---|---|---|---|
| $71,032,330.00 | 2,000 | $35,516.16 | 20,172 |
knitr::kable(category_summary %>%
select(category_1, category_2, revenue_formatted, pct_of_total, orders, quantity),
col.names = c("Category", "Subcategory", "Revenue", "% of Total", "Orders", "Quantity"),
caption = "Sales by Category",
format.args = list(big.mark = ","),
align = c("l", "l", "r", "r", "r", "r"))| Category | Subcategory | Revenue | % of Total | Orders | Quantity |
|---|---|---|---|---|---|
| Mountain | Cross Country Race | $19,224,630.00 | 27.1% | 1,130 | 3,683 |
| Road | Elite Road | $15,334,665.00 | 21.6% | 1,254 | 4,483 |
| Road | Endurance Road | $10,381,060.00 | 14.6% | 1,075 | 3,322 |
| Mountain | Trail | $9,373,460.00 | 13.2% | 994 | 2,569 |
| Mountain | Over Mountain | $7,571,270.00 | 10.7% | 714 | 1,514 |
| Road | Triathalon | $4,053,750.00 | 5.7% | 621 | 1,149 |
| Road | Cyclocross | $2,108,120.00 | 3.0% | 519 | 901 |
| Mountain | Sport | $1,932,755.00 | 2.7% | 863 | 2,177 |
| Mountain | Fat Bike | $1,052,620.00 | 1.5% | 277 | 374 |
Key Insights
This comprehensive sales analysis reveals several important patterns:
Temporal Dynamics: The data demonstrates clear seasonal variations across all time aggregations. Quarterly trends show the overall trajectory, while weekly data captures short-term volatility and helps identify operational patterns that might be masked at higher aggregations.
Category Performance: Both Road and Mountain bike categories show distinct performance characteristics, with each subcategory exhibiting unique sales patterns. The trend smoothing reveals underlying demand patterns that can inform inventory and marketing decisions.
Product Portfolio: The diversity of subcategories within each main category suggests a well-balanced product mix. Performance varies across subcategories, indicating opportunities for targeted marketing and inventory optimization.
Strategic Applications: These visualizations provide a foundation for data-driven decision making in inventory planning, promotional timing, and sales forecasting. The multi-resolution time series analysis enables both strategic planning and tactical adjustments.
Analysis completed on 2025-12-23