Dining Dynamics: Analyzing Customer Trends at Danny’s Diner
Introduction
Danny, who is truly enthusiastic of Japanese cuisine, taking a risky venture at the start of 2024. With strong determination, he opens a lovely little restaurant named ‘Danny’s Diner, specializing in his three beloved dishes: Sushi, Curry, and Ramen.
Problem Statement
Danny’s Diner seeks assistance in interpreting their operational data to ensure its survival. While they have gathered some basic information over their initial months, they are unsure how to use their data to run the business in the right direction.
Objective
Danny aims to gain insights about his customers’ behaviour, including their visiting patterns, spending habits, and preferred menu items. Using this knowledge, he intends to build deeper connections with his customers, which will help him deliver a better and more personalised experience for his loyal customers.
Datasets Provided
Danny has shared a sample dataset containing information of three customers due to privacy concerns. Despite its limited size, he believes that this sample provides sufficient data for analysis:
Table 1: Sales
The sales table provides all customer level purchases with a corresponding order date and product IDs — Information of when and what menu items were ordered.
Table 2: Menu
The menu table maps the Product ID to the actual Product Name and price of each menu item.
Table 3: Members
The members table provides the join date when a customer joined the Danny’s Diner loyalty program.
Entity Relationship Diagram
My aim is to explore the datasets provided by Danny to find useful information that will help Danny’s Diner do well in a competitive market and give its loyal customers even better dining experiences. Danny’s Diner keeps track of its business using three main lists: Sales, Menu, and Members. Each list helps keep track of various parts of the business, like what customers buy and who is in the loyalty program.
In the diagram below, we can see how these lists connect to each other using the ‘primary keys.’ For example, the “customer_id” connect the sales list with the members list, so we can see who is buying what and who is in the loyalty program. Similarly, the “product_id” connects the sales list with the menu list, showing us which menu items are selling the most. By analysing these connections, Danny’s Diner can extract actionable insights to improve customer satisfaction, refine menu offerings, and optimize overall operational efficiency.
Analytics Questions
Before starting the analysis, let us identify some business questions related to our data:
1. What is the total amount each customer spent at the restaurant?
2. How many days has each customer visited the restaurant?
3. When was the last time a customer purchased from the restaurant?
4. What is the most purchased item on the menu and how many times was it purchased by all customers?
5. What are the total items and amount spent for each member before and after they became a member?
6. Return ALL the customers and their join date?
7. Which customer made the least purchase?
Analysis and Exploration
To conduct the analysis, I utilized SQL Server Management Studio (SSMS). You can employ any other tool for analysis.
Step 1: Creating the Database, Tables, and Inserting Sample Data to respective tables:
Step 2: Analysing business questions:
The SQL query calculated the total amount each customer spent at the restaurant by joining the Sales and Menu tables based on the product Id. Below are total amount spent by each customer:
The analysis reveals varying levels of spending among customers, with total expenditures ranging from £36 to £76. This indicates diversity in customer purchasing behaviour at Danny’s Diner. Let’s see the pattern of days customer visited the restaurant for having a better clarity of the analysis:
The SQL query analysed the total number of days each customer visited Danny’s Diner by counting the unique order dates in the Sales table for each customer. Below are the visitation frequencies for each customer:
These visitation patterns reflect diverse customer behaviours, ranging from regular visitors to occasional visitors.
The SQL query examined the last purchase dates for customers at Danny’s Diner by identifying the maximum order date from the Sales table for each customer. Below are the last purchase dates for each customer:
Knowing when a customer last visited the Diner incorporating the frequency of visiting from Question 2, helps us see how interested they are in coming back. Based on what we found, Customer B seems really interested and might come back again.
This SQL query retrieves the most purchased menu item by counting the occurrences of each product ID in the Sales table. It joins the Menu and Sales tables based on the product ID and groups the results by the product name. Then, it orders the groups in descending order by the count of purchases and selects the top record to identify the most purchased item.
The results show that ‘Ramen’ was the most purchased item, with a total of 8 orders. Promoting customers to buy popular menu items like ramen can increase sales and make customers happier, bringing in more revenue for the restaurant.
The SQL query retrieves all customers and their join dates by performing a right join between the Members and Sales tables on the customer ID, ensuring that all customers are included regardless of their purchase history. The DISTINCT keyword ensures unique combinations of customer ID and join date are returned.
Result:
Customers A and B signed up for the loyalty program on different days, but Customer C has not joined yet, suggesting there’s opportunity to explore alternative methods to encourage them to join the program.
Result :
Result:
When comparing the total sales before and after joining the loyalty program, overall, there is an increase in both the number of items purchased and the total sales amount. However, looking at individual sales, Customer A showed a notable increase in both items bought and spending, indicating a positive impact with the program. On the other hand, Customer B’s spending decreased slightly after joining, even though the number of items purchased stayed the same. This suggests that the loyalty program may have different effects for each customer, due to varying levels of involvement or benefits received.
The SQL query identifies the customer who made the least purchase by summing up the prices of menu items purchased by each customer, grouping them by customer ID, and then ordering the results in ascending order of total purchase amount.
The result indicates that Customer C made the least purchase, totalling £36, suggesting they may be less engaged or visit the restaurant less frequently compared to other customers.
Dashboard
Observations
· Customer spending varies, with Customer A being the highest spender at £76, followed by Customer B at £74, and Customer C at £36.
· Visitation patterns differ among customers, with Customer B visiting the restaurant the most frequently (6 days), followed by Customer A (4 days) and Customer C (2 days). Customers A and B being part of the loyalty program might be influencing their more frequent visits compared to Customer C.
· ‘Ramen’ is the most purchased menu item, indicating its popularity among customers.
· Customer joining dates for the loyalty program vary, with Customer A joining on 2024–01–07 and Customer B joining on 2024–01–09, while Customer C has not yet joined.
· Joining the loyalty program increased sales and item purchases in total. Yet, individual responses varied: Customer A spent more, while Customer B’s spending slightly dropped despite unchanged purchases, indicating differing program impacts based on customer engagement or benefits.
· Customer C made the least purchase, spending £36. Since Customer C has not joined the loyalty program, this may be a contributing factor to their lower spending.
Suggestions
· Focus promotions on popular items like ‘Ramen’ to boost sales and make customers happier.
· Encourage Customer C to join the loyalty program by trying different approaches to increase customer engagement.
· Offer personalized rewards tailored to each customer’s spending habits to make the loyalty program more effective and appealing.
· Analyse customer data regularly to improve menu options, promotional plans, and overall customer satisfaction for long-term success
