Understanding the Olist Orders Table: E-Commerce Order Lifecycle Analytics

Understanding the Orders Table: E-Commerce Data Postmortem Report

Data Source

Dataset Name

Brazilian E-Commerce Public Dataset by Olist

Source URL

[Brazilian E-Commerce Public Dataset by Olist]

https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce?resource=download&utm_source=chatgpt.com

---

E-Commerce Data Engineering

Understanding the Orders Table Is the First Step Toward Reliable Analytics

Every executive KPI, delivery metric, customer insight, and operational report depends on the quality of your order data. Before building dashboards and analytics solutions, ensure your business processes are fully understood, validated, and engineered correctly.

DeTLeng helps organizations build practical Data Engineering, ETL, BigQuery, Analytics Engineering, Reporting Automation, and Business Intelligence solutions that transform raw operational data into trusted business insights.

🚀 Explore DeTLeng Solutions

Data Engineering • ETL Development • BigQuery • Data Warehousing • Analytics Engineering • Business Intelligence

Table Under Investigation

cs003_olist_raw_orders

---

DeTLeng Operation Theatre

Patient

Orders Table

Status

Raw Dataset

Objective

Understand the complete order lifecycle
before designing an analytics-ready
data model.

---

Why This Table Matters

This is the central business process table.

Every e-commerce company ultimately asks:

How many orders?
How many customers?
How fast are we delivering?
How many orders are cancelled?
Are we meeting promised delivery dates?

All of these questions begin here.

---

Column Postmortem

Column	Business Meaning
order_id	Unique order
customer_id	Customer placing order
order_status	Current order state
order_purchase_timestamp	Purchase moment
order_approved_at	Payment approval
order_delivered_carrier_date	Sent to carrier
order_delivered_customer_date	Delivered
order_estimated_delivery_date	Promised delivery

---

Business Lifecycle

Customer Places Order
        ↓
Order Approved
        ↓
Carrier Receives Package
        ↓
Customer Receives Package

This table captures every stage.

---

Data Quality Investigation

---

Query 1

Total Orders

SQL

SELECT COUNT(*)
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`;

Purpose

Determine total order volume.

Business Value

Answers:

How many transactions
exist in the business?

---

Query 2

Duplicate Order Check

SQL

SELECT
order_id,
COUNT(*) cnt
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`
GROUP BY order_id
HAVING COUNT(*) > 1;

Purpose

Verify primary key uniqueness.

Business Risk

Duplicate orders can inflate:

Revenue
Order Count
Customer Metrics

Expected Result

0 rows

---

Query 3

Missing Order IDs

SQL

SELECT COUNT(*)
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`
WHERE order_id IS NULL;

Purpose

Ensure every order is identifiable.

Business Impact

Without an order ID:

No traceability
No reporting
No auditing

---

Query 4

Order Status Distribution

SQL

SELECT
order_status,
COUNT(*) total_orders
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`
GROUP BY order_status
ORDER BY total_orders DESC;

Purpose

Understand operational performance.

Business Questions

How many delivered?
How many cancelled?
How many unavailable?
How many shipped?

Executive Insight

Order status distribution reflects operational health.

---

Lifecycle Analytics

---

Query 5

Approval Time

SQL

SELECT
AVG(
TIMESTAMP_DIFF(
order_approved_at,
order_purchase_timestamp,
HOUR
)
) AS avg_approval_hours
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`;

Purpose

Measure payment approval speed.

Business Question

How quickly are orders approved?

Executive Value

Faster approval:

Faster fulfillment
Better customer experience

---

Query 6

Delivery Lead Time

SQL

SELECT
AVG(
TIMESTAMP_DIFF(
order_delivered_customer_date,
order_purchase_timestamp,
DAY
)
) AS avg_delivery_days
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`
WHERE order_status='delivered';

Purpose

Measure delivery speed.

Business Question

How long does delivery take?

Executive Value

One of the most important e-commerce KPIs.

---

Query 7

Late Deliveries

SQL

SELECT
COUNT(*) late_orders
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`
WHERE
order_delivered_customer_date >
order_estimated_delivery_date;

Purpose

Identify SLA failures.

Business Question

Did we deliver on time?

Executive Value

Customer satisfaction indicator.

---

Query 8

Late Delivery Percentage

SQL

SELECT
ROUND(
100 *
COUNTIF(
order_delivered_customer_date >
order_estimated_delivery_date
)
/
COUNT(*)
,2
) AS late_delivery_pct
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`
WHERE order_status='delivered';

Purpose

Measure delivery performance.

Business KPI

Late Delivery %

Executives love this metric.

---

Query 9

Average Fulfillment Time

SQL

SELECT
AVG(
TIMESTAMP_DIFF(
order_delivered_customer_date,
order_purchase_timestamp,
DAY
)
) AS fulfillment_days
FROM `detleng-case-studies.cs003_olist_raw.cs003_olist_raw_orders`
WHERE order_status='delivered';

Purpose

Measure end-to-end order completion.

Business Question

How long does it take
to fulfill an order?

---

Strategic Value of This Table

Without this table:

❌ No Sales Analytics

❌ No Delivery Analytics

❌ No Customer Analytics

❌ No Executive Dashboard

❌ No KPI Framework

---

With this table:

✅ Order Lifecycle Analytics

✅ Operational Reporting

✅ Delivery Performance

✅ Customer Journey Tracking

✅ Executive KPIs

✅ Business Intelligence Foundation

---

DeTLeng Conclusion

The Orders table is the operational heartbeat
of the Olist E-Commerce ecosystem.

Every order begins here.

Every delivery ends here.

Every executive KPI ultimately depends on
the quality, completeness, and reliability
of this dataset.

Before building dashboards, reports,
or advanced analytics, a Data Engineer
must fully understand and validate this table.

CS-003 begins with understanding the business
behind the data, not merely loading files into
a warehouse.

---

Remembered Point 📍

CS-003 Progress:

✅ order_items postmortem

✅ orders postmortem

⏳ next: customers postmortem

⏳ then full ERD / Data Model

⏳ then staging layer

⏳ then analytics-ready model

⏳ then BigQuery ETL implementation

⏳ then DeTLeng case study publication (CS-003) 🚀