← 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

R$41.7KR$549.8KR$1.1MJan 2017Oct 2017Aug 2018
Item GMV (BRL)

Delivery experience

StatusOrdersAvg daysReviewLow review
On time88,16310.84.299.2%
Late7,66131.32.5754.0%

Data quality

7 of 8 checks marked PASS

CheckIssuesStatus
delivered_missing_delivery_date8REVIEW
duplicate_customer_ids0PASS
duplicate_order_ids0PASS
duplicate_order_item_keys0PASS
nonpositive_item_price0PASS
orphan_customer_orders0PASS
orphan_order_items0PASS
orphan_payments0PASS
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.

CohortM0M1M2M3M4M5M6M7M8M9M10M11M12
2017-01100.0%0.3%0.3%0.1%0.4%0.1%0.4%0.1%0.1%—0.4%0.1%0.7%
2017-02100.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-03100.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-04100.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-05100.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-06100.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-07100.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-08100.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-09100.0%0.7%0.6%0.3%0.5%0.2%0.2%0.3%0.3%0.2%0.3%0.1%—
2017-10100.0%0.7%0.3%0.1%0.2%0.2%0.2%0.4%0.3%0.2%0.2%——
2017-11100.0%0.6%0.4%0.2%0.2%0.2%0.1%0.2%0.1%0.1%———
2017-12100.0%0.2%0.3%0.3%0.3%0.2%0.2%0.0%0.2%————
2018-01100.0%0.3%0.4%0.3%0.3%0.2%0.2%0.2%—————
2018-02100.0%0.3%0.4%0.3%0.3%0.2%0.2%——————
2018-03100.0%0.4%0.3%0.3%0.1%0.1%———————
2018-04100.0%0.6%0.3%0.2%0.1%————————
2018-05100.0%0.5%0.3%0.2%—————————
2018-06100.0%0.4%0.3%——————————
Category and delivery

Where GMV and customer experience differ

Top 15 categories by item GMV

health beautyR$1.2M
8,647 orders · 4.23 review
watches giftsR$1.2M
5,495 orders · 4.12 review
bed bath tableR$1.0M
9,272 orders · 4.00 review
sports leisureR$954.9K
7,530 orders · 4.23 review
computers accessoriesR$888.7K
6,530 orders · 4.08 review
furniture decorR$711.9K
6,307 orders · 4.06 review
housewaresR$615.6K
5,743 orders · 4.19 review
cool stuffR$610.2K
3,559 orders · 4.22 review
autoR$579.0K
3,810 orders · 4.15 review
toysR$471.3K
3,804 orders · 4.24 review
garden toolsR$470.5K
3,448 orders · 4.18 review
babyR$400.4K
2,809 orders · 4.10 review
perfumeryR$390.1K
3,086 orders · 4.26 review
telephonyR$309.9K
4,093 orders · 4.05 review
office furnitureR$268.2K
1,254 orders · 3.64 review

Largest states by delivered orders

StateOrdersGMVOn timeReview
SP40,501R$5.1M94.1%4.25
RJ12,350R$1.8M86.5%3.97
MG11,354R$1.6M94.4%4.19
RS5,345R$728.9K92.9%4.19
PR4,923R$666.1K95.0%4.24
SC3,546R$507.0K90.2%4.13
BA3,256R$493.6K86.0%3.93
DF2,080R$296.5K92.9%4.13
ES1,995R$268.6K87.8%4.08
GO1,957R$282.8K91.8%4.10
PE1,593R$251.9K89.2%4.08
CE1,279R$219.8K84.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
);