A fictional retail company wants to identify which customer segments and geographic markets generate the highest sales and profitability. The goal is to understand where the company's most valuable customers are concentrated in order to support commercial strategy, resource allocation, and future growth opportunities.
The superstore dataset was downloaded from the Kaggle website. The dataset is composed of 10,000 rows and multiple columns containing key metrics such as sales, profits, locations, categories, sub-categories, segments and so on. All this data was shaped and cleaned after being imported into MySQL.
All queries used during this project are updated below:
-- Creating the table structure before the importation:
CREATE TABLE data(
Row_ID INT,
Order_ID VARCHAR(200),
Order_Date DATE,
Ship_Date DATE,
Ship_Mode VARCHAR(200),
Customer_ID VARCHAR(200),
Customer_Name VARCHAR(200),
Segment VARCHAR(200),
Country VARCHAR(200),
City VARCHAR(200),
State VARCHAR(200),
Postal_Code INT,
Region VARCHAR(200),
Product_ID VARCHAR(200),
Category VARCHAR(200),
Sub_Category VARCHAR(200),
Product_Name VARCHAR(200),
Sales DECIMAL(10,2),
Quantity INT,
Discount DECIMAL(10,2),
Profit DECIMAL(10,2)
);
-- Exploratory Data Analysis (EDA):
-- 1st - Checking mistaken sales
SELECT COUNT(*) FROM data WHERE sales IS NULL OR sales = 0 OR sales < 0;
-- 2nd - Deleting mistaken sales
DELETE FROM data WHERE sales IS NULL OR sales = 0 OR sales < 0;
-- 3rd - Checking duplicated data
SELECT
COUNT(row_id),
COUNT(DISTINCT row_id)
FROM customer_project.data;
-- A Common Table Expression (CTE) to rank customers per region:
CREATE TABLE CTE AS
WITH key_customers AS (
SELECT
region,
customer_name,
SUM(sales) AS total_sales,
RANK() OVER (PARTITION BY region ORDER BY SUM(sales) DESC) AS ranking
FROM data
GROUP BY customer_name, region
)
SELECT
region,
customer_name,
total_sales,
ranking
FROM key_customers
WHERE ranking <= 3;
For this project, I used Power Query through Power BI to aggregate the profit margin column to the main table. After the data cleaning process, I set up a dashboard in order to analyze sales and profitability across customer segments, product categories and geographic markets.
| Observations | Findings | Insights | Recommendations |
|---|---|---|---|
| Total sales are $1,937,615.91, total profit climbed to $231,048.65, and the average profit margin is 11.6%. | Almost 90% of sales are consumed by expenses. | The profit margin is good for this sector but could be improved. | The company should focus its activity on selling technology and office supplies. The average profit margin could increase to 15%. |
| The consumer segment represents the company's primary source of revenue, but corporate home office customers also represent a significant share of revenue. | The consumer segment generates the highest share of the total sales. | The company has a strong consumer base, while corporate and home office segments show potential for future growth. | The company should design target marketing campaigns and tailored offers for corporate and home office customers to increase their share of sales. |
| The highest value category is technology, as it provides the biggest sales portion and profit margin. | The most valuable category is technology, as it combines the highest sales and profitability. | Technology is the main driver of both revenue and profitability. | The company should continue to invest in and prioritize the technology category. |
| Sales took place throughout the United States, from 2014 to 2018. | Customers are located throughout the country. | Revenue is geographically diversified. | The company should keep a nationwide commercial presence, but tailor marketing campaigns to the strongest-performing states. |
| The most important customer is Sean Miller, located in the South. This represents 1.22% of the turnover. | Sales are distributed across customers, with no single customer accounting for a significant share of total revenue. | There is no customer dependency. | Continue building a diversified customer base to reduce dependency risk and ensure stable revenue growth. |
| Phones represent the highest-selling product sub-category, making technology the company's strongest commercial category. | Phones are the top-selling sub-category and a key driver of technology category performance. | There is a strong demand for phone products. | The company could implement cross-selling strategies for phone products. On the other hand, the company could optimize its technology portfolio, as machine items provide a negative profit margin. |
| Several sub-categories, including machines, bookcases, tables, binders, and appliances, generate negative profit margins despite contributing to sales. | Some products are affecting overall profitability and could be consuming resources without adding value. | This indicates opportunities to improve profitability through pricing or product portfolio optimization. | The company should review the pricing, discount strategy, or product assortment for unprofitable subcategories such as bookcases, tables, binders, and appliances before considering reducing their presence in the portfolio. |
In conclusion, to answer the question "A fictional retail company wants to identify which customer segments and geographic markets generate the highest sales and profitability. The goal is to understand where the company's most valuable customers are concentrated in order to support commercial strategy, resource allocation and future growth opportunities." Based on my data analysis, the consumer segment purchasing technology products generates the highest sales and profitability. In addition, sales are geographically diversified across the United States.