top of page

How to build a cohort retention table with ChatGPT and check it in pandas

2 minutes ago
8 min read
Cover reading How to build a cohort retention table with ChatGPT and check it in pandas over a teal city skyline, Data Studios original cover

A cohort retention table groups customers by the month of their first purchase and shows what share of each group comes back one, two or three months later. ChatGPT's data analysis feature can build one from an order export in a few minutes, because it writes and runs Python with pandas on the file you upload. The table it returns still needs checking, because three errors produce plausible but wrong cohort tables: counting orders instead of customers, using the wrong cohort definition, and filling months that have not happened yet with zero.


The method below uses a small illustrative order file of 25 orders from 12 customers, the prompts to send, and the correct retention table computed independently in pandas and in SQL. In that example the January cohort has 5 customers and a month-1 retention of 60%, while a version that counts orders instead of customers reports 50% for the same cell. That ten-point gap is the kind of error an analyst has to catch before a retention chart reaches a board deck.


··········


WHAT A COHORT RETENTION ANALYSIS MEASURES.


Product, growth and finance teams use cohort tables to see whether newer customers behave better or worse than older ones. A single blended retention rate hides that: if recent cohorts churn faster, an overall average can stay flat for months while the business quietly deteriorates. Splitting customers by acquisition month makes each row comparable with the others, because every row starts from the same point, month 0, the month of the first order.


Three definitions have to be fixed before any tool touches the data. The cohort is the month of the first order, the period is a calendar month, and a customer counts as retained in month N if they placed at least one order N months after their cohort month. Each cell is then the number of distinct retained customers divided by the cohort size in month 0. Cells for months that are still in the future for a given cohort stay blank, since those customers have not had the chance to return yet.


Other definitions are possible, such as signup date instead of first order, weekly periods, or revenue retention instead of customer retention. Each produces a different table, so the prompt should state the definition explicitly instead of letting ChatGPT choose one.


··········


CHATGPT DATA ANALYSIS REQUIREMENTS AND FILE LIMITS.


OpenAI's Help Center describes data analysis as a feature in which ChatGPT writes Python code to process the data, executes it and examines the results, using libraries such as pandas and Matplotlib. The code can be shown through the "View Analysis" option or the link at the end of a message, which is the part an analyst needs for review. The limits below were verified on OpenAI's Help Center pages on November 20, 2026.


........


Item

Documented value

File types for data analysis

Excel (.xls, .xlsx), CSV, PDF, JSON

Maximum file size

512 MB per file

CSV and spreadsheet size

About 50MB, depending on row size

Files per conversation

Up to 10

Free users

Up to 3 file uploads per day (File Uploads FAQ)

Storage caps

10GB per user, 100GB per organization

Code environment

Destroyed within 13 hours of conversation inactivity

Interactive charts

Bar, pie, scatter and line in most cases


........


The data analysis article does not list which ChatGPT plans include the feature, so the plan available in a given workspace should be confirmed in the product itself. For exports above the CSV size limit, aggregate or split the file first, as described in the guide on analyzing a CSV over ChatGPT's 50MB limit. The 13-hour environment lifetime also means that a cohort analysis left overnight may need the file uploaded again before follow-up questions work.


··········


THE ILLUSTRATIVE ORDER DATA FOR THE COHORT TABLE.


The worked example uses a file called orders.csv with one row per order and three columns: order_id, customer_id and order_date. The data is illustrative and covers January to April 2026. The table below summarizes it by customer; the uploaded file holds the 25 individual order rows.


........


Customer

Order dates (2026)

Orders

C01

Jan 5, Feb 11, Mar 3, Apr 20

4

C02

Jan 14, Mar 22

2

C03

Jan 8, Jan 27

2

C04

Jan 19, Feb 2

2

C05

Jan 30, Feb 17, Apr 9

3

C06

Feb 3, Mar 15, Apr 2

3

C07

Feb 21

1

C08

Feb 9, Apr 25

2

C09

Feb 26, Mar 1

2

C10

Mar 7, Apr 13

2

C11

Mar 18

1

C12

Apr 6

1


........


Two traps are built into the data. Customer C03 ordered twice in January and never again, which inflates month-0 counts when orders are counted instead of customers. Customer C09 ordered on February 26 and March 1, three days apart, which counts as month-1 retention under a calendar-month definition even though the gap is short. Before running the analysis, a quick deduplication and row-count check like the one in the guide on cleaning and deduplicating a CSV with ChatGPT confirms that the file holds 25 orders and 12 customers.


··········


PROMPTS FOR BUILDING THE COHORT RETENTION TABLE IN CHATGPT.


The first prompt fixes the definitions and asks for the code to be shown. Stating the denominator and the treatment of future months closes off two of those errors before the code is written.


I uploaded orders.csv (columns: order_id, customer_id, order_date).
Build a monthly cohort retention table:
- Cohort = calendar month of each customer's FIRST order.
- month_number = whole calendar months between the cohort month and the order month.
- A customer is retained in month N if they have at least one order in that month.
- Count DISTINCT customers, never orders.
- Retention = retained customers / cohort size in month 0.
- Leave cells blank (not 0) for months that have not happened yet for a cohort.
Show the cohort sizes, the customer counts and the retention percentages
as separate tables, and show the Python code you ran.

A second prompt asks ChatGPT to audit its own result against simple totals that are easy to verify by hand.


Check your result:
1. Do the month-0 cohort sizes add up to the total number of distinct customers?
2. How many orders and how many distinct customers are in the file?
3. List the customers counted as retained in month 1 for the January cohort.
Then add a heatmap of the retention percentages.

The heatmap request is useful for presentation, but OpenAI's documentation lists heat maps among the chart types that are not interactive, so ChatGPT returns it as a non-interactive chart.


··········


THE CORRECT COHORT RETENTION RESULT COMPUTED IN PANDAS.


The table below is the expected result for the illustrative file, computed independently in Python. The cohort sizes are 5, 4, 2 and 1 customers, which add up to the 12 distinct customers in the file.


........


Cohort

Size

Month 1

Month 2

Month 3

Jan 2026

5

60.0% (3)

40.0% (2)

40.0% (2)

Feb 2026

4

50.0% (2)

50.0% (2)

blank

Mar 2026

2

50.0% (1)

blank

blank

Apr 2026

1

blank

blank

blank


........


The pandas code below reproduces the table and can be run on the same file to compare against ChatGPT's output line by line. It derives the cohort from the first order, converts dates to monthly periods, drops duplicate customer-month pairs and divides by the month-0 size.


import pandas as pd

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
orders["order_month"] = orders["order_date"].dt.to_period("M")

first = orders.groupby("customer_id")["order_month"].min().rename("cohort")
orders = orders.join(first, on="customer_id")
orders["month_number"] = (orders["order_month"] - orders["cohort"]).apply(lambda d: d.n)

active = orders.drop_duplicates(["customer_id", "cohort", "month_number"])
counts = active.pivot_table(index="cohort", columns="month_number",
                            values="customer_id", aggfunc="nunique")
sizes = counts[0]
retention = counts.div(sizes, axis=0).round(3)
print(sizes.to_dict())
print(retention)

Teams that keep orders in a warehouse can run the same logic in SQL. The query below was executed in DuckDB on the illustrative rows and returns the same counts and rates; date functions differ slightly in BigQuery, Snowflake and other engines.


WITH firsts AS (
  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
  FROM orders
  GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT o.customer_id, f.cohort_month,
         DATE_DIFF('month', f.cohort_month, DATE_TRUNC('month', o.order_date)) AS month_number
  FROM orders o
  JOIN firsts f ON f.customer_id = o.customer_id
)
SELECT cohort_month, month_number,
       COUNT(*) AS customers,
       ROUND(COUNT(*) * 1.0 / FIRST_VALUE(COUNT(*)) OVER (
         PARTITION BY cohort_month ORDER BY month_number), 3) AS retention
FROM activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

When the cohort rows are summarized into a single month-1 figure, the weighted rate across the three cohorts with an observed month 1 is 6 retained out of 11 customers, or 54.5%. A simple average of the three percentages gives 53.3%. The weighted figure is the correct one for a blended rate, because it gives each customer equal weight instead of giving the one-customer and five-customer cohorts the same influence.


··········


COMMON CHATGPT COHORT ERRORS AND HOW TO SPOT THEM.


Each error below produces a plausible-looking table, which is why the checks have to be numeric. The values in the second column are what the illustrative file produces under each mistake.


........


Error

Effect on the example

Check

Counting orders, not customers

Jan month 1 shows 50% (3 of 6 orders)

Month-0 sizes must sum to 12

Future months filled with 0

Feb month 3 shows 0%

Blank cells form a triangle

Simple average of cohort rates

Month 1 blended shows 53.3%

Weighted rate is 54.5%

Cohort by any order month

C02 also appears in a Mar cohort

Each customer in one cohort

Day-based periods (30 days)

C09 not retained in month 1

Confirm calendar months in code


........


The code view settles most of these questions quickly. Look for nunique or drop_duplicates on customer and month, for a groupby with min on the order date to define the cohort, and for the division by the month-0 column. If the code uses count on order_id, or computes the cohort from the order month of each row, the table is wrong regardless of how clean the heatmap looks. A related set of checks for aggregation errors appears in the guide on writing and debugging pandas code with Claude.


Two further judgment calls should stay with the analyst. The first is whether refunds, test accounts or internal orders should be removed before the cohort is built, since in a five-customer cohort a single test account is worth 20 percentage points of retention. The second is whether unusually large customers distort revenue-based variants of the table; the outlier checks in detecting outliers with ChatGPT apply before any revenue retention is calculated.


··········


DATA PRIVACY WHEN UPLOADING CUSTOMER ORDERS TO CHATGPT.


Order exports usually contain customer identifiers, and OpenAI's policy on training data depends on the type of account. OpenAI's Help Center states that content from business offerings such as ChatGPT Team, ChatGPT Enterprise and the API Platform is not used to train its models, while content from services for individuals may be used unless the user opts out through the privacy portal or the data controls settings. Files uploaded to a conversation are kept for the retention period of that chat, and deleted chats and files are removed within 30 days according to the File Uploads FAQ.


A cohort analysis needs only a customer key, an order identifier and a date. Replacing real customer IDs with pseudonymous keys before upload, and leaving out names, emails and addresses, removes most of the exposure without changing the result, because the calculation never uses those fields.


··········


COHORT RETENTION WITH CHATGPT: WHAT TO VERIFY BEFORE SHARING.


OpenAI documents that ChatGPT's data analysis feature runs pandas code on uploaded CSV, Excel and JSON files, shows that code on request, and works within a 512 MB per-file limit, an approximate 50MB limit for CSV and spreadsheet files, and 10 files per conversation. Those facts make a cohort retention table a realistic task for the tool, provided the file is small enough and the definitions are written into the prompt.


What OpenAI does not document is how reliably the generated code follows a given cohort definition. The plan that includes data analysis, the data-training settings of the account and the retention of uploaded files all depend on the workspace type. For the illustrative file, a correct table shows cohort sizes of 5, 4, 2 and 1, a January month-1 retention of 60%, blank cells for unobserved months and a weighted month-1 rate of 54.5%. Running the pandas or SQL version on the same export and comparing it cell by cell takes a few minutes and catches the order-counting and zero-filling errors that otherwise slip into reports.


··········


FOLLOW US FOR MORE.


·····


DATA STUDIOS


·····


datastudios.org


bottom of page