Uncovering Sales Insights for Maven Roasters Coffee Shop Chain: A Data-Driven Approach
Uncovering Sales Insights for Maven Roasters Coffee Shop Chain: A Data-Driven Approach
Embedded Files
For millions of people, coffee is more than just a beverage—it's a way of life, a passion, and a culture. Coffee shops are places where connections are made and stories unfold, from the comforting warmth of a perfectly brewed coffee to the rich aroma of freshly ground beans. In this project, we examine the financial records of a fictitious chain of New York City coffee shops called Maven Roasters. We hope to gain insightful knowledge about customer behavior, product trends, and sales performance by utilizing Maven Analytics data. Ultimately, we want to use data-driven decision-making to propel business growth. Come along as we investigate the intriguing realm of coffee shop sales and identify areas for innovation and optimization.
For millions of people, coffee is more than just a beverage—it's a way of life, a passion, and a culture. Coffee shops are places where connections are made and stories unfold, from the comforting warmth of a perfectly brewed coffee to the rich aroma of freshly ground beans. In this project, we examine the financial records of a fictitious chain of New York City coffee shops called Maven Roasters. We hope to gain insightful knowledge about customer behavior, product trends, and sales performance by utilizing Maven Analytics data. Ultimately, we want to use data-driven decision-making to propel business growth. Come along as we investigate the intriguing realm of coffee shop sales and identify areas for innovation and optimization.
Objectives :
Objectives :
Calculate total sales revenue and order quantity for each month.
Identify month-on-month sales and order trends.
Quantify differences in monthly sales and order quantities.
Identify top-selling products and analyze category performance.
Compare sales across different locations.a
Analyze sales variations by day of the week and time of day.
Track individual product sales over time.
Explore correlations between product types and sales performance.
Data Overview :
Data Overview :
Dataset Source: Kaggle, uploaded by Ahmed Abbas link is here.
Scope: Transaction records for Maven Roasters, a fictional coffee shop chain with three NYC locations.
Data Includes:
Dates & Times: Transaction dates and timestamps.
Locations: Store locations in NYC.
Product Details: Product ID, category, type, and detailed descriptions.
Sales Metrics: Quantity sold and unit price.
Purpose: Educational use and practicing data analysis techniques.
Methodology :
Methodology :
This section outlines the SQL queries used for data cleaning and preparation. The process involves creating a copy of the original data, verifying the copied data, checking for duplicates, identifying null values, and removing problematic records. These steps ensure the data is accurate and ready for analysis.
This section outlines the SQL queries used for data cleaning and preparation. The process involves creating a copy of the original data, verifying the copied data, checking for duplicates, identifying null values, and removing problematic records. These steps ensure the data is accurate and ready for analysis.
**To check original queries, please refer to Github Gist embedded below.**
Data Clexaning
Data Clexaning
Creating a Copy of the Data : A backup of the original table is created for safe data cleaning and analysis.
Creating a Copy of the Data : A backup of the original table is created for safe data cleaning and analysis.
Verifying Table Creation : Ensures the table copy was successful and lists all records.
Printing Column Names : Retrieves and prints column names for reference
Checking for Duplicate Transaction IDs : Identifies duplicate transaction IDs.
*Dynamic SQL for Duplicates Check Across All Columns : Dynamically check for duplicates in all columns without writing repeating queries for all columns.
Checking for Null Values in transaction_date : Counts records with null transaction dates.
Dynamic SQL for Null Check Across All Columns : Dynamically check for null values in all columns without writing repeating queries for all columns.
Removing Records with Null transaction_date: Deletes records with missing transaction dates.
SQL Analysis
SQL Analysis
Total & Month-on-Month Sales Growth
Total & Month-on-Month Sales Growth
Total & Month-on-Month Order Growth
Total & Month-on-Month Order Growth
Sale Difference Between Months
Sale Difference Between Months
Order Difference Between Months
Order Difference Between Months
Monthly Top-Selling Product & Product Type Based on Quantity Sold
Monthly Top-Selling Product & Product Type Based on Quantity Sold
Monthly Top-Selling Product & Product Type Based on Sales Revenue
Monthly Top-Selling Product & Product Type Based on Sales Revenue
Top-Selling Products overall
Top-Selling Products overall
Sales Performance Across Store Locations
Sales Performance Across Store Locations
Top Store Locations Each Month Based on Total Sales
Top Store Locations Each Month Based on Total Sales
Hourly Variation and Peak Sales Hours
Hourly Variation and Peak Sales Hours
Weekly variation & Peak Sales day
Weekly variation & Peak Sales day
Key Findings
Key Findings
Sales and Orders insights:
Highest sales are recorded in August, followed by October and September.
Top orders are also recorded in August, with October marginally surpassing September in the number of orders.
June and July have the lowest sales and orders.
Seasonal , Monthly Sales and Order Trends:
Sales and orders decrease from the start to the end of summer.
Sales and orders increase as winter approaches, showing a trend of higher activity in colder months.
Sales and orders increase at the beginning of winter and start to decrease as summer approaches.
Product Performance:
Coffee is the top-selling product in both sales and order numbers, followed by teas and bakery items, with slight month-to-month variations.
Tea maintains more or less constant sales and good order numbers, with a decrease in sales during summer months and a rise as colder months approach.
Bakery products perform well in summer but generate less revenue compared to coffee and tea during colder months.
Other products follow more or less similar trends to coffee and tea, with variations in performance based on seasonal changes.
Top Revenue Locations:
Hell's Kitchen leads in overall sales and order numbers.
Astoria and Lower Manhattan follow, with almost equal shares of sales distribution.
Product-Specific Trends:
For coffee, sales decrease each month with a gradual spike in August and September.
For tea, sales and orders are relatively stable, with decreases in summer and increases in colder months.
For bakery products, sales and orders peak during summer and decrease during colder months.
Other products exhibit trends similar to those of coffee and tea, with seasonal variations impacting their performance.
**For a detailed view, you can explore the Power BI dashboard here and SQL queries notebook here. These will offer \data visualization to support the analysis.**
Conclusion
Conclusion
Seasonal Influence on Sales and Orders:
From the analysis we can see seasonal pattern where sales and orders dip during the summer months and rise during the winter months. This could be influenced by consumer behavior, with more people likely to consume hot beverages like coffee and tea during colder months.
Product Preferences:
Coffee remains the most preferred product regardless of the season, indicating strong customer loyalty and preference. Teas also show consistent performance, suggesting a stable customer base.
Location-Based Performance:
Hell's Kitchen stands out in sales and order numbers, possibly due to higher foot traffic or better marketing strategies. The nearly equal distribution of sales between Astoria and Lower Manhattan suggests a balanced market presence.
Actionable Insights:
Marketing and Promotions: Consider ramping up marketing efforts and promotions for coffee and tea products during the colder months to maximize sales. For bakery items, focus on summer promotions to leverage their peak performance during that season.
Page updated
Google Sites
Report abuse