Build an Analytics Widget for DenchClaw
Build a compact analytics widget app for DenchClaw that shows key CRM metrics on your dashboard with real-time DuckDB queries and auto-refresh.
A full analytics dashboard is useful for deep dives. But most of the time, you just want to glance at a few key numbers: pipeline total, deals closing this week, new leads today. A widget is perfect for this — compact, always visible, auto-refreshing.
This guide builds an analytics widget Dench App: a small card that shows key CRM metrics and lives on your DenchClaw dashboard grid.
What You're Building#
- A compact widget showing 4-6 key metrics
- Real-time DuckDB queries with
dench.db.query() - Sparkline charts for trend context
- Auto-refresh every 5 minutes
- Click-to-expand into a full detail view
Step 1: App Setup#
mkdir -p ~/.openclaw-dench/workspace/apps/analytics-widget.dench.app.dench.yaml:
name: Analytics
description: Key CRM metrics widget with sparklines
icon: trending-up
version: 1.0.0
permissions:
- read:crm
display: widget
widget:
width: 4
height: 2
refreshMs: 300000Step 2: Widget HTML#
index.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Analytics Widget</title>
<style>
* { box-sizing: border-box; margin: 0; padding: 0; }
body { font-family: system-ui, sans-serif; background: #0f172a; color: #e2e8f0; padding: 12px; height: 100vh; display: grid; grid-template-columns: repeat(3, 1fr); grid-template-rows: 1fr 1fr; gap: 10px; }
.metric { background: #1e293b; border-radius: 8px; padding: 12px; display: flex; flex-direction: column; justify-content: space-between; }
.label { font-size: 10px; color: #64748b; text-transform: uppercase; letter-spacing: 0.04em; }
.value { font-size: 22px; font-weight: 800; line-height: 1; margin-top: 4px; }
.delta { font-size: 11px; margin-top: 4px; }
.delta.up { color: #10b981; }
.delta.down { color: #ef4444; }
.delta.neutral { color: #64748b; }
svg.sparkline { width: 100%; height: 28px; margin-top: 6px; }
.metric.wide { grid-column: span 2; }
.refresh-indicator { position: fixed; bottom: 6px; right: 8px; font-size: 9px; color: #334155; }
</style>
</head>
<body>
<div class="metric" id="m-pipeline">
<div class="label">Pipeline</div>
<div class="value" id="v-pipeline">—</div>
<div class="delta" id="d-pipeline">Loading...</div>
</div>
<div class="metric" id="m-leads">
<div class="label">New Leads</div>
<div class="value" id="v-leads">—</div>
<div class="delta" id="d-leads">This week</div>
</div>
<div class="metric" id="m-deals">
<div class="label">Open Deals</div>
<div class="value" id="v-deals">—</div>
<div class="delta" id="d-deals">—</div>
</div>
<div class="metric wide" id="m-revenue">
<div class="label">Won This Month</div>
<div class="value" id="v-revenue">—</div>
<svg class="sparkline" id="spark-revenue" viewBox="0 0 200 28" preserveAspectRatio="none"></svg>
</div>
<div class="metric" id="m-winrate">
<div class="label">Win Rate</div>
<div class="value" id="v-winrate">—</div>
<div class="delta" id="d-winrate">Last 90 days</div>
</div>
<div class="refresh-indicator" id="lastRefresh"></div>
<script src="widget.js"></script>
</body>
</html>Talk to Sales
Tell us about your team and we'll show you how Dench fits your workflow.
Step 3: Widget Logic#
widget.js:
function fmt(num) {
if (num >= 1000000) return '$' + (num / 1000000).toFixed(1) + 'M';
if (num >= 1000) return '$' + (num / 1000).toFixed(0) + 'K';
return '$' + num.toLocaleString();
}
function drawSparkline(svgId, values) {
const svg = document.getElementById(svgId);
if (!values || values.length < 2) return;
const max = Math.max(...values, 1);
const min = Math.min(...values, 0);
const range = max - min || 1;
const w = 200;
const h = 28;
const points = values.map((v, i) => {
const x = (i / (values.length - 1)) * w;
const y = h - ((v - min) / range * (h - 4)) - 2;
return `${x},${y}`;
});
const lastY = parseFloat(points[points.length - 1].split(',')[1]);
const lastX = w;
svg.innerHTML = `
<defs>
<linearGradient id="sg-${svgId}" x1="0" y1="0" x2="0" y2="1">
<stop offset="0%" stop-color="#6366f1" stop-opacity="0.3"/>
<stop offset="100%" stop-color="#6366f1" stop-opacity="0"/>
</linearGradient>
</defs>
<polygon points="${points.join(' ')} ${lastX},${h} 0,${h}" fill="url(#sg-${svgId})"/>
<polyline points="${points.join(' ')}" fill="none" stroke="#6366f1" stroke-width="1.5"/>
<circle cx="${lastX - (w/(values.length-1))}" cy="${lastY}" r="2.5" fill="#6366f1"/>
`;
}
async function loadMetrics() {
const [pipeline, leads, deals, revenue, winrate, revenueHistory] = await Promise.all([
// Total open pipeline
dench.db.query(`SELECT SUM(CAST("Value" AS DOUBLE)) AS total FROM v_deals WHERE "Stage" NOT IN ('Closed Won', 'Closed Lost')`),
// New leads this week
dench.db.query(`SELECT COUNT(*) AS count FROM v_people WHERE "Status" = 'Lead' AND created_at >= CURRENT_DATE - INTERVAL '7 days'`),
// Open deal count + change
dench.db.query(`SELECT COUNT(*) AS count FROM v_deals WHERE "Stage" NOT IN ('Closed Won', 'Closed Lost')`),
// Revenue won this month
dench.db.query(`SELECT SUM(CAST("Value" AS DOUBLE)) AS total FROM v_deals WHERE "Stage" = 'Closed Won' AND DATE_TRUNC('month', CAST("Close Date" AS DATE)) = DATE_TRUNC('month', CURRENT_DATE)`),
// Win rate last 90 days
dench.db.query(`SELECT ROUND(100.0 * SUM(CASE WHEN "Stage" = 'Closed Won' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 0) AS rate FROM v_deals WHERE "Stage" IN ('Closed Won', 'Closed Lost') AND "Close Date" >= CURRENT_DATE - INTERVAL '90 days'`),
// Last 6 months revenue
dench.db.query(`SELECT DATE_TRUNC('month', CAST("Close Date" AS DATE)) AS month, SUM(CAST("Value" AS DOUBLE)) AS total FROM v_deals WHERE "Stage" = 'Closed Won' AND "Close Date" >= CURRENT_DATE - INTERVAL '6 months' GROUP BY month ORDER BY month`)
]);
// Pipeline
const pipelineVal = pipeline[0]?.total || 0;
document.getElementById('v-pipeline').textContent = fmt(pipelineVal);
document.getElementById('d-pipeline').textContent = 'Open opportunities';
document.getElementById('d-pipeline').className = 'delta neutral';
// New leads
const leadsCount = leads[0]?.count || 0;
document.getElementById('v-leads').textContent = leadsCount;
document.getElementById('d-leads').textContent = leadsCount > 0 ? `+${leadsCount} this week` : 'None this week';
document.getElementById('d-leads').className = `delta ${leadsCount > 0 ? 'up' : 'neutral'}`;
// Open deals
document.getElementById('v-deals').textContent = deals[0]?.count || 0;
document.getElementById('d-deals').textContent = 'active';
// Revenue
const revenueVal = revenue[0]?.total || 0;
document.getElementById('v-revenue').textContent = fmt(revenueVal);
// Sparkline
const sparkValues = revenueHistory.map(r => r.total || 0);
if (sparkValues.length > 0) drawSparkline('spark-revenue', sparkValues);
// Win rate
const rate = winrate[0]?.rate || 0;
document.getElementById('v-winrate').textContent = `${rate}%`;
document.getElementById('d-winrate').textContent = 'Last 90 days';
document.getElementById('d-winrate').className = `delta ${rate >= 50 ? 'up' : rate >= 30 ? 'neutral' : 'down'}`;
// Update refresh time
document.getElementById('lastRefresh').textContent = `Updated ${new Date().toLocaleTimeString()}`;
}
// Auto-refresh
setInterval(loadMetrics, 300000);
dench.events.on('entry:updated', loadMetrics);
dench.events.on('entry:created', loadMetrics);
loadMetrics();Extending the Widget#
Configurable metrics: Add a settings mode (long-press or gear icon) that lets you choose which 6 metrics to show from a list of available queries.
Alert thresholds: Add red highlighting when a metric drops below a threshold — e.g., turn the pipeline number red if it drops below your monthly target.
Click-to-drill: Add onclick handlers to each metric card that open the full analytics tab or filter the CRM table to the relevant entries.
Frequently Asked Questions#
How do I add this widget to my DenchClaw home screen?#
Save the .dench.app folder to ~/.openclaw-dench/workspace/apps/. It will appear in the sidebar. If it's configured as a display: widget, it will be available to add to the dashboard grid.
Can I show different metrics for different team members?#
Add an owner filter: load the current user's identity via dench.store.get('current_user') and add WHERE "Owner" = '${owner}' to the queries.
How do I add more metrics beyond 6?#
Expand the grid layout in CSS (grid-template-columns: repeat(4, 1fr) etc.) and add more metric divs. Match the count to your DuckDB queries.
What's the minimum refresh interval?#
The refreshMs in .dench.yaml controls the auto-refresh. The minimum recommended is 60000 (1 minute) to avoid excessive DuckDB query load. For real-time dashboards, use dench.events.on() instead of polling.
Ready to try DenchClaw? Install in one command: npx denchclaw. Full setup guide →
