World Café Menu Analysis

Kristen Healy
2024-05-20

• Assignment
• Data Overview
  • Recommendations and Next Steps
• Query Details
  • Menu Items
  • Orders
  • Combined Data

Assignment

The fictional Taste of the World Café, a restaurant serving international cuisine, debuted a new menu at the beginning of the year. The business owner wants to get an understanding of how the new menu items are doing with customers: which items are doing well and which are not, and what its top customers seem to like best.

Because the owner wants the information quickly and the data is in a MySQL database, all of the analysis will be done in SQL.

Note: I used “top orders” as a proxy for “top customers” since we have order- and item-level data but not customer-level data.

Data Overview

We have 3 months of order data–from January through March, with 5370 total orders and 12,234 items ordered. The 7 top orders by item count had 14 items on them, and 20 orders had more than 12 items. January and March had similar order numbers (1800+), while there was a dip in February (<1700). Total revenue for the period was $159,217.

The menu has 32 items across 4 different international cuisines:

• Italian (9)
• Asian (8)
• Mexican (9)
• American (6)

The Asian category had the most items ordered at 3470, followed by Italian (2948), Mexican (2945), and American (2734). Interestingly, although the American category trailed the rest, which is unsurprising since the category has the fewest items, 3 of the most frequently ordered items were in that category:

• Hamburger, $12.95
• Edamame, $5
• Korean Beef Bowl, $17.95
• Cheeseburger, $13.95
• French Fries, $7

Prices range from $5 (Edamame) to $19.95 (Shrimp Scampi), with an average menu item price of $13.29. Italian is the highest-priced category on average ($16.75), followed by Asian ($13.48), Mexican ($11.82), and American ($10.33).

Given that, it wasn’t suprising to find that the top 5 orders by spend (>$185) ordered heavily from the Italian category:

• Italian, 26 items
• Asian, 17 items
• Mexican, 16 items
• American, 10 item

Recommendations and Next Steps

Although the American category has the lowest average prices, the items in this category are ordered frequently by customers as a whole. Although our top spenders order “American” items less often, I would recommend leaving the category largely as is because it’s driving both revenue and order numbers. The cheeseburger and hamburger, for example are among our top 5 items by revenue.

The Mexican category, however, needs changes. I would recommend eliminating both the Chicken Tacos and the Cheese Quesadillas from the menu since they’re performing poorly. The tacos were ordered only once by our highest spenders and is the least popular item amongst all customer orders, despite its relatively low price ($11.95). Similarly the quesadillas ($10.95) were the 5th least popular item overall and weren’t ordered at all by our highest spenders.

Overall, both the Italian and Asian categories are doing well. They are the top 2 categories by revenue, and are broadly popular judged by order item count. Therefore, I don’t recommend any immediate changes to either of these categories.

Next steps: I’d like to conduct a customer survey to get some additional information from our customers about what changes they’d like to see on our menu. I’ll set up a meeting with you and the executive chef to discuss the parameters.

Query Details

The restaurant database consists of 2 tables:

• menu_items
• order_detail

Menu Items Table

The menu_items table included the following columns:

• menu_item_id
• item_name
• category
• price

/* set restaurant_db as the my default db */
USE restaurant_db;

/* preliminary view of the table */
SELECT *
FROM menu_items;

/* number of menu items and categories */
SELECT
    COUNT(menu_item_id) AS num_items,
    COUNT(DISTINCT category) AS num_categories
FROM menu_items;

/* Summary menu item pricing info */
SELECT
    MAX(price) AS highest_price,
    MIN(price) AS lowest_price,
    ROUND(AVG(price),2) AS avg_price,
    (SELECT price
		FROM   menu_items
		GROUP  BY price
		ORDER  BY COUNT(*) DESC, price DESC
		LIMIT 1) AS most_frequent_price
FROM menu_items;

/* What are the least expensive items on the menu?*/
SELECT
    item_name,
    category,
    price
FROM
	menu_items
ORDER BY
	price
LIMIT 5;

/* What are the most expensive items on the menu*/
SELECT
    item_name,
    category,
    price
FROM
	menu_items
ORDER BY
	price DESC
LIMIT 5;

/* summary pricing info by category */    
SELECT
	category,
    COUNT(menu_item_id) AS num_items,
    MAX(price) AS most_expensive,
    MIN(price) AS least_expensive,
    ROUND(AVG(price),2) AS avg_price
FROM menu_items
GROUP BY category
ORDER BY avg_price DESC, num_items DESC;

Orders Table

The table contains rows for each order_details_id. The order_details table included the following columns:

• order_details_id
• order_id
• order_date
• order_time
• item_id

/* view order details table */
SELECT *
FROM
	order_details;

/* What order dates are included in the table? */
SELECT 
	MIN(order_date) AS first_date,
    MAX(order_date) AS last_date
FROM
	order_details;

/* 	How many orders?
	How many items were ordered?
*/

SELECT 
	COUNT(DISTINCT order_id) AS num_orders,
    COUNT(order_details_id) num_order_items
FROM order_details;

/*	Which orders had the most items? */
SELECT 
	order_id,
	COUNT(item_id) AS num_items
FROM
	order_details
GROUP BY
	order_id
ORDER BY
	num_items DESC;

/*	How many orders had more than 12 items: */

SELECT COUNT(*) FROM
(SELECT
	order_id,
	COUNT(item_id) AS num_items
FROM
	order_details
GROUP BY
	order_id
HAVING
	num_items > 12) AS num_orders;

/* Are there any differences in order/item counts by month? */
SELECT 
    MONTH(order_date) AS order_month,
	COUNT(DISTINCT order_id) AS num_orders,
    COUNT(item_id) AS num_items
FROM order_details 
GROUP BY order_month;

Combined Data

After becoming familiar with the two tables, I joined them for further analysis.

/* 	Create view combining the menu_items table and the order_details table.
	We'll use this in other queries.
*/
CREATE VIEW full_order_details AS
SELECT *
FROM
	order_details
LEFT JOIN menu_items
	ON order_details.item_id = menu_items.menu_item_id;

SELECT *
FROM
	full_order_details;

/* most often ordered (items)*/
SELECT
	item_name,
    category,
    price,
    COUNT(item_id) AS item_count
FROM
	full_order_details
GROUP BY
	item_name,category,price
ORDER BY
	item_count DESC;

/* most often ordered (categories)*/
SELECT
	category,
    ROUND(AVG(price),2) AS avg_price,
    COUNT(category) AS cat_count
FROM
	full_order_details
GROUP BY
	category
ORDER BY
	cat_count DESC;

/* items by revenue */
SELECT
	item_name,
    category,
    price,
    SUM(price) AS item_revenue,
    COUNT(item_id) AS item_count
FROM
	full_order_details
GROUP BY
	item_name,category,price
ORDER BY
	item_revenue DESC;

/* category revenue */
SELECT
	category,
    SUM(price) AS category_revenue
FROM
	full_order_details
GROUP B
	category
ORDER BY
	category_revenue DESC;

/* total revenue */
SELECT
	SUM(price) AS total_revenue
FROM
	full_order_details;

/* rank orders by highest spend */
SELECT 
	order_id,
	SUM(price) AS total_price
FROM
	full_order_details
GROUP BY order_id
ORDER BY total_price DESC;

/* rank orders by highest number of items with order total as tiebreaker */
SELECT 
	order_id,
    COUNT(item_id) AS item_count
FROM
	full_order_details
GROUP BY order_id
ORDER BY item_count DESC, SUM(price) DESC;

/* order 2075 was in the top 5 by spend but not by total items...
	order 3473 was in the top 5 by total items but not by spend...
    what was on these 2 orders?
*/
SELECT 
	order_id,
    item_name,
    category,
    COUNT(item_id),
    price
FROM 
	full_order_details
WHERE 
	order_id 
		IN (2075,3473)
GROUP BY 1,2,3,5
ORDER BY order_id, price DESC;

/* order details of highest spend order */
SELECT
	order_id,
	category,
    item_name,
    price,
    COUNT(item_name) num_items
FROM 
	full_order_details
WHERE 
	order_id = 
    (SELECT order_id
    FROM
		full_order_details
		GROUP BY order_id
		ORDER BY SUM(price) DESC
		LIMIT 1) -- returns order id of the order with the highest order total
GROUP BY order_id,category,item_name, price
ORDER BY num_items DESC;

/* category count for top order by spend */
SELECT
	order_id,
	category,
    COUNT(category) AS items_in_cat
FROM 
	full_order_details
WHERE 
	order_id = 
    (SELECT order_id
    FROM
		full_order_details
		GROUP BY order_id
		ORDER BY SUM(price) DESC
		LIMIT 1)
GROUP BY order_id, category
ORDER BY items_in_cat DESC;

/* items from top 5 orders by spend*/
WITH top_five AS
	(SELECT 
		order_id
	FROM
		full_order_details
	GROUP BY order_id
	ORDER BY SUM(price) DESC
	LIMIT 5)
SELECT
	category,
    item_name,
    price,
    COUNT(item_name) num_items
FROM 
	full_order_details
WHERE 
	order_id IN
		(SELECT order_id
		FROM
			top_five)
GROUP BY category,item_name, price
ORDER BY num_items DESC;

/* items from 5 top orders by item count*/
WITH top_five AS
	(SELECT 
		order_id
	FROM
		full_order_details
	GROUP BY order_id
	ORDER BY COUNT(item_id) DESC, SUM(price) DESC
	LIMIT 5)
SELECT
	category,
    item_name,
    price,
    COUNT(item_id) num_items
FROM 
	full_order_details
WHERE 
	order_id IN
		(SELECT order_id
		FROM
			top_five)
GROUP BY category,item_name, price
ORDER BY num_items DESC;

/* category count for top 5 orders by spend*/
WITH top_five AS
	(SELECT 
		order_id
	FROM
		full_order_details
	GROUP BY order_id
	ORDER BY SUM(price) DESC
	LIMIT 5)
SELECT
	category,
    COUNT(category) cat_count_order_freq
FROM 
	full_order_details
WHERE 
	order_id IN
		(SELECT order_id
		FROM
			top_five)
GROUP BY category
ORDER BY cat_count_order_freq DESC;

/* category count for top 5 orders by item count*/
WITH top_five AS
	(SELECT 
		order_id
	FROM
		full_order_details
	GROUP BY order_id
	ORDER BY COUNT(item_id) DESC, SUM(price) DESC
	LIMIT 5)
SELECT
	category,
    COUNT(category) cat_count_order_freq
FROM 
	full_order_details
WHERE 
	order_id IN
		(SELECT order_id
		FROM
			top_five)
GROUP BY category
ORDER BY cat_count_order_freq DESC;