as5d8w/ai-business-insights：销售数据校验与报表导出工具

Personal portfolio project for reviewing sales uploads, checking data quality, charting deterministic metrics, and exporting business reports. All sample data is fictional. This is not a commercial client deployment, and there is no public hosted demo unless you run it locally.

Portfolio docs: Case study · Demo script · Screenshot checklist · GitHub release checklist

Architecture overview

Upload → Column mapping → Validation → Standardized table
              ↓
    Deterministic analytics (KPIs, Plotly, Excel/PDF)
              ↓
    Optional DeepSeek insights (aggregated metrics only, on button click)

Business logic lives in src/; app.py orchestrates the Streamlit UI. Numerical metrics are computed in Python; the AI layer only interprets summarized metrics when you request it.

Problem

Small sales files often arrive with inconsistent column names, missing values, and duplicate order IDs. Before anyone trusts a dashboard or an AI summary, the data needs a clear import path, explicit mapping, validation, and transparent metrics. This app shows that workflow end to end without hiding excluded rows or rewriting the original upload.

Key features

• CSV and XLSX upload with size limits and clear import errors
• Suggested and manual column mapping to a standard sales schema
• Structured validation with blocking errors, warnings, and notes
• Deterministic KPIs and Plotly charts from the standardized table
• Downloadable Excel and PDF reports built from the same analytics results
• Optional DeepSeek AI insights from aggregated metrics only (no raw upload sent)
• Automated tests with mocked API and export checks

Technology stack

Python 3.11+ is required. Dependencies were tested with Python 3.12.10.

Setup

Windows PowerShell:

py -3.12 -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

macOS or Linux:

python3 -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.txt

Optional AI configuration:

copy .env.example .env

Edit .env and set DEEPSEEK_API_KEY. Never commit .env. See .env.example for optional DEEPSEEK_MODEL and DEEPSEEK_TIMEOUT_SECONDS.

DeepSeek API key

1. Create an account and API key at DeepSeek Platform.
2. Set DEEPSEEK_API_KEY in .env or in your shell environment.
3. Optional: DEEPSEEK_MODEL (default deepseek-flash) and DEEPSEEK_TIMEOUT_SECONDS (default 30).

API usage may incur cost on your DeepSeek account. The repository does not contain API keys.

Run the app

streamlit run app.py

Example workflow

1. Upload sample_data/sales_sample.csv.
2. Confirm or adjust the suggested column mapping.
3. Click Run validation and review errors, warnings, and the standardized preview.
4. Read KPI cards and charts built from usable rows only.
5. Download business_insights_report.xlsx and business_insights_report.pdf (no API key required).
6. Optionally click Generate AI Insights when DEEPSEEK_API_KEY is configured.
7. Download the PDF again if you want the current session’s AI section included.

The sidebar default upload limit is 10 MB.

Standard fields

If sales_amount is missing and both quantity and unit_price are numeric and zero or positive, the standardized table fills it with quantity * unit_price. That calculation is not written back to the uploaded data.

Sales metrics (deterministic)

Analytics reads the standardized table after validation. A row is included in totals only when order_date, quantity, unit_price, and sales_amount are all usable (finite, non-negative numbers; missing values exclude the row and are not treated as zero).

Average order value is not sales divided by row count. Repeated order IDs remain as separate line items. Rows without an order ID stay in total sales but are excluded from order-based averages.

Trend charts use daily totals when usable dates span 31 days or fewer; otherwise they use calendar months. Empty periods are omitted, not drawn as zero.

Downloadable reports

Reports reuse the existing SalesAnalyticsResult and validation issues. They do not recalculate metrics, change the uploaded dataframe, or call DeepSeek automatically.

Excel (business_insights_report.xlsx) sheets:

• Executive Summary — KPIs, row inclusion counts, analytics notes
• Product Performance — product name, sales amount, quantity, rank
• Sales Trend — period, sales amount, quantity, plus trend note
• Data Quality — grouped validation categories and analytics exclusions

PDF (business_insights_report.pdf) includes the same deterministic content in a readable layout. AI sections appear only when valid insights already exist in the current Streamlit session. A note states that AI recommendations require human review.

If analytics are unavailable (status != ok), exports are blocked with an explanation instead of misleading zero-filled reports.

AI insights (optional)

AI output is interpretation, not source-of-truth numbers. The app sends a compact JSON summary (totals, ranked products, trend periods, inclusion counts). It does not send the full file or individual order IDs.

Click Generate AI Insights to request English JSON with executive_summary, key_findings, recommendations, and limitations. Malformed responses are rejected. AI text is not guaranteed to be accurate.

KPIs, charts, and Excel/PDF exports work without an API key.

Data privacy and secrets

• Do not commit .env, API keys, or private uploads to GitHub.
• .gitignore excludes .env, .streamlit/secrets.toml, virtual environments, pytest cache, and local export filenames (business_insights_report.xlsx, business_insights_report.pdf).
• Use uploads/ or private/ for local test files; those folders are ignored if you create them.
• Export files contain aggregated metrics and product names from rankings, not the full raw upload.
• DeepSeek requests use environment variables only; keys are not shown in the UI or error messages.

Tests

python -m pip install -r requirements.txt
python -m pytest -q

Latest run on this machine: 94 passed (includes mocked DeepSeek and in-memory Excel/PDF export tests; no live API calls during tests).

Supported import formats

• UTF-8 or UTF-8 BOM comma-separated .csv
• .xlsx first worksheet only; formula text is not executed
• Leading blank or # comment lines in CSV are skipped

Legacy .xls, other encodings, and non-comma CSV delimiters are rejected with clear errors.

Validation summary

Blocking errors include unmapped required fields, missing values, invalid dates, non-numeric amounts, and negative quantity or price. Warnings include duplicate order IDs (rows kept) and sales amount mismatches. Informational notes include calculated sales_amount rows.

Project layout

app.py                     Streamlit interface
src/data_loader.py         CSV and XLSX import
src/column_mapper.py       Mapping suggestions and checks
src/data_validator.py      Validation and standardized table
src/sales_analytics.py     Metrics and Plotly charts
src/ai_insights.py         Optional DeepSeek integration
src/report_export.py       Excel and PDF report builders
docs/                      Case study, demo script, screenshot checklist, GitHub release checklist
tests/                     Automated tests
sample_data/               Fictional sales sample
.env.example               Placeholder environment variables

Screenshots

Recommended shots and capture steps are in docs/SCREENSHOT_CHECKLIST.md. Images are not bundled in this repository until you add them manually (for example under docs/screenshots/). There is no public demo URL.

Known limitations

• No user accounts, databases, payments, or production deployment in this repo
• AI depends on DeepSeek availability, quota, and aggregated input quality
• AI responses are session-scoped; they are not stored server-side
• Reports do not embed Plotly chart images
• European decimal commas (for example 1.250,50) are not parsed
• Non-English column suggestions are not automatic
• Duplicate order IDs are reported but not auto-resolved
• Very large files are limited by the sidebar size cap

Sample data

sample_data/sales_sample.csv contains 24 fictional orders. The header comment states the records are invented.