Skip to content
Armand Notue

Data Science · Predictive Analytics · Business Intelligence

Full-Cycle E-Commerce Churn & CLV

An end-to-end churn and customer-value analysis of a Brazilian marketplace, from SQL engineering to an executive-ready retention plan.

Complete
RFM SEGMENTSTEMPORAL SPLIT555155343211151432111R · F · M SCORESTRAINTESTSchematic
Problem
Which customers are about to leave, how much revenue is at risk, and what should the marketing team do about it?
Approach
Built an analytical base table in SQL across nine relational tables, modeled churn with a temporal split, segmented customers with RFM, estimated CLV with BG/NBD and Gamma-Gamma, and turned the findings into five prioritized recommendations.

Key results

customers
96,096
records across 9 tables
1.55M
actionable RFM segments
8

01

Overview

A full-cycle data science project on the public Olist marketplace dataset — 96,096 customers and about 1.55M records across nine relational tables — covering SQL engineering, cleaning, EDA, modeling, segmentation, CLV estimation and executive recommendations.

02

Problem & motivation

Churn models are easy to build and easy to get wrong. The goal was to answer a business question end to end, and to be rigorous about the parts that usually go unchecked: leakage, model fit and whether a result is actually actionable.

03

My role

  • Independent project — SQL, analysis, modeling, segmentation and executive write-up.

04

System architecture

  1. Stage 1:Data

    • 9 relational tables

      ~1.55M records

  2. Stage 2:SQL

    • Analytical base table

      JOINs, CTEs and window functions

  3. Stage 3:Explore

    • 28 features · 20+ charts

      Cleaning, feature engineering, annotated EDA

  4. Stage 4:Model

    • 4 churn models

      Rebuilt on a temporal train/test split

  5. Stage 5:Segment

    • RFM segmentation

      8 actionable segments

    • CLV

      BG/NBD + Gamma-Gamma

  6. Stage 6:Act

    • 5 recommendations

      Prioritized for the marketing team

From relational tables to a prioritized retention plan.

05

Technical implementation

  1. SQL first

    The analytical base table is assembled in SQL — joins across the marketplace's relational tables, CTEs for intermediate aggregates and window functions for per-customer history.

  2. Modeling

    Four classifiers (including XGBoost and LightGBM) compared on 28 engineered features, evaluated on a temporal split so the model never sees the future.

  3. Segmentation and value

    RFM scoring groups customers into eight segments with a clear action for each; a BG/NBD + Gamma-Gamma model estimates lifetime value.

06

Engineering decisions & challenges

  • A perfect score is a bug report

    The first models scored ROC-AUC = 1.0. The cause: recency_days directly encoded the churn label. The pipeline was rebuilt with a temporal train/test split and the fix documented.

  • A correct model can still be the wrong tool

    BG/NBD correctly predicted near-zero CLV because about 98% of customers never purchase again. RFM segmentation was the more actionable framework for a single-purchase marketplace.

07

Evaluation & results

Key findings from the analysis.

churn rate83,789 of 93,358 customers in the modeling set
89.8%
repeat purchase rateThe root cause behind churn
3.0%
delivery time, active vs churnedThe leading churn driver
8 vs 13 days

What separates churned customers

Churned customers waited 62% longer for delivery and left more bad reviews — delivery is the clearest operational lever.

Active vs churned customersActiveChurned

Delivery time (days)

  • Active8 days
  • Churned13 days

Bad-review rate

  • Active9.8%
  • Churned13.4%

Average review score (of 5)

  • Active4.30
  • Churned4.11
Show data table
MetricActiveChurned
Delivery time (days)8 days13 days
Bad-review rate9.8%13.4%
Average review score (of 5)4.304.11

Source: Executive dashboard figure and repository README

  • Revenue figures in the notebook are historical totals or projections. The five recommendations carry a projected recovery of R$1M+ — a modeled estimate, not a realized result.

08

From the notebook

Real figures from the project's notebook, as published in the repository. Select one to enlarge it.

09

Lessons learned

  1. Distrust perfect metrics

    Leakage hides in features that look reasonable. A temporal split is the default for anything that predicts the future.

  2. Actionability is part of correctness

    Choosing the framework the business can act on is as important as choosing the most sophisticated one.

10

Technologies used

  • Python
  • SQL
  • pandas
  • scikit-learn
  • XGBoost
  • LightGBM
  • Lifetimes
  • Plotly

Want the deeper walkthrough?

I'm happy to go through the code, the trade-offs and what I'd do next.

Get in touch