Most "AI for data analysis" content is either too generic ("ask it to explain your data!") or too narrow ("here's a prompt that writes a GROUP BY"). Neither survives contact with a real Monday morning.
Below are 10 prompts I actually use as an analyst. Each one has the structure, the constraints that make it produce usable output instead of a confident-sounding hallucination, and a real example.
The pattern, as always: Role → Context → Constraints → Output format. The constraints are where the magic is. A prompt without constraints gives you a Wikipedia summary. A prompt with sharp constraints gives you a first draft you can ship.
1. SQL query from a business question (the one that doesn't hallucinate columns)
Role: You are a senior analyst writing SQL against a known schema.
Context: Business question: {question}. Schema (tables + columns + types): {DDL}. SQL dialect: {postgres/mysql/bigquery/snowflake}.
Constraints:
- Only reference columns that exist in the schema I pasted. If the question needs a column that doesn't exist, say so explicitly — do not invent one.
- Prefer CTEs over subqueries for readability.
- Every JOIN must specify the join condition. No implicit joins.
- Wrap column names with spaces or reserved words in double quotes.
- If the query needs a window function, use it; do not fake it with a self-join.
- Add a one-line comment above each CTE explaining what it returns.
Output: The SQL, then a 2-sentence explanation of the approach.
Why this works: Pasting the schema and constraining the model to it is the difference between a query that runs and a query that references a user_signups table you don't have.
2. Exploratory analysis plan for an unfamiliar dataset
Role: You are a senior data scientist planning the first 60 minutes of EDA on a new dataset.
Context: Dataset description: {what it is}. Columns + types: {list}. Row count: {approx}. Business context: {why we care}.
Constraints:
- Output a numbered list of 8-10 specific analyses to run, in order.
- For each, state the question it answers and the chart type to use.
- The first three analyses must be data-quality checks (nulls, dupes, ranges), not business questions.
- Flag any column that looks like a free-text field and propose how to handle it.
- Do not propose machine learning in the first 60 minutes.
Output: An EDA plan, ordered.
3. Variance explanation for a non-technical stakeholder
Role: You are explaining a metric change to an executive who does not want to see SQL.
Context: Metric: {metric}. Change: {from X to Y, over period Z}. Driver breakdown (if known): {segment-level deltas}.
Constraints:
- First sentence states the direction and magnitude in plain English ("Revenue dropped 12% last week").
- Second sentence names the single biggest driver, as a share of the change.
- Use a "what changed / what didn't / what we don't know yet" structure.
- No p-values, no statistical jargon, no "the data suggests."
- If a segment moved in the opposite direction of the headline, call it out — do not bury it.
Output: A 5-sentence summary.
Example (revenue dropped 12%):
Revenue dropped 12% last week, from $84k to $74k. The drop is almost entirely driven by the mid-tier plan, which fell 31%; enterprise and free-tier were flat. We did not lose customers — the mid-tier contraction was driven by 8 accounts downgrading to free. What we don't know yet is whether this is a reaction to the price change two weeks ago or a coincidence. I'm pulling the downgrade reasons from the exit survey today.
4. Anomaly investigation checklist
Role: You are an analyst who just got a spike/drop alert and needs to triage it.
Context: Metric: {metric}. Anomaly: {spike/drop of X% at time T}. Alerting system: {what fired}.
Constraints:
- Produce a checklist of 6-8 investigation steps, ordered by "most likely cause" to "least likely."
- Step 1 must be a data-pipeline check (did the data actually arrive? is the anomaly real or a pipeline gap?).
- Include a "what would confirm this is not real" check.
- For each step, state what result would point to that cause.
- Do not recommend "monitor it" as a step — that's not an investigation.
Output: A numbered triage checklist.
5. Metric definition document
Role: You are writing the canonical definition for a business metric so that finance, product, and analytics stop arguing about it.
Context: Metric name: {name}. Intent: {what it's supposed to capture}.
Constraints:
- Sections: Definition / Inclusions / Exclusions / Edge cases / Refresh cadence / Owner.
- "Inclusions" and "Exclusions" must each have at least 4 concrete examples.
- "Edge cases" must cover: refunds, trials, internal/test accounts, cross-currency, timezone.
- The definition sentence must be one sentence that a non-analyst can repeat back.
- Name the system of record (which table/view is authoritative).
Output: A metric definition doc, ready to drop into the metrics catalog.
6. Dashboard critique (the one that tells you what to cut)
Role: You are a senior analyst reviewing a dashboard for usefulness.
Context: Dashboard purpose: {stated purpose}. Charts: {list each chart + what it shows}. Audience: {who looks at it}.
Constraints:
- For each chart, state: does it answer a decision? If not, recommend cutting it.
- Identify any chart that duplicates information already on the dashboard.
- Flag any chart where the audience would not be able to interpret the axis or filter.
- Propose 1-2 charts that are missing and would actually drive a decision.
- Do not recommend "make it more visually appealing" — that is not the problem.
Output: A chart-by-chart review with cut/keep/add recommendations.
7. Cohort analysis design
Role: You are designing a cohort analysis to answer a retention question.
Context: Question: {are monthly users retained better than weekly? / does the signup source matter? / etc.}. Available data: {user events, signup dates, etc.}.
Constraints:
- Define the cohorting key (what groups users into a cohort).
- Define the cohort size (weekly? monthly? why).
- Define the retention metric (D7? D30? active-in-window? why).
- State the comparison you're going to make and the decision it would drive.
- Call out the selection bias risk — what kind of user is over-represented in this cohort definition?
Output: A cohort spec, not the query.
8. A/B test results summary (that doesn't overclaim)
Role: You are summarizing an A/B test for a product team.
Context: Hypothesis: {what we tested}. Variant: {what changed}. Samples: {n per arm}. Result: {lift, confidence interval, p-value}.
Constraints:
- State whether the result is statistically significant, full stop.
- If significant, state the effect size in the original units (not just "% lift").
- Report the confidence interval, not just the point estimate.
- Name one segment where the effect was different (if sliced). If not sliced, say so.
- Explicitly state what this test does NOT prove (no causal claims beyond the intervention).
Output: A 4-sentence summary + 1-sentence recommendation.
Example:
The new checkout flow increased conversion from 3.1% to 3.4% (relative lift of +9.7%, 95% CI [+2.1%, +17.9%], p=0.012). This is statistically significant. The effect was consistent across desktop and mobile. This test proves the new flow converts better; it does not prove why, and it does not guarantee the lift holds at higher traffic.
9. Data quality report for a new table
Role: You are profiling a new table before anyone else uses it.
Context: Table: {name}. Columns + types: {list}. Sample rows: {paste 5 rows}.
Constraints:
- For each column, report: null rate (if estimable from sample), distinct values, min/max or top-3 values.
- Flag any column with inconsistent formatting (dates in two formats, mixed case IDs).
- Flag any column that looks like it should be unique but isn't.
- Identify likely foreign keys and state whether they appear to join to the expected parent table.
- Rate overall trust: production-ready / use-with-caveats / do-not-use-yet.
Output: A profiling report with a trust rating.
10. Stakeholder question → analysis plan (the de-scoping prompt)
Role: You are a senior analyst responding to a vague stakeholder ask.
Context: The ask (verbatim): {paste}. Stakeholder: {role}. Timeline: {how much time they have}.
Constraints:
- Restate the question in your own words to confirm understanding.
- Identify the 2-3 sub-questions buried in the vague ask.
- For each sub-question, state whether the data exists to answer it.
- Propose the minimum viable analysis (the 20% that answers 80% of the question).
- Explicitly name what you are NOT going to do, and why (data doesn't exist / not worth the time / out of scope).
Output: A reply to the stakeholder that confirms scope and de-scopes gracefully.
How I test analysis prompts
- Run it on a dataset you already understand. If the prompt's EDA plan misses something you know is in the data, the constraints are too loose.
- Check the SQL against an actual schema. Hallucinated columns are the #1 failure mode. If you don't paste the schema, you will get invented table names.
- Read the stakeholder summary aloud. If it sounds like a data scientist wrote it for another data scientist, rewrite the constraints to ban jargon.
The library these came from
These prompts are part of a larger library I've built and tested across real analysis, product, and engineering work. If you want the full set:
- Developer Product-Launch Prompt Pack ($9) — 7 prompts for launching data-informed products: gum.co/qevvy
- Developer Productivity Prompt Library ($49) — 30 prompts including data + engineering workflows: gum.co/plytri
- SaaS Marketing Copy Prompt Pack ($49) — 25 prompts for analytics-informed marketing: gum.co/orcwxs
- AI Startup Operations Prompt System ($199) — 50+ prompts + 10 workflows for running a data-driven company: gum.co/aeqnd
- AI Business Transformation Playbook ($999) — 100+ prompts + 12 workflows, the full system: gum.co/boivub
The $9 pack is the cheapest entry point. If it saves you one round of "what column is that again?" it's paid for itself.
The structure is the asset, not the specific prompts. Once Role → Context → Constraints → Output is muscle memory, you'll write your own for every repeatable part of your analytical workflow. The 10 above are the ones that have survived actual weekly use.
If you've got an analysis prompt that outperforms these, I want to see it — the best prompts always come from people who run queries every day.
Top comments (0)