4. Performance 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.

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:

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.

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:

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:

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.

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;

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;