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

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




