retail-merchant-briefing

Overview

Replaces the VP Merchandising's Monday morning dashboard review with a single interactive briefing. Synthesizes sales, margin, inventory, and competitive data across all categories, surfaces the top 5 issues ranked by revenue impact, diagnoses root causes, and recommends specific actions. Works with any data warehouse, spreadsheet, or CSV export.

Workflow

<Definition - Data Source Cascade> Try in order, stop at first success:

1. Amazon Quick Dataset Q&A (if a QuickSight dataset is configured): ask questions directly in natural language against the full dataset with automatic SQL generation and security enforcement.
2. Data warehouse MCP connector detected (any SQL-compatible connector): query catalog for sales/margin tables.
3. Data source input provided as file path (.csv, .xlsx): parse with run_python.
4. Spreadsheet connector available (Google Sheets, OneDrive): search for "sales" or "performance" in shared files.
5. Nothing available: ask user to upload a file or describe their data setup. </Definition - Data Source Cascade>

<Definition - Category Health Index> A composite score (0-100) per category calculated from:

• Sales vs plan variance (weight: 30%)
• Margin vs plan variance (weight: 25%)
• Sell-through rate vs target (weight: 20%)
• Weeks of cover vs target (weight: 15%)
• Comp store performance (weight: 10%) Lower score = more attention needed. Categories below 60 are flagged as issues. </Definition - Category Health Index>

<Definition - Issue> A category-metric combination that exceeds the threshold_pct variance from its comparison baseline. Each issue must include: category name, metric, actual value, plan/baseline value, variance %, estimated revenue impact, and severity (critical/warning/watch). </Definition - Issue>

<Definition - Retail Week> The customer's fiscal week definition. Common patterns: Mon-Sun, Sun-Sat, or custom (e.g., 4-5-4 calendar). Captured during first-run setup and stored in config. </Definition - Retail Week>

<Workflow - Setup description="First-run configuration and data source detection. Runs when no saved config exists." tools=[run_python, file_read] triggers=["first time", "configure", "setup merchant briefing"]

[Decide] Check if {{config_directory}}/merchant-briefing-config.json exists:

• EXISTS: Load saved configuration (data source path, retail week definition, category hierarchy, metric preferences). Proceed to Generate Briefing workflow.
• DOES NOT EXIST: Continue with setup below. Validate: File existence determined. If fails: Assume first run.

[Ask user] "This looks like your first briefing. I need to understand your data setup. Please answer:

1. Where does your sales data live? (file path, spreadsheet link, or data warehouse connection)
2. What does your retail week look like? (Mon-Sun, Sun-Sat, or other)
3. Which categories do you want tracked? (or 'all')
4. Do you have plan/budget data available? (yes/no, and where)" Validate: User responds with at least a data source. If fails: Offer a sample template so the user can see the expected format.

[Decide] Detect data source type from user response per <Definition - Data Source Cascade>:

• SQL-compatible MCP connector detected: probe with a lightweight test query (SELECT 1 or equivalent).
• File path provided: verify file exists with file_read.
• Spreadsheet connector available: search for matching files.
• Nothing found: offer sample template. Validate: At least one data source is accessible. If fails: [Ask user] "I could not find sales data in that location. Could you upload a CSV or Excel file directly?"

[Agent] Map the user's column names to the standard schema. Expected columns: date, category, revenue, units, margin_pct, plan_revenue, plan_units, inventory_on_hand, weeks_of_cover. If column names differ, infer mapping from headers and confirm with user. Validate: At least date, category, and revenue are mapped. If fails: [Ask user] to clarify which columns contain category names, dates, and sales figures.

[Agent] Save configuration to {{config_directory}}/merchant-briefing-config.json with all mappings, data source path, retail week definition, and category list. Validate: Config file written successfully. If fails: Retry write. If still fails, present config as text for user to save manually.

[Agent] Generate a sample briefing using the first available week of data as a proof-of-concept. Present to user. Validate: Sample briefing rendered. If fails: Present raw data summary instead of full dashboard.

</Workflow - Setup>

<Workflow - Generate Briefing description="Main workflow: pull data, analyze, build dashboard." tools=[run_python, file_write, file_read, open_in_session_tab, web_search] triggers=["give me my Monday briefing", "weekly performance summary", "what needs attention", "category performance", "what happened last week"]

[Agent] Read {{config_directory}}/merchant-briefing-config.json to retrieve data source, column mappings, retail week, and category list. Validate: Config loaded with at least data source and column mappings. If fails: Route to Setup workflow.

[Decide] Resolve time period for analysis:

• time_period input provided: use it.
• Not provided: default to most recent complete retail week. Validate: Start and end dates determined. If fails: [Ask user] "Which week should I analyze?"

[Agent] Pull data for the analysis period AND the comparison period using run_python:

• If SQL connector: execute query with date filters.
• If file: load and filter by date column. Calculate: revenue, units, margin, sell-through, weeks of cover per category. Validate: Data returned with at least 1 category and 1 metric. If fails: Check date format, retry with broader filter. If still empty, report "No data found for this period."

[Agent] Score every category on a Category Health Index per <Definition - Category Health Index>. Rank by score (lowest = most attention needed). Select top 5 issues exceeding threshold_pct. Validate: At least 1 issue identified. If no issues exceed threshold, report "All categories performing within tolerance." If fails: Lower threshold by 2% and retry. If still none, present summary without issue focus.

[Agent] For each of the top 5 issues, determine likely root cause using run_python:

• Sales down + inventory up = demand shortfall or pricing issue.
• Sales down + inventory down = supply/availability issue.
• Margin down + sales flat = cost pressure or excess markdowns.
• Comp negative + category positive = store-level issue. Validate: Each issue has a root cause hypothesis. If fails: Label as "Requires investigation" rather than guessing.

[Decide] Is web_search available and useful for these categories?

• Yes: Search for recent market news, competitor pricing moves, or seasonal factors relevant to flagged categories.
• No: Skip competitive intel section. Validate: At least 1 relevant market signal found, or skip confirmed. If fails: Skip gracefully. Do not invent competitive data.

[Agent] For each issue, generate one specific recommended action:

• Include: what to do, expected impact, effort level (quick win / project / strategic).
• Actions must be specific ("Review markdown cadence for Footwear, consider 10% reduction") not generic ("Look into it"). Validate: Each action is specific and actionable. If fails: Rewrite vague actions with concrete next steps.

[Agent] Load highcharts and html_design skills. Build a self-contained HTML dashboard with:

• Header: retail week, date range, overall health score.
• Section 1: Category health heatmap (color-coded grid).
• Section 2: Top 5 issues table (category, metric, variance, impact, root cause, action).
• Section 3: Revenue trend chart (this period vs comparison).
• Section 4: Competitive signals (if available).
• Footer: data source, generated timestamp. Validate: HTML renders without errors. If fails: Fall back to markdown summary.

[Agent] Write the HTML to {output_directory}/merchant-briefing-{period}.html using file_write. Validate: File written successfully. If fails: Retry. If still fails, output markdown to chat.

[Agent] Open the briefing in the session tab using open_in_session_tab. Validate: File opened. If fails: Provide file path for manual opening.

[Agent] Present a plain-text summary to the user using <Template - Briefing Summary>. Validate: Summary presented. If fails: N/A.

</Workflow - Generate Briefing>

<Template - Briefing Summary>

Your briefing for {{period}} is ready. Top issues:
{{#each top_issues}}
- {{this.summary}}
{{/each}}
Full dashboard is open in the tab.

</Template - Briefing Summary>