Monday Coffee is a growing retail coffee brand looking to optimize its current sales operations and identify the most profitable cities for future market expansion. This project acts as an end-to-end Business Intelligence solution, transforming raw transactional data into actionable strategic insights.
By utilizing SQL for complex database querying and Power BI for dynamic data modeling and visualization, this project uncovers hidden sales trends, evaluates geographic rent efficiencies, and delivers targeted recommendations for the company's next franchise locations.
The goal of this project is to analyze the sales data of Monday Coffee, a company that has been selling its products online since January 2023, and to recommend the top three major cities in India for opening new coffee shop locations based on consumer demand and sales performance.
-
Coffee Consumers Count
How many people in each city are estimated to consume coffee, given that 25% of the population does? -
Total Revenue from Coffee Sales
What is the total revenue generated from coffee sales across all cities in the last quarter of 2023? -
Sales Count for Each Product
How many units of each coffee product have been sold? -
Average Sales Amount per City
What is the average sales amount per customer in each city? -
City Population and Coffee Consumers
Provide a list of cities along with their populations and estimated coffee consumers. -
Top Selling Products by City
What are the top 3 selling products in each city based on sales volume? -
Customer Segmentation by City
How many unique customers are there in each city who have purchased coffee products? -
Average Sale vs Rent
Find each city and their average sale per customer and avg rent per customer -
Monthly Sales Growth
Sales growth rate: Calculate the percentage growth (or decline) in sales over different time periods (monthly). -
Market Potential Analysis
Identify top 3 city based on highest sales, return city name, total sale, total rent, total customers, estimated coffee consumer
The raw dataset consists of over 10,000+ transaction records across 14 distinct cities. The data was modeled using a Star Schema to optimize query performance and enable dynamic filtering in Power BI.
- Fact Table:
sales(Transaction records, Revenue, Product IDs, Customer IDs) - Dimension Tables:
city(Population, Estimated Rent, Geographic Rank)customers(Customer details and location mapping)products(Product catalog and pricing)
After analyzing the data, the recommended top three cities for new store openings are:
City 1: Pune
- Average rent per customer is very low.
- Highest total revenue.
- Average sales per customer is also high.
City 2: Delhi
- Highest estimated coffee consumers at 7.7 million.
- Highest total number of customers, which is 68.
- Average rent per customer is 330 (still under 500).
City 3: Jaipur
- Highest number of customers, which is 69.
- Average rent per customer is very low at 156.
- Average sales per customer is better at 11.6k.
Schemas.sql: Data definition scripts establishing primary/foreign key relationships and table structures.
Solutions.sql: Advanced SQL queries answering 10 complex business questions using window functions (LAG, DENSE_RANK), CTEs, and multi-table joins.
Monday_Coffee_Expansion_Analysis.pbix: The interactive Power BI dashboard featuring dynamic date-slicing, DAX measures (Total Revenue, Rent-per-Customer), and cross-filtering visuals.
.csv files: The raw datasets utilized for ingestion (sales.csv, city.csv, customers.csv, products.csv).
Database Management: Relational database design, Primary/Foreign Key mapping.
Advanced SQL (MySQL 8.0): Common Table Expressions (CTEs), Window Functions, Time-Series Analysis (YEAR/MONTH/QUARTER), Aggregations.
Business Intelligence: Star Schema data modeling, DAX measure creation (SUM, DISTINCTCOUNT, DIVIDE), Interactive Dashboard Design, KPI tracking.