Cohort retention, order funnel, RFM segmentation and category demand on 100k+ real Brazilian marketplace orders — every analytic question answered in pure SQL, with a one-command reproducible PostgreSQL pipeline.
TL;DR — the honest headline. (1) Repeat purchasing is rare: a mature cohort (2017-06, 3,037 first-time buyers) retains only 0.5% in month 1 and stays in the low single digits — the platform grows by acquiring new customers, not by bringing them back. (2) The funnel loses almost nothing at approval (99,441 kept down to 99,281 = 99.84%), and the biggest visible loss is at delivery completion: only 96,476 of 99,441 placed orders reach a delivery timestamp, while reviews (98,673) even outnumber delivered orders because reviews exist for non-delivered orders too (incl. 599 canceled). (3) Revenue is concentrated: one RFM segment ("Need Attention", 39,636 customers) generates 8,009,393.97 of the 15,419,773.75 delivered-only revenue (42.46%). Every number below is copied from a committed
results/*.csv— see Data sources.
- TL;DR
- Introduction
- Tech Stack
- Project Structure
- Quick Start
- Reproducibility
- Data
- Methodology
- Results
- Limitations
- Recommendations
- Support
- Contributing
- License
- Acknowledgements
This project simulates a day in the life of a marketplace analyst: given the Brazilian E-Commerce Public Dataset by Olist (Kaggle, ~100k orders, 9 tables), how loyal are the customers, where does the order journey break, who drives the revenue, and what do regions buy? Four questions, four SQL queries, no machine learning and no analytical code outside PostgreSQL: Python only renders the charts from committed query results.
Everything follows the data-analysis golden standards used in the project's
learning notes (notes/): data integrity is verified before any conclusion,
every number in this README is traceable to a committed results/*.csv, the
verdicts are as honest as the data — including the "boring" ones — and the
whole pipeline is reproducible with one command.
Main contributions:
- 4 analytical queries + 1 integrity check + 1 EDA query using the full
modern-SQL toolbox: common table expressions (CTEs), window functions
(
LAG,NTILE,RANK,PARTITION BY),COUNT(*) FILTER, andpercentile_contfor robust medians. - Right-censoring and data quirks handled explicitly — recent cohorts see fewer months, "delivered by status" differs from "delivered by timestamp", and the funnel is intentionally not forced to be monotonic.
- A deterministic, one-command pipeline:
make allbrings up PostgreSQL in Docker, runs every query, and renders every chart without randomness. - Honest business reading of the results — including revenue concentration and a retention story that is not a success story.
An analyst-task-to-artifact map:
| Task | Where it lives |
|---|---|
| Data integrity (dupes, nulls, ranges, status mix) | queries/00_data_integrity.sql, results/00_data_integrity.csv |
| Cohort retention matrix | queries/01_cohort_retention.sql, results/01_cohort_retention.csv |
| Order funnel + median durations | queries/02_order_funnel.sql, results/02_order_funnel.csv |
| RFM customer segmentation | queries/03_rfm_segmentation.sql, results/03_rfm_segmentation.csv |
| Top-10 categories per state | queries/04_top_categories_by_state.sql, results/04_top_categories_by_state.csv |
| Monthly order volume (EDA) | queries/05_orders_over_time.sql, results/05_orders_over_time.csv |
| Charts for this README | scripts/make_charts.py, assets/*.png |
| Reasoning per stage (RU) | notes/01..07 |
| Category | Technologies |
|---|---|
| Database | PostgreSQL 16 (postgres:16-alpine, Docker Compose) |
| Query language | SQL — window functions, CTEs, FILTER, percentile_cont, date_trunc |
| Orchestration | Make (make db / make queries / make charts / make all) |
| Scripting | Bash (scripts/run_queries.sh streams psql output to results/) |
| Charts | Python, matplotlib, pandas — pinned in requirements.txt |
.
├── db/
│ ├── docker-compose.yml # postgres:16-alpine, CSV mount, healthcheck
│ └── schema.sql # DDL for 9 tables + one-shot COPY from data/
├── queries/ # 00 integrity, 01 retention, 02 funnel,
│ # 03 RFM, 04 categories, 05 orders over time
├── scripts/
│ ├── run_queries.sh # each .sql -> results/<name>.csv (psql in container)
│ └── make_charts.py # matplotlib: reads results/, writes assets/
├── results/ # committed CSV exports — every README number lives here
├── assets/ # 5 PNG charts referenced by this README
├── notes/ # stage-by-stage learning notes (Russian)
├── data/ # raw Kaggle CSVs (NOT committed) + data/README.md
├── Makefile
├── requirements.txt # pinned chart-rendering environment
├── README.md # this file (English, source of truth)
└── README_ru.md # full Russian translation, synced numbers
Prerequisites: Docker with Compose support, and the 9 Olist CSVs placed in
data/ with the original Kaggle filenames (see data/README.md).
Option A — everything with one command:
make all # db + queries + chartsmake all is three targets chained:
make db # docker compose up -d --wait, loads CSVs on first init
make queries # run all 6 .sql files -> results/*.csv
make charts # .venv/bin/python scripts/make_charts.py -> assets/*.pngOption B — manual, step by step:
docker compose -f db/docker-compose.yml up -d --wait
bash scripts/run_queries.sh queries/00_data_integrity.sql
bash scripts/run_queries.sh queries/01_cohort_retention.sql
bash scripts/run_queries.sh queries/02_order_funnel.sql
bash scripts/run_queries.sh queries/03_rfm_segmentation.sql
bash scripts/run_queries.sh queries/04_top_categories_by_state.sql
bash scripts/run_queries.sh queries/05_orders_over_time.sql
.venv/bin/python scripts/make_charts.pypsql runs inside the container (docker compose exec), so you need no
PostgreSQL client on the host. Inspect results directly:
docker compose -f db/docker-compose.yml exec -T postgres \
psql -U olist -d olist -c "SELECT * FROM olist_orders LIMIT 5;"- One command reproduces the whole report:
make all. - Deterministic SQL: no randomness anywhere in the pipeline; window
functions are pinned with explicit tie-breakers (
customer_unique_id) so bucket membership cannot shift between runs or query plans. - Pinned environment:
requirements.txtfixes matplotlib, pandas and psycopg exactly as resolved on 2026-09-24. - Results are committed and byte-identical on re-run — this was verified:
re-running
make queriesleavesgit diff --stat results/empty, and re-runningscripts/make_charts.pyreproduces identical PNGs. - Every README number is a copy of a committed CSV cell, so the report cannot drift from what the queries actually produce.
Brazilian E-Commerce Public Dataset by Olist (Kaggle). ~100k orders from 2016-09-04 to 2018-10-17 across 9 tables covering orders, customers, items, payments, reviews, sellers, products, category translations and geolocation.
Unit of analysis nuance: the customer table holds 99,441 order-scoped
customer_id rows but only 96,096 distinct customer_unique_id (real
people). Of those, 93,358 have at least one delivered order; the other
2,738 never received a delivered order and are excluded from retention,
RFM and revenue by design — those are real business segments, not query losses.
Table row counts (results/00_data_integrity.csv):
| Table | Rows |
|---|---|
| olist_orders | 99,441 |
| olist_customers | 99,441 (96,096 unique people) |
| olist_order_items | 112,650 |
| olist_order_payments | 103,886 |
| olist_order_reviews | 99,224 |
| olist_products | 32,951 |
| olist_sellers | 3,095 |
| olist_geolocation | 1,000,163 |
| product_category_name_translation | 71 |
Status mix (results/00_data_integrity.csv): 96,478 delivered (97.0%),
1,107 shipped, 625 canceled, 609 unavailable, 314 invoiced, 301 processing,
5 created, 2 approved — 99,441 in total. Key integrity facts verified before
analysis: no duplicates on any business key, price range 0.85–6,735.00,
freight 0.00–409.68, review scores within 1–5, and 2,965 orders without a
delivery timestamp (see Limitations).
Delivered-only convention: revenue, retention "proof of life" and RFM count
only order_status = 'delivered' (96,478 orders). The funnel converts on
timestamps instead — 96,476 orders carry a delivery timestamp — and the
2-row difference between the two definitions is documented, not papered over.
Core schema (analysis-relevant subset):
erDiagram
olist_orders ||--o| olist_customers : places
olist_orders ||--o{ olist_order_items : contains
olist_orders ||--o{ olist_order_payments : paid_with
olist_orders ||--o{ olist_order_reviews : reviewed_by
olist_order_items }o--|| olist_products : "product_id"
olist_products }o--o| product_category_name_translation : category
Each query keeps its reasoning in its header comment; the excerpts below are the load-bearing fragments.
A cohort = calendar month of a customer's first delivered order. A customer is active in a later month if they have at least one delivered order in it. Retention for a cohort at a given age = share of the cohort's customers active in that relative month. Month 0 is 100% by construction — a useful self-check of the whole query chain.
-- relative month: calendar months between first purchase and later activity
(date_part('year', ca.activity_month) - date_part('year', fo.cohort_month)) * 12
+ (date_part('month', ca.activity_month) - date_part('month', fo.cohort_month))
AS relative_monthretention_pct = round(100.0 * count(DISTINCT ca.customer_unique_id) / cs.size, 1)The unit is customer_unique_id, not customer_id — customer_id is
assigned per order, so the same returning person would otherwise be counted as
a new customer and inflate cohort sizes. Only delivered orders count as
activity ("proof of life"): a canceled order means the customer received
nothing.
All 99,441 orders enter — a funnel measures loss, and loss only means
something against the full population. Stages are proven by a single column,
counted in one pass with COUNT(*) FILTER:
SELECT 'placed' AS stage, 1 AS stage_order,
count(*) FILTER (WHERE at_placed) AS n_orders FROM funnel_steps
UNION ALL
SELECT 'approved', 2,
count(*) FILTER (WHERE at_approved) AS n_orders FROM funnel_steps
UNION ALL
SELECT 'handed_to_carrier', 3,
count(*) FILTER (WHERE at_carrier) AS n_orders FROM funnel_stepsStep-to-step conversion uses LAG (a window function) instead of a self-join:
round(100.0 * n_orders / lag(n_orders) OVER (ORDER BY stage_order), 2)
AS conv_from_prev_pctMedian durations between approval, carrier hand-off and delivery use
percentile_cont(0.5) (robust to skewed durations), converted to hours via
EXTRACT(EPOCH FROM ...).
One row per customer with at least one delivered order. Recency = days from
the customer's last purchase to the reference date (the dataset's global
MAX(order_purchase_timestamp), 2018-10-17); frequency = distinct delivered
orders; monetary = SUM(price + freight_value) over delivered orders. Each
axis is cut into 5 equal-population buckets:
NTILE(5) OVER (ORDER BY recency_days DESC, customer_unique_id) AS r_score,
NTILE(5) OVER (ORDER BY frequency DESC, customer_unique_id) AS f_score,
NTILE(5) OVER (ORDER BY monetary DESC, customer_unique_id) AS m_scoreDESC means quintile 5 = best. The customer_unique_id tie-breaker pins the
bucket membership deterministically (see Limitations). A
CASE in strict order maps the three ranks to business labels (Champions,
At Risk, Lost, ...), with a catch-all ELSE so no customer is dropped.
Revenue = SUM(price + freight_value) over delivered orders per
(customer state, English category), joined through the translation table with
COALESCE(..., 'unknown') so no revenue silently disappears. States vs
categories ranked inside each state:
RANK() OVER (PARTITION BY r.state ORDER BY r.revenue DESC) AS rank_in_statecustomer_state answers demand ("what does the region buy"); the same items
table could answer supply via seller_state — here the demand view is the
question.
Every figure below is copied directly from a committed CSV, which is also the
re-usable output of make queries. Chart-to-CSV mapping:
| Figure | Data source |
|---|---|
assets/retention_heatmap.png |
results/01_cohort_retention.csv |
assets/order_funnel.png |
results/02_order_funnel.csv |
assets/rfm_segments.png |
results/03_rfm_segmentation.csv |
assets/top_categories_by_state.png |
results/04_top_categories_by_state.csv |
assets/orders_over_time.png |
results/05_orders_over_time.csv |
23 monthly cohorts from 2016-09 to 2018-08, 93,358 first-time buyers in total; the largest cohort is 2017-11 (7,060). Retention collapses after the first month and never recovers:
Cohort 2017-06 (3,037 customers), ages 1–10 months (results/01_cohort_retention.csv):
| Month after first purchase | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
|---|---|---|---|---|---|---|---|---|---|---|
| Retention, % | 0.5 | 0.4 | 0.4 | 0.3 | 0.4 | 0.4 | 0.2 | 0.1 | 0.2 | 0.3 |
Month 0 is 100.0 by construction. The message is consistent across cohorts: month-1 retention sits around 0.5% (e.g. 2017-05 = 0.5, 2017-07 = 0.5, 2017-08 = 0.7) and later months hover between 0.1% and 0.4%. The ragged right edge of the heatmap is right-censoring, not a dip to zero — recent cohorts simply have not lived long enough yet.
Reading: the marketplace keeps almost nobody. Revenue growth is driven by acquiring first-time buyers, not by retention — a fragile growth model.
The lifecycle of all 99,441 orders, five stages plus two median durations
(results/02_order_funnel.csv):
| Stage | n_orders | vs previous | vs placed |
|---|---|---|---|
| placed | 99,441 | — | 100.00% |
| approved | 99,281 | 99.84% | 99.84% |
| handed_to_carrier | 97,658 | 98.37% | 98.21% |
| delivered_to_customer | 96,476 | 98.79% | 97.02% |
| reviewed | 98,673 | 102.28% | 99.23% |
Median durations: approval to carrier hand-off 43.58 h; carrier to delivery 170.39 h (~7 days).
Two honest observations. First, the biggest visible loss is at delivery
completion, not at approval: only 160 orders fail to be approved (99.84%
survive), while by delivery 2,965 of the placed orders have no delivery
timestamp (619 of them canceled). Second, reviewed (98,673) exceeds
delivered (96,476): reviews exist for orders without a delivery timestamp,
so the funnel step is a feedback signal, not a phase of the delivery chain —
that is a fact, not a bug, and the chart keeps it visible rather than
"fixing" the order.
93,358 customers split into 7 business segments by recency/frequency/monetary
quintiles (results/03_rfm_segmentation.csv):
| Segment | mode (r,f,m) | Customers | Share | Revenue | Avg check |
|---|---|---|---|---|---|
| Need Attention | 2,1,1 | 39,636 | 42.46% | 8,009,393.97 | 202.07 |
| At Risk | 1,5,4 | 22,356 | 23.95% | 3,598,943.14 | 160.98 |
| Lost | 1,1,1 | 7,475 | 8.01% | 1,252,585.56 | 167.57 |
| New Customers | 5,1,1 | 3,468 | 3.71% | 1,118,073.85 | 322.40 |
| Potential Loyalists | 5,2,3 | 8,503 | 9.11% | 627,163.61 | 73.76 |
| Loyal Customers | 5,3,5 | 6,012 | 6.44% | 490,686.71 | 81.62 |
| Champions | 5,4,5 | 5,908 | 6.33% | 322,926.91 | 54.66 |
Delivered-only revenue totals 15,419,773.75; customer shares sum to 93,358 (the 100.01% in the CSV is rounding in the last decimal).
Reading: revenue is double-concentrated — 42.46% comes from a single "Need Attention" segment (big-bucket mid-recency customers), and 23.95% sits in "At Risk" (once-frequent customers who stopped buying, avg check 160.98). Champions, the textbook VIP target, are the smallest segment by revenue (322,926.91) — highly recency-score-heavy, but few spend weeks apart. New Customers carry the highest average check (322.40) — high-intent traffic that the marketplace currently fails to convert into repurchase (see retention).
Top-10 revenue categories per state, 27 states x 10 = 270 rows
(results/04_top_categories_by_state.csv). The chart shows the five largest
states by revenue (SP, RJ, MG, RS, PR):
| State | #1 category | Rank-1 revenue | Share of state | State total revenue |
|---|---|---|---|---|
| SP | bed_bath_table | 549,061.89 | 9.52% | 5,769,703.15 |
| RJ | watches_gifts | 188,264.84 | 9.16% | 2,055,401.57 |
| MG | health_beauty | 175,007.52 | 9.62% | 1,818,891.67 |
| RS | bed_bath_table | 72,428.18 | 8.41% | 861,472.79 |
| PR | sports_leisure | 66,641.83 | 8.53% | 781,708.80 |
Reading: no state depends on a single category — the #1 category is ~8-10%
of state revenue everywhere. São Paulo alone accounts for 5,769,703.15 of
delivered revenue (37% of the 15,419,773.75 total) and leads with
bed_bath_table (549,061.89). In the category 'unknown' (no translatable
Portuguese name) appears only in the small states PA (rank 7, 10,338.50) and
RR (rank 9, 301.82) — 10,640.32 visible in the top-10 output.
Monthly delivered order volume, 23 months (results/05_orders_over_time.csv),
summing to 96,478 — exactly the delivered-by-status count:
Volume grows through 2017 to a peak of 7,289 in 2017-11, then fluctuates around 6-7k until the series ends at 6,351 in 2018-08. The series stops there because no order bought in 2018-09/10 was ever delivered — the last delivered purchase was made 2018-08-29 — so the tail is censored history, not a crash.
- Right-censoring. The snapshot ends on 2018-10-17 and the last delivered purchase was made 2018-08-29. Recent cohorts and the order-volume series see fewer months than old ones; an empty recent cell means "history has not caught up", not "zero".
- Delivered-only scope. Revenue, RFM and retention count only
order_status = 'delivered': the 2,965 orders without a delivery timestamp and all 625 non-delivered canceled orders are excluded from money figures — the funnel measures them, but the money does not. - NTILE tie-splitting roughness.
NTILEsplits rows, not values: tied recency/frequency/monetary values can fall on different sides of a quintile boundary. The query pins membership with a deterministic tie-breaker (customer_unique_id), but the boundary roughness itself is inherent to the method. - Single market, single time window. One country, one marketplace, 25 months (2016-09..2018-10). The numbers describe Olist in that window only; they are not generalizable to other markets or to today.
- 'unknown' product categories. 1.32% of delivered revenue (203,353.84) carries no translatable category name; within the top-10 output it is only visible for PA and RR (10,640.32 combined). Revenue itself is never dropped — only its category label is missing.
- Status vs timestamp. "Delivered" by status is 96,478 orders, by delivery timestamp 96,476 — an 8-row and 6-row edge in each direction. All figures state which definition they use.
Data-grounded next steps, in order of expected impact:
- Treat growth as acquisition-led and act on repeat purchase. With ~0.5% month-1 retention, the number-one lever is any program that brings a customer back in month 1-3 (post-delivery offers, preferred-freight membership). Re-measure with the same cohort matrix — it is cheap to re-run on fresh data.
- Investigate the delivery-completion gap. 2,965 of 99,441 placed orders never get a delivery timestamp (619 canceled) and the carrier-to-delivery median is ~7 days (170.39 h). Carrier SLAs and delivery-status tracking are the most actionable funnel fix; the approval stage is already healthy (99.84%).
- Target the RFM segments by money, not by name. "Need Attention" holds 42.46% of revenue; "At Risk" (22,356 customers, 3,598,943.14) is the cheapest win-back target. A Champions VIP program is a loyalty play, not a revenue play on this data (322,926.91).
- Merchandise regionally. Category leadership varies by state (bed_bath_table leads SP and RS, health_beauty leads MG and RN, sports_leisure leads PR) — the 270-row CSV is a ready-made regional campaign planner.
- Fix the data, then refill the funnel numbers. Enrich the ~1.32% 'unknown' category revenue and close delivery-timestamp gaps; both would let the same queries answer with cleaner denominators.
- Found a bug or have a question: open a GitHub issue.
- Discussion about methodology: GitHub Discussions.
Contributions are welcome. Please open an issue first to discuss the intended
change, keep queries/*.sql deterministic (no random()), and re-run
make all before submitting a pull request so results/ stays byte-for-byte
reproducible.
MIT — Copyright (c) 2026 dimassaa.
- Dataset: Brazilian E-Commerce Public Dataset by Olist on Kaggle.
- Analysis and chart layout follow the data-analysis quality rules documented
in the project's learning notes (
notes/, Russian): minimal, question-driven visualization, honest scales, reproducible numbers.