Analyzing online retailer orders for it’s Sales Strategy using SQL and Tableau
This is a follow up project using the same dataset from a Brazilian online retail store Olist with the aim of answering business questions. The earlier project was done using python find it here.
It is recommended that among the skillset a data analyst/scientist need to have SQL is top on the list. When it comes to presenting your findings a good data visualization tool to use would be Tableau ,Power BI or Qlik Sense.
I decided to add a few questions to the earlier ones.We shall be answering the following business questions.
- Total number of orders purchased and delivered by year
- Yearly Number of customers and customers growth rate.
- What are the top 10 products for 2016, 2017, 2018, respectively?
- Top selling product in revenue
- Which day of the week, customers tend to go shopping?
To remind us the table from which we are obtaining our dataset see the Entity Relationship Diagram below
- Total number of orders purchased and delivered by year
Looking at the orders table we see that the orders captured were from 2016–2018. 2018 recorded the highest number of orders at 52783 with 2016 being the least at 267 of the orders that had their status as “delivered”.
2. Yearly Number of customers and customers growth rate.
When we look at the customers growth rate which is when the e-commerce store got purchase orders from new customers we find out that 2017 recorded the highest rate compared to 2018 .There were no prior sales before 2016 hence forming our starting point.
3. What are the top 10 products for 2016, 2017, 2018, respectively?
Looking at the orders placed from 2016–2018 , the Baby category was most ordered in 2018 and doesn’t come up in the previous years. This is where now as data analyst you dig deep to find out more (growth opportunities ).Comparing the purchase orders of watches_gifts in 2016 ,2017 and 2018 we find that they were on an upward trend ,same as sports_leisure purchase in 2016 vs 2018.
4. Top selling product in revenue
When it comes to finding the most selling product categories from the e-commerce store worth noting is 6 out of the top 10 product categories were sold in 2018 while the rest in 2017. Health_beauty category had the most revenue at $850,599.18 followed by watches_gifts at $763563.56 , bed_bath_table at $616017.01,sports_leisure at $593239.98 and computer_accessories at $544143.96 having revenues above $500k.
5.Which day of the week, customers tend to go shopping?
Most of the orders purchased were placed in 2017 on Friday’s followed by Saturday’s then Monday’s, Sunday’s and Tuesday’s respectively all in the month of November.
When it comes to the year 2018 most of the orders purchased were made on Monday’s in the month of May followed by Monday’s in the month of August.
Summary:
- 2018 recorded the most number of orders placed from the website hence had high revenue compared to 2016 and 2017.
- There are 5 common product category purchases across the years which are computers_accessories, console_games,sport_leisure ,small_appliances and watches_gifts.
- Most orders in 2017 were purchased during weekends, while in 2018 most were purchased on weekdays.
Comments ,suggestions are very much welcome.