Olist Data Cleaning and EDA

Today i am going to be writing about the recent project that i just completed.

A few days ago there was a challenge on Olist data cleaning and EDA of which the data was provided and so i took part in it and i am going to go through the steps i took in this challenge.

The data is about order details from Olist store in Brazil.

Olist is a Brazilian e-commerce platform that connects small and medium-sized businesses to customers across Brazil. The platform operates as a marketplace, where merchants can list their products and services and customers can browse and purchase them online. The Olist sales dataset available on Kaggle is a collection of anonymised data about orders placed on the Olist platform between January 2017 and August 2018. It contains a wide range of information about each order, including the order date, product details, payment and shipping information, customer and seller IDs, and customer reviews. The dataset also includes information about the sellers who list their products on Olist, as well as data on customer behavior and demographics. The dataset is designed to help analysts and researchers better understand the e-commerce landscape in Brazil and identify opportunities for growth and optimisation.

After downloading the data which was a total of 9 files, i loaded them into a DBMS.

PHASE 1: DATA CLEANING

After taking a look at the dataset i could notice that:

1. Data type of some columns needs to be changed.
2. There were NULL values and needs to be replaced.
3. Some column names needs to be changed.

CHANGE DATA TYPE OF COLUMNS

Some columns had the wrong data type so it had to be changed.

--order_item table
ALTER TABLE olist_order_items ALTER COLUMN shipping_limit_date DATE;

ALTER TABLE olist_order_items ALTER COLUMN price MONEY;

ALTER TABLE olist_order_items ALTER COLUMN freight_value MONEY;

--order_payment table
ALTER TABLE olist_order_payments ALTER COLUMN payment_installments INT;

ALTER TABLE olist_order_payments ALTER COLUMN payment_sequential INT;

ALTER TABLE olist_order_items ALTER COLUMN payment_value MONEY;

--change the data type for the orders table
ALTER TABLE olist_orders ALTER COLUMN order_purchase_timestamp DATE;
ALTER TABLE olist_orders ALTER COLUMN order_approved_at DATE;
ALTER TABLE olist_orders ALTER COLUMN order_delivered_carrier_date DATE;
ALTER TABLE olist_orders ALTER COLUMN order_delivered_customer_date DATE;
ALTER TABLE olist_orders ALTER COLUMN order_estimated_delivery_date DATE;

--order_reviews table
ALTER TABLE olist_order_reviews ALTER COLUMN review_answer_timestamp DATE;
ALTER TABLE olist_order_reviews ALTER COLUMN review_score INT;

--order_payment table
ALTER TABLE olist_order_payments ALTER COLUMN payment_sequential INT;

REPLACE NULL VALUES

Some columns contained NULL values so i replaced them with ‘N/A’ and ‘0’.

--products table
UPDATE olist_products
SET product_category_name = 'N/A'
WHERE product_category_name IS NULL;

UPDATE olist_products
SET product_name_lenght = '0'
WHERE product_name_lenght IS NULL;

UPDATE olist_products
SET product_description_lenght = '0'
WHERE product_description_lenght IS NULL;

UPDATE olist_products
SET product_photos_qty = '0'
WHERE product_photos_qty IS NULL;

--order_reviews table
UPDATE olist_order_reviews
SET review_comment_message = 'N/A'
WHERE review_comment_message IS NULL;

CHANGE COLUMN NAMES

In the product_category_name_translation table the column names were used as the first row, so i changed the column names and deleted the first row.

--product_category_name_translation table
EXEC sp_RENAME 'product_category_name_translation.column1', 'product_category_name', 'COLUMN';
EXEC sp_RENAME 'product_category_name_translation.column2', 'product_category_name_english', 'COLUMN';
DELETE FROM dbo.product_category_name_translation
WHERE product_category_name LIKE 'product_category_name';

PHASE 2: EDA

This stage is about exploring the dataset and answering some business questions that came alongside with the data.

Here are the list of business questions and their answers together with visuals.

1. What is the total revenue generated by Olist, and how has it changed over time?

SELECT YEAR(o.order_purchase_timestamp)  AS Purchase_Timestamp, SUM(payment_value) AS Total_Revenue
FROM dbo.olist_orders o
JOIN dbo.olist_order_payments op
ON op.order_id = o.order_id
GROUP BY YEAR(o.order_purchase_timestamp)
ORDER BY SUM(payment_value) DESC

Revenue vs Time

In the year 2018 and 2017 Olist generated the highest revenue.

2. How many orders were placed on Olist, and how does this vary by month or season?

--By month
SELECT MONTH(order_purchase_timestamp) AS Monthly, COUNT(*) AS Total_Number_of_order
FROM dbo.olist_orders
GROUP BY MONTH(order_purchase_timestamp)
ORDER BY COUNT(*) DESC

--By Season
SELECT CASE
  WHEN MONTH(order_purchase_timestamp) IN (12, 1, 2) THEN 'Winter'
  WHEN MONTH(order_purchase_timestamp) IN (3, 4, 5) THEN 'Spring'
  WHEN MONTH(order_purchase_timestamp) IN (6, 7, 8) THEN 'Summer'
  ELSE 'Fall'
  END AS Season,
  COUNT(*) AS Monthly
FROM dbo.olist_orders
GROUP BY MONTH(order_purchase_timestamp)
ORDER BY COUNT(*) DESC

May, July and August is when Olist usually have high number of orders also during Summer and Spring season.

3. What are the most popular product categories on Olist, and how do their sales volumes compare to each other?

SELECT  t.product_category_name_english AS Product_Name, COUNT(op.payment_value) AS Sales_Volume
FROM dbo.product_category_name_translation t
JOIN dbo.olist_products p
ON t.product_category_name = p.product_category_name
JOIN dbo.olist_order_items oi
ON oi.product_id = p.product_id
JOIN dbo.olist_order_payments op
ON op.order_id = oi.order_id
GROUP BY t.product_category_name_english
ORDER BY COUNT(op.payment_value) DESC

The 3 most popular products on Olist are Bed bath table, Health beauty and Sport leisure.

4. What is the average order value (AOV) on Olist, and how does this vary by product category or payment method?

--By payment method
SELECT  op.payment_type AS Payment_Type, ROUND(SUM(op.payment_value) / COUNT(oi.product_id),2) AS AOV
FROM dbo.product_category_name_translation t
JOIN dbo.olist_products p
ON t.product_category_name = p.product_category_name
JOIN dbo.olist_order_items oi
ON oi.product_id = p.product_id
JOIN dbo.olist_order_payments op
ON op.order_id = oi.order_id
GROUP BY op.payment_type
ORDER BY AOV DESC

--By product category
SELECT  t.product_category_name_english AS Product_name, ROUND(SUM(op.payment_value) / COUNT(oi.product_id),2) AS AOV
FROM dbo.product_category_name_translation t
JOIN dbo.olist_products p
ON t.product_category_name = p.product_category_name
JOIN dbo.olist_order_items oi
ON oi.product_id = p.product_id
JOIN dbo.olist_order_payments op
ON op.order_id = oi.order_id
GROUP BY t.product_category_name_english

Payment vs AOV

Credit card, boleto and debit card had the high AOV, and by product Agro industry and commerce had the highest AOV which is the average dollar amount spent each time a customer places an order on Olist.

Product vs AOV

Payment vs AOV

5. How many sellers are active on Olist, and how does this number change over time?

SELECT COUNT(DISTINCT seller_id) AS Seller, COUNT(product_id) AS Product_sold, order_purchase_timestamp AS Time
FROM dbo.olist_order_items oi
JOIN dbo.olist_orders o
ON o.order_id = oi.order_id
GROUP BY order_purchase_timestamp
HAVING  order_purchase_timestamp >= DATEADD(MONTH, 3, '2016-12-23') AND COUNT(product_id) > 1
ORDER BY COUNT(product_id) DESC

We can say a seller is active only if the seller has made more than 1 sale in a period of 3 months.

As the number of active sellers increase so does the sale of product increase.

6. What is the distribution of seller ratings on Olist, and how does this impact sales performance?

7. How many customers have made repeat purchases on Olist, and what percentage of total sales do they account for?

SELECT  
 c.customer_unique_id AS Unique_customer,
 COUNT(oi.order_id) AS Repeat_purchase,
 ROUND(COUNT(oi.order_id)/SUM(op.payment_value)*100,2) AS Percentage_total_sales
FROM dbo.olist_customers c
JOIN dbo.olist_orders o
ON o.customer_id = c.customer_id
JOIN dbo.olist_order_payments op
ON op.order_id = o.order_id
JOIN dbo.olist_order_items oi
ON oi.order_id = op.order_id
GROUP BY c.customer_unique_id
HAVING COUNT(oi.order_id) > 1
ORDER BY Repeat_purchase DESC

8. What is the average customer rating for products sold on Olist, and how does this impact sales performance?

SELECT t.product_category_name_english AS Product_name, AVG(review_score) AS Average_cutomer_rating, COUNT(op.payment_sequential) AS Sales_volume
FROM dbo.olist_order_reviews r
JOIN dbo.olist_order_payments op
ON op.order_id = r.order_id
JOIN dbo.olist_order_items oi
ON oi.order_id = op.order_id
JOIN dbo.olist_products p
ON p.product_id = oi.product_id
JOIN dbo.product_category_name_translation t
ON t.product_category_name = p.product_category_name
GROUP BY t.product_category_name_english
ORDER BY COUNT(op.payment_sequential) DESC

Products with 3 and 4 rating score have high sales which means that the products are performing well.

9. What is the average order cancellation rate on Olist, and how does this impact seller performance?

SELECT
 YEAR(order_purchase_timestamp),
 COUNT(oi.order_id) AS Total_orders,
 SUM(CASE WHEN order_status = 'canceled' THEN 1 END) AS Cancelled_orders,
 CAST(SUM(CASE WHEN order_status = 'canceled' THEN 1 END) AS DECIMAL(10,0)) / COUNT(*) AS Average_cancellation_rate
FROM dbo.olist_orders o
JOIN dbo.olist_order_items oi
ON oi.order_id = o.order_id
GROUP BY YEAR(order_purchase_timestamp)
ORDER BY YEAR(order_purchase_timestamp)

In 2016 when orders was low the cancellation rate was high but as of 2017 and 2018 cancellation rate dropped and total orders increased.

10. What are the top-selling products on Olist, and how have their sales trends changed over time?

SELECT t.product_category_name_english AS Product_name, COUNT(op.payment_sequential) AS Top_selling, YEAR(o.order_purchase_timestamp) AS Year
FROM dbo.product_category_name_translation t
JOIN dbo.olist_products p
ON p.product_category_name = t.product_category_name
JOIN dbo.olist_order_items oi
ON oi.product_id = p.product_id
JOIN dbo.olist_order_payments op
ON op.order_id = oi.order_id
JOIN dbo.olist_orders o
ON o.order_id = op.order_id
GROUP BY t.product_category_name_english, YEAR(o.order_purchase_timestamp)
ORDER BY COUNT(op.payment_sequential) DESC

The top selling products on Olist are bed bath table, health beauty, sports leisure, funiture decor, computer accessories, housewares and watches gift.

11. Which payment methods are most commonly used by Olist customers, and how does this vary by product category or geographic region?

SELECT DISTINCT t.product_category_name_english AS Product_name, op.payment_type AS Payment_type, COUNT(op.payment_type) AS Count_payment_type
FROM dbo.olist_order_payments op
JOIN dbo.olist_order_items oi
ON oi.order_id = op.order_id
JOIN dbo.olist_products p
ON p.product_id = oi.product_id
JOIN dbo.product_category_name_translation t
ON t.product_category_name = p.product_category_name
GROUP BY t.product_category_name_english, op.payment_type
ORDER BY COUNT(op.payment_type) DESC

The most uses payment type on Olist is credit card followed by boleto.

12. How do customer reviews and ratings affect sales and product performance on Olist?

SELECT  
 r.review_score AS Review_score,
 SUM(op.payment_value) AS Total_sales, 
 COUNT(op.payment_sequential) AS Unit_of_product_sold
FROM dbo.olist_order_reviews r
JOIN dbo.olist_order_items oi
ON oi.order_id = r.order_id
JOIN dbo.olist_order_payments op
ON op.order_id = oi.order_id
JOIN dbo.olist_products p
ON p.product_id = oi.product_id
JOIN dbo.product_category_name_translation t
ON t.product_category_name = p.product_category_name
GROUP BY r.review_score
ORDER BY COUNT(op.payment_sequential) DESC

Products that have the 4 and 5 review score has the highest sales. Which means that the products on Olist are performing good on sales.

13. Which product categories have the highest profit margins on Olist, and how can the company increase profitability across different categories?

SELECT DISTINCT t.product_category_name_english,
  ROUND(SUM(op.payment_value) - SUM(oi.price) /  SUM(op.payment_value),2) AS Profit_margin
FROM dbo.olist_order_payments op
JOIN dbo.olist_order_items oi
ON oi.order_id = op.order_id
JOIN dbo.olist_products p
ON oi.product_id = p.product_id
JOIN dbo.product_category_name_translation t
ON t.product_category_name = p.product_category_name
GROUP BY t.product_category_name_english
ORDER BY Profit_margin DESC

Here the top products with high profit margin are bed bath table, health beauty, computer accessories, furniture decor, watches gift, sports leisure and housewares.

14. How does Olist’s marketing spend and channel mix impact sales and customer acquisition costs, and how can the company optimize its marketing strategy to increase ROI?

15. Geolocation having high customer density. Calculate customer retention rate according to geolocations.

WITH repeat_customer AS (
  SELECT customer_unique_id, customer_state
  FROM dbo.olist_orders o
  JOIN dbo.olist_customers c
  ON o.customer_id = c.customer_id
  GROUP BY c.customer_unique_id, customer_state
  HAVING MIN(o.order_purchase_timestamp) <= DATEADD(MONTH, -6, '2018-10-17')
),
 retained_customer AS (
  SELECT customer_unique_id, customer_state
  FROM dbo.olist_orders o
  JOIN dbo.olist_customers c
  ON o.customer_id = c.customer_id
  WHERE o.order_purchase_timestamp BETWEEN DATEADD(MONTH, -6, '2018-10-17') AND '2018-10-17'
  GROUP BY c.customer_unique_id, customer_state
  HAVING COUNT(o.order_purchase_timestamp) > 1
)
SELECT
 retained_customer.customer_state,
  COUNT( repeat_customer.customer_unique_id) AS Repeated_customer,
  COUNT( retained_customer.customer_unique_id) AS Retained_customer,
 ROUND(CAST(COUNT( repeat_customer.customer_unique_id) AS FLOAT) / NULLIF(COUNT( retained_customer.customer_unique_id ) ,0) * 100, 2) AS Retention_rate
FROM retained_customer
FULL JOIN repeat_customer
ON retained_customer.customer_unique_id = repeat_customer.customer_unique_id AND retained_customer.customer_state = repeat_customer.customer_state
WHERE retained_customer.customer_state IS NOT NULL
GROUP BY retained_customer.customer_state
ORDER BY Retention_rate DESC

8 geolocations have high retention rate.

Thank you for reading.