- Project type
- Portfolio project
- Dataset
- Synthetic dataset
- Dataset provenance
- A synthetic Shopify-format order dataset (October 2023 to December 2024) generated with the project's own Python script.
- Client status
- Demonstration analysis; does not represent a PA Data Analytics client.
View the data behind this chart
| Cohort | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 | M13 | M14 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2023-10 | 39% | 22.9% | 18.5% | 17.1% | 15.1% | 10.2% | 14.6% | 10.7% | 12.2% | 10.2% | 7.3% | 8.3% | 7.8% | 5.4% |
| 2023-11 | 40.5% | 20.3% | 21.2% | 16.2% | 12.6% | 12.2% | 11.3% | 9.9% | 10.4% | 10.4% | 6.3% | 9% | 8.6% | – |
| 2023-12 | 43.1% | 23.9% | 22.3% | 12.2% | 16% | 9% | 13.8% | 9.6% | 9.6% | 7.4% | 9% | 9.6% | – | – |
| 2024-01 | 35.2% | 20% | 20.5% | 15.2% | 14.8% | 9% | 9.5% | 9% | 12.4% | 10.5% | 10.5% | – | – | – |
| 2024-02 | 39.1% | 25.5% | 21.1% | 17.4% | 15.5% | 9.9% | 12.4% | 8.1% | 6.2% | 7.5% | – | – | – | – |
| 2024-03 | 33.9% | 22.4% | 18.8% | 10.4% | 9.4% | 13.5% | 10.9% | 8.3% | 10.4% | – | – | – | – | – |
| 2024-04 | 34.1% | 22.5% | 17.6% | 13.7% | 15.4% | 9.9% | 12.1% | 10.4% | – | – | – | – | – | – |
| 2024-05 | 36.8% | 20.3% | 13.4% | 11.7% | 13% | 10.4% | 6.5% | – | – | – | – | – | – | – |
| 2024-06 | 37.8% | 27.9% | 16.9% | 12.9% | 9.5% | 16.9% | – | – | – | – | – | – | – | – |
| 2024-07 | 38.8% | 27% | 19.4% | 15.8% | 13.8% | – | – | – | – | – | – | – | – | – |
| 2024-08 | 39.5% | 20.6% | 14.9% | 15.8% | – | – | – | – | – | – | – | – | – | – |
| 2024-09 | 38% | 25.9% | 20% | – | – | – | – | – | – | – | – | – | – | – |
| 2024-10 | 38.2% | 21.3% | – | – | – | – | – | – | – | – | – | – | – | – |
| 2024-11 | 36.9% | – | – | – | – | – | – | – | – | – | – | – | – | – |
| 2024-12 | – | – | – | – | – | – | – | – | – | – | – | – | – | – |
01Business question
Most online stores know that many customers never come back, but not when the drop-off happens, which acquisition months produce the most loyal customers, or how revenue builds up across customer groups.
02Dataset & context
Dataset: Synthetic / Demonstration Dataset. This portfolio analysis uses a synthetic order dataset generated in Shopify's order-export format: 6,404 orders from 2,810 customers between October 2023 and December 2024, with $401,672 in recorded revenue. It does not represent a PA Data Analytics client. Because it follows the Shopify export format, the same pipeline can be run on a real Shopify orders export.
03Approach
Group customers by the month of their first purchase, then track what share of each cohort returns in each later month and how much returning customers spend.
04Methodology
- A SQL pipeline of views that assigns each customer to an acquisition cohort, calculates months since first purchase, counts active customers per cohort month and calculates retention rates.
- Written in standard SQL (run in SQLite) so it can be adapted to warehouses such as BigQuery or PostgreSQL with minor dialect changes.
- Retention heatmaps from month 0 to month 14, retention curves, and revenue per active customer by cohort month.
05Modelling & analysis
Dataset findings: average retention was 37.9% at month 1, 23.1% at month 2, 18.7% at month 3, 11.2% at month 6 and 9.0% at month 12. The largest loss happens between the first purchase and month 1, after which the curve flattens.
The October 2023 cohort, the longest-running in the dataset, shows the pattern clearly: 205 customers at acquisition, 80 in month 1, 21 in month 6 and 17 in month 12. Customers who kept buying tended to spend more per order over time.
06Results within the dataset
In this dataset, most customer loss happens in the first month after the first purchase, so that is where retention effort would have the greatest leverage. These are findings from synthetic data, not results achieved by a business.
07What this analysis demonstrates
How cohort analysis shows the timing of customer loss, which a single overall retention rate hides, and how a reusable SQL pipeline can be refreshed each month to track whether retention is improving.
08How a business could use this analysis
- Focus retention effort on the first 30 days after a first purchase, where losses are usually largest.
- Compare acquisition months and channels to see which bring the most loyal customers.
- Refresh the cohort pipeline monthly to measure the effect of changes.
09Related service
This project demonstrates methods used in our customer intelligence services.
Related insight: A Beginner's Guide to RFM Segmentation in SQL
10Apply this to your data
If you are facing a similar question, we can look at what your data can support and what a useful answer would look like.