- Tehmina Aziz
- Sevil Kucuk
- Rutvik Pimpalkar
- Hande Gabrieli-Knobloch
This report provides a comprehensive analysis of the company's business performance from 2015 to 2018, leveraging key operational and financial metrics. Overall, the period showcased robust sales growth, culminating in Total Sales of $2,297,355 and Total Profit of $286,347, yielding an Overall Profit Margin of 12%. While growth trends were generally positive through 2017, a notable decline in Q4 2018 warrants further investigation.
Performance is concentrated in key product categories (Technology, Office Supplies) and specific regions (East, West, New York City). A critical challenge identified is persistent negative profitability in certain product lines, likely influenced by aggressive discounting and/or high Cost of Goods Sold (COGS). This report integrates insights from both Marketing Specialist and Data Scientist perspectives to offer actionable recommendations for optimized financial health and sustained growth.
- Total Sales:
$2,297,355 - Total Profit:
$286,347 - Profit Ratio:
1.205 - Average Sales per Order:
$230 - Average Profit per Order:
$29 - Overall Profit Margin:
12% - Total Units Sold:
37,873
- Fluctuating Growth Trajectory (2015-2018): Overall positive but inconsistent sales and profit trends.
- Mid-Period Peak Performance (2017 Q3): Achieved highest quarterly performance at
~$30.09K. - Significant 2018 Q4 Decline: Sharp drop to
~$9.14K, contrasting prior growth. - Actionable Insight: Requires further analysis to understand drivers of strong mid-period growth and recent decline.
- Concentrated Sales Regions: Sales volume is notably concentrated in specific geographic clusters, particularly in the Northeastern and West Coast (California, Washington) regions of the U.S.
- Varying State-Level Performance: There's a clear disparity in sales performance across states, with several states exhibiting significantly higher sales volumes (represented by larger circles) compared to the majority.
- Sparse Central U.S. Activity: Sales activity appears relatively sparse across the central United States, indicating potential untapped markets or lower penetration in these areas.
- Regional Dominance: The visual data suggests that sales are heavily driven by a few dominant states or metropolitan areas within the identified high-performance regions.
- Consumer Segment Dominance: The Consumer segment accounts for the largest share of revenue at
50.6%, indicating it's the primary revenue driver. - Significant Corporate Contribution: The Corporate segment contributes a substantial
30.7%of total revenue, making it the second most impactful segment. - Lower Home Office Share: The Home Office segment represents the smallest portion of revenue at
18.7%. - Segmented Revenue Focus: Over
80%of revenue is derived from the Consumer and Corporate segments, highlighting these as core target groups.
- Technology Leads Profitability: Technology is the most profitable category, generating significantly higher profit compared to others.
- Strong Office Supplies Contribution: Office Supplies is a substantial contributor to profit, closely following Technology.
- Lower Furniture Profitability: Furniture shows considerably lower profit generation compared to Technology and Office Supplies.
- Category Profit Disparity: There's a clear disparity in profit contribution across categories, with Technology and Office Supplies being the primary profit drivers.
- "Other" Dominance: The "Other" manufacturer category significantly leads in total sales, surpassing individual manufacturers.
- Top Tier Performance: Hon and Global are the leading named manufacturers, demonstrating substantial sales contributions.
- Long-Tail Distribution: A considerable drop-off in sales volume is observed after the top few manufacturers, indicating a long-tail distribution where many smaller manufacturers contribute less individually.
- Key Manufacturer Contribution: The top 5-6 manufacturers (excluding "Other") collectively represent a significant portion of total sales, highlighting their importance to revenue generation.
Why Might We Have Chosen to Focus on the Technology Category and the Top 10 Manufacturers in This Area?
- Highest Profitability: The visuals clearly indicate that the "Technology" category generates significantly higher profit (
$145.43K) compared to other categories. Concentrating on the most profitable area implies investing where the returns are maximized. - Market Size and Potential: The "Other" category's dominance in overall sales suggests a broad and diverse manufacturer base within the technology sector, implying a large and varied market. Focusing on the top 10 manufacturers means targeting the most impactful players in the market.
- Strategic Focus: Rather than engaging with all product categories and manufacturers, focusing on the most profitable category and its major players ensures a more efficient allocation of resources.
- Maximized Profit Generation: Concentrating on the most profitable category maximizes the potential to increase the company's overall profitability.
- Resource Efficiency: Resources such as marketing, sales, R&D, and inventory management can be utilized more effectively by directing them towards the area generating the highest revenue and profit.
- In-depth Market Understanding: Focusing on the top 10 manufacturers within the technology category allows for a deeper understanding of their strategies, products, pricing, and customer segments.
- Development of Targeted Strategies: Focus enables the creation of more precise and effective marketing, sales, and business development strategies tailored to specific manufacturers or product sub-categories.
- Risk Mitigation: By segmenting the market and concentrating on the most attractive segment, uncertainties and risks can be reduced, as efforts are directed towards the area with the highest potential for success.
- Specialization and Reputation: Developing in-depth expertise within the technology category can position the company as an authority or leader in this field.
- Value Proposition Optimization: Possessing extensive knowledge of the specific needs and pain points of technology customers and key manufacturers in this sector allows for the creation of more compelling and optimized value propositions.
- Niche Marketing and Customer Loyalty: Focusing on a specific area allows for more niche marketing campaigns and the cultivation of stronger relationships with targeted customer segments.
- Innovation and Product Development: Closely monitoring trends and identifying gaps among the top 10 manufacturers enables the company to develop more innovative products and services.
- Competitive Advantage: A sustainable competitive advantage can be built by leveraging our strengths in the most profitable area and targeting competitor weaknesses.
- Fall Season Dominance: The Fall season exhibits the highest sales volume, accounting for
36.6%of annual sales. - Consistent Performance Across Other Seasons: Winter (
21.2%), Spring (21.3%), and Summer (20.8%) show relatively consistent and balanced sales contributions. - Seasonal Peak Identification: The data clearly identifies Fall as the peak sales period, indicating strong seasonal demand.
- West Region Leads Sales: The West region generates the highest total sales, indicating it as the primary revenue driver.
- Significant East Region Contribution: The East region is a strong second in total sales, showing robust market presence.
- Lower Southern and Central Sales: The South and Central regions contribute comparatively less to overall sales.
- Regional Disparity: Sales performance varies significantly by region, with a clear concentration in the West and East.
- Significant Product-Level Losses: A concentrated number of products are generating substantial negative profit, with
"Cubify CubeX 3D Printer Double Head Print"leading losses at-$8,880. - Technology & Office Supplies Concentration: Loss-making products are primarily found within Technology (e.g., 3D Printers, Laser Printers) and Office Supplies/Furniture (e.g., various tables, binding systems) categories.
- Urgent Profitability Review: The magnitude of individual product losses necessitates an immediate review of pricing, discounting, and/or cost structures for these specific items.
- Weak Negative Correlation: The R-squared value of
0.048indicates a very weak negative correlation between Discount and Profit, suggesting that only4.8%of the variance in profit can be explained by discount variations. - Scattered Data Points: The scatter plot shows widely dispersed data points, reinforcing the minimal linear relationship between these two variables.
- Limited Predictive Power: Discount alone is a poor predictor of profit outcomes, implying other factors significantly influence profitability.
- Conduct a granular, product-specific root cause analysis for the top loss-making items. This involves deep dives into COGS, supply chain inefficiencies, return rates, and the specific discount application logic.
- Immediately review and revise pricing strategies and discount policies for identified loss-making products. Implement stricter controls on maximum discount percentages. Explore alternative promotional strategies (e.g., bundling with profitable items, value-added services) instead of aggressive price reductions. Consider strategic discontinuation for persistently unprofitable products.
- PostgreSQL: (relational queries)
- Beekeeper Studio: (SQL IDE for query execution and data inspection)
- Google Sheets: (for data cleaning, visual analysis, and dynamic dashboards)
- Tableau: (for interactive data visualization and storytelling)
- Wrote complex SQL queries across multiple tables (
orders,order_details,products,customers). - Applied JOIN, GROUP BY, FILTER, CASE WHEN, and aggregation functions.
- Calculated customer lifetime value (CLV), product-level profits, monthly sales trends, delivery delays.
- Identified:
- Top performing regions, cities, and customers
- Product bundling opportunities
- Customer churn risks (inactive for 180+ days)
- Bulk order patterns (quantity ≥ 100)
- Cleaned raw data (date formatting, missing values, standardization).
- Created pivot tables for profit and sales by region, category, and segment.
- Built dynamic dashboards using data validation dropdowns.
- Implemented calculated fields such as:
- Profit margin per order
- Time-to-ship in days
- Order classification: High / Low / Loss (using IFS logic)
- Built interactive dashboards using joined and cleaned datasets.
- Designed:
- Monthly profit line charts
- Geo maps by shipping region
- Profit vs. sales scatter plots
- Top 10 products by segment and category
- Published dashboards on Tableau Public.
- What is the most profitable product category by region?
- Which customer segment generates the highest lifetime profit?
- What is the average delivery time per region?
- What product bundles generate the most combined profit?
- How many customers are at risk of churn?
This project demonstrates real-world data analytics skills across SQL querying, data cleaning, interactive dashboard creation, and business storytelling. It also showcases my ability to structure analytical work, derive actionable insights, and present findings in a professional format.
- 📄
unicorn_queries.sql– SQL scripts for all 13+ questions - 📊
Google Sheets– View Google Sheets - 📋
executive_summary.pdf– View Key insights and recommendations - 📈 Tableau Dashboard – View on Tableau Public
- 🎥 Video Presentation – View on YouTube
- All group project data has been uploaded to the system by Sevil Kücük: Unicorn-Project-Masterschool
From a marketing standpoint, the analysis reveals several key areas for strategic focus:
- Customer Segmentation for Targeted Campaigns: The Consumer segment is the largest revenue driver (
50.6%), followed by Corporate (30.7%). This clear segmentation allows for highly tailored marketing campaigns. - Seasonal Campaign Optimization: The significant sales peak in the Fall (
36.6%) presents a prime opportunity for concentrated marketing spend and promotional activities. - Geographic Market Penetration: High sales concentration in the Northeast and West Coast indicates strong market acceptance. Marketing efforts should capitalize on these established strongholds, while sparse activity in the Central U.S. and South suggests opportunities for targeted market entry.
- Product Portfolio & Promotion Strategy: The presence of numerous loss-making products within profitable categories highlights a disconnect. The weak correlation between discount and profit (
R²=0.048) suggests that aggressive discounting is not an effective long-term strategy for driving profitable sales.
From a data science perspective, the analysis provides valuable insights into data quality, analytical methods, and future modeling opportunities:
- Data Granularity & Quality: The ability to derive insights from over
9,000rows of sales data confirms a robust underlying dataset. - Effective Exploratory Data Analysis (EDA): The use of pivot tables was effective in revealing customer behavior and sales patterns.
- Identified Data Anomaly (Loss-Making Products): The clear identification of specific loss-making products is a critical outcome that requires deeper root cause analysis.
- Correlation Limitations: The extremely low R-squared (
0.048) for Discount vs. Profit correlation indicates that a simple linear relationship is insufficient. Future modeling should consider multivariate and non-linear analysis.
To further enhance insights, future data collection or integration could focus on:
- Customer Lifetime Value (CLTV) data to understand long-term segment profitability.
- Marketing Spend by Channel/Campaign to correlate marketing efforts with sales and profit outcomes.
- Inventory Levels & Stockout Data to analyze the impact of inventory management on discounting and profitability.