Online Business Reporting with SQL

As an analyst, it is essential to extract the data we want for a large database, and SQL is the tool for this!


The Brazilian E-Commerce dataset from Kaggle has 100k order data from 2016 to 2018 made at multiple marketplaces in Brazil.

There are 10 questions that I am interested to understand more about Olist’s sales performance and their customers. For some questions, I would add some notes and some points to put in mind when writing the query.

  1. Update product category name to the English version.
  2. Top 10 sales product
  3. What are the top 10 products for 2016, 2017, 2018, respectively?
  4. Which day of the week, customers tend to go shopping?
  5. What are the top 3 products for each state?
  6. What is the number of monthly active users for 2017? What is the MoM growth rate? What is the retention rate?
  7. What is the most popular payment method?
  8. How many orders in the different order price range?
  9. Average time of review being answered
  10. Order amount distribution(25%, 50%, 75% and average order amount)

Result

By asking the above questions, I know that:

  • Customers prefer using credit cards as the payment method.
  • Customers tend to shop on weekdays.
  • Health and beauty category is the top sales product category within three years, and first place for 2018 and third place for 2017.
  • On average, it takes two days for a customer’s review to get an answer.

Takeaways

Using SQL to leverage customer data enables us to understand the business and customers better, leading to better performance and driving better strategy. Calculating key metrics and getting report-ready data using SQL and further analyzing data within Business Intelligence tools such as Python or Tableau makes the data analytics process more efficient.

Access Full Project here