Menu Analysis and Revenue Optimization

This project focuses on analyzing data from Taste of the World Café, a fictional restaurant offering a diverse menu at varying price points. The dataset consists of two tables: menu_items and order_details. These tables are linked by a common key: order_details.order_id and menu_items.menu_item_id.

Objectives:

• Determine the best-selling and least-selling menu items.
• Identify which items are favored by high-spending customers.

Methods:

Exploring the menu_items table:

• Conducted simple queries to understand the table’s structure and contents.
• Utilized SELECT queries to retrieve menu items, ordering them by price to identify the most and least expensive items.
• Filtered menu items using the WHERE clause to find Italian dishes and using the ORDER BY clause to find the least and most expensive Italian dishes.
• Calculated the average cost of items with each category using the AVERAGE function.

Exploring the order_details table:

• Employed basic queries to explore the contents and structure of the table.
• Utilized the MIN and MAX functions to find the date range of the orders.
• Used the COUNT DISTINCT function to identify unique order IDs, providing a total count of orders.
• Counted all rows to determine the total number of items ordered.
• Identified the order with the most number of items using a subquery, and determined the number of orders with more than 12 items using the HAVING clause.

Joining the two tables:

• Performed a LEFT JOIN between the order_details and menu_items tables based on their common IDs.
• Identified the least and most ordered items by grouping them by item name and categories and ordering them by the number of purchases.
• Determined top five orders by total spend by calculating the sum of prices for each order ID and grouping them accordingly.
• Identified the most expensive order in the dataset by summing the prices for each order ID.

Summary of Findings:

• The menu consists of 32 items with Edamame being the least expensive ($5.00) and Shrimp Scampi being the most expensive ($19.95) item.
• Italian dishes range from $14.50 (Spaghetti) to $19.95 (Shrimp Scampi).
• There are 9 Italian dishes, 9 Mexican dishes, 8 Asian dishes, and 6 American dishes.
• The average price for each category are as follows: Italian ($16.95), Asian ($13.48), Mexican ($11.80), American ($10.07).
• The dataset covers orders from January 1, 2023, to March 31, 2023, with a total of 5,370 orders and 12,234 items ordered.
• The largest order contained 14 items, and 20 orders had more than 12 items.
• The least ordered item was Chicken Tacos (123 orders) while the most ordered item was a Hamburger (622 orders).
• The top five orders by total spend ranged from $185.10 to $192.45.
• The most expensive order was order ID 440, totaling $192.15.

Recommendations:

• Emphasize marketing efforts for Italian dishes, considering their higher average price.
• Streamline the menu by removing the least popular dishes to focus on customer favorites.
• Implement promotions to retain high-value customers identified through top orders by total spend.
• Analyze factors contributing to high-spend orders to identify upsell opportunities.
• In addition to the highest revenue, determine the most profitable items and categories.
• Promote top-selling items through marketing campaigns and feature them on menus.

By leveraging insights from the analysis, Taste of the World Café can optimize its menu offerings and increase customer satisfaction, leading to sustained revenue growth and long-term success.