Calculate customer cohorts in pandas and reconcile with SQL

Assign each customer to a defined starting cohort, count distinct active customers by activity period and divide by the original cohort size. Reconcile the intermediate customer-month table before comparing percentages across tools.

This example uses first-observed purchase month, not verified lifetime acquisition month. The source window is complete from January through April 2026 under the synthetic fixture's contract.

Reuse one dataset across two implementations

Use purchases.csv from the advanced SQL lab, alongside the pandas runtime lab. Its eleven purchases belong to five customers. A and B first appear in January, C in February, D in March and E in April.

Customer B purchases twice in January. Counting rows would overstate that month's active customers, so reduce to one customer-month before cohort aggregation.

import sqlite3
import pandas as pd
from build_and_verify import ROOT

purchases = pd.read_csv(ROOT.parent / 'advanced-sql' / 'purchases.csv',
                        dtype={'customer_id': 'string', 'purchase_id': 'string'})
purchases['month'] = pd.to_datetime(purchases['purchased_at'], format='%Y-%m-%d').dt.to_period('M').astype(str)
activity = purchases[['customer_id', 'month']].drop_duplicates()
first = activity.groupby('customer_id')['month'].min().rename('cohort')
activity = activity.join(first, on='customer_id')
counts = activity.groupby(['cohort', 'month']).size().rename('active_customers')
with sqlite3.connect(':memory:') as db:
    purchases[['purchase_id', 'customer_id', 'purchased_at', 'amount_paise']].to_sql('purchases', db, index=False)
    sql_rows = db.execute('''
        WITH monthly AS (
          SELECT DISTINCT customer_id, substr(purchased_at,1,7) AS month FROM purchases
        ), first AS (
          SELECT customer_id, MIN(month) AS cohort FROM monthly GROUP BY customer_id
        )
        SELECT first.cohort, monthly.month, COUNT(*)
        FROM monthly JOIN first USING(customer_id)
        GROUP BY first.cohort, monthly.month ORDER BY 1,2
    ''').fetchall()
pandas_rows = [(cohort, month, int(value)) for (cohort, month), value in counts.sort_index().items()]
assert pandas_rows == sql_rows
months = ['2026-01', '2026-02', '2026-03', '2026-04']
grid = pd.MultiIndex.from_tuples([(cohort, month) for cohort in sorted(first.unique())
                                 for month in months if month >= cohort], names=['cohort', 'month'])
complete_counts = counts.reindex(grid, fill_value=0)
sizes = first.value_counts()
retention = {key: int(value) / int(sizes[key[0]]) for key, value in complete_counts.items()}
assert [retention[('2026-01', month)] for month in months] == [1, .5, .5, .5]
assert retention[('2026-02', '2026-04')] == 0
assert ('2026-04', '2026-05') not in retention
print('Pandas and SQL counts agree; eligible zero cells and cutoff verified')

The SQL query is executed inside the same example, so the comparison checks actual results rather than two written tables that could share a transcription error.

Explain the denominator and the zero cells

January's cohort size remains two. February, March and April each contain one active member of that cohort, producing 50% in each month. The active member changes: A is active in February and April, while B is active in March.

Consequently, these percentages describe period activity, not uninterrupted survival. A customer can return after an inactive month.

The February cohort has no April purchase, so its April cell is zero because April is within the complete observation window. May is outside that window and is not filled with zero.

Reconcile before interpreting

Compare unique customer IDs, customer-month rows, cohort assignments, cohort sizes and active counts. Matching percentages can hide different numerators and denominators, especially for small cohorts.

The pandas Period documentation describes month-period representation, while Python's sqlite3 reference covers the in-memory database used for independent execution.

State the observation limitation

A customer whose first observed purchase is in January might have purchased before the extract began. Without earlier history, call the cohort first-observed rather than newly acquired. Also define how merged customer identities, cancellations and refunds affect activity before applying this approach to real data.

Exercise: add a second January purchase for A and verify that retention is unchanged. Then add C's April purchase and show that only the appropriate cohort-period count changes from zero to one.

NeuraPath's Data Analytics with Generative AI course connects SQL and pandas through shared metric contracts. Agreement is useful when both implementations measure the same population and observation window.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Pandas resample: make absent days visible.
• Continue with Detect outliers without automatically deleting valid sales.