This Project involved cleaning and preprocessing the raw dataset in Excel as well as EDA and visualization in Tableau. The dataset contained customer orders, region details, and marketing information. In cleaning process, the goal was to identify and address data quality issues, improve consistency, and document any limitations. Analysis process involves sales metrics, marketing channel metrics and regional metrics.

Index

• Data Cleaning
• EDA
• Dashboard
• Recommendation

1. Data Cleaning

Cleaning Objectives

• Fix formatting issues
• Handle missing or inconsistent data
• Standardize text and numerical values
• Ensure proper data types for analysis

Dataset

1. Orders Table: ~20,000 rows of customer orders and transation details
2. Region Table: Country and region codes

Issue Log and Resolutions

Files

• Orders, Regions - Original Dataset(anonymized)
• Orders_Cleaned, Regions_Cleaned - Cleaned version with corrections
• Issue Log - Tabular format of th issues above
• All can be found in the excel workbook here!

Notes

• Certain unsolved issues were left for business teams to validate due to external dependencies.
• The cleanes dataset can now be confidently used for further analysis or dashboard reporting

Contact

For any questions or suggestions, feel free to open an issue or contact me. khc13258@gmail.com

2. EDA

Analysis Objectives

• How did total revenue dollars across all products perform during 2019-2022? Conduct some intial analysis to help product, marketing, and finance nanagers understand high-level trends.
• Look at total usd_price across months, products, and averages. Identify minimums, and ant outliers
• Can also slice by region and other demographics like marketing channel, or account creation method

KPI

• Total Sales
• Total Orders
• AOV

Data overview in Excel

*1. USD_Price/Month *

• Total sales across 2019-2022 is -6.1<, ranging in monthly sales from $80K to $500K (Finance Team)

*2. USD_price/Product *

• Top performing products is gaming monitor(almost $2M total sales), worst performing is gaming headset ($800). Might be missing data, would need to check with team (Finance team, Product team)

3. USD_price / Product Category

• Worst performing category is headsets, which are reponsible for less than 2% of sales (Product team)

*4. USD_Price / Month *

• December 2020 saw a large spike in sales - seems like fall and winter months before the new year perform best. Maybe related to holiday season or promotion at that time. (Marketing team)

*5. USD_price / Product *

• All products had significant spike in sales 2020 onwards, following the general trend in total sales. This indicated a macro or company-wide impact(mostly likely due to COVID) (Finance team)

*6. USD_price / Product *

• Almost all products exhibited a splike in December 2020 - look into promotions or campaigns that happened at this time (Marketing team)

EDA in Tableau

Overall USD

• Sales more than doubled in early 2020, with all-time high in Sep and Dec 2020. Sales dropped significantly in Feb 2021, nearing pre-COVID levels.

Products USD

• 27in 4K Gaming monitor, Nintendo Switch, and Sony Playstation5 bundle are main drivers of the dip - all three exhibit the same plateauing behavior in 2020 and 2021.

Marketing USD

Direct is the main driver of sales - all other channels pale in comparison

Region USD

• All regions exhibit similar dips in 2020 and 2021, indicating global / macro trend

Product Marketing

• Whole all three top products(27in 4K Gaming Monitor, Nintendo Switch, Sony Playstation5 Bundle) exhibited dips in early 2020 and 2021, Sony playstation5 bundle had major dip in direct traffic in the beginning of 2021 compared to other products and marketing channels. Since then, consistent decline for that products's direct channel sales.

Product Region

• For Sony Playstation5 bundle, the drop is mostly contained to the NA region and direct traffic - may indicate a shift in trends or competitors there.

3. Dashboard

4. Recommendation

*1. Finance / Analytics *

• (Directional) Investigate the main drivers of the dip in sales in the years following COVID - which products and regions?

*2. Marketing *

• (Actionable) Push more promotions and marketing strategies towards North American sales for gaming monitor and playstation, which have more transaction than other top2 products.
• (Actionable) Double down on Winter sales by pushing promotions for Top3 products in October / November
• (Actionable) Focus marketing strategy on social media channel, which shows potential uptick, in order to shift away from reliance on Direct traffic.

*3. Product *

• (Actionable) Remove headsets from the inventory as these make up less than 2% of overall revenue.

*4. Data *

• (Directional) Double check attribution to marketing channel is correct - direct semms oversized.

5. General

• (Contextual) Awareness than when people spend more time at home, there's greater willingness to spend on gaming products.

링크텍스트

1. Date Schema

I have built multiple tables including customers, orders, order items, sellers, products, payments, and reviews of the Olist dataset.

This dashboard helps identify logistics bottlenecks and understand delivery performance across different regions and routes.

2. Sales Dashboard Overview

The Sales dashboard provides an overview of key business metrics and sales performance within the Olist e-commerce platform.

At the top of the dashboard, several key performance indicators (KPIs) summarize the overall business performance

The Sales KPI Line chart shows monthly sales performance trends, allowing users to track how sales volume changes over time.

On the right side, the Category Sales chart highlights the top-performing product categories based on order volume. Categories such as cama_mesa_banho, beleza_saude, and esporte_lazer generate the highest number of sales.

The Brazil map visualization displays the average payment amount per customer by state, providing insights into regional purchasing behavior.

Finally, the Payment Type Breakdown chart shows the distribution of payment methods used by customers. Credit cards account for the majority of transactions, followed by boleto payments.

Overall, this dashboard enables users to quickly understand sales performance, category trends, regional purchasing patterns, and payment behaviors within the Olist platform.

3. Logistics Dashboard Overview

The Logistics dashboard focuses on analyzing delivery performance and identifying potential logistics bottlenecks across the Olist platform.

At the top of the dashboard, key indicators summarize the overall delivery performance

The Average Late Rate by Categories chart highlights product categories with the highest delivery delay rates. Categories such as casa_conforto, moveis_colchao, and audio show relatively higher delay rates compared to others.

The Seller-Customer Delivery Days chart analyzes the average delivery time by seller-to-customer routes. Routes such as SP → RJ and SP → RS show relatively longer delivery times, suggesting potential logistics challenges along these routes.

The Average Delivery Days Map provides a geographic overview of delivery performance across Brazilian states, helping identify regions with slower delivery times.

Finally, the Orders Delayed Due to Logistics by State chart shows the number of orders delayed due to logistics issues in each state. States such as SP and RJ account for the largest share of logistics-related delivery delays.

Overall, this dashboard helps identify delivery bottlenecks, high-delay product categories, and regional logistics performance within the Olist platform.

4. Review Dashboard Overview

The Review dashboard analyzes customer feedback and satisfaction across the Olist platform.

At the top of the dashboard, several key indicators summarize the overall review statistic.

The Total Orders by Reviews chart shows the distribution of review scores over time. Most orders receive high ratings (4–5 stars), indicating generally positive customer satisfaction across the platform.

The Top 10 Highest Rated Categories chart highlights product categories with the highest average review scores. Categories such as cds_dvds_musicais and fashion_roupa_infanto receive consistently high customer ratings.

In contrast, the Top 10 Lowest Rated Categories chart identifies product categories with relatively lower review scores. These categories may require further investigation to understand potential issues related to product quality, delivery, or customer expectations.

Overall, this dashboard helps identify customer satisfaction trends and category-level review performance within the Olist marketplace.

Further EDA was conducted based on the previous analysis of the Olist dataset.

1. Payment Analysis

Payment types distribution

Credit cards are the dominant payment method, accounting for about 74% of all transactions.

Payment Intallments / Count

This chart shows the distribution of payment installments. We can see that most transactions are made with a single payment, indicating that many customers prefer paying in full rather than using installments. A Smaller portion of customers use 2 to 4 installments, while long installment plans are relatively rare.

Most orders (about 97–98%) are paid with a single payment. Orders with multiple payments are very rare, indicating customers usually pay the full amount at once.

2. Customer Review Analysis

Wordcloud

I created an average review score data mart by categories and its cnt on MySQl and then I've loaded it on Python.

[MySQL]
use olistdata;

# Categories Review Datamart
SELECT
  COALESCE(t.product_category_name_english, p.product_category_name) AS category,
  COUNT(*) AS review_cnt,
  ROUND(AVG(r.review_score), 3) AS avg_review_score
FROM order_reviews r
JOIN orders_items oi
  ON r.order_id = oi.order_id
JOIN products p
  ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
  ON p.product_category_name = t.product_category_name
WHERE r.review_score IS NOT NULL
GROUP BY category
ORDER BY avg_review_score DESC;

Categories like CDs/DVDs, children’s clothes, and books have the highest review scores (around 4.4–4.6), indicating strong customer satisfaction. In contrast, categories such as office furniture and electronics-related items have lower score (around 3.2~3.5), indicating more customer complaints or delivery/product issues.

In-depth Analysis of Three Problematic Sellers

Among the bottom five categories by average review score, office_furniture has the largest sample size (1,687). Therefore, I drilled down into this category(office_furniture) to identify specific sellers with consistently low review scores.

As a result, I identified three problematic sellers in the office_furniture category with the highest number of review scores of 2 or below.

These sellers show consistently high proportions of low ratings, suggesting potential issues with product quality, shipping, or customer service.

Next, to analyze where the orders from these sellers were distributed, I filtered the dataset using the problematic seller_ids. The results show that about 60% of the orders from these sellers occurred in SP and RJ, where the sellers themselves are located.

Is delivery delay actually associated with review scores of 2 or below?

[Python Code]

problem_orders['is_delayed'] = (
    problem_orders['order_delivered_customer_date'] >
    problem_orders['order_estimated_delivery_date']
)

# 지연 vs 비지연 2점 이하 비율 비교 
problem_orders['is_low'] = problem_orders['review_score'] <= 2

delay_compare = (
    problem_orders
    .groupby('is_delayed')['is_low']
    .mean()
    .reset_index()
)

The direct cause of the low average review scores of these sellers is delivery delays. However, the earlier hypothesis—that delivery delays were mainly caused by logistics infrastructure limitations—is not fully supported. Low ratings are not concentrated only in remote regions but also appear in SP and RJ, where the sellers themselves are located. This suggests that the issue may stem not only from logistics infrastructure but also from seller-related problems, indicating that both seller factors and infrastructure limitations contribute to the delay.

[Next Step] To more clearly determine whether the issue stems from seller-related problems or logistics infrastructure limitations, we can examine the difference between shipping_limit_date and order_delivered_carrier_date, which allows us to check whether the delay occurred at the seller stage. If shipping_limit_date is later, the delay is more likely related to delivery or logistics issues. If order_delivered_carrier_date is later, the delay likely occurred at the seller’s fulfillment stage.

[Python Code]

problem_orders['order_delivered_carrier_date'] = pd.to_datetime(problem_orders['order_delivered_carrier_date'])
problem_orders['shipping_limit_date'] = pd.to_datetime(problem_orders['shipping_limit_date'])

problem_orders['seller_late'] = (
    problem_orders['order_delivered_carrier_date'] >
    problem_orders['shipping_limit_date']
)

problem_orders.groupby('seller_id')['seller_late'].mean().sort_values(ascending=False)

The seller shipment delay rate—defined as cases where carrier handover occurs after the shipping_limit_date—ranges from 16.7% to 30.3%, which is 1.8–3.3 times higher than the Olist overall average (9.2%). This suggests that the primary driver of low review scores is more likely seller-side fulfillment and processing delays rather than external logistics infrastructure issues.

However, the characteristics of the office_furniture category, where products are typically large and bulky, as well as the remaining limitations in logistics infrastructure, should not be overlooked.

Therefore, it would be useful to examine the correlation between delivery time or delay status and product characteristics, specifically product volume and weight, where volume is calculated using product length, height, and width.

Correlation between delivery time and product characteristics

The base dataset was created using the full dataset instead of focusing on a specific category(office_furniture).

Orders with delivery delays took about 30 days on average, compared to around 10 days for non-delayed orders.

Delayed orders are approximately 300g heavier on average than non-delayed orders.

Delayed orders have about 10% larger product volume on average than non-delayed orders.

However, the correlation analysis shows a coefficient of 0.016, indicating virtually no relationship between product volume and delivery delays. ![] (https://velog.velcdn.com/images/hyuunk_/post/cbfec604-5794-4940-95bf-b291f653e551/image.png)

The correlation between product weight and freight value is 0.61, indicating a relatively strong positive relationship. Similarly, product volume and freight value show a correlation of 0.59, suggesting a comparable positive association.

Delayed orders took an average of about 30 days for delivery, while non-delayed orders took around 10 days on average.

[Conclusion] Low review scores in the office_furniture category are primarily driven by seller-side shipment delays rather than logistics infrastructure constraints.

3. Logistics Infrastructure Analysis

Same State vs Differnet State

When the seller and customer are in the same state, the average delivery time is about 7 days, while it increases to about 14 days when they are in different states, indicating that cross-state deliveries take roughly twice as long.

States with the highest average delivery days were identified for orders where the seller and customer are located in the same state.

Delivery Delay Ratio by State

To gain insights into the causes of delivery delays, cases where shipping_limit_date is later than order_delivered_carrier_date were defined as seller_delay, while the opposite cases were defined as logistics_delay.

[Python Code]

# 배송소요일 계산 
base2['delivery_days'] = (
    base2['order_delivered_customer_date'] - 
    base2['order_purchase_timestamp']
).dt.days

# 최종 배송 지연 여부 8ㅑ![](https://velog.velcdn.com/images/hyuunk_/post/d3eff9d6-709c-4ad0-866c-bd8328aa0563/image.png)

base2['is_delayed'] = (
    base2['order_delivered_customer_date'] > 
    base2['order_estimated_delivery_date']
)

# seller 출고 지연 여부 
base2['seller_late'] = (
    base2['order_delivered_carrier_date'] >
    base2['shipping_limit_date']
)

Load only states with more than 200 total orders (order_id).

The analysis suggests key point that logistics infrastructure may play a larger role in delivery delays in RJ. In this region, 77% of delayed orders occur during the logistics stage, which is nearly 20 percentage points higher than the approximately 60% observed in other regions.

Average lead time by state analysis

Seller processing lead time and logistics delivery lead time(carrier_delivery_days) were defined as follows.

[Python Code]

# Seller Processing Lead Time 
base2['seller_processing_days'] = (
    base2['order_delivered_carrier_date'] - 
    base2['order_purchase_timestamp']
).dt.days

# Carrier Delivery Days  
base2['carrier_delivery_days'] = (
    base2['order_delivered_customer_date'] - 
    base2['order_delivered_carrier_date']
).dt.days

Based on the average across all customer states, the seller processing lead time for delayed orders is 5.36 days, compared to 2.52 days for non-delayed orders, indicating it roughly doubles when delays occur. However, the carrier delivery lead time increases much more significantly—from 7.44 days to 25.19 days, more than tripling. This suggests that delivery delays are driven more by logistics delivery time than by seller processing time.

This boxplot analysis shows that carrier delivery time is significantly longer for delayed orders, with the median increasing from about 7 days to around 20 days. The distribution is also much wider, with several extreme delays observed, suggesting that logistics delivery time is a major driver of delivery delays.

The median logistics delivery time for delayed orders is about three times higher than that of non-delayed orders, and in some cases extreme delivery delays exceeding 100 days were observed.

In-depth Analysis of RJ Delivery Time

The distribution shows that non-delayed orders are concentrated within 3–10 days, while delayed orders are mainly distributed between 15–30 days with a long tail extending beyond 40 days. Considering the result above, this indicates that delivery delays are primarily driven by substantially longer carrier delivery times.

A deeper analysis of logistics lead time in the RJ region reveals several key patterns. Most deliveries are concentrated within 5–15 days, indicating relatively fast delivery lead times on average. In fact, the majority of orders arrive well before the SLA threshold. However, deliveries begin to exceed the SLA around the 95th percentile (P95). The distribution also shows a long right tail, suggesting that a small number of extreme delays significantly contribute to the overall delay rate. The long right tail in the distribution contributes significantly to the high delivery delay rate.

Comparing carrier delivery times between Brazil overall and the RJ region shows little difference in the median, but RJ exhibits longer delivery times in the upper percentiles, with delays of +6 days at P90, +9 days at P95, and +12 days at P99.

RJ experiences 2–3 times more extremely long delivery delays (30+ days) compared to the overall average across Brazilian states, indicating a structural tendency toward long-tail delivery delays.

RJ shows similar average delivery performance to the national level, but the severity of SLA exceedance and the frequency of extremely long deliveries are 2–3 times higher than the national average. This suggests the presence of structural bottlenecks that occur under certain conditions.

Therefore, it is necessary to drill down into the top 5% of RJ orders (those exceeding P95) to examine whether the delays are concentrated in specific product categories, time periods, or carriers.

Top 5% Tail Analysis of Carrier Delivery Days in RJ

Analysis of the top 5% delayed orders in the RJ region shows that the seller processing time remained within a normal range, averaging around 3 days, while the carrier delivery time increased abnormally to an average of 45 days. This indicates that the primary cause of the delay lies in the logistics transportation stage rather than seller processing.

76.7% of the top 5% delayed deliveries in RJ originate from sellers located in SP, which is about 10 percentage points higher than the overall share of SP-origin orders in RJ (66%). This suggests the possibility of relatively more severe logistics bottlenecks along the SP→RJ delivery route.

Average Review Score by Freight Cost Across Regions

Across all regions, a clear negative relationship is observed between delivery days and review scores, indicating that longer delivery times consistently lead to lower customer ratings.

Average Late Rate by Category (Top 15)

Several categories show late delivery rates above the overall average (~10%), with audio, fashion underwear, and Christmas items recording the highest delay rates.

Monthly Late Rate & Order Volume

Late delivery rates fluctuate over time, with noticeable spikes during periods of high order volume, suggesting demand surges may contribute to delivery delays.

Creating RFM Data Mart on MySQL Recency, Frequency, Monetary, avg_delivery_days, avg_review_score, delay_rate are produced as columns.

USE olistdata;

WITH pay AS (
  SELECT
    order_id,
    SUM(payment_value) AS order_payment
  FROM order_payments
  GROUP BY order_id
),

delivery AS (
  SELECT
    order_id,
    order_delivered_customer_date,
    order_estimated_delivery_date,
    DATEDIFF(order_delivered_customer_date, order_purchase_timestamp) AS delivery_days
  FROM orders
  WHERE order_status = 'delivered'
),

base AS (
  SELECT
    c.customer_unique_id,
    o.order_id,
    o.order_purchase_timestamp,
    pay.order_payment,
    d.delivery_days,
    d.order_delivered_customer_date,
    d.order_estimated_delivery_date,
    r.review_score
  FROM orders o
  JOIN customers c
    ON o.customer_id = c.customer_id
  JOIN pay
    ON o.order_id = pay.order_id
  JOIN delivery d
    ON o.order_id = d.order_id
  LEFT JOIN order_reviews r
    ON o.order_id = r.order_id
  WHERE o.order_status = 'delivered'
),

snapshot AS (
  SELECT MAX(order_purchase_timestamp) AS snap_dt
  FROM orders
  WHERE order_status = 'delivered'
)

SELECT
  b.customer_unique_id,

  -- RFM
  DATEDIFF(MAX(s.snap_dt), MAX(b.order_purchase_timestamp)) AS recency,
  COUNT(DISTINCT b.order_id) AS frequency,
  SUM(b.order_payment) AS monetary,

  -- 배송 & 리뷰
  AVG(b.delivery_days) AS avg_delivery_days,
  AVG(b.review_score) AS avg_review,

  -- 지연율
  AVG(
    CASE
      WHEN b.order_delivered_customer_date > b.order_estimated_delivery_date THEN 1
      ELSE 0
    END
  ) AS delay_rate

FROM base b
CROSS JOIN snapshot s
GROUP BY b.customer_unique_id;

Created Data Mart are saved as csv file in computer, and imported into Tableau for further analysis of RFM Data

2. Score Caculation & Visualization (Tableau)

Recency Score

The first step in Tableau was to create Recency Score and vizualize distribution with bar chart.

Recency was defined as the number of days since the customer's most recent purchase, calculated from the last date available in the dateset. I assigned higher Recency score to customers with lower Recency values.

Inactive customer concentration This distribution shows a strong concentration of customers with low Recency scores. About 31.56% of customers fall into Recency score 1, indicating that a large portion of customers have not made a purchase recently. The remaining scores are relatively evenly distributed, suggesting that active customers are spread across different recency levels.

Frequency Score

Frequency Score are calcuated like this. The more customers are involved in purchase the higher score they're assigned

97% of customers are involved in 1 purchase, only 3% of customers purchased over 2 on the other hand.

Monetary Score

Monetary scores were assigned based on customer spending levels, with higher spending customers receiving higher scores.

Long Tail Distribution Most customers belong to the lower Monetary score groups, indicating that the majority of customers spend relatively small amounts, while only a small portion of customers are high-value spenders.

RFM Group Score

Added three separate score, RFM Score has been calulated

3. RFM Analysis (Focusing on relationship between average arrived days and average review score)

Most customers fall into the mid-range RFM scores, particularly between 10 and 13. Customer satisfaction remains consistently high, with average review scores above 4 across most segments. Delivery times are generally stable around 10–12 days, although a few segments show longer delivery periods.

4. Arrived Day & Reivew Score Analysis

Average arrived days by review score (1~5)

Clear negative relationship between delivery time and customer satisfaction are observed. As delivery speed improves, the average review score increases.

• EDA를 시작하기에 앞서, 간편함을 위해 데이터마트를 생성하였다.

• 컬럼으로는 주문ID/고객ID/고객 주/배송소요일/지연일수/리뷰스코어/결측치를 제외한 order_purchase_timestamp, order_delivered_customer_date, order_estimated_Date가 9개의 컬럼이 포함되었다.

2. 일별 주문량 계산

SELECT
  DATE(order_purchase_timestamp) AS order_date,
  COUNT(*) AS daily_orders
FROM orders
WHERE order_status = 'delivered'
  AND order_purchase_timestamp IS NOT NULL
GROUP BY order_date
ORDER BY order_date;

• MySQL에서 전체 기간 주문량을 일별로 나타낸 CSV 파일을 파이썬에서 시각화하였다.

  • 가장 눈에 띄는 특징으로는 2017-11-24일 주문량에서 매우 큰 이상치가 발견된다는 점이다.

• 리서치 결과, 해당 일자는 블랙프라이데이로 확인되었다.
• 따라서, 블랙프라이데이에 대한 세부적인 EDA를 진행해보았다.

3. 블랙프라이데이 카테고리별 판매량

select 
p.product_category_name as category, 
count(*) as items_sold 
from orders o
join orders_items oi 
on o.order_id = oi.order_id 
join products p 
on oi.product_id = p.product_id
where o.order_status = 'delivered' 
and date(o.order_purchase_timestamp) = '2017-11-24'
group by p.product_category_name
order by items_sold desc;

• 마찬가지로 SQL에서 CSV파일을 생성한 뒤 Top10 판매 카테고리를 파이썬에서 시각화하였다.

• 그 결과, 침구/식탁/욕실용품이 압도적인 판매량을 보였고, 가구/인테리어, 공구/정원용품, 스포츠/레저, 뷰티/헬스, 휴대폰/통신기기, 시계/선물용품, 장난감, 컴퓨터/IT/악세사리, 향수가 뒤를 이었다.

• 1-2개 카테고리만 압도적으로 나오는 관계로 로그 변환를 해주어 재확인하였다.

4. 블랙프라이데이 카테고리별 AOV

SELECT
  category,
  AVG(order_value) AS avg_order_value
FROM (
  SELECT
    o.order_id,
    p.product_category_name AS category,
    SUM(oi.price + oi.freight_value) AS order_value
  FROM orders o
  JOIN orders_items oi
    ON o.order_id = oi.order_id
  JOIN products p
    ON oi.product_id = p.product_id
  WHERE o.order_status = 'delivered'
    AND DATE(o.order_purchase_timestamp) = '2017-11-24'
  GROUP BY o.order_id, p.product_category_name
) t
GROUP BY category
ORDER BY avg_order_value DESC;

• 판매량에 이어 Top10 AOV(판매금액)을 확인하였다.

• 농업/산업/상업용 장비가 압도적인 판매량을 기록하였다.

• 대형가전, 가전제품, 건설/공구/조명, 시계/선물용품, 악기, 카메라/영상장비, 거실가구, 사무용가구, 아이디어/기획상품이 뒤를 이었다.

5. 블랙프라이데이 할부율 분석

• 가설: 세일 이벤트 기간이 고객들의 고가 물품 소비를 촉진하여 할부율이 높아졌을 것이다.

  SELECT
  period,
  COUNT(*) AS orders,
  ROUND(AVG(payment_installments), 2) AS avg_installments,
  ROUND(SUM(CASE WHEN payment_installments > 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS installment_rate
  FROM (
  SELECT
    o.order_id,
    CASE
      WHEN DATE(o.order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-11-23' THEN 'Before'
      WHEN DATE(o.order_purchase_timestamp) = '2017-11-24' THEN 'Black Friday'
      WHEN DATE(o.order_purchase_timestamp) BETWEEN '2017-11-25' AND '2017-12-08' THEN 'After'
    END AS period,
    op.payment_installments
  FROM orders o
  JOIN order_payments op ON o.order_id = op.order_id
  WHERE o.order_status = 'delivered'
    AND DATE(o.order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-12-08'
  ) t
  GROUP BY period;

• 비교 기간은 블랙프라이데이 전후 2주로 잡았다.
• 전체 기간으로 잡고 비교한다면 블랙프라이데이가 아닌 계절 요인, olist 서비스 확대 요인 등 불순물이 추가되어 목적이 흐려질 수 있다는 판단으로 전후 비교 기간을 2주로 설정하였다.

• 블랙프라이데이의 평균 할부 개월 수는 3.51개월로, 블랙프라이데이 전 2주와 후 2주와 비교했을 때 가장 많았다.

• 할부율 또한 57.95%로 가장 높음을 확인할 수 있다.
• 이는 블랙프라이데이가 고가 상품 구매를 유도하거나, 평소 미루던 소비를 앞당긴 이벤트 였음을 시사하고 있다.

6. 블랙프라이데이 배송 지연율 분석

• 가설: 주문량이 폭증한만큼, 배송 지연율 또한 높아졌을 것이다. SELECT period, round(AVG(is_delayed)*100,2) AS delay_rate FROM ( SELECT CASE

  WHEN DATE(order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-11-23' THEN 'Before'
  WHEN DATE(order_purchase_timestamp) = '2017-11-24' THEN 'Black Friday'
  WHEN DATE(order_purchase_timestamp) BETWEEN '2017-11-25' AND '2017-12-08' THEN 'After'

  END AS period, is_delayed FROM delivery_review_dm WHERE DATE(order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-12-08' ) t GROUP BY period;

• 앞서 생성한 배송-리뷰 데이터마트(delivery_review_dm)를 활용하였다.

• 블랙프라이데이의 배송지연율이 평소(Before)이 배해 거의 1.7배 가까이 상승했음을 알 수 있다.

• 즉, 블랙프라이데이 기간 중 주문 급증으로 인해 배송 시스템의 처리 한계를 초과했고, 그 영향이 이벤트 이후까지 이어졌음을 알 수 있다.

• 종합적으로, 고객은 블랙프라이데이에 더 많은, 더 큰 금액을, 더 쉽게 결제했지만, 운영 측면(배송)은 이를 따라가지 못했음을 알 수 있다.
• 이는 앞서 분석한 지역별 배송 지연 인사이트 발굴과 연결지어 해석할 수 있다.

[특징 및 인사이트]

• zip 기준으로 '대표위경도'를 만들어서 같은 동네에 사는 각각의 주문들은 하나의 점으로 만들어 주었다.(평균)
• Sellers는 대도시 밀집 지역인 남동부 지역에 밀집되어 있다.

# geolocation을 zip 기준으로 '대표위경도' 만들기 (평균)
geo_agg = ( 
    geo.groupby('geolocation_zip_code_prefix', as_index=False)
    .agg({'geolocation_lat' : 'mean', 'geolocation_lng' : 'mean'})
)

# sellers에 위경도 붙이기 
sellers_map_df = sellers[['seller_id', 'seller_zip_code_prefix', 'seller_city', 'seller_state']].merge(
    geo_agg,
    left_on='seller_zip_code_prefix', 
    right_on='geolocation_zip_code_prefix',
    how='left'
)

2. Customers 분포

[인사이트]

• Olist의 주 고객들은 남동부와 북동부 지역에 밀집되어 있다.
• Customer도 Seller와 마찬가지로 customer 테이블에서 아이디/zip 코드/도시/주 칼럼을 geo_agg와 merge하여 customers_map_df 데이터프레임을 생성한 뒤 지도를 그린다.

3. 지연 건수

# 배송이 estimated time보다 delay된 지역만 뽑아서 보기 

df_delay = orders[[
    'order_id', 'customer_id',
    'order_purchase_timestamp',
    'order_delivered_customer_date', 
    'order_estimated_delivery_date'
]].copy()

# daytime 변환 
for c in ['order_purchase_timestamp', 'order_delivered_customer_date', 'order_estimated_delivery_date']:
    df_delay[c] = pd.to_datetime(df_delay[c], errors='coerce')

# delivered & estimated 있는 것만 
df_delay = df_delay.dropna(subset=['order_delivered_customer_date','order_estimated_delivery_date'])

# 지연일수
df_delay['delay_days'] = (df_delay['order_delivered_customer_date'] - df_delay['order_estimated_delivery_date']).dt.days

# delay된 주문만
df_delay = df_delay[df_delay['delay_days'] > 0].copy()

df_delay[['order_id','delay_days']].head()

[output]

• 실제 배송이 지연된 주문과 그 지연 일수를 정리한 분석용 데이터셋 도출

df_delay = df_delay.merge(
    cus[['customer_id','customer_zip_code_prefix','customer_city','customer_state']],
    on='customer_id',
    how='left'
)

df_delay = df_delay.merge(
    geo_agg[['geolocation_zip_code_prefix','geolocation_lat','geolocation_lng']],
    left_on='customer_zip_code_prefix',
    right_on='geolocation_zip_code_prefix',
    how='left'
)

df_delay = df_delay.dropna(subset=['geolocation_lat','geolocation_lng']).copy()
df_delay.shape

[인사이트]

• 위 지도는 지연 주문의 절대적인 개수이다.
• 대도시 밀집 지역인 남동부 지역에 전체 주문량이 많은만큼 확실히 지연 주문 건수도 많은 것을 알 수 있다.
• 초록색: 지연 주문 한 자릿 수 / 노란색: 지연 주문 두 자릿 수 / 주황색: 지연 주문 3-4자릿수
• 하지만 위 지도는 절대적인 지연 주문량만 나타낼 뿐, 지연율 및 배송 속도 평균 등 성능을 비교하기에는 무리가 있다.
• 그래서 초록색 지역은 배송이 빠르고 주황색 지역은 배송이 느리다로 해석하면 안된다.

4. 배송 지표 만들기

# 배송 + 고객
base = (orders[['order_id','customer_id',
                'order_purchase_timestamp',
                'order_delivered_customer_date',
                'order_estimated_delivery_date']]
        .merge(cus[['customer_id','customer_state']],
               on='customer_id', how='left'))

# datetime 변환
for c in ['order_purchase_timestamp',
          'order_delivered_customer_date',
          'order_estimated_delivery_date']:
    base[c] = pd.to_datetime(base[c], errors='coerce')

base = base.dropna(subset=[
    'order_purchase_timestamp',
    'order_delivered_customer_date',
    'order_estimated_delivery_date'
])

# 배송 지표
base['shipping_days'] = (
    base['order_delivered_customer_date']
    - base['order_purchase_timestamp']
).dt.days

base['delay_days'] = (
    base['order_delivered_customer_date']
    - base['order_estimated_delivery_date']
).dt.days

base['is_delayed'] = (base['delay_days'] > 0).astype(int)

# 리뷰 결합
base = base.merge(
    ordrev[['order_id','review_score']],
    on='order_id',
    how='left'
)

state_metrics = (
    base.groupby('customer_state')
    .agg(
        주문건수=('order_id','count'),
        배송지연율=('is_delayed','mean'),
        평균배송소요일=('shipping_days','mean'),
        평균지연일수=('delay_days', lambda x: x[x>0].mean()),
        평균리뷰평점=('review_score','mean')
    )
    .reset_index()
    .sort_values('배송지연율', ascending=False)
)

state_metrics

5. 지연율 지도 시각화

[분석 결과 요약 및 인사이트 정리]

• 분석 결과 요약: 브라질 북부/북동부 지역에서 배송 지연율이 상대적으로 높게 나타난 것을 볼 수 있다. 반면, 남동부 지역은 낮은 지연율을 보였다. 이는 판매자/고객 밀집 지역과 물류 인프라 수준 차이에서 기인한 구조적 문제로 해석할 수 있다.

• 결과 해석:

1) 북부/북동부 지역: 높은 배송 지연율

• Amazonas, Para, Maranhao, 일부 Nordeste 지역에서 지연율이 가장 높다.
• 주문 절대량은 남동부에 비해 많지 않지만, 주문 대비 지연 비율이 매우 높다
• 장거리 배송에 필요한 물류 허브 부족, 도로/운송 인프라 열세, 배송 리드타임 변동성 등으로 해석할 수 있다.

  2) 중부 지역: 중간 수준의 지연율

• Mato Grosso, Goias 등
• 주문량과 지연율 모두 중간 수준
• 물류 접근성은 일부 확보했으나 남동부 대비 여전히 거리 연결성의 한계가 존재한다.

  3) 남동부/남부: 낮은 배송 지연율

• SP, Rio de Janerio, Parana, Minas Gerais 등
• 대도시 밀집 지역으로, 주문량이 매우 많지만 지연율은 오히려 낮다.
• Sellers 분포 지도에서 알 수 있듯이, 판매자 밀집 지역이라 평균 배송 거리가 짧다. 물류센터 및 허브가 집중 되어있는 곳이기 때문이다.

• 인사이트

1) 지연율은 주문량과 반드시 비례하지 않는다. 오히려 물류 인프라와 지역 접근성의 문제로 해석할 수있다. 2) 북부/북동부: SLA 차등 적용 필요, 물류 파트너 확대 or 지역 허브 전략 검토 필요 3) 남동부: 대규모 주문 처리에도 안정적인 현재 구조

6. 분포비교(시각적 검증)

[인사이트]

브라질 olist 데이터분석 프로젝트 포트폴리오 구성

1. 개요 및 데이터 소개

2. RFM 세그먼트, 배송, 리뷰 데이터마트 생성(SQL)

• RFM 세그먼트별로 평균 배송일자 및 리뷰점수 상관관계 분석을 위한 데이터마트 생성
• 주문 데이터 기반 고객 RFM 지표 산출
• SQL을 활용한 고객 세그먼트 분류

3. RFM 고객 세그먼트별 특징 시각화(Tableau)

4. SQL 카테고리 관련 데이터마트 생성 (SQL)

5. RFM 고객 세그먼트별 특징 시각화(추가분석)

6. 지역별 배송 지연 인사이트 발굴(Python)

7. 프로젝트를 마치며...

• 이커머스 주문은 평일보다는 시간적으로 여유가 있는 주말에 더 많이 발생할 것이다.(기각)

• 배송 소요일수가 짧을수록 고객 리뷰 점수가 높을 것이다. (채택)
• 대도시 고객일수록 상품 가격 대비 실제 결제 금액이 더 높을 것이다. (채택)

EDA를 위한 KPI 선정

• 아직 전처리는 하지 않은 상태에서, Raw Data를 훑어보며 주요 KPI를 선정하였다.
• 이커머스에서 활용되는 주요 KPI가 있지만, 세부적인 EDA를 위해 세워둔 일부 가설을 기반으로 더 디테일 한 KPI를 선정하였다 .

Olist Store E-commerce analysis Project KPI

   - KPI1: 주말 vs 평일 주문 기준 결제 통계 (order_purchase_timestamp) 
   - KPi2: 리뷰스코어 5점이고 결제 수단이 신용카드인 주문 건수 
   - KPI3: 펫샵(pet_shop) 카테고리 주문의 평균 배송 소요일수
   - KPI4: 상파울로 거주 고객의 평균 상품 가격 및 평균 결제 금액 
   - KPI5: 배송 소요일수(배송완료일-주문일시)와 리뷰 평점 간의 관계

EDA 1

KPI1: 주말 vs 평일 주문 기준 결제 통계

[쿼리]
SELECT * FROM olistdata.orders;
SELECT * FROM olistdata.order_payments;

select kpi1.day_end, 
concat(round(kpi1.total_payment / (select sum(payment_value) from order_payments) * 100, 2)
, '%') as percentage_payment_values 
from 
(select ord.day_end , sum(pmt.payment_value) as total_payment
from order_payments as pmt 
join 
(select distinct order_id, 
case 
when weekday(order_purchase_timestamp) in (5,6) then "Weekend" 
else "Weekday"
end as Day_end 
from orders) as ord
on ord.order_id = pmt.order_id
group by ord.day_end) as kpi1 ; 

[Result]

[Insight]

• 전체 결제 금액 중, 약 3/4가 평일에 발생하였음
• 즉, 고객들은 주말보다 평일에 훨씬 더 많이 구매
• 주말 쇼핑이 주력일 거라는 가설과는 다른 결과

KPI2: 리뷰스코어 5점이고 결제 수단이 신용카드인 주문 건수

[쿼리]
select 
count(pmt.order_id) as Total_Orders
from 
order_payments pmt
inner join order_reviews rev on pmt.order_id = rev.order_id
where 
rev.review_score = 5 
and pmt.payment_type = 'credit_card' ;

[Result]

[Insight]

• 고객 만족도 + 핵심 결제수단이 동시에 충족된 주문이 상당히 많음

KPI3: 펫샵(pet_shop) 카테고리 주문의 평균 배송 소요일수

[쿼리]
select 
prod.product_category_name, 
round(avg(datediff(ord.order_delivered_customer_date , ord.order_purchase_timestamp)), 0) as Avg_delivery_days 
from orders ord 
join 
(select product_id, order_id, product_category_name
from products
join orders_items using(product_id)) as prod 
on ord.order_id = prod.order_id 
where prod.product_category_name = "Pet_shop"
group by prod.product_category_name ; 

[Result]

[Insight]

• 평균 11일의 소요 시간은 이커머스 기준으로 다소 긴 편에 속함
• 추가분석1: "배송이 느린 카테고리는 고객 만족도에 어떠한 영향을 주는가?"
• 추가분석2: "펫샵 vs 전체 평균 배송일 비교"
• 추가분석3: "펫샵 내 배송일수 구간별 리뷰 분포"

KPI4: 상파울로 거주 고객의 평균 상품 가격 및 평균 결제 금액

[쿼리]
with orderItemsAvg as ( 
select round(avg(item.price)) as avg_order_item_price
from orders_items item
join orders ord 
on item.order_id = ord.order_id 
join customers cust on ord.customer_id = cust.customer_id 
where cust.customer_city = "Sao Paulo" 
)
select 
(select avg_order_item_price from orderItemsAvg) as avg_order_item_price, 
round(avg(pmt.payment_value)) as avg_payment_value 
from order_payments pmt 
join orders ord on pmt.order_id = ord.order_id 
join customers cust on ord.customer_id = cust.customer_id 
where 
cust.customer_city = "Sao Paulo" ;

[Result]

[Insight]

• 실제 상품 가격 평균은 108(약 29,000원), 최종 결제 금액은 136(약 36,700원)
• 상품 가격 외 추가 비용이 결제 금액에 반영되고 있음을 알 수 있음(ex.배송비, 세금, 복수 상품 구매 가능성)
• 추가분석 1: 상파울루 vs 타 도시 평균 비교
• 추가분석 2: 상파울루 배송비 평균 추정

KPI5: 배송 소요일수(배송완료일-주문일시)와 리뷰 평점 간의 관계

[쿼리]
select 
rew.review_score, 
round(avg(datediff(ord.order_delivered_customer_date , order_purchase_timestamp)),0) as "Avg shipping days" 
from orders as ord 
join order_reviews as rew on rew.order_id = ord.order_id 
group by rew.review_score 
order by rew.review_score ;

[Result]

[Insight]

• 배송이 빠를수록 리뷰 점수가 높고, 배송이 느릴수록 리뷰점수가 낮다
• 리뷰1점 고객은 평균 21일 대기, 리뷰 5점 고객은 평균 11일 대시(10일 차이)
• 배송 속도는 고객 만족도를 결정짓는 핵심 요인 중 하나

[총평] 본 분석은 고객의 구매 시점, 결제 방식, 배송 리드타임이 고객 만족도에 미치는 영향을 검증하기 위해 다섯 가지 가설을 설정하였다. 분석 결과, 주문은 주말보다 평일에 집중되어 있었으며, 고만족 고객은 신용카드 결제에 집중되는 경향을 보였다. 또한 특정 카테고리에서는 배송 기간이 상대적으로 길게 나타났고, 대도시 고객일수록 상품 가격 대비 실제 결제 금액이 높았다. 마지막으로 배송 소요일수와 리뷰 점수 간에는 명확한 음의 상관관계가 확인되어, 배송 속도가 고객 만족도를 결정짓는 핵심 요인임을 데이터로 입증하였다.

     1) SQL 입문자로써, 이만한 양질의 데이터를 찾기 어려웠다. 
     2) 선행분석들이 많아 어려움을 겪을 때 참고할 수 있는 자료가 많았다.

     12월 안에 SQL 및 Python을 통한 분석과 POWER BI 대시보드, 포트폴리            오까지 완성하는 것을 목표.

     1) 매일 분석한 내용들을 벨로그에 기록하고 회고. 
     2) 추가 작성 예정 

테이블 및 컬럼 확인

데이터 정제

• 처음 데이터를 다운로드 받았을 때, 텍스트 데이터 중 알파벳이 깨져 들어있는 행들이 많았다.
• 우선, 엑셀 상으로 Ctrl+F 기능을 통해 한글로 잘못들어온 글자들을 알파벳으로 변환해주었다. 'geolocation' 테이블의 'city' 컬럼에서 고급필터 기능을 통해 어떤 도시 종류들이 있는지 쭉 뽑아낸다음, 잘못 받아진 글자들을 변환하였다.
• 예를 들어, 찾->a, 챕->e, 찼->o으로 잘못 변환되어 받아졌음을 확인하고 ctrl+F를 통해 한번에 바꾸어 주었다.
• 마찬가지로, 'order_reviews' 테이블의 'comment message' 칼럼에도 해당 작업이 필요함을 뒤늦게 확인하였다. (Python을 통해 SQL로 Data Import를 하는 과정에서 깨져서 들어온 글자 때문에 인코딩 문제를 겪으며 해당 테이블에도 같은 문제가 있음을 파악하였다)
• 너무 많은 텍스트 데이터가 있어서, 엑셀 상으로 하나하나 바꾸어주는 대신에 기존에 설정한 'utf-8'을 'latin1'로 바꿔주어서 깨진 특수 문자를 전부 강제로 문자로 읽게 하였다.

환경 세팅 단계(인프라)

1. 물리적 스키마 생성

• MySQL에서 'olistdata'라는 이름으로 물리적 스키마를 생성해주었다.(Creata New Scheme)

2. 데이터 적재

• MySQL Database 카테고리에 있는 'Data Import'기능을 통해 테이블을 불러오려했으나 잘 되지않았다.
• 'Table Data Import Wizard'를 통해 파일별로 데이터를 불러오려했으나 시간이 너무 소요되는 문제가 있었다.
• 그래서 그냥 Python으로 SQL에 데이터를 보내는 방식을 택하기로 하였다.

  [사용코드]
  

engine = create_engine( "mysql+pymysql://root:Marine13258!@localhost:3306/olistdata" ) base_path = r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset"

files = { r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_customers_dataset.csv": "customers", r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_geolocation_dataset.csv": "geolocation", r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_order_items_dataset.csv": "orders_items", r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_order_payments_dataset.csv": "order_payments", r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_order_reviews_dataset.csv": "order_reviews", r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_orders_dataset.csv": "orders", r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_products_dataset.csv": "products", r"C:\Users\SAMSUNG\OneDrive\바탕 화면\olist dataset\olist_sellers_dataset.csv": "sellers", }

for files, table in files.items(): print(f"{table}) 업로드 중...") df = pd.read_csv( os.path.join(base_path, files), encoding="latin1" ) df.to_sql(table, engine, if_exists='replace', index=False) print(f"{table} 완료")

print("전체 업로드 완료")

```

분석 단계

1. 모델링

• 'Moldes' 카테고리에서 데이터 구조를 파악하고 EER 다이어그램을 통해 도식화하기 위해 데이터를 뜯어 보았다.
• 용어나 PK, FK 등 개념들이 헷갈려서 기본서와 유튜브를 다시 찾아보았다.
• 그래도 아직 어떻게 해야할지 감이 잡히지 않는다.

<앞으로의 진행방향>

• 데이터 구조 파악 (PK/FK 설정)
• 다이어그램으로 도식화
• 데이터 전처리
• KPI 설정
• 분석 주제 설정