An end-to-end interactive Business Intelligence (BI) and operations monitoring platform designed for Quick Commerce (Blinkit) store metrics, customer RFM lifecycle analysis, marketing campaign ROI tracking, and logistics SLA audits.
This project bridges the gap between raw data storage and decision-makers by using a python-based ETL pipeline to extract insights from an SQLite database and display them on a premium, high-fidelity dark-themed web interface. It allows stakeholders to audit operational health, track customer retention, and optimize marketing spend in real-time.
While Blinkit’s revenue has grown steadily since launch (averaging 530K–600K INR monthly), customer satisfaction has remained low, with the overall average rating sitting at a mediocre 3.34 out of 5.
Our diagnostic analysis reveals that customer dissatisfaction is driven by systemic operational bottlenecks:
- Severe Product Quality Issues: A staggering 54.4% stock damage rate in daily logs (especially in Household Care, Dairy, and Fruits & Vegetables) is spoiling perishable goods before delivery.
- Poor Delivery Reliability (30% Delay Rate): Nearly 1 in 3 orders is delayed, with the bottleneck occurring during store picking and packing operations rather than rider transit.
- High Negative Customer Sentiment: Negative sentiment accounts for 34% of all feedback, with "Traffic" listed as a major reason for delivery delays.
- Automate Data Pipelines: Implement a lightweight, zero-dependency Python ETL pipeline to query the SQLite transactional database and generate dashboard datasets.
- Analyze Customer Lifecycles: Segment customers using Recency, Frequency, and Monetary (RFM) criteria to identify loyal champions and churn-prone users.
- Identify Operational Bottlenecks: Pinpoint specific dark stores and distance brackets causing packing and delivery latency.
- Optimize Marketing ROI: Compare acquisition costs (CPA) and Return on Ad Spend (ROAS) across Email, SMS, App, and Social Media channels to suggest budget reallocations.
- Provide Strategic Recommendations: Deliver actionable business solutions to improve supply chain integrity and customer retention.
The application processes a relational SQLite database (blinkit.db) containing the following tables:
orders: Transactional records includingorder_id,customer_id,store_id,order_total, andorder_date_only.order_items: Fine-grained details of items in each order, mappingproduct_id,quantity, andunit_price.products: Product catalog details containingproduct_name,brand, andcategory.inventory: Store stock logs managingstock_receivedanddamaged_stockrates.customers: Profiles includingcustomer_name,customer_segment, andregistration_date.customer_feedback: Customer ratings, text feedback,feedback_category, andsentimentanalysis labels.delivery_performance: Logistics metrics includingdelivery_status,delay_minutes,distance_km, andreasons_if_delayed.marketing_performance: Advertising logs detailing campaign spends,channel, impressions, clicks, and conversions.
- Database Engine:
SQLite 3— The relational data warehouse. - Data Pipeline (ETL):
Python 3+sqlite3+json— Handles aggregates, SQL query execution, and RFM classification. - Data Visualization:
Chart.js— Renders responsive canvas-based visualizations (doughnut, line, bar, and stacked layouts). - User Interface:
HTML5&CSS3— Implements modern dark-mode aesthetics, custom gradients, glassmorphism, and transitions. - Web Server:
Python HTTP Server— Runs the dashboard locally onhttp://localhost:8000.
Below is a walk-through of the interactive dashboard showing the interactive filters, responsive charts, and modules in action:
- Distance vs. Delay Paradox: Delays are independent of travel distance (Short deliveries <1.5km average 5.44 min delay vs. Long deliveries >4.5km average 5.48 min delay). Store-side picking/packing latency is the true culprit.
- Channel Performance: Email is the most efficient acquisition channel with the highest ROAS of 2.05 and the lowest CPA of 53.53 INR.
- High Churn Risk: A high volume of customer registrations are categorised as "At-Risk" or "Lost" due to poor fulfillment quality.
- Perishable Damages: Fresh categories like Fruits & Vegetables and Dairy suffer from daily damage rates exceeding 59%, which severely impacts CSAT.
- Reduce In-Store Latency: Set a strict 3-minute picking SLA for dark store partners and optimize warehouse layouts.
- Cold Chain Supply Chain Audit: Audit refrigeration and transition to temperature-controlled transit packaging to reduce perishable damage.
- Reallocate Marketing Budgets: Move budget away from low-efficiency App channels (CPA: 56.03 INR) to Email (ROAS: 2.05) and Referral Programs (ROAS: 2.03).
- Proactive Delay Compensation: Automatically credit customer wallets when deliveries are significantly delayed to mitigate negative sentiment.
blinkit.db— Relational database containing quick-commerce tables.extract_data_for_dashboard.py— Python ETL script calculating aggregates and RFM classifications.serve_dashboard.py— Python socket server running the web interface.update_dashboard.bat— Single-click launcher to run ETL and start the server.index.html— Tabbed responsive HTML structure.index.js— Chart rendering, filters, and page navigation logic.index.css— Custom glassmorphic styling and grid layouts.plots/— Folder containing visual assets and previews.business_insights_report.md— Strategic business recommendations report.
Double-click the launcher script in the root directory:
update_dashboard.batOpen your terminal in the project directory and run:
# 1. Refresh dashboard data
python extract_data_for_dashboard.py
# 2. Launch the local web server
python serve_dashboard.pyOpen your browser and navigate to http://localhost:8000.
- Predictive Inventory Alerting: Integrate machine learning to forecast stock demand and prevent critical under-stocking.
- Rider Route Optimization: Add dynamic GPS routing APIs to guide riders away from traffic bottlenecks.
- Live Database Connection: Transition from batch-generated JSON data to a live WebSocket server querying the SQLite database dynamically.
[NAKKA ROSHINI]
- MSc Data Science Student
- Aspiring Data Analyst and Data Scientist