E-commerce Sales and Delivery Performance

Portfolio projectOlist public dataset

The business question

A marketplace can grow its sales while customers in some regions still face slow deliveries or high freight charges. I used SQL and Power BI to bring commercial performance and customer experience into a single report, allowing readers to compare where demand comes from, which sellers contribute to it and how delivery performance varies.

What the data showed

Historical dataset, with all years, regions and states selected. Verified against the public report on 16 October 2026.

• The Southeast generated 65.4% of customer revenue. Customers in this region accounted for R$8.65 million of R$13.22 million in delivered product sales. This makes it the largest customer market in the report.
• The top ten sellers generated 13.3% of revenue. Together they contributed R$1.75 million, among 2,970 active sellers. Their share makes these sellers worth monitoring, while most revenue came from the wider network.
• The Northeast had the highest regional late-delivery rate: 12.7%. This compares with 6.8% overall and 6.1% in the Southeast. It is a clear priority for investigating delivery performance.
• Late orders received much lower review scores. Reviewed late orders averaged 2.27 out of 5, compared with 4.29 for on-time orders. The gap shows an association between delivery reliability and customer ratings, not proof of causation.

See the source values and calculations used for these findings. The figures here are a fixed snapshot; the embedded report responds to your filters.

Building a reliable analytical model

In MySQL, I extracted the raw CSV records into staging tables, cleaned text and numeric fields, and built analytical views. Item, payment and review records are summarised to one row per order before joining them to the order table. This prevents an order with several items or payments from multiplying the order-level totals.

Separate customer and seller geography dimensions distinguish where demand originates from where sellers are based. DAX measures calculate delivered revenue, average order value, active sellers and delivery performance within the selected context.

SQL and geographic data quality

The accompanying geography SQL script uses common table expressions and window functions to deduplicate postcode coordinates, calculate median locations and select a single state for each postcode. It also includes checks comparing customer counts before and after the geography join, helping detect joins that could multiply records.

The report uses a geography snapshot prepared from the source CSVs; the SQL script reproduces the same logic in MySQL.

SQL you can inspect

This shortened example uses the same CTE, join and delivery-flag logic as the order view. It totals the items first, then joins one result per order. The CASE expression marks delivered orders.

WITH item_summary AS (
    SELECT order_id,
           SUM(CAST(price AS DECIMAL(12,2))) AS item_revenue
    FROM raw_order_items
    GROUP BY order_id
)
SELECT o.order_id,
       COALESCE(i.item_revenue, 0) AS item_revenue,
       CASE WHEN o.order_status = 'delivered'
            THEN 1 ELSE 0 END AS delivered_flag
FROM raw_orders o
LEFT JOIN item_summary i ON o.order_id = i.order_id;

SELECT p.product_id,
       TRIM(BOTH '\r' FROM COALESCE(
           t.product_category_name_english,
           p.product_category_name, 'unknown')) AS product_category,
       CAST(NULLIF(p.product_weight_g, '') AS DECIMAL(12,2))
           AS product_weight_g
FROM raw_products p
LEFT JOIN raw_category_translation t
    ON p.product_category_name = t.product_category_name;

SELECT COUNT(*) AS customer_rows_before_join
FROM dim_customer;

SELECT COUNT(*) AS customer_rows_after_join,
       SUM(g.postcode IS NULL) AS customers_without_coordinates
FROM dim_customer c
LEFT JOIN vw_postcode_geography g
  ON LPAD(c.customer_zip_code_prefix, 5, '0') = g.postcode;

View the SQL project on GitHub

Four views of marketplace performance

• Market Geography: compare customer revenue, regional demand and service performance across Brazil.
• Sales & Assortment: explore monthly delivered merchandise revenue, leading categories and freight charged.
• Seller Network: examine seller concentration, leading sellers and regional commercial contribution.
• Delivery Experience: compare late deliveries, delivery times and review outcomes.

Decisions the report supports

The report gives a marketplace team a way to investigate whether commercially important regions also experience delivery problems, how dependent sales are on particular sellers, and where freight charges deserve closer review. These comparisons can guide follow-up analysis and operational prioritisation.

Limits of the analysis

This is a historical public dataset. Differences between regions, sellers or review groups describe associations and do not establish causes. Revenue is not profit, and customer freight charges are not the marketplace's delivery costs.