How to build SugarCRM sales dashboards in Metabase

{
  "mcpServers": {
    "sugarcrm": {
      "url": "https://mcp.pipedream.net/v2"
    }
  }
}

# Install the Metabase CLI
npm install -g @metabase/cli

# Log in (opens your browser; requires Metabase v62+)
mb auth login --url https://your-metabase.example.com

# Load a SugarCRM CSV export — creates a table AND a model
mb upload csv --file sugarcrm-opportunities.csv --collection root

# Refresh that same table later from a new export
mb upload replace <table-id> --file sugarcrm-opportunities.csv

Create a polished Metabase dashboard for SugarCRM sales analytics.
Work end to end: get the data into Metabase if it isn't there yet, then build.

Goal: Help sales leaders understand pipeline, win rate, conversion, sales cycle,
and forecast from SugarCRM data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Sugar tables and
  models, and any connected Sugar database). If durable data is already present —
  a read replica for self-hosted Sugar, a synced SugarCloud copy, or uploaded
  earlier — use it and skip to Step 2.
- If nothing is there, pull it with the SugarCRM MCP server (scope the connection
  to read-only where possible): opportunities, accounts, leads, and activities.
  Write each result to a CSV, then load it with the Metabase CLI — run
  "mb upload csv --file <export>.csv" so each upload creates a table and a
  ready-to-query model. Use "mb upload replace <table-id> --file <export>.csv" to
  refresh an existing table instead of creating duplicates.

Step 2 — Inspect before querying:
SugarCRM CSV exports are usually flat and pre-aggregated (one row per opportunity,
with columns like sales_stage, amount, and date_closed), while the Sugar database
holds raw tables plus the opportunities_audit change log. Inspect the actual
tables and column names first; do not assume exact names or that an audit/stage
history table exists.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  SugarCRM — it reads a database or CLI-uploaded tables.
- Sugar's sales_stage values include Closed Won and Closed Lost — define "won"
  and "closed" once and reuse them.
- For win rate, state the denominator explicitly (won / closed vs. won / created)
  and hold the cohort fixed.
- Report sales cycle length and deal size as medians (p50) and p90, never plain
  averages — both are right-skewed.
- Only compute sales cycle length, time-in-stage, or stage conversion when
  opportunity stage-change history (opportunities_audit) exists; otherwise use a
  caveat.
- Convert amounts to a single reporting currency using amount_usdollar or a rate
  table; caveat any mix.
- Only build a card if its underlying column/metric exists in the data.
- A single CSV is a point-in-time snapshot: only build trend cards if there is a
  usable date column or multiple periods have been uploaded.

Dashboard title: SugarCRM Sales Overview

Sections:
1. Executive summary (KPI cards): Open pipeline; Weighted forecast; Win rate
   (last 90 days); Median sales cycle length; Average and median deal size;
   Opportunities closing this period.
2. Pipeline: Open opportunities by stage; value by rep and team; slipped deals.
3. Conversion: Stage-to-stage conversion; win rate by team and source; lost
   reasons.
4. Velocity: Median cycle length; time-in-stage; deal velocity; aging.
5. Activity: Calls/meetings by rep; activities per opportunity; lead response.

Filters: Team, Sales stage, Rep, Lead source, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a database or
warehouse) create reusable models: modeled_sugar_opportunities,
modeled_sugar_stage_history, modeled_sugar_accounts, modeled_sugar_leads, and
modeled_sugar_activities.

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. Include caveats for any metric
that cannot be calculated from the available data. Reconcile totals against
Sugar's own reports. Keep it practical, dense, and executive-readable.

Closed Won as a share of closed opportunities over the last 12 months.

SELECT
  date_trunc('month', o.date_closed) AS month,
  COUNT(*) FILTER (WHERE o.sales_stage = 'Closed Won')                          AS won,
  COUNT(*) FILTER (WHERE o.sales_stage IN ('Closed Won', 'Closed Lost'))        AS closed,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE o.sales_stage = 'Closed Won')
      / NULLIF(COUNT(*) FILTER (WHERE o.sales_stage IN ('Closed Won', 'Closed Lost')), 0),
    1
  ) AS win_rate_pct
FROM modeled_sugar_opportunities o
WHERE o.sales_stage IN ('Closed Won', 'Closed Lost')
  AND o.date_closed >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Open opportunities, raw value, and weighted value by sales stage.

SELECT
  o.sales_stage,
  COUNT(*)                       AS open_opps,
  ROUND(SUM(o.amount_usdollar), 2) AS pipeline_value,
  ROUND(SUM(o.amount_usdollar * o.probability / 100.0), 2) AS weighted_value
FROM modeled_sugar_opportunities o
WHERE o.sales_stage NOT IN ('Closed Won', 'Closed Lost')
GROUP BY o.sales_stage
ORDER BY pipeline_value DESC;

Days from created to won; medians beat averages here.

-- Median days from created to won, by month closed
SELECT
  date_trunc('month', o.date_closed) AS month,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (o.date_closed - o.date_entered)) / 86400.0
  ) AS median_cycle_days,
  percentile_cont(0.9) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (o.date_closed - o.date_entered)) / 86400.0
  ) AS p90_cycle_days
FROM modeled_sugar_opportunities o
WHERE o.sales_stage = 'Closed Won'
GROUP BY 1
ORDER BY 1;