How to build Dynamics 365 Sales dashboards in Metabase

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

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

Create a polished Metabase dashboard for Microsoft Dynamics 365 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,
forecast, and rep activity from Dynamics 365 Sales (Dataverse) data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Dynamics 365 /
  Dataverse tables and models). If durable data is already present — landed via
  Synapse Link / Fabric or uploaded earlier — use it and skip to Step 2.
- If nothing is there, pull it with the Dynamics 365 MCP server (scope the
  connection to read-only where possible): opportunities, accounts, contacts,
  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:
Dynamics 365 CSV exports are usually flat and pre-aggregated (one row per
opportunity, with columns like stage, estimated value, and close date), while
warehouse tables land raw Dataverse rows with option-set codes and separate stage
history. Inspect the actual tables and column names first; do not assume exact
names or that a stage-history table exists.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Dynamics 365 — it reads a database or CLI-uploaded tables.
- Define "won" and "closed" once (opportunity statecode = Won vs. Won/Lost) 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 exists; otherwise use a caveat.
- Dataverse money fields may carry multiple transaction currencies — convert all
  amounts to a single reporting currency; 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: Dynamics 365 Sales Overview

Sections:
1. Executive summary (KPI cards): Open pipeline; Coverage vs. target; 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 and business unit;
   opportunities past close date.
3. Conversion: Stage-to-stage conversion; win rate by process and source; loss
   reasons.
4. Forecast & velocity: Weighted vs. unweighted pipeline; median cycle length;
   time-in-stage; aging.
5. Activity: Calls/emails/appointments by rep; activities per opportunity;
   overdue activities.

Filters: Business Process, Stage, Owner, Lead source, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_d365_opportunities, modeled_d365_stage_history,
modeled_d365_accounts, modeled_d365_contacts, and modeled_d365_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
Dynamics 365 Sales' own reports. Keep it practical, dense, and
executive-readable. Avoid vanity metrics.

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

SELECT
  date_trunc('month', o.actual_close_date) AS month,
  COUNT(*) FILTER (WHERE o.state_code = 'Won')                    AS won,
  COUNT(*) FILTER (WHERE o.state_code IN ('Won', 'Lost'))         AS closed,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE o.state_code = 'Won')
      / NULLIF(COUNT(*) FILTER (WHERE o.state_code IN ('Won', 'Lost')), 0),
    1
  ) AS win_rate_pct
FROM modeled_d365_opportunities o
WHERE o.state_code 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 stage.

SELECT
  o.stage_name,
  COUNT(*)                     AS open_opportunities,
  ROUND(SUM(o.est_value), 2)   AS open_value
FROM modeled_d365_opportunities o
WHERE o.state_code = 'Open'
GROUP BY o.stage_name
ORDER BY open_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.actual_close_date) AS month,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (o.actual_close_date - o.created_on)) / 86400.0
  ) AS median_cycle_days,
  percentile_cont(0.9) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (o.actual_close_date - o.created_on)) / 86400.0
  ) AS p90_cycle_days
FROM modeled_d365_opportunities o
WHERE o.state_code = 'Won'
GROUP BY 1
ORDER BY 1;