Zomato Data Insights System
Budget: ₹1,500 – ₹12,500 INR
Give me Brief Description for Project Title
Zomata - Food Delivery Data Insights Using
Python and SQL
Skills take away From
This Project
SQL
Python
Streamlit
Data Engineering
Database Management
Domain Food Delivery and Logistics Analytics
Problem Statement:
Imagine you are working as a data scientist at Zomato. Your goal is to enhance
operational efficiency and improve customer satisfaction by analyzing food delivery
data. You need to build an interactive Streamlit tool that enables seamless data entry
for managing orders, customers, restaurants, and deliveries. The tool should support
robust database operations like adding columns or creating new tables dynamically
while maintaining compatibility with existing code.
Business Use Cases:
• Order Management:
• Identifying peak ordering times and locations.
• Tracking delayed and canceled deliveries.
• Customer Analytics:
• Analyzing customer preferences and order patterns.
• Identifying top customers based on order frequency and value.
• Delivery Optimization:
• Analyzing delivery times and delays to improve logistics.
• Tracking delivery personnel performance.
• Restaurant Insights:
• Evaluating the most popular restaurants and cuisines.
• Monitoring order values and frequency by restaurant.
Approach:
1) Dataset Creation:
• Use Python (Faker) to generate synthetic datasets for customers, orders,
restaurants, and deliveries.
• Populate the SQL database with these datasets.
2) Database Design:
• Create normalized SQL tables for Customers, Orders, Restaurants, and
Deliveries.
• Ensure compatibility for dynamic schema changes (e.g., adding columns,
creating new tables).
3) Data Entry Tool:
Develop a Streamlit app for:
• Adding, updating, and deleting records in the SQL database.
• Dynamically creating new tables or modifying existing ones.
4) Data Insights:
• Use SQL queries and Python to extract insights like peak times, delayed
deliveries, and customer trends.
• Visualize the insights in the Streamlit app.(Add on)
5) OOP Implementation:
• Encapsulate database operations in Python classes.
• Implement robust and reusable methods for CRUD (Create, Read, Update,
Delete) operations.
6) Order Management:
• Identifying peak ordering times and locations.
• Tracking delayed and canceled deliveries.
7) Customer Analytics:
• Analyzing customer preferences and order patterns.
• Identifying top customers based on order frequency and value.
8) Delivery Optimization:
• Analyzing delivery times and delays to improve logistics.
• Tracking delivery personnel performance.
9) Restaurant Insights:
• Evaluating the most popular restaurants and cuisines.
• Monitoring order values and frequency by restaurant.
Results:
By the end of this project, learners will achieve:
• A fully functional SQL database for managing food delivery data.
• An interactive Streamlit app for data entry and analysis.
• Should write 20 sql queries and do analysis.
• Dynamic compatibility with database schema changes.
• Comprehensive insights into order trends, delivery performance, and customer
behavior.
Project Evaluation metrics:
• Database Design:
• Proper normalization of tables and relationships between them.
• Code Quality:
• Use of OOP principles to ensure modularity and scalability.
• Robust error handling for database operations.
• Streamlit App Functionality:
• Usability of the interface for data entry and insights.
• Compatibility with schema changes.
• Data Insights:
• Use 20 sql queries for data analysis
• Documentation:
• Clear and comprehensive explanation of the code and approach.
Technical Tags:
SQL, Python, Streamlit, Data Engineering, Object-Oriented Programming, Relational
Databases, Data Analysis.
Data Set:
• Source: Synthetic dataset generated using Python.
• Format: CSV or direct insertion into SQL tables.
• Library: Use Faker Python to create dummy dataset
Customers Table
This table stores information about customers.
• customer_id (Primary Key): Unique identifier for each customer.
• name: Customer’s full name.
• email: Contact email address.
• phone: Contact phone number.
• location: Address or location of the customer.
• signup_date: Date the customer signed up.
• is_premium: Boolean indicating if the customer has a premium membership.
• preferred_cuisine: Customer's preferred cuisine type.
• total_orders: Total number of orders placed by the customer.
• average_rating: Average rating given by the customer to restaurants.
Restaurants Table
This table manages restaurant information.
• restaurant_id (Primary Key): Unique identifier for each restaurant.
• name: Restaurant name.
• cuisine_type: Primary cuisine type served (e.g., Indian, Chinese).
• location: Location of the restaurant.
• owner_name: Name of the restaurant owner.
• average_delivery_time: Average delivery time for this restaurant (in minutes).
• contact_number: Restaurant’s contact number.
• rating: Average customer rating of the restaurant (1-5 scale).
• total_orders: Total number of orders received by the restaurant.
• is_active: Boolean indicating if the restaurant is currently active on the
platform.
Orders Table
This table manages order details.
• order_id (Primary Key): Unique identifier for each order.
• customer_id (Foreign Key): References the customer_id in the Customers
table.
• restaurant_id (Foreign Key): References the restaurant_id in the Restaurants
table.
• order_date: Date and time when the order was placed.
• delivery_time: Date and time when the order was delivered.
• status: Current status of the order (e.g., Pending, Delivered, Cancelled).
• total_amount: Total bill amount for the order.
• payment_mode: Payment mode used (e.g., Credit Card, Cash, UPI).
• discount_applied: Discount amount applied to the order.
• feedback_rating: Rating given by the customer for the order (1-5 scale).
Deliveries Table
This table stores information about deliveries.
• delivery_id (Primary Key): Unique identifier for each delivery.
• order_id (Foreign Key): References the order_id in the Orders table.
• delivery_person_id (Foreign Key): References the delivery_person_id in a
Delivery Persons table (if applicable).
• delivery_status: Current delivery status (e.g., On the way, Delivered).
• distance: Distance of delivery in kilometers.
• delivery_time: Actual time taken for delivery (in minutes).
• estimated_time: Estimated delivery time (in minutes).
• delivery_fee: Delivery fee charged for the order.
• vehicle_type: Type of vehicle used for delivery (e.g., Bike, Car).
Delivery Persons Table (Optional)
This optional table stores information about delivery personnel.
• delivery_person_id (Primary Key): Unique identifier for each delivery person.
• name: Delivery person’s full name.
• contact_number: Contact phone number.
• vehicle_type: Type of vehicle used for delivery.
• total_deliveries: Total number of deliveries completed.
• average_rating: Average rating given by customers to this delivery person.
• location: Current base location of the delivery person.
Justification for Columns:
• Comprehensive Tracking: Columns like order_status, delivery_status, and
average_rating help track the performance of restaurants and delivery
personnel.
• Customer Insights: Columns such as preferred_cuisine and average_rating
provide insights into customer preferences.
• Operational Efficiency: Columns like distance, estimated_time, and
delivery_time enable performance tracking and optimization.
• Data Integration: Foreign keys ensure seamless linkage between tables for
relational operations.
Data Set Explanation:
Content:
• Customer profiles with order frequency and locations.
• Order details including date, value, status, and delivery times.
• Restaurant information with cuisines and popularity metrics.
• Delivery personnel performance metrics.
Preprocessing Steps:
• Ensure no missing or duplicate data.
• Validate relationships between tables (e.g., customer_id in Orders matches
customer_id in Customers).
Project Deliverables:
• Source Code:
• Python scripts for dataset generation, database management, and
Streamlit app development.
• Streamlit App:
• An interactive app for data entry and analysis.
• 20 SQL queries for data analysis
• Documentation:
• Explanation of the approach, database schema, and instructions to run the
project.
Project Guidelines:
• Coding Standards:
• Use proper naming conventions, comments, and modular functions.
• Follow Python OOP principles for database and application logic.
• Version Control:
• Use Git to manage the codebase, with clear commit messages.
• Error Handling:
• Handle database exceptions and provide meaningful error messages in
the Streamlit app.
• User Experience:
• Design a user-friendly interface for the Streamlit app.
• Testing:
• Ensure all database operations (CRUD) work seamlessly.
Zomata - Food Delivery Data Insights Using
Python and SQL
Skills take away From
This Project
SQL
Python
Streamlit
Data Engineering
Database Management
Domain Food Delivery and Logistics Analytics
Problem Statement:
Imagine you are working as a data scientist at Zomato. Your goal is to enhance
operational efficiency and improve customer satisfaction by analyzing food delivery
data. You need to build an interactive Streamlit tool that enables seamless data entry
for managing orders, customers, restaurants, and deliveries. The tool should support
robust database operations like adding columns or creating new tables dynamically
while maintaining compatibility with existing code.
Business Use Cases:
• Order Management:
• Identifying peak ordering times and locations.
• Tracking delayed and canceled deliveries.
• Customer Analytics:
• Analyzing customer preferences and order patterns.
• Identifying top customers based on order frequency and value.
• Delivery Optimization:
• Analyzing delivery times and delays to improve logistics.
• Tracking delivery personnel performance.
• Restaurant Insights:
• Evaluating the most popular restaurants and cuisines.
• Monitoring order values and frequency by restaurant.
Approach:
1) Dataset Creation:
• Use Python (Faker) to generate synthetic datasets for customers, orders,
restaurants, and deliveries.
• Populate the SQL database with these datasets.
2) Database Design:
• Create normalized SQL tables for Customers, Orders, Restaurants, and
Deliveries.
• Ensure compatibility for dynamic schema changes (e.g., adding columns,
creating new tables).
3) Data Entry Tool:
Develop a Streamlit app for:
• Adding, updating, and deleting records in the SQL database.
• Dynamically creating new tables or modifying existing ones.
4) Data Insights:
• Use SQL queries and Python to extract insights like peak times, delayed
deliveries, and customer trends.
• Visualize the insights in the Streamlit app.(Add on)
5) OOP Implementation:
• Encapsulate database operations in Python classes.
• Implement robust and reusable methods for CRUD (Create, Read, Update,
Delete) operations.
6) Order Management:
• Identifying peak ordering times and locations.
• Tracking delayed and canceled deliveries.
7) Customer Analytics:
• Analyzing customer preferences and order patterns.
• Identifying top customers based on order frequency and value.
8) Delivery Optimization:
• Analyzing delivery times and delays to improve logistics.
• Tracking delivery personnel performance.
9) Restaurant Insights:
• Evaluating the most popular restaurants and cuisines.
• Monitoring order values and frequency by restaurant.
Results:
By the end of this project, learners will achieve:
• A fully functional SQL database for managing food delivery data.
• An interactive Streamlit app for data entry and analysis.
• Should write 20 sql queries and do analysis.
• Dynamic compatibility with database schema changes.
• Comprehensive insights into order trends, delivery performance, and customer
behavior.
Project Evaluation metrics:
• Database Design:
• Proper normalization of tables and relationships between them.
• Code Quality:
• Use of OOP principles to ensure modularity and scalability.
• Robust error handling for database operations.
• Streamlit App Functionality:
• Usability of the interface for data entry and insights.
• Compatibility with schema changes.
• Data Insights:
• Use 20 sql queries for data analysis
• Documentation:
• Clear and comprehensive explanation of the code and approach.
Technical Tags:
SQL, Python, Streamlit, Data Engineering, Object-Oriented Programming, Relational
Databases, Data Analysis.
Data Set:
• Source: Synthetic dataset generated using Python.
• Format: CSV or direct insertion into SQL tables.
• Library: Use Faker Python to create dummy dataset
Customers Table
This table stores information about customers.
• customer_id (Primary Key): Unique identifier for each customer.
• name: Customer’s full name.
• email: Contact email address.
• phone: Contact phone number.
• location: Address or location of the customer.
• signup_date: Date the customer signed up.
• is_premium: Boolean indicating if the customer has a premium membership.
• preferred_cuisine: Customer's preferred cuisine type.
• total_orders: Total number of orders placed by the customer.
• average_rating: Average rating given by the customer to restaurants.
Restaurants Table
This table manages restaurant information.
• restaurant_id (Primary Key): Unique identifier for each restaurant.
• name: Restaurant name.
• cuisine_type: Primary cuisine type served (e.g., Indian, Chinese).
• location: Location of the restaurant.
• owner_name: Name of the restaurant owner.
• average_delivery_time: Average delivery time for this restaurant (in minutes).
• contact_number: Restaurant’s contact number.
• rating: Average customer rating of the restaurant (1-5 scale).
• total_orders: Total number of orders received by the restaurant.
• is_active: Boolean indicating if the restaurant is currently active on the
platform.
Orders Table
This table manages order details.
• order_id (Primary Key): Unique identifier for each order.
• customer_id (Foreign Key): References the customer_id in the Customers
table.
• restaurant_id (Foreign Key): References the restaurant_id in the Restaurants
table.
• order_date: Date and time when the order was placed.
• delivery_time: Date and time when the order was delivered.
• status: Current status of the order (e.g., Pending, Delivered, Cancelled).
• total_amount: Total bill amount for the order.
• payment_mode: Payment mode used (e.g., Credit Card, Cash, UPI).
• discount_applied: Discount amount applied to the order.
• feedback_rating: Rating given by the customer for the order (1-5 scale).
Deliveries Table
This table stores information about deliveries.
• delivery_id (Primary Key): Unique identifier for each delivery.
• order_id (Foreign Key): References the order_id in the Orders table.
• delivery_person_id (Foreign Key): References the delivery_person_id in a
Delivery Persons table (if applicable).
• delivery_status: Current delivery status (e.g., On the way, Delivered).
• distance: Distance of delivery in kilometers.
• delivery_time: Actual time taken for delivery (in minutes).
• estimated_time: Estimated delivery time (in minutes).
• delivery_fee: Delivery fee charged for the order.
• vehicle_type: Type of vehicle used for delivery (e.g., Bike, Car).
Delivery Persons Table (Optional)
This optional table stores information about delivery personnel.
• delivery_person_id (Primary Key): Unique identifier for each delivery person.
• name: Delivery person’s full name.
• contact_number: Contact phone number.
• vehicle_type: Type of vehicle used for delivery.
• total_deliveries: Total number of deliveries completed.
• average_rating: Average rating given by customers to this delivery person.
• location: Current base location of the delivery person.
Justification for Columns:
• Comprehensive Tracking: Columns like order_status, delivery_status, and
average_rating help track the performance of restaurants and delivery
personnel.
• Customer Insights: Columns such as preferred_cuisine and average_rating
provide insights into customer preferences.
• Operational Efficiency: Columns like distance, estimated_time, and
delivery_time enable performance tracking and optimization.
• Data Integration: Foreign keys ensure seamless linkage between tables for
relational operations.
Data Set Explanation:
Content:
• Customer profiles with order frequency and locations.
• Order details including date, value, status, and delivery times.
• Restaurant information with cuisines and popularity metrics.
• Delivery personnel performance metrics.
Preprocessing Steps:
• Ensure no missing or duplicate data.
• Validate relationships between tables (e.g., customer_id in Orders matches
customer_id in Customers).
Project Deliverables:
• Source Code:
• Python scripts for dataset generation, database management, and
Streamlit app development.
• Streamlit App:
• An interactive app for data entry and analysis.
• 20 SQL queries for data analysis
• Documentation:
• Explanation of the approach, database schema, and instructions to run the
project.
Project Guidelines:
• Coding Standards:
• Use proper naming conventions, comments, and modular functions.
• Follow Python OOP principles for database and application logic.
• Version Control:
• Use Git to manage the codebase, with clear commit messages.
• Error Handling:
• Handle database exceptions and provide meaningful error messages in
the Streamlit app.
• User Experience:
• Design a user-friendly interface for the Streamlit app.
• Testing:
• Ensure all database operations (CRUD) work seamlessly.