An end-to-end business intelligence solution that replaced manual Excel reporting with real-time interactive dashboards, enabling data-driven decisions for a food delivery platform.
↓ Explore the Case StudyZestyEats is a growing food delivery platform operating across multiple cities. The company was struggling with fragmented data across multiple Excel spreadsheets, making it impossible to get a unified view of business performance. Management needed a comprehensive analytics solution to understand sales patterns, optimize delivery operations, and improve customer satisfaction.
I designed and developed an end-to-end Power BI analytics platform that consolidates data from 5 source tables into a unified star schema model. The solution includes 3 interactive dashboards with 15+ visualizations, enabling real-time monitoring of KPIs, customer analytics, and delivery performance metrics.
The implementation eliminated 4+ hours of weekly manual reporting, provided instant access to business insights, and enabled data-driven decision making. Key stakeholders can now self-serve their analytics needs without depending on manual report requests.
ZestyEats had accumulated significant operational data but lacked the infrastructure to convert this data into actionable intelligence. The leadership team was making decisions based on gut feeling rather than data, leading to suboptimal resource allocation and missed revenue opportunities.
Business data was scattered across 5 separate Excel files with no standardized format. Each department maintained their own version, leading to inconsistent metrics and conflicting reports. There was no single source of truth.
The operations team spent over 4 hours every week manually preparing Excel reports for management. This repetitive work was error-prone and left no time for actual analysis or deriving insights.
There was no real-time visibility into key metrics. Management received weekly snapshots that were already outdated by the time decisions were made. Emerging trends and issues went unnoticed until they became major problems.
With 30% of deliveries exceeding the promised time, customer satisfaction was declining. The operations team had no tools to identify bottlenecks, understand traffic patterns, or optimize delivery personnel allocation.
I conducted stakeholder interviews to understand the specific needs of each department. This requirements gathering phase was crucial to ensure the final solution would address real business needs.
I followed a structured approach to deliver this project, starting with understanding business requirements and ending with deployment and documentation. Each phase built upon the previous one to ensure a robust and scalable solution.
Conducted stakeholder interviews to understand business needs, pain points, and expected outcomes. Documented current reporting processes to identify automation opportunities.
Performed comprehensive data profiling on all 5 source tables to understand data quality, identify missing values, and document data types and relationships.
Used Power Query to extract, transform, and load data from Excel sources. Implemented data cleansing rules and created calculated columns for analysis.
Designed and implemented a star schema with Order_details as the central fact table, connected to 4 dimension tables for optimized query performance.
Created 12+ calculated measures using DAX to derive business metrics, including time intelligence, percentage calculations, and context manipulation.
Designed 3 purpose-built dashboards following data visualization best practices. Implemented cross-filtering, drill-through actions, and conditional formatting.
Conducted thorough testing including data validation, user acceptance testing, and performance optimization before publishing to Power BI Service.
I implemented a classic star schema with Order_details as the central fact table containing transactional data. This is connected to 4 dimension tables that provide context for analysis. This design optimizes query performance and enables efficient DAX calculations.
Star Schema Model - Exactly as built in Power BI
Central transactional table containing order records with related delivery details brought in as calculated columns for analysis.
| Column Name | Type | Description |
|---|---|---|
| Customer ID | FK | Links to User_details table |
| Delivery_details.Delivery Person Id | Calculated | Related delivery person from Delivery_details |
| Delivery_details.Delivery Person Ratings | Calculated | Delivery rating from related table |
| Delivery_details.Festival | Calculated | Festival indicator for the delivery |
| Delivery_details.Multiple Deliveries | Calculated | Number of deliveries rider handled |
| Delivery_details.Road Traffic Density | Calculated | Traffic level: Low, Medium, High, Jam |
| Delivery_details.Time Order Picked | Calculated | Timestamp when order was picked up |
Contains customer demographic information for segmentation and targeting analysis.
| Column Name | Type | Description |
|---|---|---|
| Customer ID | PK | Unique customer identifier |
| Age | Number | Customer's age in years |
| Age group | Calculated | Teen / Twenties / Thirties / Senior |
| City | Text | City of residence |
| Delivery Location Latitude | Decimal | Delivery address latitude |
| Delivery Location Longitude | Decimal | Delivery address longitude |
Partner restaurant information including cuisine type and service offerings.
| Column Name | Type | Description |
|---|---|---|
| Restaurant ID | PK | Unique restaurant identifier |
| Name | Text | Restaurant name |
| Cuisine Type | Text | Type of cuisine offered |
| Food Category | Text | Veg / Non-veg / Fine Dining |
| Dine-in Available | Boolean | Whether dine-in service is offered |
Delivery execution details including timing, conditions, and external factors affecting delivery performance.
| Column Name | Type | Description |
|---|---|---|
| Delivery Person Id | FK | Links to Delivery_person_details |
| Delivery Person Ratings | Measure | Aggregated rating for the delivery person |
| Festival | Boolean | Whether it was a festival day |
| Multiple Deliveries | Measure | Number of concurrent deliveries |
| Order Id | FK | Links to Order_details |
| Road Traffic Density | Text | Low / Medium / High / Jam |
| Time Order Picked | Time | When order was picked from restaurant |
| Time Taken (Min) | Measure | Total delivery time in minutes |
| Tip | Measure | Tip amount given by customer |
Delivery personnel information for performance tracking and fleet analysis.
| Column Name | Type | Description |
|---|---|---|
| Delivery_person_Age | Number | Age of delivery personnel |
| Delivery_person_ID | PK | Unique delivery person identifier |
| Type_of_vehicle | Text | Motorcycle / Scooter / Electric Scooter / Bicycle |
| Vehicle_condition | Number | Condition rating 1-5 |
I designed three specialized dashboards, each serving a distinct stakeholder group. The dashboards follow a consistent design language while providing purpose-specific visualizations and insights.
Target Users: CEO, Leadership, Finance
An executive-level dashboard providing a high-level overview of business performance. Features prominent KPI cards, revenue trends, and category breakdowns for quick decision-making.
Target Users: Marketing, Product, Sales
A deep-dive into customer demographics and ordering patterns. Enables marketing team to understand who their customers are and how they behave, informing targeted campaign strategies.
Target Users: Operations, Logistics, Management
Operational dashboard focused on delivery efficiency and logistics optimization. Provides insights into delivery times, traffic impact, and personnel performance to improve customer satisfaction.
Target Users: Strategy, Expansion Team, Regional Managers
Geographic performance dashboard analyzing order patterns, revenue distribution, and customer behavior across different city tiers (Metropolitan, Semi-Urban, Urban) to inform expansion and marketing strategies.
The analysis revealed several actionable insights that can directly impact business outcomes. Each insight is paired with a data-driven recommendation for implementation.
Customers aged 18-35 (Twenties and Thirties age groups) contribute approximately 80% of total revenue. The Twenties segment alone accounts for the highest order frequency, while Thirties have higher average order values.
Orders in the ₹500-1000 range (Medium category) show the optimal balance of frequency and value, contributing to the majority of revenue. Low value orders are frequent but have minimal revenue impact, while High value orders are rare.
High traffic conditions increase average delivery time from 22 minutes (low traffic) to 38 minutes (high traffic) - a 73% increase. This directly impacts the on-time delivery rate (currently at 69.81%) and customer satisfaction scores.
Analysis of delivery person efficiency shows that motorcycle/bike riders consistently achieve 15% better delivery times compared to scooters, especially in urban areas with high traffic. Electric scooters show comparable performance with lower operating costs.
A classic Pareto distribution: the top 20% of partner restaurants generate approximately 65% of total revenue. These restaurants also have higher average ratings and better preparation time consistency.
Metropolitan cities generate ₹16.7M (76%) of total sales from 18,251 orders, while Semi-Urban contributes only ₹0.2M from just 239 orders (1%). Urban areas show strong per-order value (₹914.47) similar to Metropolitan (₹914.37), but Semi-Urban AOV lags at ₹849.98 - indicating different customer behavior.
This analytics solution directly addressed critical business challenges that were impacting ZestyEats' operational efficiency, customer satisfaction, and strategic decision-making. Below is a detailed breakdown of each problem and how this project solved it.
Business data was scattered across 5 separate Excel files maintained by different departments. Each team had their own version of "truth," leading to conflicting numbers in management meetings. The operations team couldn't access sales data, while marketing had no visibility into delivery performance.
Built a unified star schema data model consolidating all 5 data sources into a single, governed Power BI solution. All departments now access the same centralized dashboards with consistent metrics and definitions.
The analytics team spent over 4 hours every week manually preparing Excel reports for management. This involved copying data from multiple sources, creating pivot tables, formatting charts, and emailing reports. The process was error-prone and left no time for actual analysis.
Automated the entire reporting pipeline with self-refreshing Power BI dashboards. Stakeholders access real-time data without waiting for manual report generation. The solution includes scheduled data refresh and email subscriptions for key snapshots.
Leadership had zero real-time visibility into business performance. They received weekly Excel snapshots that were already outdated. Emerging problems like delivery delays or revenue dips went unnoticed for days. Reactive management led to customer complaints and lost revenue.
Deployed interactive dashboards accessible 24/7 via Power BI Service. Leadership can check KPIs anytime from any device. The KPIs Overview dashboard provides instant access to Total Sales, Deliveries, AOV, and On-Time Delivery rates with real-time filtering.
30% of deliveries were exceeding the promised delivery time, but the operations team had no visibility into why. They couldn't identify which factors (traffic, weather, rider, restaurant) were causing delays. Customer satisfaction was declining with no data to guide improvement initiatives.
Created the Delivery Performance dashboard with detailed analysis of Time Taken vs Traffic Density, Delivery Person efficiency by vehicle type, weather impact, and restaurant-level performance. Operations can now identify bottlenecks and optimize resource allocation.
The marketing team was running generic campaigns with no understanding of customer demographics, preferences, or ordering patterns. They had no data on which age groups order most, what food categories are popular, or which customer segments drive the most revenue. Marketing budget was wasted on unfocused campaigns.
Built the Customer Analytics dashboard with complete demographic breakdown by Age Group, Gender, and City. Food Category preferences and Order Type distribution enable targeted campaigns. Discovery that 18-35 age group drives 80% of revenue allows focused marketing spend.
Every data question required a formal request to the analytics team. Stakeholders waited days for answers to simple questions like "What were last week's sales by order type?" The bottleneck frustrated departments and delayed decision-making across the organization.
Empowered stakeholders with self-service analytics. Interactive slicers for Order Type, Date, and other dimensions allow anyone to explore data independently. The intuitive interface requires no technical training. Cross-filtering enables ad-hoc analysis without analyst involvement.
Management had no visibility into how different city tiers (Metropolitan, Urban, Semi-Urban) performed. Expansion decisions were made on intuition rather than data. No understanding of whether Semi-Urban had growth potential or Urban areas were underserved. Regional marketing budgets were allocated without performance insights.
Created the City & Regional Performance dashboard showing complete breakdown by city tier. Discovered Metropolitan drives 76% of revenue (₹16.7M) while Semi-Urban contributes only 1% (₹0.2M) - revealing massive untapped potential. Urban shows strong AOV (₹914.47) indicating expansion viability.
By solving these 7 core business problems, ZestyEats now has a robust analytics foundation that enables data-driven decision making at every level of the organization. The solution transforms raw transactional data into actionable insights, eliminates operational blind spots, and empowers teams to self-serve their analytics needs.
Experience the interactive dashboards and explore the data for yourself. All visualizations are fully functional with cross-filtering and drill-down capabilities.