Data Analyst
Capstone Project
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.
Tools
Python
Google Colab
Looker Studio
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?
Project Goals
As a data analyst, this project has the following objectives:
Methodology
Data Cleaning
Data Collection & Understanding
Data Analysis &
Visualization
Insight &
Recommendation
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
Data Collection & Understanding
Brazilian E-Commerce Public Dataset
Customers
Sellers
Geolocation
Data Collection & Understanding
Brazilian E-Commerce Public Dataset
Product Category
Products
Orders
Data Collection & Understanding
Brazilian E-Commerce Public Dataset
Order Items
Order Reviews
Order Payments
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:
Data Cleaning
Handling Missing Value
In the Orders Dataset, there are some missing values with the following details:
Handling: Imputation of Missing Value by using the median time from order_purchase_timestamp to order_approved_at.
Handling: Imputation of Missing Value by using median time from order_approved_at to order_delivered_carrier_date.
Handling: Imputation of Missing Value by using the median time from order_delivered_carrier_date to order_delivered_customer_date.
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
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
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:
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.
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%.
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.
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.
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.
Orders by Order Status shows that most orders have been “delivered” which is 97% or 96,478 orders.
Exploratory Data Analysis
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).
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.
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.
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
Data Vizualization (Dashboard)
Data Vizualization (Dashboard)
Data Vizualization (Dashboard)
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:
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.
ATTACHMENT
PT. CERDAS DIGITAL NUSANTARA
Jl. Makaliwe Raya No. 36. Grogol,
Grogol Petamburan. Jakarta Barat 11450.
Terima kasih
Thank you
谢谢
Xièxiè
ありがとうございまし
Arigatōgozaimashita
캄사함니다
Kamsahamnida