SKILL.md - sales-analytics

# SKILL.md - sales-analytics

## Overview

The sales-analytics skill provides advanced revenue analytics for farm direct-market sales. It computes season comparisons, per-product price histories, market comparisons, and trend analysis.

## Available Tools

### compare_seasons

Compare revenue performance between two seasons for the same farm.

**Input schema:**
```typescript
{
  season_a: string;    // e.g. "Spring 2025"
  season_b: string;    // e.g. "Spring 2024"
  group_by?: "product" | "market" | "category";   // default: "product"
}
```

**Returns:**
```typescript
{
  season_a: { name: string; total_revenue: number; event_count: number; avg_per_event: number };
  season_b: { name: string; total_revenue: number; event_count: number; avg_per_event: number };
  delta_revenue: number;
  delta_pct: number;
  rows: Array<{
    group_key: string;      // product/market/category name
    revenue_a: number;
    revenue_b: number;
    delta_revenue: number;
    delta_pct: number;
  }>;
}
```

---

### compare_markets

Side-by-side comparison of two markets over the same date range.

**Input schema:**
```typescript
{
  market_id_a: string;
  market_id_b: string;
  date_from: string;
  date_to: string;
}
```

**Returns:**
```typescript
{
  market_a: { name: string; type: string; total_revenue: number; event_count: number; avg_per_event: number; avg_customers: number | null };
  market_b: { name: string; type: string; total_revenue: number; event_count: number; avg_per_event: number; avg_customers: number | null };
  shared_products: Array<{
    product_name: string;
    revenue_a: number;
    revenue_b: number;
    avg_price_a: number;
    avg_price_b: number;
  }>;
  weekly_revenue: Array<{
    week: string;
    revenue_a: number;
    revenue_b: number;
  }>;
}
```

---

### get_price_history

Retrieve historical unit prices for a single product over time, optionally split by market.

**Input schema:**
```typescript
{
  product_id: string;
  date_from?: string;
  date_to?: string;
  split_by_market?: boolean;   // default false
}
```

**Returns:**
```typescript
{
  product_name: string;
  unit: string;
  avg_price: number;
  min_price: number;
  max_price: number;
  data_points: Array<{
    date: string;
    unit_price: number;
    market_name: string | null;
    quantity_sold: number;
  }>;
  by_market?: Array<{
    market_name: string;
    avg_price: number;
    data_points: number;
  }>;
}
```

---

### get_season_summary

Get a summary table of all seasons on record.

**Input schema:** `{}`

**Returns:**
```typescript
{
  seasons: Array<{
    season_name: string;     // "Spring 2025"
    date_from: string;
    date_to: string;
    total_revenue: number;
    event_count: number;
    avg_per_event: number;
    top_product: string;
    top_market: string;
  }>;
}
```

---

### get_revenue_by_category

Revenue breakdown by product category for a date range.

**Input schema:**
```typescript
{
  date_from: string;
  date_to: string;
  market_id?: string;
}
```

**Returns:**
```typescript
{
  total_revenue: number;
  categories: Array<{
    category: string;
    revenue: number;
    pct_of_total: number;
    product_count: number;
    top_product: string;
  }>;
}
```

---

### suggest_pricing

Analyze price history and suggest optimal pricing for each product based on historical data.

**Input schema:**
```typescript
{
  product_id?: string;         // if omitted, analyze all products
  benchmark_market_id?: string; // compare price against this market specifically
}
```

**Returns:**
```typescript
{
  suggestions: Array<{
    product_name: string;
    current_default_price: number;
    avg_actual_price: number;
    suggested_price: number;
    reasoning: string;       // e.g. "avg actual $4.31 exceeds default $4.00 — consider raising default"
    price_by_market: Array<{ market_name: string; avg_price: number }>;
  }>;
}
```

## Implementation Notes

- All queries use SQL aggregation on the `sale_line_items` table joined to `sale_events`, `products`, and `markets`
- Season boundaries are loaded from the `settings` table at query time; if not configured the season name is derived from the month: `{month_name} {year}`
- `compare_markets` weekly revenue aligns both markets to the same ISO week numbers, inserting `0` for weeks where a market had no events
- `suggest_pricing` returns suggestions only for products with at least 5 line item records; products with fewer records are returned with `reasoning: "insufficient data"`
- Percentage changes are computed as `(b - a) / a * 100` and returned as `number` rounded to 1 decimal place