Showcase Excel - Coffee Shop Dashboard

Maven Analytics' Guided Project – Coffee Shop Dashboard

Summary

1. Business Case

Business Problem: There is a current lack of insight regarding purchasing behavior, patterns, and trend development.

Situation: You have recently become a franchise owner at Maven Roasters, a coffee shop chain with three locations in New York City.

Task: To better understand purchase behavior in order to streamline operations based on transactional data collected from January to June 2023.

Objective: Transform the data into a dynamic dashboard that franchise owners can use to identify patterns, trends, and further explore business opportunities.

Core Objectives:

• Prepare Data for Further Analysis
• Explore the coffee shop dataset
• Conduct basic data QA and profiling
• Add calculated date and time fields to prepare the data for analysis

---

Explore the Data with Pivot Tables:

• Slice and dice the coffee shop data with Excel PivotTables.
• Create views to analyze time series and product-level trends.

---

Build a Dynamic Dashboard:

• Visualize the data with Pivot Charts, applying Gestalt principles such as “visual hierarchy.”
• Design an interactive dashboard to identify insights and provide recommendations.

---

2. Show Insights & Impact

Impact on the Business:

Revenue:

• Total revenue for the period January to June 2023: $698,812.
• The months of June ($166,486) and May ($156,728) were the most successful periods.
• The "Hells Kitchen" location generated the highest revenue: $236,511.
• The top 15 product types accounted for 90% of the revenue ($629,499). "Brewed Chai Tea" was the most valuable product ($77,082), followed by "Gourmet Brewed Coffee" ($70,035).

Transactions:

• Total transactions recorded from January to June 2023: 149,116.
• The months of May (33,527) and June (25,335) saw the highest number of transactions.
• The "Astoria" location recorded the highest number of transactions: 50,599.
• The top three weekdays for transactions were Monday (21,643), Thursday (21,654), and Friday (21,701).
• The "Lower Manhattan" location was the most successful on Mondays, while success at the other two locations was more evenly distributed.
• The most successful opening hours were between 7 and 10 PM, totaling 67,391 transactions.
• Pay attention to the opening hours between 6 and 8 PM for the "Lower Manhattan" location. Data analysis shows the fewest number of transactions during this time, total 200.
• The most popular product categories were "Coffee" (58,416 transactions) and "Tea" (45,449 transactions).
• The least popular product categories were "Branded" (747 transactions) and "Packaged Chocolate" (487 transactions).
• The top 15 product types accounted for 97% of transactions (144,919), with "Brewed Chai Tea" (17,183 transactions) and "Gourmet Brewed Coffee" (16,912 transactions) being the most popular.

Recommendations:

• Increase marketing efforts and promotions during the 6 to 8 PM time slot at the "Lower Manhattan" location to boost transactions.
• Otherwise consider and investigate the impact of reducing opening hours.
• Continue to focus on top-selling products like "Brewed Chai Tea" and "Gourmet Brewed Coffee" to maximize revenue.
• Consider analyzing customer feedback or conducting surveys to understand the lower popularity of "Branded" and "Packaged Chocolate" products and take steps to improve their appeal.

---

3. Data Storytelling

• Visualizations Used - Storytelling:
• Slicer: Store Location
• Line Chart: Monthly trend in revenue and transactions
• Column Chart(s): Display transaction trends by week and hour of the day
• Bar Chart: Breakdown by Product Category and Top 15 Product Types
• Line Chart: Displays high-level trends
• Inserted Shapes: To display total revenue and transactions (quick insights).
• 10-Second Rule: Visualizations are designed to be easily understood within 10 seconds, ensuring quick and effective communication of insights
• Gestalt Principles: Composite and color use to enhance visual hierarchy

4. Provide Technical Depth

• Excel: Utilized for creating the dashboard
• Data: Transaction records from January to June 2023
• Data Structure: Single tables transformed into necessary PivotTables
• Data Volume: 149,116 records with 11 fields.
• Design Enhancements: Removed all gridlines and reduced the use of white space by implementing a coffee-themed wallpaper in the background to create a coffee environment dashboard

Conclusion

• The "Summary Coffee Shop" dashboard effectively consolidates and visualizes key business metrics, providing the leadership team with valuable insights into revenue, transactions, trends, patterns, and future opportunities. The use of interactive elements and best-practice visualizations ensures that the data is presented in a clear and accessible manner.