Embedded Files
A data-driven Business Intelligence dashboard designed to provide the bakery's management with a comprehensive view of marketing ROI, sales growth trends, and channel efficiency across regional markets and customer segments.
The Business Problem & Goal
Crust & Craft Bakery faced challenges in evaluating the effectiveness of its marketing investments across different channels (Google, Instagram, and Referrals). Without a consolidated view of how advertising spend relates to actual revenue, management struggled to identify which service types—Standard, Premium, or Seasonal—were driving growth and which regions were underperforming. This lack of transparency led to inefficient budget allocation and missed opportunities for customer retention.
The Goal
The Goal
The primary objective was to develop a comprehensive Data Analytics solution that bridges the gap between marketing costs and sales performance. The goal was to provide actionable insights into Marketing ROI, analyze customer behavior (New vs. Returning), and evaluate regional performance. By visualizing these key metrics, the project aims to empower executive management to optimize advertising budgets, refine service offerings, and maximize overall profitability.
Analytical Process & Data Modeling
Analytical Process & Data Modeling
Data Preparation (Power Query):
Data Preparation (Power Query):
- Streamlined the data cleaning process using Power Query within Excel.
- I performed ETL (Extract, Transform, Load) tasks to clean 500+ records, ensuring all dates were formatted correctly and categorizing "Service Types" to eliminate any inconsistencies in the raw dataset.
Advanced Formula Engineering:
Developed complex Excel formulas to calculate essential business metrics without the need for external tools. This included:
Using SUMIFS & Pivot Calculations to derive Total Revenue and Ad Spend by region.
Creating custom formulas to calculate Marketing ROI and Conversion Rates per channel.
Pivot Table Modeling:
Structured the data using Pivot Tables to create a dynamic multi-dimensional model.
This allowed for instant grouping of data by Region (North, South, East, West) and Customer Segment (New vs. Returning), providing a flexible way to slice through the bakery's performance.
Interactive Dashboard Design:
Built a fully interactive dashboard using Excel Slicers, Timelines, and Dynamic Charts.
The design allows management to filter the entire report with a single click, providing a "user-friendly" interface that transforms static spreadsheet data into a strategic decision-making tool.
The Interactive Solution (Full Dashboard Demo)
The Interactive Solution (Full Dashboard Demo)
Key Insights & Findings
Key Insights & Findings
High-Value Service Tiers Driving Revenue:
The Premium and Seasonal service categories are the primary revenue drivers. While Standard services have a high volume, Premium offerings contribute a significantly higher profit margin per transaction.
Marketing Channel ROI Variance:
There is a distinct performance gap between channels; Instagram and Referrals demonstrated the highest Return on Investment (ROI), whereas Google Ads showed higher spending with fluctuating conversion rates.
Customer Loyalty & Retention Impact:
Granular analysis reveals that Returning Customers have a 25% higher average order value compared to New Customers, particularly favoring "Premium" services, which highlights the success of retention strategies.
Regional Market Dominance:
The North and West regions emerged as the top-performing markets. The dashboard identifies these areas as core revenue hubs, while the South region shows potential for growth if marketing spend is optimized.
Conversion Efficiency Insights:
By tracking the "Cost Per Conversion," the analysis identified specific days where Ad Spend peaked without a proportional rise in Conversions, indicating a need for better ad-timing and targeting during off-peak hours.
Business Impact & Conclusion
The Crust & Craft Bakery Marketing & Sales Analysis solution transformed raw transactional data into a strategic roadmap for growth. By bridging the gap between advertising costs and revenue, the management team gained the clarity needed to make data-driven investment decisions. The analysis led to the immediate optimization of marketing budgets, shifting focus toward high-ROI channels like Instagram and Referrals. Additionally, by identifying the high profitability of Premium and Seasonal services among Returning Customers, the bakery was able to refine its loyalty programs and regional targeting strategies (specifically in the North and West regions) to maximize long-term sustainability and profit margins.
Business Impact & Conclusion
The Crust & Craft Bakery Marketing & Sales Analysis solution transformed raw transactional data into a strategic roadmap for growth. By bridging the gap between advertising costs and revenue, the management team gained the clarity needed to make data-driven investment decisions. The analysis led to the immediate optimization of marketing budgets, shifting focus toward high-ROI channels like Instagram and Referrals. Additionally, by identifying the high profitability of Premium and Seasonal services among Returning Customers, the bakery was able to refine its loyalty programs and regional targeting strategies (specifically in the North and West regions) to maximize long-term sustainability and profit margins.
Page updated
Google Sites
Report abuse