Which customers come back?
From: Elena Cho, Customer Marketing Lead, Juniper Trail Outfitters (fictional).
The new-customer team thinks the 2025 cohort is already weaker than previous cohorts. Before we change the welcome program, build a cohort view that gives people equal time to return.
Data: Juniper Orders, original fictional order-line data from 2022 through 2025. Customer Name identifies a customer; Order ID can appear on multiple lines.
Done: A table of distinct active customers and retention by acquisition year and calendar years since acquisition, a checked year-one rate for 2024, and a recommendation that states the observation limit.
You are testing a claim about customer behavior, so define the comparison before choosing a chart. Acquisition year comes from each customer's first observed purchase. Year one means the next calendar year, even if the customer first bought late in December. That convention is useful for an annual report, but it'sn't a full twelve-month survival measure.
Build the table in stages and inspect its customer counts before calculating a rate. The denominator should describe the original cohort and remain stable when you focus on a later age. A denominator rebuilt from the visible age-one customers would make every retained cohort look perfect.
The source ends in 2025. Keep that boundary in the recommendation. You can review 2024's year-one activity, but you can't judge 2025 at year one until the following year is observed.
Retention = COUNTD([Customer Name])
/ MIN({ FIXED [Cohort Year] : COUNTD([Customer Name]) })
The numerator changes by activity age; the cohort denominator doesn't.