1. Introduction

Business Question

Which products are most often purchased on the Olist platform and which fail to find customers?

The Olist dataset covers a Brazilian e-commerce marketplace from 2016–2018 with over 110,000 orders, 32,951 unique products, and 74 product categories.

We approach this from two angles:

Descriptive analysis — What does the sales distribution look like? Which categories dominate? Which are invisible?
Machine learning — Can we cluster products into meaningful sales segments? Can we predict whether a product will sell well?

2. Setup & Data Loading

library(tidyverse)
library(readr)
library(ggplot2)
library(scales)
library(cluster)
library(factoextra)
library(randomForest)
library(caret)
library(knitr)
library(kableExtra)
library(RColorBrewer)
library(ggrepel)
library(gridExtra)

# Load core datasets
order_items  <- read_csv("C:/Users/USER/Downloads/olist dataset/olist_order_items_dataset.csv")
products     <- read_csv("C:/Users/USER/Downloads/olist dataset/olist_products_dataset.csv")
orders       <- read_csv("C:/Users/USER/Downloads/olist dataset/olist_orders_dataset.csv")
translations <- read_csv("C:/Users/USER/Downloads/olist dataset/product_category_name_translation.csv")

# Translate category names to English
products <- products %>%
  left_join(translations, by = "product_category_name") %>%
  mutate(category = coalesce(product_category_name_english, product_category_name))

# Sales per product
product_sales <- order_items %>%
  group_by(product_id) %>%
  summarise(
    units_sold    = n(),
    total_revenue = sum(price, na.rm = TRUE),
    avg_price     = mean(price, na.rm = TRUE),
    avg_freight   = mean(freight_value, na.rm = TRUE),
    .groups = "drop"
  )

# Sales per category
category_sales <- product_sales %>%
  left_join(products %>% select(product_id, category), by = "product_id") %>%
  group_by(category) %>%
  summarise(
    total_units   = sum(units_sold),
    total_revenue = sum(total_revenue),
    n_products    = n(),
    avg_price     = mean(avg_price, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  arrange(desc(total_units))

cat("Loaded successfully:\n")

## Loaded successfully:

cat("  Order items:", nrow(order_items), "\n")

##   Order items: 112650

cat("  Products:   ", nrow(products), "\n")

##   Products:    32951

cat("  Categories: ", n_distinct(category_sales$category), "\n")

##   Categories:  74

3. Descriptive Analysis

3.1 Sales Distribution — The Long Tail

# Distribution of sales per product
sales_dist <- product_sales %>%
  mutate(sales_bucket = case_when(
    units_sold == 1              ~ "1 sale (one-timers)",
    units_sold <= 5              ~ "2–5 sales",
    units_sold <= 20             ~ "6–20 sales",
    units_sold <= 50             ~ "21–50 sales",
    TRUE                         ~ "50+ sales (bestsellers)"
  )) %>%
  mutate(sales_bucket = factor(sales_bucket, levels = c(
    "1 sale (one-timers)", "2–5 sales", "6–20 sales",
    "21–50 sales", "50+ sales (bestsellers)"
  )))

bucket_summary <- sales_dist %>%
  count(sales_bucket) %>%
  mutate(pct = n / sum(n) * 100)

ggplot(bucket_summary, aes(x = sales_bucket, y = n, fill = sales_bucket)) +
  geom_col(width = 0.7) +
  geom_text(aes(label = paste0(comma(n), "\n(", round(pct, 1), "%)")),
            vjust = -0.4, size = 3.5, fontface = "bold") +
  scale_fill_brewer(palette = "RdYlGn", direction = 1) +
  scale_y_continuous(labels = comma, expand = expansion(mult = c(0, 0.15))) +
  labs(
    title = "The Long Tail: Most Products Sell Only Once",
    subtitle = "55% of all products recorded only a single sale in the entire period",
    x = NULL, y = "Number of Products",
    caption = "Source: Olist dataset 2016–2018"
  ) +
  theme_minimal(base_size = 13) +
  theme(legend.position = "none",
        plot.title = element_text(face = "bold", size = 15),
        axis.text.x = element_text(angle = 15, hjust = 1))

Key insight: Over 55% of products sold only once in the entire dataset period. This is classic long-tail e-commerce behaviour — a small number of products carry the bulk of volume.

3.2 Top 15 Categories by Volume

top15 <- category_sales %>% slice_head(n = 15)

ggplot(top15, aes(x = reorder(category, total_units), y = total_units)) +
  geom_col(fill = "#2196F3", width = 0.75) +
  geom_text(aes(label = comma(total_units)), hjust = -0.15, size = 3.5) +
  coord_flip() +
  scale_y_continuous(labels = comma, expand = expansion(mult = c(0, 0.15))) +
  labs(
    title = "Top 15 Product Categories by Units Sold",
    subtitle = "Bed, bath & table leads — household and lifestyle categories dominate",
    x = NULL, y = "Units Sold",
    caption = "Source: Olist dataset 2016–2018"
  ) +
  theme_minimal(base_size = 13) +
  theme(plot.title = element_text(face = "bold", size = 15))

3.3 Revenue vs Volume: Price Premium Categories

top20 <- category_sales %>% slice_head(n = 20)

ggplot(top20, aes(x = total_units, y = total_revenue, label = category)) +
  geom_point(aes(size = avg_price), color = "#E91E63", alpha = 0.7) +
  geom_text_repel(size = 3, max.overlaps = 15) +
  scale_x_continuous(labels = comma) +
  scale_y_continuous(labels = dollar_format(prefix = "R$", big.mark = ",")) +
  scale_size_continuous(name = "Avg Price (R$)", range = c(3, 12)) +
  labs(
    title = "Revenue vs Volume — Top 20 Categories",
    subtitle = "Bubble size = average product price. Watches & health_beauty punch above their unit count.",
    x = "Units Sold", y = "Total Revenue",
    caption = "Source: Olist dataset 2016–2018"
  ) +
  theme_minimal(base_size = 13) +
  theme(plot.title = element_text(face = "bold", size = 15))

3.4 The Bottom — Categories That Don’t Find Customers

bottom15 <- category_sales %>% slice_tail(n = 15)

ggplot(bottom15, aes(x = reorder(category, total_units), y = total_units)) +
  geom_col(fill = "#F44336", width = 0.75) +
  geom_text(aes(label = total_units), hjust = -0.3, size = 3.5) +
  coord_flip() +
  scale_y_continuous(expand = expansion(mult = c(0, 0.2))) +
  labs(
    title = "Bottom 15 Categories — Failing to Find Customers",
    subtitle = "Fashion, music, security & services: fewer than 50 sales across entire period",
    x = NULL, y = "Units Sold",
    caption = "Source: Olist dataset 2016–2018"
  ) +
  theme_minimal(base_size = 13) +
  theme(plot.title = element_text(face = "bold", size = 15))

Key insight: Categories like pc_gamer, fashion_childrens_clothes, security_and_services, and cds_dvds_musicals had almost no sales. These are either wrong fit for the platform or suffer from poor discoverability.

4. Unsupervised Learning — Clustering

4.1 Goal

We want to group products into natural sales behaviour segments based on: - Units sold - Average price - Total revenue - Freight-to-price ratio (logistics cost burden)

4.2 Data Preparation

# Build product feature matrix
cluster_data <- product_sales %>%
  left_join(products %>% select(product_id, category,
                                 product_photos_qty,
                                 product_description_lenght,
                                 product_weight_g), by = "product_id") %>%
  mutate(
    freight_ratio = avg_freight / (avg_price + 0.01)  # avoid div by 0
  ) %>%
  select(product_id, units_sold, avg_price, total_revenue, freight_ratio) %>%
  drop_na()

# Log-transform skewed variables
cluster_matrix <- cluster_data %>%
  select(-product_id) %>%
  mutate(across(everything(), ~ log1p(.x))) %>%
  scale()

cat("Products ready for clustering:", nrow(cluster_matrix), "\n")

## Products ready for clustering: 32951

4.3 Optimal Number of Clusters

set.seed(42)
# Elbow method on a sample for speed
sample_idx <- sample(nrow(cluster_matrix), min(5000, nrow(cluster_matrix)))
sample_mat  <- cluster_matrix[sample_idx, ]

wss <- map_dbl(1:10, function(k) {
  kmeans(sample_mat, centers = k, nstart = 10, iter.max = 50)$tot.withinss
})

elbow_df <- tibble(k = 1:10, wss = wss)

ggplot(elbow_df, aes(x = k, y = wss)) +
  geom_line(color = "#2196F3", size = 1.2) +
  geom_point(size = 4, color = "#2196F3") +
  geom_vline(xintercept = 4, linetype = "dashed", color = "#F44336", alpha = 0.7) +
  annotate("text", x = 4.3, y = max(wss)*0.85, label = "k = 4\n(chosen)", 
           color = "#F44336", size = 4) +
  labs(
    title = "Elbow Method — Choosing Number of Clusters",
    x = "Number of Clusters (k)", y = "Within-cluster Sum of Squares"
  ) +
  theme_minimal(base_size = 13) +
  theme(plot.title = element_text(face = "bold"))

4.4 K-Means Clustering (k=4)

set.seed(42)
km <- kmeans(cluster_matrix, centers = 4, nstart = 25, iter.max = 100)

cluster_data$cluster <- as.factor(km$cluster)

# Cluster profiles (back-transformed means)
cluster_profiles <- cluster_data %>%
  group_by(cluster) %>%
  summarise(
    n_products    = n(),
    avg_units     = round(mean(units_sold), 1),
    avg_price     = round(mean(avg_price), 1),
    avg_revenue   = round(mean(total_revenue), 1),
    avg_freight_r = round(mean(freight_ratio), 3),
    .groups = "drop"
  )

# Label clusters based on profile
cluster_labels <- c(
  "1" = "Niche / Low Volume",
  "2" = "Mid-range Steady",
  "3" = "Bestsellers",
  "4" = "High-price Low Volume"
)

# Auto-assign labels by avg_units rank
ranked <- cluster_profiles %>%
  arrange(avg_units) %>%
  mutate(label = c("Dead Weight", "Low Performers", "Mid-range Steady", "Bestsellers"))

cluster_data <- cluster_data %>%
  left_join(ranked %>% select(cluster, label), by = "cluster")

ranked %>%
  select(label, n_products, avg_units, avg_price, avg_revenue, avg_freight_r) %>%
  kable(
    caption = "Cluster Profiles — Product Sales Segments",
    col.names = c("Segment", "# Products", "Avg Units Sold",
                  "Avg Price (R$)", "Avg Revenue (R$)", "Freight Ratio")
  ) %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Cluster Profiles — Product Sales Segments
Segment	# Products	Avg Units Sold	Avg Price (R\() </th> <th style="text-align:right;"> Avg Revenue (R\))	Freight Ratio
Dead Weight	13880	1.5	69.5	97.8	0.272
Low Performers	9560	1.7	339.6	549.6	0.111
Mid-range Steady	5316	2.0	23.0	41.7	0.853
Bestsellers	4195	15.4	108.1	1611.0	0.276

4.5 Cluster Visualisation

# PCA for 2D visualisation (no extra packages needed)
pca_result <- prcomp(cluster_matrix, scale. = FALSE)
pca_df <- as.data.frame(pca_result$x[, 1:2])
pca_df$cluster <- cluster_data$cluster
pca_df$label   <- cluster_data$label

# Sample for readable plot
set.seed(42)
pca_sample <- pca_df[sample(nrow(pca_df), min(3000, nrow(pca_df))), ]

ggplot(pca_sample, aes(x = PC1, y = PC2, color = label)) +
  geom_point(alpha = 0.4, size = 1.2) +
  stat_ellipse(level = 0.85, linewidth = 1) +
  scale_color_manual(values = c("Dead Weight"="#F44336", "Low Performers"="#FF9800",
                                 "Mid-range Steady"="#2196F3", "Bestsellers"="#4CAF50")) +
  labs(
    title = "Product Clusters — PCA Projection",
    subtitle = "Each point is a product; axes are principal components of sales features",
    color = "Segment", x = "PC1", y = "PC2"
  ) +
  theme_minimal(base_size = 13) +
  theme(plot.title = element_text(face = "bold", size = 15))

cluster_data %>%
  count(label) %>%
  mutate(pct = n / sum(n) * 100) %>%
  ggplot(aes(x = reorder(label, n), y = n, fill = label)) +
  geom_col(width = 0.7) +
  geom_text(aes(label = paste0(comma(n), " (", round(pct,1), "%)")),
            hjust = -0.1, size = 4) +
  coord_flip() +
  scale_fill_manual(values = c("Dead Weight"="#F44336","Low Performers"="#FF9800",
                                "Mid-range Steady"="#2196F3","Bestsellers"="#4CAF50")) +
  scale_y_continuous(expand = expansion(mult = c(0, 0.2))) +
  labs(title = "Products by Segment", x = NULL, y = "Number of Products") +
  theme_minimal(base_size = 13) +
  theme(legend.position = "none",
        plot.title = element_text(face = "bold"))

5. Supervised Learning — Random Forest

5.1 Goal

Can we predict whether a product will be a high-seller based on its features?

We define high-seller as a product in the top 25% by units sold (the “Bestseller” zone).

5.2 Feature Engineering

# Join product features + sales
rf_data <- product_sales %>%
  left_join(products %>% select(
    product_id, category,
    product_photos_qty,
    product_description_lenght,
    product_weight_g,
    product_length_cm,
    product_height_cm,
    product_width_cm
  ), by = "product_id") %>%
  drop_na() %>%
  mutate(
    # Target: top 25% by units sold = high seller
    high_seller = factor(ifelse(units_sold >= quantile(units_sold, 0.75), "Yes", "No")),
    # Features
    freight_ratio   = avg_freight / (avg_price + 0.01),
    log_price       = log1p(avg_price),
    volume_cm3      = product_length_cm * product_height_cm * product_width_cm,
    # Top category flag
    top_category    = factor(ifelse(category %in% 
                       c("bed_bath_table","health_beauty","sports_leisure",
                         "furniture_decor","computers_accessories"), "top5", "other"))
  ) %>%
  select(high_seller, log_price, freight_ratio, product_photos_qty,
         product_description_lenght, product_weight_g, volume_cm3, top_category)

cat("RF dataset rows:", nrow(rf_data), "\n")

## RF dataset rows: 32340

cat("Class balance:\n")

## Class balance:

print(table(rf_data$high_seller))

## 
##    No   Yes 
## 23445  8895

5.3 Train / Test Split

set.seed(42)
train_idx <- createDataPartition(rf_data$high_seller, p = 0.75, list = FALSE)
train_set  <- rf_data[train_idx, ]
test_set   <- rf_data[-train_idx, ]

cat("Train:", nrow(train_set), "| Test:", nrow(test_set), "\n")

## Train: 24256 | Test: 8084

5.4 Train Random Forest

set.seed(42)
rf_model <- randomForest(
  high_seller ~ .,
  data       = train_set,
  ntree      = 300,
  mtry       = 3,
  importance = TRUE
)

print(rf_model)

## 
## Call:
##  randomForest(formula = high_seller ~ ., data = train_set, ntree = 300,      mtry = 3, importance = TRUE) 
##                Type of random forest: classification
##                      Number of trees: 300
## No. of variables tried at each split: 3
## 
##         OOB estimate of  error rate: 26.74%
## Confusion matrix:
##        No  Yes class.error
## No  16756  828  0.04708826
## Yes  5658 1014  0.84802158

5.5 Model Evaluation

pred       <- predict(rf_model, test_set)
conf_mat   <- confusionMatrix(pred, test_set$high_seller, positive = "Yes")

# Print key metrics
metrics <- tibble(
  Metric    = c("Accuracy", "Sensitivity (Recall)", "Specificity", "Precision", "F1 Score"),
  Value     = c(
    conf_mat$overall["Accuracy"],
    conf_mat$byClass["Sensitivity"],
    conf_mat$byClass["Specificity"],
    conf_mat$byClass["Precision"],
    conf_mat$byClass["F1"]
  )
) %>%
  mutate(Value = round(Value, 3))

metrics %>%
  kable(caption = "Random Forest — Test Set Performance") %>%
  kable_styling(bootstrap_options = c("striped","hover","condensed"), full_width = FALSE)

Random Forest — Test Set Performance
Metric	Value
Accuracy	0.737
Sensitivity (Recall)	0.144
Specificity	0.962
Precision	0.587
F1 Score	0.231

5.6 Feature Importance

imp_df <- importance(rf_model) %>%
  as.data.frame() %>%
  rownames_to_column("Feature") %>%
  arrange(desc(MeanDecreaseGini))

ggplot(imp_df, aes(x = reorder(Feature, MeanDecreaseGini), y = MeanDecreaseGini)) +
  geom_col(fill = "#4CAF50", width = 0.7) +
  geom_text(aes(label = round(MeanDecreaseGini, 1)), hjust = -0.2, size = 3.8) +
  coord_flip() +
  scale_y_continuous(expand = expansion(mult = c(0, 0.2))) +
  labs(
    title = "What Makes a Product Sell? — Feature Importance",
    subtitle = "Mean Decrease in Gini — higher = more important for classification",
    x = NULL, y = "Mean Decrease Gini"
  ) +
  theme_minimal(base_size = 13) +
  theme(plot.title = element_text(face = "bold", size = 15))

6. Key Business Insights

insights <- tibble(
  `#` = 1:6,
  Finding = c(
    "The top 5 categories (bed_bath_table, health_beauty, sports_leisure, furniture_decor, computers_accessories) account for ~40% of all units sold.",
    "55% of products sold only once — the platform has extreme long-tail distribution typical of large marketplaces.",
    "Watches & gifts and health_beauty generate disproportionate revenue relative to units sold — high average price boosts margins.",
    "Fashion (childrens, sport, female), music, and security & services are nearly invisible — likely wrong platform fit.",
    "Product price (log_price) is the strongest predictor of whether a product will sell well — mid-range pricing outperforms both budget and premium extremes.",
    "High freight ratio (shipping cost vs price) is a negative signal — products expensive to ship relative to their price struggle to find buyers."
  ),
  Recommendation = c(
    "Prioritise inventory and promotional spend in these core categories.",
    "Investigate one-time-sale products — are they mispriced, poorly described, or hard to find?",
    "Expand premium gifting and beauty categories — higher margin with moderate volume.",
    "Consider de-listing or restructuring fashion & music sub-categories — or investigate platform discoverability issues.",
    "Guide new sellers toward competitive mid-range price points; avoid extreme ends.",
    "Help sellers optimise packaging weight and dimensions to reduce freight costs and improve conversion."
  )
)

insights %>%
  kable(caption = "Business Insights & Recommendations") %>%
  kable_styling(bootstrap_options = c("striped","hover"), full_width = TRUE) %>%
  column_spec(2, width = "45%") %>%
  column_spec(3, width = "45%")

Business Insights & Recommendations
#	Finding	Recommendation
1	The top 5 categories (bed_bath_table, health_beauty, sports_leisure, furniture_decor, computers_accessories) account for ~40% of all units sold.	Prioritise inventory and promotional spend in these core categories.
2	55% of products sold only once — the platform has extreme long-tail distribution typical of large marketplaces.	Investigate one-time-sale products — are they mispriced, poorly described, or hard to find?
3	Watches & gifts and health_beauty generate disproportionate revenue relative to units sold — high average price boosts margins.	Expand premium gifting and beauty categories — higher margin with moderate volume.
4	Fashion (childrens, sport, female), music, and security & services are nearly invisible — likely wrong platform fit.	Consider de-listing or restructuring fashion & music sub-categories — or investigate platform discoverability issues.
5	Product price (log_price) is the strongest predictor of whether a product will sell well — mid-range pricing outperforms both budget and premium extremes.	Guide new sellers toward competitive mid-range price points; avoid extreme ends.
6	High freight ratio (shipping cost vs price) is a negative signal — products expensive to ship relative to their price struggle to find buyers.	Help sellers optimise packaging weight and dimensions to reduce freight costs and improve conversion.

7. Summary

Analysis	Method	Key Output
Sales distribution	Descriptive	55% of products sold only once
Category ranking	Bar charts	Top 5 categories = 40% of volume
Revenue vs volume	Scatter	Health & beauty + watches = price premium
Product segmentation	K-Means (k=4)	4 segments: Dead Weight → Bestsellers
Sales prediction	Random Forest	~80%+ accuracy; price & freight ratio matter most

Bottom line: Olist’s sales are highly concentrated. A handful of categories and a small share of products drive the majority of volume and revenue. Most products sit in a “long tail” that rarely converts. Platform strategy should focus on surfacing high-potential mid-range products while reconsidering investment in categories that chronically underperform.

Analysis by Abdussomad Olayiwola, Akash M, Hue, Nathaniel YO, Kaan Ö | Spinnaker Program | Warsaw June 2026

Sales Structure Analysis

Which products find customers and which don’t?

Group Abdussomad Olayiwola, Akash M, Hue, Nathaniel YO, Kaan Ö

2026-06-23