Ironclaw Pipeline Analytics

Generate interactive analytics dashboards from CRM data. Use when asked to "show pipeline stats", "create a report", "analyze leads", "show conversion rates"...

Security Scan

The skill's instructions and requirements align with its stated purpose (translating NL to DuckDB SQL and rendering Recharts dashboards); it is instruction-only and does not ask for unrelated credentials or installs, but it assumes a DuckDB workspace and the ability to execute queries and render interactive charts which may not be available or desirable in every environment.

Purpose & Capability

The name and description (pipeline analytics from CRM data) match the SKILL.md: it translates natural-language to DuckDB SQL, runs queries on pivot views (v_*), and formats results as Recharts-compatible report JSON. No unrelated credentials, binaries, or installs are requested.

Instruction Scope

Instructions focus on querying DuckDB workspace views and producing Recharts-compatible JSON. They do assume the agent can (a) execute DuckDB SQL against a workspace database and (b) render or display interactive Recharts components inline in chat. The SKILL.md does not specify how to connect to DuckDB or how rendering will be performed; that ambiguity could cause the agent to try different execution mechanisms or spawn subprocesses depending on the environment. Also be aware queries will access workspace CRM data — sensitive data may be returned in reports.

Install Mechanism

Instruction-only skill with no install spec and no code files. Nothing is written to disk and no external artifacts are fetched.

Credentials

No environment variables, credentials, or config paths are requested. The only implicit requirement is read access to the DuckDB workspace and its v_* pivot views, which is proportionate to the stated purpose.

Persistence & Privilege

always is false and the skill does not request elevated or persistent privileges. It does not attempt to modify system or other-skill configurations.

Assessment

This skill appears coherent with its purpose, but before installing consider: 1) Ensure your agent environment actually has a DuckDB workspace and the v_* pivot views the skill expects; otherwise it will fail or the agent may try alternate methods to run SQL. 2) Confirm you are comfortable the agent will execute queries over your workspace CRM data — test on non-production data first and avoid exposing sensitive PII. 3) Verify your chat environment can render interactive Recharts (HTML/JS) or be prepared to accept static alternatives. 4) The skill has no publisher homepage and source is unknown; prefer skills from identifiable maintainers when possible. 5) If you enable autonomous invocation, monitor initial runs to ensure queries and rendering behave as expected.

Pipeline Analytics — NL → SQL → Interactive Charts

Transform natural language questions into DuckDB queries and render results as interactive Recharts dashboards inline in chat.

Workflow

User asks question in plain English
→ Translate to DuckDB SQL against workspace pivot views (v_*)
→ Execute query
→ Format results as report-json
→ Render as interactive Recharts components

DuckDB Query Patterns

Discovery — What objects exist?

-- List all objects and their entry counts
SELECT o.name, o.display_name, COUNT(e.id) as entries
FROM objects o
LEFT JOIN entries e ON e.object_id = o.id
GROUP BY o.name, o.display_name
ORDER BY entries DESC;

-- List fields for an object
SELECT f.name, f.field_type, f.display_name
FROM fields f
JOIN objects o ON f.object_id = o.id
WHERE o.name = 'leads'
ORDER BY f.position;

-- Available pivot views
SELECT table_name FROM information_schema.tables
WHERE table_name LIKE 'v_%';

Common Analytics Queries

Pipeline Funnel

SELECT "Status", COUNT(*) as count
FROM v_leads
GROUP BY "Status"
ORDER BY CASE "Status"
  WHEN 'New' THEN 1
  WHEN 'Contacted' THEN 2
  WHEN 'Qualified' THEN 3
  WHEN 'Demo Scheduled' THEN 4
  WHEN 'Proposal' THEN 5
  WHEN 'Closed Won' THEN 6
  WHEN 'Closed Lost' THEN 7
  ELSE 99
END;

Outreach Activity Over Time

SELECT DATE_TRUNC('week', "Last Outreach"::DATE) as week,
       "Outreach Channel",
       COUNT(*) as messages_sent
FROM v_leads
WHERE "Last Outreach" IS NOT NULL
GROUP BY week, "Outreach Channel"
ORDER BY week;

Conversion Rates by Source

SELECT "Source",
       COUNT(*) as total,
       COUNT(*) FILTER (WHERE "Status" = 'Qualified') as qualified,
       COUNT(*) FILTER (WHERE "Status" IN ('Closed Won', 'Converted')) as converted,
       ROUND(100.0 * COUNT(*) FILTER (WHERE "Status" = 'Qualified') / COUNT(*), 1) as qual_rate,
       ROUND(100.0 * COUNT(*) FILTER (WHERE "Status" IN ('Closed Won', 'Converted')) / COUNT(*), 1) as conv_rate
FROM v_leads
GROUP BY "Source"
ORDER BY total DESC;

Reply Rate Analysis

SELECT "Outreach Channel",
       COUNT(*) as sent,
       COUNT(*) FILTER (WHERE "Reply Received" = true) as replied,
       ROUND(100.0 * COUNT(*) FILTER (WHERE "Reply Received" = true) / COUNT(*), 1) as reply_rate
FROM v_leads
WHERE "Outreach Status" IS NOT NULL
GROUP BY "Outreach Channel";

Time-to-Convert

SELECT "Source",
       AVG(DATEDIFF('day', created_at, "Converted At"::DATE)) as avg_days_to_convert,
       MEDIAN(DATEDIFF('day', created_at, "Converted At"::DATE)) as median_days
FROM v_leads
WHERE "Status" = 'Converted' AND "Converted At" IS NOT NULL
GROUP BY "Source";

Report-JSON Format

Generate Recharts-compatible report cards:

{
  "type": "report",
  "title": "Pipeline Analytics — February 2026",
  "generated_at": "2026-02-17T14:30:00Z",
  "panels": [
    {
      "title": "Pipeline Funnel",
      "type": "funnel",
      "data": [
        {"name": "New Leads", "value": 200},
        {"name": "Contacted", "value": 145},
        {"name": "Qualified", "value": 67},
        {"name": "Demo Scheduled", "value": 31},
        {"name": "Closed Won", "value": 13}
      ]
    },
    {
      "title": "Outreach Activity",
      "type": "area",
      "xKey": "week",
      "series": [
        {"key": "linkedin", "name": "LinkedIn", "color": "#0A66C2"},
        {"key": "email", "name": "Email", "color": "#EA4335"}
      ],
      "data": [
        {"week": "Feb 3", "linkedin": 25, "email": 40},
        {"week": "Feb 10", "linkedin": 30, "email": 35}
      ]
    },
    {
      "title": "Lead Source Breakdown",
      "type": "donut",
      "data": [
        {"name": "LinkedIn Scrape", "value": 95, "color": "#0A66C2"},
        {"name": "YC Directory", "value": 45, "color": "#FF6600"},
        {"name": "Referral", "value": 30, "color": "#10B981"},
        {"name": "Inbound", "value": 20, "color": "#8B5CF6"}
      ]
    },
    {
      "title": "Reply Rates by Channel",
      "type": "bar",
      "xKey": "channel",
      "series": [{"key": "rate", "name": "Reply Rate %", "color": "#3B82F6"}],
      "data": [
        {"channel": "LinkedIn", "rate": 32},
        {"channel": "Email", "rate": 18},
        {"channel": "Multi-Channel", "rate": 41}
      ]
    }
  ]
}

Chart Types Available

Type	Use Case	Recharts Component
bar	Comparisons, categories	BarChart
line	Trends over time	LineChart
area	Volume over time	AreaChart
pie	Distribution (single level)	PieChart
donut	Distribution (with center metric)	PieChart (innerRadius)
funnel	Stage progression	FunnelChart
scatter	Correlation (2 variables)	ScatterChart
radar	Multi-dimension comparison	RadarChart

Pre-Built Report Templates

1. Pipeline Overview

• Funnel: Lead → Contacted → Qualified → Demo → Closed
• Donut: Lead source breakdown
• Number cards: Total leads, conversion rate, avg deal size

2. Outreach Performance

• Area: Messages sent over time (by channel)
• Bar: Reply rates by channel
• Line: Conversion trend week-over-week
• Number cards: Total sent, reply rate, meetings booked

3. Rep Performance (if multi-user)

• Bar: Leads contacted per rep
• Bar: Reply rate per rep
• Bar: Conversions per rep
• Scatter: Activity volume vs. conversion rate

4. Cohort Analysis

• Heatmap-style: Conversion rate by signup week × time elapsed
• Line: Retention/engagement curves by cohort

Natural Language Mapping

User Says	SQL Pattern	Chart Type
"show me pipeline"	GROUP BY Status	funnel
"outreach stats"	COUNT by channel + status	bar + area
"how are we converting"	conversion rates	funnel + line
"compare sources"	GROUP BY Source	bar
"weekly trend"	DATE_TRUNC + GROUP BY	line / area
"who replied"	FILTER Reply Received	table
"best performing"	ORDER BY conversion DESC	bar
"lead breakdown"	GROUP BY any dimension	pie / donut

Saving Reports

Reports can be saved as .report.json files in the workspace:

~/.openclaw/workspace/reports/
  pipeline-overview.report.json
  weekly-outreach.report.json
  monthly-review.report.json

These render as live dashboards in the Ironclaw web UI when opened.

Cron Integration

Auto-generate weekly/monthly reports:

{
  "name": "Weekly Pipeline Report",
  "schedule": { "kind": "cron", "expr": "0 9 * * MON", "tz": "America/Denver" },
  "payload": {
    "kind": "agentTurn",
    "message": "Generate weekly pipeline analytics report. Query DuckDB for this week's data. Create report-json with: funnel, outreach activity (area), reply rates (bar), source breakdown (donut). Save to workspace/reports/ and announce summary."
  }
}