How to build Affinity deal-flow dashboards in Metabase

{
  "mcpServers": {
    "affinity": {
      "url": "https://your-gumloop-server-url/mcp"
    }
  }
}

# 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 Affinity CSV export — creates a table AND a model
mb upload csv --file affinity-opportunities.csv --collection root

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

Create a polished Metabase dashboard for Affinity deal-flow and pipeline analytics.
Work end to end: get the data into Metabase if it isn't there yet, then build.

Goal: Help partners and platform teams understand deal flow, pipeline, stage
conversion, sourcing quality, and relationship coverage from Affinity data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for Affinity tables and
  models). If durable Affinity 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 Affinity MCP server (scope the connection
  to read-only where possible): opportunities, organizations, lists and list
  entries, and interactions. 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:
Affinity CSV exports are usually flat and pre-aggregated (one row per opportunity
or list entry, with columns like list, stage, amount, and source), while
warehouse tables are raw and normalized (persons, organizations, list entries,
field-value changes, interactions). Stages and statuses live in list-specific
fields, so inspect the actual tables and column names first; do not assume exact
names or a global stage set.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Affinity — it reads a database or CLI-uploaded tables.
- Affinity is highly customizable: stages and statuses live in list-specific
  fields, so resolve them per list rather than assuming a global stage set.
- For win rate, state the denominator explicitly (won / decided vs. won /
  created) and hold the cohort fixed.
- Report deal size and time-in-stage as medians (p50) and p90, never plain
  averages — both are right-skewed.
- Only compute time-in-stage or stage conversion when list-entry stage-change
  history exists; otherwise use a caveat.
- 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: Affinity Deal Flow Overview

Sections:
1. Executive summary (KPI cards): Open pipeline; New deals (last 90 days); Win
   rate; Median deal size; Median time-in-stage; Orgs with a recent interaction.
2. Deal flow: New opportunities by source; pipeline by list and stage; stale
   deals.
3. Conversion: Stage-to-stage conversion by list; win rate by source and sector.
4. Relationships: Interactions per opportunity; coverage; response time.
5. Sourcing: Sourced deals by channel; source-to-won conversion; deal size by
   source.

Filters: List, Stage, Owner, Source, Date range.

Reuse the models Metabase auto-created from uploaded CSVs, or (for a warehouse)
create reusable models: modeled_affinity_opportunities,
modeled_affinity_list_entries, modeled_affinity_stage_history,
modeled_affinity_organizations, and modeled_affinity_interactions.

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
Affinity's own reporting. Keep it practical, dense, and executive-readable.

The current funnel — active deals and value by list and stage.

SELECT
  le.list_name,
  le.stage,
  COUNT(*)                        AS active_deals,
  ROUND(SUM(o.amount), 2)         AS pipeline_value
FROM modeled_affinity_list_entries le
JOIN modeled_affinity_opportunities o ON o.id = le.opportunity_id
WHERE o.status = 'active'
GROUP BY le.list_name, le.stage
ORDER BY pipeline_value DESC;

Won as a share of decided opportunities, by sourcing channel.

SELECT
  o.source,
  COUNT(*) FILTER (WHERE o.status = 'won')                     AS won,
  COUNT(*) FILTER (WHERE o.status IN ('won', 'passed'))        AS decided,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE o.status = 'won')
      / NULLIF(COUNT(*) FILTER (WHERE o.status IN ('won', 'passed')), 0),
    1
  ) AS win_rate_pct
FROM modeled_affinity_opportunities o
WHERE o.status IN ('won', 'passed')
GROUP BY o.source
ORDER BY decided DESC;

Active opportunities with no logged interaction in 30 days.

-- Active opportunities with no logged interaction in the last 30 days
SELECT
  o.id,
  o.name,
  o.owner,
  MAX(i.occurred_at) AS last_interaction
FROM modeled_affinity_opportunities o
LEFT JOIN modeled_affinity_interactions i
  ON i.organization_id = o.organization_id
WHERE o.status = 'active'
GROUP BY o.id, o.name, o.owner
HAVING MAX(i.occurred_at) < CURRENT_DATE - INTERVAL '30 days'
    OR MAX(i.occurred_at) IS NULL
ORDER BY last_interaction NULLS FIRST;