Amazon ASIN Profit Report Generator

Generate a product-dimension profit margin report (50-column Excel, A-AX) from Amazon transaction, cost, and advertising data.

Required Input Files

Ask the user to provide all three:

1. Platform transaction CSV — Monthly Unified Transaction report (Amazon Settlement)
2. Product cost Excel — Cost table with columns: 名称, SKU, ASIN, 采购成本(USD), 头程运费(USD), 总成本（USD）
3. Advertising data Excel — Promoted products report (商品推广_推广的商品_报告) with 广告SKU column for spend alignment
4. Template Excel — sale_profit_asin_template.xlsx (50-column template A-AX, in user workspace)

Output

A 50-column Excel file with per-SKU profit rows + totals row. See references/template_mapping.md for full column definitions.

Workflow

Step 1: Verify Data Files

Confirm the user has all 3 data files and the template. Check column names match expected format. If any are missing, ask the user to provide them.

Step 2: Run the Script

Use the bundled script scripts/generate_report.py with the actual file paths:

python scripts/generate_report.py \
    --csv <transaction_csv> \
    --cost <cost_xlsx> \
    --ad <ad_xlsx> \
    --template <template_xlsx> \
    --output <output_xlsx>

The script:

• Reads CSV (skips 9 metadata rows, filters Transfer/Cost of Advertising)
• Extracts real SKU from amzn.gr.XXX- prefixed values
• Aggregates orders, refunds, adjustments by SKU
• Reads cost table, builds SKU→cost mapping
• Reads ad data (promoted products report), groups by 广告SKU, aggregates spend/sales/orders
• Allocates shared costs (storage, service fees, liquidation) by net sales ratio
• Computes all 50 columns per SKU
• Writes output with proper formatting (currency, percentage, integer, rate)

Step 3: Present Results

After the script runs, show the user a summary table with key metrics:

| SKU | 净销售额 | 毛利润(I) | 毛利率(J) | 平均毛利润(K) | 广告占比(L) | |-----|---------|----------|----------|-------------|-----------| | ... | | | | | | | 合计 | | | | | |

Column Mapping Reference

Load references/template_mapping.md when detailed column definitions or formula explanations are needed.

Customizations

If the user's data files use different column names, adapt the script's column references accordingly before running.