Build Business Dashboards with AI

1. 1

   Define the Dashboard's Single Purpose

   The most common dashboard mistake is trying to show everything. Before writing a single line of code, use AI to pressure-test your design concept: who uses this, what decision does it support, and what are the 5-7 metrics that matter and nothing else.

   Claude ChatGPT

   Help me design a business dashboard. I need to define its scope before building anything.
   
   **Business context:**
   - Team/function this serves: [e.g., 'Marketing team of 8 people' / 'C-suite executive team' / 'Customer support managers']
   - Business we're in: [e.g., 'SaaS product with monthly subscriptions' / 'E-commerce store' / 'B2B services company']
   - Primary decision this dashboard supports: [e.g., 'Weekly team standup: are we on track for monthly targets?' / 'Daily operational health check: do we need to escalate anything?' / 'Monthly board review: are we growing as expected?']
   
   **Data I have available:**
   - Data sources: [e.g., 'Postgres database with orders table, users table, events table' / 'Google Analytics' / 'Salesforce CRM export' / 'CSV files I update weekly']
   - Update frequency: [e.g., 'Real-time', 'Daily batch at 6am', 'Manual weekly update']
   - Historical depth: [e.g., '2 years of data']
   
   **What I'm currently tracking (or want to track):**
   [List every metric you're considering showing — dump them all here]
   
   **Please help me:**
   
   1. **Identify the 5-7 KPIs** that actually matter for my stated decision. Explain why each one is essential. Be ruthless about cutting metrics that are 'interesting' but don't change decisions.
   
   2. **Design the information hierarchy**: Which metrics go in the top row (the headline numbers)? Which are secondary detail? What belongs in a drill-down rather than the main view?
   
   3. **Define the alert logic**: For each KPI, what threshold indicates 'something is wrong and needs attention now'? What's the normal range?
   
   4. **Suggest the right time comparisons**: For each metric, should I show vs. yesterday / vs. last week / vs. last month / vs. same period last year / vs. budget? Why?
   
   5. **Recommend the right tool** for my use case: Streamlit / Dash / Metabase / Grafana / Google Sheets / Tableau / Looker Studio (free) — based on my technical level and data sources.

   Copy

   Tip: Write the dashboard spec as a one-pager before writing any code. Title it with the audience and decision: 'Weekly Marketing Dashboard — for the marketing team's Monday standup to answer: are we on track for this month's MQL target?' Every metric on the page should be justifiable against that sentence. If you can't explain why a metric belongs, cut it.

2. 2

   Write the Data Queries

   Most dashboard projects spend 80% of their time on data plumbing: writing SQL queries, handling data quality issues, building aggregations. AI excels at this — describe what you want to measure and it writes the query.

   ChatGPT Claude

   Write the SQL queries (or data transformation code) to power my dashboard metrics.
   
   **Database:** [PostgreSQL / MySQL / BigQuery / Snowflake / SQLite / DuckDB / other]
   
   **My table schemas** (paste CREATE TABLE statements or describe columns):
   ```sql
   [Paste your schema here, e.g.:
   orders: id, user_id, created_at, status (pending/completed/refunded), amount_usd, product_id
   users: id, email, created_at, plan (free/pro/enterprise), country
   events: id, user_id, event_name, timestamp, properties (jsonb)]
   ```
   
   **Sample data** (optional but helpful — paste 3-5 rows per table):
   ```
   [Paste sample rows]
   ```
   
   **Metrics I need to calculate:**
   
   1. [Metric name, e.g., 'Daily Active Users'] — Definition: [e.g., 'users who triggered at least one event today'] — Time grain: [daily/weekly/monthly] — Filters: [e.g., 'exclude internal users where email ends in @mycompany.com']
   
   2. [Metric name] — Definition: [exact business definition] — Time grain: — Filters:
   
   3. [Repeat for each metric, being precise about the business definition]
   
   **For each metric, write:**
   - A clean, optimized SQL query that returns the data I need
   - A version that returns the current period AND the comparison period (e.g., this week vs. last week) so I can calculate % change
   - Comments explaining the business logic in each query (especially any tricky parts like handling timezones, deduplication, or status filters)
   - Any indexes I should add to make these queries fast
   
   **Also:**
   - Flag any data quality issues you can see from my schema (e.g., 'amount_usd could be NULL for pending orders — decide how to handle this')
   - Suggest a single denormalized view or materialized view I can create so the dashboard queries are faster and simpler

   Copy

   Tip: Always add a timezone conversion in your queries if you have a global user base. '2024-01-01' in UTC is still 2023-12-31 in California. Dashboard metrics that shift overnight because of timezone handling erode trust fast. Ask AI to add explicit timezone conversion to every timestamp comparison in your queries.

3. 3

   Build the Dashboard Layout

   Now build the actual UI. Streamlit is the fastest path to a working Python dashboard — you can have something real running in under an hour. If you're not a developer, Looker Studio (free) or Metabase (open source) with the SQL queries from the previous step is the right path.

   ChatGPT Claude

   Build a complete Streamlit dashboard using my queries and KPI definitions.
   
   **KPIs to show:**
   [List your 5-7 KPIs from Step 1, with their SQL queries from Step 2]
   
   **Layout I want:**
   - Top row: [e.g., '4 metric cards showing: Total Revenue MTD, MoM growth %, Active Users, Churn Rate']
   - Second section: [e.g., 'Daily revenue line chart for past 30 days, with last 30 days of prior year as a faded reference line']
   - Third section: [e.g., 'Bar chart: revenue by product category, sorted descending; next to it a table of top 10 customers by revenue']
   - Filters: [e.g., 'Date range picker at the top; Country dropdown (default: All); Product category filter']
   
   **Data connection:**
   - Database: [PostgreSQL / SQLite / CSV file / Google Sheets / other]
   - Connection string pattern: [e.g., 'postgresql://user:pass@host:5432/dbname' — I'll replace with real values]
   
   **Styling:**
   - Color scheme: [e.g., 'Dark theme' / 'White background with blue accent (#0066CC)' / 'Use Streamlit default']
   - Metric card style: [e.g., 'Show value, delta vs. prior period, and a mini trend sparkline']
   - Alert coloring: [e.g., 'Turn metric red if below target, green if above — I'll define thresholds per metric']
   
   **Please write:**
   1. Complete `app.py` with all charts, metrics, and filters wired up
   2. `data.py` — a separate module for all database queries, with caching (`@st.cache_data(ttl=3600)`)
   3. `requirements.txt` with pinned versions
   4. A `.env.example` file for database credentials
   5. Instructions to run locally and deploy to Streamlit Cloud (free)
   
   Add a 'Last updated' timestamp in the footer showing when data was last refreshed.

   Copy

   Tip: Use `@st.cache_data(ttl=3600)` on every database function so the dashboard doesn't re-query the database on every interaction. Without caching, Streamlit re-runs the entire script on every user action, and your dashboard becomes unbearably slow. For a dashboard that needs truly real-time data, set TTL to 60 seconds; for daily reporting dashboards, 3600 seconds (1 hour) is fine.

4. 4

   Add Alerts and Automated Reports

   A dashboard people have to remember to check is only half a solution. Add automated alerts that push to Slack, email, or Teams when a metric crosses a threshold, and a weekly automated email digest. AI can write both the alert logic and the scheduling code.

   Claude ChatGPT

   Add alerting and automated reporting to my dashboard.
   
   **Alert requirements:**
   
   Alert 1:
   - Metric: [e.g., 'Daily signups']
   - Trigger condition: [e.g., 'Falls more than 30% below the 7-day rolling average']
   - Message: [e.g., 'Signups are down 35% vs last week average. Today: [X], 7-day avg: [Y]. Check acquisition channels.']
   - Channel: [Slack / email / SMS / Teams]
   
   Alert 2:
   - Metric: [metric name]
   - Trigger: [condition with exact threshold]
   - Message: [what to say in the alert, including relevant context]
   - Channel:
   
   [Repeat for each alert, max 5-7 alerts — more creates alert fatigue]
   
   **Weekly digest email:**
   - Send every: [Monday morning 8am / Friday 5pm / other]
   - Recipients: [e.g., 'marketing team DL: [email protected]']
   - Content: [e.g., 'This week vs. last week for all 7 KPIs, with trend arrows and % change. Top 3 wins. Anything flagged red.']
   - Format: [HTML email / plain text / Slack message]
   
   **Infrastructure:**
   - How I'll schedule this: [cron job on a server / GitHub Actions / Airflow / Cloud Scheduler / n8n / other]
   - Email service: [SMTP / SendGrid / Mailgun / AWS SES]
   - Slack webhook: [yes I have one / no, show me how to set one up]
   
   **Please write:**
   1. Python script `alerts.py` with the alert logic, threshold checks, and notification sending
   2. Python script `weekly_digest.py` that generates and sends the HTML email report
   3. GitHub Actions workflow YAML (or cron syntax) to schedule both scripts
   4. Instructions for setting up the Slack incoming webhook or email credentials
   5. A simple SQLite table schema to log which alerts have fired (to avoid duplicate alerts)

   Copy

   Tip: Alert fatigue is real. Start with 2-3 alerts for genuinely critical conditions — things that would prompt someone to drop what they're doing. If an alert fires more than once a week in normal operations, either the threshold is wrong or the metric is too volatile to alert on. Review and tune thresholds after the first two weeks.

5. 5

   Document and Hand Off the Dashboard

   A dashboard no one knows how to update is a liability. AI can write the documentation — what each metric means, how to update the data, how to add a new chart, and what to do when something breaks.

   Claude ChatGPT

   Write the documentation for my business dashboard so someone else on my team can maintain it.
   
   **Dashboard overview:**
   - Name: [Dashboard name]
   - Purpose: [One sentence on what decision it supports and for whom]
   - Update frequency: [How often data refreshes]
   - Owner: [Who is responsible for keeping it working]
   
   **KPIs in this dashboard:**
   [List each KPI]
   
   **Please write:**
   
   1. **Metric definitions** (one paragraph per KPI):
      - What it measures (plain English, no jargon)
      - Exact calculation / business rules (e.g., 'A user is counted as active if they had at least one session event in the last 7 days. Internal test accounts are excluded.')
      - What a good number looks like vs. a concerning number
      - Common reasons it can spike or drop
   
   2. **Data sources and dependencies**:
      - Where data comes from and how it gets into the database
      - What breaks if a data source goes down
      - How to verify data freshness
   
   3. **How to update/modify the dashboard**:
      - How to change a threshold or color
      - How to add a new metric or chart
      - How to deploy changes
   
   4. **Troubleshooting guide**:
      - 'The dashboard shows no data' — steps to diagnose
      - 'A metric looks wrong' — how to verify against the source
      - 'The dashboard is slow' — how to debug query performance
   
   5. **Runbook for common operations**:
      - How to backfill missing data
      - How to add a new user/team to access the dashboard
      - How to temporarily disable an alert that's noisy
   
   Format as a wiki-style document with clear headers. Write for someone who knows SQL but didn't build this dashboard.

   Copy

   Tip: Write the documentation the day you finish the dashboard, not six months later. You remember all the decisions you made — why you excluded certain users, why you chose 30-day windows, why Q3 has an annotation. In six months you won't, and neither will your replacement. 30 minutes of documentation saves hours of confusion.

Recommended Tools for This Scenario

ChatGPT

Freemium

The AI assistant that started the generative AI revolution

• GPT-4o multimodal model with text, vision, and audio
• DALL-E 3 image generation
• Code Interpreter for data analysis and visualization

Get Started →

Claude

Freemium

Anthropic's AI assistant built for thoughtful analysis and safe, nuanced conversations

• 200K token context window for massive document processing
• Artifacts — interactive side-panel for code, docs, and visualizations
• Projects with persistent context and custom instructions

Get Started →

Wolfram Alpha

Freemium

Computational knowledge engine for math, science, and data analysis

• Symbolic and numerical math computation (algebra through advanced calculus)
• Step-by-step solutions showing full working process (Pro)
• Real-world data queries across science, finance, geography, nutrition

Get Started →