From Manual Spreadsheets to Daily AI-Driven Sales Intelligence for a Specialty Retailer

The Challenge

Stilorancho, a growing specialty retailer, was drowning in raw data. Their Vend POS system exported daily sales as multi-file CSV bundles — separate reports for revenue totals, product unit counts, and category performance — with no unified view. Each morning, the operations team faced a manual, error-prone process: download files, cross-reference columns, calculate daily averages, compare against prior-month benchmarks, and format a summary for the ownership team.

The complexity was compounding. Vend's export format changed across billing periods: some exports placed files in nested dated subdirectories, others were flat. Revenue totals required using Vend's pre-calculated footer rows — not summing individual product rows — to avoid double-counting bundled SKUs. A naive LLM or spreadsheet formula would get this wrong consistently. The team needed a system that understood where to get authoritative numbers, not just how to add them up.

Beyond data accuracy, the business lacked a persistent, accessible intelligence layer. Insights lived in email threads and ephemeral spreadsheets. There was no trend line, no searchable archive, and no shared portal where ownership and staff could align on performance against targets.

The WinGuardian Solution

WinGuardian architected a fully automated, two-agent AI pipeline orchestrated by a single Windows batch script. The system is deliberately minimal and infrastructure-light — no cloud containers, no managed queues — because the client runs on shared hosting with FTP-only deployment. Every design decision prioritized reliability within real constraints.

Agent 1 — Data Normalization (Claude Haiku via Anthropic API): The first agent receives the raw, merged CSV — assembled by a PowerShell pre-processor that detects Vend's nested directory structure, unwraps it, and builds a canonical merged file with a Revenue footer row appended. Claude Haiku's role is strictly normalization: parse the multi-column Vend format, extract product names, unit counts, and — critically — pull benchmark revenue totals only from the footer row. The prompt explicitly forbids summing product rows, eliminating the double-count risk. Output is a clean, typed JSON artifact consumed by the next stage.

Pre-Computation Layer (Pure PowerShell Arithmetic): Before the second agent is invoked, a deterministic PowerShell script performs all benchmark mathematics: daily average vs. prior-month average, monthly run-rate vs. prior-month total, growth percentage, and directional arrow encoding. This separation of concerns was a hard-won architectural decision — keeping arithmetic out of the LLM prevents hallucinated calculations and makes the logic auditable by any developer without AI context.

Agent 2 — Report Rendering (Gemini CLI): The second agent consumes the pre-computed JSON and formats it into a fully-rendered HTML report following a strict template: revenue summary card, benchmark progress bars, top-5 product table, and narrative insights. The output is saved as a static HTML file and appended to a manifest index — the report archive the portal reads at runtime.

Portal Frontend (React 18 + TypeScript + Tailwind on Netlify): A React SPA serves as the intelligence portal. It reads the manifest to render a card grid of all historical reports, with growth indicators, revenue deltas, and one-click drill-down into the full rendered report. The dark-themed UI (#0d1117 background, #1e3a8a accent borders) reflects the professional, data-dense aesthetic the ownership team requested. A PHP backend on shared hosting handles JWT-authenticated API calls for report metadata, with email notification via PHP's native mail() function triggering on each successful deployment.

Ethical AI Design: WinGuardian's pipeline treats the LLMs as transformation specialists, not oracles. No financial totals are delegated to the model's reasoning — they are pre-computed and passed as verified inputs. This resilient-by-design architecture means a model update or API change cannot introduce a silent calculation error into business-critical revenue data.

The Results

• Zero manual effort: The entire pipeline — from raw CSV drop to deployed portal and email notification — completes in under 90 seconds. The operations team drops the Vend export into a watched folder and walks away.
• Approximately 2 hours of manual reporting labor reclaimed daily, redirected toward customer experience and inventory decisions.
• 100% pipeline reliability across all tested export formats, including edge cases: baseline reports (no prior period for comparison), Vend's nested subdirectory exports, and multi-currency formatting anomalies.
• Persistent, searchable intelligence archive: Every report since Day 1 is accessible in the portal, enabling ownership to identify weekly trends, seasonal patterns, and product velocity shifts that were previously invisible in ephemeral spreadsheets.
• Accurate benchmarking from Day 1: The prior-month daily average benchmark was correctly implemented — eliminating a circular benchmark bug (seven-day average derived from itself) that would have made every report show a misleadingly constant ~14% achievement rate.

The Strategic Difference

Any developer can wire up an API call. What WinGuardian delivered was a system that stays correct under adversarial real-world conditions — changing export formats, encoding edge cases, shared-hosting constraints, and the subtle traps that emerge when LLMs are asked to do math they were not designed to do reliably.

The engagement began as a request for a "report automation script." It evolved into a full architectural conversation: Where does truth live in this data? What happens when the export format changes next quarter? How do you prevent a model version upgrade from silently corrupting revenue figures? WinGuardian raised these questions before they became incidents.

The result is not just a working pipeline — it is a maintainable, extensible platform. The manifest-driven archive, the clean separation between normalization, computation, and rendering, and the documented PowerShell pre-processor mean a future developer (or the ownership team themselves) can extend the system — add a new benchmark, support a new export format, or integrate a new data source — without reverse-engineering a black-box prompt.

That is the WinGuardian difference: we build systems that outlast the sprint. Strategic alliance means leaving clients with infrastructure they own, understand, and can grow — not a dependency on our continued involvement to keep the lights on.