Reproducible Data Analysis: Turn a One-Off Query into a Procedure That Always Gives the Same Number

Tell me whether to freeze this analysis into a runnable script or
leave it as a one-off query in chat.

What I'm doing: [description of the analysis, e.g. a monthly
revenue breakdown by category and channel from three exports]
How often: [once a month]
Who gets the result: [leadership, accounting, a client]
What gets decided based on it: [next month's budget, commission
amounts]
How big the data is: [~40,000 rows, growing by ~3,000 a month]
Who will do this if I can't: [no one / a colleague in accounting]

Answer in a structured way:
1. Recommendation: chat / script / script with formal approval.
2. Justification broken down by criterion, not in general terms.
3. What would need to be written down for someone else to handle
   this.
4. What happens if I leave it as is — what mistake is most likely
   to slip through.
Give me the recommendation straight away, and admit where you're
not sure.

Before you calculate anything, go through the file [name] and
list the decisions you'd otherwise have to make for me.

I want an overview of [what I want to calculate, e.g. revenue by
month and category for 2026].

List a numbered set of questions you need my answer to, ordered
by how much they'd affect the result. For each one, say exactly
what you don't know, what the options are, how the result would
differ, and what you'd choose if I didn't answer.

I'm typically interested in: what counts as one case, which rows
to exclude, which date determines the period, whether amounts
include tax, and how empty values are handled.
Don't calculate anything yet, just ask.

We're done. Now write what we just agreed on into a single
runnable file, so you don't have to work it out again next time.

Requirements:
- One file, named after what it does: mesicni-prehled-trzeb.
- A comment header at the top: what the file calculates, from
  which inputs, what the output is, when it was created, and who
  is responsible for its contents.
- Anything that changes between runs (input path, period,
  thresholds) goes as named values in one place right below the
  header. Nowhere else should numbers or paths be hardcoded.
- Split it into sections: loading, input validation, cleaning,
  calculation, output to the console and to a CSV with the date
  in its name.
- Nothing extra that we didn't agree on. Wherever you had to
  assume something, write it into the header as an open question
  for me.

Then show me the whole file and explain what each part does in
plain English.

Add comments to the script, but not descriptive ones —
explanatory ones. For each step, write:
- what happens in it, in plain language,
- WHY it's done — what business rule is behind it,
- what would happen if that step were missing,
- who or what decided it should be this way (who confirmed it
  for me).

Comment every filter and every condition especially carefully —
for those, I want to see the reason, not a description. Write so
that a colleague who knows our company but doesn't program can
follow it.
Write the comments in plain English.

Add control printouts to the script so I can see what's happening
to the data.

Specifically:
- row count right after loading the file,
- row count before and after every filter, and the difference in
  both absolute numbers and percent,
- the sum of the key amount column before and after cleaning, so
  I can see how much money vanished through filtering,
- the count of empty and nonsensical values in the columns that
  feed into the calculation,
- the count of unique categories, and a list of those that appear
  fewer than [five] times — that's usually where typos hide.

Number the printouts according to the steps of the procedure. At
the end, add a summary line: how many rows went in, how many
dropped out, and how many are in the result.

mesicni-uzaverka/
  data/           exports with a date in the name, never overwritten
  vystup/         results from individual runs, also dated
  mesicni-prehled-trzeb.sql   the calculation itself
  definice.md     what the terms mean and what we decided to count
  navod.md        how to run it
  historie.md     what changed, when, and why

Go through our script and pull out every implicit decision that's
in the code but never explained anywhere.

For each one, write what the script does (with a line reference),
what other reasonable approach would exist, and when that
difference would actually matter.

Then assemble a definice.md file: a list of the terms the
calculation uses (for example [revenue, active customer, month,
cancellation]), a one-sentence definition of how the script
actually uses each one, and a note on who confirmed it.
For terms where I never gave you a decision, write UNCONFIRMED and
attach the question I need to answer.

Set up version history in this folder using git, so I can go back
to any earlier version of the calculation.

Set it up like this:
- The history should include the script, definice.md, navod.md,
  and historie.md.
- The history should NOT include the data/ or vystup/ folders —
  they're large files and they hold operational data. Set it up
  so they can't end up there even by accident.
- Save the current state as the starting point.

Then explain in five lines what I should do every time I change
the script, and how to view the difference between two versions.
Write it for someone who's never seen git, with commands ready to
copy and paste.

From now on: whenever you change the script at my request, save
the change to the history with a description in plain English
that states WHAT actually changed (for example, “credit notes are
now subtracted from revenue”), not a technical description.

Set up a recurring run of this procedure.

When: [the 1st of every month at 7:00]
What should happen:
1. Run the script over the newest export in the data/ folder.
2. Save the output to vystup/ with the date in its name.
3. Also save the full run log (control printouts) to a file in
   log/.
4. Send me [an email / a chat message] with a short summary: the
   headline numbers, the checksums, what's different from the
   last run, and whether the run ended with an error.

Rules:
- If there's no newer export in the data/ folder than last time,
  don't calculate anything, and tell me the source data is
  missing.
- If the script ends with an error, send me the error message and
  don't try to work around it.
- Don't send or publish anything to anyone else — the output goes
  to me only, and I decide what happens with it.

Tell me where this setup got saved and how to turn it off.

Add an input check at the start of the script that stops it when
the data doesn't look the way it should.

Check:
- that the file exists and isn't empty,
- that it contains exactly the expected columns — have the script
  stop on either a missing column or an unexpected extra one,
- that the data types match (a date is a date, an amount is a
  number),
- that the row count is within a reasonable range of the last run
  (have the script stop on a deviation over [30]%),
- that no new categories the procedure doesn't know about have
  appeared,
- that there are no duplicates by [document number].

If a check fails, the script should exit with an error and print,
in plain English, WHAT is wrong and what I should do about it. No
automatic fixes, no filling in missing values, no continuing with
just a warning — either the data checks out, or nothing gets
calculated.

Build me a regression check for this procedure.

1. From our data, produce a small test sample (on the order of
   [200] rows) covering all the important cases: normal records,
   cancellations, empty values, boundary dates around a month
   change, a duplicate, a category with a typo. Replace personal
   information with made-up data.
2. Run the script over it and save the result as the expected
   output.
3. Show how you arrived at each individual number, so I can verify
   it by hand.
4. Prepare a procedure that runs the script over the sample,
   compares it to the expected output, and on any difference,
   prints which number and by how much.

I want to run this every time before I deploy a changed script to
production data.

Compare today's run output against the file from the last run.

Today: [vystup/trzby-2026-08-01.csv]
Last time: [vystup/trzby-2026-07-01.csv]

Give me:
1. A table: metric, last time, today, absolute difference,
   percentage difference.
2. Which values changed by more than [10]% — list these
   separately.
3. Changes in a period that's already closed (older months
   shouldn't change) — list these first, they're the most
   suspicious.
4. New and disappeared categories or items.
5. For every larger difference, offer a possible explanation and
   tell me how I could verify it — in which system, and what to
   look for there.

Don't claim what happened. State what's visible, and what
questions follow from it for me.

Write an entry about today's run into the historie.md file.

Source material: [script output + comparison with the previous
run].

Structure (max 12 lines):
- date of the run and the input file used,
- headline numbers: [total revenue, document count, category
  count],
- checksums, and whether they match expectations,
- what changed versus the previous run and what the explanation
  is,
- what remains unexplained and needs to be tracked down,
- whether the script changed since last time, and how,
- who approved the result (leave blank, I'll fill it in).

Write it matter-of-factly. Where you don't know the explanation,
write “unexplained” — don't make up a reason.