An end-to-end Business Intelligence project that transforms raw CRM sales data into actionable commercial insights using Python, SQL, and Power BI.
The solution enables business stakeholders to monitor revenue performance, pipeline health, customer segmentation, sales effectiveness, and data quality through an interactive dashboard built on a star-schema data model.
Sales organizations often store customer, opportunity, product, and sales team information across multiple CRM tables, making it difficult to answer critical commercial questions efficiently.
This project was designed to answer questions such as:
- How much revenue has the business generated?
- What is the current pipeline value?
- Which salespeople and customer segments perform best?
- Where do opportunities drop out of the sales funnel?
- How does win rate change over time?
- Is the underlying data reliable enough for reporting?
The objective was to build a complete BI solution—from raw data to executive dashboards—that supports data-driven commercial decision making.
| Category | Details |
|---|---|
| Industry | CRM / B2B Sales Analytics |
| Dataset | Maven Analytics CRM Sales Opportunities |
| Records | 8,800 Sales Opportunities |
| Dashboard | 5 Pages |
| Database | SQLite |
| Tools | Python, SQL, Power BI, DAX |
| Methodology | Python → SQL → Power BI |
The project uses the Maven Analytics CRM Sales Opportunities dataset available on Kaggle.
| Table | Description |
|---|---|
| sales_pipeline.csv | Sales opportunities, stages, revenue, close dates |
| accounts.csv | Customer details, industry, revenue, employee count |
| products.csv | Product catalog |
| sales_teams.csv | Sales representatives and managers |
Source
https://www.kaggle.com/datasets/innocentmfa/crm-sales-opportunities
- Python (Pandas)
- SQL (SQLite)
- Power BI
- DAX
- Git & GitHub
Performed data preparation using Pandas by:
- Importing and exploring all source tables
- Cleaning missing values and duplicates
- Standardizing column names and data formats
- Validating revenue and date fields
- Creating business features including:
- Sales Cycle
- Deal Size Category
- Quarter
- Fiscal Year
- Customer Segment
- Exporting cleaned datasets for SQL analysis
Performed business analysis using SQL, including:
- Revenue & Pipeline KPIs
- Win & Lost Rate Analysis
- Monthly Revenue Trends
- Salesperson Performance Ranking
- Customer Analysis
- Product Analysis
- Regional Performance
- Sales Funnel Conversion
- Running Totals using Window Functions
SQL concepts demonstrated:
- Joins
- CTEs
- Aggregate Functions
- Window Functions
- CASE Statements
- Ranking Functions
Designed a star-schema data model consisting of:
- Sales Opportunities
- Date
- Customer
- Product
- Salesperson
- Region
Created DAX measures including:
- Revenue
- Pipeline Value
- Win Rate
- Lost Rate
- Average Deal Size
- Sales Cycle
- Revenue Growth %
- Opportunity Count
- Closed Won
- Closed Lost
The Power BI dashboard consists of five interactive pages designed to monitor commercial performance, sales pipeline health, customer behaviour, and reporting data quality.
Provides an executive-level view of overall commercial performance, including revenue trends, pipeline value, win rate, and key sales KPIs.
- The United States is the primary revenue contributor, generating 84% of total revenue ($8.43M of $10.01M).
- GTX Pro generates the highest revenue ($3.51M), despite GTX Basic having the highest opportunity volume.
- Revenue performance varies significantly month-to-month, with peaks observed in June and September.
- Continue prioritising high-value product opportunities while identifying strategies to increase international market contribution.
- Revenue volatility should be monitored to understand seasonal patterns and sales cycle impacts.
Analyzes opportunity movement across sales stages, conversion performance, and pipeline health.
- 48.16% of opportunities entering the funnel successfully convert into Won deals.
- Won opportunities have a longer average sales cycle (52 days) compared with Lost opportunities (41 days).
- A number of open opportunities have exceeded the typical sales cycle.
- Sales teams can improve conversion by reviewing ageing opportunities and identifying deals requiring intervention.
- Pipeline monitoring can help prioritise high-potential opportunities.
Evaluates salesperson effectiveness, customer segments, regional contribution, and revenue drivers.
- Darcel Schlecht is the highest-performing salesperson with $1.15M revenue.
- Enterprise customers contribute approximately 73% of total revenue.
- Win rates remain relatively consistent across industries.
- Enterprise accounts represent the strongest revenue opportunity.
- Sales resource allocation can be reviewed to maximise coverage of high-value segments.
Examines sales outcomes, lost opportunities, and performance trends.
- Retail, Technology, and Medical industries record the highest lost opportunity counts.
- GTX Basic has the highest lost deals due to its higher sales volume.
- Monthly win rates fluctuate between 49% and 83%.
- Additional lost-reason tracking would help identify root causes such as pricing, competition, or product fit.
- Sales strategies can be refined based on recurring loss patterns.
Validates reporting reliability through completeness checks, duplicate detection, and data quality monitoring.
- All 8,800 Opportunity IDs are unique.
- Overall data completeness is 83.8%.
- Three data issues were identified and corrected during preparation.
- Data validation processes improve confidence in reporting outputs.
- Reliable data foundations support accurate business decisions.
This solution demonstrates how Business Intelligence can support commercial decision making by enabling stakeholders to:
- Monitor executive sales KPIs
- Track pipeline performance
- Identify sales bottlenecks
- Compare salesperson effectiveness
- Evaluate customer segments
- Monitor product performance
- Improve reporting accuracy through data quality validation
- Business Intelligence
- Commercial Analytics
- Sales Performance Analysis
- Customer Analytics
- KPI Reporting
- Business Storytelling
- Python (Pandas)
- Data Cleaning
- Feature Engineering
- Data Validation
- Joins
- CTEs
- Window Functions
- Ranking Functions
- Aggregations
- Star Schema Data Modeling
- DAX Measures
- Interactive Dashboard Design
- KPI Development
Commercial-Sales-Performance-Analytics/
│
├── data/
│ ├── raw/
│ ├── cleaned/
│ └── sales.db
│
├── python/
│ ├── data_cleaning.py
│ └── feature_engineering.py
│
├── sql/
│ ├── create_tables.sql
│ ├── business_queries.sql
│ └── analysis.sql
│
├── images/
│ ├── 01_executive_overview.png
│ ├── 02_sales_funnel_pipeline.png
│ ├── 03_sales_customer_performance.png
│ ├── 04_win_loss_analysis.png
│ └── 05_data_quality_dashboard.png
│
├── README.md
└── Commercial_Sales_Performance_Dashboard.pbix
Planned future enhancements include integrating an AI-powered insight generator using LangChain and Groq to automatically summarize dashboard trends and highlight notable business changes in natural language.
- Telecom Churn Analytics
- AI Retail Analytics Platform
Joyce Lee
Data Analyst | Business Intelligence | Reporting Analytics
📧 Email: howielee1208@gmail.com
🔗 GitHub: https://github.com/joyceleehy