Revenue Performance
How is Product Sales performing over time, and which categories are contributing to growth?
← Back to Projects
Olist Brazilian E-Commerce SQL Analysis
An end-to-end MySQL case study using the Olist Brazilian E-Commerce dataset to examine revenue performance, concentration risk, and the relationship between delivery outcomes and customer experience.
Business Questions
The analysis focuses on three management questions, with SQL used as the analytical method rather than the focus of the page.
Revenue Concentration & Dependency Risk
Where is Product Sales concentrated across customers, categories, and sellers?
Delivery Performance & Customer Experience
Where do delivery issues occur, and how are delivery outcomes associated with customer reviews?
Data & Methodology
A consistent scope keeps the analyses comparable and reduces the risk of inflated results from one-to-many joins.
Olist Brazilian E-Commerce
A relational dataset covering orders, customers, products, sellers, payments, reviews, and geographic information.
SUM(order_items.price)
Freight is evaluated separately and is not included in Product Sales.
Jan 2017 to Aug 2018
Incomplete boundary periods are excluded from the main trend analysis.
Delivered Orders
Sales-related analyses use delivered orders to keep the operating scope consistent.
- Use
customer_unique_idfor customer-level analysis. - Control for item-level and payment-level row multiplication.
- Retain the latest review when an order has multiple review records.
- Treat ZIP code prefixes carefully because they are not unique.
Relational model and ERD
orders serves as the central transaction table, linking customers to order items, payments, and reviews. Product and seller dimensions connect through order_items.
Revenue Performance
Business question: How is the business performing, and what is driving Product Sales growth?
Top 5 Categories39.88%of total Product Sales
Categories Growing50 of 7269.44% grew from 2017 H2 to 2018 H1
Top 5 Growth Contributors45.18%of total positive growth, equal to 811.7K in incremental Product Sales
Category Growth Breadth
Growing categories50
Non-growing categories22
69.44% of category groups grew from 2017 H2 to 2018 H1.
Delivered orders, Jan 2017 to Aug 2018. Product Sales = item price excluding freight.
Key Findings
- The top five product categories generated 39.88% of total Product Sales.
- 50 of 72 category groups grew from 2017 H2 to 2018 H1, representing 69.44% of categories.
- The top five growth categories contributed 45.18% of total positive growth.
- Growth was relatively broad based, but a meaningful share of incremental growth remained concentrated in the strongest categories.
Business Implication
Growth is not dependent on only a small number of categories, which suggests a relatively broad sales expansion. However, nearly half of positive growth still came from the five strongest growth categories. Management should monitor these key growth drivers while continuing to support growth across the broader category mix.
Highlighted SQL05_revenue_performance.sql
WITH monthly_sales AS (
SELECT
DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') AS order_month,
SUM(oi.price) AS product_sales
FROM orders AS o
JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
AND o.order_purchase_timestamp >= '2017-01-01'
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY order_month
),
sales_with_lag AS (
SELECT order_month, product_sales,
LAG(product_sales) OVER (ORDER BY order_month) AS previous_month_sales
FROM monthly_sales
)
SELECT order_month, product_sales, previous_month_sales,
ROUND((product_sales - previous_month_sales) / NULLIF(previous_month_sales, 0) * 100, 2) AS mom_growth_pct
FROM sales_with_lag
ORDER BY order_month;Revenue Concentration & Dependency Risk
Business question: Where is Product Sales concentrated, and where could dependency risk exist?
Customer29.14%of Product Sales generated by the top 5% of customers
Category33.14%of Product Sales generated by the top 5% of categories
Seller52.99%of Product Sales generated by the top 5% of sellers
Product Sales Concentration Comparison
Top 1%
Top 5%
Top 10%
Top 20%
Delivered orders, Jan 2017 to Aug 2018. Concentration is measured as share of Product Sales.
Key Findings
- Seller concentration was the strongest of the three dimensions analyzed.
- The top 5% of sellers generated 52.99% of Product Sales, compared with 33.14% for categories and 29.14% for customers.
- The top 20% of sellers generated 82.20% of Product Sales.
- Customer concentration should not be interpreted as dependence on loyal high-frequency buyers. Only 3.00% of customers placed more than one order, and repeat customers contributed only 5.50% of Product Sales.
Business Implication
Product Sales are more concentrated among sellers than among customers or product categories, making seller concentration the strongest potential dependency risk identified in the dataset. Management should monitor exposure to the highest-contributing sellers and evaluate whether key categories have sufficient seller diversification. This analysis identifies concentration, not confirmed supply risk, because the dataset does not include seller capacity, contracts, or substitution options.
Highlighted SQL06_revenue_concentration.sql
WITH seller_sales AS (
SELECT oi.seller_id AS seller_id, SUM(oi.price) AS product_sales
FROM orders AS o
JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
AND o.order_purchase_timestamp >= '2017-01-01'
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY oi.seller_id
),
ranked_sellers AS (
SELECT seller_id, product_sales,
ROW_NUMBER() OVER (ORDER BY product_sales DESC) AS sales_rank,
COUNT(*) OVER () AS total_sellers
FROM seller_sales
)
SELECT ROUND(SUM(CASE WHEN sales_rank <= CEIL(total_sellers * 0.05) THEN product_sales ELSE 0 END) / SUM(product_sales) * 100, 2) AS top_5_pct_share
FROM ranked_sellers;Delivery Performance & Customer Experience
Business question: Where are delivery problems occurring, and how are they associated with customer satisfaction?
Late Delivery Rate8.13%7,822 of 96,203 delivered orders arrived after the estimated delivery date
Late Order 1-Star Rate53.76%compared with 6.60% for on-time orders
Review Score Cliff>3 Days Lateaverage review score falls sharply once delays exceed approximately three days
Selected Customer State Late Delivery Rates
Selected states illustrate the variation in late delivery rates across destination markets.
Seller-tier late rates were similar: Tier A 8.47%, Tier B 7.41%, Tier C 8.08%.
Average Review Score by Delivery Timing
Delivered orders, Jan 2017 to Aug 2018. Review analysis uses the latest review per order.
Key Findings
- 8.13% of delivered orders arrived after the estimated delivery date.
- Seller sales tier did not meaningfully explain late delivery performance, with late rates of 8.47%, 7.41%, and 8.08% across Tiers A, B, and C.
- Late delivery rates varied substantially across customer destination states, suggesting stronger geographic concentration than seller-tier concentration.
- Average review scores declined sharply once delivery delays exceeded approximately three days.
- Late orders had a 53.76% one-star review rate compared with 6.60% for on-time orders, approximately 8.1x higher.
Business Implication
Delivery delays are strongly associated with lower customer satisfaction. The sharp deterioration after approximately three days late provides a practical intervention threshold: orders expected to exceed this point should be prioritized for proactive customer communication or operational review. Geographic differences also suggest that delivery performance should be monitored by destination market, while avoiding assumptions about root cause without additional logistics data.
Highlighted SQL07_delivery_customer_experience.sql
WITH ranked_reviews AS (
SELECT r.order_id AS order_id, r.review_score AS review_score,
ROW_NUMBER() OVER (PARTITION BY r.order_id ORDER BY r.review_answer_timestamp DESC) AS review_rank
FROM order_reviews AS r
),
latest_reviews AS (
SELECT order_id, review_score FROM ranked_reviews WHERE review_rank = 1
),
delivery_reviews AS (
SELECT DATEDIFF(o.order_delivered_customer_date, o.order_estimated_delivery_date) AS delay_days,
lr.review_score AS review_score
FROM orders AS o
JOIN latest_reviews AS lr ON o.order_id = lr.order_id
WHERE o.order_status = 'delivered'
AND o.order_purchase_timestamp >= '2017-01-01'
AND o.order_purchase_timestamp < '2018-09-01'
AND o.order_delivered_customer_date IS NOT NULL
AND o.order_estimated_delivery_date IS NOT NULL
)
SELECT CASE WHEN delay_days <= -10 THEN '10+ days early' WHEN delay_days < -3 THEN '3-10 days early'
WHEN delay_days < 0 THEN '0-3 days early' WHEN delay_days <= 3 THEN 'On time to 3 days late'
WHEN delay_days <= 7 THEN '3-7 days late' WHEN delay_days <= 15 THEN '7-15 days late'
ELSE '15+ days late' END AS delivery_timing,
ROUND(AVG(review_score), 2) AS avg_review_score
FROM delivery_reviews GROUP BY delivery_timing;Recommendations
01
Monitor and Diversify Seller Exposure
Seller concentration is the strongest potential dependency risk identified in the analysis, with the top 5% of sellers generating 52.99% of Product Sales. Management should monitor exposure to the highest-contributing sellers and evaluate whether key product categories have sufficient seller diversification.
02
Sustain Broad-Based Category Growth
Growth was relatively broad based, with 50 of 72 category groups increasing sales from 2017 H2 to 2018 H1. However, the top five growth categories still contributed 45.18% of total positive growth. Management should support the strongest growth drivers while continuing to develop the broader category mix.
03
Intervene Before Delays Exceed 3 Days
Customer satisfaction deteriorates sharply once delivery delays exceed approximately three days. Orders expected to cross this threshold should be prioritized for proactive customer communication or operational review, with delivery performance monitored by destination market.
Ready to learn more about my work?
Explore my background and experience, or get in touch.