Level 2 · Customer retention in pandas

Same question, same definitions

Catherine’s retention review in pandas, with the same definitions as the SQL level of this case: completed orders only, cohorts by the year of the first completed order, “repeat” means two or more completed orders, and the 90-day rate only counts customers who had 90 days to come back.

Step 1: one row per customer

One row per customer is a groupby without as_index=False, so customer_id stays as the index:

completed = orders[orders["status"] == "completed"]
per_customer = completed.groupby("customer_id").agg(
    first_order=("order_date", "min"), n_orders=("order_id", "count")
)

From here every question is a second groupby over this small table. A boolean is_repeat column makes the counting readable: "size" counts every customer in the cohort, "sum" counts the True values. .dt.year gives an integer year, the same as SQL’s year().

Look at the 2025 cohort before moving on: its all-time repeat rate is much lower. That is right-censoring, not necessarily a collapse. Someone who first bought in November 2025 has had a few weeks to come back, a 2023 customer three years, so any all-time rate is unfair to recent cohorts. Step 2 fixes that with a fixed 90-day window.

Step 2: the second order with cumcount

To find each customer’s second order, number their orders. groupby(...).cumcount() numbers the rows inside each group 0, 1, 2, … in the order they currently sit, so sort first, then number:

completed = orders[orders["status"] == "completed"].sort_values(["customer_id", "order_date", "order_id"])
completed["n"] = completed.groupby("customer_id").cumcount()   # 0, 1, 2, ...

cumcount starts at 0, so the first order is n == 0 and the second is n == 1. Sorting by order_id as well as the date breaks ties exactly as the SQL version does.

pivot(index="customer_id", columns="n", values="order_date") turns the two rows per customer into two columns. It is pivot_table without an aggregation, for when each index/column pair has one value. The new columns are named after the values of n, 0 and 1, so .rename(columns={0: "first_order", 1: "second_order"}) gives them readable names (old name on the left, new name on the right). One-time buyers get NaT (missing timestamp) as their second order. Comparisons with NaT return False, so they fail the 90-day test with no extra code.

pd.Timedelta(days=90) is a length of time, exactly 90 × 24 hours, and adding it to a datetime moves it forward: pd.Timestamp("2025-01-10 14:00") + pd.Timedelta(days=90) is 2025-04-10 14:00. It is the pandas spelling of SQL’s INTERVAL 90 DAY. Using (second - first).dt.days <= 90 would also be reasonable but can differ by a day from the SQL interval near midnight; keeping the same arithmetic in both is what makes the numbers match.

Step 3: merge for the segment

customers has one row per customer and so does per_customer, so validate="one_to_one" should pass, and it does. If the duplicate customer records (ids 2001–2018) had orders, this is where they would show up; they don’t, but the check costs nothing.

("n_orders", "mean") gives orders per customer, the same as SQL’s AVG(n_orders). Select the columns in the order the task lists before returning.

SQL and pandas side by side

Idea	SQL	pandas
customers in a cohort	COUNT(*)	("is_repeat", "size")
repeaters	COUNT(*) FILTER (WHERE n_orders >= 2)	("is_repeat", "sum")
nth order	ROW_NUMBER()	sort_values + cumcount()
rows to columns	MAX(CASE WHEN rn = 2 ...)	pivot(columns="n")
missing second order	NULL	NaT

Pitfalls

• Forgetting to sort before cumcount. It numbers rows in their current order, which is not guaranteed to be by date after a filter or merge.
• count vs size. "count" skips missing values, "size" does not. On a column with no missing values they agree; know which one you mean.
• Eligibility on the wrong date. Filter on the first order, not on signup date: customers sign up before they buy, sometimes months before.