Business request:
Issue: Rising storage costs and unclear returns on marketing investments.
Goal: Classify the product matrix by revenue contribution (ABC analysis) to prioritize inventory and advertising budgets. We need to understand which products are the business drivers and which are dead weight.
Problem Analysis & Hypotheses
To optimize profits, we need to review our sales structure.
Potential issues:
- Overstocking: We store too much inventory that hardly sells (Category C), tying up capital.
- Out-of-stock Risks: Our key products (Category A) may run out of stock at the wrong time due to lack of focus.
- Marketing Inefficiency: Budgets are spent equally on promoting all products, instead of scaling the success of top sellers.
Analytical Framework: Top-Down Approach
To solve this problem, we will follow the chain from the total sales volume to specific management decisions:
- Aggregation: We sum up the revenue for each unique product based on all closed orders.
- ***Relative Share:*** We calculate the percentage share of each product in the company's total turnover.
- ***Cumulative Calculation:*** Calculate the Cumulative Share to rank products from most profitable to least profitable.
- ***Classification:*** We assign categories (A, B, C) based on the classic Pareto rule (80/15/5).
- Cross-Check: We compare the results with the return data from Case #1 to identify “toxic” products (low revenue + high returns).
- Business Insights & Decision
Deep Dive: Aggregation
SQL request:
SELECT p.product_name, SUM(o.quantity*p.sale_price) AS total_revenue
FROM orders AS o
JOIN products AS p
ON o.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_revenue DESC;