Group
Assign first-observed purchase cohorts with SQL CTEs.
↗← ALL WORKThe first sale is
INDEPENDENT PROJECT / SQL COHORTS + PANDAS / 2026 / 06
The first sale is
only the beginning.
A cohort view that keeps customer definitions and observation windows honest.
EXPLORE THE STORY ↓23.65%weighted month-one retention
Only cohorts with a complete next month.
01
THE BUSINESS PROBLEM
Who returns after their first observed purchase?
A next-month return and a repeat purchase somewhere in the year answer different questions.
23.65%return in the next month
Mature cohorts only.
64.21%repeat within the analysis window
Identified customers with more than one eligible invoice.
02From question
WHAT I DID
From question
to useful work.
Measure
Track monthly activity and leave future cells unobserved.
↗Validate
Rebuild in pandas and reconcile every cohort–age cell.
↗03A retention baseline
THE RESULTS
A retention baseline
you can test against.
Every SQL and pandas cohort count matches. The heatmap exposes timing and maturity instead of hiding them in one headline.
| Cohort | M0 | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2010-12 | 100 percent | 37 percent | 32 percent | 38 percent | 36 percent | 40 percent | 36 percent | 35 percent | 35 percent | 40 percent | 37 percent | 50 percent |
| 2011-01 | 100 percent | 22 percent | 27 percent | 23 percent | 32 percent | 29 percent | 25 percent | 24 percent | 30 percent | 33 percent | 36 percent | — |
| 2011-02 | 100 percent | 19 percent | 19 percent | 28 percent | 27 percent | 25 percent | 25 percent | 28 percent | 25 percent | 31 percent | — | — |
| 2011-03 | 100 percent | 15 percent | 25 percent | 20 percent | 22 percent | 17 percent | 27 percent | 23 percent | 28 percent | — | — | — |
| 2011-04 | 100 percent | 21 percent | 20 percent | 21 percent | 20 percent | 23 percent | 22 percent | 26 percent | — | — | — | — |
| 2011-05 | 100 percent | 19 percent | 17 percent | 17 percent | 21 percent | 23 percent | 26 percent | — | — | — | — | — |
| 2011-06 | 100 percent | 17 percent | 16 percent | 26 percent | 23 percent | 33 percent | — | — | — | — | — | — |
| 2011-07 | 100 percent | 18 percent | 21 percent | 22 percent | 27 percent | — | — | — | — | — | — | — |
| 2011-08 | 100 percent | 21 percent | 25 percent | 24 percent | — | — | — | — | — | — | — | — |
| 2011-09 | 100 percent | 23 percent | 30 percent | — | — | — | — | — | — | — | — | — |
| 2011-10 | 100 percent | 24 percent | — | — | — | — | — | — | — | — | — | — |
| 2011-11 | 100 percent | — | — | — | — | — | — | — | — | — | — | — |
Percentages rounded for display. M0 = first observed purchase month. Blank future months are unobserved, not zero. Scroll the table on mobile.
THE BUSINESS TAKEAWAY
Use mature cohorts to design a first-to-second purchase experiment. Measure incremental lift with a control group.
↗THE EVIDENCE / GO DEEPER
LET’S DISCUSS YOUR PROJECT ↗Methods, context & full project notes ＋
The business question
After a first observed purchase, how many customers return in the next month — and how does that differ from buying more than once over a full year?
The method
Created first-observed-purchase cohorts with SQL CTEs and monthly customer activity. Independently rebuilt the calculation in pandas, then reconciled every cohort/age cell. Unobserved future months remain blank rather than being treated as zero retention.
The result
Weighted month-one retention is 23.65% for cohorts with a complete next month. Across the full analysis window, 64.21% of identified customers have more than one eligible invoice. These answer different business questions; presenting them as interchangeable would be misleading.
A decision this can support
Use mature cohort trends to set a baseline for a first-to-second purchase experiment. Test whether reminders or onboarding help eligible first-time customers return, and evaluate incremental lift with a control group before claiming success.
Limits of the evidence.
“First observed” is not necessarily first-ever purchase. Earlier customer history is unavailable, so the first cohort is left-censored. Unidentified customers are excluded from retention, and the result cannot be generalised to them. The analysis is observational and does not measure a campaign effect.
Validation
SQL and pandas cohort counts match. Month-zero retention equals 100% for every cohort. All observed rates stay between 0% and 100%. The monthly trading pipeline also reconciles to the underlying line totals.
Data: Daqing Chen, Online Retail (2015), UCI. DOI 10.24432/C5BW33 · CC BY 4.0. Independent analysis of historical public data; recommendations have not been tested in a live business.
Istanbul shopping ↗
YOUR NEXT MOVE