← All analytics projects
04
Project 04 · SQL, cohorts & marketplace operations
E-commerce SQL Analytics
Analyze marketplace orders with safe SQL grains, cohort retention, delivery KPIs, and category performance.
DuckDB SQLCohortsData quality
Delivered orders96,478
Item GMVR$13.2M
Repeat customer rate3.0%
On-time delivery91.9%
Average review4.16 / 5
Executive view
Marketplace growth and operating quality
Sep 2016 to Aug 2018 · reviewed DuckDB aggregates
Monthly item GMV
Item GMV (BRL)
Delivery experience
| Status | Orders | Avg days | Review | Low review |
|---|---|---|---|---|
| On time | 88,163 | 10.8 | 4.29 | 9.2% |
| Late | 7,661 | 31.3 | 2.57 | 54.0% |
Data quality
7 of 8 checks marked PASS
| Check | Issues | Status |
|---|---|---|
| delivered_missing_delivery_date | 8 | REVIEW |
| duplicate_customer_ids | 0 | PASS |
| duplicate_order_ids | 0 | PASS |
| duplicate_order_item_keys | 0 | PASS |
| nonpositive_item_price | 0 | PASS |
| orphan_customer_orders | 0 | PASS |
| orphan_order_items | 0 | PASS |
| orphan_payments | 0 | PASS |
Cohort analysis
Retention by months after first purchase
Weighted retention
0.17%
21,401 customers in eligible cohorts. Size-weighted active customers ÷ cohort size.
Cohort retention matrix
M0–M12; color intensity follows retention rate.
| Cohort | M0 | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2017-01 | 100.0% | 0.3% | 0.3% | 0.1% | 0.4% | 0.1% | 0.4% | 0.1% | 0.1% | — | 0.4% | 0.1% | 0.7% |
| 2017-02 | 100.0% | 0.2% | 0.3% | 0.1% | 0.4% | 0.1% | 0.3% | 0.2% | 0.1% | 0.2% | 0.1% | 0.3% | 0.1% |
| 2017-03 | 100.0% | 0.4% | 0.4% | 0.4% | 0.4% | 0.2% | 0.2% | 0.3% | 0.3% | 0.1% | 0.4% | 0.1% | 0.2% |
| 2017-04 | 100.0% | 0.6% | 0.2% | 0.2% | 0.3% | 0.3% | 0.3% | 0.3% | 0.3% | 0.2% | 0.3% | 0.1% | 0.0% |
| 2017-05 | 100.0% | 0.5% | 0.5% | 0.3% | 0.3% | 0.3% | 0.4% | 0.1% | 0.3% | 0.3% | 0.3% | 0.3% | 0.2% |
| 2017-06 | 100.0% | 0.5% | 0.4% | 0.4% | 0.3% | 0.4% | 0.4% | 0.2% | 0.1% | 0.2% | 0.3% | 0.4% | 0.2% |
| 2017-07 | 100.0% | 0.5% | 0.3% | 0.2% | 0.3% | 0.2% | 0.3% | 0.1% | 0.2% | 0.3% | 0.2% | 0.3% | 0.1% |
| 2017-08 | 100.0% | 0.7% | 0.3% | 0.3% | 0.3% | 0.5% | 0.3% | 0.3% | 0.1% | 0.1% | 0.3% | 0.2% | 0.1% |
| 2017-09 | 100.0% | 0.7% | 0.6% | 0.3% | 0.5% | 0.2% | 0.2% | 0.3% | 0.3% | 0.2% | 0.3% | 0.1% | — |
| 2017-10 | 100.0% | 0.7% | 0.3% | 0.1% | 0.2% | 0.2% | 0.2% | 0.4% | 0.3% | 0.2% | 0.2% | — | — |
| 2017-11 | 100.0% | 0.6% | 0.4% | 0.2% | 0.2% | 0.2% | 0.1% | 0.2% | 0.1% | 0.1% | — | — | — |
| 2017-12 | 100.0% | 0.2% | 0.3% | 0.3% | 0.3% | 0.2% | 0.2% | 0.0% | 0.2% | — | — | — | — |
| 2018-01 | 100.0% | 0.3% | 0.4% | 0.3% | 0.3% | 0.2% | 0.2% | 0.2% | — | — | — | — | — |
| 2018-02 | 100.0% | 0.3% | 0.4% | 0.3% | 0.3% | 0.2% | 0.2% | — | — | — | — | — | — |
| 2018-03 | 100.0% | 0.4% | 0.3% | 0.3% | 0.1% | 0.1% | — | — | — | — | — | — | — |
| 2018-04 | 100.0% | 0.6% | 0.3% | 0.2% | 0.1% | — | — | — | — | — | — | — | — |
| 2018-05 | 100.0% | 0.5% | 0.3% | 0.2% | — | — | — | — | — | — | — | — | — |
| 2018-06 | 100.0% | 0.4% | 0.3% | — | — | — | — | — | — | — | — | — | — |
Category and delivery
Where GMV and customer experience differ
Top 15 categories by item GMV
Largest states by delivered orders
| State | Orders | GMV | On time | Review |
|---|---|---|---|---|
| SP | 40,501 | R$5.1M | 94.1% | 4.25 |
| RJ | 12,350 | R$1.8M | 86.5% | 3.97 |
| MG | 11,354 | R$1.6M | 94.4% | 4.19 |
| RS | 5,345 | R$728.9K | 92.9% | 4.19 |
| PR | 4,923 | R$666.1K | 95.0% | 4.24 |
| SC | 3,546 | R$507.0K | 90.2% | 4.13 |
| BA | 3,256 | R$493.6K | 86.0% | 3.93 |
| DF | 2,080 | R$296.5K | 92.9% | 4.13 |
| ES | 1,995 | R$268.6K | 87.8% | 4.08 |
| GO | 1,957 | R$282.8K | 91.8% | 4.10 |
| PE | 1,593 | R$251.9K | 89.2% | 4.08 |
| CE | 1,279 | R$219.8K | 84.7% | 3.94 |
SQL review
Inspect the versioned analytical queries
00_create_tables.sql
-- Load the eight source CSVs into typed DuckDB tables.
-- {{RAW_DIR}} is replaced by build_warehouse.py with an absolute local path.
CREATE OR REPLACE TABLE raw_customers AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/olist_customers_dataset.csv',
header = true,
sample_size = -1
);
CREATE OR REPLACE TABLE raw_orders AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/olist_orders_dataset.csv',
header = true,
sample_size = -1
);
CREATE OR REPLACE TABLE raw_order_items AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/olist_order_items_dataset.csv',
header = true,
sample_size = -1
);
CREATE OR REPLACE TABLE raw_order_payments AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/olist_order_payments_dataset.csv',
header = true,
sample_size = -1
);
CREATE OR REPLACE TABLE raw_order_reviews AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/olist_order_reviews_dataset.csv',
header = true,
sample_size = -1
);
CREATE OR REPLACE TABLE raw_products AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/olist_products_dataset.csv',
header = true,
sample_size = -1
);
CREATE OR REPLACE TABLE raw_sellers AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/olist_sellers_dataset.csv',
header = true,
sample_size = -1
);
CREATE OR REPLACE TABLE raw_category_translation AS
SELECT * FROM read_csv_auto(
'{{RAW_DIR}}/product_category_name_translation.csv',
header = true,
sample_size = -1
);