How to use MCP for data analysis with AI

1// Claude Desktop config:
2{
3  "mcpServers": {
4    "database": {
5      "command": "npx",
6      "args": ["-y", "@modelcontextprotocol/server-postgres",
7        "postgresql://analyst:readonly@localhost:5432/analytics"]
8    },
9    "filesystem": {
10      "command": "npx",
11      "args": ["-y", "@modelcontextprotocol/server-filesystem",
12        "/Users/you/analysis-workspace"]
13    }
14  }
15}
16
17// Example analysis questions:
18// "What's our monthly revenue trend for the past 12 months?"
19// "Compare conversion rates between mobile and desktop users"
20// "Find the top 10 customers by lifetime value and their order patterns"
21// "Read the Q4-data.csv file and compare it with our database records"

1// Analysis workflow examples:
2
3// Revenue analysis:
4// "What's our total revenue by month for 2025? Show the month-over-month
5//  growth rate and identify any months with significant drops."
6
7// The AI generates:
8// SELECT DATE_TRUNC('month', created_at) as month,
9//        SUM(total) as revenue
10// FROM orders WHERE created_at >= '2025-01-01'
11// GROUP BY 1 ORDER BY 1;
12
13// User cohort analysis:
14// "Break down our users by signup month and show their 30/60/90 day
15//  retention rates. Which cohort has the best retention?"
16
17// Funnel analysis:
18// "How many users go from signup to first purchase, and what's the
19//  average time between signup and first order?"
20
21// File + database:
22// "Read the marketing-spend.csv file, then compare the monthly spend
23//  with our monthly revenue to calculate ROI per channel."

1// src/analysis-tools.ts
2import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
3import { z } from "zod";
4import pg from "pg";
5
6const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
7
8export function registerAnalysisTools(server: McpServer) {
9  server.tool(
10    "revenue_by_period",
11    "Analyze revenue grouped by time period with growth rates",
12    {
13      period: z.enum(["day", "week", "month", "quarter"]).default("month"),
14      startDate: z.string().describe("Start date in YYYY-MM-DD format"),
15      endDate: z.string().optional().describe("End date, defaults to today"),
16    },
17    async ({ period, startDate, endDate }) => {
18      const end = endDate || new Date().toISOString().split("T")[0];
19      const result = await pool.query(`
20        WITH periods AS (
21          SELECT DATE_TRUNC($1, created_at) AS period,
22                 SUM(total) AS revenue,
23                 COUNT(*) AS orders
24          FROM orders
25          WHERE created_at BETWEEN $2 AND $3
26          GROUP BY 1 ORDER BY 1
27        )
28        SELECT period, revenue, orders,
29               ROUND((revenue - LAG(revenue) OVER (ORDER BY period)) /
30                 NULLIF(LAG(revenue) OVER (ORDER BY period), 0) * 100, 1) AS growth_pct
31        FROM periods
32      `, [period, startDate, end]);
33      return { content: [{ type: "text", text: JSON.stringify(result.rows, null, 2) }] };
34    }
35  );
36
37  server.tool(
38    "export_csv",
39    "Run a SQL query and save results as a CSV file",
40    {
41      sql: z.string().describe("SELECT query to export"),
42      filename: z.string().describe("Output filename like report.csv"),
43    },
44    async ({ sql, filename }) => {
45      if (/\b(DELETE|DROP|UPDATE|INSERT)\b/i.test(sql)) {
46        return { content: [{ type: "text", text: "Error: Only SELECT queries allowed" }], isError: true };
47      }
48      const result = await pool.query(sql);
49      const headers = result.fields.map(f => f.name).join(",");
50      const rows = result.rows.map(r =>
51        result.fields.map(f => JSON.stringify(r[f.name] ?? "")).join(",")
52      );
53      const csv = [headers, ...rows].join("\n");
54      const fs = await import("fs/promises");
55      const outPath = `/tmp/analysis/${filename}`;
56      await fs.mkdir("/tmp/analysis", { recursive: true });
57      await fs.writeFile(outPath, csv, "utf-8");
58      return { content: [{ type: "text", text: `Exported ${result.rowCount} rows to ${outPath}` }] };
59    }
60  );
61}

1server.tool(
2  "generate_weekly_report",
3  "Generate a weekly business metrics report",
4  {
5    weekStart: z.string().describe("Monday date in YYYY-MM-DD format"),
6  },
7  async ({ weekStart }) => {
8    const weekEnd = new Date(new Date(weekStart).getTime() + 7 * 86400000)
9      .toISOString().split("T")[0];
10
11    const revenue = await pool.query(
12      `SELECT SUM(total) as total, COUNT(*) as orders FROM orders
13       WHERE created_at BETWEEN $1 AND $2`, [weekStart, weekEnd]
14    );
15    const newUsers = await pool.query(
16      `SELECT COUNT(*) as count FROM users
17       WHERE created_at BETWEEN $1 AND $2`, [weekStart, weekEnd]
18    );
19    const topProducts = await pool.query(
20      `SELECT p.name, SUM(oi.quantity) as units, SUM(oi.total) as revenue
21       FROM order_items oi JOIN products p ON oi.product_id = p.id
22       JOIN orders o ON oi.order_id = o.id
23       WHERE o.created_at BETWEEN $1 AND $2
24       GROUP BY p.id ORDER BY revenue DESC LIMIT 5`, [weekStart, weekEnd]
25    );
26
27    const report = {
28      period: `${weekStart} to ${weekEnd}`,
29      revenue: revenue.rows[0],
30      newUsers: newUsers.rows[0].count,
31      topProducts: topProducts.rows,
32    };
33
34    return { content: [{ type: "text", text: JSON.stringify(report, null, 2) }] };
35  }
36);