An end-to-end data analytics project using SQL, PostgreSQL, DAX, and Power BI.
This project simulates the work of a junior data analyst at a retail company:
you design the schema, load raw CSV files into PostgreSQL, build views, create DAX measures, and deliver interactive dashboards in Power BI.
This project analyzes retail sales performance across multiple years.
The goal was to build a complete, end-to-end analytics workflow including:
- SQL-based data cleaning & transformation
- Data modeling in PostgreSQL
- Calculation of key KPIs using DAX
- Interactive Power BI dashboards for insights & decision-making
This project was designed to answer key business questions for a retail company.
- What is the total revenue for each year?
- Which categories contribute the most to sales?
- What are the sales trends over time (monthly, quarterly, yearly)?
- Which products generate the highest revenue and profit?
- How do different customer segments (gender, income, etc.) contribute to revenue?
- Which customer groups purchase the highest-margin products?
- Which products have the highest gross profit?
- What are the top-performing products by revenue?
- Which categories and subcategories consistently perform well?
- What is the return rate (%) by product category?
- Which products have high return rates and low profitability?
- Are certain territories linked to higher return volume?
- How does monthly revenue change over time?
- What is the 3-month moving average trend?
- What is the year-over-year (YoY) revenue growth?
- SQL (PostgreSQL) – for data cleaning, joining, calculations
- Power BI – for data modeling, DAX measures, visualization
- DAX – custom KPI calculations
- GitHub – version control & project documentation
The project is built on a simple retail star schema:
- fact_sales – one row per order line (date, customer, product, territory, quantity)
- returns_data – product returns by date, territory, and product
- calendar – full date table (year, quarter, month, weekdays, etc.)
- dim_customer – customer demographics and income
- dim_product – product details (SKU, name, price, cost, attributes)
- dim_product_subcategories – product subcategories
- dim_product_categories – product categories
- dim_territory – region, country, continent
- fact_sales.order_date → calendar.calendar_date
- fact_sales.customer_key → dim_customer.customer_key
- fact_sales.product_key → dim_product.product_key
- fact_sales.territory_key → dim_territory.territory_key
- dim_product.product_subcategory_key → dim_product_subcategories.product_subcategory_key
- dim_product_subcategories.product_category_key → dim_product_categories.product_category_key
- returns_data.product_key → dim_product.product_key
- returns_data.territory_key → dim_territory.territory_key
- returns_data.return_date → calendar.calendar_date
Retail-Sales-Analytics-Dashboard/
│
├── README.md # Full project documentation
├── Retail-Sales-Analytics-Dashboard.pbix # Final Power BI dashboard
│
├── schema.sql # SQL script: creates dimension & fact tables
├── vw_sales.sql # SQL view used by Power BI
├── analysis_queries.sql # Example analysis queries used in SQL exploration
│
└── Screenshots/ # Dashboard images
├── Page1_ExecutiveSummary.png
├── Page2_SalesTrends.png
├── Page3_ProductInsights.png
└── Page4_ReturnsAnalysis.png
- Total Revenue
- Gross Profit
- Profit Margin %
- Revenue Trend Over Time
- Revenue by Category
- Monthly Revenue Trend
- 3-Month Moving Average
- YoY Revenue Change (%)
- Year & Category slicers
- Top 10 Products by Revenue
- Top 10 Products by Gross Profit
- Detailed product table
- Category / Subcategory slicers
- Return Rate % (KPI)
- Return Rate by Category
- Profitability vs Return Rate (Scatter Plot)
- Return Details Table
Total Revenue = SUM(public_vw_sales.revenue)
Total Gross Profit = SUM(public_vw_sales.gross_profit)
Profit Margin % =
DIVIDE([Total Gross Profit], [Total Revenue])
YoY Revenue % =
VAR CurrentYear = SELECTEDVALUE(public_vw_sales.year_)
VAR PrevYear = CurrentYear - 1
VAR CurrRevenue =
CALCULATE([Total Revenue], public_vw_sales.year_ = CurrentYear)
VAR PrevRevenue =
CALCULATE([Total Revenue], public_vw_sales.year_ = PrevYear)
RETURN
DIVIDE(CurrRevenue - PrevRevenue, PrevRevenue)
You can either just explore the Power BI file, or fully recreate the backend in PostgreSQL.
- Download
Retail-Sales-Analytics-Dashboard.pbixfrom this repository. - Open it in Power BI Desktop.
- If the data connection fails:
- You can view the existing visuals as-is, or
- Re-point the data source to your own PostgreSQL / CSV files.
-
Create a PostgreSQL database (for example):
CREATE DATABASE retail_sales;
-
Run the schema script to create all tables:
schema.sql
-
Load your data into:
calendardim_customerdim_product_categoriesdim_product_subcategoriesdim_productdim_territoryfact_salesreturns_data
-
Create the main reporting view:
view_vw_sales.sql
This creates the
vw_salesview that Power BI uses for most visuals. -
Connect Power BI to PostgreSQL:
- In Power BI Desktop → Get data → PostgreSQL database
- Server: your Postgres server (e.g.
localhost) - Database:
retail_sales - Select:
vw_salesreturns_data
- Load the data and refresh the report.
Now you have the same data model and dashboards running end-to-end.
Here are the key pages from the Power BI dashboard included in this project.
High-level KPIs and overview of revenue, profitability, and customer trends.
Monthly + yearly revenue trends, moving averages, and YoY analysis.
Top products by revenue, gross profit, and category performance.
Return rate performance and profitability vs return rate.
A summary of the most important insights discovered in the analysis.
- Revenue consistently increased year-over-year, with the strongest growth in 2021.
- The 3-month moving average shows stable seasonal patterns.
- Bikes were the highest revenue-generating category across all years.
- A small group of top products contributed the majority of revenue.
- Products with higher profit margins were primarily in the Accessories and Components categories.
- The Top 10% of customers generated a disproportionately large share of revenue.
- High-income customers showed the largest purchase volume.
- Male and female customers had similar buying patterns, but male customers bought slightly higher-priced items.
- Overall return rate was low (0–3% for most categories).
- A few specific products had unusually high return rates and may require quality review.
- Some territories showed higher returns, indicating potential fulfillment or shipping issues.
- Focusing marketing efforts on top revenue-generating customer segments can increase profitability.
- Improving quality control on high-return products could reduce lost revenue.
- Accessories and Components categories have high margins and should be promoted more.
Author: Shohag
If you’d like to discuss this project, collaborate, or talk about data analyst roles:
- 💼 LinkedIn: NURA ALAM SHOHAG
- 🧑💻 GitHub: Shohag-DataAnalyst