How to build Insightly sales dashboards in Metabase

{
  "mcpServers": {
    "insightly": {
      "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 an Insightly CSV export — creates a table AND a model
mb upload csv --file insightly-opportunities.csv --collection root

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

Create a polished Metabase dashboard for Insightly 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 Insightly CRM data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Insightly tables and
  models). If durable Insightly data is already present — synced from a warehouse
  or uploaded earlier — use it and skip to Step 2.
- If nothing is there, pull it with the Insightly MCP server (managed via
  Pipedream): opportunities, pipelines and stages, organisations, 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:
Insightly CSV exports are usually flat and pre-aggregated (one row per
opportunity, with columns like OPPORTUNITY_STATE, BID_AMOUNT, PROBABILITY,
PIPELINE_ID, and STAGE_ID). Warehouse tables are raw and relational (join
STAGE_ID to pipeline stages for readable, ordered names). Inspect the actual
tables and column names first; do not assume exact names or that stage-change
history exists.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Insightly — it reads a database or CLI-uploaded tables.
- Insightly opportunities carry an OPPORTUNITY_STATE (OPEN / WON / LOST /
  SUSPENDED/ABANDONED) — define "won" and "closed" once and reuse them.
- Join STAGE_ID to Pipeline Stages to get readable, ordered stage names, and note
  that stages are pipeline-specific.
- For win rate, state the denominator explicitly (won / decided 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 build a card if its underlying column/metric exists in the data; if
  stage-change history is missing, skip sales cycle length, time-in-stage, and
  stage conversion and use a caveat instead.
- Convert BID_AMOUNT to a single reporting currency; caveat any mix.
- 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: Insightly 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 owner; slipped deals.
3. Conversion: Stage-to-stage conversion; win rate by pipeline and source;
   abandoned vs. lost.
4. Velocity: Median cycle length; time-in-stage; deal velocity; aging.
5. Activity: Tasks/events by owner; activities per opportunity; overdue tasks.

Filters: Pipeline, Stage, Owner, Lead source, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_insightly_opportunities,
modeled_insightly_stage_history, modeled_insightly_pipelines,
modeled_insightly_organisations, and modeled_insightly_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
Insightly's own reports. Keep it practical, dense, and executive-readable.

WON as a share of decided opportunities over the last 12 months.

SELECT
  date_trunc('month', o.actual_close_date) AS month,
  COUNT(*) FILTER (WHERE o.opportunity_state = 'WON')                   AS won,
  COUNT(*) FILTER (WHERE o.opportunity_state IN ('WON', 'LOST'))        AS decided,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE o.opportunity_state = 'WON')
      / NULLIF(COUNT(*) FILTER (WHERE o.opportunity_state IN ('WON', 'LOST')), 0),
    1
  ) AS win_rate_pct
FROM modeled_insightly_opportunities o
WHERE o.opportunity_state IN ('WON', 'LOST')
  AND o.actual_close_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

The current funnel — open opportunities and value by pipeline stage.

SELECT
  s.pipeline_name,
  s.stage_name,
  s.stage_order,
  COUNT(*)                    AS open_opps,
  ROUND(SUM(o.bid_amount), 2) AS pipeline_value
FROM modeled_insightly_opportunities o
JOIN modeled_insightly_pipelines s ON s.stage_id = o.stage_id
WHERE o.opportunity_state = 'OPEN'
GROUP BY s.pipeline_name, s.stage_name, s.stage_order
ORDER BY s.pipeline_name, s.stage_order;

Days from created to won; medians beat averages here.

-- Median days from created to won, by month closed
SELECT
  date_trunc('month', o.actual_close_date) AS month,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (o.actual_close_date - o.date_created)) / 86400.0
  ) AS median_cycle_days,
  percentile_cont(0.9) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (o.actual_close_date - o.date_created)) / 86400.0
  ) AS p90_cycle_days
FROM modeled_insightly_opportunities o
WHERE o.opportunity_state = 'WON'
GROUP BY 1
ORDER BY 1;