Prompt: Design a Pivot Table Summary

Designs the exact rows, columns and values for a pivot table that answers one business question, instead of a vague suggestion to pivot the data.

The prompt

Open in Studio

You are a data analyst who designs pivot tables that answer one specific question, not a general-purpose summary of everything in the sheet.

Objective: Design a pivot table that answers: {{BUSINESS QUESTION — e.g. which region had the highest average order value each quarter?}}

Context: {{AVAILABLE COLUMNS — e.g. order_date, region, product_category, order_value, customer_id}}.

Constraints:
- Specify exactly one field (or one derived field, named explicitly) for Rows, exactly one for Columns, and exactly one for Values, plus its aggregation (sum, average, count)
- Only use columns named in the available columns above; if a needed field must be derived (e.g. quarter from a date), say so explicitly and describe how
- Do not propose more than one pivot table; if the question genuinely needs two views, say so as a limitation instead of adding a second table
- State one filter to apply, if the question implies one, or state explicitly that no filter is needed

Example: "average order value by region and quarter" needs Rows: region, Columns: quarter (derived from order_date), Values: average of order_value.

Output format: Return four labelled lines — Rows, Columns, Values, Filter — followed by one sentence stating what the finished pivot table would show.

Before finishing, check that every field referenced is either in the available columns or explicitly marked as derived, with the derivation explained.

Example

Input

BUSINESS QUESTION — e.g. which region had the highest average order value each quarter?: which region had the highest average order value each quarter?
AVAILABLE COLUMNS — e.g. order_date, region, product_category, order_value, customer_id: order_date, region, product_category, order_value, customer_id

Output

Rows: region
Columns: quarter — derived from order_date by grouping the date into calendar quarters (Q1–Q4)
Values: average of order_value
Filter: none needed; the question asks about all regions and all quarters in the dataset

The finished pivot table would show, for each region, the average order value in each calendar quarter, letting you read across each row to see how a region's average order value moved quarter by quarter and compare that pattern against other regions in the same period.

Why it works

Fixing exactly one field per Rows, Columns and Values slot keeps the pivot answering the one question asked, not becoming an unfocused catch-all table.
Requiring an explicit derivation for any calculated field (like quarter from a date) stops a silently wrong grouping from entering the summary.
The pre-finish check re-verifies every referenced field exists or is explicitly derived, catching a field that doesn't actually exist in the data.

Why the basic version fails

“Make me a pivot table of this data” with no specific question produces either an overloaded table with too many fields to read, or a generic summary that doesn’t actually answer the question you had in mind when you exported the data.

What this prompt fixes

Tying the design to one specific business question, with exactly one field in each of Rows, Columns and Values, keeps the resulting table readable and directly answers what was asked instead of showing everything at once. Requiring an explicit derivation for calculated fields (like turning a date into a quarter) is what stops a silent grouping mistake, such as grouping by calendar year when the business actually reports on a different fiscal year.

Tips per model

ChatGPT is good at suggesting the right aggregation (sum versus average) once the question is specific; a vague question tends to get a default that may not be what you need.
Claude tends to flag when a single pivot table genuinely can’t answer a two-part question, which is a useful signal to split the analysis rather than force it into one view.
If your organisation uses a non-calendar fiscal year, say so explicitly in the business question — a default quarter grouping will otherwise use calendar quarters.