Advanced SQL Analytics Project – Customer Report

📌 Overview

This project demonstrates Advanced Analytics using SQL by building a Customer Report that consolidates customer details, segmentations, and key performance indicators (KPIs).

The report is implemented as a SQL View, making it easy to query directly or connect to BI tools like Power BI or Tableau.

---

⚙️ Project Setup

1. Database Creation

The database can be set up in three ways:

1. Run Provided SQL Script (init_database.sql) – creates schema, tables, and inserts data run as it is just change the files location accordingly.
2. Import Flat Files (CSV) – load provided CSV files into SQL Server.
3. Restore from Backup – restore the provided .bak file to quickly set up the DataWarehouseAnalytics database.

2. Tables Used

• Customer – customer details (ID, name, birthdate, demographics).
• Product – product details (ID, category, cost).
• Sales – transactional data (order dates, sales amount, quantity).

---

📊 Customer Report

The Customer Report consolidates customer-level insights by combining dimensions and aggregations into one SQL View.

Included Metrics:

• Customer Information: ID, Name, Age, Age Group.
• Segmentation: VIP, Regular, New Customers (based on spending and history).
• Aggregations:
  • Total Orders
  • Total Sales
  • Total Quantity
  • Total Products Purchased
  • Customer Lifespan (months active)
  • Last Order Date

KPIs:

• Recency: Months since the last order.
• Average Order Value (AOV): Total Sales / Total Orders.
• Average Monthly Spend: Total Sales / Lifespan.

---

🛠️ SQL Techniques Used

• Aggregations: SUM, COUNT, AVG, MAX, MIN
• Window Functions: LAG(), SUM() OVER(...)
• CTEs (Common Table Expressions)
• Subqueries for intermediate calculations
• CASE WHEN for segmentation (VIP / Regular / New, and age groups)
• Date Functions: YEAR(), MONTH(), DATEDIFF()

---

🚀 Outcomes

After completing this project,I have:

• Learned how to consolidate raw customer data into an analytics-ready report.
• Performed segmentation and KPI generation with SQL.
• Created a 360° customer view that can be consumed by stakeholders or BI dashboards.

---

📂 Files Included

SQL Scripts

• 01_Initialize_DB.sql – Creates the database schema and loads initial tables (Customer, Product, Sales).
• 02_Change_OverTime_Analysis.sql – Analyzes sales trends over time (yearly, monthly).
• 03_Cumulative_Analysis.sql – Computes running totals and moving averages using window functions.
• 04_Performance_Analysis.sql – Compares sales performance against averages and previous year (YoY analysis).
• 05_Proportionality_Analysis.sql – Calculates category contributions (part-to-whole analysis).
• 06_Data_Segmentation.sql – Segments customers/products into groups based on behavior and ranges.
• 07_Customer_Report.sql – Final consolidated customer report with KPIs (Recency, AOV, Monthly Spend).

Datasets (CSV Files)

• Customer.csv – Customer details (IDs, names, birthdate, demographics).
• Product.csv – Product information (IDs, categories, cost).
• Sales.csv – Transactional sales data (order dates, quantities, sales amounts).

These CSV files act as the raw data sources for all analysis scripts.

---

📌 Next Steps

• Extend the analysis to Product Reports.
• Connect this view to Power BI/Tableau for visualization.
• Automate report refresh with SQL Server Agent Jobs.