The Situation:

Taste of the World Cafe is a bustling restaurant located in the heart of a vibrant city. Renowned for its diverse menu offerings inspired by cuisines from around the globe, the cafe has built a loyal customer base over the years. From exotic appetizers to hearty main courses and delectable desserts, the cafe prides itself on serving generous portions of flavorful dishes that cater to a variety of tastes and dietary preferences.

In an effort to keep pace with evolving culinary trends and customer preferences, Taste of the World Cafe recently debuted a new menu at the start of the year. The management team spared no effort in curating an enticing array of dishes, incorporating fresh ingredients and innovative flavors to delight the palates of their discerning patrons.

The Assignment:

As the newest addition to the team, Angelica has been appointed as the Data Analyst tasked with delving into the intricacies of customer data to evaluate the performance of the new menu items. The restaurant management is keen to gain actionable insights into which menu items resonate well with customers and which ones may require further attention or refinement.

The Objectives:

1. Explore the Menu Items Table: Begin by examining the menu_items table to gain a comprehensive understanding of the items featured on the new menu. Identify the range of dishes, categories, and pricing structures to inform your analysis.
2. Explore the Order Details Table: Dive into the order_details table to analyze the data collected regarding customer orders. Explore the frequency of orders, order values, and any pertinent customer feedback or preferences captured in the dataset.
3. Customer Reaction Analysis: Utilize both tables to discern how customers are reacting to the new menu offerings. Identify popular menu items, customer ordering patterns, and any notable trends or anomalies that may influence decision-making.

The End User:

The primary stakeholders encompass the restaurant management team, including the executive chef, operations manager, and marketing personnel. They rely on the analyst's expertise to extract actionable insights from the data, enabling informed decision-making and strategic planning initiatives.

The Action Plan:

Objective 1: Explore the items table

The first objective is to better understand the menu_items table by:

1. Finding the number of items on the menu.
2. Determining the least and most expensive items on the menu.
3. Identifying the number of Italian dishes on the menu, and finding the least and most expensive Italian dishes.
4. Calculating the number of dishes in each category and determining the average dish price within each category.

-- 1. Combine the menu_items and order_details tables into a single table
SELECT *
FROM order_details
LEFT JOIN menu_items
ON menu_items.menu_item_id = order_details.item_id;

-- 2. Determine the least and most ordered items and their respective categories
SELECT
    menu_items.item_name,
    menu_items.category,
    COUNT(order_details.order_details_id) AS order_count
FROM order_details
LEFT JOIN menu_items
ON menu_items.menu_item_id = order_details.item_id
GROUP BY menu_items.menu_item_id, menu_items.category
ORDER BY order_count ASC
LIMIT 1;

-- Result: Least ordered item: Chicken Tacos - Mexican

SELECT
    menu_items.item_name,
    menu_items.category,
    COUNT(order_details.order_details_id) AS order_count
FROM order_details
LEFT JOIN menu_items
ON menu_items.menu_item_id = order_details.item_id
GROUP BY menu_items.menu_item_id, menu_items.category
ORDER BY order_count DESC
LIMIT 1;

-- Result: Most ordered item: Hamburger - American

-- 3. Identify the top 5 orders that spent the most money
SELECT
    order_details.order_id,
    SUM(menu_items.price) AS total_spent
FROM order_details
LEFT JOIN menu_items
ON menu_items.menu_item_id = order_details.item_id
GROUP BY order_details.order_id
ORDER BY total_spent DESC
LIMIT 5;

-- 4. View the details of the highest spend order and the specific items purchased
SELECT
    order_details.order_id,
    menu_items.item_name,
    menu_items.category
FROM order_details
LEFT JOIN menu_items
ON menu_items.menu_item_id = order_details.item_id
WHERE order_id = '440';

-- 5. Bonus: View the details of the top 5 highest spend orders
SELECT
    order_details.order_id,
    menu_items.item_name,
    menu_items.category
FROM order_details
LEFT JOIN menu_items
ON menu_items.menu_item_id = order_details.item_id
WHERE order_id IN (440, 2075, 1957, 330, 2675);

Objective 2: Explore the orders table

The second objective is to better understand the order_details table by:

1. Finding the date range of the table.
2. Determining how many orders were made within this date range and the total number of items ordered.
3. Identifying which orders had the most number of items.
4. Counting how many orders had more than 12 items.

-- 1. Find the date range of the table
SELECT MIN(order_date) AS earliest_date, MAX(order_date) AS latest_date 
FROM order_details;

-- Result: 2023-01-01 to 2023-03-31

-- 2. Determine how many orders were made within this date range and the total number of items ordered
SELECT 
    COUNT(DISTINCT order_id) AS total_orders, 
    SUM(quantity) AS total_items_ordered 
FROM order_details 
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- Result: 12,234 orders between 2023-01-01 to 2023-03-31
--         12,097 items ordered between 2023-01-01 to 2023-03-31

-- 3. Identify which orders had the most number of items
SELECT 
    order_id,
    COUNT(item_id) AS number_of_items
FROM order_details
GROUP BY order_id
ORDER BY COUNT(item_id) DESC
LIMIT 1;

-- Result: Order with ID 4482 had the most items

-- 4. Count how many orders had more than 12 items
SELECT 
    order_id,
    COUNT(item_id) AS number_of_items
FROM order_details
GROUP BY order_id
HAVING number_of_items > 12
ORDER BY COUNT(item_id) DESC;

Objective 3: Analyze customer behavior

The final objective is to combine the menu_items and order_details tables to:

1. Identify the least and most ordered items and their respective categories.
2. Determine the top 5 orders that spent the most money.
3. View the details of the highest spend order.
4. View the details of the top 5 highest spend orders.

-- 1. Identify the least and most ordered items and their respective categories
SELECT
    menu_items.item_name,
    menu_items.category,
    COUNT(order_details.order_details_id) AS total_orders
FROM order_details
    LEFT JOIN menu_items ON menu_items.menu_item_id = order_details.item_id
GROUP BY menu_items.item_name, menu_items.category
ORDER BY total_orders ASC
LIMIT 1;

-- Result: Least ordered item - "Chicken Tacos" in the category "Mexican"

SELECT
    menu_items.item_name,
    menu_items.category,
    COUNT(order_details.order_details_id) AS total_orders
FROM order_details
    LEFT JOIN menu_items ON menu_items.menu_item_id = order_details.item_id
GROUP BY menu_items.item_name, menu_items.category
ORDER BY total_orders DESC
LIMIT 1;

-- Result: Most ordered item - "Hamburger" in the category "American"

-- 2. Determine the top 5 orders that spent the most money
SELECT
    order_details.order_id,
    SUM(menu_items.price) AS total_amount
FROM order_details
    LEFT JOIN menu_items ON menu_items.menu_item_id = order_details.item_id
GROUP BY order_details.order_id
ORDER BY total_amount DESC
LIMIT 5;

-- Result: Orders 440, 2075, 1957, 330, and 2675 are the top 5 highest spenders

-- 3. View the details of the highest spend order
SELECT
    order_details.order_id,
    menu_items.item_name,
    menu_items.category
FROM order_details
    LEFT JOIN menu_items ON menu_items.menu_item_id = order_details.item_id
WHERE order_id = 440;

-- Result: Details of items purchased in order ID 440

-- 4. (Bonus) View the details of the top 5 highest spend orders
SELECT
    order_details.order_id,
    menu_items.item_name,
    menu_items.category
FROM order_details
    LEFT JOIN menu_items ON menu_items.menu_item_id = order_details.item_id
WHERE order_id IN (440, 2075, 1957, 330, 2675)
ORDER BY order_details.order_id, menu_items.item_name;

The Report:

Objective 1: Explore the items table

To gain a comprehensive understanding of the new menu, the menu_items table was thoroughly examined. Here are the key findings:

• Total Items: The menu consists of 32 unique items.
• Least Expensive Item: The most affordable item on the menu is “Edamame.”
• Most Expensive Item: The priciest item offered is “Shrimp Scampi” from the Italian category, priced at $19.95.
• Italian Dishes: Out of the 32 items, 9 belong to the Italian cuisine. The least expensive Italian dish is “Spaghetti” priced at $14.50, while the most expensive is “Shrimp Scampi” at $19.95.
• Average Price by Category:
  • American: $10.06
  • Asian: $13.47
  • Mexican: $11.80
  • Italian: $16.75

The insights derived from this exploration provide a foundational understanding of the menu’s composition, price distribution, and category-specific pricing. This information will serve as a basis for further analysis in subsequent objectives.

Objective 2: Explore the orders table

The order_details table, which captures detailed information about each order, was examined to gain insights into customer ordering patterns. Here are the key findings:

• Date Range: The orders were recorded between January 1, 2023, and March 31, 2023.
• Orders and Items Count: During this period, a total of 12,234 orders were placed, comprising 12,097 individual items.
• Order with Most Items: Order ID 4482 had the highest number of items, indicating a potentially large group order or catering order.
• Orders with More than 12 Items: Further analysis revealed that there are 20 orders with more than 12 items. This could be indicative of larger group orders or special event catering.

The insights from this analysis provide valuable information about the volume and size of orders during the specified period, which can aid in understanding customer behavior and preferences.

Objective 3: Analyze customer behavior

The combined analysis of menu_items and order_details sheds light on customer ordering behavior and preferences:

• Least and Most Ordered Items:
• Least Ordered Item: “Chicken Tacos” from the Mexican category.
• Most Ordered Item: “Hamburger” from the American category.
• Top 5 Highest Spending Orders: Order 440, Order 2075, Order 1957, Order 330, Order 2675
• Details of the Highest Spending Order (Order 440):
• Details of the Top 5 Highest Spending Orders:

• Items purchased in each order are detailed, providing insights into the most popular and profitable items among customers.

This analysis offers a comprehensive understanding of customer preferences, popular menu items, and high-spending order trends. Such insights are invaluable for optimizing the menu, marketing strategies, and overall business operations.

The Recommendations

• Expand Italian Menu: Introduce more Italian dishes to capitalize on the higher average price point of $16.75, enhancing the category’s revenue potential.
• Promote Mexican Cuisine: Launch special promotions or combo offers to boost sales in the Mexican category, which currently has fewer items and an average price of $11.80.
• Introduce Group Order Discounts: Encourage larger group orders by offering special discounts or packages to increase order value and customer satisfaction.
• Implement Loyalty Programs: Reward customers with more than 12 items in their order with a loyalty program to encourage repeat business.
• Upselling and Feedback: Train staff in upselling techniques to increase order values and collect feedback for underperforming items like “Chicken Tacos” to enhance their appeal.