Repurchase rate and cohort retention: a practical modelling guide

Two metrics, two different questions

For one-off eCommerce purchases, repurchase within a window is usually the sharper metric: it has a yes/no answer per customer and a natural deadline. For subscriptions – or any business where ongoing activity matters more than the second order – the retention curve is the primary object, and repurchase becomes a special case of it. Most brands want both, built on the same cohort table so they can't disagree about who belongs to which month.

Both start from the same prep: a deduplicated orders table with resolved customer identity. That second condition is doing heavy lifting – if guest checkouts and duplicate emails aren't collapsed to one customer key, every returning customer who checks out differently counts as a cheap new customer and a lost old one, deflating repurchase and flattering CAC simultaneously. (The identity work is covered in the data prep guide.)

Choosing the repurchase window

The window should reflect your natural purchase cycle, not a benchmark chart from someone else's category. A consumable with a 30–45 day usage cycle earns a 60-day window; skincare on a quarterly rhythm suits 90; considered purchases may need 180. Two rules matter more than the exact figure:

• Fix it once. A repurchase rate is only comparable across cohorts if every cohort gets the same window, measured in the same timezone, with the same refund treatment.
• Report complete windows only. A 90-day rate for a cohort acquired 40 days ago isn't low – it's unfinished. That's the right-censoring trap, and it deserves its own section below.

Repurchase rate by cohort, in SQL

One dataflow, two window functions: find each customer's first and second order, then test the gap against the window.

-- 90-day repurchase rate by monthly acquisition cohort
with orders_ranked as (
  select customer_id,
         order_date,
         row_number() over (
           partition by customer_id order by order_date
         ) as order_n
  from PREP_ORDERS
),
firsts as (
  select customer_id, order_date as first_order_date
  from orders_ranked where order_n = 1
),
seconds as (
  select customer_id, order_date as second_order_date
  from orders_ranked where order_n = 2
)
select date_trunc('month', f.first_order_date) as cohort_month,
       count(*)                                as customers,
       count_if(
         datediff('day', f.first_order_date, s.second_order_date) <= 90
       )                                       as repurchased_90d,
       round(100.0 * repurchased_90d / nullif(customers, 0), 1)
                                               as repurchase_rate_90d
from firsts f
left join seconds s using (customer_id)
where f.first_order_date <= dateadd('day', -90, current_date)
group by 1
order by 1;

The where clause is the honesty filter: it drops cohorts whose 90-day window hasn't fully elapsed, so the trend line never dips at the end just because time hasn't passed yet. The median datediff between first and second orders from the same CTEs gives you time-to-second-order – worth tracking alongside the rate, because a stable rate arriving later is an early warning the cycle is stretching.

The retention curve, in SQL

Retention generalises the cohort table from the LTV:CAC guide: for each cohort and each month-number, count customers with any order that month.

-- Share of each cohort active N months after acquisition
with cohorts as (
  select customer_id,
         date_trunc('month', min(order_date)) as cohort_month
  from PREP_ORDERS
  group by 1
),
activity as (
  select distinct
         c.cohort_month,
         datediff('month', c.cohort_month,
                  date_trunc('month', o.order_date)) as month_number,
         o.customer_id
  from PREP_ORDERS o
  join cohorts c using (customer_id)
)
select cohort_month,
       month_number,
       count(distinct customer_id) as active_customers,
       round(100.0 * active_customers /
             nullif(first_value(active_customers) over (
               partition by cohort_month order by month_number
             ), 0), 1)             as retention_pct
from activity
group by 1, 2
order by 1, 2;

Laid out with cohorts as rows and month-numbers as columns, this is the classic retention triangle. Read it vertically, not horizontally: the question is whether month-3 retention is improving across successive cohorts, which is the cleanest signal that product, merchandising or CRM changes are actually working.

The traps that inflate the numbers

• Right-censoring – the recent-cohorts-look-worse illusion. Fix: complete windows only, and cut cohort comparisons at ages every plotted cohort has reached.
• Identity leaks – unresolved guest checkouts undercount repeat behaviour. Fix in prep, then re-baseline history once, loudly.
• Refund blindness – a second order that was fully refunded isn't a repurchase. Net refunds against orders before ranking them.
• Grain drift – exchanges and split shipments synced as extra "orders" manufacture repurchases. The dedupe-and-version prep job exists precisely for this.
• Migration seams – a replatform or connector change that resets customer IDs shears every curve at the seam. Annotate it; don't explain it away cohort by cohort.

None of these are exotic. They're the reason two dashboards in the same company can show repurchase rates ten points apart – each one is a prep decision made inconsistently.

From queries to a model

The durable version of this work is a model: a small set of dataflows – cohort assignment, repurchase, retention – with declared grains, scheduled weekly so Monday starts with fresh curves, and monitored so a failed run alerts a person. If today this logic lives in a BI tool's prep layer, rebuilt per workbook, that's the fragility to fix first: flow-level rebuilds keyed to one visualisation tool are why we built warehouse-native scheduling into the same product as prep – the comparison is in Refyner vs Tableau Prep.

In Refyner, this is the built-in Retention & repurchase model: time-to-second-order and repeat-purchase curves by cohort, running as pushdown on your own Snowflake, open for your analysts to adapt. Building and sampling dataflows is free; scheduled runs are pay-as-you-go – see the pricing page.

Keep reading: