ER Diagram :
Query Objective: Identify the top-performing cities in Brazil in terms of total revenue and order volume to help focus business efforts on high-revenue regions.
Key Insights:
São Paulo (SP) leads with the highest revenue, making it a strategic city for marketing and logistics investments.
The top 5 cities contribute significantly to total revenue, indicating that e-commerce activity is concentrated in major urban centers.
Businesses can expand their operations and promotions in high-potential cities like Belo Horizonte and Brasília.
This query analyzes the monthly customer order trend over three years (2016–2018) for delivered orders. It provides a clear understanding of seasonal order patterns and year-over-year growth trends, allowing businesses to identify peak months for customer demand.
Key Observations:
Significant Growth in 2018: Orders in 2018 are consistently higher compared to 2017 and 2016. For example, August 2018 recorded 5,135 delivered orders, more than double the orders in August 2017 (2,652). Steady Growth from April to September. The months from April to September show consistent growth across all years, indicating mid-year as a strong sales period.
Use Case :
Demand Forecasting : Marketing and Sales Teams can predict periods of high demand (April to September) and prepare promotional activities around these months.
Resource Allocation and Logistics Planning : Operations Teams can allocate additional resources, such as delivery staff and support teams, during peak order periods to ensure smooth order fulfillment.
This query Tracks monthly churn rates to identify patterns in customer retention and assess periods with high customer turnover.
Observations :
Volatile churn rates in the early months are due to a low customer base.
November 2017 and September 2018 exhibit unusually high churn rates, indicating possible operational or seasonal challenges.
This query combines data from revenue, review scores, and payment methods to provide a comprehensive view of monthly business performance. It’s useful for understanding the relationship between sales performance and customer satisfaction while identifying payment trends.
Observations:
Consistent Revenue Growth:The data shows that revenue increases steadily across multiple months, with some months like October 2016 showing spikes.
High Review Scores:The average review score remains around 4.4, indicating positive customer feedback across all months.
Payment Method Diversity: Credit card and boleto appear frequently, showing a preference for flexible payment options. Understanding which payment methods drive higher revenue can help optimize payment offerings.
Use Case :
Payment Strategy Optimization : Knowing the preferred payment methods and their contribution to revenue can help businesses adjust payment offerings to cater to customer preferences.
Identify top-rated and least-rated product categories based on customer feedback. This helps inform product strategy, quality improvement, and marketing efforts.
Observations:
CDs, DVDs & Musicals (Avg. Score: 4.64) leads the chart, showing strong customer satisfaction — likely due to low return risk and product expectation alignment.
Diapers & Hygiene (Avg. Score: 3.26) is the lowest, possibly due to high customer expectations around hygiene standards.
Use Case :
Procurement Teams: Reassess vendor quality and packaging for low-rated categories like Diapers and Office Furniture.
Marketing Teams: Highlight high-performing categories like Books and Music in promotions and campaigns.