1 Sales Performance and Profit Optimization Analysis

This report analyzes retail sales data to evaluate business performance and identify opportunities to improve profitability.

2 Load Required Libraries

library(tidyverse)
library(lubridate)
library(scales)
library(janitor)

3 Import Dataset

sales_data <- read.csv("superstore_sales.csv", stringsAsFactors = FALSE)

sales_data <- clean_names(sales_data)

str(sales_data)
## 'data.frame':    9994 obs. of  21 variables:
##  $ row_id       : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ order_id     : chr  "CA-2016-152156" "CA-2016-152156" "CA-2016-138688" "US-2015-108966" ...
##  $ order_date   : chr  "11/8/2016" "11/8/2016" "6/12/2016" "10/11/2015" ...
##  $ ship_date    : chr  "11/11/2016" "11/11/2016" "6/16/2016" "10/18/2015" ...
##  $ ship_mode    : chr  "Second Class" "Second Class" "Second Class" "Standard Class" ...
##  $ customer_id  : chr  "CG-12520" "CG-12520" "DV-13045" "SO-20335" ...
##  $ customer_name: chr  "Claire Gute" "Claire Gute" "Darrin Van Huff" "Sean O'Donnell" ...
##  $ segment      : chr  "Consumer" "Consumer" "Corporate" "Consumer" ...
##  $ country      : chr  "United States" "United States" "United States" "United States" ...
##  $ city         : chr  "Henderson" "Henderson" "Los Angeles" "Fort Lauderdale" ...
##  $ state        : chr  "Kentucky" "Kentucky" "California" "Florida" ...
##  $ postal_code  : int  42420 42420 90036 33311 33311 90032 90032 90032 90032 90032 ...
##  $ region       : chr  "South" "South" "West" "South" ...
##  $ product_id   : chr  "FUR-BO-10001798" "FUR-CH-10000454" "OFF-LA-10000240" "FUR-TA-10000577" ...
##  $ category     : chr  "Furniture" "Furniture" "Office Supplies" "Furniture" ...
##  $ sub_category : chr  "Bookcases" "Chairs" "Labels" "Tables" ...
##  $ product_name : chr  "Bush Somerset Collection Bookcase" "Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back" "Self-Adhesive Address Labels for Typewriters by Universal" "Bretford CR4500 Series Slim Rectangular Table" ...
##  $ sales        : num  262 731.9 14.6 957.6 22.4 ...
##  $ quantity     : int  2 3 2 5 2 7 4 6 3 5 ...
##  $ discount     : num  0 0 0 0.45 0.2 0 0 0.2 0.2 0 ...
##  $ profit       : num  41.91 219.58 6.87 -383.03 2.52 ...

4 Data Cleaning

sales_data$order_date <- mdy(sales_data$order_date)
sales_data$ship_date <- mdy(sales_data$ship_date)

colSums(is.na(sales_data))
##        row_id      order_id    order_date     ship_date     ship_mode 
##             0             0             0             0             0 
##   customer_id customer_name       segment       country          city 
##             0             0             0             0             0 
##         state   postal_code        region    product_id      category 
##             0             0             0             0             0 
##  sub_category  product_name         sales      quantity      discount 
##             0             0             0             0             0 
##        profit 
##             0
sales_data <- distinct(sales_data)

sales_data$sales <- as.numeric(sales_data$sales)
sales_data$profit <- as.numeric(sales_data$profit)
sales_data$discount <- as.numeric(sales_data$discount)
sales_data$quantity <- as.numeric(sales_data$quantity)

5 Key Performance Indicators (KPIs)

total_sales <- sum(sales_data$sales)
total_profit <- sum(sales_data$profit)
total_orders <- n_distinct(sales_data$order_id)

profit_margin <- total_profit / total_sales

kpi_table <- data.frame(
Total_Sales = total_sales,
Total_Profit = total_profit,
Total_Orders = total_orders,
Profit_Margin = profit_margin
)

kpi_table

6 Regional Sales Performance

region_summary <- sales_data %>%
group_by(region) %>%
summarise(
total_sales = sum(sales),
total_profit = sum(profit)
)

region_summary

6.1 Sales by Region

ggplot(region_summary, aes(x = reorder(region, total_sales), y = total_sales)) +
geom_col() +
coord_flip() +
scale_y_continuous(labels = comma) +
labs(
title = "Total Sales by Region",
x = "Region",
y = "Total Sales"
)

7 Profit Margin by Category

category_profit <- sales_data %>%
group_by(category) %>%
summarise(
total_sales = sum(sales),
total_profit = sum(profit)
) %>%
mutate(profit_margin = total_profit / total_sales)

category_profit

7.1 Category Profit Margin Plot

ggplot(category_profit, aes(x = category, y = profit_margin)) +
geom_col() +
scale_y_continuous(labels = percent) +
labs(
title = "Profit Margin by Category",
x = "Category",
y = "Profit Margin"
)

8 Customer Segmentation Analysis

segment_summary <- sales_data %>%
group_by(segment) %>%
summarise(
total_sales = sum(sales),
total_profit = sum(profit),
total_orders = n()
)

segment_summary

8.1 Sales by Customer Segment

ggplot(segment_summary, aes(x = segment, y = total_sales)) +
geom_col() +
scale_y_continuous(labels = comma) +
labs(
title = "Sales by Customer Segment",
x = "Customer Segment",
y = "Total Sales"
)

9 Discount vs Profit Relationship

ggplot(sales_data, aes(x = discount, y = profit)) +
geom_point(alpha = 0.4) +
geom_smooth(method = "lm") +
labs(
title = "Discount vs Profit Relationship",
x = "Discount",
y = "Profit"
)

10 Key Business Insights

The analysis reveals several critical insights into the organization’s sales performance and profitability dynamics:

  • Regional Performance Disparities Sales and profitability are unevenly distributed across regions, indicating varying market demand, operational efficiency, and customer behavior. Some regions contribute significantly to revenue but may not proportionally contribute to profit, suggesting inefficiencies or high operational costs.

  • Category-Level Profitability Differences Product categories exhibit distinct profit margin patterns. While certain categories generate high sales volumes, their profitability may be constrained by higher costs or aggressive discounting. Conversely, some categories, though lower in sales volume, deliver stronger margins and contribute more effectively to overall profitability.

  • Customer Segment Contribution Customer segments differ notably in purchasing behavior, order frequency, and profitability contribution. Some segments drive high revenue through frequent purchases, while others may offer better margins despite lower sales volume. This highlights the importance of segment-specific strategies.

  • Impact of Discounting on Profitability The analysis clearly demonstrates a negative relationship between discount levels and profit. Excessive discounting erodes profit margins and, in some cases, leads to losses. This suggests that current discount strategies may not be optimally aligned with profitability goals.

  • Order Volume vs Profit Trade-off Higher order volumes do not always translate into increased profitability. In some cases, increased sales driven by discounts or low-margin products may dilute overall profit performance.

11 Recommendations

Based on the findings, the following strategic recommendations are proposed to enhance business performance and profitability:

  • Manage Discounts Carefully Avoid unnecessary or excessive discounts, especially on low-profit products. Ensure promotions are targeted and still profitable.

  • Focus on High-Profit Products Give more attention to products that generate higher profit by prioritizing them in marketing, sales, and inventory decisions.

  • Apply Region-Specific Strategies Study underperforming regions to identify issues and improve them, while strengthening and expanding strategies in high-performing regions.

  • Use Customer Segmentation Treat customers based on their value. Retain high-value customers with loyalty programs and manage less profitable ones more efficiently.

  • Improve Pricing and Control Costs Regularly review pricing and reduce unnecessary costs to protect and increase profit margins.

  • Use Data for Better Decisions Track key performance indicators (KPIs) and use data analysis to guide business decisions and improve overall performance.

12 Conclusion

The analysis demonstrates that while the business achieves strong sales performance, profitability is constrained by inefficient discounting, uneven regional performance, and suboptimal product mix. Sustainable growth requires a shift from revenue-driven to profit-driven strategies. By optimizing pricing, focusing on high-margin opportunities, and adopting targeted regional and customer approaches, the business can significantly improve overall financial performance.