Case 3 · 2 levels

Customer retention

Measure whether customers come back: first-order cohorts, repeat-purchase rate, a fair 90-day repeat rate and the consumer versus business split, in SQL and in pandas.

Start

This case unlocks when both chapters are done. The levels are readable now.

Acquiring a customer is expensive; keeping one is where an online store makes its money. “Are people coming back?” is one of the first questions any new analyst is asked, and one of the easiest to answer wrongly, because the honest answer depends on when each customer started.

This case builds the standard retention toolkit on Harbor & Pine’s orders: cohorts by first order, the repeat-purchase rate, a 90-day repeat rate that does not punish recent customers for being recent, and the split between consumer and business accounts. You solve it in SQL with window functions and in pandas with groupby, and the numbers must match.

Levels

  1. Customer retention in SQL

    from Catherine Hale, CEO

    cohort, repeat rate, row number, cte, right censoring

    Boss · 3 steps · SQL

  2. Customer retention in pandas

    from Catherine Hale, CEO

    cohort, repeat rate, cumcount, timedelta, merge, right censoring

    Boss · 3 steps · pandas