Restaurant Orders Insights and Opportunities

THE SITUATION

Fictitiously, a team member of a data analytics consultancy has left the consultancy unexpectedly for undisclosed reasons before completing or even properly documenting their work on a client project, a restaurant order analysis project.

A newly hired team member, Junior Data Analyst Jade Handy, is one of several newly hired analysts. The newly hired analysts are rushing to get up to speed quickly and submit a report (aka the "Report"). The Report is their preliminary understanding of the restaurant order analysis project so the project manager can decide which newly hired analyst will fill the project team vacancy.

In order to be chosen, the junior data analyst knows he'll have to go above and beyond what the other data analysts are doing, which is basically just plugging in whatever saved MySQL script queries of the departed analyst's work can be found. (See https://github.com/jadehandy/maven_restaurant_mysql_basic_c2mp2 for Maven Guided Project's exploration sequence and my code block submissions.)

THE END CUSTOMER

The end customer for this restaurant order analysis project, a restaurant owner, is curious how analyzing the data from customer orders can give a better picture of the effect the current menu has on her sales . This is the focus of actual project work that the junior analyst needs to demonstrate in his Report.

THE GAME PLAN

The analyst knows he'll have to do more than just analyze order data to identify the most and least popular menu items and types of cuisine in order to assess what the restaurant's top customers seem to like best.

He will have to show his project manager he understands the structure of the data, how best to capture and properly document the analysis for continuity purposes, as well as creatively discover and clearly present hidden insights and opportunities the restaurant owner will instantly recognize as ripe with possibilities.

RECAP

This portfolio project (aka "Restaurant Order Insights and Opportunities") is for you, the reader.

The Report is the preliminary understanding of the restaurant order analysis project so the project manager can decide which newly hired analyst will fill the project team vacancy.

THE OBJECTIVES

A. The Objectives (for the Report)

1. Assess the Entity Relationships

2. Map Out a Database Schema

3. Explore the Reference Table

4. Explore the Transaction Table

5. Customer Insights

6. Challenging Assumptions

7. Unfinished Work

8. Report Summary

B. The Objectives (for the Restaurant Order Insights and Opportunities)

1. Demonstrate Data Storytelling and Data Visualization Acumen

2. Demonstrate Competitive Advantage

THE REPORT

A. The Objectives (For the Report)

1. Assess the Entity Relationships

What are we working with here? The database was received from the restaurant owner's point of sale system. Only two tables were provided to the data analyst. Customer orders (order_details) and menu items (menu_items). Customer orders naturally include menu items, so this makes sense.

Note: The database has already been "cleaned."

2. Map Out a Database Schema

The database schema as shown indicates a somewhat unusual scenario in that primary keys are not the common denominator for the task at hand.

The foreign key, item_id, from the order_details table (serving as the Transaction Table) matches with the primary key, menu_item_id, from the menu_items table (serving as the Reference Table).

3. Explore the Reference Table: Get an idea of what is on the new menu.

View the menu_items table. (Note: https://github.com/jadehandy/maven_restaurant_mysql_basic_c2mp2 for complete exploration sequence and code blocks.)

Task 1: SELECT * statement

Result: 32 records

The menu items and prices are not new to the restaurant owner. So, a tree map created from this query is a great way for the restaurant owner to see, differently, what they already know like the back of their hand.

Task 2: GROUP BY category and AVG(price) were used to reveal category information. Specifically, the average dish price.

SELECT 
    category, AVG(price)

FROM 
    menu_items

GROUP BY 
    category;

Result: American ($10.066667), Asian ($13.475000), Mexican ($11.800000), and Italian($16.750000).

This finding alone makes Italian cuisine a contender for the category which is most impactful to sales.

Conclusion #1: Boosting Italian dish orders = faster ROI.

4. Explore the Transaction Table: Get an idea of the data that has been collected.

View the order_details table. (Note: https://github.com/jadehandy/maven_restaurant_mysql_basic_c2mp2 for complete exploration sequence and code blocks.)

Task 3: SELECT * statement

Result:

Task 4: ORDER BY was again used. This time for order date information. The dataset utilized for this analysis encompassed order information spanning January 1, 2023, to March 31, 2023, representing the initial three months of the restaurant's operation with the current menu.

Task 5: Using COUNT(DISTINCT order_id) revealed the dataset comprises a total of 5,370 orders recorded during this period.

5. Customer Insights: Use both tables to understand how customers are reacting to the new menu.

Task 6: A LEFT JOIN was chosen in order to begin understanding how customers have been reacting to the restaurant's current menu.

SELECT *

FROM 
    order_details LEFT JOIN menu_items

    ON menu_items.menu_item_id = order_details.item_id;

Result:

Task 7: Anytime you are trying to increase sales or optimize offerings, it is helpful to know the most and least ordered items that you are selling. We now know these facts due to the following query.

SELECT
    menu_items.menu_item_id,
    menu_items.item_name,

COUNT(order_details.order_id) AS order_count

FROM
    menu_items

LEFT JOIN
    order_details ON menu_items.menu_item_id = order_details.item_id

GROUP BY
    menu_items.menu_item_id, menu_items.item_name

ORDER BY
    order_count DESC, menu_items.menu_item_id

Result: The most ordered menu item? Hamburgers, with 622 orders.

Result: Least ordered menu item? Chicken Tacos, with only 123 orders.

Task 8: Here is the same most-least check, however, on categories.

SELECT 
    menu_items.category,

    COUNT(order_details.order_details_id) AS order_count

FROM 
    menu_items

LEFT JOIN 
    order_details ON menu_items.menu_item_id = order_details.item_id

GROUP BY 
    menu_items.category

ORDER BY 
    order_count ASC

Result: American 2734, Mexican 2945, Italian 2948, Asian 3470

Task 9: Again, a tree map is better illustration than a result table. To show this, the following code will combine most and least menu items and categories.

SELECT

menu_items.menu_item_id,

menu_items.item_name,

menu_items.category,

COUNT(order_details.order_id) AS order_count

FROM

menu_items

LEFT JOIN

order_details ON menu_items.menu_item_id = order_details.item_id

GROUP BY

menu_items.menu_item_id, 

menu_items.item_name

ORDER BY
    order_count ASC, menu_items.menu_item_id

Result: (Another result table that is sure to make the restaurant owner's eyes glaze over.)

The restaurant owner will appreciate the combined data visualization of, what is essentially, the last 3 result tables.

Conclusion #2: Advertising Asian menu items is most likely to boost order volume. Generally speaking, past product purchases predict future promotion performance.

Task 10: The next typical exploration is what the top 5 orders consist of in terms of categories. (Note the code optimization using aliases od and mi.)

SELECT 
    category, COUNT(item_id) AS num_items

FROM 
    order_details od LEFT JOIN menu_items mi

    ON od.item_id = mi.menu_item_id

WHERE 
    order_id IN (440, 2075, 1957, 330, 2675)

GROUP BY 
    category;

Result: Asian 17, American 10, Italian 26, Mexican 16

Conclusion #3: We should keep these expensive Italian dishes on our menu, because people order them often. They are especially popular for our highest-spend customers.

6. Challenging Assumptions

Finding/conclusion #1: Italian dishes bring in the most revenue per dish. Boosting Italian dish orders = faster ROI.

Finding/conclusion #2: Asian items are the most ordered items. Side note: In a separate finding, Asian cuisine brings in the most revenue. Advertising Asian menu items is most likely to boost order volume because, as previously pontificated, past product purchases predict future promotion performance.

Finding/conclusion #3: Italian cuisine is especially popular with our highest-spend customers. We should keep these expensive Italian dishes on our menu, because people order them often.

These finding/conclusions beg the question: What does item order volume look like across different receipt amounts? Is it realistic to think Italian cuisine, or Asian cuisine for that matter, is dominant across all receipt amounts?

Task 11: With a bar chart race, we may see that item dominance varies across receipt amounts. (See the live bar chart race version here: https://public.flourish.studio/visualisation/16569158/ )

Wow! That's dramatic. Item dominance varies dramatically at different receipt amounts. American cuisine dominates at some of the lower receipt amounts, Asian dominates some receipt amounts in the middle, and Italian dominates the tippy top end.

Task 12: Do you wonder if the order volume is as crazy as menu item performance at different receipt amounts? . . . Let's see what a histogram reveals.

Sure is! Turns out that order volume shifts dramatically after the receipt amount exceeds $60.00. A correlated translation means the restaurant hosts far fewer large dinner parties (i.e. a large number of diners per receipt) than tables having probably 1-4 guests per table.

7. Unfinished Work: Work to be done by the data analyst after being selected for the restaurant order analysis project.

Does average spend per diner increase as receipt amount increases?

Where should marketing dollars be spent? Are they better spent attracting bigger parties (e.g. family and work gatherings), attracting more Asian cuisine orders, attracting more Italian cuisine orders, or attracting particular receipt amounts (e.g. "Spend $60 or more and get a free appetizer!").

8. Report Summary

Italian cuisine dominates the sales by item on the upper end of receipt amounts and average item price, whereas Asian cuisine dominates sales by order volume (and overall sales revenue).

Data visualizations show great disparity at different receipt amounts.

There seems to be a lot of incentive to investigate increasing guest party size.

B. The Objectives (for the Restaurant Order Insights and Opportunities)

1. Demonstrate Data Storytelling and Data Visualization Acumen

Storytelling and data visualization acumen? You be the analyst!

2. Demonstrate Competitive Advantage

Compare this project to the game plan introduced above.

Compare this project to similar portfolio projects utilizing the same criteria. Filters: Most Popular, SQL, and Restaurant Orders (dataset) on Maven Showcase https://mavenanalytics.io/showcase?datasets=37&order=&sortBy=most_popular&tools=3

Are "we" off to a good start? If so, connect with me for work opportunities!