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.

Mark Rachapoom

March 26, 2026·6 min read

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: 300000

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>

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();

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.