Pipeline Analytics — NL → SQL → 交互式图表

将自然语言问题转换为 DuckDB 查询，并将结果渲染为聊天内联的交互式 Recharts 仪表板。

工作流程

用户用自然语言提问
→ 转换为针对工作区透视视图 (v_*) 的 DuckDB SQL
→ 执行查询
→ 将结果格式化为 report-json
→ 渲染为交互式 Recharts 组件

DuckDB 查询模式

发现 — 存在哪些对象？

sql -- 列出所有对象及其条目数量 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;

-- 列出对象的字段
SELECT f.name, f.fieldtype, f.displayname
FROM fields f
JOIN objects o ON f.object_id = o.id
WHERE o.name = leads
ORDER BY f.position;

-- 可用的透视视图
SELECT tablename FROM informationschema.tables
WHERE tablename LIKE v%;

常见分析查询

管道漏斗

sql 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;

随时间变化的外联活动

sql 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;

按来源的转化率

sql 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;

回复率分析

sql 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;

转化时间

sql SELECT Source, AVG(DATEDIFF(day, createdat, Converted At::DATE)) as avgdaystoconvert, MEDIAN(DATEDIFF(day, createdat, Converted At::DATE)) as mediandays FROM v_leads WHERE Status = Converted AND Converted At IS NOT NULL GROUP BY Source;

Report-JSON 格式

生成 Recharts 兼容的报告卡片：

json
{
type: report,
title: Pipeline Analytics — 2026年2月,
generated_at: 2026-02-17T14:30:00Z,
panels: [
{
title: 管道漏斗,
type: funnel,
data: [
{name: 新线索, value: 200},
{name: 已联系, value: 145},
{name: 已合格, value: 67},
{name: 已安排演示, value: 31},
{name: 已成交, value: 13}
]
},
{
title: 外联活动,
type: area,
xKey: week,
series: [
{key: linkedin, name: LinkedIn, color: #0A66C2},
{key: email, name: 电子邮件, color: #EA4335}
],
data: [
{week: 2月3日, linkedin: 25, email: 40},
{week: 2月10日, linkedin: 30, email: 35}
]
},
{
title: 线索来源细分,
type: donut,
data: [
{name: LinkedIn 抓取, value: 95, color: #0A66C2},
{name: YC 目录, value: 45, color: #FF6600},
{name: 推荐, value: 30, color: #10B981},
{name: 入站, value: 20, color: #8B5CF6}
]
},
{
title: 按渠道的回复率,
type: bar,
xKey: channel,
series: [{key: rate, name: 回复率 %, color: #3B82F6}],
data: [
{channel: LinkedIn, rate: 32},
{channel: 电子邮件, rate: 18},
{channel: 多渠道, rate: 41}
]
}
]
}

可用图表类型

类型	用例	Recharts 组件
bar	比较、分类	BarChart
line

随时间变化的趋势 | LineChart | | area | 随时间变化的量 | AreaChart | | pie | 分布（单层） | PieChart | | donut | 分布（带中心指标） | PieChart (innerRadius) | | funnel | 阶段进展 | FunnelChart | | scatter | 相关性（2个变量） | ScatterChart | | radar | 多维比较 | RadarChart |

预建报告模板

1. 管道概览

• - 漏斗：线索 → 已联系 → 已合格 → 演示 → 已成交
• 环形图：线索来源细分
• 数字卡片：总线索数、转化率、平均交易规模

2. 外联绩效

• - 面积图：随时间发送的消息（按渠道）
• 柱状图：按渠道的回复率
• 折线图：周环比转化趋势
• 数字卡片：总发送量、回复率、已预约会议

3. 人员绩效（多用户时）

• - 柱状图：每人联系的线索数
• 柱状图：每人回复率
• 柱状图：每人转化数
• 散点图：活动量与转化率

4. 群组分析

• - 热力图风格：按注册周 × 经过时间的转化率
• 折线图：按群组的留存/参与曲线

自然语言映射

用户说	SQL 模式	图表类型
显示管道	GROUP BY Status	funnel
外联统计

COUNT by channel + status | bar + area | | 转化情况如何 | conversion rates | funnel + line | | 比较来源 | GROUP BY Source | bar | | 每周趋势 | DATE_TRUNC + GROUP BY | line / area | | 谁回复了 | FILTER Reply Received | table | | 表现最好的 | ORDER BY conversion DESC | bar | | 线索细分 | GROUP BY any dimension | pie / donut |

保存报告

报告可以保存为工作区中的 .report.json 文件：

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

在 Ironclaw Web UI 中打开时，这些文件会渲染为实时仪表板。

Cron 集成

自动生成每周/每月报告：
json
{
name: 每周管道报告,
schedule: { kind: cron, expr: 0 9 MON, tz: America/Denver },
payload: {
kind: agentTurn,
message: 生成每周管道分析报告。查询本周数据的 DuckDB。创建包含以下内容的 report-json：漏斗、外联活动（面积图）、回复率（柱状图）、来源细分（环形图）。保存到 workspace/re