article·8 min read
Rundown 02 — Collect NPS Into a Google Sheet, Every Week
Sets up an NPS survey that writes into a Google Sheet, then gives you a weekly digest that tells you the score, the split, whether the movement is real or noise, and which detractors are waiting on a reply.
- Workflows
- Product discovery
What it does: sets up an NPS survey that writes into a Google Sheet, then gives you a weekly digest that tells you the score, the split, whether the movement is real or noise, and which detractors are waiting on a reply.
When to use it: you want a running read on customer sentiment and you don't want to buy a tool for it, or you have a tool and nobody reads what it sends.
What you need: a Google account. That's it. Twenty minutes once to set it up, then two minutes a week.
Time: the weekly run is faster than opening the dashboard you already pay for — which is the point, because the failure mode of NPS isn't collecting it, it's the six weeks nobody looks.
---
Why most weekly NPS is worse than no NPS
Three failure modes, and the weekly cadence makes all three worse:
1. The number moves and it's just noise. NPS is a difference between two percentages, so it swings hard on small samples. At 50 responses the margin of error is roughly ±20 points. A move from 31 to 24 is a rounding error being narrated as a decline, and someone will build a quarter of work on top of it.
2. Everyone gets surveyed, constantly. Weekly sending turns into weekly asking, the people who still answer are the ones who always answer, and you end up measuring your most patient customers rather than your customers.
3. The score becomes the deliverable. A detractor writes a paragraph explaining exactly what's broken, it gets counted as a 4, and nobody replies. The number goes in the deck; the customer goes elsewhere.
The setup below is built so the weekly number arrives with its own error bars and a short list of people to actually talk to.
---
Part 1 — the setup prompt (run once)
Paste this into Claude.
Help me set up a lightweight NPS collection system in Google Forms and Google
Sheets. I want to run a weekly digest off it afterwards, so the structure needs
to survive being read by a script every week.
STEP 0 — CHECK WHAT YOU CAN REACH
Tell me first whether you can create and edit Google Forms and Sheets in this
session, or whether you can only give me instructions to follow myself. Don't
guess — check, then say which mode we're in. Either is fine; I just need to know
which one before we start.
STEP 1 — ASK ME
In one message, ask me for:
- My company/product name, as customers would say it
- Who I'm surveying, and roughly how many of them exist
- Whether this is relationship NPS (how do you feel about us overall) or
transactional NPS (how was that specific interaction). If I say both, tell me
they are different metrics and must not be mixed in one score.
- How I'll deliver the survey link (email, in-app, after a support ticket)
Then wait.
STEP 2 — THE FORM
Design a Google Form with no more than three questions:
1. The standard NPS question, worded exactly: "How likely are you to recommend
<company> to a friend or colleague?" as a 0-10 linear scale, labelled
"Not at all likely" at 0 and "Extremely likely" at 10. Required.
2. "What's the main reason for your score?" - long answer, optional. Do not
make it required; a forced comment is a worse comment.
3. Nothing else, unless I insist. Segment data (plan, tenure, account owner)
should be passed in as a pre-filled hidden field or joined later from my own
records, never asked. Every extra question costs response rate, and response
rate is the only thing keeping this data honest.
Give me the pre-filled link format so I can pass a customer ID through.
STEP 3 — THE SHEET
Link the Form to a Sheet with exactly three tabs:
- Responses: owned by the Form. Never edited by hand, never sorted, never
filtered in place. Treat it as append-only raw data.
- Scored: reads from Responses and adds, per row, the category
(Promoter 9-10, Passive 7-8, Detractor 0-6), the ISO week, and a blank
"replied?" column for me to tick.
- Weekly log: one row per week, appended by the weekly run. Columns: week
ending, rolling-4-week NPS, n, margin of error, promoter %, passive %,
detractor %, responses sent, response rate, note.
Give me the formulas for the Scored tab, and set up the Weekly log headers.
STEP 4 — THE SAMPLING PLAN
Write me a one-paragraph sending rule and put it in a text box at the top of the
Weekly log tab. It must include:
- No customer surveyed more than once in any 90-day period
- Who is excluded (trials, churned, anyone in an open complaint)
- A fixed weekly send volume rather than "whoever we remember"
Tell me what weekly volume I need to get a usable sample, given the population
size I gave you in step 1, and be honest if my population is too small for a
weekly read - if it is, recommend fortnightly or monthly instead and say why.
STEP 5 — CONFIRM
Show me the tab structure and the sending rule, tell me the link to the live
Form, and stop. Don't send anything to anyone.---
Part 2 — the weekly prompt
This is the one you run every week. Set it as a scheduled task in whichever Claude surface you use, or paste it every Monday morning — the discipline matters more than the automation.
Run my weekly NPS digest from <link to my Google Sheet>.
RULES
- Compute NPS as (% promoters) minus (% detractors), rounded to a whole number.
Never average the raw scores. Passives count in the denominator only.
- Use a rolling 4-week window, not the single week. Report the single week's n
separately so I can see collection volume.
- Always report the 95% margin of error alongside the score, using
1.96 * sqrt((p + d - (p - d)^2) / n) * 100, where p and d are the promoter and
detractor proportions. Round it to a whole number of points.
- Compare to the previous 4-week window. State the movement in points, then
state plainly whether it exceeds the margin of error. If it doesn't, write
"within noise" and do not offer an explanation for it. Do not narrate noise.
- If n is under 30 for the rolling window, lead with that. A score on 12
responses is not a score.
OUTPUT - keep it to this, nothing more:
NPS - week ending <date>
Rolling 4 weeks: <score> (n=<n>, +/-<moe> points)
Split: <p>% promoters / <x>% passives / <d>% detractors
Previous 4 weeks: <score> (n=<n>)
Movement: <+/-n> points - <within noise | outside the margin of error>
Collected this week: <n> responses from <sent> sent (<rate>%)
Needs a reply (<count>):
- <score> - <customer, if known> - "<first line of their comment>" - <days waiting>
(List every response scoring 0-6 that left a comment and has no tick in the
replied? column, oldest first. If there are none, say "none outstanding".)
Data health:
- <any of: response rate falling, same customers recurring inside 90 days,
blank weeks, a week where volume spiked, scores clustering at 10 in a way
that suggests the link went out somewhere it shouldn't have. If everything
is clean, write "clean".>
THEN
Append this week's row to the Weekly log tab. Tell me the row you added. Don't
write anything to the Responses tab, ever.---
Getting more out of it
- Reply to the detractors before you read the score. That section is the only part of the digest that changes an outcome this week. The number is context; the list is work.
- Let it run eight weeks before you interpret anything. Two points make a line and the line will be wrong. The rolling window plus the error bars are there to stop you acting in week three.
- Don't benchmark against published industry NPS. Other people's numbers come from different sampling, different populations and a strong incentive to publish the good ones. Your own trailing quarter is the only comparison worth making.
- When the score does move outside the margin of error, that's when you go back to the verbatims — and at that point you want Rundown 01's synthesis, not a skim.
---
If you do this more than twice
Which you will, because the whole point is that it runs every week. Save it as a skill in Claude Code, Cowork or Codex and it becomes something you invoke by name, that runs on a schedule without you, and that a colleague can run identically while you're on holiday. The skill version of this Rundown is [link], along with the digest template and the NPS maths it applies.
The prompt is the same either way. The difference is that a scheduled skill still runs in week nine, which is roughly when the pasted version stops.