Danny’s Dashing Diner!
Case Study #1 of Danny Ma’s #8WeekSQLChallenge
INTRODUCTION
As a massive lover of Japanese dishes, Danny embarked on a rather risky venture — he opened a Japanese restaurant that sold his three favourite dishes — sushi, curry and ramen, at the beginning of 2021.
PROBLEM STATEMENT
Danny reached out to me after he saw my brilliant work with parch & posey. He needed information on his customers especially their visiting patterns, how much money they had spent and also their favourite items on the menu. A deeper connection with his customers would help him deliver a better and more personalised experience for his loyal customers.
He wanted to know whether or not to expand the existing customer loyalty program.
DATA
Danny provided a sample of his overall customer data due to privacy issues but hoped that these examples would be enough to produce the insights using SQL.
Three key datasets for the analysis:
salesmenumembers
NOTE: The prices of the products:
sushi — $10
curry — $15
ramen — $12
QUESTIONS
Before we delve into the questions, all queries to get the answers can be found in my github repository and postgresql was used for this project.
- What was the total amount each customer spent at the restaurant?
Customer A had spent a total of $76 since sales commenced, customer B, $74 and customer C, $36.
2. How many days had each customer visited the restaurant?
Customer B enjoyed spending time at the diner with a total of 6 visits.
3. What was the first item from the menu purchased by each customer?
Customers A and B launched their Danny’s Diner journey with curry and customer C started with ramen.
4. What is the most purchased item on the menu and how many times was it purchased by all customers?
Ramen had the highest number of purchases with 8 orders. It would seem customer C was on to something on the first day.
5. Which item was the most popular for each customer?
The in demand ramen topped the highest number of purchases for customers A and C with three orders each. Customer B loved all the dishes equally with all three dishes being ordered twice.
6. Which item was purchased first by the customer after they became a member?
On opening the restaurant, Danny had no members. After a while, customers A and B became members and their first orders after becoming members were curry and sushi, respectively. As at the time of this analysis, customer C was yet to pick up the membership form.
7. Which item was purchased just before the customer became a member?
The items ordered before customers A and B became members were sushi and sushi; yes, both customers ordered sushi.
8. What was the total number of items and amount spent for each member before they became a member?
As stated earlier, Danny had no members then customers A and B became members. However, before becoming members, customers A had spent $25 on two orders and customer B had spent $40 on three orders.
9. If $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?
On every purchase made, customers were awarded points ($1 = 10 points). Ordering a sushi was 100 points(200 points with the 2x multiplier), curry was 150 points and ramen was 120 points. After accumulating the purchases of each customer, I tallied the results and arrived at the following points:
Customer A — 860 points.
Customer B — 940 points.
Customer C — 360 points.
10. In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi — how many points do customer A and B have at the end of January?
For this question, every order within seven days of becoming a member was double the points. Ordering a sushi was 200 points, curry 300 points and ramen 240 points within seven days. After seven days, the 2x points ended. At the end of January, customer A had 1,020 points and customer B had 320 points.
INSIGHTS
Ramen would seem to be the favourite dish due to the number of orders so far. Research can be done on how to improve the curry and sushi dishes.
Of the three customers produced in the dataset, two had become members. My advice to Danny would be to delay the expansion of the program for a little while; let the current wave ride a little bit more before really going for an expansion of the customer loyalty program.
CONCLUSION
This case study was part of Danny Ma’s #8WeekSQLChallenge. The case study itself can be found here. It was another avenue to work with SQL and improve my skills, looking forward to more projects!
You can connect with me on LinkedIn!