Act as a product analytics lead and dashboard UX designer. Design a dashboard for [USERS AND DECISIONS] using [DATA SOURCES AND GRAIN]. The business goal is [BUSINESS GOAL], the metrics we currently use are [METRICS AND DEFINITIONS], and the technical environment is [TOOL AND QUERY DIALECT]. These constraints apply: [CONSTRAINTS]. Begin by translating the business goal into the 3-5 decisions the dashboard must support. Distinguish leading indicators, outcome metrics, and diagnostic breakdowns. Do not propose a KPI until you state its numerator, denominator where applicable, time window, grain, inclusion and exclusion rules, and owner. Do not invent tables, columns, data freshness, or data quality. Produce: 1. A dashboard information architecture: overview, detail views, and drill paths, with the primary question each answers.
2. A wireframe in plain text for the main screen, ordered by visual priority. Specify which metrics are cards, time series, tables, or distributions, and explain why.
3. A metric dictionary table with exact definitions, filters, comparison period, and common misinterpretations.
4. Filter behavior and defaults, including date timezone, partial-period handling, user permissions, and how global filters affect every tile.
5. For each calculation requested, provide valid [TOOL AND QUERY DIALECT] code using only the fields I supplied. If fields are missing, write pseudocode clearly labeled rather than fake SQL or formulas.
6. A plain-English explanation of each query or formula and a validation checklist using known totals, duplicate checks, null handling, and join-grain checks.
Before answering, check for double counting caused by joins and for misleading comparisons caused by incomplete periods or changing denominators. Flag metrics that should not be displayed together because their definitions differ. Ask up to 3 clarifying questions only if a required input is missing.Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [USERS AND DECISIONS] | Describe the dashboard users and the specific decisions they need to make. | Lifecycle marketing manager and channel leads deciding weekly where to shift acquisition spend and which campaigns need investigation |
| [DATA SOURCES AND GRAIN] | List available tables or sheets, their key fields, and what one row represents in each source. | ad_spend: one row per date/channel/campaign; signups: one row per user signup with channel; subscriptions: one row per subscription event; refunds: one row per refund event |
| [BUSINESS GOAL] | State the business outcome the dashboard should improve or monitor. | Increase paid subscriptions while keeping first-30-day refund rate below 4% |
| [METRICS AND DEFINITIONS] | List existing metrics, formulas if known, and disagreements or ambiguities around their definitions. | CAC = ad spend divided by paid subscriptions; conversion = paid subscriptions divided by signups; definitions are unclear for refunds and late attribution |
| [TOOL AND QUERY DIALECT] | Name the BI tool, spreadsheet, or database dialect and any required syntax constraints. | Looker Studio connected to BigQuery Standard SQL |
| [CONSTRAINTS] | List refresh timing, permissions, performance limits, mobile needs, stakeholders, or required filters. | Daily refresh at 8 a.m. ET; executives see all channels; channel leads only their channel; date and channel filters required |
How to use
- List actual table names, field names, and row grain before pasting the prompt; this is what prevents plausible but wrong calculations.
- Ask the AI to label any missing field as pseudocode rather than filling gaps with invented schema.
- Check each metric definition with the data owner, especially attribution window, timezone, refunds, and denominator rules.
- Follow up with: “Using this revised schema [SCHEMA], replace the pseudocode with BigQuery SQL and add one row-level test query per metric.”
Variations
KPI definition
Use this when teams use the same metric name differently.
Standardize these dashboard KPIs: [METRIC LIST] for [BUSINESS CONTEXT]. Available fields and grain are [DATA SCHEMA]. For each KPI, produce a metric contract: business purpose, numerator, denominator, entity grain, event date, timezone, inclusions, exclusions, attribution logic, owner, and example calculation in [TOOL]. Identify naming conflicts and metrics that should be retired or renamed. Do not invent fields or business rules. Ask up to 3 questions only if needed.
Executive dashboard
Use this for a concise leadership view rather than an operator workspace.
Design an executive dashboard for [BUSINESS GOAL] used in [MEETING CADENCE]. The available data is [DATA SOURCES], and leadership needs to decide [DECISIONS]. Specify no more than eight primary visuals, the headline narrative each supports, comparison periods, thresholds, and drill-down links for operators. Include a metric dictionary and data caveats. Avoid vanity metrics and do not propose calculations unsupported by the supplied schema. Ask up to 3 questions only if needed.
Formula debugging
Use this when a dashboard number does not match a trusted report.
Debug this dashboard calculation in [TOOL]: [CURRENT FORMULA OR QUERY]. Expected result is [EXPECTED RESULT], actual result is [ACTUAL RESULT], and source schema with row grain is [SCHEMA]. Analyze filters, date logic, joins, aggregation level, nulls, duplicates, and distinct-count behavior. Return a corrected formula or query using only supplied fields, a plain-English explanation, and three validation tests. Do not guess missing schema. Ask up to 3 questions only if needed.
Tips
- Write the grain beside every source table before joining it; joining campaign-day spend to user-level events can multiply spend unless you aggregate first.
- Show a partial current week or month clearly, or compare it only with the same elapsed portion of the prior period.
- A metric card needs a decision threshold or a drill path; otherwise it is usually decoration rather than dashboard UI.
- Use a single governed definition for acquisition date, subscription date, and refund date, because mixing them silently changes conversion and retention figures.
FAQ
Can AI write SQL for my dashboard?
Yes, when you provide table names, fields, keys, and row grain. Test the result against a known total and inspect joins before publishing it.
How many KPIs should a dashboard have?
Start with the few that support the user's recurring decisions, often three to five on an overview. Put diagnostics in drill-down views instead of crowding the first screen.
Why do dashboard metrics disagree with finance reports?
Common causes are different date fields, timezone cutoffs, refund treatment, attribution rules, and duplicate rows after joins. A metric dictionary makes those differences visible.