Agent Skills Framework Extension

Business Analytics Patterns Skill

When to Use This Skill

Use this skill when implementing business analytics patterns patterns in your codebase.

How to Use This Skill

1. Review the patterns and examples below
2. Apply the relevant patterns to your implementation
3. Follow the best practices outlined in this skill

Data analysis, reporting dashboards, BI patterns, and metrics visualization.

Core Capabilities

1. KPI Calculation - Business metric computation
2. Cohort Analysis - User behavior over time
3. Funnel Analysis - Conversion tracking
4. Dashboard Design - Visualization patterns
5. Report Generation - Automated reporting

KPI Calculation Queries

-- Daily Active Users (DAU), Weekly (WAU), Monthly (MAU)
WITH daily_users AS (
    SELECT
        DATE(event_time) as date,
        COUNT(DISTINCT user_id) as dau
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY DATE(event_time)
),
weekly_users AS (
    SELECT
        DATE_TRUNC('week', event_time) as week,
        COUNT(DISTINCT user_id) as wau
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '12 weeks'
    GROUP BY DATE_TRUNC('week', event_time)
),
monthly_users AS (
    SELECT
        DATE_TRUNC('month', event_time) as month,
        COUNT(DISTINCT user_id) as mau
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', event_time)
)
SELECT
    d.date,
    d.dau,
    w.wau,
    m.mau,
    ROUND(d.dau::NUMERIC / NULLIF(w.wau, 0) * 100, 2) as dau_wau_ratio,
    ROUND(d.dau::NUMERIC / NULLIF(m.mau, 0) * 100, 2) as dau_mau_ratio
FROM daily_users d
LEFT JOIN weekly_users w ON DATE_TRUNC('week', d.date) = w.week
LEFT JOIN monthly_users m ON DATE_TRUNC('month', d.date) = m.month
ORDER BY d.date DESC;

-- Revenue Metrics
WITH revenue_data AS (
    SELECT
        DATE(created_at) as date,
        SUM(amount) as revenue,
        COUNT(DISTINCT user_id) as paying_users,
        COUNT(*) as transactions
    FROM payments
    WHERE status = 'completed'
        AND created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY DATE(created_at)
)
SELECT
    date,
    revenue,
    paying_users,
    transactions,
    ROUND(revenue / NULLIF(paying_users, 0), 2) as arpu,
    ROUND(revenue / NULLIF(transactions, 0), 2) as avg_transaction,
    SUM(revenue) OVER (ORDER BY date) as cumulative_revenue,
    LAG(revenue) OVER (ORDER BY date) as prev_day_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY date)) /
          NULLIF(LAG(revenue) OVER (ORDER BY date), 0) * 100, 2) as revenue_growth_pct
FROM revenue_data
ORDER BY date DESC;

-- Customer Lifetime Value (CLV)
WITH customer_revenue AS (
    SELECT
        user_id,
        MIN(created_at) as first_purchase,
        MAX(created_at) as last_purchase,
        COUNT(*) as total_orders,
        SUM(amount) as total_revenue,
        DATE_PART('day', MAX(created_at) - MIN(created_at)) as customer_lifespan_days
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),
cohort_stats AS (
    SELECT
        DATE_TRUNC('month', first_purchase) as cohort_month,
        COUNT(*) as cohort_size,
        AVG(total_orders) as avg_orders,
        AVG(total_revenue) as avg_revenue,
        AVG(customer_lifespan_days) as avg_lifespan_days
    FROM customer_revenue
    GROUP BY DATE_TRUNC('month', first_purchase)
)
SELECT
    cohort_month,
    cohort_size,
    ROUND(avg_orders, 2) as avg_orders_per_customer,
    ROUND(avg_revenue, 2) as avg_revenue_per_customer,
    ROUND(avg_lifespan_days, 0) as avg_customer_lifespan_days,
    ROUND(avg_revenue / NULLIF(avg_lifespan_days / 365.0, 0), 2) as annual_customer_value
FROM cohort_stats
ORDER BY cohort_month DESC;

Cohort Analysis

-- Retention Cohort Analysis
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) as cohort_month
    FROM events
    WHERE event_type = 'signup'
    GROUP BY user_id
),
user_activity AS (
    SELECT DISTINCT
        e.user_id,
        uc.cohort_month,
        DATE_TRUNC('month', e.event_time) as activity_month
    FROM events e
    JOIN user_cohorts uc ON e.user_id = uc.user_id
),
cohort_size AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT user_id) as cohort_users
    FROM user_cohorts
    GROUP BY cohort_month
),
retention_data AS (
    SELECT
        ua.cohort_month,
        ua.activity_month,
        COUNT(DISTINCT ua.user_id) as active_users,
        EXTRACT(MONTH FROM AGE(ua.activity_month, ua.cohort_month)) as month_number
    FROM user_activity ua
    GROUP BY ua.cohort_month, ua.activity_month
)
SELECT
    rd.cohort_month,
    cs.cohort_users,
    rd.month_number,
    rd.active_users,
    ROUND(rd.active_users::NUMERIC / cs.cohort_users * 100, 2) as retention_pct
FROM retention_data rd
JOIN cohort_size cs ON rd.cohort_month = cs.cohort_month
WHERE rd.month_number <= 12
ORDER BY rd.cohort_month, rd.month_number;

-- Revenue Cohort
WITH revenue_cohorts AS (
    SELECT
        o.user_id,
        DATE_TRUNC('month', MIN(o.created_at)) as cohort_month,
        DATE_TRUNC('month', o.created_at) as order_month,
        SUM(o.amount) as revenue
    FROM orders o
    WHERE o.status = 'completed'
    GROUP BY o.user_id, DATE_TRUNC('month', o.created_at)
),
cohort_revenue AS (
    SELECT
        cohort_month,
        order_month,
        SUM(revenue) as total_revenue,
        COUNT(DISTINCT user_id) as paying_users,
        EXTRACT(MONTH FROM AGE(order_month, cohort_month)) as month_number
    FROM revenue_cohorts
    GROUP BY cohort_month, order_month
)
SELECT
    cohort_month,
    month_number,
    total_revenue,
    paying_users,
    ROUND(total_revenue / NULLIF(paying_users, 0), 2) as arpu,
    SUM(total_revenue) OVER (
        PARTITION BY cohort_month
        ORDER BY month_number
    ) as cumulative_revenue
FROM cohort_revenue
WHERE month_number <= 12
ORDER BY cohort_month, month_number;

Funnel Analysis

-- Conversion Funnel
WITH funnel_stages AS (
    SELECT
        session_id,
        user_id,
        MAX(CASE WHEN event_type = 'page_view' AND page = '/landing' THEN 1 ELSE 0 END) as landed,
        MAX(CASE WHEN event_type = 'signup_started' THEN 1 ELSE 0 END) as signup_started,
        MAX(CASE WHEN event_type = 'signup_completed' THEN 1 ELSE 0 END) as signup_completed,
        MAX(CASE WHEN event_type = 'first_action' THEN 1 ELSE 0 END) as activated,
        MAX(CASE WHEN event_type = 'subscription_started' THEN 1 ELSE 0 END) as subscribed
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY session_id, user_id
)
SELECT
    'Landing Page' as stage,
    COUNT(*) as users,
    100.0 as pct_of_total,
    100.0 as pct_of_prev
FROM funnel_stages WHERE landed = 1

UNION ALL

SELECT
    'Signup Started' as stage,
    COUNT(*) as users,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE landed = 1), 0) * 100, 2) as pct_of_total,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE landed = 1), 0) * 100, 2) as pct_of_prev
FROM funnel_stages WHERE signup_started = 1

UNION ALL

SELECT
    'Signup Completed' as stage,
    COUNT(*) as users,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE landed = 1), 0) * 100, 2) as pct_of_total,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE signup_started = 1), 0) * 100, 2) as pct_of_prev
FROM funnel_stages WHERE signup_completed = 1

UNION ALL

SELECT
    'Activated' as stage,
    COUNT(*) as users,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE landed = 1), 0) * 100, 2) as pct_of_total,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE signup_completed = 1), 0) * 100, 2) as pct_of_prev
FROM funnel_stages WHERE activated = 1

UNION ALL

SELECT
    'Subscribed' as stage,
    COUNT(*) as users,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE landed = 1), 0) * 100, 2) as pct_of_total,
    ROUND(COUNT(*)::NUMERIC / NULLIF((SELECT COUNT(*) FROM funnel_stages WHERE activated = 1), 0) * 100, 2) as pct_of_prev
FROM funnel_stages WHERE subscribed = 1;

Dashboard API

// src/analytics/dashboard-service.ts
import { Pool } from 'pg';

interface MetricValue {
  value: number;
  change: number;
  changePercent: number;
  trend: 'up' | 'down' | 'stable';
}

interface DashboardMetrics {
  dau: MetricValue;
  mau: MetricValue;
  revenue: MetricValue;
  arpu: MetricValue;
  churnRate: MetricValue;
  nps: MetricValue;
}

interface TimeSeriesPoint {
  date: string;
  value: number;
}

export class DashboardService {
  constructor(private readonly db: Pool) {}

  async getMetrics(dateRange: { start: Date; end: Date }): Promise<DashboardMetrics> {
    const [current, previous] = await Promise.all([
      this.getMetricsForPeriod(dateRange.start, dateRange.end),
      this.getMetricsForPeriod(
        this.subtractDays(dateRange.start, this.daysDiff(dateRange.start, dateRange.end)),
        dateRange.start
      ),
    ]);

    return {
      dau: this.calculateMetricValue(current.dau, previous.dau),
      mau: this.calculateMetricValue(current.mau, previous.mau),
      revenue: this.calculateMetricValue(current.revenue, previous.revenue),
      arpu: this.calculateMetricValue(current.arpu, previous.arpu),
      churnRate: this.calculateMetricValue(current.churnRate, previous.churnRate, true),
      nps: this.calculateMetricValue(current.nps, previous.nps),
    };
  }

  async getRevenueTimeSeries(
    start: Date,
    end: Date,
    granularity: 'day' | 'week' | 'month'
  ): Promise<TimeSeriesPoint[]> {
    const truncFunc = granularity === 'day' ? 'day' : granularity === 'week' ? 'week' : 'month';

    const result = await this.db.query(
      `SELECT
         DATE_TRUNC($1, created_at) as date,
         SUM(amount) as value
       FROM payments
       WHERE status = 'completed'
         AND created_at BETWEEN $2 AND $3
       GROUP BY DATE_TRUNC($1, created_at)
       ORDER BY date`,
      [truncFunc, start, end]
    );

    return result.rows.map(row => ({
      date: row.date.toISOString().split('T')[0],
      value: parseFloat(row.value),
    }));
  }

  async getCohortRetention(cohortMonth: string): Promise<number[]> {
    const result = await this.db.query(
      `WITH user_cohort AS (
         SELECT user_id
         FROM users
         WHERE DATE_TRUNC('month', created_at) = $1
       ),
       monthly_activity AS (
         SELECT DISTINCT
           e.user_id,
           EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', e.event_time), $1::date)) as month_num
         FROM events e
         JOIN user_cohort uc ON e.user_id = uc.user_id
       )
       SELECT
         month_num,
         COUNT(DISTINCT user_id)::FLOAT /
           (SELECT COUNT(*) FROM user_cohort) * 100 as retention
       FROM monthly_activity
       GROUP BY month_num
       ORDER BY month_num`,
      [cohortMonth]
    );

    return result.rows.map(row => parseFloat(row.retention.toFixed(2)));
  }

  async getFunnelData(
    funnelId: string,
    start: Date,
    end: Date
  ): Promise<{ stage: string; users: number; conversionRate: number }[]> {
    // Funnel configuration would come from database
    const funnelConfig = await this.getFunnelConfig(funnelId);

    const stages = [];
    let prevCount = 0;

    for (const stage of funnelConfig.stages) {
      const result = await this.db.query(
        `SELECT COUNT(DISTINCT user_id) as count
         FROM events
         WHERE event_type = $1
           AND event_time BETWEEN $2 AND $3`,
        [stage.eventType, start, end]
      );

      const count = parseInt(result.rows[0].count);
      const conversionRate = prevCount === 0 ? 100 : (count / prevCount) * 100;

      stages.push({
        stage: stage.name,
        users: count,
        conversionRate: parseFloat(conversionRate.toFixed(2)),
      });

      prevCount = count;
    }

    return stages;
  }

  private calculateMetricValue(
    current: number,
    previous: number,
    lowerIsBetter = false
  ): MetricValue {
    const change = current - previous;
    const changePercent = previous === 0 ? 0 : (change / previous) * 100;

    let trend: 'up' | 'down' | 'stable';
    if (Math.abs(changePercent) < 1) {
      trend = 'stable';
    } else if (lowerIsBetter) {
      trend = change < 0 ? 'up' : 'down';
    } else {
      trend = change > 0 ? 'up' : 'down';
    }

    return {
      value: current,
      change,
      changePercent: parseFloat(changePercent.toFixed(2)),
      trend,
    };
  }

  private daysDiff(start: Date, end: Date): number {
    return Math.ceil((end.getTime() - start.getTime()) / (1000 * 60 * 60 * 24));
  }

  private subtractDays(date: Date, days: number): Date {
    return new Date(date.getTime() - days * 24 * 60 * 60 * 1000);
  }
}

Report Generation

# src/analytics/report_generator.py
from datetime import datetime, timedelta
from dataclasses import dataclass
from typing import List, Dict, Any
import pandas as pd
from jinja2 import Template

@dataclass
class ReportConfig:
    name: str
    recipients: List[str]
    schedule: str  # cron expression
    sections: List[str]
    format: str  # 'html', 'pdf', 'csv'

class ReportGenerator:
    def __init__(self, db_connection, config: ReportConfig):
        self.db = db_connection
        self.config = config

    def generate_executive_summary(
        self,
        start_date: datetime,
        end_date: datetime
    ) -> Dict[str, Any]:
        """Generate executive summary metrics."""
        metrics = {
            'period': f"{start_date.strftime('%Y-%m-%d')} to {end_date.strftime('%Y-%m-%d')}",
            'kpis': self._get_kpis(start_date, end_date),
            'revenue': self._get_revenue_summary(start_date, end_date),
            'users': self._get_user_summary(start_date, end_date),
            'highlights': self._get_highlights(start_date, end_date),
        }
        return metrics

    def _get_kpis(self, start: datetime, end: datetime) -> Dict[str, Any]:
        query = """
        WITH current_period AS (
            SELECT
                COUNT(DISTINCT user_id) as active_users,
                SUM(amount) as revenue,
                COUNT(*) as transactions
            FROM events e
            LEFT JOIN payments p ON e.user_id = p.user_id
            WHERE e.event_time BETWEEN %s AND %s
        ),
        previous_period AS (
            SELECT
                COUNT(DISTINCT user_id) as active_users,
                SUM(amount) as revenue,
                COUNT(*) as transactions
            FROM events e
            LEFT JOIN payments p ON e.user_id = p.user_id
            WHERE e.event_time BETWEEN %s AND %s
        )
        SELECT
            c.active_users, c.revenue, c.transactions,
            p.active_users as prev_active_users,
            p.revenue as prev_revenue,
            p.transactions as prev_transactions
        FROM current_period c, previous_period p
        """

        period_length = (end - start).days
        prev_start = start - timedelta(days=period_length)
        prev_end = start

        df = pd.read_sql(query, self.db, params=[start, end, prev_start, prev_end])
        row = df.iloc[0]

        return {
            'active_users': {
                'value': int(row['active_users']),
                'change': self._calc_change(row['active_users'], row['prev_active_users']),
            },
            'revenue': {
                'value': float(row['revenue'] or 0),
                'change': self._calc_change(row['revenue'], row['prev_revenue']),
            },
            'transactions': {
                'value': int(row['transactions']),
                'change': self._calc_change(row['transactions'], row['prev_transactions']),
            },
        }

    def _calc_change(self, current: float, previous: float) -> float:
        if not previous:
            return 0
        return round((current - previous) / previous * 100, 2)

    def render_html(self, data: Dict[str, Any]) -> str:
        template = Template("""
        <!DOCTYPE html>
        <html>
        <head>
            <style>
                body { font-family: Arial, sans-serif; margin: 20px; }
                .metric { display: inline-block; margin: 10px; padding: 15px;
                          background: #f5f5f5; border-radius: 8px; }
                .metric-value { font-size: 24px; font-weight: bold; }
                .metric-change { font-size: 14px; color: #666; }
                .positive { color: #22c55e; }
                .negative { color: #ef4444; }
            </style>
        </head>
        <body>
            <h1>{{ config.name }}</h1>
            <p>Period: {{ data.period }}</p>

            <h2>Key Metrics</h2>
            {% for name, metric in data.kpis.items() %}
            <div class="metric">
                <div class="metric-label">{{ name | replace('_', ' ') | title }}</div>
                <div class="metric-value">{{ metric.value | format_number }}</div>
                <div class="metric-change {{ 'positive' if metric.change >= 0 else 'negative' }}">
                    {{ '+' if metric.change >= 0 else '' }}{{ metric.change }}%
                </div>
            </div>
            {% endfor %}

            <h2>Revenue Summary</h2>
            {{ data.revenue | safe }}

            <h2>User Activity</h2>
            {{ data.users | safe }}
        </body>
        </html>
        """)

        return template.render(data=data, config=self.config)

Usage Examples

Build KPI Dashboard

Apply business-analytics-patterns skill to create executive dashboard with DAU, revenue, and retention metrics

Cohort Analysis

Apply business-analytics-patterns skill to implement user retention cohort analysis with SQL

Automated Reporting

Apply business-analytics-patterns skill to create weekly business report with email delivery

Success Output

When successful, this skill MUST output:

✅ SKILL COMPLETE: business-analytics-patterns

Completed:
- [x] KPI calculation queries tested and returning correct metrics
- [x] Cohort analysis queries validated with historical data
- [x] Funnel analysis showing conversion rates at each stage
- [x] Dashboard API endpoints implemented and tested
- [x] Report generation service producing accurate outputs

Outputs:
- SQL queries for KPIs (DAU/WAU/MAU, revenue, CLV)
- Cohort retention analysis queries
- Funnel conversion tracking queries
- Dashboard service implementation (TypeScript/Python)
- Automated report generator with email delivery

Completion Checklist

Before marking this skill as complete, verify:

• KPI queries return accurate metrics matching business requirements
• Cohort analysis handles edge cases (new users, churned users)
• Funnel analysis accounts for all conversion steps
• Dashboard API response times under 2 seconds
• Report generation handles large datasets (>1M rows)
• Metrics validated against source data (sample verification)
• Date/time handling correct across timezones
• NULL handling in SQL queries (NULLIF, COALESCE)
• Performance optimized with appropriate indexes

Failure Indicators

This skill has FAILED if:

• ❌ KPI calculations return incorrect values or NULL unexpectedly
• ❌ Cohort analysis missing users or showing incorrect retention rates
• ❌ Funnel analysis conversion rates don't sum correctly
• ❌ Dashboard API slow (>5 seconds) or timing out
• ❌ Report generation fails on large datasets
• ❌ Division by zero errors not handled (missing NULLIF)
• ❌ Date arithmetic incorrect (timezone issues)
• ❌ Metrics not validated against known correct values

When NOT to Use

Do NOT use this skill when:

• Dataset is small (<1000 rows) - use simple aggregation queries instead
• Real-time analytics required (use stream processing patterns)
• Machine learning predictions needed (use ML patterns, not SQL analytics)
• Operational metrics only (use monitoring-observability skill)
• Single-user analytics (use user-behavior-analytics skill)
• Financial reporting with compliance requirements (use financial-reporting skill)
• Custom visualization needs (use data-visualization-patterns skill)

Use these alternatives instead:

• Real-time: stream-processing-patterns skill
• ML-based: predictive-analytics-patterns skill
• Operational: monitoring-observability skill

Anti-Patterns (Avoid)

Anti-Pattern	Problem	Solution
No NULLIF in division	Division by zero errors	Always use NULLIF(denominator, 0)
Ignoring timezone handling	Incorrect daily metrics	Use AT TIME ZONE or store UTC
Cartesian joins in cohorts	Exponential query time	Use explicit JOIN conditions
No query result caching	Slow dashboard loads	Implement materialized views or caching
Hardcoded date ranges	Stale metrics	Use dynamic date calculations (CURRENT_DATE)
Missing window functions	Complex self-joins	Use LAG, LEAD, SUM OVER for comparisons
No data validation	Metrics drift unnoticed	Validate against known correct values

Principles

This skill embodies:

• #1 Recycle Before Create - Use proven SQL patterns (window functions, CTEs) instead of custom logic
• #5 Eliminate Ambiguity - Clear metric definitions (DAU = distinct users per day, not sessions)
• #7 Verification Protocol - Validate metrics against source data before deploying
• #8 No Assumptions - Handle NULLs explicitly, validate date ranges
• #10 Keep It Simple - Use CTEs for readability instead of nested subqueries

Full Standard: CODITECT-STANDARD-AUTOMATION.md

Integration Points

• database-schema-optimization - Analytics query optimization
• monitoring-observability - Real-time metrics
• data-engineering-patterns - ETL for analytics