Who Comes Back?
Customer Retention in dbt
An online clothing store wants more repeat buyers, and the obvious first question is which customers are worth chasing. I built a tested dbt pipeline on BigQuery to answer it: how many customers return after their first order, and whether where they came from or what they bought first predicts it.
61,438
Customers modeled
6%
Reorder within 90 days
85%
First-year revenue from the first order
Every cohort, month by month
Each row is the group of customers whose first order fell in that quarter. Each column is a month after that first order, and a brighter cell means more of them ordered again. Two things stand out: most cells are dark (few customers ever return), and the rows brighten toward the bottom (recent customers return more often). The findings below dig into both.
Hover or tap a cell for its value
View data
| First order quarter | Customers | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2020 Q1 | 430 | 0.93% | 0.93% | 0.93% | 1.4% | 0.23% | 0.93% | 0.7% | 0.93% | 0.7% | 0.93% | 1.4% | 0.93% |
| 2020 Q2 | 521 | 0.58% | 0.77% | 0.77% | 0.96% | 0.96% | 1.34% | 1.34% | 0.96% | 0.38% | 0.58% | 1.54% | 0.38% |
| 2020 Q3 | 659 | 1.21% | 1.67% | 1.37% | 0.76% | 0.76% | 1.06% | 0.76% | 1.06% | 1.06% | 0.76% | 1.52% | 1.06% |
| 2020 Q4 | 731 | 0.82% | 1.23% | 0.82% | 0.68% | 0.96% | 1.09% | 1.23% | 0.55% | 1.37% | 1.64% | 0.82% | 1.37% |
| 2021 Q1 | 796 | 1.26% | 1.13% | 0.38% | 1.01% | 0.75% | 0.38% | 0.63% | 0.75% | 1.51% | 0.88% | 0.75% | 0.88% |
| 2021 Q2 | 977 | 0.92% | 0.92% | 1.13% | 1.33% | 1.02% | 1.13% | 0.82% | 1.13% | 1.02% | 0.72% | 1.02% | 0.92% |
| 2021 Q3 | 1,090 | 1.1% | 0.92% | 0.55% | 0.73% | 1.38% | 0.73% | 1.47% | 0.28% | 1.01% | 1.28% | 0.92% | 0.92% |
| 2021 Q4 | 1,207 | 0.75% | 0.91% | 1.08% | 0.75% | 0.91% | 1.16% | 1.08% | 0.99% | 1.24% | 1.08% | 1.16% | 1.08% |
| 2022 Q1 | 1,281 | 1.01% | 1.09% | 1.48% | 1.09% | 0.62% | 1.48% | 0.86% | 0.78% | 0.62% | 1.01% | 0.7% | 0.7% |
| 2022 Q2 | 1,423 | 0.77% | 1.12% | 1.48% | 1.26% | 1.05% | 1.26% | 0.84% | 1.12% | 1.19% | 0.77% | 1.12% | 0.84% |
| 2022 Q3 | 1,569 | 0.89% | 0.7% | 1.4% | 1.27% | 0.89% | 1.15% | 1.34% | 1.15% | 1.27% | 1.02% | 1.21% | 1.21% |
| 2022 Q4 | 1,725 | 1.04% | 1.33% | 0.93% | 1.16% | 1.8% | 1.45% | 1.68% | 0.99% | 1.04% | 1.1% | 0.93% | 1.16% |
| 2023 Q1 | 1,794 | 1.17% | 1% | 0.95% | 1.17% | 0.95% | 1.11% | 1.62% | 1.11% | 1% | 0.95% | 1.06% | 1.51% |
| 2023 Q2 | 1,907 | 0.73% | 1% | 1.36% | 0.68% | 1.05% | 1.36% | 1.52% | 1.21% | 1.21% | 1.26% | 1.21% | 0.58% |
| 2023 Q3 | 2,088 | 1.68% | 1.15% | 1.29% | 2.01% | 1.39% | 1.39% | 1.68% | 0.86% | 1.39% | 0.81% | 1.44% | 1.68% |
| 2023 Q4 | 2,237 | 1.21% | 1.07% | 1.52% | 1.52% | 1.03% | 1.12% | 1.03% | 1.34% | 1.39% | 1.43% | 1.07% | 1.3% |
| 2024 Q1 | 2,458 | 1.59% | 1.42% | 1.51% | 1.67% | 1.59% | 1.42% | 1.75% | 1.79% | 1.59% | 1.51% | 1.63% | 1.51% |
| 2024 Q2 | 2,512 | 1.71% | 1.55% | 1.11% | 1.87% | 1.71% | 1.55% | 1.75% | 1.67% | 1.91% | 2.23% | 1.59% | 1.23% |
| 2024 Q3 | 2,774 | 2.16% | 1.77% | 1.59% | 1.41% | 1.33% | 1.62% | 1.66% | 1.44% | 1.41% | 1.66% | 1.66% | 1.95% |
| 2024 Q4 | 2,981 | 1.95% | 1.85% | 1.61% | 2.18% | 2.08% | 1.85% | 1.64% | 1.51% | 1.85% | 1.91% | 1.64% | 1.54% |
| 2025 Q1 | 3,185 | 1.88% | 2.32% | 1.85% | 2.14% | 2.14% | 1.88% | 1.57% | 2.01% | 2.48% | 2.2% | 2.23% | 1.95% |
| 2025 Q2 | 3,524 | 2.27% | 2.44% | 2.21% | 2.38% | 2.07% | 1.9% | 1.93% | 2.27% | 2.33% | 2.27% | 2.1% | 2.53% |
| 2025 Q3 | 3,854 | 3.11% | 2.57% | 2.93% | 2.75% | 2.85% | 2.46% | 2.18% | 2.59% | 2.39% | 2.36% | 2.54% | 2.71% |
| 2025 Q4 | 4,268 | 2.93% | 3.12% | 2.48% | 2.6% | 3.12% | 2.84% | 3.07% | 3.21% | 2.46% | 2.65% | ||
| 2026 Q1 | 4,638 | 3.88% | 4.23% | 4.16% | 4.36% | 3.82% | 4.09% | 4.01% | |||||
| 2026 Q2 | 5,505 | 5.5% | 6.32% | 4.7% | 5.71% | ||||||||
| 2026 Q3 | 4,567 | 8.9% |
From raw orders to a customer table
The data is Google's public thelook e-commerce dataset: synthetic but realistic orders from 2019-01 to 2026-08. Raw tables flow through staging, a shared intermediate model, and three marts. Every layer is tested, and the full build passes 32 of 32 models and tests.
Sources
BigQuery public data
- orders
- order_items
- products
- users
Staging
views
- stg_orders
- stg_order_items
- stg_products
- stg_users
Intermediate
view
- int_orders_with_revenue
Marts
tables
- dim_customers
- fct_cohort_retention
- fct_repeat_rate_by_segment
- A fair repeat metric. A customer who first bought last month hasn't had time to come back. Counting them as "didn't repeat" would make newer segments look worse, so the 90-day repeat flag is left empty until a customer has a full 90 days.
- Shared logic in one place. Order revenue and each customer's 1st, 2nd, 3rd order sequence live in one intermediate model that both marts reuse.
- Clean definitions. Cancelled and returned orders are excluded, the current partial month is dropped, and names and emails never leave staging.
- Tests beyond the basics. Custom SQL tests check that each cohort has one row per month and that retention always falls between 0% and 100%, with the first month exactly 100%.
Most customers buy once
Only 6% of customers place a second order within 90 days. In any given month after the first purchase, just 2.5% of a cohort orders again, sliding to 1.21% by month 24. As a result, the first order makes up about 85% of what a customer spends in their first year.
Share of customers ordering in each month after their first order
View data
| Month | Ordered | Customers observed |
|---|---|---|
| 1 | 2.5% | 58,983 |
| 2 | 2.36% | 56,871 |
| 3 | 2.03% | 54,958 |
| 4 | 2.08% | 53,011 |
| 5 | 1.88% | 51,366 |
| 6 | 1.78% | 49,660 |
| 7 | 1.74% | 48,250 |
| 8 | 1.62% | 46,728 |
| 9 | 1.65% | 45,300 |
| 10 | 1.57% | 43,896 |
| 11 | 1.53% | 42,460 |
| 12 | 1.5% | 41,192 |
| 13 | 1.45% | 39,859 |
| 14 | 1.41% | 38,606 |
| 15 | 1.45% | 37,423 |
| 16 | 1.48% | 36,192 |
| 17 | 1.34% | 35,082 |
| 18 | 1.31% | 33,967 |
| 19 | 1.35% | 32,969 |
| 20 | 1.29% | 31,897 |
| 21 | 1.38% | 30,905 |
| 22 | 1.28% | 29,928 |
| 23 | 1.22% | 28,916 |
| 24 | 1.21% | 27,952 |
Where customers come from doesn't predict who returns
Every acquisition channel lands between 5.6% and 6.1%, and their confidence intervals overlap. A chi-square test finds no real difference (χ² = 1.9 on 4 degrees of freedom). First-purchase category looks more interesting at first, ranging from 2.7% to 7.3%, but with 26 categories a few will look extreme by chance, and the spread is what random noise would produce (χ² = 23.6 on 25 degrees of freedom).
90-day repeat rate by acquisition channel (dashed line: all customers, 6%)
View data
| Channel | Repeat rate | 95% CI | Customers |
|---|---|---|---|
| Search | 6.05% | 5.81% to 6.29% | 38,698 |
| Display | 6.05% | 5.05% to 7.05% | 2,182 |
| Organic | 5.86% | 5.35% to 6.37% | 8,171 |
| 5.74% | 4.87% to 6.6% | 2,789 | |
| 5.55% | 4.77% to 6.33% | 3,313 |
Newer customers come back faster
The one thing that is moving: the share of customers who order again the month after their first purchase rose from 0.68% for 2019 customers to 2.6% for 2025 customers, and 5.48% so far in 2026. Something changed recently, and it isn't the channel mix.
Ordered again the month after their first order, by year of first order
* 2026 includes customers who first ordered January through July.
View data
| First order year | Ordered next month | Customers |
|---|---|---|
| 2019 | 0.68% | 737 |
| 2020 | 0.9% | 2,341 |
| 2021 | 0.98% | 4,070 |
| 2022 | 0.93% | 5,998 |
| 2023 | 1.21% | 8,026 |
| 2024 | 1.86% | 10,725 |
| 2025 | 2.6% | 14,831 |
| 2026 (Jan-Jul) | 5.48% | 12,255 |
What I'd tell the business
- Don't move marketing budget based on retention. Channels bring in customers who behave the same after the first order, so judge channels on acquisition cost and first-order value instead.
- Build a second-purchase program for every new customer. With roughly 94% not returning within 90 days, the opportunity is universal, not tied to one segment. Launch it as a test with a holdout group so the lift can be measured instead of assumed.
- Find out what improved in 2024 to 2026. Early retention has more than doubled since 2023. Before investing in anything new, identify what drove it (product mix, promotions, email, site changes) and do more of it.
- thelook is synthetic data generated by Google for demos, so the patterns show the method rather than a real retailer's behavior.
- A customer counts as retained in a month if they place at least one completed order that month. Cancelled and returned orders don't count.
- Confidence intervals use a normal approximation, which is reasonable at these sample sizes.