Sales Operations Dashboard

Purpose

This recipe produces a fully functional sales operations dashboard that visualizes CRM pipeline data in real time, tracks rep activity metrics, calculates stage conversion rates, and generates weighted revenue forecasts. The output is a deployed internal tool (Retool, Metabase, or Grafana) connected to your CRM via automated data sync, giving sales leadership a single-pane view of pipeline health, deal velocity, and forecast accuracy. [src1]

Prerequisites

• CRM account with API access enabled — HubSpot (Settings > Integrations > API Key), Pipedrive (Settings > Personal > API), or Salesforce (Setup > API)
• Pipeline stages defined in CRM — at minimum: Lead, Qualified, Proposal, Negotiation, Closed Won, Closed Lost with win probabilities assigned
• PostgreSQL database — free tier at Supabase or Neon (500MB sufficient for <25 reps)
• Dashboard tool account — Retool (free for 5 users) or Metabase (open source, self-hosted)
• 90+ days of historical deal data in CRM — required for meaningful forecasting; 30 days minimum for basic pipeline views

Constraints

• CRM API rate limits: HubSpot = 100 requests/10 seconds (free), Pipedrive = 80 requests/2 seconds. Implement exponential backoff and batch fetching. [src1]
• Pipeline stage names in the dashboard schema MUST exactly match CRM stage names — any mismatch produces zero-value conversion metrics. [src2]
• Forecasting requires minimum 90 days of closed deal data. With fewer than 50 closed deals, weighted pipeline forecast error exceeds 40%. [src6]
• PII fields (contact email, phone) must be excluded from shared dashboard views or masked per GDPR/CCPA.
• Dashboard refresh interval: 15 minutes minimum for operational views. Sub-minute polling wastes API quota without meaningful data changes.

Tool Selection Decision

Which path?
├── User is non-technical AND budget = free
│   └── PATH A: No-Code Free — Google Looker Studio + CRM native export
├── User is non-technical AND budget > $0
│   └── PATH B: No-Code Paid — Retool (free tier) + Supabase + n8n Cloud
├── User is semi-technical or developer AND budget = free
│   └── PATH C: Code + Free — Metabase (self-hosted) + PostgreSQL + cron script
└── User is developer AND budget > $0
    └── PATH D: Code + Paid — Retool + Supabase + n8n + custom transforms

Execution Flow

Step 1: Design the Data Model

Duration: 30-60 minutes · Tool: SQL client (psql, DBeaver, or Supabase dashboard)

Create staging tables that normalize CRM data into a consistent schema regardless of CRM platform. Core tables: deals, activities, stage_history, pipeline_snapshots with appropriate indexes for dashboard query performance.

Verify: SELECT COUNT(*) FROM deals; returns 0 (tables created). · If failed: Check PostgreSQL connection string and user permissions.

Step 2: Build the CRM Data Sync

Duration: 1-2 hours · Tool: n8n, Pipedream, or custom Node.js script

Rate limit: HubSpot = 100/10s, Pipedrive = 80/2s. Batch in groups of 50 with 1s delay.

Configure ETL pipeline that extracts deals and activities from CRM API, transforms field names to the staging schema, and upserts into PostgreSQL. Set sync schedule: every 15 minutes for active pipeline, every 6 hours for historical snapshots. [src1]

Verify: SELECT COUNT(*) FROM deals WHERE updated_at > NOW() - INTERVAL '1 hour'; returns > 0. · If failed: Check API key validity and rate limit headers.

Step 3: Build Pipeline Visualization Queries

Duration: 1-2 hours · Tool: SQL + Retool/Metabase

Create five core queries: pipeline funnel by stage, stage-to-stage conversion rates, rep activity scoreboard, weighted forecast by close month, and deal velocity trend. Each query powers a specific dashboard component. [src6]

Verify: Each query returns data with no SQL errors. · If failed: Check column names match your schema.

Step 4: Assemble the Dashboard UI

Duration: 1-2 hours · Tool: Retool or Metabase

Build the dashboard layout: KPI row (total pipeline, weighted forecast, win rate, deal velocity), pipeline funnel chart, forecast by month chart, conversion table, rep scoreboard, deal velocity trend line, and filterable deals table. [src3]

Verify: All 6 dashboard sections render with data. KPI values match CRM totals within 5%. · If failed: Check query bindings in dashboard tool.

Step 5: Configure Alerts and Scheduled Snapshots

Duration: 30-60 minutes · Tool: n8n or Pipedream + Slack/Email

Set up daily pipeline snapshot insertion and alert conditions: pipeline value drop >20% vs 7-day average, rep inactivity >48 hours, deals stuck >2x average days in stage. Route alerts to Slack or email.

Verify: Slack notification fires on test trigger. Pipeline_snapshots table has today's row. · If failed: Check Slack webhook URL or SMTP settings.

Step 6: Deploy and Share Access

Duration: 30 minutes · Tool: Retool/Metabase deployment settings

Share dashboard URL with sales team. Set viewer-only permissions for reps, editor for managers. Enable mobile access for field sales.

Verify: At least one non-admin user can access the dashboard and see current data.

Output Schema

{
  "output_type": "sales_operations_dashboard",
  "format": "deployed web application",
  "components": [
    {"name": "pipeline_funnel", "type": "chart", "description": "Horizontal bar chart showing deal count and value by stage"},
    {"name": "forecast_chart", "type": "chart", "description": "Monthly stacked bar of pipeline value vs weighted forecast"},
    {"name": "kpi_cards", "type": "metrics", "description": "Total pipeline, weighted forecast, win rate, deal velocity"},
    {"name": "rep_scoreboard", "type": "table", "description": "Activity counts per rep: calls, emails, meetings"},
    {"name": "conversion_table", "type": "table", "description": "Stage-to-stage conversion rates with pass rate percentages"},
    {"name": "deals_table", "type": "table", "description": "Filterable table of all active deals"}
  ],
  "refresh_interval": "15 minutes",
  "data_source": "PostgreSQL staging database synced from CRM"
}

Quality Benchmarks

If below minimum: Check data sync job logs for failures. Pipeline value discrepancy usually means deals with $0 amount or unmapped stages are excluded.

Error Handling

Cost Breakdown

Anti-Patterns

Wrong: Building pipeline views directly on CRM API calls

Every dashboard load fires dozens of API requests to the CRM. At 5 users refreshing 10 times daily, that burns 500+ API calls/day — hitting rate limits within weeks and creating brittle, slow dashboards. [src1]

Correct: ETL to staging database with scheduled sync

Extract CRM data to PostgreSQL on a 15-minute schedule. Dashboard queries hit the local database with sub-second response. API quota used only for sync, not for every page load.

Wrong: Showing weighted forecast without calibrating win probabilities

Default CRM stage probabilities are arbitrary. Teams using uncalibrated probabilities see 30-50% forecast error, making the weighted number worse than useless. [src6]

Correct: Calibrate probabilities from historical conversion data

Calculate actual conversion rates per stage from 6+ months of closed deal data. Update CRM stage probabilities to match reality. Re-calibrate quarterly.

When This Matters

Use when a startup sales team has outgrown CRM-native reporting and needs a unified view of pipeline health, rep performance, and revenue forecast. Requires at least one active CRM with 90+ days of deal history. This recipe builds the dashboard — for sales process design or strategy, use a playbook card instead.