Product Sales and Promotions Analysis

Project Description:

This project focuses on analyzing product sales, agent performance, and promotional effectiveness using SQL. The dataset includes sales transactions, promotions, and agent details, structured within a data warehouse. The goal is to extract insights that help optimize sales strategies, customer targeting, and promotional efficiency.

Key Tasks Performed:

  • Designed and structured a relational Database for efficient data storage and retrieval
  • Ingested and cleaned sales dat using SQL, ensuring data integrity and consistency
  • Evaluated promotional campaigns, identifying which promotions led to the highest sales uplift
  • Created aggregated sales reports, tracking revenue trends and customer purchasing behavior.

Creating Key Tables (Data Modeling)

Structured database schema to support sales and promotional analysis.


                                CREATE TABLE STAGE_DW.Agents (
                                    AGENT_ID INT PRIMARY KEY,
                                    AGENT_EXPERIENCE_LEVEL NVARCHAR(50),
                                    AGENT_EDUCATION_LEVEL NVARCHAR(50)
                                );
                                
                                CREATE TABLE STAGE_DW.Promotions (
                                    PROMO_ID INT PRIMARY KEY,
                                    PROMO_NAME VARCHAR(30) NOT NULL,
                                    PROMO_SUBCATEGORY VARCHAR(30) NOT NULL
                                );

Sales Performance Analysis by Agent

Identifies top-performing agents based on sales revenue and experience level.


                                SELECT a.AGENT_ID, a.AGENT_EXPERIENCE_LEVEL, 
                                COUNT(s.SALE_ID) AS Total_Sales, 
                                SUM(s.SALE_AMOUNT) AS Total_Revenue
                                FROM Sales s
                                JOIN Agents a ON s.AGENT_ID = a.AGENT_ID
                                GROUP BY a.AGENT_ID, a.AGENT_EXPERIENCE_LEVEL
                                ORDER BY Total_Revenue DESC;

Analyzing Promotion Effectiveness

Reveals which promotions drive the highest revenue and sales volume.


                                SELECT p.PROMO_NAME, COUNT(s.SALE_ID) AS Sales_During_Promo, 
                                SUM(s.SALE_AMOUNT) AS Revenue_Generated
                                FROM Sales s
                                JOIN Promotions p ON s.PROMO_ID = p.PROMO_ID
                                GROUP BY p.PROMO_NAME
                                ORDER BY Revenue_Generated DESC;

Customer Purchasing Trends

Tracks monthly sales trends to identify peak purchasing periods.


                            SELECT FORMAT(SALE_DATE, 'yyyy-MM') AS Sales_Month, 
                            COUNT(SALE_ID) AS Total_Sales, 
                            SUM(SALE_AMOUNT) AS Total_Revenue
                            FROM Sales
                            GROUP BY FORMAT(SALE_DATE, 'yyyy-MM')
                            ORDER BY Sales_Month;