πThis Repo serves as a Business case study. Providing Solution , Insights and Recommendation for the growth in their Revenue through sql queries.
πThis SQL solutions done in Google BigQuery
πIf you have any questions, reach out to me on LinkedIn.
π Welcome to the Target Data Analysis project! π
As a data scientist at Target, you've been given the exciting opportunity to analyze 100k orders from 2016 to 2018 made at Target in Brazil. ππ§π·
π The dataset is available in 8 csv files:
- customers.csv π§βπ€βπ§
- geolocation.csv πΊοΈ
- order_items.csv ποΈ
- payments.csv π³
- reviews.csv π
- orders.csv π¦
- products.csv π
- sellers.csv π©βπΌ
π So, what does 'good' look like? We'll import the dataset and perform exploratory analysis steps to check the structure, characteristics, data types, and time period for which the data is given. We'll also look at the cities and states of customers who ordered during the given period. π΅οΈββοΈ
π In-depth Exploration:
- Is there a growing trend on e-commerce in Brazil? π How can we describe the complete scenario? π€ Can we see some seasonality with peaks at specific months? βοΈβοΈ
- What time do Brazilian customers tend to buy? π Do they prefer to shop at dawn, morning, afternoon, or night? π πππ
- Evolution of E-commerce orders in the Brazil region:
- Get month on month orders by states π
- Distribution of customers across the states in Brazil πΊοΈπ₯
- Impact on Economy: Analyze the money movement by e-commerce by looking at order prices, freight and others.
- Get % increase in cost of orders from 2017 to 2018 (include months between Jan to Aug only) - You can use βpayment_valueβ column in payments table π°
- Mean & Sum of price and freight value by customer state π΅π
- Analysis on sales, freight and delivery time ππ
- Calculate days between purchasing, delivering and estimated delivery π
- Find time_to_delivery & diff_estimated_delivery β³
- Group data by state, take mean of freight_value, time_to_delivery, diff_estimated_delivery π
- Sort the data to get the following:
- Top 5 states with highest/lowest average freight value - sort in desc/asc limit 5 ππ
- Top 5 states with highest/lowest average time to delivery πβ°
- Top 5 states where delivery is really fast/ not so fast compared to estimated date πβ‘
- Payment type analysis: π³
- Month over Month count of orders for different payment types π
- Count of orders based on the no. of payment installments π’
Give a βοΈ if you like this project!