In this project, I take on the role of Business Intelligence Analyst in order to gain insights and provide recommendations to drive business success as well as customer engagement. With the raw data provided, I utilized MySQL for ETL (Extract, Transform, Load) processes and Exploratory Data Analysis (EDA).

Goals

My first goal for this project was to understand the two tables.

The first table included data points on menu items that the restaurant offers, such as item ID numbers, names of dishes, the category of the dishes (American, Mexican, Asian, Italian), and price per dish.

The second table contained records of every order placed from January 1, 2023 to March 31, 2023. Details of the orders included unique order ID numbers, date and time of purchase of the order, and item ID numbers for every dish purchased per order.

My next goal was to combine the two tables into one in order to get a more complete perspective.

To achieve this, I used a LEFT JOIN where the menu item ID number columns from both tables could be used to complete the query.

SELECT *

FROM order_details

LEFT JOIN menu_items

    ON menu_items.menu_item_id=order_details.item_id

Business Needs

The business needed to understand customer behavior in order to maximize efficiency. In order to do this, I analyzed:

• Least and most expensive items on the menu;
• Number of dishes in each category;
• Average dish price per category;
• Least and most ordered items on the menu;
• The orders with the highest number of items per purchase and;
• Top 5 orders in terms of total spending.

Insights

I was able to determine the following to the above business needs:

• The least expensive item is Edamame - an Asian dish - and it costs $5.00.
• The most expensive item is Shrimp Scampi - an Italian dish - and it costs $19.95.
• The number of dishes in each category is quite balanced. 6 American, 8 Asian, 9 Mexican, and 9 Italian dishes are offered at this restaurant.
• Average dish price per category: American - $10.07, Asian - $13.48, Mexican - $11.80, Italian - $16.75.
• The least purchased items were Chicken Tacos (123 - Mexican), whereas the most purchased were Hamburgers (622 - American).
  • Chicken Tacos cost $11.95, which is slightly higher than the average Mexican dish price. This could be contributing to the lower sales. However, Hamburgers cost $12.95, which is also higher than the average American dish price.
    • Perhaps the clientele simply prefers American options, but the restaurant may want to take a closer look into Chicken Taco sales going forward or consider removing it from the menu entirely.
• The 7 orders with the highest number of items per purchase (14) averaged 2.9 American, 3.1 Asian, 4.3 Italian, and 3.7 Mexican items orders.
  • Italian food appears to be the most ordered category of menu options among orders with high quantities.
  • Even though American dishes average out as least expensive, the customer base still tends to lean more toward Italian dishes - on average the most expensive items.
• Top 5 orders in terms of total spending breaks down as follows:
  • American = 2 items/order
  • Asian = 3.4 items/order
  • Italian = 5.2 items/order
  • Mexican = 3.2 items/order
  • Italian and Asian items appear to be the most popular dishes as they have the highest average of quantities per order.
  • Considering that those exact two categories are also the two most expensive, it makes perfect sense why the total spent in these orders are inflated.