Willy wonka factory data analysis with SQL by BCG (Blanca, Jussara and Rodrigo)
Wonka Chocolate Factory – Revenue Optimization Analysis
Project Overview This project analyzes Wonka Chocolate Factory’s sales, cost, factory performance, and customer purchasing behavior to identify practical opportunities to increase revenue and profitability. By examining product-level profitability, factory and location efficiency, and customer loyalty patterns, the analysis aims to answer a core business question:
Where should Wonka focus its resources to maximize revenue while minimizing cost inefficiencies?
Project Structure:
├───Data │ ├───Processed │ │ customer_loyalty.csv │ │ factories_profit.csv │ │ products_profit.csv │ │ products_profit_margin.csv │ │ product_sales_factory.csv │ │ profit&sales_factory.csv │ │ profit_margin_factory.csv │ │ │ └───Raw │ wonka_choc_factory.csv │ ├───Notebooks │ .DS_Store │ 03_charts_sql.ipynb │ factory&product_sales.ipynb │ ├────Queries │ Hypothesis_1.sql │ Hypothesis_2.sql │ Hypothesis_3.sql │ Schema.sql │ .DS_Store │ .gitattributes │ .Rhistory │ Erd_image.png └──README.MD
Key Findings & Business Insights Product Profitability Concentration Finding: A limited number of products generate the majority of Wonka’s gross profit, while several products show high costs with relatively low sales or profit contribution. Insight: Revenue growth is driven by focus, not volume. Continuing to support low-margin products reduces overall profitability and limits investment in high-performing products.
Factory and Location Performance Finding: Profitability varies significantly by factory and city. Some factories consistently generate positive gross profit, while others operate at low efficiency or near-negative contribution. Insight: Wonka is not equally profitable across all production locations. Location-specific cost structures and logistics play a decisive role in revenue performance.
Shipping Mode Efficiency Finding: Premium shipping modes (First Class) are associated with higher operational costs and are not consistently linked to higher sales or profit. Insight: Shipping decisions directly affect margins. Overuse of premium shipping erodes profitability without delivering proportional revenue benefits.
Customer Loyalty and Revenue Stability Finding: Customers who place repeat orders and purchase higher unit volumes contribute more consistently to revenue and show higher lifetime value. Insight: Revenue stability depends heavily on loyal customers. Faster and more reliable fulfillment supports repeat purchasing behavior and long-term revenue growth.
Final Insight Wonka can increase revenue not by expanding its product line or factory footprint, but by strategically focusing on what already works best. This analysis demonstrates that targeted optimization across products, locations, and customer relationships provides the strongest path to sustainable revenue growth.
Project presentation link:
https://www.canva.com/design/DAG7O-orl6w/DYyOagM8eVlHyvt1v7GMYg/edit