Strategic Analysis for Balanced Tree – SQL Case Study

For complete Presentation and the Outputs, follow the below link:

https://github.com/SakshiYadav13/Strategic-Analysis-for-Balanced-Tree-SQL-Case-Study/blob/main/8WEEKSSQLCHALLENGE.COM.pptx

1. Introduction

Balanced Tree Clothing Company specializes in providing a curated selection of clothing and lifestyle wear for modern adventurers. To maintain a competitive edge, the company seeks to analyze its sales performance, understand customer behaviors, and generate financial reports to inform business decisions. Danny, the CEO, emphasizes the need for actionable insights to enhance overall business operations.

2. Objective

The primary objective of this report is to analyze sales data to uncover trends and insights that can inform merchandising strategies and financial planning. Specific goals include:

• Assessing overall sales performance and revenue generation.
• Understanding customer transaction behaviors.
• Identifying top-selling products and categories.
• Evaluating the impact of discounts on sales and revenue.

3. Data Overview

The analysis utilizes two primary datasets:

• Product Details (balanced_tree.product_details): Contains product-specific information including product IDs, names, categories, and segments.
• Product Sales (balanced_tree.sales): Includes transaction-level data, capturing quantity sold, transaction prices, discounts, member status, and timestamps of sales.

4. Approach & Methodology

The analysis follows these key steps:

Data Extraction: Retrieve relevant data from the product details and sales tables.

Data Transformation: Prepare and clean the data as needed for analysis, ensuring data types align and missing values are addressed.

Data Analysis: Execute SQL queries to derive insights based on predefined business questions.

Reporting: Summarize findings and insights in a structured format to facilitate decision-making.

5. SQL Queries

To analyze the sales performance and gain insights into customer behavior, several SQL queries were executed. The following queries provide valuable metrics:

1. What was the total quantity sold for all products? SELECT SUM(qty) AS total_quantity_sold FROM balanced_tree.sales;
2. What is the total generated revenue for all products before discounts? SELECT SUM(qty * price) AS total_revenue_before_discount FROM balanced_tree.sales;
3. What was the total discount amount for all products? SELECT SUM(discount) AS total_discount_amount FROM balanced_tree.sales;

Transaction Analysis

4. How many unique transactions were there? SELECT COUNT(DISTINCT txn_id) AS unique_transactions FROM balanced_tree.sales;
5. What is the average unique products purchased in each transaction? SELECT ROUND(AVG(product_count), 2) AS avg_unique_products_per_transaction FROM (SELECT txn_id, COUNT(DISTINCT prod_id) AS product_count FROM balanced_tree.sales GROUP BY txn_id) AS transaction_product_counts;
6. What are the 25th, 50th, and 75th percentile values for the revenue per transaction? WITH transaction_revenue AS (SELECT txn_id, SUM(qty * price) AS transaction_total_revenue FROM balanced_tree.sales GROUP BY txn_id) SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY transaction_total_revenue) AS percentile_25, PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY transaction_total_revenue) AS percentile_50, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY transaction_total_revenue) AS percentile_75 FROM transaction_revenue;
7. What is the average discount value per transaction? SELECT ROUND(AVG(transaction_discount), 2) AS avg_discount_per_transaction FROM (SELECT txn_id, SUM(discount) AS transaction_discount FROM balanced_tree.sales GROUP BY txn_id) AS transaction_discounts;
8. What is the percentage split of all transactions for members vs non-members? SELECT member, ROUND(COUNT(DISTINCT txn_id) * 100.0 / (SELECT COUNT(DISTINCT txn_id) FROM balanced_tree.sales), 2) AS percentage_split FROM balanced_tree.sales GROUP BY member;
9. What is the average revenue for member transactions and non-member transactions? SELECT member, ROUND(AVG(qty * price), 2) AS avg_revenue_per_transaction FROM balanced_tree.sales GROUP BY member;

Product Analysis

10. What are the top 3 products by total revenue before discount? SELECT pd.product_name, SUM(s.qty * s.price) AS total_revenue_before_discount FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.product_name ORDER BY total_revenue_before_discount DESC LIMIT 3;
11. What is the total quantity, revenue, and discount for each segment? SELECT pd.segment_name, SUM(s.qty) AS total_quantity, SUM(s.qty * s.price) AS total_revenue, SUM(s.discount) AS total_discount FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.segment_name;
12. What is the top selling product for each segment? WITH ranked_products AS (SELECT pd.segment_name, pd.product_name, SUM(s.qty) AS total_quantity_sold, ROW_NUMBER() OVER (PARTITION BY pd.segment_name ORDER BY SUM(s.qty) DESC) AS rank FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.segment_name, pd.product_name) SELECT segment_name, product_name, total_quantity_sold FROM ranked_products WHERE rank = 1;
13. What is the total quantity, revenue, and discount for each category? SELECT pd.category_name, SUM(s.qty) AS total_quantity, SUM(s.qty * s.price) AS total_revenue, SUM(s.discount) AS total_discount FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.category_name;
14. What is the top selling product for each category? WITH ranked_products AS (SELECT pd.category_name, pd.product_name, SUM(s.qty) AS total_quantity_sold, ROW_NUMBER() OVER (PARTITION BY pd.category_name ORDER BY SUM(s.qty) DESC) AS rank FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.category_name, pd.product_name) SELECT category_name, product_name, total_quantity_sold FROM ranked_products WHERE rank = 1;
15. What is the percentage split of revenue by product for each segment? SELECT pd.segment_name, pd.product_name, ROUND(SUM(s.qty * s.price) * 100.0 / SUM(SUM(s.qty * s.price)) OVER (PARTITION BY pd.segment_name), 2) AS revenue_percentage FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.segment_name, pd.product_name;
16. What is the percentage split of revenue by segment for each category? SELECT pd.category_name, pd.segment_name, ROUND(SUM(s.qty * s.price) * 100.0 / SUM(SUM(s.qty * s.price)) OVER (PARTITION BY pd.category_name), 2) AS segment_revenue_percentage FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.category_name, pd.segment_name;
17. What is the percentage split of total revenue by category? SELECT pd.category_name, ROUND(SUM(s.qty * s.price) * 100.0 / SUM(SUM(s.qty * s.price)) OVER (), 2) AS category_revenue_percentage FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.category_name;
18. What is the total transaction “penetration” for each product? SELECT pd.product_name, ROUND(COUNT(DISTINCT s.txn_id) * 100.0 / (SELECT COUNT(DISTINCT txn_id) FROM balanced_tree.sales), 2) AS penetration_percentage FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY pd.product_name;
19. What is the most common combination of at least 1 quantity of any 3 products in a single transaction? WITH transaction_combinations AS (SELECT txn_id, STRING_AGG(pd.product_name, ', ') AS product_combination FROM balanced_tree.sales s JOIN balanced_tree.product_details pd ON s.prod_id = pd.product_id GROUP BY txn_id) SELECT product_combination, COUNT(*) AS combination_count FROM transaction_combinations GROUP BY product_combination ORDER BY combination_count DESC LIMIT 1;

6. Challenges & Issues Faced

The analysis process encountered several challenges that impacted data accuracy and efficiency. These included:

1. Data Quality and Consistency:

   • Inconsistent data types across different tables created obstacles during joins and aggregations, leading to potential inaccuracies in the final outputs. This necessitated additional data cleansing steps to ensure compatibility and reliability of results.

2. Performance Issues:

   • Large datasets often resulted in slow query execution times, particularly for complex aggregations and subqueries. This issue required optimization strategies, such as indexing and query restructuring, to enhance performance and ensure timely reporting.

3. Complex Queries and Logic:

   • Some analysis required intricate SQL queries, including nested queries and multiple joins, which made them challenging to understand and maintain. Simplifying these queries without losing necessary detail was crucial for future scalability and usability.

4. Understanding Customer Segmentation:

   • Analyzing member versus non-member transactions highlighted the need for deeper insights into customer behavior. This aspect required additional layers of analysis to identify specific trends and preferences within each segment.

7. Business Insights & Impact

The analysis yielded significant insights that can drive strategic decisions for Balanced Tree Clothing Company:

1. Sales Trends and Seasonality:

   • The data revealed clear sales peaks during certain months, indicating effective promotional strategies or seasonal demand fluctuations. Recognizing these patterns enables better planning for inventory and marketing campaigns.

2. Customer Engagement:

   • The proportion of transactions from members compared to non-members demonstrated a strong engagement with loyalty programs. This insight suggests that further investment in member benefits could enhance customer retention and boost sales.

3. Product Performance:

   • Identifying the top-selling products provided clarity on consumer preferences, allowing for focused marketing efforts and improved inventory management. By promoting high-demand items, the company can maximize revenue and minimize excess stock.

4. Discount Impact:

   • The analysis of discount usage indicated that while discounts drive sales volume, they also significantly impact overall revenue. Balancing discount strategies will be essential to maintain profitability while attracting customers.

8. Recommendations to Increase Business & Profit

To capitalize on the insights gained from the analysis, the following recommendations are proposed:

1. Enhanced Marketing Strategies:

   • Develop targeted marketing campaigns aimed at member customers, leveraging their demonstrated loyalty. Personalized offers and exclusive promotions could further enhance engagement and drive repeat purchases.

2. Inventory Optimization:

   • Implement strategies to ensure that top-selling products are consistently available, thereby meeting customer demand and preventing lost sales opportunities. Consider a dynamic inventory management system to adjust stock levels based on real-time sales data.

3. Regular Data Analysis:

   • Establish a routine for monthly data analysis to track sales performance and customer behavior continuously. This will enable proactive adjustments to marketing strategies and inventory management, ensuring responsiveness to market changes.

4. Segmentation and Personalization:

   • Utilize customer segmentation to create tailored marketing strategies that resonate with different demographics. By understanding the preferences of members versus non-members, the company can optimize communication and promotional efforts.

9. Conclusion & Future Plans

This report outlines the key insights derived from analyzing sales performance at Balanced Tree Clothing Company. By understanding sales trends, customer engagement, and product performance, the company is better positioned to make informed decisions that enhance business operations.

Moving forward, it is essential to establish a systematic approach for ongoing data analysis. This will not only facilitate timely insights but also allow the company to remain agile in response to evolving market conditions. Continuous monitoring and adaptation based on customer behaviors will be crucial for sustained growth and profitability.