Walk me through how you'd analyze retention for a new feature.
Data/growth interviews and PM interviews both ask. Knowing the SQL and the interpretation is table stakes.
Define cohort: users who did action X in week W. Track what fraction return in weeks W+1, W+2, W+N.
SQL: SELECT cohort_week, period_number, COUNT(DISTINCT user_id) FROM activity JOIN cohort ON user_id GROUP BY 1, 2 ... then pivot.
Healthy curves flatten — retention stabilizes at a non-zero floor. Bad curves decay to zero — product has no stickiness.
Compare cohorts: week-over-week cohort improvement suggests product is getting better for new users. Flat cohorts mean you're treadmilling.
Segment: by acquisition channel, by first-week behavior, by country. Often a specific segment drives the average.
I'd define the cohort by first-time use of the feature: users who did X in week W. Then I'd measure what fraction came back and used it in weeks W+1, W+2, ..., W+12. A healthy curve flattens — say D30 retention at 25% and holding. A dying curve goes to zero within a few periods. I'd compare cohorts across weeks: if more recent cohorts retain better than older ones, the product is improving for new users. If all cohorts look the same, you're running on the treadmill. The most useful layer is segmentation — retention by acquisition channel almost always shows huge variance, and the 'average' hides which channel is actually working. For a launch I'd also watch W+1 carefully — a 50% drop in week 2 is normal; a 90% drop says the feature didn't deliver on expectation.
Always segment by power-user vs casual in the cohort — averages lie when distributions are skewed.
Retention is a lagging metric. Pair with engagement (events per user) for faster signal.
HireMe AI (即答侠) includes the SQL template plus interpretation rubric for each curve shape.
Two sides of the same coin — retention = 1 - churn at any period. Retention is more positive-framed for product narratives.
D1 catches onboarding issues, D7 catches weekly habit formation, D30 catches long-term value. Different teams obsess over different ones.
HireMe AI listens to your interview in real-time, detects questions, and generates instant answer suggestions — so you never freeze up again.
Try HireMe AI Free