Product Profit Analysis¶
Introduction¶
This section focuses on analyzing the profitability of our product offerings. We will explore various dimensions of product performance, including profit margins, sales volume, and customer purchasing behavior. This will allow us to identify which products are driving profitability and which may be underperforming and which are dead weight products. That information will be crucial for making informed decisions about inventory management, marketing strategies, and product development.
Product Profit Analysis¶
Profit Margin Analysis¶
11. Which product categories and subcategories drive the highest profit margins, and which are drag on profitability?
Solution: Solving this questions will reveal which product categories and subcategories are the most profitable and which are underperforming. So, we need to calculate financial metrics first, and then group the results by product category and subcategory to identify trends and insights. The following SQL query calculates total revenue, total cost, total profit, and profit margin for each product category and subcategory.
Note: When the financial metrics are calculated, there may be some categories or subcategories with no sales, which would lead to null values. To handle this effectively without corrupting the analysis, we can use the COALESCE function to replace null values with zeros. This ensures that our calculations for profit and profit margin are accurate and that we can still include those categories in our analysis without skewing the results.
Show SQL Code
with product_stats as(
select
sub.ProductSubcategoryName as product_subcategory,
cat.ProductCategoryName as product_category,
cast(sum(fis.salesamount) as decimal(20,3)) as total_revenue,
cast(sum(fis.totalproductcost) as decimal(20,3)) as total_cost
from Products p
left join FactInternetSales fis
on fis.ProductKey = p.ProductKey
inner join ProductSubcategory sub
on p.ProductSubcategoryKey = sub.ProductSubcategoryKey
inner join ProductCategory cat
on sub.ProductCategoryKey = cat.ProductCategoryKey
group by
sub.ProductSubcategoryName,
cat.ProductCategoryName
)
select
product_category,
product_subcategory,
coalesce(total_revenue,0) as total_revenue,
coalesce(total_cost,0) as total_cost,
coalesce(total_revenue - total_cost,0) as total_profit,
coalesce(cast(100.0 * (total_revenue - total_cost) /
nullif(total_revenue,0) as decimal(10,3)),0) as profit_margin
from product_stats
order by
profit_margin desc;
Results:
| product_category | product_subcategory | total_revenue | total_cost | total_profit | profit_margin |
|---|---|---|---|---|---|
| Accessories | Bike Racks | 39360.000 | 14720.640 | 24639.360 | 62.600 |
| Accessories | Bike Stands | 39591.000 | 14807.034 | 24783.966 | 62.600 |
| Accessories | Tires and Tubes | 245529.320 | 91828.569 | 153700.751 | 62.600 |
| Accessories | Cleaners | 7218.600 | 2699.756 | 4518.844 | 62.600 |
| Clothing | Shorts | 71319.810 | 26673.650 | 44646.160 | 62.600 |
| Clothing | Vests | 35687.000 | 13346.938 | 22340.062 | 62.600 |
| Accessories | Hydration Packs | 40307.670 | 15075.098 | 25232.572 | 62.600 |
| Accessories | Helmets | 225335.600 | 84275.772 | 141059.828 | 62.600 |
| Accessories | Fenders | 46619.580 | 17435.681 | 29183.899 | 62.600 |
| Clothing | Gloves | 35020.700 | 13097.799 | 21922.901 | 62.600 |
| Clothing | Socks | 5106.320 | 1909.786 | 3196.534 | 62.600 |
| Accessories | Bottles and Cages | 56798.190 | 21242.842 | 35555.348 | 62.599 |
| Bikes | Mountain Bikes | 9952759.564 | 5439135.458 | 4513624.106 | 45.350 |
| Bikes | Road Bikes | 14520584.036 | 8983284.338 | 5537299.698 | 38.134 |
| Bikes | Touring Bikes | 3844801.050 | 2389928.354 | 1454872.696 | 37.840 |
| Clothing | Caps | 19688.100 | 15159.837 | 4528.263 | 23.000 |
| Clothing | Jerseys | 172950.680 | 133172.024 | 39778.656 | 23.000 |
| Components | Headsets | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Chains | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Pedals | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Cranksets | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Bottom Brackets | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Mountain Frames | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Derailleurs | 0.000 | 0.000 | 0.000 | 0.000 |
| Accessories | Locks | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Handlebars | 0.000 | 0.000 | 0.000 | 0.000 |
| Accessories | Panniers | 0.000 | 0.000 | 0.000 | 0.000 |
| Accessories | Pumps | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Forks | 0.000 | 0.000 | 0.000 | 0.000 |
| Clothing | Bib-Shorts | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Wheels | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Brakes | 0.000 | 0.000 | 0.000 | 0.000 |
| Clothing | Tights | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Road Frames | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Saddles | 0.000 | 0.000 | 0.000 | 0.000 |
| Components | Touring Frames | 0.000 | 0.000 | 0.000 | 0.000 |
| Accessories | Lights | 0.000 | 0.000 | 0.000 | 0.000 |
Insights: The analysis revealed the following insights:
- The "Accessories" category, particularly subcategories like "Bike Racks," "Bike Stands," and "Tires and Tubes," consistently showed high profit margins, indicating strong profitability in these areas.
- The "Clothing" category also demonstrated solid profit margins, with subcategories such as "Shorts" and "Vests" performing well.
- In contrast, the "Bikes" category, while generating significant total profit, had lower profit margins compared to accessories and clothing, suggesting that the cost structure for bikes may be less favorable.
- The "Components" category had no recorded sales, resulting in zero profit and profit margin, which indicates that these products may not be contributing to profitability and could be considered for reevaluation in the product lineup. Don't forget that the highest profit margin does not mean the highest total profit. For example, the "Bikes" category has a lower profit margin than "Accessories," but it generates a much higher total profit due to its larger sales volume. This highlights the importance of considering both profit margin and total profit when evaluating product performance. Profit margin just illustrates the difference between revenue and cost, while total profit takes into account the scale of sales, which can significantly impact overall profitability. Therefore, it's crucial to analyze both metrics to get a comprehensive understanding of product performance and make informed decisions about inventory management and marketing strategies.
High Volume vs High Value Products¶
12. What is the disparity between our "High Volume" products and our "High Value" products?
Solution: To solve this question, we need to give a rank to our products based on their Value - total revenue coming from the product, and Volume - total quantity sold for that product. Then we can compare the top 10 products in each category to see if there are any disparities between high volume and high value products. If the volume rank is significantly different from the value rank, it means that the product is either a high volume product with low value or a high value product with low volume. The main strategy of the companies is mainly getting higher revenue. Therefore, in this analysis, we mainly focus on the high value products and try to increase the volume size of those products. However, we also need to keep an eye on the high volume products, as they can also contribute significantly to total revenue, even if their profit margins are lower. By analyzing the disparity between high volume and high value products, we can identify opportunities to optimize our product offerings and marketing strategies to maximize profitability.
Show SQL Code
with product_agg as(
select
p.ProductKey,
p.ProductName,
sum(salesamount) as product_revenue,
sum(OrderQuantity) as product_quantity
from FactInternetSales fis
join products p
on p.ProductKey = fis.ProductKey
group by
p.ProductKey,
p.ProductName
),
rank_products as (
select
ProductName,
product_revenue,
product_quantity,
row_number() over (order by product_revenue desc) as product_value_rank,
row_number() over (order by product_quantity desc) as product_volume_rank
from product_agg
)
select
ProductName,
product_revenue,
product_quantity,
product_value_rank,
product_volume_rank
from rank_products
where product_value_rank <= 10 or product_volume_rank <= 10
order by product_value_rank asc, product_volume_rank;
Results:
| ProductName | product_revenue | product_quantity | product_value_rank | product_volume_rank |
|---|---|---|---|---|
| Road-150 Red, 48 | 1205876.9900 | 337 | 1 | 44 |
| Road-150 Red, 62 | 1202298.7200 | 336 | 2 | 45 |
| Road-150 Red, 52 | 1080637.5400 | 302 | 3 | 49 |
| Road-150 Red, 56 | 1055589.6500 | 295 | 4 | 51 |
| Road-150 Red, 44 | 1005493.8700 | 281 | 5 | 52 |
| Mountain-200 Black, 42 | 979960.7300 | 427 | 6 | 28 |
| Mountain-200 Silver, 38 | 979035.7800 | 422 | 7 | 29 |
| Mountain-200 Black, 46 | 961600.8100 | 419 | 8 | 30 |
| Mountain-200 Black, 38 | 954715.8400 | 416 | 9 | 31 |
| Mountain-200 Silver, 46 | 930315.9900 | 401 | 10 | 36 |
| Sport-100 Helmet, Red | 78027.7000 | 2230 | 68 | 5 |
| Sport-100 Helmet, Blue | 74353.7500 | 2125 | 70 | 7 |
| Sport-100 Helmet, Black | 72954.1500 | 2085 | 71 | 9 |
| Fender Set - Mountain | 46619.5800 | 2121 | 79 | 8 |
| Water Bottle - 30 oz. | 21177.5600 | 4244 | 129 | 1 |
| Mountain Bottle Cage | 20229.7500 | 2025 | 131 | 10 |
| AWC Logo Cap | 19688.1000 | 2190 | 133 | 6 |
| Mountain Tire Tube | 15444.0500 | 3095 | 136 | 3 |
| Road Tire Tube | 9480.2400 | 2376 | 153 | 4 |
| Patch Kit/8 Patches | 7307.3900 | 3191 | 155 | 2 |
Insights: The analysis revealed the following insights:
- There is a strong disparity between high value and high volume products. The top 10 high value products are predominantly bikes, which have high revenue but relatively lower quantities sold. In contrast, the top 10 high volume products are mostly accessories, which have lower revenue but significantly higher quantities sold.
- The high value products, such as the "Road-150 Red" series and "Mountain-200" series, generate substantial revenue but are sold in smaller quantities, indicating that they are likely premium products with higher price points. These products may require targeted marketing strategies to maintain their profitability while potentially increasing their sales volume.
- The high volume products, such as the "Sport-100 Helmet" and "Water Bottle - 30 oz.," sell in large quantities but contribute less to total revenue, suggesting that they may be more price-sensitive and could benefit from strategies focused on cost reduction or bundling to enhance their profitability.
- The disparity between high value and high volume products highlights the importance of balancing our product portfolio to maximize overall profitability. While high value products can drive significant revenue, high volume products can contribute to steady cash flow and customer engagement. Therefore, it's crucial to analyze both categories and develop strategies that leverage the strengths of each to optimize our product offerings and marketing efforts.
We can use some optimization algorithms to find the optimal price points for high volume products to increase their revenue without significantly reducing their sales quantity. For example, as the control and automation engineering background I can build a linear programming model to determine the optimal price for each high volume product, taking into account factors such as production costs, demand elasticity, and competitive pricing. By optimizing the pricing strategy for high volume products, we can potentially increase their revenue contribution while maintaining or even boosting their sales quantity, ultimately enhancing overall profitability.
Basket Size Analysis¶
13. What is our basket size distribution, and how frequently do customers buy multiple items in a single transaction?
Solution Method: First of all we need to define the unique basket sizes. Basket size means the number of items purchased in a single transaction. To calculate this, we should group the sales data by SalesOrderNumber which represents a unique transaction and then count the total quantity of items in that single transaction. The results will give us all unique transactions and corresponding basket sizes. However, we need to define the number transactions for each basket size. Therefore, we should group by the obtained resutls based on the basket size and then count the number of transactions for each basket size. Finally, we can calculate the percentage of transactions for each basket size to understand how frequently customers buy multiple items in a single transaction. This analysis will provide insights into customer purchasing behavior and help us identify opportunities to encourage larger basket sizes through marketing strategies or promotions.
with bucket_size_calc as(
select
-- first determine bucket size
SalesOrderNumber,
sum(OrderQuantity) as num_items
from FactInternetSales
group by SalesOrderNumber
)
select
num_items as bucket_size,
count(SalesOrderNumber) as num_orders,
cast(100.0 * count(SalesOrderNumber) / sum(count(SalesOrderNumber)) over()
as decimal(6,3)) as order_pct
from bucket_size_calc
group by
num_items
order by num_orders ;
Results:
| bucket_size | num_orders | order_pct |
|---|---|---|
| 8 | 3 | 0.011 |
| 7 | 12 | 0.043 |
| 6 | 109 | 0.394 |
| 5 | 696 | 2.516 |
| 4 | 3138 | 11.345 |
| 3 | 5870 | 21.223 |
| 2 | 8163 | 29.513 |
| 1 | 9668 | 34.954 |
Insights: The analysis revealed the following insights: - Most customers (34.954%) purchase a single item in a transaction, indicating that a significant portion of our sales comes from customers who buy one product at a time. This suggests that there may be opportunities to encourage customers to add more items to their baskets through targeted promotions or cross-selling strategies. - A substantial percentage of transactions (29.513%) involve customers purchasing two items, which indicates that there is already a significant level of multi-item purchasing behavior. This presents an opportunity to further increase the average basket size by promoting complementary products or offering discounts for purchasing multiple items. - The percentage of transactions decreases as the basket size increases, with only a small percentage of customers purchasing more than three items in a single transaction. This suggests that while there is some willingness among customers to purchase multiple items, there may be barriers to larger basket sizes that we can address through marketing strategies, such as offering bundled discounts or creating product packages that encourage customers to buy more items together. - Overall, understanding the basket size distribution provides valuable insights into customer purchasing behavior and can inform strategies to increase average order value and enhance customer engagement by encouraging larger basket sizes through targeted promotions and cross-selling opportunities.
Dead Weight Products¶
14. Are there any "Dead Weight" products in our active catalog that have negligible sales in the most recent year?
Solution Method: To solve this question, we should calculate the financial metrics for each product in the most recent year by filtering the years in the join statements. Then we can identify products with negligible sales by setting thresholds for revenue and quantity. For example, we can consider products with total revenue less than $10,000 or total quantity sold less than 50 units as "Dead Weight" products. By analyzing these products, we can make informed decisions about whether to discontinue them or implement strategies to boost their sales, such as targeted marketing campaigns or bundling them with more popular products. This analysis will help us optimize our product catalog and focus on offerings that contribute more significantly to our overall profitability.
Show SQL Code
select
p.ProductKey,
p.ProductName,
coalesce(sum(fis.SalesAmount),0) as revenue,
coalesce(sum(fis.OrderQuantity),0) as quantity
from Products p
left join FactInternetSales fis
on fis.ProductKey = p.ProductKey
and year(fis.OrderDate) = 2013
where p.Status = 'Current'
group by
p.ProductKey,
p.ProductName
having
coalesce(sum(fis.SalesAmount),0) < 10000
or
coalesce(sum(fis.OrderQuantity),0) < 50
order by
coalesce(sum(fis.SalesAmount),0) desc;
Show SQL code
select
min(ListPrice) as min_price,
avg(ListPrice) as avg_price,
max(ListPrice) as max_price,
STDEV(ListPrice) as std
from products;
-- min price : 2.28
-- avg price : 747.6617
-- max price : 3578.27
with product_stats as (
select
p.ProductKey,
p.ListPrice,
sum(fis.SalesAmount) as revenue,
sum(fis.OrderQuantity) as quantity
from Products p
left join FactInternetSales fis
on fis.ProductKey = p.ProductKey
group by
p.ProductKey,
p.ListPrice
)
select
case
when ListPrice <= 100 then 'Entry-level'
when ListPrice > 1000 then 'Premium'
else 'Standard'
end as 'Price-Tiers',
sum(revenue) as total_revenue,
sum(quantity) as total_units_sold,
cast(100.0 * sum(revenue) / nullif(sum(sum(revenue)) over (),0)
as decimal(10,3))as revenue_pct
from product_stats
group by
case
when ListPrice <= 100 then 'Entry-level'
when ListPrice > 1000 then 'Premium'
else 'Standard'
end
order by
revenue_pct desc;