OpenClaw for Data Analysis: Build Automated Dashboards and Reports

Data analysis shouldn't be a manual chore. With OpenClaw, you can automate everything from SQL queries and API data collection to real-time dashboards and scheduled reports. Here's how to build a complete data analysis pipeline that runs itself.

Why OpenClaw for Data Analysis?

Most data teams spend 80% of their time on data collection, cleaning, and basic reporting—not on actual analysis. OpenClaw changes that by automating the repetitive parts:

• Scheduled data collection from APIs, databases, and web sources
• Automated SQL queries with parameterized inputs
• Real-time data transformation and cleaning
• Dynamic visualization generation (charts, tables, dashboards)
• Scheduled report delivery via email, Slack, or webhook

Setting Up Your Data Analysis Environment

First, let's configure OpenClaw with the tools you'll need. I recommend starting with these skills:

1. Install Essential Skills

# Install data-focused skills
clawhub install csv-analysis
clawhub install sql-query
clawhub install api-collector
clawhub install chart-generator
clawhub install report-scheduler

2. Configure Database Connections

Create a configuration file for your database connections. Here's an example for PostgreSQL:

// ~/.openclaw/config/data-sources.json
{
  "databases": {
    "postgres": {
      "host": "localhost",
      "port": 5432,
      "database": "analytics",
      "username": "${DB_USER}",
      "password": "${DB_PASSWORD}",
      "ssl": true
    },
    "clickhouse": {
      "host": "clickhouse.example.com",
      "port": 9440,
      "database": "events",
      "username": "${CLICKHOUSE_USER}",
      "password": "${CLICKHOUSE_PASSWORD}"
    }
  },
  "apis": {
    "google_analytics": {
      "client_id": "${GA_CLIENT_ID}",
      "client_secret": "${GA_CLIENT_SECRET}",
      "refresh_token": "${GA_REFRESH_TOKEN}"
    },
    "stripe": {
      "api_key": "${STRIPE_API_KEY}"
    }
  }
}

Building Your First Data Analysis Skill

Let's create a skill that runs daily revenue analysis. This skill will:

1. Query Stripe for yesterday's transactions
2. Calculate revenue by product and country
3. Generate a visualization
4. Post results to Slack

SKILL.md Structure

# daily-revenue-analysis

## Description
Daily revenue analysis and reporting for Stripe transactions.

## Installation
```bash
# Clone the skill
git clone https://github.com/yourusername/daily-revenue-analysis.git
cd daily-revenue-analysis

# Install dependencies
npm install

# Configure environment
cp .env.example .env
# Edit .env with your Stripe and Slack credentials
```

## Usage
```bash
# Run manually
node index.js --date $(date -v-1d '+%Y-%m-%d')

# Schedule via cron
0 9 * * * cd /path/to/skill && node index.js --date $(date -v-1d '+%Y-%m-%d')
```

Implementation Code

// index.js - Main analysis script
const { Stripe } = require('stripe');
const { WebClient } = require('@slack/web-api');
const { createCanvas } = require('canvas');
const fs = require('fs');
const path = require('path');

class DailyRevenueAnalyzer {
  constructor() {
    this.stripe = new Stripe(process.env.STRIPE_API_KEY);
    this.slack = new WebClient(process.env.SLACK_TOKEN);
  }

  async analyze(date) {
    console.log(`Analyzing revenue for ${date}`);
    
    // 1. Fetch transactions
    const charges = await this.stripe.charges.list({
      created: {
        gte: Math.floor(new Date(date).getTime() / 1000),
        lt: Math.floor(new Date(date).getTime() / 1000) + 86400
      },
      limit: 100
    });

    // 2. Process data
    const revenueByProduct = {};
    const revenueByCountry = {};
    
    for (const charge of charges.data) {
      const amount = charge.amount / 100; // Convert from cents
      const product = charge.metadata.product || 'unknown';
      const country = charge.billing_details.address?.country || 'unknown';
      
      revenueByProduct[product] = (revenueByProduct[product] || 0) + amount;
      revenueByCountry[country] = (revenueByCountry[country] || 0) + amount;
    }

    // 3. Generate visualization
    const chartPath = await this.generateChart(revenueByProduct, date);
    
    // 4. Send to Slack
    await this.sendToSlack({
      date,
      totalRevenue: charges.data.reduce((sum, c) => sum + (c.amount / 100), 0),
      byProduct: revenueByProduct,
      byCountry: revenueByCountry,
      chartPath
    });

    return { success: true, analyzed: charges.data.length };
  }

  async generateChart(revenueByProduct, date) {
    const products = Object.keys(revenueByProduct);
    const revenues = Object.values(revenueByProduct);
    
    const canvas = createCanvas(800, 400);
    const ctx = canvas.getContext('2d');
    
    // Draw chart
    ctx.fillStyle = 'white';
    ctx.fillRect(0, 0, 800, 400);
    
    ctx.fillStyle = '#3b82f6';
    const maxRevenue = Math.max(...revenues);
    const barWidth = 600 / products.length;
    
    products.forEach((product, i) => {
      const height = (revenues[i] / maxRevenue) * 300;
      ctx.fillRect(100 + i * barWidth, 350 - height, barWidth - 10, height);
      
      // Product label
      ctx.fillStyle = '#374151';
      ctx.font = '12px Arial';
      ctx.fillText(product, 100 + i * barWidth, 370);
    });
    
    // Save chart
    const chartPath = path.join(__dirname, `charts/revenue-${date}.png`);
    const buffer = canvas.toBuffer('image/png');
    fs.writeFileSync(chartPath, buffer);
    
    return chartPath;
  }

  async sendToSlack(data) {
    await this.slack.files.upload({
      channels: process.env.SLACK_CHANNEL,
      file: fs.createReadStream(data.chartPath),
      title: `Daily Revenue Report - ${data.date}`,
      initial_comment: `📊 *Daily Revenue Report for ${data.date}*
` +
        `• Total Revenue: $${data.totalRevenue.toFixed(2)}
` +
        `• Top Product: ${Object.entries(data.byProduct).sort((a, b) => b[1] - a[1])[0]?.[0] || 'N/A'}
` +
        `• Transactions Analyzed: ${Object.values(data.byProduct).reduce((a, b) => a + b, 0)}`
    });
  }
}

// CLI entry point
const args = process.argv.slice(2);
const dateArg = args.find(arg => arg.startsWith('--date='))?.split('=')[1] || 
                new Date(Date.now() - 86400000).toISOString().split('T')[0];

const analyzer = new DailyRevenueAnalyzer();
analyzer.analyze(dateArg)
  .then(result => {
    console.log('Analysis complete:', result);
    process.exit(0);
  })
  .catch(error => {
    console.error('Analysis failed:', error);
    process.exit(1);
  });

Advanced: Real-Time Dashboard with WebSocket Updates

For real-time monitoring, you can build a dashboard that updates automatically. Here's a simple Express server that serves live metrics:

// dashboard-server.js
const express = require('express');
const http = require('http');
const WebSocket = require('ws');
const { Pool } = require('pg');

const app = express();
const server = http.createServer(app);
const wss = new WebSocket.Server({ server });

const pool = new Pool({
  host: process.env.PG_HOST,
  port: process.env.PG_PORT,
  database: process.env.PG_DATABASE,
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD
});

// Serve static dashboard
app.use(express.static('public'));

// WebSocket for real-time updates
wss.on('connection', (ws) => {
  console.log('Dashboard client connected');
  
  // Send initial data
  updateClient(ws);
  
  // Update every 30 seconds
  const interval = setInterval(() => updateClient(ws), 30000);
  
  ws.on('close', () => {
    clearInterval(interval);
    console.log('Dashboard client disconnected');
  });
});

async function updateClient(ws) {
  try {
    const metrics = await getLiveMetrics();
    ws.send(JSON.stringify({
      type: 'metrics_update',
      timestamp: new Date().toISOString(),
      metrics
    }));
  } catch (error) {
    console.error('Failed to update client:', error);
  }
}

async function getLiveMetrics() {
  const [revenueResult, usersResult, eventsResult] = await Promise.all([
    pool.query(`
      SELECT 
        SUM(amount) as daily_revenue,
        COUNT(DISTINCT user_id) as paying_users,
        AVG(amount) as avg_order_value
      FROM transactions 
      WHERE created_at >= NOW() - INTERVAL '24 hours'
    `),
    pool.query(`
      SELECT 
        COUNT(*) as active_users,
        COUNT(DISTINCT country) as countries
      FROM users 
      WHERE last_seen >= NOW() - INTERVAL '1 hour'
    `),
    pool.query(`
      SELECT 
        event_type,
        COUNT(*) as count
      FROM events 
      WHERE created_at >= NOW() - INTERVAL '1 hour'
      GROUP BY event_type 
      ORDER BY count DESC 
      LIMIT 5
    `)
  ]);
  
  return {
    revenue: revenueResult.rows[0],
    users: usersResult.rows[0],
    topEvents: eventsResult.rows
  };
}

server.listen(3000, () => {
  console.log('Dashboard server running on http://localhost:3000');
});

Integrating with Existing BI Tools

OpenClaw can enhance your existing BI stack. Here's how to connect with popular tools:

Metabase Integration

# metabase-webhook.py
import requests
import json
from datetime import datetime, timedelta

class MetabaseWebhook:
    def __init__(self, metabase_url, api_key):
        self.metabase_url = metabase_url
        self.headers = {
            'Content-Type': 'application/json',
            'X-Metabase-Session': api_key
        }
    
    def trigger_dashboard_refresh(self, dashboard_id):
        """Trigger a Metabase dashboard refresh via OpenClaw"""
        url = f"{self.metabase_url}/api/dashboard/{dashboard_id}/refresh"
        response = requests.post(url, headers=self.headers)
        return response.json()
    
    def export_dashboard_pdf(self, dashboard_id, filename):
        """Export dashboard as PDF for email reports"""
        url = f"{self.metabase_url}/api/dashboard/{dashboard_id}/export/pdf"
        response = requests.get(url, headers=self.headers, stream=True)
        
        with open(filename, 'wb') as f:
            for chunk in response.iter_content(chunk_size=8192):
                f.write(chunk)
        
        return filename

Tableau Prep Automation

# tableau-prep-flow.yaml
name: Daily Data Pipeline
schedule: "0 2 * * *"  # Run at 2 AM daily

steps:
  - name: Extract Source Data
    type: sql
    query: |
      SELECT * FROM source_table
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
  
  - name: Clean and Transform
    type: python
    script: |
      import pandas as pd
      df = pd.read_csv('input.csv')
      # Data cleaning logic
      df['clean_column'] = df['dirty_column'].str.strip().str.lower()
      df.to_csv('cleaned.csv', index=False)
  
  - name: Load to Warehouse
    type: bigquery
    dataset: analytics
    table: daily_metrics
    write_disposition: WRITE_TRUNCATE
  
  - name: Send Completion Alert
    type: slack
    channel: "#data-alerts"
    message: "✅ Daily data pipeline completed at {{timestamp}}"

Monitoring and Alerting

Set up monitoring for your data pipelines. Here's a simple health check skill:

#!/bin/bash
# health-check.sh

# Check database connectivity
pg_isready -h $DB_HOST -p $DB_PORT || {
  echo "Database connection failed"
  curl -X POST -H 'Content-type: application/json' \
    --data '{"text":"🚨 Database connection failed for analytics pipeline"}' \
    $SLACK_WEBHOOK
  exit 1
}

# Check data freshness
DATA_AGE=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c \
  "SELECT EXTRACT(EPOCH FROM (NOW() - MAX(created_at))) FROM analytics_table;")

if [ -z "$DATA_AGE" ]; then
  DATA_AGE=0
fi
if [ $DATA_AGE -gt 86400 ]; then
  echo "Data is stale (older than 24 hours)"
  curl -X POST -H 'Content-type: application/json' \
    --data '{"text":"⚠️ Analytics data is stale - last update was more than 24 hours ago"}' \
    $SLACK_WEBHOOK
  exit 2
fi

echo "Health check passed"
exit 0

Schedule this health check to run hourly via cron, and OpenClaw will alert you immediately if anything goes wrong with your data pipelines.

Getting Started with Your First Data Automation

Start with a simple, high-value automation:

1. Choose one repetitive data task: Daily report generation, data cleaning, or API sync
2. Document the manual steps: Write down exactly what you do
3. Create a skill: Turn those steps into a SKILL.md with clear instructions
4. Test with sample data: Run against a small subset first
5. Schedule it: Use OpenClaw's cron to run automatically
6. Monitor and refine: Check logs, adjust as needed