Build a retention spreadsheet you can actually check.

Define what staying means

Imagine a paid online workshop business. Someone might remain subscribed without attending a session, while another person attends a free event without paying. Paid retention and participation are different measures. Pick the one your first analysis should answer.

For example, define a cohort by the month of a customer’s first paid enrollment. Then define monthly retention as the share of that original group with an active paid subscription at each subsequent month-end. Put these rules in an assumptions tab, including how refunds, cancellations, and reactivations are handled.

Keep inputs separate from calculations

Supply the relevant exports and explain which system owns each fact. A payment export may establish billing events; a membership export may establish subscription status. Ask AI to inspect their fields before joining them. Prefer a stable customer ID over a name. If email is the only shared identifier, account for case, spaces, and changed addresses.

Raw source values belong in the workbook as inputs. Calculated counts, rates, and summaries should use formulas or another clearly documented, inspectable calculation method. Asking every cell to be a formula would confuse source data with analysis.

1. Create separate raw-data tabs without altering the original exports.
2. Build a customer mapping table and flag unmatched or ambiguous records.
3. Create an assumptions tab describing cohort membership and retention rules.
4. Add the cohort table, supporting calculations, and charts tied to calculated cells.

Test the model with a small example

If 20 customers join in April and 15 are active at the end of May, that group’s next-month retention is 75%. A useful model lets you trace those 15 customers back to the source. Pick a few records yourself, including one cancellation and one reactivation, and check how they were counted.

Pay particular attention to months that have not happened yet. A customer group formed last month cannot have six months of observed retention. Those future cells should be blank or marked not yet observed. Showing zero would wrongly make newer groups look worse.

If you compare acquisition channels, use the same observation window. Comparing one group’s first month with another group’s sixth month does not answer whether one source brings customers who stay longer.

Ask for findings that survive inspection

Ask the assistant to separate observations from possible explanations. A lower rate in one cohort is an observation. Claiming that a price increase caused it requires more evidence. Small groups can swing sharply when only one or two customers leave, so show the original cohort sizes alongside percentages.

Finally, add a fresh sample row and confirm the model responds as expected. Request an update checklist describing where new exports go and which ranges need extending. The first deliverable should make the next month’s analysis easier to repeat and easier to question.