1 Overview

This report demonstrates how financial transaction data can be managed, aggregated, and transformed into managerial insights. Using a structured dataset (sales transactions), we perform time-based aggregation (weekly, monthly, quarterly) and category-level analysis to support business decision-making.

Key objectives

  • Measure overall revenue performance over time (Total Sales)
  • Compare category performance (Road vs Mountain)
  • Identify patterns, seasonality, and outliers that affect financial trends

2 Data Source

This analysis uses multiple related datasets provided by the instructor, representing a financial transaction database for bicycle sales. The datasets include:

  • orderlines: base transactional data containing individual sales records
  • bikes: product master data including category and sub-category information
  • bikeshops: bikeshop reference data
  • bike_orderlines: an enriched transactional dataset resulting from the integration of orderlines, bikes, and bikeshops

The bike_orderlines dataset is used as the primary source for analysis, as it represents a consolidated financial table suitable for time-based aggregation and managerial reporting.

#Import library
library(tidyverse)
library(lubridate)
library(scales)
library(readxl)

# Load base/dimension tables (for database demonstration)
orderlines <- read_excel("orderlines.xlsx")
bikes      <- read_excel("bikes.xlsx")
bikeshops  <- read_excel("bikeshops.xlsx")

# Main analysis table
sales <- read_csv("bike_orderlines.csv", show_col_types = FALSE)

# Robust parsing + cleaning
sales <- sales %>%
  mutate(
    # robust date parsing (works for "YYYY-mm-dd", "YYYY-mm-dd HH:MM", "YYYY-mm-dd HH:MM:SS")
    order_date = parse_date_time(order_date, orders = c("Ymd HMS", "Ymd HM", "Ymd")),
    
    # make sure total_price is numeric even if it contains commas/$
    total_price = readr::parse_number(as.character(total_price))
  ) %>%
  filter(!is.na(order_date), !is.na(total_price)) %>%   # remove broken rows
  mutate(
    week = floor_date(order_date, "week", week_start = 1),
    month = floor_date(order_date, "month"),
    quarter_label = paste0(year(order_date), "-Q", quarter(order_date))
  )

# Quick sanity checks (optional but helpful)
summary(sales$total_price)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##     415    1950    3200    4541    5330  106600
range(sales$order_date)
## [1] "2011-01-07 UTC" "2015-12-25 UTC"
count(sales, category_1, sort = TRUE)

3 Data Management & Aggregation

In financial reporting, transaction-level data is typically rolled up into periods (weekly/monthly/quarterly) to support analysis at different managerial horizons:

  • Weekly: operational monitoring (short-term fluctuations)
  • Monthly: tactical planning and budgeting checks
  • Quarterly: strategic evaluation (executive reporting)
#Total Sales aggregation by period (uses total_price from dataset)

make_total_ts <- function(df, period_col, period_name) {
df %>%
group_by({{ period_col }}) %>%
summarise(total_sales = sum(total_price, na.rm = TRUE), .groups = "drop") %>%
mutate(period_type = period_name)
}

#Category Sales aggregation by period + sub-category

#category_1 = main category (e.g., Road, Mountain)
#category_2 = sub-category

make_category_ts <- function(df, main_category, period_col, period_name) {
df %>%
filter(category_1 == main_category) %>%
group_by({{ period_col }}, category_2) %>%
summarise(total_sales = sum(total_price, na.rm = TRUE), .groups = "drop") %>%
mutate(category = main_category, period_type = period_name)
}

5 Category Sales Analysis (Road vs Mountain)

The purpose of category-level sales analysis is to evaluate revenue contribution and stability across product segments. Separating Road and Mountain categories supports management decisions such as product focus, marketing allocation, and inventory planning.

5.1 Road Category - Weekly / Monthly / Quarterly

road_weekly <- make_category_ts(sales, "Road", week, "Weekly")

ggplot(road_weekly, aes(x = week, y = total_sales, color = category_2)) +
geom_line(linewidth = 0.9, alpha = 0.9) +
scale_color_brewer(palette = "Set2") +
scale_y_continuous(labels = scales::dollar) +
labs(
title = "Road Category Sales (Weekly)",
subtitle = "Sub-category breakdown reveals which segments drive weekly performance",
x = "Week",
y = "Sales (USD)",
color = "Sub-Category"
) +
theme_minimal(base_size = 12)

road_monthly <- make_category_ts(sales, "Road", month, "Monthly")

ggplot(road_monthly, aes(x = month, y = total_sales, color = category_2)) +
geom_line(linewidth = 1.0, alpha = 0.9) +
scale_color_brewer(palette = "Dark2") +
scale_y_continuous(labels = scales::dollar) +
labs(
title = "Road Category Sales (Monthly)",
subtitle = "Monthly trends are useful for identifying seasonality and sustained growth",
x = "Month",
y = "Sales (USD)",
color = "Sub-Category"
) +
theme_minimal(base_size = 12)

road_quarterly <- make_category_ts(sales, "Road", quarter_label, "Quarterly")

ggplot(road_quarterly, aes(x = quarter_label, y = total_sales, color = category_2, group = category_2)) +
geom_line(linewidth = 1.0, alpha = 0.9) +
geom_point(size = 1.8, alpha = 0.9) +
scale_color_brewer(palette = "Set1") +
scale_y_continuous(labels = scales::dollar) +
labs(
title = "Road Category Sales (Quarterly)",
subtitle = "Quarterly view supports strategic comparison between Road sub-categories",
x = "Quarter",
y = "Sales (USD)",
color = "Sub-Category"
) +
theme_minimal(base_size = 12) +
theme(axis.text.x = element_text(angle = 45, hjust = 1))

5.2 Mountain Category - Weekly / Monthly / Quarterly

mtn_weekly <- make_category_ts(sales, "Mountain", week, "Weekly")

ggplot(mtn_weekly, aes(x = week, y = total_sales, color = category_2)) +
geom_line(linewidth = 0.9, alpha = 0.9) +
scale_color_brewer(palette = "Set2") +
scale_y_continuous(labels = scales::dollar) +
labs(
title = "Mountain Category Sales (Weekly)",
subtitle = "Weekly monitoring highlights volatility and potential outlier weeks",
x = "Week",
y = "Sales (USD)",
color = "Sub-Category"
) +
theme_minimal(base_size = 12)

mtn_monthly <- make_category_ts(sales, "Mountain", month, "Monthly")

ggplot(mtn_monthly, aes(x = month, y = total_sales, color = category_2)) +
geom_line(linewidth = 1.0, alpha = 0.9) +
scale_color_brewer(palette = "Dark2") +
scale_y_continuous(labels = scales::dollar) +
labs(
title = "Mountain Category Sales (Monthly)",
subtitle = "Monthly view helps compare stability across Mountain sub-categories",
x = "Month",
y = "Sales (USD)",
color = "Sub-Category"
) +
theme_minimal(base_size = 12)

mtn_quarterly <- make_category_ts(sales, "Mountain", quarter_label, "Quarterly")

ggplot(mtn_quarterly, aes(x = quarter_label, y = total_sales, color = category_2, group = category_2)) +
geom_line(linewidth = 1.0, alpha = 0.9) +
geom_point(size = 1.8, alpha = 0.9) +
scale_color_brewer(palette = "Set1") +
scale_y_continuous(labels = scales::dollar) +
labs(
title = "Mountain Category Sales (Quarterly)",
subtitle = "Quarterly comparison supports higher-level category portfolio decisions",
x = "Quarter",
y = "Sales (USD)",
color = "Sub-Category"
) +
theme_minimal(base_size = 12) +
theme(axis.text.x = element_text(angle = 45, hjust = 1))

6 Key Managerial Insights and Recommendations

This section summarizes the key findings derived from the Total Sales and Category Sales analysis (weekly, monthly, and quarterly) from 2011 to end of 2015, and translates them into actionable managerial recommendations.

6.1 Key Managerial Insights

  • Sustained Revenue Growth

Quarterly Total Sales exhibit a clear upward trend, indicating consistent long-term revenue growth. Weekly sales show high volatility, while monthly and quarterly views provide more reliable signals for managerial evaluation.

  • Category Revenue Contribution

The Road category consistently outperforms the Mountain category in terms of total sales, establishing Road products as the primary revenue driver of the business.

  • Sub-Category Performance (Road)

Road sub-categories show differentiated contributions across time, enabling product portfolio evaluation and targeted planning.

  • Volatility in Mountain Category

Mountain category sales display higher short-term volatility, particularly in weekly data, which may reflect event-driven demand or promotional effects.

  • Seasonality and Outliers

Seasonal patterns are visible across categories, while outliers suggest irregular high-value transactions that should be monitored separately.

6.2 Managerial Recommendations

  • Use Quarterly Sales for Strategic Decisions

Rely on quarterly aggregated sales for strategic planning, while using weekly data primarily for operational monitoring.

  • Prioritize Road Category Investments

Focus budgeting and inventory planning on the categories and sub-categories with consistent revenue contribution.

  • Plan Promotions Using Seasonality

Align promotion timing and forecasting with seasonal peaks to maximize revenue performance.

  • Investigate Outlier Periods

Review weeks/months with abnormal spikes to understand drivers (bulk orders, campaigns, special events).