Business request:

The Nordic Sphere manager noticed that revenue was growing, but net profit was stagnant.

Problem Analysis & Hypotheses

Business Issue:

Revenue is growing, but net profit is stagnating. This means operating expenses are growing faster than revenue.

Hypothesis 1.1:

Returns & Logistics

• Growing sales into new regions could lead to an increase in returns. Each return doubles logistics costs and lost marketing expenses. Even one return can wipe out the profit from three successful sales.

Hypothesis 1.2:

Product Mix (Assortment Shift).

• We started selling more low-margin items (like decorative candles) and less high-margin items (like sofas). We're making some cash, but the profit from each sale is pennies.

Returns & Logistics🚚

Analytical Framework: Top-Down Approach

1. Level 1: Geography. We're looking to see if there's a specific country dragging down the overall statistics.
2. Level 2: Categories. We'll check which category experienced a decline in profitability (Furniture vs. Decor).
3. Level 3: Root Cause. We investigate the reasons for returns (defective product, color, slow delivery).

Deep Dive: Geography

SQL request:

SELECT 
    c.country,
    COUNT(o.order_id) AS total_orders,
    COUNT(r.return_id) AS total_returns,
    ROUND(COUNT(r.return_id)::numeric / COUNT(o.order_id) * 100, 2) AS return_rate_percent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN returns r ON o.order_id = r.order_id
GROUP BY c.country
ORDER BY return_rate_percent DESC;

Untitled