🎉 Get Started for Free! Sign up today and activate your Free Plan—no credit card required!

🚀 Launching Private Beta for Startups: Get in touch!

✨ Schedule a Demo Today and Discover How Autonmis Can Empower Your Workflow!

🎉 Get Started for Free! Sign up today and activate your Free Plan—no credit card required!

🚀 Launching Private Beta for Startups: Get in touch!

✨ Schedule a Demo Today and Discover How Autonmis Can Empower Your Workflow!

🎉 Get Started for Free! Sign up today and activate your Free Plan—no credit card required!

🚀 Launching Private Beta for Startups: Get in touch!

✨ Schedule a Demo Today and Discover How Autonmis Can Empower Your Workflow!

🎉 Get Started for Free! Sign up today and activate your Free Plan—no credit card required!

🚀 Launching Private Beta for Startups: Get in touch!

✨ Schedule a Demo Today and Discover How Autonmis Can Empower Your Workflow!

Back

11/16/2024

Sachin Chandra

Unlocking Retail Success: A Comprehensive Analysis of Business Metrics

This notebook provides a thorough analysis of essential retail metrics, leveraging SQL to extract valuable insights from the data. It explores sales trends, customer rete...

INTRODUCTION

In today’s dynamic retail environment, understanding and leveraging key business metrics is essential for sustained success. This analysis delves into various aspects of retail performance, including sales trends, customer behavior, product performance, seasonal fluctuations, and financial metrics. By employing data-driven strategies, this report aims to provide actionable insights that can enhance decision-making, optimize inventory management, and improve overall profitability. Join us as we explore the intricate relationships among these metrics and uncover opportunities for growth and efficiency in the retail sector.

Step 1: Sales Overview

Goal

To get an initial snapshot of the retail business’s overall sales performance, focusing on total sales, number of orders, and average order value. This provides a foundation to understand the company’s scale and baseline trends.

1. Calculate Total Sales, Total Orders, and Average Order Value

1  SELECT SUM(totalamount) AS total_sales FROM orders;

SELECT SUM(totalamount) AS total_sales FROM orders;

1  SELECT COUNT(orderid) AS total_orders FROM orders;

SELECT COUNT(orderid) AS total_orders FROM orders;

1  SELECT AVG(totalamount) AS avg_order_value FROM orders;

SELECT AVG(totalamount) AS avg_order_value FROM orders;

2. Monthly Sales Trends

• To identify sales patterns, aggregate sales by month to see if there are any recurring trends or seasonal spikes.

1  #sql
2  SELECT DATE_TRUNC('month', orderdate) AS month,
3     SUM(totalamount)::NUMERIC AS monthly_sales
4  FROM orders
5  GROUP BY month
6  ORDER BY month;

#sql
SELECT DATE_TRUNC('month', orderdate) AS month,
   SUM(totalamount)::NUMERIC AS monthly_sales
FROM orders
GROUP BY month
ORDER BY month;

3. Product-wise Discount Analysis

1  #sql
2  SELECT 
3      P.productname AS product_name, 
4      SUM(OI.unitprice * OI.discount)::int AS total_discount 
5  FROM 
6      OrderItems OI 
7  JOIN 
8      Products P ON OI.productid = P.productid
9  GROUP BY P.productname;

#sql
SELECT 
    P.productname AS product_name, 
    SUM(OI.unitprice * OI.discount)::int AS total_discount 
FROM 
    OrderItems OI 
JOIN 
    Products P ON OI.productid = P.productid
GROUP BY P.productname;

This analysis calculates the total discounted amount per product, providing insights into discount distribution across different items. Grouping by product name highlights which products benefit most from discounts and can inform pricing or promotional strategies.

Step 2: Customer Behavior Analysis

Goal

To gain insights into customer behavior by analyzing spending patterns and order frequency. This step helps us understand which customers are most engaged, how often they make purchases, and their average spend. These metrics are valuable for creating targeted marketing and customer retention strategies.

1. Total Spend and Average Order Value per Customer

• Calculate the total spending and average order value for each customer. This can help identify high-spending customers.

1  #sql
2  SELECT customerid,
3         COUNT(orderid) AS order_count,
4         SUM(totalamount) AS total_spend,
5         AVG(totalamount) AS avg_order_value
6  FROM orders
7  GROUP BY customerid
8  ORDER BY total_spend DESC;

#sql
SELECT customerid,
       COUNT(orderid) AS order_count,
       SUM(totalamount) AS total_spend,
       AVG(totalamount) AS avg_order_value
FROM orders
GROUP BY customerid
ORDER BY total_spend DESC;

This query shows each customer’s total spending, the number of orders they’ve placed, and their average order value. By sorting customers by total spending, we can identify high-value customers who contribute significantly to revenue.

2. Customer Purchase Frequency

• Analyze the frequency of purchases per customer to identify repeat customers and assess engagement.

1  #sql
2  SELECT customerid,
3         COUNT(orderid) AS purchase_frequency
4  FROM orders
5  GROUP BY customerid
6  ORDER BY purchase_frequency DESC;

#sql
SELECT customerid,
       COUNT(orderid) AS purchase_frequency
FROM orders
GROUP BY customerid
ORDER BY purchase_frequency DESC;

Visualizations, like a bar chart of top spenders and a histogram of purchase frequency, help us quickly grasp which customers are most valuable and how engaged they are overall. These insights are foundational for shaping customer loyalty and marketing strategies.

Step 3: Order Status and Payment Method Analysis

Goal

To examine the distribution of order statuses and understand customer preferences for payment methods. Analyzing order statuses can reveal potential bottlenecks or issues in the fulfillment process, while insights on payment methods help identify preferred ways customers transact, which can inform checkout experience optimization.

1. Order Status Distribution

• Calculate the count and percentage of each order status to understand the order lifecycle.

1  #sql
2  SELECT orderstatus,  
3         COUNT(orderid) AS status_count,  
4         (COUNT(orderid) * 100.0 / (SELECT COUNT(*) FROM orders))::NUMERIC AS status_percentage 
5  FROM orders
6  GROUP BY orderstatus
7  ORDER BY status_count DESC;

#sql
SELECT orderstatus,  
       COUNT(orderid) AS status_count,  
       (COUNT(orderid) * 100.0 / (SELECT COUNT(*) FROM orders))::NUMERIC AS status_percentage 
FROM orders
GROUP BY orderstatus
ORDER BY status_count DESC;

2. Payment Method Analysis

• Identify the popularity of each payment method by linking orders with paymentmethods.

1  #sql
2  SELECT p.methodname,
3         COUNT(o.orderid) AS usage_count,
4         (COUNT(o.orderid) * 100.0 / (SELECT COUNT(*) FROM orders))::NUMERIC AS usage_percentage
5  FROM orders o
6  JOIN paymentmethods p ON o.paymentmethodid = p.paymentmethodid
7  GROUP BY p.methodname
8  ORDER BY usage_count DESC;

#sql
SELECT p.methodname,
       COUNT(o.orderid) AS usage_count,
       (COUNT(o.orderid) * 100.0 / (SELECT COUNT(*) FROM orders))::NUMERIC AS usage_percentage
FROM orders o
JOIN paymentmethods p ON o.paymentmethodid = p.paymentmethodid
GROUP BY p.methodname
ORDER BY usage_count DESC;

Step 4: Product Performance and Profitability

Goal

To identify which products drive the most revenue and are most frequently purchased, as well as to assess their profitability. This step provides valuable insights into the best-performing products, helping optimize stock levels and future product offerings.

1. Top-Selling Products by Revenue

• Calculate the total revenue generated by each product by joining OrderItems and Products.

1  #sql
2  SELECT p.productname,  
3         SUM(oi.quantity * oi.unitprice) AS total_revenue,  
4         SUM(oi.quantity) AS total_quantity,  
5         (SUM(oi.quantity * oi.unitprice))::TEXT AS total_revenue_text  
6  FROM orderitems oi
7  JOIN products p ON oi.productid = p.productid
8  GROUP BY p.productname
9  ORDER BY total_revenue DESC;

#sql
SELECT p.productname,  
       SUM(oi.quantity * oi.unitprice) AS total_revenue,  
       SUM(oi.quantity) AS total_quantity,  
       (SUM(oi.quantity * oi.unitprice))::TEXT AS total_revenue_text  
FROM orderitems oi
JOIN products p ON oi.productid = p.productid
GROUP BY p.productname
ORDER BY total_revenue DESC;

2. Most Frequently Purchased Products

• Analyze products by their quantity sold to identify the most popular products.

1  #sql
2  SELECT p.productname, 
3         SUM(oi.quantity) AS total_quantity,  
4         SUM(oi.quantity)::TEXT AS total_quantity_text  
5  FROM orderitems oi
6  JOIN products p ON oi.productid = p.productid
7  GROUP BY p.productname
8  ORDER BY total_quantity DESC;

#sql
SELECT p.productname, 
       SUM(oi.quantity) AS total_quantity,  
       SUM(oi.quantity)::TEXT AS total_quantity_text  
FROM orderitems oi
JOIN products p ON oi.productid = p.productid
GROUP BY p.productname
ORDER BY total_quantity DESC;

3. Discount Usage by Product

• To understand discount usage, calculate the total number of discounted items and total discount amount applied per product.

1  #sql
2  SELECT p.productname,  
3         SUM(oi.discount) AS total_discount,  
4         COUNT(oi.orderitemid) AS discount_count,  
5         SUM(oi.discount)::TEXT AS total_discount_text  
6  FROM orderitems oi
7  JOIN products p ON oi.productid = p.productid
8  WHERE oi.discount > 0
9  GROUP BY p.productname
10  ORDER BY total_discount DESC;

#sql
SELECT p.productname,  
       SUM(oi.discount) AS total_discount,  
       COUNT(oi.orderitemid) AS discount_count,  
       SUM(oi.discount)::TEXT AS total_discount_text  
FROM orderitems oi
JOIN products p ON oi.productid = p.productid
WHERE oi.discount > 0
GROUP BY p.productname
ORDER BY total_discount DESC;

Step 5: Inventory Management and Stock Levels

Goal

To analyze current inventory levels, identify products that are low in stock, and assess overall inventory turnover. This step is crucial for optimizing stock levels, minimizing holding costs, and ensuring that popular products are available for customers.

1. Low Stock Products

• Identify products that are below a certain reorder level. This helps ensure that popular items remain in stock.

1  #sql
2  SELECT p.productname,  
3         p.unitsinstock,  
4         p.reorderlevel,  
5         p.unitsinstock::TEXT AS unitsinstock_text  
6  FROM products p
7  WHERE p.unitsinstock < p.reorderlevel
8  ORDER BY p.unitsinstock ASC;

#sql
SELECT p.productname,  
       p.unitsinstock,  
       p.reorderlevel,  
       p.unitsinstock::TEXT AS unitsinstock_text  
FROM products p
WHERE p.unitsinstock < p.reorderlevel
ORDER BY p.unitsinstock ASC;

2. Inventory Turnover Rate

• Calculate the inventory turnover rate to assess how quickly inventory is sold and replaced over a specific period.

1  #sql
2  SELECT SUM(oi.quantity) AS total_sold,  
3         SUM(p.unitsinstock) AS total_stock,  
4         (SUM(oi.quantity) / NULLIF(SUM(p.unitsinstock), 0))::NUMERIC AS turnover_rate  
5  FROM orderitems oi
6  JOIN products p ON oi.productid = p.productid;

#sql
SELECT SUM(oi.quantity) AS total_sold,  
       SUM(p.unitsinstock) AS total_stock,  
       (SUM(oi.quantity) / NULLIF(SUM(p.unitsinstock), 0))::NUMERIC AS turnover_rate  
FROM orderitems oi
JOIN products p ON oi.productid = p.productid;

3. Trends in Inventory Levels

• Analyze changes in inventory levels over time to identify patterns or seasonal fluctuations.

1  #sql
2  SELECT p.productname, 
3         p.unitsinstock, 
4         p.reorderlevel,  
5         p.unitsinstock::TEXT AS unitsinstock_text  
6  FROM products p
7  WHERE p.unitsinstock < p.reorderlevel
8  ORDER BY p.unitsinstock ASC;

#sql
SELECT p.productname, 
       p.unitsinstock, 
       p.reorderlevel,  
       p.unitsinstock::TEXT AS unitsinstock_text  
FROM products p
WHERE p.unitsinstock < p.reorderlevel
ORDER BY p.unitsinstock ASC;

Step 6: Customer Insights and Behavior Analysis

Goal

To analyze customer purchasing patterns and behavior, identify high-value customers, and assess overall customer retention and satisfaction.

1. High-Value Customers

• Identify customers who generate the most revenue.

1  #sql
2  SELECT c.customerid,  
3         c.firstname || ' ' || c.lastname AS customer_name,  
4         SUM(o.totalamount) AS total_spent,  
5         COUNT(o.orderid) AS total_orders,  
6         SUM(o.totalamount)::TEXT AS total_spent_text  
7  FROM orders o
8  JOIN customers c ON o.customerid = c.customerid  
9  GROUP BY c.customerid, customer_name
10  ORDER BY total_spent DESC
11  LIMIT 10;

#sql
SELECT c.customerid,  
       c.firstname || ' ' || c.lastname AS customer_name,  
       SUM(o.totalamount) AS total_spent,  
       COUNT(o.orderid) AS total_orders,  
       SUM(o.totalamount)::TEXT AS total_spent_text  
FROM orders o
JOIN customers c ON o.customerid = c.customerid  
GROUP BY c.customerid, customer_name
ORDER BY total_spent DESC
LIMIT 10;

2. Customer Purchasing Patterns

• Analyze the frequency of purchases by customers.

1  #sql
2  SELECT c.customerid,  
3         c.firstname || ' ' || c.lastname AS customer_name,  
4         COUNT(o.orderid) AS total_orders,  
5         AVG(o.totalamount) AS average_order_value,  
6         COUNT(o.orderid)::TEXT AS total_orders_text  
7  FROM orders o
8  JOIN customers c ON o.customerid = c.customerid  
9  GROUP BY c.customerid, customer_name
10  ORDER BY total_orders DESC;

#sql
SELECT c.customerid,  
       c.firstname || ' ' || c.lastname AS customer_name,  
       COUNT(o.orderid) AS total_orders,  
       AVG(o.totalamount) AS average_order_value,  
       COUNT(o.orderid)::TEXT AS total_orders_text  
FROM orders o
JOIN customers c ON o.customerid = c.customerid  
GROUP BY c.customerid, customer_name
ORDER BY total_orders DESC;

3. Customer Retention Analysis

• Examine retention rates by comparing repeat purchases over a specific period.

1  #sql
2  WITH customer_orders AS (
3      SELECT customerid,  
4             DATE_TRUNC('year', orderdate) AS order_year,  
5             COUNT(orderid) AS total_orders  
6      FROM orders  
7      GROUP BY customerid, order_year
8  )
9  SELECT co.order_year,  
10         COUNT(DISTINCT co.customerid) AS retained_customers,  
11         (COUNT(DISTINCT co.customerid) / NULLIF((SELECT COUNT(DISTINCT customerid) 
12                                                   FROM orders 
13                                                   WHERE DATE_TRUNC('year', orderdate) = co.order_year - INTERVAL '1 year'), 0)) * 100 AS retention_rate  
14  FROM customer_orders co  
15  GROUP BY co.order_year  
16  ORDER BY co.order_year;

#sql
WITH customer_orders AS (
    SELECT customerid,  
           DATE_TRUNC('year', orderdate) AS order_year,  
           COUNT(orderid) AS total_orders  
    FROM orders  
    GROUP BY customerid, order_year
)
SELECT co.order_year,  
       COUNT(DISTINCT co.customerid) AS retained_customers,  
       (COUNT(DISTINCT co.customerid) / NULLIF((SELECT COUNT(DISTINCT customerid) 
                                                 FROM orders 
                                                 WHERE DATE_TRUNC('year', orderdate) = co.order_year - INTERVAL '1 year'), 0)) * 100 AS retention_rate  
FROM customer_orders co  
GROUP BY co.order_year  
ORDER BY co.order_year;

Step 7: Sales Channel Performance Analysis

Goal

To analyze the performance of different sales channels, evaluate their contributions to overall revenue, and identify areas for improvement.

1. Sales by Channel

• Aggregate total sales by each channel.

1  #sql
2  SELECT s.methodname AS sales_channel,  
3         SUM(o.totalamount) AS total_sales,  
4         COUNT(o.orderid) AS total_orders,  
5         SUM(o.totalamount)::TEXT AS total_sales_text  
6  FROM orders o
7  JOIN paymentmethods s ON o.paymentmethodid = s.paymentmethodid  
8  GROUP BY s.methodname
9  ORDER BY total_sales DESC;

#sql
SELECT s.methodname AS sales_channel,  
       SUM(o.totalamount) AS total_sales,  
       COUNT(o.orderid) AS total_orders,  
       SUM(o.totalamount)::TEXT AS total_sales_text  
FROM orders o
JOIN paymentmethods s ON o.paymentmethodid = s.paymentmethodid  
GROUP BY s.methodname
ORDER BY total_sales DESC;

2. Monthly Sales Trends by Channel

• Analyze sales trends over time for each channel.

1  #sql
2  SELECT DATE_TRUNC('month', o.orderdate) AS month,  
3         s.methodname AS sales_channel,  
4         SUM(o.totalamount) AS total_sales  
5  FROM orders o
6  JOIN paymentmethods s ON o.paymentmethodid = s.paymentmethodid  
7  GROUP BY month, s.methodname  
8  ORDER BY month, total_sales DESC;

#sql
SELECT DATE_TRUNC('month', o.orderdate) AS month,  
       s.methodname AS sales_channel,  
       SUM(o.totalamount) AS total_sales  
FROM orders o
JOIN paymentmethods s ON o.paymentmethodid = s.paymentmethodid  
GROUP BY month, s.methodname  
ORDER BY month, total_sales DESC;

3. Performance Comparison of Channels

• Evaluate performance metrics like average order value across channels.

1  #sql
2  SELECT s.methodname AS sales_channel,  
3         AVG(o.totalamount) AS average_order_value,  
4         COUNT(o.orderid) AS total_orders,  
5         (AVG(o.totalamount)::TEXT) AS average_order_value_text  
6  FROM orders o
7  JOIN paymentmethods s ON o.paymentmethodid = s.paymentmethodid  
8  GROUP BY s.methodname  
9  ORDER BY average_order_value DESC;

#sql
SELECT s.methodname AS sales_channel,  
       AVG(o.totalamount) AS average_order_value,  
       COUNT(o.orderid) AS total_orders,  
       (AVG(o.totalamount)::TEXT) AS average_order_value_text  
FROM orders o
JOIN paymentmethods s ON o.paymentmethodid = s.paymentmethodid  
GROUP BY s.methodname  
ORDER BY average_order_value DESC;

Step 8: Promotional Effectiveness Analysis

Goal

To assess the impact of promotions on sales performance, evaluate which promotions are most effective, and inform future promotional strategies.

1. Sales During Promotional Periods

• Calculate total sales during promotions.

1  #sql
2  SELECT p.promotionname,  
3         SUM(o.totalamount) AS total_sales,  
4         COUNT(o.orderid) AS total_orders,  
5         SUM(o.totalamount)::TEXT AS total_sales_text  
6  FROM orders o
7  JOIN productpromotion pp ON o.orderid = pp.productid  
8  JOIN promotions p ON pp.promotionid = p.promotionid  
9  GROUP BY p.promotionname
10  ORDER BY total_sales DESC;

#sql
SELECT p.promotionname,  
       SUM(o.totalamount) AS total_sales,  
       COUNT(o.orderid) AS total_orders,  
       SUM(o.totalamount)::TEXT AS total_sales_text  
FROM orders o
JOIN productpromotion pp ON o.orderid = pp.productid  
JOIN promotions p ON pp.promotionid = p.promotionid  
GROUP BY p.promotionname
ORDER BY total_sales DESC;

2. Customer Response to Promotions

• Evaluate customer purchases during promotions versus non-promotional periods.

1  #sql
2  SELECT CASE WHEN pp.promotionid IS NOT NULL THEN 'Promotional' ELSE 'Non-Promotional' END AS promotion_status,  
3         COUNT(o.orderid) AS total_orders,  
4         SUM(o.totalamount) AS total_sales  
5  FROM orders o
6  LEFT JOIN productpromotion pp ON o.orderid = pp.productid  
7  GROUP BY promotion_status;

#sql
SELECT CASE WHEN pp.promotionid IS NOT NULL THEN 'Promotional' ELSE 'Non-Promotional' END AS promotion_status,  
       COUNT(o.orderid) AS total_orders,  
       SUM(o.totalamount) AS total_sales  
FROM orders o
LEFT JOIN productpromotion pp ON o.orderid = pp.productid  
GROUP BY promotion_status;

3. Average Discount Impact on Sales

• Analyze how the average discount percentage influences total sales.

1  #sql
2  SELECT p.discountpercentage,  
3         SUM(o.totalamount) AS total_sales  
4  FROM orders o
5  JOIN productpromotion pp ON o.orderid = pp.productid  
6  JOIN promotions p ON pp.promotionid = p.promotionid  
7  GROUP BY p.discountpercentage  
8  ORDER BY p.discountpercentage DESC;

#sql
SELECT p.discountpercentage,  
       SUM(o.totalamount) AS total_sales  
FROM orders o
JOIN productpromotion pp ON o.orderid = pp.productid  
JOIN promotions p ON pp.promotionid = p.promotionid  
GROUP BY p.discountpercentage  
ORDER BY p.discountpercentage DESC;

Step 9: Seasonal Trends Analysis

Goal

To identify seasonal patterns in sales and inventory, evaluate the impact of seasonality on business performance, and develop strategies to optimize inventory and marketing efforts.

1. Monthly Sales Trends

• Analyze sales data by month to identify peak seasons.

1  #sql
2  SELECT DATE_TRUNC('month', orderdate) AS month,  
3         SUM(totalamount) AS total_sales  
4  FROM orders  
5  GROUP BY month  
6  ORDER BY month;

#sql
SELECT DATE_TRUNC('month', orderdate) AS month,  
       SUM(totalamount) AS total_sales  
FROM orders  
GROUP BY month  
ORDER BY month;

2. Seasonal Product Performance

• Evaluate how different products perform during peak seasons.

1  #sql
2  SELECT p.productname,  
3         DATE_TRUNC('month', o.orderdate) AS month,  
4         SUM(oi.quantity) AS total_sold  
5  FROM orderitems oi
6  JOIN orders o ON oi.orderid = o.orderid  
7  JOIN products p ON oi.productid = p.productid  
8  GROUP BY p.productname, month  
9  ORDER BY month, total_sold DESC;

#sql
SELECT p.productname,  
       DATE_TRUNC('month', o.orderdate) AS month,  
       SUM(oi.quantity) AS total_sold  
FROM orderitems oi
JOIN orders o ON oi.orderid = o.orderid  
JOIN products p ON oi.productid = p.productid  
GROUP BY p.productname, month  
ORDER BY month, total_sold DESC;

3. Inventory Levels by Season

• Analyze inventory levels against sales to assess turnover rates during peak seasons.

1  #sql
2  SELECT DATE_TRUNC('month', orderdate) AS month,  
3         SUM(p.unitsinstock) AS total_inventory,  
4         SUM(oi.quantity) AS total_sold  
5  FROM orderitems oi
6  JOIN orders o ON oi.orderid = o.orderid  
7  JOIN products p ON oi.productid = p.productid  
8  GROUP BY month  
9  ORDER BY month;

#sql
SELECT DATE_TRUNC('month', orderdate) AS month,  
       SUM(p.unitsinstock) AS total_inventory,  
       SUM(oi.quantity) AS total_sold  
FROM orderitems oi
JOIN orders o ON oi.orderid = o.orderid  
JOIN products p ON oi.productid = p.productid  
GROUP BY month  
ORDER BY month;

Step 10: Financial Metrics Analysis

Goal

To analyze key financial metrics, such as profit margins and overall profitability, evaluate cost structures, and inform financial planning and budgeting.

1. Profit Margin Analysis

• Calculate profit margins for each product.

1  #sql
2  SELECT p.productname,  
3         SUM(oi.quantity * oi.unitprice) AS total_sales,  
4         SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100)) AS total_discount,  
5         (SUM(oi.quantity * oi.unitprice) - SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100))) AS profit,  
6         ((SUM(oi.quantity * oi.unitprice) - SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100))) / NULLIF(SUM(oi.quantity * oi.unitprice), 0)) * 100 AS profit_margin  
7  FROM orderitems oi
8  JOIN products p ON oi.productid = p.productid  
9  LEFT JOIN productpromotion pp ON p.productid = pp.productid  
10  LEFT JOIN promotions pr ON pp.promotionid = pr.promotionid  
11  GROUP BY p.productname  
12  ORDER BY profit_margin DESC;

#sql
SELECT p.productname,  
       SUM(oi.quantity * oi.unitprice) AS total_sales,  
       SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100)) AS total_discount,  
       (SUM(oi.quantity * oi.unitprice) - SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100))) AS profit,  
       ((SUM(oi.quantity * oi.unitprice) - SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100))) / NULLIF(SUM(oi.quantity * oi.unitprice), 0)) * 100 AS profit_margin  
FROM orderitems oi
JOIN products p ON oi.productid = p.productid  
LEFT JOIN productpromotion pp ON p.productid = pp.productid  
LEFT JOIN promotions pr ON pp.promotionid = pr.promotionid  
GROUP BY p.productname  
ORDER BY profit_margin DESC;

2. Total Profit and Revenue Analysis

• Evaluate total revenue and profit generated over a specific period.

1  #sql
2  SELECT DATE_TRUNC('month', orderdate) AS month,  
3         SUM(totalamount) AS total_revenue,  
4         SUM(oi.quantity * oi.unitprice - (oi.discount * oi.unitprice)) AS total_profit  
5  FROM orders o
6  JOIN orderitems oi ON o.orderid = oi.orderid  
7  GROUP BY month  
8  ORDER BY month;

#sql
SELECT DATE_TRUNC('month', orderdate) AS month,  
       SUM(totalamount) AS total_revenue,  
       SUM(oi.quantity * oi.unitprice - (oi.discount * oi.unitprice)) AS total_profit  
FROM orders o
JOIN orderitems oi ON o.orderid = oi.orderid  
GROUP BY month  
ORDER BY month;

3. Cost Structure Evaluation

• Assess overall cost structure by analyzing costs related to products and promotions.

1  #sql
2  SELECT SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100)) AS total_discount_cost,  
3         SUM(oi.quantity * oi.unitprice) AS total_cost  
4  FROM orderitems oi  
5  LEFT JOIN productpromotion pp ON oi.productid = pp.productid  
6  LEFT JOIN promotions pr ON pp.promotionid = pr.promotionid;

#sql
SELECT SUM(oi.quantity * (oi.unitprice * COALESCE(pr.discountpercentage, 0) / 100)) AS total_discount_cost,  
       SUM(oi.quantity * oi.unitprice) AS total_cost  
FROM orderitems oi  
LEFT JOIN productpromotion pp ON oi.productid = pp.productid  
LEFT JOIN promotions pr ON pp.promotionid = pr.promotionid;

Conclusion

This comprehensive analysis of retail business metrics has provided valuable insights into sales performance, customer behavior, product performance, promotional effectiveness, seasonal trends, and financial metrics. By leveraging data-driven strategies, the business can optimize inventory management, enhance customer engagement, and improve overall profitability. Regularly revisiting these analyses will ensure that the business adapts to changing market conditions, maintains competitiveness, and continues to meet customer needs effectively.

Get started with Unlocking Retail Success: A Comprehensive Analysis of Business Metrics

Click below to copy this free template.

Recommended Templates

11/26/2024

Ritik Raj

Financial Risk Analysis

Financial institutions want to assess the risk levels of their portfolios or investments. In this notebook, we perform a comprehensive financial risk analysis of a ...Read more

Optimizing Financial Portfolio Performance

11/22/2024

Sachin Chandra

Optimizing Financial Portfolio Performance: Analyzing Trends, Risk, and Return for Better Asset Allocation

This notebook leverages advanced data analysis techniques to evaluate and optimize a financial portfolio. It offers a comprehensive approach to understanding asset price trends, measuring ...Read more

Simplify your Data Work

For Enterprises, discover how scaleups and SMEs across various industries can leverage Autonmis

to bring down their TCO and effectively manage their Business Analytics stack.

Schedule Demo