How to Build a Net Retention Cohort Model Using Claude + Excel

👋 Yo! it’s CJ Gustafson and welcome to Mostly Metrics.

New format today. I think you’re going to dig this.

I’ve spent years teaching you how to build the templates that CFOs and FP&A professionals need to do their jobs well. Stuff like ARR waterfalls, budgeting board slides, contribution margin analyses, sales capacity models… More templates than you can shake a stick at.

But in the last eight weeks… the way I build stuff has changed. I use AI now, more specifically Claude inside Excel and PPTX, and what used to eat up half my week takes about 30 minutes.

NGL, it it feels like sorcery.

So I hit record and built a tutorial for you. Live.

I took a messy revenue export - 80 customers, three years of data, segment labels that look like five different people entered them - and built a full NRR cohort model with segment cuts using Claude and Excel.

It’s free. Everyone gets it. Hit play.

Oh if you haven’t installed the Claude desktop app or excel plugin yet, fear not. IT manager Carl is here to help. Here’s the v0 set up guide.

I’m learning stuff on the fly as capabilities evolve. And here’s what I hope you’ll come to appreciate…

My first prompt was too vague and the output came back funky. The cohort table started at some weird number instead of 100%. I had to ask Claude “what the hell is Q0?” And the conditional formatting looked like a Christmas tree.

Then I got hyper specific. I used Claude’s desktop app to write myself a better prompt - I basically fed it its own instructions nom nom nom - pasted it into the Excel sidebar, and the second pass was night and day. Clean data tab. Proper cohort matrixes. Segment cuts that actually made sense.

I left both attempts in the video on purpose.

I hate walkthrough videos where someone glides through a build like they nailed it first try. Dude. There were 17 small steps and 12 “wait, that’s wrong” moments in between. They just edited them out.

(Note: I did edit out the waiting for Claude to calculate part because it’s me just scrolling twitter for 20 minutes drinking a miller lite at 10:30 PM)

The gap between a lazy prompt and a specific one is the whole game right now. Watching me figure that out in real time is more useful than watching me pretend I got it right the first time.

This is the first video in the series. I’m going to keep building the models and templates I’ve always taught you, but now with AI in the workflow.

Video is free. And below is everything you need to try to build this yourself.

The exact prompts. Word for word - including the vague one that flopped and the specific one that worked. The gap between them is the whole lesson.

The raw data file. Same dummy dataset from the video. 80 customers, three segments, three years, all the mess baked in. Drop it like it’s hot in Excel, open the Claude sidebar, rip.

The final output. What I walked away with after some minor cleanup.

You’ll have the materials, the prompts, and the steps to run this yourself. Downloads below.

Here’s the step-by-step, prompt-by-prompt breakdown of what I did in the video.

First: Download the raw data file, open it in Excel, install the Claude add-in if you haven’t already and follow along.

This is where you resist the urge to start building. You want Claude to audit the data first and surface the decisions you need to make before writing a single formula. Open the Claude sidebar in Excel and type:

Prompt #1 (the vague one):

This is the prompt I used in the first attempt. It kinda works — Claude found the segment label mess, the negative MRR, the partial months, the reactivation gaps. It asked me the right questions.

But when I told it to go build, the output was half-baked because I hadn’t given it enough structure to work with.

It’s very similar to if you had a first year analyst who you just told to go and figure shit out. If they’re smart, they’d get some of it right, but it wouldn’t be exactly what you wanted.

Oh, and Claude is going to ask you a series of questions before it builds anything. Here’s what it asked me and how I answered (the first time around):

How should I handle the prorated first month? → Annualize the prorated portion. Use the second month’s MRR as the normalized value. The proration is a billing artifact, not a retention data point.

Customers with multi-month gaps — churn, reactivation, or zero-fill? → Treat as churn. Flag reactivation customers separately so you can see how they affect the cohort math.

What NRR time grain? → Quarterly.

How should NRR be calculated — cumulative vs. start? → From start of cohort (not rolling cumulative).

Negative MRR — floor at zero, include as-is, or exclude? → I excluded it. In the video you’ll hear me say “I’m gonna exclude this one.” In hindsight, including it as contraction is defensible too. Depends on whether the credit was a one-time billing fix or a real downsell. Know your data.

Which field defines the cohort quarter — start date or first revenue month? → Contract start date. That’s when we started paying the reps.

This is where the first attempt fell short. I basically said “go build it” and got something that was structurally okay but had weird issues — Q0 columns that didn’t make sense, NRR not starting at 100%, no clean data tab.

So I went to Claude’s desktop app and asked it: what would make for the best prompt if I’m building an NRR cohort model from this dataset? It wrote me a prompt. I pasted that prompt into the Excel sidebar.

Prompt #2 (the specific one):

Net net, this is the prompt that changed everything. The clean data tab came back right. Segment labels normalized. Reactivation flags in place. Raw data preserved on its own tab. Night and day from the first attempt.

After the clean data was ready, Claude asked me what views I wanted. Notice we just jumped before into the final product without taking this intermediate step. Here’s what I said:

That last line — “check in with me after the first sheet is done” — is a small thing that makes a big difference. Otherwise Claude will build all three sheets and if the first one is wrong, you’re fixing everything. Have it show you one, confirm it looks right, then let it keep going.

(Major life hack - when it asks you for input, ask it what best practices are. I accept some of Claude’s answers to Claude’s own questions.)

Claude’s default formatting is functional but ugly. I had to go back and forth on conditional formatting a few times. It looked like a Christmas tree. Then I asked it to use a blue gradient. Better, but I still might change it.

Never expect the formatting to be perfect on the first pass. Get the math right, then make it pretty.

This is where it gets fun. Once the model was built, I typed:

Claude came back with the full diagnosis — blended NRR of 96%, Enterprise at 102% carrying the business, Mid-Market leaking at 80%, SMB at 79% (the “value destruction engine” line). It gave me three immediate actions and calculated the $633K retention gap unprompted.

Then I asked for waterfall charts:

Download Completed File

A few things to think about when you run this on your actual numbers:

Segment thresholds. My dummy data uses fixed segments. Your segments might have changed over time — a customer that was Mid-Market two years ago might be Enterprise now. Decide whether you’re using current segment or segment-at-signing.

Reactivations. I flagged them and kept them in the cohort. Some CFOs I’ve talked to prefer pulling them out entirely. Depends on volume — if you’ve got 3 reactivations it doesn’t matter, if you’ve got 30 it’ll distort your cohort curves.

Monthly vs. quarterly cut. I used monthly in the video. For a board deck, quarterly is usually cleaner. For an internal FP&A review, monthly gives you more signal on when exactly the contraction is happening.

Negative MRR. I excluded it. If your business has a lot of credits and refunds flowing through the billing system, you might want to include them as contraction instead. Just be consistent.

Next time, we’re building something else. Maybe a headcount model, maybe an ARR waterfall. Let me know what you want to see.

Wishing you a Claude prompt that contemplates crisp formatting,

CJ