- Situation: As the new franchise owner of Maven Roasters, a beloved coffee shop chain with three bustling locations in New York City, you're poised to brew success.
- Assignment: With a rich blend of transactional data from January to June 2023 at your fingertips, your mission is to distill insights that will refine purchase behavior and optimize operations. Dive into the data to discover:
- The total number of transactions
- The period these transactions cover
- The variety of products and categories sold
- Transform this data into a dynamic dashboard that will empower franchise owners to spot patterns, trends, and lucrative opportunities.
- Source: Next Coffee Shop
- License: Public Domain
- Downloaded from https://www.mavenanalytics.io/data-playground
- Revenue Trends: From the crisp winter start of January to the warm embrace of June, Maven Roasters' revenue has shown a robust upward trend across all locations, with a slight chill in February's figures. To counter the winter dip, a Winter Warmer Campaign featuring a unique Hot Chocolate flavor could be the perfect blend to spice up sales in the cooler months.
- Store Performance: The three NYC stores have collectively poured an impressive $698,812 from 149,116 transactions, serving 214,470 products. Hell's Kitchen leads the pack with $236,511, followed closely by Astoria with $232,244, and Lower Manhattan at $230,057. With an average monthly growth of 17.5%, the stores have consistently ground out increasing profits, peaking in June.
- Transactional Heatmap: The weekdays brew the most activity, with Friday, Thursday, and Monday being the peak days. In contrast, Saturday simmers down as the least popular day. To perk up the weekend vibe, a Saturday Special Promotion could be just the jolt needed to attract more patrons.
- Rush Hour Readiness: The hours between 8 AM and 10 PM are when the coffee buzz is at its peak. It's recommended to steam up staffing during these hours, especially in the morning. For Astoria, an extra shot of afternoon and evening staff could smooth out operations.
- Product Popularity: Coffee, with 58,416 transactions, and Tea, with 45,449 transactions, are the hot favorites. The Bakery category, with 22,796 transactions, is also steeping with potential. A combo deal pairing Coffee or Tea with a Bakery item, or a tempting Buy-One-Get-One-Free promotion offer, could sweeten the deal and froth up additional sales.
- Data Profiling: Whip the raw data into shape, ready for analysis.
- Data Exploration: Use Excel PivotTables to sift through the data.
- Dashboard Construction: Craft a dynamic dashboard that visually expresses the data's story.
- Revenue Calculation: A new column now captures the essence of earnings.
- Temporal Insights: New columns unveil the Month and Day of the Week from each transaction date, while another extracts the Hour from transaction times.
- Monthly Revenue: A PivotTable blooms on a new tab, revealing revenue trends over months.
- Transactional Rhythms: Additional PivotTables rhythmically display transactions by day of the week and hour of the day.
- Product Popularity: A PivotTable ranks product categories by transaction count, descending into insight.
- Top 15 Products: Another PivotTable filter through the noise to spotlight the Top 15 products by transactions and revenue.
- Visual Narratives: Charts now narrate the tale of revenue by month (line chart), transactions by day and hour (column charts), and product category popularity (bar chart).
- Interactive Slicing: A slicer for store location now serves as a conduit, linking all charts for seamless interaction.
- Final Touches: A careful curation of formatting, alignment, and polish brings the dashboard to life, ready to guide Maven Roasters toward informed decisions and strategic moves.
If you have any feedback, please reach out to us at rajputdevyansh9@gmail.com or https://www.devyanshrajput.live