Most analysis that goes wrong went wrong before any code was written, because the question was never turned into something a table can answer. This lesson is the translation step, and the four questions that make it reliable.
The four questions, before any SQL
Someone asks "are we growing?". Before you open a query editor, answer these:
1. Growing in what? Users, revenue, orders, sessions, usage? "Growth" is four different numbers that frequently disagree. Revenue can rise while users fall, and both can rise while usage per user collapses.
2. Over what period, compared with what? Week on week, month on month, against last year, against the plan? A 10% rise is excellent against last month and poor against last January if January is your peak.
3. Which population? All users, or paying ones? Including trials? Including the team's own test accounts? Including the enterprise customer who is 40% of revenue?
4. What would change your mind? If the answer is "nothing", you are being asked to confirm something rather than find it out, and it is better to know that now.
The fourth question is the one people skip, and it is the one that saves the most time. An analysis that cannot come out the other way is a presentation.
A worked translation
Vague: "Is the new onboarding working?"
Specific enough to compute:
Among users who signed up between 1 February and 28 February 2026, excluding internal accounts and users who never verified an email address, what proportion completed at least one lesson within seven days of signing up? Compare with the same measure for users who signed up in January, before the change shipped on 1 February.
Five decisions are now visible and arguable:
- Population: February sign-ups, verified, external.
- Numerator: completed at least one lesson.
- Window: seven days from sign-up, not calendar weeks.
- Comparison: the previous month's cohort.
- Cut-off: the change shipped on 1 February.
Each is a thing a stakeholder can disagree with before you spend a day on it, which is the point. The version in the first line is a thing nobody can disagree with and nobody can compute.
Write it as a one-sentence specification, every time:
SPEC = {
'question': 'Did the new onboarding increase week-one activation?',
'population': 'users who signed up in Feb 2026, verified, excluding internal',
'numerator': 'users with at least one lesson completed',
'denominator': 'all users in the population',
'window': '7 days from signup, per user',
'comparison': 'the same measure for Jan 2026 signups',
'changes_my_mind': 'a difference of less than 2 percentage points',
}
width = max(len(k) for k in SPEC)
for key, value in SPEC.items():
print(f'{key:<{width}} {value}')
Open this example in the editor and change it. Nothing you type is saved.
That dictionary is six lines and it is the deliverable of the first hour. Put it at the top of the notebook and in the message where you share the result.
Writing the definition down before you compute
The trap this avoids is the one where the number changes and nobody can say whether the world changed or the query did.
import pandas as pd
import numpy as np
rng = np.random.default_rng(5)
n = 2000
users = pd.DataFrame({
'user_id': np.arange(1, n + 1),
'signed_up': pd.Timestamp('2026-01-01') + pd.to_timedelta(rng.integers(0, 59, n), 'D'),
'verified': rng.random(n) < 0.86,
'internal': rng.random(n) < 0.03,
})
users['activated_days'] = np.where(
rng.random(n) < 0.42, rng.integers(0, 20, n), np.nan)
# The population, as specified - and the count at every step, so a
# reader can see what each exclusion cost.
step = {'all signups': len(users)}
population = users[users['signed_up'] < '2026-03-01']
step['in the period'] = len(population)
population = population[~population['internal']]
step['external only'] = len(population)
population = population[population['verified']]
step['verified only'] = len(population)
for label, count in step.items():
print(f'{label:<16} {count:>6,}')
print()
# The measure, as specified: activated WITHIN SEVEN DAYS.
population = population.assign(
activated=lambda d: d['activated_days'].notna() & (d['activated_days'] <= 7),
month=lambda d: d['signed_up'].dt.to_period('M').astype(str),
)
result = population.groupby('month', as_index=False).agg(
users=('user_id', 'size'),
activated=('activated', 'sum'),
)
result['rate'] = (result['activated'] / result['users'] * 100).round(1)
print(result.to_string(index=False))
print()
jan, feb = result.set_index('month')['rate']
print(f'January {jan}%, February {feb}%, difference {feb - jan:+.1f} points')
Open this example in the editor and change it. Nothing you type is saved.
Notice the counts at each exclusion. "We excluded internal accounts" is a sentence; "we excluded 61 internal accounts, 3% of sign-ups" is a fact somebody can check. If the February exclusion were 30% rather than 3%, that is a data problem the headline number would have hidden.
The denominator decides the answer
import pandas as pd
import numpy as np
rng = np.random.default_rng(9)
n = 1000
users = pd.DataFrame({
'user_id': np.arange(n),
'verified': rng.random(n) < 0.70,
'activated': rng.random(n) < 0.30,
})
# Only verified users can activate, which is the usual arrangement.
users.loc[~users['verified'], 'activated'] = False
denominators = {
'all signups': len(users),
'verified signups': int(users['verified'].sum()),
}
activated = int(users['activated'].sum())
for label, denominator in denominators.items():
print(f'{label:<18} {activated}/{denominator} = {activated / denominator * 100:.1f}%')
Open this example in the editor and change it. Nothing you type is saved.
Same numerator, two defensible denominators, and the answers differ by a third. Neither is wrong; what is wrong is reporting one without saying which, because next quarter somebody will compute the other and the trend will look like a change that never happened.
The output is a sentence
An analysis is not a table. It is a sentence a decision can hang on, with the table underneath as evidence.
jan_rate, feb_rate = 36.4, 41.2
jan_n, feb_n = 612, 588
print(
f'Week-one activation rose from {jan_rate}% to {feb_rate}% '
f'({feb_rate - jan_rate:+.1f} points) between the January and February '
f'sign-up cohorts (n={jan_n:,} and {feb_n:,}), measured as the share of '
f'verified external sign-ups completing at least one lesson within seven '
f'days. The onboarding change shipped on 1 February, so the February '
f'cohort is the first fully exposed to it.'
)
print()
print('What would change this conclusion:')
for caveat in [
'a change in what counts as "verified" between the two months',
'a marketing campaign that changed who was signing up',
'the seven-day window overlapping a holiday in one month and not the other',
]:
print(' -', caveat)
Open this example in the editor and change it. Nothing you type is saved.
Three parts, and all three matter. The number with its units and its sample size. The definition, in one clause. The things that would make it wrong, which is what distinguishes a finding from a claim.
A worked example
import numpy as np
import pandas as pd
rng = np.random.default_rng(seed=71)
# ---- The specification, written first. ------------------------------
SPEC = {
'question': 'Did the checkout change increase completed orders per session?',
'population': 'sessions from external users, Jan-Mar 2026, excluding bots',
'numerator': 'sessions containing at least one completed order',
'denominator': 'all sessions in the population',
'comparison': 'the month before the change against the month after',
'shipped': '2026-02-15',
'changes_my_mind': 'a difference under 1 percentage point, or n < 500 either side',
}
for key, value in SPEC.items():
print(f'{key:<18} {value}')
print()
# ---- The data. ------------------------------------------------------
n = 12_000
sessions = pd.DataFrame({
'session_id': np.arange(n),
'started': pd.Timestamp('2026-01-01') + pd.to_timedelta(rng.integers(0, 89, n), 'D'),
'internal': rng.random(n) < 0.04,
'bot': rng.random(n) < 0.07,
})
# The real effect being measured: a small lift after the change date.
after = sessions['started'] >= '2026-02-15'
base = np.where(after, 0.185, 0.160)
sessions['ordered'] = rng.random(n) < base
# ---- The population, counted at every exclusion. --------------------
counts = {'all sessions': len(sessions)}
population = sessions[~sessions['bot']]
counts['not a bot'] = len(population)
population = population[~population['internal']]
counts['external'] = len(population)
for label, count in counts.items():
print(f'{label:<14} {count:>7,}')
dropped = counts['all sessions'] - counts['external']
print(f'{"excluded":<14} {dropped:>7,} ({dropped / counts["all sessions"]:.1%})')
print()
# The exclusion rate must be similar on both sides of the change, or
# the comparison is between two different populations.
population = population.assign(period=np.where(
population['started'] >= SPEC['shipped'], 'after', 'before'))
excl = (sessions.assign(period=np.where(sessions['started'] >= SPEC['shipped'], 'after', 'before'))
.groupby('period')[['bot', 'internal']].mean().round(4))
print('exclusion rates by period:')
print(excl)
print()
# ---- The measure. ---------------------------------------------------
result = (population.groupby('period', as_index=False)
.agg(sessions=('session_id', 'size'), ordered=('ordered', 'sum')))
result['rate'] = result['ordered'] / result['sessions']
result = result.set_index('period').loc[['before', 'after']].reset_index()
print(result.assign(rate=lambda d: (d['rate'] * 100).round(2)).to_string(index=False))
print()
before_rate = float(result.loc[result.period == 'before', 'rate'].iloc[0])
after_rate = float(result.loc[result.period == 'after', 'rate'].iloc[0])
difference = (after_rate - before_rate) * 100
# ---- The threshold the spec set, applied. ---------------------------
n_before = int(result.loc[result.period == 'before', 'sessions'].iloc[0])
n_after = int(result.loc[result.period == 'after', 'sessions'].iloc[0])
big_enough = n_before >= 500 and n_after >= 500
meaningful = abs(difference) >= 1.0
print(f'difference {difference:+.2f} points, n={n_before:,} and {n_after:,}')
print(f'sample large enough: {big_enough}')
print(f'passes the threshold set in advance: {meaningful}')
print()
# ---- The sentence. --------------------------------------------------
verdict = 'rose' if difference > 0 else 'fell'
print(
f'Order rate per session {verdict} from {before_rate:.2%} to {after_rate:.2%} '
f'({difference:+.2f} points) between the month before and the month after '
f'the checkout change on {SPEC["shipped"]}, measured over external '
f'non-bot sessions (n={n_before:,} and {n_after:,}).'
)
print()
print('What would change this conclusion:')
for caveat in [
'the before and after periods differ in traffic mix, not just in checkout',
'the bot filter changed behaviour between the periods',
'something else shipped in the same window',
]:
print(' -', caveat)
Open this example in the editor and change it. Nothing you type is saved.
The step worth copying is the exclusion-rate comparison. A before-and-after measurement is only valid if the two populations were filtered the same way; if the bot rate doubled in February, the "lift" could be entirely a change in who got counted. That check costs three lines and it is the one a reviewer will ask about.
When it goes wrong
| Symptom | Cause |
|---|---|
| Two people get different numbers | The definition was never written down |
| The metric moved and nobody knows why | The query changed, not the world |
| The result is "interesting" but unusable | No decision was attached to it |
| A before/after comparison is suspect | The populations were filtered differently |
| An exclusion removed most of the data | Nobody counted what each filter cost |
| The analysis confirmed what was expected | No falsifying condition set in advance |
| The denominator is ambiguous | "Rate" without saying of what |
| A caveat emerged in the meeting | Caveats belong with the result, not after it |
A check you can run
Take the last analysis you shared and write its specification as the six-key dictionary above - question, population, numerator, denominator, window, comparison.
If you cannot fill one of them from memory, nobody reading the result could have either. Fill it in, re-run, and see whether the number changed. In my experience it does roughly a third of the time, and the gap is always in the population.