This project analyzes the sales data of a coffee shop to understand customer purchasing behavior, evaluate product profitability, assess sales performance across countries, and other key insights. The analysis aims to support the coffee shop in optimizing its operations, enhancing profitability, and building targeted marketing strategies.
Tools Used: Excel for data cleaning, pivot analysis, and dashboard creation; PowerPoint for presentation and insight communication.
- CoffeeShopData.xlsx: The cleaned and analyzed dataset.
- Dashboard: Interactive dashboard with slicers for profit margin, country, and coffee type.
- PowerPoint Presentation: Summarizes insights, visualizations, and recommendations.
- Data Cleaning:
- Checked for duplicates, filled missing values, removed empty columns, and verified formatting consistency.
- Data Consolidation:
- Used lookup functions to create a unified view for easier analysis.
- Profit Margin Calculation:
- Calculated profit margins for each product to understand profitability.
- Pivot Analysis:
- Created pivot tables and charts to visualize sales trends by coffee type, customer contribution to total sales, sales by country, and quantity ordered by coffee type.
- Most Purchased Products: Rob and Ara were the highest in sales volume but surprisingly had the lowest profit margins. This suggests that while these types are popular, they may not be optimized for profitability.
- Highest Profit Margins: Other coffee types with lower sales volumes may actually be more profitable per unit, highlighting an opportunity to boost awareness or incentivize purchases of these higher-margin products.
- Popular Product Configurations: The 0.2g size with a large roast type is the most commonly ordered combination, typically purchased without a loyalty card.
- Top-Contributing Customers: Only a small subset of customers (top 10 by sales) contributes disproportionately to total revenue, underscoring the value of retention strategies targeting high-value customers.
- Country-wise Sales: USA leads with 79% of sales, followed by Ireland (15%) and UK (6%). The overwhelming concentration in the US suggests opportunities for localized marketing and deeper market penetration.
- Top Sales Regions: Within the USA, the states with the highest sales volumes include Washington, Houston, New York, Los Angeles, and El Paso. Understanding preferences in these areas could further refine promotional strategies.
- Total Revenue: $45,134 across all time periods.
- Total Profit: Only $4,520, representing a profit margin of approximately 10%. This narrow margin suggests a need to re-evaluate pricing or optimize cost structures to improve profitability.
- The peak sales year was 2021, but a sharp decline followed, with sales dropping ~48.7% by 2022. This significant drop may warrant an investigation into factors such as market saturation, competition, or shifts in consumer behavior.
-
Revise Pricing for Popular Products
Rob and Ara’s popularity, despite low margins, suggests room to explore slight price adjustments or cost efficiencies to boost margins without affecting demand adversely. -
Targeted Marketing in the USA
Since the USA accounts for the majority of sales, focus on deepening market share in high-purchasing states through localized promotions and customer engagement efforts. -
Enhance Loyalty Program
With most orders made without loyalty cards, redesigning the loyalty program with enticing benefits may increase participation and encourage repeat purchases among existing customers. -
Investigate Sales Decline Causes
The sales drop after 2021 is concerning. Consider customer surveys or competitive analysis to understand the underlying causes and devise corrective strategies.
Dashboard light-mode:
Dashboard dark-mode:
For any feedback, send me an email at disephdum@gmail.com, using the repository title as the subject.