An end-to-end data analytics project that transforms raw retail transaction data into actionable insights on profitability, customer performance, product performance, regional performance, and returns & refunds.
ProfitLens is an end-to-end retail analytics project built to answer a fundamental business question:
Where is the business generating revenue but failing to maximize profitability, and what actions can management take to improve financial performance?
The project combines SQL, Python, and Power BI to move from raw transactional data → data cleaning → exploratory analysis → profitability analysis → interactive dashboards → management recommendations.
Rather than focusing only on sales volume, ProfitLens evaluates performance through the lens of revenue, gross profit, profit margin, customer contribution, product contribution, regional performance, and refund/return exposure — the metrics that actually determine whether growth is healthy.
Strong revenue does not automatically mean strong business performance. A retail business can post impressive sales while quietly bleeding profitability because of:
- Low-margin products propping up top-line revenue
- Unprofitable or marginal customer segments
- Regional performance gaps hidden inside national averages
- A product mix skewed toward volume over margin
- High refund/return exposure eating into net revenue
- Revenue concentration risk (too much reliance on a few customers/products)
- High-revenue customer segments that may not translate proportionally into gross profit
ProfitLens investigates each of these areas to pinpoint where management attention would create the greatest financial impact — and backs every recommendation with the underlying data.
- Which products generate the most revenue?
- Which customers contribute the most revenue?
- Which categories drive revenue?
- How is revenue distributed across the business?
- Which products generate the highest gross profit?
- Which categories contribute the most profit?
- Which customers generate high revenue but relatively lower profitability?
- Which states/regions contribute the most gross profit?
- How does revenue compare with gross profit margin across segments?
- Which customers are the largest revenue contributors?
- Which customers generate the highest profit?
- Which high-revenue customers have comparatively lower margins (i.e., where is revenue quality weakest)?
- How much revenue is exposed to refunds?
- Which categories generate the highest refund amounts?
- Which products have the highest refund exposure?
- Which states contribute the highest refund amounts?
- What is the refund rate across categories?
┌─────────────────────────────┐
│ Raw Transactional │
│ Data │
│ Orders • Customers • │
│ Products • Returns • etc. │
└──────────────┬──────────────┘
│
▼
┌─────────────────────────────┐
│ SQL Data Layer │
│ │
│ • Data Cleaning │
│ • Data Validation │
│ • Business Analysis │
│ • CTEs / Window Functions │
│ • Aggregations & Ranking │
└──────────────┬──────────────┘
│
▼
┌─────────────────────────────┐
│ Cleaned Dataset │
│ │
│ order_item_cleaned.csv │
└──────────────┬──────────────┘
│
▼
┌─────────────────────────────┐
│ Python Analytics │
│ │
│ • Data Inspection │
│ • Exploratory Data Analysis│
│ • Profitability Analysis │
│ • Return & Refund Analysis │
└──────────────┬──────────────┘
│
▼
┌─────────────────────────────┐
│ Business Insights │
│ │
│ Revenue • Profitability │
│ Customers • Products │
│ Regions • Refunds │
└──────────────┬──────────────┘
│
▼
┌─────────────────────────────┐
│ Power BI Layer │
│ │
│ • Data Modeling │
│ • DAX Measures │
│ • KPI Development │
│ • Interactive Dashboards │
└──────────────┬──────────────┘
│
▼
┌─────────────────────────────┐
│ Decision Support │
│ │
│ Executive Summary │
│ Business Recommendations │
│ Actionable Insights │
└─────────────────────────────┘
| Layer | Tools Used |
|---|---|
| Database | SQL (schema design, CTEs, window functions, joins) |
| Data Cleaning & EDA | Python (Pandas, NumPy) |
| Visualization (EDA) | Matplotlib, Seaborn |
| Business Intelligence | Power BI (DAX, Power Query, data modeling) |
| Version Control | Git & GitHub |
| Environment | Jupyter Notebook |
ProfitLens/
│
├── 01_documentation/
│ ├── 01_requirements.txt
│ └── 02_ProfitLens_Project_Documentation.pdf
│
├── 02_data/
│ ├── cleaned_data/
│ │ └── order_item_cleaned.csv
│ └── raw_data/
│ ├── categories.csv
│ ├── customers.csv
│ ├── order_items.csv
│ ├── orders.csv
│ ├── products.csv
│ └── returns.csv
│
├── 03_sql/
│ ├── 01_data_cleaning.sql
│ └── 02_business_analysis.sql
│
├── 04_python/
│ ├── 01_data_inspection.ipynb
│ ├── 02_EDA.ipynb
│ ├── 03_profitability_analysis.ipynb
│ └── 04_return_&_refund_analysis.ipynb
│
├── 05_powerbi/
│ └── ProfitLens_Dashboard.pbix
│
├── 06_reports/
│ ├── 01_ProfitLens_Executive_Summary_PDF.pdf
│ └── 02_ProfitLens_Business_Recommendation.pdf
│
├── 07_visuals/
│ ├── 01_Executive_Overview_Dashboard.png
│ ├── 02_Product_Customer_Profitability_Dashboard.png
│ └── 03_Return_Refund_Dashboard.png
│
├── 08_additional_resources/
│
├── .gitignore
└── README.md
- Data Cleaning (SQL) — Standardized formats, handled nulls/duplicates, and validated referential integrity across orders, customers, products, and returns tables.
- Business Analysis (SQL) — Used CTEs and window functions to calculate revenue, gross profit, margins, and ranked contribution by customer/product/region.
- Exploratory Data Analysis (Python) — Profiled distributions, outliers, and correlations across revenue, profit, and refund variables.
- Profitability & Return Analysis (Python) — Isolated high-revenue/low-margin segments and quantified refund exposure by category, product, and state.
- Dashboarding (Power BI) — Built a connected data model with DAX measures for revenue, gross margin %, refund rate, and customer contribution, surfaced through interactive drill-down dashboards.
-
💡 The top 10 customers by revenue account for only 1.22% of total revenue, indicating that revenue is highly diversified rather than concentrated among a small group of customers. However, the analysis does not calculate the corresponding gross-profit share for these customers.
-
💡 Bluetooth Speaker is a relatively low-margin product, generating approximately ₹9.67M (0.75%) of total revenue while carrying a gross margin of only 21.43%, below the overall business gross margin of 25.51%. A second Bluetooth Speaker SKU shows a similarly weak 23.27% margin.
-
💡 Bihar contributes the highest gross profit at approximately ₹14.47 Cr, or 4.41% of total gross profit. Uttarakhand underperforms with approximately ₹35.50M revenue and ₹8.82M gross profit, compared with Karnataka's ₹36.60M revenue and ₹9.35M gross profit, indicating weaker profit generation despite comparable revenue.
-
💡 Apparel/Fashion accounts for approximately 19.53% of total refund value while representing only 17.93% of units sold, indicating disproportionate refund exposure relative to its sales volume. Toys has the highest absolute refund value at ₹26.16M, representing 20.37% of total refunds.
-
💡 Overall refund exposure stands at 9.99% of gross revenue, translating to approximately ₹128.39M (₹12.84 Cr) in refunded revenue. The analysis also records a 13.06% order return rate, making refunds a material profitability concern.
The dashboard includes:
- Executive Summary — Revenue, gross profit, margin %, and refund rate at a glance
- Product Performance — Revenue vs. margin by product/category
- Customer Performance — Top customers by revenue vs. profit contribution
- Regional Performance — State-wise revenue and profitability heatmap
- Returns & Refunds — Refund exposure by category, product, and region
Based on the analysis, ProfitLens surfaces the following data-backed recommendations:
-
Re-evaluate pricing and sourcing for low-margin products — Products such as Bluetooth Speaker generate meaningful revenue but operate below the 25.51% overall gross margin, indicating an opportunity to improve supplier costs, pricing, or promotional strategy without sacrificing sales volume.
-
Prioritize profitability over revenue alone in product decisions — Management should evaluate products using revenue, gross margin, and gross-profit contribution together, rather than prioritizing high-revenue products that may contribute disproportionately less profit.
-
Investigate refund drivers in high-exposure categories — Apparel/Fashion generates 19.53% of total refund value despite representing only 17.93% of units sold, while Toys contributes the highest absolute refund value at ₹26.16M. These categories should be investigated for product quality, sizing/specification issues, fulfillment accuracy, and customer-expectation gaps.
-
Reduce refund-related revenue leakage — With refunds representing approximately 9.99% of gross revenue (₹128.39M / ₹12.84 Cr) and an overall 13.06% order return rate, the business should establish category-level return KPIs and conduct root-cause analysis to reduce avoidable refunds.
-
Optimize regional sales and profitability strategy — Bihar is the strongest gross-profit contributor, while regions such as Uttarakhand generate revenue comparable to Karnataka but produce slightly weaker gross-profit outcomes. Regional strategies should therefore be evaluated on profitability and margin quality, not revenue alone.
-
Maintain a diversified customer base while monitoring account profitability — The top 10 customers contribute only 1.22% of total revenue, suggesting low customer-concentration risk. However, customer-level gross-profit contribution should be incorporated into account management to identify customers who generate revenue but deliver weaker margins.
-
Shift commercial focus toward sustainable profit growth Commercial resources should prioritize:
- High-margin products
- Profitable regions
- Lower-refund categories
- Strong gross-profit contributors
Low-margin and high-refund segments should be managed through targeted pricing, sourcing, product, and operational interventions.
The goal is not simply to maximize revenue, but to improve the quality and sustainability of revenue growth.
git clone https://github.com/ak-dataanalytics/ProfitLens.git
cd ProfitLensProject requirements and supporting documentation are available in:
01_documentation/
Open the SQL scripts from:
03_sql/
Run the scripts in the following order:
01_data_cleaning.sql
↓
02_business_analysis.sql
Make sure the database connection, database name, and table names match your local SQL Server environment.
Install the required dependencies:
pip install -r 01_documentation/01_requirements.txtLaunch Jupyter Notebook:
jupyter notebookRun the notebooks from:
04_python/
in the following order:
01_data_inspection.ipynb
↓
02_EDA.ipynb
↓
03_profitability_analysis.ipynb
↓
04_return_&_refund_analysis.ipynb
Open:
05_powerbi/ProfitLens_Dashboard.pbix
using Power BI Desktop.
- SQL: CTEs, window functions, joins, aggregate business logic
- Python: data cleaning, EDA, statistical summarization (Pandas, NumPy)
- Data visualization: Matplotlib, Seaborn
- Business intelligence: Power BI data modeling and DAX
- Business acumen: translating raw metrics into profitability-focused insight and recommendations
- Add time-series/trend analysis of revenue and margin over time
- Build a customer segmentation model (RFM or clustering) to complement the profitability view
- Automate the SQL → Python → Power BI refresh pipeline
Data Analyst | SQL | Python | Power BI | Excel | Business Intelligence
📧 Email: adityakrr02@gmail.com
🔗 LinkedIn: https://linkedin.com/in/xaditya
💻 GitHub: https://github.com/ak-dataanalytics
If you found this project useful or interesting, consider ⭐ starring the repo!