From: Owen Fairley, growth lead at Juniper Lane
Re: Is the club keeping people?
Juniper Club, our paid membership, opened in January. Members sign up, then (we hope) keep ordering. I need a retention table: one row per signup month (the cohort), one column per month since signup (0 is the signup month), and in each cell the share of that cohort's members who ordered in that month.
Use members.csv and member_orders.csv. The order export is known to be messy: some rows are repeated, a few orders belong to member ids we can't find, and a few are dated before the member signed up. Members who signed up and never ordered still count in their cohort. Data runs to the end of June, so later months of the newest cohorts haven't happened yet: leave them empty, not zero.
Finish with the cohort that kept members best and worst two months after signup, and a chart I can show.
Cohort tables answer "do customers stay?" better than any single number. You'll clean the orders with the checks from the data quality unit, compute months since signup from calendar months, count distinct active members, divide by cohort size along the rows, and keep the difference between "no one ordered" and "that month hasn't happened".