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.