Skip to main content
Episode 17 of 24Back office40 min

Build sales reports you can trust, with CSV export

Add admin reports for sales by day, week and month, top products, average order value, low stock and customer growth, with date ranges and a safe CSV download.

You will have: A /admin/reports area with charts and tables that match your real orders, a date-range picker, and CSV export that never leaks personal data by default.

Before you start

  • Episode 16 is done, so orders have clear statuses and refunds are recorded
  • Several test orders exist across different days. If not, ask the agent to create a seed script for fake test orders in the local database only
  • Decide your store's time zone (for example the city where you pack orders), so a day means the same thing in every report
  • Write down the five questions you ask about the business every week; the agent will check the reports answer them

Make it yours

Change the example (fashion store) to your own. Your answers are saved in this browser and used in every episode.

Other series keywords (5)

Your prompt

Paste into: Your AI agent (Claude Code or Codex) opened in your project folder. Start a fresh session so it reads your project notes first.
You are a careful senior full-stack engineer working inside my project folder. The store is "Thread & Needle", a fashion e-commerce store selling women's and men's clothing, shoes and accessories in United States, charging in USD. Build a reports area in the admin panel so I can understand sales without exporting everything into a spreadsheet. I am not a programmer, so explain any technical word in a few words.

Plan first: look at the orders, order items, refunds, stock and customers tables, then show me your plan (which report answers which question, and the exact rule for what counts as a sale) and wait for my OK before changing anything.

Rules for the numbers (write them at the top of the reports page in plain words):
- A sale counts only when the order is paid. Cancelled and unpaid orders are excluded.
- Refunds are subtracted on the day they happen, so net sales = paid amounts minus refunds.
- All money is stored and calculated in the smallest unit of USD (for example cents) and shown with proper formatting. No floating point money maths.
- Days, weeks and months use the store time zone that I will tell you, not the server's.

Tasks:
1. A date-range control with presets (today, last 7 days, last 30 days, this month, last month, custom). The range is stored in the page address so a report can be bookmarked. Validate it with Zod and cap the maximum range.
2. Sales report: orders, gross sales, refunds, net sales, shipping and tax collected, with a daily, weekly or monthly toggle, shown as a simple chart and a table. Use lightweight charts made with plain SVG or CSS; do not add a heavy chart library or any third-party tracking script.
3. Top products: best sellers by units and by revenue for the range, with the share of total.
4. Average order value and orders per customer, compared with the previous period of the same length.
5. Low stock report: products at or below their threshold, with days of stock left based on recent sales, where there is enough data.
6. Customer growth: new accounts per period and the share of orders from returning customers. Show counts only, not names.
7. CSV export on each report. Export aggregated rows only by default. Protect against spreadsheet formula injection by prefixing any cell that starts with =, +, - or @ with a single quote. Use UTF-8 and put the date range in the file name.
8. Do the grouping in SQL with indexes where needed, so reports stay fast with thousands of orders. Add a short cache of a few minutes only if it is clearly needed.

Security and privacy rules:
- Reports are admin only, checked on the server for the page and for every export route.
- Reports must not show customer emails, names or addresses. If I later want a customer-level export, it must be a separate, clearly labelled button that asks for confirmation.
- Never print, log or commit secrets or personal data.

Verify: add unit tests for the sale rule, refund subtraction and rounding, and for the CSV escaping. Compare each report total with a direct count in the local shop_local database and show me both numbers side by side. Run type-check, lint, tests and a production build.

Commit on a feature branch with a clear message.

Report back in this format: 1) What was done. 2) What I should check myself. 3) Anything that needs my decision.

Review the prompt, then copy it. Never paste real passwords or API keys into a chat.

How to check it worked

  • The reports page states in plain words what counts as a sale and how refunds are treated
  • Changing the date range changes every number and the address bar, and the page still works when you bookmark it
  • Net sales for a day equals paid orders minus refunds when you add up that day's orders by hand
  • The downloaded CSV opens in a spreadsheet with correct columns and contains no emails or names
  • A product name starting with an equals sign shows as text in the CSV, not as a formula
  • The low stock report agrees with the Low stock panel from Episode 16

Sample result

Example: fashion store

Example result for the fashion store: the sales report for the last 30 days. Figures are test data.

If something goes wrong

Report totals do not match your payment gateway dashboard.
The gateway may count by a different time zone, include fees, or list test and live data separately. Ask the agent: "Show me the exact rule and time zone each total uses, and list the orders in one day so I can compare."
Charts are empty or show one big bar.
You may have too few orders on different days. Ask for a seed script that creates fake test orders spread over 60 days, in the local database only, never production.
The CSV shows strange characters or everything in one column.
Ask for UTF-8 with a byte order mark if your spreadsheet needs it, and tell the agent which spreadsheet app and language settings you use.
Reports get slow as orders grow.
Ask the agent to show the slowest query with EXPLAIN, add an index on the paid date column, and keep any cache short.

Free. These reports read your own database, so they cost nothing extra. Heavy queries on a free database count toward its compute allowance; verify limits on the provider's pricing page.

More in Back office

This is part of the series Build a secure, no-cost e-commerce website with AI agents. See all 24 episodes, the tools used and answers to common questions on the series page.

We use optional cookies for analytics and referral tracking. Learn more about our privacy policy