Retail Sales Performance — Skill

Triggers

• "create sales report" → produce Excel file (see Excel section below)
• "show sales dashboard" → render interactive dashboard widget in chat (see Dashboard section below)

Overview

Produces a single .xlsx file with 4 sheets, each populated from the retail PostgreSQL database via the retail-db MCP tool.

Always also read /mnt/skills/public/xlsx/SKILL.md before writing code.

---

Step-by-step execution

Step 1 — Run 4 SQL queries via MCP

Use retail-db:run_select for each query below. SQL comments must use /* */ syntax — -- is rejected by the MCP tool.

---

Sheet 1 · Monthly Sales Summary

SELECT
    (txn_date_key / 100) % 100                                  AS month,
    COUNT(*)                                                     AS total_transactions,
    SUM(net_amount)                                              AS total_revenue,
    SUM(qty_sold)                                                AS total_units_sold,
    COUNT(*) FILTER (WHERE customer_type = 'NEW')                AS new_customers,
    COUNT(*) FILTER (WHERE customer_type = 'RETURNING')          AS returning_customers,
    COUNT(*) FILTER (WHERE txn_status = 'RETURN')                AS returns,
    SUM(net_amount) FILTER (WHERE txn_status != 'RETURN')        AS net_revenue,
    ROUND(SUM(net_amount) / NULLIF(COUNT(*), 0), 2)              AS avg_basket_size,
    400000                                                       AS target_revenue,
    ROUND(
        SUM(net_amount) FILTER (WHERE txn_status != 'RETURN')::NUMERIC
        / 400000, 6
    )                                                            AS achievement_pct
FROM fact_transaction
WHERE txn_date_key >= 20260101
GROUP BY ROLLUP(1)
ORDER BY 1 NULLS LAST

Notes:

• Row where month IS NULL = YTD total (ROLLUP grand total)
• Display month as Thai month name: {1:"มกราคม", 2:"กุมภาพันธ์", 3:"มีนาคม", 4:"เมษายน", 5:"พฤษภาคม", 6:"มิถุนายน"} or "YTD Total" for null
• achievement_pct = net_revenue / (target × months elapsed) for YTD row

---

Sheet 2 · Slow-Moving Product Tracker

SELECT * FROM (
    WITH sales_flags AS (
        SELECT
            product_key,
            COUNT(*)                AS total_transactions_30d,
            SUM(qty_sold)           AS units_sold_30d,
            MAX(txn_date_key)       AS last_sale_date_key
        FROM fact_transaction
        WHERE txn_date_key >= (
            SELECT MAX(txn_date_key) - 100
            FROM fact_transaction
        )
          AND txn_status != 'RETURN'
        GROUP BY product_key
    )
    SELECT
        dp.product_code,
        dp.product_name,
        dc.category_name,
        dp.stock_qty,
        COALESCE(sf.units_sold_30d, 0)          AS units_sold_30d,
        sf.last_sale_date_key,
        CASE
            WHEN sf.last_sale_date_key IS NULL THEN NULL
            ELSE (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT, 'YYYYMMDD'))
        END                                      AS days_since_last_sale,
        dp.unit_cost,
        dp.unit_price,
        ROUND(dp.stock_qty * dp.unit_cost, 2)    AS stock_value,
        ROUND(COALESCE(sf.units_sold_30d, 0)::NUMERIC / 30, 3) AS velocity,
        CASE
            WHEN COALESCE(sf.units_sold_30d, 0) = 0 THEN 999
            ELSE ROUND(dp.stock_qty::NUMERIC / (sf.units_sold_30d::NUMERIC / 30), 1)
        END                                      AS days_of_stock,
        CASE
            WHEN sf.last_sale_date_key IS NULL THEN 'CRITICAL'
            WHEN (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT, 'YYYYMMDD')) > 60 THEN 'CRITICAL'
            WHEN (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT, 'YYYYMMDD')) > 30 THEN 'WARNING'
            WHEN (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT, 'YYYYMMDD')) > 14 THEN 'WATCH'
            ELSE 'ACTIVE'
        END                                      AS level,
        CASE
            WHEN sf.last_sale_date_key IS NULL THEN 'พิจารณาลดราคา/คืนสินค้า'
            WHEN (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT, 'YYYYMMDD')) > 60 THEN 'พิจารณาลดราคา/คืนสินค้า'
            WHEN (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT, 'YYYYMMDD')) > 30 THEN 'จัดโปรโมชั่นด่วน'
            WHEN (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT, 'YYYYMMDD')) > 14 THEN 'ติดตามยอดขาย'
            ELSE 'ปกติ'
        END                                      AS action
    FROM dim_product dp
    LEFT JOIN dim_category dc
        ON dp.category_key = dc.category_key
    LEFT JOIN sales_flags sf
        ON dp.product_key = sf.product_key
    WHERE dp.is_active = TRUE
) sub
ORDER BY units_sold_30d ASC, COALESCE(days_since_last_sale, 999) DESC

Level color coding (apply as cell fill):

Level	Fill hex
CRITICAL	#FCEBEB
WARNING	#FAEEDA
WATCH	#E6F1FB
ACTIVE	#EAF3DE

---

Sheet 3 · Staff Performance Leaderboard

WITH txn_totals AS (
    SELECT
        ds.staff_key,
        COUNT(*)                                            AS total_transactions,
        SUM(ft.net_amount)                                  AS total_revenue,
        SUM(ft.qty_sold)                                    AS units_sold,
        COUNT(*) FILTER (WHERE ft.txn_status = 'RETURN')   AS returns_handled,
        COUNT(*) FILTER (WHERE fc.customer_type = 'NEW')   AS new_customers_acquired,
        COUNT(*) FILTER (WHERE ft.has_upsell = TRUE)       AS upsell_count
    FROM fact_transaction ft
    LEFT JOIN dim_staff ds
        ON ft.staff_key = ds.staff_key
    LEFT JOIN fact_customer fc
        ON ft.customer_key = fc.customer_key
    WHERE ft.txn_date_key >= 20260101
      AND ft.txn_status != 'RETURN'
    GROUP BY ds.staff_key
)
SELECT
    ds.staff_code                                               AS staff_id,
    ds.full_name                                                AS staff_name,
    db.branch_name                                              AS branch,
    COALESCE(tt.total_transactions, 0)                          AS total_transactions,
    COALESCE(tt.total_revenue, 0)                               AS total_revenue,
    COALESCE(tt.units_sold, 0)                                  AS units_sold,
    CASE
        WHEN COALESCE(tt.total_transactions, 0) > 0
        THEN ROUND(tt.total_revenue / tt.total_transactions, 2)
        ELSE 0
    END                                                         AS avg_basket_size,
    COALESCE(tt.returns_handled, 0)                             AS returns_handled,
    COALESCE(tt.new_customers_acquired, 0)                      AS new_customers_acquired,
    CASE
        WHEN COALESCE(tt.total_transactions, 0) > 0
        THEN ROUND(tt.upsell_count::NUMERIC / tt.total_transactions, 6)
        ELSE 0
    END                                                         AS upsell_rate,
    150000                                                      AS target_revenue,
    ROUND(COALESCE(tt.total_revenue, 0)::NUMERIC / 150000, 6)  AS achievement_pct
FROM dim_staff ds
LEFT JOIN dim_branch db
    ON ds.branch_key = db.branch_key
LEFT JOIN txn_totals tt
    ON ds.staff_key = tt.staff_key
WHERE ds.is_active = TRUE
ORDER BY total_revenue DESC, upsell_rate DESC

Computed columns (add in Python after query):

short_summary — classify each row:

total_transactions == 0 or total_revenue == 0          → "Inactive"
total_transactions < 60                                → "ข้อมูลน้อย"
achievement_pct >= 1.0 and upsell_rate >= 0.20         → "Top performer"
total_transactions >= 250 and achievement_pct < 0.50   → "High volume, low revenue"
upsell_rate < 0.05                                     → "Low engagement"
total_revenue >= 100000 and new_customers_acquired <= 5 → "High revenue, low acquisition"
else                                                   → "Developing"

action — management suggestion:

"Top performer"                 → "ขยายโซน / ให้เป็น mentor"
"High volume, low revenue"      → "ทบทวนเทคนิคขาย / coaching"
"Inactive"                      → "ตรวจสอบด่วน"
"Low engagement"                → "กำหนด KPI upsell"
"High revenue, low acquisition" → "เพิ่มเป้าลูกค้าใหม่"
"ข้อมูลน้อย"                    → "ติดตามเพิ่มเติม"
"Developing"                    → "ติดตามผล / support"

---

Sheet 4 · Category Performance Analysis

SELECT
    *,
    CASE
        WHEN revenue_contribution >= 0.30 AND avg_margin_pct >= 0.25 THEN 'Star category'
        WHEN revenue_contribution >= 0.20                             THEN 'High volume'
        WHEN avg_margin_pct >= 0.35                                   THEN 'High margin — low volume'
        WHEN return_rate >= 0.08                                      THEN 'High return risk'
        WHEN revenue_contribution < 0.08                              THEN 'Low contribution'
        ELSE                                                               'Stable'
    END                                                               AS summary,
    CASE
        WHEN revenue_contribution >= 0.30 AND avg_margin_pct >= 0.25 THEN 'ขยาย SKU / เพิ่ม shelf space'
        WHEN revenue_contribution >= 0.20                             THEN 'ทบทวน margin — volume ดีแต่กำไรต่ำ'
        WHEN avg_margin_pct >= 0.35                                   THEN 'เพิ่ม visibility / จัดโปรโมชั่น'
        WHEN return_rate >= 0.08                                      THEN 'ตรวจสอบคุณภาพสินค้า / นโยบาย return'
        WHEN revenue_contribution < 0.08                              THEN 'พิจารณาลด SKU / เพิ่มสินค้าใหม่'
        ELSE                                                               'รักษา performance / ติดตามต่อ'
    END                                                               AS suggest
FROM (
    SELECT
        dc.category_name                                                          AS category,
        COUNT(DISTINCT dp.product_key)                                            AS total_skus,
        COUNT(ft.txn_key)                                                         AS total_transactions,
        ROUND(SUM(ft.net_amount), 2)                                              AS total_revenue,
        SUM(ft.qty_sold)                                                          AS total_units_sold,
        COUNT(ft.txn_key) FILTER (WHERE ft.txn_status = 'RETURN')                AS returns,
        ROUND(
            COUNT(ft.txn_key) FILTER (WHERE ft.txn_status = 'RETURN')::NUMERIC
            / NULLIF(COUNT(ft.txn_key), 0), 6)                                   AS return_rate,
        ROUND(AVG(dp.margin_pct), 4)                                              AS avg_margin_pct,
        ROUND(SUM(ft.net_amount) * AVG(dp.margin_pct), 2)                        AS gross_profit,
        ROUND(
            SUM(ft.net_amount)::NUMERIC
            / NULLIF(SUM(SUM(ft.net_amount)) OVER (), 0), 6)                     AS revenue_contribution
    FROM fact_transaction ft
    LEFT JOIN dim_product dp
        ON ft.product_key = dp.product_key
    LEFT JOIN dim_category dc
        ON dp.category_key = dc.category_key
    WHERE ft.txn_date_key >= 20260101
    GROUP BY dc.category_name
) base
ORDER BY total_revenue DESC

---

Step 2 — Build Excel file with openpyxl

Use openpyxl. Font: Arial 10. Do NOT use pandas to_excel (no formatting control).

General sheet formatting rules

• Row 1 = header row: bold, white text (FFFFFF), dark blue fill (1F3864), center-aligned, frozen
• Data rows: alternating white / light gray (F2F2F2)
• Auto-fit column widths (min 10, max 50)
• All percentage columns: format as 0.00%
• Currency columns (revenue, profit, cost, basket): format as #,##0.00
• Count columns ≥ 1000: format with thousands separator #,##0
• Thai text columns: UTF-8 — openpyxl handles natively

Sheet 1 — "Monthly Sales Summary"

Columns: Month | Total Transactions | Total Revenue | Units Sold | New Customers | Returning Customers | Returns | Net Revenue | Avg Basket Size | Target Revenue | Achievement %

• month null row → "YTD Total": bold, light yellow fill (FFFDE7)
• Achievement %: 0.00% format; color red if < 80%, amber if 80–99%, green if ≥ 100%

Sheet 2 — "Slow-Moving Products"

Columns: Product Code | Product Name | Category | Stock Qty | Units Sold (30d) | Last Sale Date | Days Since Last Sale | Unit Cost | Unit Price | Stock Value | Velocity (u/day) | Days of Stock | Level | Action

• last_sale_date_key: format YYYYMMDD int → YYYY-MM-DD string
• days_of_stock = 999 → display as "∞"
• level cell fill color per color table above
• velocity: 0.000 number format

Sheet 3 — "Staff Performance"

Columns: Staff ID | Staff Name | Branch | Total Transactions | Total Revenue | Units Sold | Avg Basket Size | Returns Handled | New Customers | Upsell Rate | Target Revenue | Achievement % | Short Summary | Action

• Upsell Rate, Achievement %: 0.00% format
• Total Revenue: bold
• Short Summary cell fill:

  Top performer              → #EAF3DE (green)
  High volume, low revenue   → #E6F1FB (blue)
  Inactive                   → #FCEBEB (red)
  Low engagement             → #FAEEDA (amber)
  High revenue, low acq.     → #EEEDFE (purple)
  Developing / ข้อมูลน้อย   → #F1EFE8 (gray)
  

Sheet 4 — "Category Performance"

Columns: Category | Total SKUs | Total Transactions | Total Revenue | Units Sold | Returns | Return Rate | Avg Margin % | Gross Profit | Revenue Contribution | Summary | Suggest

• Return Rate, Avg Margin %, Revenue Contribution: 0.00% format
• Gross Profit: #,##0.00 format
• Summary cell fill:

  Star category              → #EAF3DE
  High volume                → #E6F1FB
  High margin — low volume   → #EEEDFE
  High return risk           → #FCEBEB
  Low contribution           → #FAEEDA
  Stable                     → #F1EFE8
  

---

Step 3 — File naming & output

Save to /mnt/user-data/outputs/RetailSales_Report_YYYYMMDD.xlsx where YYYYMMDD = today's date (datetime.date.today().strftime('%Y%m%d')).

Then call present_files with the output path.

---

Dashboard (chat widget) — trigger: "show sales dashboard"

Do not create an Excel file. Render using visualize:show_widget only.

Step 1 — Run 4 SQL queries via MCP (same tool, same /* */ comment rule)

Query A — Monthly trend

SELECT
    (txn_date_key / 100) % 100                                              AS month,
    COUNT(*)                                                                 AS total_transactions,
    SUM(net_amount)                                                          AS total_revenue,
    SUM(net_amount) FILTER (WHERE txn_status != 'RETURN')                   AS net_revenue,
    COUNT(*) FILTER (WHERE txn_status = 'RETURN')                           AS returns,
    ROUND(
        SUM(net_amount) FILTER (WHERE txn_status != 'RETURN')::NUMERIC
        / 400000, 4)                                                         AS achievement_pct
FROM fact_transaction
WHERE txn_date_key >= 20260101
GROUP BY 1
ORDER BY 1

Query B — Slow-moving product health

WITH sales_flags AS (
    SELECT
        product_key,
        MAX(txn_date_key)   AS last_sale_date_key
    FROM fact_transaction
    WHERE txn_status != 'RETURN'
    GROUP BY product_key
)
SELECT
    COUNT(*)                                                                                                              AS total_active_skus,
    COUNT(*) FILTER (WHERE sf.last_sale_date_key IS NULL)                                                                AS no_sales,
    COUNT(*) FILTER (WHERE (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT,'YYYYMMDD')) > 60)                       AS critical,
    COUNT(*) FILTER (WHERE (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT,'YYYYMMDD')) BETWEEN 31 AND 60)          AS warning,
    COUNT(*) FILTER (WHERE (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT,'YYYYMMDD')) BETWEEN 15 AND 30)          AS watch,
    COUNT(*) FILTER (WHERE (CURRENT_DATE - TO_DATE(sf.last_sale_date_key::TEXT,'YYYYMMDD')) <= 14)                      AS active
FROM dim_product dp
LEFT JOIN sales_flags sf ON dp.product_key = sf.product_key
WHERE dp.is_active = TRUE

Query C — Staff leaderboard (top 6 by revenue)

SELECT
    ds.full_name                                                    AS staff_name,
    COALESCE(SUM(ft.net_amount), 0)                                 AS total_revenue,
    COUNT(ft.txn_key)                                               AS total_transactions,
    ROUND(
        COALESCE(SUM(ft.net_amount), 0)::NUMERIC / 150000, 4)      AS achievement_pct
FROM fact_transaction ft
LEFT JOIN dim_staff ds ON ft.staff_key = ds.staff_key
WHERE ft.txn_date_key >= 20260101
  AND ft.txn_status != 'RETURN'
GROUP BY ds.full_name
ORDER BY total_revenue DESC
LIMIT 6

Query D — Category performance

SELECT
    COALESCE(dc.category_name, '(ไม่ระบุ)')                        AS category,
    ROUND(SUM(ft.net_amount), 2)                                    AS total_revenue,
    COUNT(ft.txn_key)                                               AS total_transactions,
    ROUND(
        SUM(ft.net_amount)::NUMERIC
        / NULLIF(SUM(SUM(ft.net_amount)) OVER (), 0), 4)            AS revenue_contribution
FROM fact_transaction ft
LEFT JOIN dim_product dp ON ft.product_key = dp.product_key
LEFT JOIN dim_category dc ON dp.category_key = dc.category_key
WHERE ft.txn_date_key >= 20260101
GROUP BY dc.category_name
ORDER BY total_revenue DESC

---

Step 2 — Compute derived KPIs from query results (in JS inside the widget)

From Query A (aggregate all months):

• total_revenue = SUM net_revenue across all months
• total_transactions = SUM total_transactions across all months
• ytd_achievement = total_revenue / (400000 × months_count) × 100
• total_returns = SUM returns across all months
• avg_basket = total_revenue / total_transactions

From Query B:

• total_skus from result
• critical_count = no_sales + critical (both require urgent action)
• warning_count, watch_count, active_count

---

Step 3 — Render with visualize:show_widget

Layout (top to bottom, single column, full width)

┌─────────────────────────────────────────────────┐
│  Section label: "YTD 2026 — overview"           │
│  KPI strip (5 cards in one row):                │
│  Net Revenue | Transactions | Achievement % | Avg Basket | Returns │
├─────────────────────────────────────────────────┤
│  Section label: "Slow-moving product health"    │
│  4 colored cards (Critical | Warning | Watch | Active) │
├─────────────────────────────────────────────────┤
│  Section label: "Monthly revenue trend"         │
│  Full-width chart: grouped bar (transactions÷10, revenue÷10000) │
│  + line overlay (achievement %)  — height 220px │
├───────────────────────┬─────────────────────────┤
│ "Revenue by category" │ "Top staff — revenue"   │
│  Horizontal bar chart │  Horizontal bar chart   │
│  height 220px         │  height 220px           │
└───────────────────────┴─────────────────────────┘

KPI card rules

• 5 cards in grid-template-columns: repeat(5, 1fr), gap: 10px
• background: var(--color-background-secondary), border-radius: var(--border-radius-md), padding 0.85rem 1rem
• label: 12px, var(--color-text-secondary); value: 22px, weight 500; sub-text: 11px
• Color coding:
  • Net Revenue → var(--color-text-primary)
  • Transactions → var(--color-text-primary)
  • Achievement % → red #A32D2D if < 80%, amber #854F0B if 80–99%, green #3B6D11 if ≥ 100%
  • Avg Basket → var(--color-text-primary)
  • Returns → always amber #854F0B
• sub-text shows target: e.g. "target ≥100%" in red when missing

Slow-moving product health cards

• 4 cards: Critical (#FCEBEB bg, #A32D2D text) | Warning (#FAEEDA, #854F0B) | Watch (#E6F1FB, #185FA5) | Active (#EAF3DE, #3B6D11)
• Critical card shows no_sales + critical combined count (both require urgent action)
• Large number (26px), label (12px bold), sub-text (11px, muted) describing threshold

Monthly revenue trend chart (Chart.js)

• Type: bar with line overlay on second y-axis
• Bars: Transactions ÷ 10 (blue #B5D4F4) and Net revenue ÷ 10000 (green #639922) — grouped
• Line: Achievement % × 100 (red dashed #E24B4A, borderDash [4,3])
• Two y-axes: left for scaled counts, right for % (label callback: v + '%')
• x-axis labels: Thai month abbreviations ['ม.ค.','ก.พ.','มี.ค.','เม.ย.','พ.ค.','มิ.ย.',...]
• autoSkip: false on x-axis ticks
• Custom HTML legend above chart; no Chart.js default legend

Category revenue chart (Chart.js horizontal bar)

• Sort by total_revenue descending
• Each bar a distinct color from: ['#639922','#378ADD','#1D9E75','#BA7517','#7F77DD','#E24B4A']
• x-axis ticks: format as ฿{value/1000}K
• Wrapper div height: (number_of_bars × 40) + 80px

Staff revenue chart (Chart.js horizontal bar)

• Top 6 staff, sorted by total_revenue DESC
• x-axis ticks: format as ฿{value/1000}K
• Bar colors from green ramp: ['#639922','#3B6D11','#1D9E75','#378ADD','#185FA5','#0C447C']
• Wrapper div height: (6 × 40) + 80px

Chart.js shared rules

• Load from: https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.4.1/chart.umd.js
• responsive: true, maintainAspectRatio: false
• plugins: { legend: { display: false } } — always use custom HTML legend
• Grid lines: color: 'rgba(0,0,0,0.05)' on value axis only
• Canvas must have role="img" and aria-label

Section labels

• 11px, weight 500, var(--color-text-secondary), uppercase, letter-spacing: 0.06em
• margin: 1.5rem 0 0.6rem

Chart wrapper cards (monthly + the 2-col row)

• background: var(--color-background-primary), border: 0.5px solid var(--color-border-tertiary), border-radius: var(--border-radius-lg), padding: 1rem 1.25rem
• Chart title: 13px, weight 500

---

KPI reference (for context only — not printed in any output)

KPI	Target
Monthly Net Revenue	≥ ฿400,000
Achievement %	≥ 100%
Avg Basket Size	≥ ฿350
Upsell Rate	≥ 15%
Return Rate	≤ 5%

Key schema facts (fictional retail database)

• fact_transaction is the primary fact table (like fact_lead_header)
• txn_date_key is a YYYYMMDD integer; month = (txn_date_key / 100) % 100
• txn_status encodes: COMPLETED / RETURN (like lead_type WIN/LOSS)
• customer_type encodes: NEW / RETURNING (like lead source classification)
• has_upsell is a boolean flag on each transaction
• dp.margin_pct is a stored margin % on dim_product
• MCP tool hard cap: 500 rows per call
• Key joins:
  • fact_transaction.product_key = dim_product.product_key
  • fact_transaction.staff_key = dim_staff.staff_key
  • fact_transaction.customer_key = fact_customer.customer_key
  • dim_product.category_key = dim_category.category_key
  • dim_staff.branch_key = dim_branch.branch_key