AI prompts for data analysts

Help me understand this dataset before I analyse it.

DATA:
"""
[PASTE A SAMPLE, INCLUDING HEADERS]
"""

WHAT IT IS: [WHERE IT CAME FROM, WHAT IT MEASURES]
ROW COUNT: [TOTAL]
PERIOD COVERED: [DATES]
WHAT I WANT TO FIND OUT: [YOUR QUESTION]

Produce:

1. WHAT EACH COLUMN IS - your reading of each field: what it measures, its type, and its likely unit. Flag any column whose meaning is unclear from the name; ambiguous fields are where analysis goes wrong.

2. THE QUESTIONS TO ASK ABOUT THE DATA before analysing:
   - How was it collected, and what does that exclude? Every dataset has a population it cannot see.
   - What does a row represent, exactly? One customer, one order, one order line, one event?
   - Is it complete for the whole period, or does coverage change partway through?
   - Are there records that should be excluded: tests, internal users, cancelled, refunded, duplicates?

3. THE CHECKS TO RUN FIRST - specific to this data: row count, distinct counts on key fields, date range and gaps, null counts per column, and the distribution of the main numeric fields. Give the commands or formulas.

4. WHAT I EXPECT TO BE WRONG - based on the sample: likely duplicates, mixed formats, outliers, impossible values, and fields that are null more often than they should be.

5. THE DEFINITION PROBLEMS - where the analysis will depend on a definition that is not in the data. 'Active user' and 'churned' do not exist as facts; they are decisions. Name the ones my question requires.

6. WHETHER THIS DATA CAN ANSWER MY QUESTION - honestly. Frequently the answer is that it can answer a narrower question, or that a confounder makes the intended question unanswerable. Say so now rather than after the analysis.

7. WHAT IS MISSING that would make this analysis solid.

Do not analyse yet. Understand the data first.

Write analysis code.

LANGUAGE AND LIBRARIES: [Python/pandas / R / SQL / other]
DATA:
"""
[PASTE SAMPLE ROWS WITH HEADERS]
"""
FILE FORMAT AND SIZE: [CSV/Excel/database, and how large]
WHAT I WANT TO WORK OUT: [YOUR QUESTION]
OUTPUT I WANT: [numbers / table / chart / file]

Produce code that:

1. LOADS THE DATA with explicit types. Never rely on type inference for IDs, dates or anything with leading zeros. Specify dtypes and date parsing explicitly, and say why.

2. VALIDATES BEFORE ANALYSING - and prints the results so I can see them:
   - Row count loaded, versus expected
   - Null counts per column
   - Date range
   - Distinct counts on key fields
   - Any rows failing a basic plausibility check (negative quantities, dates in the future, impossible values)
   Stop and report rather than silently proceeding if something is wrong.

3. CLEANS EXPLICITLY - every cleaning step in its own line with a comment saying what and why, and printing how many rows it affected. Never drop rows silently; a filter that removes 40% of the data should be visible.

4. ANSWERS THE QUESTION - the analysis itself, with intermediate results printed rather than one chained expression. I need to be able to check the middle steps.

5. PRINTS THE ARITHMETIC - for any headline number, show the components. A single figure I cannot decompose is a figure I cannot defend.

6. HANDLES THE EDGE CASES - empty groups, division by zero, missing values in an average, and groups with too few rows to be meaningful.

Then, separately from the code:

7. THE ASSUMPTIONS the code makes about my data.
8. WHAT WOULD MAKE THE ANSWER WRONG - the data condition that would produce a plausible but incorrect result.
9. THE CHECK - a second, independent way to compute the headline number, so I can verify it.

Rules:
- Comment why, not what
- No chained one-liners for the main analysis; readable steps
- Do not use inplace operations or overwrite the source dataframe
- If the data is large enough that the approach matters, say so and adjust

Write a SQL query.

DATABASE: [Postgres / MySQL / other + version]

SCHEMA:
```sql
[PASTE CREATE TABLE STATEMENTS OR COLUMN LISTS]
```

WHAT I WANT TO KNOW: [PLAIN ENGLISH DESCRIPTION]
EDGE CASES THAT MATTER: [e.g. what to do about rows with no match, duplicates, nulls]
EXPECTED RESULT SIZE: [ROUGHLY HOW MANY ROWS]

Produce:

1. RESTATE THE REQUEST precisely, resolving ambiguity. Specifically: does 'per customer' mean per customer row or per customer who has at least one order? Should customers with zero count appear with 0 or be absent? Are ties in a ranking broken, and how? Do nulls count?
   If my description is ambiguous on any of these, state the interpretation you are using.

2. THE QUERY - formatted readably, with CTEs rather than nested subqueries where it helps.

3. HOW IT WORKS - step by step, following the logical order of evaluation, not the written order.

4. THE NULL AND DUPLICATE ANALYSIS - where nulls could change the result (they behave counterintuitively in NOT IN, in aggregates, and in joins), and whether any join could multiply rows. This is where most SQL is silently wrong.

5. VERIFICATION - a small worked example: sample input rows and what the query returns for them, including an edge case. Enough for me to check the logic without a database.

6. PERFORMANCE - what indexes it wants and whether it will scale to my stated result size.

7. ALTERNATIVE FORMULATION - if there is a meaningfully different way to write this (window function vs self-join, EXISTS vs IN), show it and say when each is preferable.

Rules:
- Never use NOT IN with a subquery that could return null. Use NOT EXISTS and say why.
- Show the interpretation you chose for every ambiguity rather than picking silently.

Write a spreadsheet formula.

SOFTWARE: [Excel / Google Sheets / other] and VERSION: [if Excel, which version - it determines available functions]

MY DATA LAYOUT:
"""
[PASTE A FEW ROWS INCLUDING HEADERS, WITH COLUMN LETTERS]
"""

WHAT I WANT TO CALCULATE: [PLAIN DESCRIPTION]
WHERE THE FORMULA GOES: [CELL]
WILL I DRAG IT DOWN OR ACROSS: [yes/no, and which]

Produce:

1. THE FORMULA - with correct absolute and relative references for how I said I will fill it.

2. HOW IT WORKS - broken into its parts, inside out, in plain words.

3. WHAT IT ASSUMES about my data: no blanks, consistent types, sorted order, exact matches. Say which assumptions it relies on.

4. WHERE IT WILL BREAK - blank cells, text stored as numbers, trailing spaces, duplicate lookup values, inconsistent date formats, and merged cells. Say which apply to my data and how to guard against them.

5. THE ERROR HANDLING VERSION - wrapped so it returns something useful instead of #N/A or #DIV/0!, and say what it should return instead.

6. THE ALTERNATIVE - if there is a better function for this in my version (XLOOKUP over VLOOKUP, FILTER over array formulas, LET for readability), show it and say when each is preferable. Note if a function I might see suggested elsewhere is not available in my version.

7. A WORKED EXAMPLE - the formula applied to my sample rows, with the result, so I can verify it before trusting it.

Rules:
- Never use VLOOKUP with a column index number if XLOOKUP or INDEX/MATCH is available; column insertion silently breaks it.
- Avoid whole-column references in large sheets.
- If the task would be better done with a pivot table or Power Query than a formula, say so.

Help me summarise this data.

DATA STRUCTURE:
"""
[PASTE HEADERS AND A FEW ROWS]
"""
ROW COUNT: [HOW MANY]
SOFTWARE: [Excel / Sheets]
WHAT I WANT TO KNOW: [YOUR QUESTIONS]

Produce:

1. IS THE DATA READY - pivot tables need clean tabular data: one header row, no blank rows or columns, no merged cells, no totals inside the data, and consistent types per column. Assess my sample and list anything to fix first.

2. FOR EACH QUESTION - the pivot configuration: what goes in rows, columns, values and filters, and which aggregation (sum, count, average, distinct count). Say what each would show.

3. THE STEP-BY-STEP - how to build it in my software, with the actual menu path.

4. THE AGGREGATION TRAP - flag where the obvious aggregation is misleading. Averaging an average is almost always wrong. Counting rows is not counting customers if customers appear more than once. Summing a percentage column is meaningless. Say which applies here.

5. THE CALCULATED FIELD - if a question needs a ratio or a derived measure, show how to add it, and warn that a calculated field computes on the aggregated totals rather than row by row, which changes the answer for ratios.

6. WHAT THE SUMMARY WILL HIDE - pivot tables average away the interesting cases. Note where the distribution matters more than the total, and what to look at alongside it.

7. THE ALTERNATIVE - if a formula approach (SUMIFS, COUNTIFS, or QUERY in Sheets) would be better because the output needs to update or feed another calculation, say so and give it.

8. THE SANITY CHECK - after building it, how to verify the pivot is right: does the grand total match a separate SUM of the source column?

Always include point 8. A pivot table that silently excludes rows is easy to build and hard to notice.

Help me choose a chart.

DATA:
"""
[PASTE OR DESCRIBE - INCLUDE HOW MANY CATEGORIES, TIME POINTS, OR SERIES]
"""

WHAT I WANT THE VIEWER TO SEE: [THE POINT YOU ARE MAKING]
AUDIENCE: [WHO, AND HOW CLOSELY THEY WILL LOOK]
WHERE IT APPEARS: [slide / report / dashboard / print]
TOOL: [Excel / Sheets / Python / other]

Produce:

1. THE COMPARISON TYPE - what relationship are you showing? Comparison between categories, change over time, part of a whole, distribution, correlation, or ranking. The chart follows from this, not from preference.

2. THE RECOMMENDATION - one chart type, with the reason, and how to configure it for my data.

3. WHY NOT THE ALTERNATIVES - the two other plausible choices and what they would do worse here.

4. THE SPECIFIC WARNINGS for my data:
   - Pie chart with more than about five slices, or where the point is comparison rather than proportion - people cannot compare angles
   - Dual axes, which imply a relationship that may not exist and can be manipulated to show anything
   - Truncated y-axis on a bar chart, which misrepresents magnitude. On a line chart showing change it can be legitimate; say which applies.
   - Too many series on one line chart; over about five, nothing is readable
   - 3D effects, which distort every comparison
   - Stacked bars when the reader needs to compare the middle segments, which is impossible without a common baseline

5. THE CONFIGURATION - axis ranges and whether to start at zero, sort order (almost always by value, not alphabetically), labels directly on the data rather than in a legend where possible, and how many decimal places.

6. WHAT TO REMOVE - gridlines, borders, background fills, redundant legends, and decoration. Everything that is not data or a label needed to read it.

7. THE TITLE - written as the finding, not the subject. 'Returns doubled after the March packaging change' beats 'Returns by month'. This single change does more for comprehension than any formatting.

8. THE HONEST CHECK - does my data actually show what I want the viewer to see? If the effect is within normal variation, or the sample is small, the chart will overstate it. Say so.

9. WHEN A TABLE IS BETTER - for precise values, few data points, or mixed units. Say if that applies here.

Critique this chart.

DESCRIPTION OF THE CHART: [TYPE, WHAT IS ON EACH AXIS, WHAT THE SERIES ARE, THE AXIS RANGES, COLOURS USED]
OR PASTE THE UNDERLYING DATA:
"""
[PASTE]
"""
WHAT IT IS MEANT TO SHOW: [THE POINT]
AUDIENCE: [WHO]
WHERE IT WILL APPEAR: [CONTEXT]

Assess:

1. WHAT A VIEWER WOULD CONCLUDE at a glance, before reading anything. Compare that to what I said it is meant to show. Divergence is the main finding.

2. MISLEADING ELEMENTS:
   - Axis truncation exaggerating a difference
   - Dual axes implying a relationship
   - Inconsistent intervals on an axis
   - A cherry-picked date range
   - Area or volume used for a linear quantity, which exaggerates by squaring or cubing
   - Aggregation hiding a reversal in the underlying segments
   - Missing baseline or context that would change the interpretation

3. READABILITY:
   - Can every label be read at the size it will appear?
   - Is the sort order meaningful?
   - Does it need a legend, or could labels go on the data?
   - Are there more colours than necessary?
   - Does it work in greyscale, and for colour-blind viewers? Red-green pairs are the common failure.

4. THE DATA-INK CHECK - what is on this chart that is not data and not needed to read it?

5. WHAT IS MISSING - sample sizes, units, the time period, the data source, a note on any exclusions, or an uncertainty indicator where the data is noisy.

6. THE TITLE - does it state the finding? If it names the subject instead, rewrite it.

7. THE HONEST QUESTION - does the data support the point strongly enough to make this chart? If the difference is small or the sample thin, the chart will make it look more solid than it is. This is the most common way honest people mislead.

8. THE REVISED SPECIFICATION - the chart as it should be.

Analyse these open-text responses.

RESPONSES:
"""
[PASTE]
"""

THE QUESTION THEY WERE ANSWERING: [EXACT WORDING]
WHO ANSWERED: [RESPONDENT DESCRIPTION AND HOW THEY WERE RECRUITED]
RESPONSE COUNT: [TOTAL, AND OUT OF HOW MANY ASKED]
WHAT I WANT TO LEARN: [PURPOSE]

Produce:

1. THE RESPONSE RATE CAVEAT - who answered the open question versus who was asked. People with strong feelings answer free-text questions; the silent majority is missing. State what this sample can support.

2. THE THEMES - with counts and the percentage of responses. For each: the theme, how many mentioned it, two or three representative verbatim quotes, and the range of intensity within it.

3. THE CODING FRAME - how you grouped responses, stated explicitly so I can check the judgement. Flag any response that could reasonably have gone in two themes.

4. FREQUENCY VERSUS INTENSITY - separate these. A theme mentioned by five people with great force is different from one mentioned in passing by forty. Both matter and they are different findings.

5. THE QUESTION WORDING EFFECT - assess whether the exact question wording steered the answers. If it asked 'what could be improved', you will get complaints regardless of overall satisfaction, and those complaints are not a satisfaction measure.

6. THE UNPROMPTED - things mentioned that the question did not ask about. These carry more weight than answers to the question, because nobody was steered toward them.

7. WHAT NOBODY SAID that you would expect. Note it, carefully, as an observation.

8. THE OUTLIERS - individual responses that are unusual and worth reading in full. Sometimes one person articulates something the themes flatten. Quote them.

9. THE LANGUAGE - the words respondents use for the thing you are asking about. Useful for future surveys, for marketing and for search.

10. THE FINDINGS ranked by confidence, with what each rests on.

Do not report a theme as a finding on the basis of two responses. State counts everywhere.

Analyse this data.

DATA:
"""
[PASTE]
"""

WHAT IT IS: [DESCRIPTION AND SOURCE]
MY QUESTION: [WHAT YOU WANT TO KNOW]
WHAT DECISION THIS INFORMS: [WHAT YOU WILL DO WITH IT]
WHAT I EXPECT TO FIND: [YOUR PRIOR - be honest]

Produce:

1. THE DESCRIPTIVE PICTURE first - counts, ranges, distributions, and time coverage. Do this before any comparison. Most questions are answered here.

2. THE ANSWER TO MY QUESTION - stated directly, with the numbers that support it and the calculation shown.

3. THE CONFIDENCE - how strongly the data supports this. Consider: sample size, whether the difference exceeds normal variation, whether the period is representative, and whether the comparison is like for like. Say plainly if the answer is 'this data cannot tell you'.

4. WHAT WOULD EXPLAIN THIS OTHER THAN MY HYPOTHESIS - alternative explanations for the pattern. At minimum consider: a change in what was measured or how, a composition change (the mix of things being averaged shifted), seasonality, an outlier driving the aggregate, and survivorship (you only see what remained).

5. THE COMPOSITION CHECK - run this specifically. An overall average can move while every subgroup stays flat, purely because the mix changed. This is Simpson's paradox and it is common enough to check every time.

6. THE OUTLIER CHECK - is the finding driven by a few extreme records? Show the result with and without them.

7. WHAT I EXPECTED VS WHAT IS THERE - I told you my prior. Where the data contradicts it, say so directly. Where the data is consistent with it, note that consistency is weaker evidence than it feels.

8. WHAT THIS DOES NOT SHOW - the limitations, specific to this dataset.

9. THE DECISION - given what I said I would do with this, does the analysis support that action? If the finding is real but too small to matter, say so.

Rules:
- Show the arithmetic for anything I might need to check
- Never say 'significant' without meaning it statistically and showing the basis
- Correlation is not causation; say 'associated with' and name what would establish causation

Help me find where this change came from.

WHAT CHANGED: [THE METRIC, AND FROM WHAT TO WHAT]
PERIOD: [WHEN]
DATA:
"""
[PASTE - INCLUDE THE DIMENSIONS YOU CAN SEGMENT BY]
"""
WHAT I ALREADY SUSPECT: [YOUR HYPOTHESIS, if any]
WHAT CHANGED IN THE BUSINESS: [ANY KNOWN CHANGES]

Produce:

1. THE DECOMPOSITION - break the overall change down by each available dimension. For each segment: its value before, after, the change, and its contribution to the total change. Sort by contribution.
   Contribution, not percentage change, is what matters: a segment that doubled but is 1% of the total explains nothing.

2. THE CONCENTRATED OR DIFFUSE QUESTION - is the change driven by a few segments or spread across all of them? This distinction determines the whole investigation. Concentrated points to something specific; diffuse points to something systemic like a measurement change or a platform-wide effect.

3. MIX VERSUS RATE - separate two effects that look identical in an aggregate:
   - The rate within segments changed
   - The mix of segments changed while each segment's rate held
   Quantify each. This is the single most useful decomposition in business analysis, and it frequently reverses the apparent conclusion.

4. NEW VERSUS EXISTING - where applicable, split the change between new entities appearing, existing ones changing, and ones disappearing. An overall figure holding steady while half the base churned and was replaced is a very different situation.

5. THE TIMING - when exactly did it start? A sharp step points to a specific event or a measurement change; a gradual slope points to a trend. Say which this looks like.

6. AGAINST MY HYPOTHESIS - does the decomposition support what I suspected? If the change is spread evenly across all segments and I suspected one specific cause, that is evidence against me.

7. THE MEASUREMENT CHECK - before concluding anything about the business, rule out that the measurement changed: tracking, definitions, data collection, or a source system. A step change across every segment simultaneously is usually a measurement artefact.

8. WHERE TO LOOK NEXT - the segment or question that would most narrow this down.

Help me interpret this result.

THE RESULT:
"""
[PASTE - THE NUMBERS, P-VALUES, CONFIDENCE INTERVALS, SAMPLE SIZES, WHATEVER YOU HAVE]
"""

WHAT WAS BEING TESTED: [THE QUESTION]
HOW THE DATA WAS COLLECTED: [METHOD]
WHAT DECISION THIS INFORMS: [WHAT YOU WILL DO]
WHAT I THINK IT MEANS: [YOUR READING]

Produce:

1. WHAT IT LITERALLY SAYS - in plain words, precisely. If there is a p-value, state what it actually means, which is not what most people think: the probability of seeing a result at least this extreme if there were genuinely no effect. It is not the probability that the hypothesis is true.

2. THE EFFECT SIZE - more important than significance and usually omitted. How big is the difference, in units that matter to the decision? A statistically significant effect can be far too small to act on.

3. THE CONFIDENCE INTERVAL - if given, what range of true values is consistent with this data. If the interval spans from 'barely worth doing' to 'transformative', the honest answer is that you do not yet know.

4. THE SAMPLE SIZE QUESTION - was this large enough to detect the effect it claims? An underpowered study that finds an effect tends to overstate its size.

5. WHAT COULD PRODUCE THIS RESULT OTHER THAN A REAL EFFECT:
   - Multiple comparisons: how many things were tested? Test twenty and one will look significant by chance.
   - Selection: who was included, who was excluded, and who dropped out
   - Timing and seasonality
   - A confounder correlated with both the treatment and the outcome
   - Stopping when the result looked good

6. WHAT IT DOES NOT SHOW - especially: whether it generalises beyond this sample, whether the relationship is causal, and whether it will persist.

7. MY READING, ASSESSED - I said what I think it means. Where I am over-reading, say so directly.

8. THE DECISION - given what I said I would do, is this result strong enough to act on? Three honest options: act, gather more data, or act anyway because the cost of being wrong is low. Say which and why.

Do not make the result sound more conclusive than it is.

Help me communicate what this data shows.

THE TABLE:
"""
[PASTE]
"""

WHAT IT IS: [WHAT THE NUMBERS MEASURE]
WHO NEEDS TO UNDERSTAND IT: [AUDIENCE AND THEIR NUMERACY]
WHAT THEY WILL DO WITH IT: [DECISION OR ACTION]
FORMAT: [email / slide / report / conversation]

Produce:

1. WHAT THE DATA ACTUALLY SHOWS - the two or three things a careful reader would take from this table, in order of importance. Not everything; the important things.

2. THE HEADLINE - one sentence containing the single most important finding and its number.

3. THE NUMBERS THAT MATTER - of everything in this table, which three or four should the audience remember? Strip the rest. A table with forty cells communicates nothing; four numbers communicate something.

4. THE COMPARISON THAT MAKES THEM MEANINGFUL - a number alone means nothing. For each key figure, what should it be compared to: last period, a target, a benchmark, or another segment? Supply the comparison.

5. THE SCALE TRANSLATION - where a number is hard to grasp, translate it into something familiar: per customer, per day, as a proportion, or as an equivalent the audience already understands. Do this only where it is honest and not where it inflates.

6. THE VISUAL - if one chart would help, which and why. If a chart would not help, say so; sometimes three numbers in a sentence beat a chart.

7. THE SIMPLIFIED TABLE - if a table is still the right format, the reduced version: fewer rows, fewer columns, sorted meaningfully, rounded sensibly, with the important cells emphasised.

8. THE WRITTEN EXPLANATION - for my stated format, the prose version. Two paragraphs maximum.

9. WHAT THE AUDIENCE WILL MISREAD - given their numeracy, the specific misinterpretation to pre-empt. Percentages of small bases and rate-versus-count confusion are the usual ones.

10. WHAT I AM LEAVING OUT, and whether any of it changes the conclusion. If something omitted would alter the reading, it must be included.

Round sensibly. Precision beyond what the data supports signals false confidence.