1 of 32

Data Analyst

Capstone Project

​

2 of 32

Identity

Rakha Maulana

Data Analyst - Group A

UPN “Veteran” Jawa Timur - Information System

Hello! I'm Rakha, SIB Batch 7 - Cakap participant, currently interested in becoming a data analyst.

3 of 32

Tools

Python

Google Colab

Looker Studio

4 of 32

Project Background

Business Overview

Business Situation

The total revenue from Brazilian e-commerce reached R$ 16,008,872.12, with the Bed, Bath, and Table category emerging as the best-selling product category. A total of 99,441 orders were placed, with the highest sales recorded in the city of São Paulo. The month with the highest total orders was November 2017. Additionally, the majority of reviews were positive, with 58.42% of customers giving a score of 5, and 89.2% of deliveries were made on time.

Problem Statement

How to increase the number of new customers every month and improve customer satisfaction levels?

5 of 32

Project Goals

As a data analyst, this project has the following objectives:

  • Analyze sales, delivery and customer review patterns to identify key factors impacting Olist's performance.
  • Provide data-driven insights that support operational and customer experience improvement strategies.
  • Develop actionable recommendations to optimize business performance.

6 of 32

Methodology

Data Cleaning

Data Collection & Understanding

Data Analysis &

Visualization

Insight &

Recommendation

7 of 32

Data Collection & Understanding

Brazilian E-Commerce Public Dataset

Data Schema

This dataset was generously provided by Olist, the largest department store in Brazilian marketplaces. Olist connects small businesses from all over Brazil to channels without hassle and with a single contract. Those merchants are able to sell their products through the Olist Store and ship them directly to the customers using Olist logistics partners.

Source: Kaggle

8 of 32

Data Collection & Understanding

Brazilian E-Commerce Public Dataset

Customers

Sellers

Geolocation

9 of 32

Data Collection & Understanding

Brazilian E-Commerce Public Dataset

Product Category

Products

Orders

10 of 32

Data Collection & Understanding

Brazilian E-Commerce Public Dataset

Order Items

Order Reviews

Order Payments

11 of 32

Data Cleaning

Handling Datatype inconsistent

In the Orders, Order_item and Order_review datasets, there are columns that contain dates but the data type is “object”.

​

Handling: Convert the data type to “Datetime64”

​

Column whose data type is converted:

  1. order_purchase_timestamp (Orders)
  2. order_approved_at (Orders)
  3. order_delivered_carrierdate (Orders)
  4. order_delivered_customer_date (Orders)
  5. order_estimated_delivery_date (Orders)
  6. shipping_limit_date (Order_item)
  7. review_creation_date (Order_review)
  8. review_answer_timestamp (Order_review)

12 of 32

Data Cleaning

Handling Missing Value

In the Orders Dataset, there are some missing values with the following details:

​

  1. 14 Missing Value in order_approved_at column with Order Status “Delivered”

Handling: Imputation of Missing Value by using the median time from order_purchase_timestamp to order_approved_at.

​

  • 2 Missing Value in order_delivered_carrier_date column with Order Status “Delivered”

Handling: Imputation of Missing Value by using median time from order_approved_at to order_delivered_carrier_date.

​

  • 8 Missing Value in order_delivered_customer_date column with Order Status “Delivered”

Handling: Imputation of Missing Value by using the median time from order_delivered_carrier_date to order_delivered_customer_date.

​

13 of 32

Data Cleaning

Handling Typo

In the Geolocation dataset, there are several values in geolocation_city using accent characters.

​

Handling: Normalize accent characters with library unicodedata

​

14 of 32

Data Cleaning

Handling Duplicate Data

In the Geolocation Dataset, there are 279,668 duplicate rows where a zip_code_prefix can appear more than once.

​

Handling: Aggregation based on the geolocation_zip_code_prefix column, by taking the average value of geolocation_lat and geolocation_lng.

​

Number of rows after aggregation: 19,015 rows without duplication

15 of 32

Data Cleaning

Row Structure Normalization

In the Order Review Dataset, there are several rows that do not match the expected structure (7 column per row).

​

Handling:

  1. Data Structure Validation
  2. Mismatched Line Joins
  3. Data Type Validation
  4. Dataset Normalization

​

16 of 32

Exploratory Data Analysis

Monthly Sales Trends show significant variations in performance, with November 2017 achieving the highest sales of R$1,194,882.80, likely driven by seasonal promotions or increased consumer demand, while December 2016 recorded the lowest sales of only R$19.62, which may have been due to Olist Ecommerce being new.

17 of 32

Exploratory Data Analysis

Customer Growth and Retention shows that Olist Ecommerce consistently adds new customers every month although the numbers vary. There is a decrease in the number of new customers in some periods and the highest decrease in new customers was in September 2018 with a percentage decrease reaching 99.75%.

18 of 32

Exploratory Data Analysis

Sales Distribution by city shows that the city with the highest sales contribution is Sau Paulo with R$2,514,906.60 and the city with the lowest sales contribution is Palotina with only R$9.99.

19 of 32

Exploratory Data Analysis

Best-selling product Category shows the highest selling category is bed_bath_table with a percentage of 10.1% or 11.115 products sold and the lowest selling category is security_and_services with a percentage of 0.0018% or only 2 products sold.

20 of 32

Exploratory Data Analysis

Sales Distribution by city shows that the highest number of sellers is Sau Paulo with a total of 694 sellers.

This corresponds to the city with the largest sales, Sau Paulo, with sales of R$2,514,906.60.

21 of 32

Orders by Order Status shows that most orders have been “delivered” which is 97% or 96,478 orders.

​

​

Exploratory Data Analysis

22 of 32

Exploratory Data Analysis

Orders by Payment Type show that the most preferred payment method is credit card with 75.2% or 76,505 orders. Followed by other payment methods Boleto (19.5% / 19,784 orders), voucher (3.8% / 3,886 orders), debit_card (1.5% / 1,528 orders) and not_defined (3 orders).

​

​

23 of 32

Exploratory Data Analysis

The visualization above shows the distribution of average delivery times by product category. The product category with the longest average delivery time is office_furniture with 19.93 days and the product category with the fastest average delivery time is art_and_craftmanship with 4.7 days.

24 of 32

Exploratory Data Analysis

The visualization above shows the distribution of Delivery Status (On-Time vs. Late). where 89.2% or 88.657 orders were delivered with On-time while the rest (10.8% or 10.784 orders) were delivered Late.

25 of 32

Exploratory Data Analysis

The visualization above illustrates the distribution of reviews by score. A significant majority, 58.42% (49,576 reviews), awarded a score of 5, indicating high satisfaction. This is followed by 19.58% (16,611 reviews) giving a score of 4, suggesting moderate satisfaction.

In contrast, 8.19% (6,948 reviews) rated the experience with a score of 3, while 3.08% (2,613 reviews) provided a score of 2, reflecting lower satisfaction. Finally, 10.9% (9,243 reviews) assigned a score of 1, signaling dissatisfaction

26 of 32

Data Vizualization (Dashboard)

27 of 32

Data Vizualization (Dashboard)

28 of 32

Data Vizualization (Dashboard)

29 of 32

Recommendation

With 10.8% of orders delivered late (10,784 orders), this represents a significant proportion that could negatively affect customer satisfaction and retention. Late deliveries often lead to poor reviews dan increased customer complaints

Recommendation:

  1. Evaluate logistics partners' performance and renegotiate terms or contracts with underperformers.
  2. Identify bottlenecks in the delivery pipeline, particularly for product categories with the longest delivery times (e.g., office furniture with an average of 19.93 days).

30 of 32

Recommendation

The sharpest decline in the number of new customers occurred in September 2018 (99.75%).

Recommendation:

Review the effectiveness of marketing campaigns in the previous period. Check if there are any changes in strategy that have a negative impact or if the products offered are relevant to market needs.

31 of 32

ATTACHMENT

32 of 32

PT. CERDAS DIGITAL NUSANTARA

Jl. Makaliwe Raya No. 36. Grogol,

Grogol Petamburan. Jakarta Barat 11450.

Terima kasih

Thank you

谢谢

Xièxiè

ありがとうございまし

Arigatōgozaimashita

캄사함니다

Kamsahamnida