1 Overview

This report analyzes instructor-provided financial transaction data to evaluate sales performance across product categories and order behavior. The goal is to demonstrate how a transactional database can be transformed into managerial insights through structured aggregation and reporting.

Key Objectives :

  • Examine overall sales trends over time (monthly and quarterly)
  • Compare category performance (Road vs Mountain)
  • Evaluate order behavior using average order value and average units per order
  • Provide managerial insights and recommendations to support decision-making

2 Data Source

This analysis uses provided relational datasets representing bicycle sales transactions:

  • bikeshops: store reference information
  • bikes: product reference information
  • orderlines: base transaction records (line items)
  • bike_orderlines: enriched transaction table used as the main analysis dataset
# Import Library
library(tidyverse)
library(lubridate)
library(scales)
library(readxl)
library(janitor)

# Load tables
orderlines <- read_excel("orderlines.xlsx") |> clean_names()
bikes <- read_excel("bikes.xlsx") |> clean_names()
bikeshops <- read_excel("bikeshops.xlsx") |> clean_names()

# Load Main Analysis Table that used
sales_raw <- read_csv("bike_orderlines.csv", show_col_types = FALSE) |> clean_names()

# Clean & prepare analysis fields

sales <- sales_raw |>
mutate(
order_date = parse_date_time(order_date, orders = c("Ymd HMS", "Ymd HM", "Ymd")),
total_price = readr::parse_number(as.character(total_price))
) |>
filter(!is.na(order_date), !is.na(total_price)) |>
mutate(
month = floor_date(order_date, "month"),
quarter = paste0(year(order_date), " Q", quarter(order_date))
)

glimpse(sales)
## Rows: 15,644
## Columns: 15
## $ order_date     <dttm> 2011-01-07, 2011-01-07, 2011-01-10, 2011-01-10, 2011-0…
## $ order_id       <dbl> 1, 1, 2, 2, 3, 3, 3, 3, 3, 4, 5, 5, 5, 5, 6, 6, 6, 6, 7…
## $ order_line     <dbl> 1, 2, 1, 2, 1, 2, 3, 4, 5, 1, 1, 2, 3, 4, 1, 2, 3, 4, 1…
## $ quantity       <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1…
## $ price          <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570,…
## $ total_price    <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570,…
## $ model          <chr> "Jekyll Carbon 2", "Trigger Carbon 2", "Beast of the Ea…
## $ category_1     <chr> "Mountain", "Mountain", "Mountain", "Mountain", "Road",…
## $ category_2     <chr> "Over Mountain", "Over Mountain", "Trail", "Over Mounta…
## $ frame_material <chr> "Carbon", "Carbon", "Aluminum", "Carbon", "Carbon", "Ca…
## $ bikeshop_name  <chr> "Ithaca Mountain Climbers", "Ithaca Mountain Climbers",…
## $ city           <chr> "Ithaca", "Ithaca", "Kansas City", "Kansas City", "Loui…
## $ state          <chr> "NY", "NY", "KS", "KS", "KY", "KY", "KY", "KY", "KY", "…
## $ month          <dttm> 2011-01-01, 2011-01-01, 2011-01-01, 2011-01-01, 2011-0…
## $ quarter        <chr> "2011 Q1", "2011 Q1", "2011 Q1", "2011 Q1", "2011 Q1", …

3 Data Management & Metrics

I also try to aggregate transactions into monthly and quarterly periods and compute key financial indicators:

  • Total Sales: revenue measure
  • Orders: number of unique orders
  • Units: total quantity sold
  • AOV (Average Order Value): Sales / Orders
  • Average Units per Order: Units / Orders
#Monthly totals

monthly_totals <- sales |>
group_by(month) |>
summarise(
total_sales = sum(total_price, na.rm = TRUE),
orders = n_distinct(order_id),
units = sum(quantity, na.rm = TRUE),
.groups = "drop"
) |>
mutate(
aov = total_sales / orders,
units_per_order = units / orders
)

# Monthly by category (Road vs Mountain)

monthly_by_cat <- sales |>
group_by(month, category_1) |>
summarise(
total_sales = sum(total_price, na.rm = TRUE),
orders = n_distinct(order_id),
units = sum(quantity, na.rm = TRUE),
.groups = "drop"
) |>
mutate(
aov = total_sales / orders,
units_per_order = units / orders
)

# Quarterly summary

quarterly_totals <- sales |>
group_by(quarter) |>
summarise(
total_sales = sum(total_price, na.rm = TRUE),
orders = n_distinct(order_id),
.groups = "drop"
)

5 Category Performance (Road vs Mountain)

Instead of plotting both categories on one line, this report uses faceting to clearly compare patterns across categories.

ggplot(monthly_by_cat, aes(x = month, y = total_sales)) +
geom_line(linewidth = 1.0, color = "#e74c3c", alpha = 0.9) +
scale_y_continuous(labels = scales::dollar) +
facet_wrap(~ category_1, scales = "free_y") +
labs(
title = "Monthly Sales by Category (Faceted View)",
subtitle = "Faceting improves readability when categories have different magnitudes",
x = "Month",
y = "Sales (USD)"
) +
theme_minimal(base_size = 12)

6 Category Revenue Share Over Time

category_share <- sales |>
  group_by(month, category_1) |>
  summarise(
    sales = sum(total_price, na.rm = TRUE),
    .groups = "drop"
  ) |>
  group_by(month) |>
  mutate(
    total_monthly_sales = sum(sales),
    revenue_share = sales / total_monthly_sales
  )

ggplot(category_share,
       aes(x = month, y = revenue_share, fill = category_1)) +
  geom_area(alpha = 0.85) +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +
  labs(
    title = "Category Revenue Share Over Time",
    subtitle = "Shows the relative contribution of Road and Mountain categories to total sales",
    x = "Month",
    y = "Revenue Share",
    fill = "Category"
  ) +
  theme_minimal(base_size = 12)

7 Order Behavior Analysis

7.1 Average Order Value (AOV) by Category

ggplot(monthly_by_cat, aes(x = month, y = aov)) +
geom_line(linewidth = 1.0, color = "#2c3e50") +
facet_wrap(~ category_1, scales = "free_y") +
scale_y_continuous(labels = scales::dollar) +
labs(
title = "Average Order Value (AOV) by Category",
subtitle = "Shows differences in transaction value across categories",
x = "Month",
y = "AOV (USD)"
) +
theme_minimal(base_size = 12)

7.2 Average Units per Order by Category

ggplot(monthly_by_cat, aes(x = month, y = units_per_order)) +
geom_line(linewidth = 1.0, color = "#3498db") +
facet_wrap(~ category_1) +
labs(
title = "Average Units per Order by Category",
subtitle = "Helps interpret whether customers buy more items per order by category",
x = "Month",
y = "Units per Order"
) +
theme_minimal(base_size = 12)

8 Key Managerial Insights and Recommendations

8.1 Key Managerial Insights

  • Monthly aggregation provides a stable view of revenue trends and is suitable for tactical planning.
  • Category performance differs over time; comparing Road and Mountain helps prioritize product planning and marketing focus.
  • Order behavior metrics (AOV and units per order) indicate that customer purchasing patterns vary by category.
  • Combining sales and order behavior offers a more complete view than revenue alone, supporting operational decisions such as inventory planning.
  • The revenue share analysis shows how the contribution of Road and Mountain categories evolves over time, highlighting whether the business relies on a balanced product mix or is increasingly dependent on a single category.

8.2 Managerial Recommendations

  • Use monthly sales and AOV as primary inputs for budgeting and forecasting.
  • Apply category-specific strategies: prioritize categories with consistent revenue contribution while maintaining adequate availability for others.
  • Monitor changes in units per order to anticipate inventory and fulfillment needs.
  • Review months with unusual spikes or drops to identify potential drivers such as promotions, supply issues, or market changes.
  • Management should monitor category revenue share to avoid over-dependence on a single product category and ensure a balanced portfolio that reduces revenue risk.