#!/usr/bin/env python3 """ etsy-sales-reporter — Parse Etsy CSV order exports into structured reports. No API key needed. Works with Etsy Shop Manager CSV export. """ import csv import sys import json import argparse from datetime import datetime, date from pathlib import Path from collections import defaultdict # ─── Category detection from product title keywords ─────────────────────── CATEGORIES = { "USMC": ["usmc", "marine", "ega", "semper fi", "semper fidelis"], "Military": ["army", "navy", "air force", "military", "veteran", "soldier", "airman"], "Christian": ["cross", "scripture", "biblical", "reformed", "christian", "faith", "verse"], "Patriotic": ["american", "flag", "patriot", "usa", "liberty", "eagle"], "Nature": ["deer", "pine", "nature", "forest", "mountain", "floral"], "Digital": ["svg", "digital", "printable", "download", "png", "dxf"], } def detect_category(title: str) -> str: title_lower = title.lower() for cat, keywords in CATEGORIES.items(): if any(kw in title_lower for kw in keywords): return cat return "Other" def clean_currency(val: str) -> float: if not val: return 0.0 return float(val.replace("$", "").replace(",", "").strip() or 0) def parse_date(val: str) -> date | None: for fmt in ("%b %d, %Y", "%Y-%m-%d", "%m/%d/%Y", "%d/%m/%Y"): try: return datetime.strptime(val.strip(), fmt).date() except ValueError: pass return None def load_csv(path: str) -> list[dict]: rows = [] with open(path, newline="", encoding="utf-8-sig") as f: reader = csv.DictReader(f) for row in reader: rows.append(row) return rows def normalize_rows(rows: list[dict]) -> list[dict]: """Normalize column names across different Etsy export formats.""" normalized = [] for row in rows: # Map common Etsy column name variants keys = {k.strip().lower(): v for k, v in row.items()} order_date_raw = ( keys.get("sale date") or keys.get("order date") or keys.get("date paid") or keys.get("date") or "" ) normalized.append({ "order_id": keys.get("order id") or keys.get("order number") or "", "sale_date": parse_date(order_date_raw), "ship_date": parse_date(keys.get("date shipped") or keys.get("ship date") or ""), "item_name": keys.get("item name") or keys.get("listing title") or keys.get("title") or "", "quantity": int(keys.get("quantity") or keys.get("qty") or 1), "price": clean_currency(keys.get("order value") or keys.get("sale amount") or keys.get("price") or "0"), "status": (keys.get("order status") or keys.get("status") or "").lower(), "buyer": keys.get("buyer username") or keys.get("buyer") or "", "refunded": "refund" in (keys.get("order status") or "").lower() or "cancel" in (keys.get("order status") or "").lower(), }) return normalized def generate_report(rows: list[dict], args) -> str: lines = [] sep = "─" * 60 active = [r for r in rows if not r["refunded"]] refunded = [r for r in rows if r["refunded"]] total_revenue = sum(r["price"] for r in active) total_orders = len(active) avg_order = total_revenue / total_orders if total_orders else 0 lines += [ f"# Etsy Sales Report — {date.today().isoformat()}", "", f"**CSV:** {args.csv_path}", "", sep, "## 📊 Summary", sep, f"- **Total Orders:** {total_orders:,}", f"- **Total Revenue:** ${total_revenue:,.2f}", f"- **Average Order Value:** ${avg_order:.2f}", f"- **Refunds/Cancellations:** {len(refunded)} ({len(refunded)/len(rows)*100:.1f}%)", "", ] if not args.summary: # ── Top Products ─────────────────────────────────────────────── product_stats = defaultdict(lambda: {"units": 0, "revenue": 0.0}) for r in active: if args.category and detect_category(r["item_name"]) != args.category: continue product_stats[r["item_name"]]["units"] += r["quantity"] product_stats[r["item_name"]]["revenue"] += r["price"] top_n = args.top_products or 10 sorted_products = sorted( product_stats.items(), key=lambda x: x[1]["revenue"], reverse=True )[:top_n] lines += [sep, f"## 🏆 Top {top_n} Products by Revenue", sep] for i, (name, stats) in enumerate(sorted_products, 1): cat = detect_category(name) short_name = name[:55] + "…" if len(name) > 55 else name lines.append( f"{i:2}. [{cat}] {short_name}\n" f" Units: {stats['units']} | Revenue: ${stats['revenue']:.2f}" ) lines.append("") # ── Monthly Trends ───────────────────────────────────────────── if args.monthly or not args.summary: monthly = defaultdict(lambda: {"orders": 0, "revenue": 0.0}) for r in active: if r["sale_date"]: key = r["sale_date"].strftime("%Y-%m") monthly[key]["orders"] += 1 monthly[key]["revenue"] += r["price"] lines += [sep, "## 📅 Monthly Revenue Trends", sep] for month in sorted(monthly): m = monthly[month] bar = "█" * int(m["revenue"] / 50) lines.append(f"{month}: ${m['revenue']:>8,.2f} ({m['orders']:>4} orders) {bar}") lines.append("") # ── Category Breakdown ────────────────────────────────────────── cat_stats = defaultdict(lambda: {"orders": 0, "revenue": 0.0}) for r in active: cat = detect_category(r["item_name"]) cat_stats[cat]["orders"] += 1 cat_stats[cat]["revenue"] += r["price"] lines += [sep, "## 🏷️ Category Breakdown", sep] for cat, stats in sorted(cat_stats.items(), key=lambda x: -x[1]["revenue"]): pct = stats["revenue"] / total_revenue * 100 if total_revenue else 0 lines.append(f" {cat:<12} {stats['orders']:>4} orders ${stats['revenue']:>8,.2f} ({pct:.1f}%)") lines.append("") # ── Fulfillment ──────────────────────────────────────────────── shipped = sum(1 for r in active if r["ship_date"]) unshipped = total_orders - shipped lines += [ sep, "## 📦 Fulfillment", sep, f"- Shipped: {shipped} ({shipped/total_orders*100:.1f}%)" if total_orders else "- No orders", f"- Pending: {unshipped}", "", ] # ── Repeat Buyers ────────────────────────────────────────────── buyer_counts = defaultdict(int) for r in active: if r["buyer"]: buyer_counts[r["buyer"]] += 1 repeat = {b: c for b, c in buyer_counts.items() if c > 1} lines += [ sep, "## 👥 Repeat Buyers", sep, f"- Total unique buyers: {len(buyer_counts)}", f"- Repeat buyers: {len(repeat)} ({len(repeat)/len(buyer_counts)*100:.1f}%)" if buyer_counts else "- No data", "", ] return "\n".join(lines) def main(): parser = argparse.ArgumentParser(description="Etsy CSV Sales Reporter") parser.add_argument("csv_path", help="Path to Etsy order export CSV") parser.add_argument("--summary", action="store_true", help="Show summary only") parser.add_argument("--top-products", type=int, metavar="N", help="Show top N products (default 10)") parser.add_argument("--monthly", action="store_true", help="Show monthly trends") parser.add_argument("--category", help="Filter by category (USMC, Military, Christian, Patriotic, Nature, Digital)") parser.add_argument("--output", help="Save report to file (markdown)") args = parser.parse_args() csv_path = Path(args.csv_path).expanduser() if not csv_path.exists(): print(f"❌ File not found: {csv_path}", file=sys.stderr) sys.exit(1) rows = load_csv(str(csv_path)) if not rows: print("❌ CSV is empty or could not be parsed.", file=sys.stderr) sys.exit(1) rows = normalize_rows(rows) report = generate_report(rows, args) if args.output: out_path = Path(args.output).expanduser() out_path.parent.mkdir(parents=True, exist_ok=True) out_path.write_text(report, encoding="utf-8") print(f"✅ Report saved to: {out_path}") else: print(report) if __name__ == "__main__": main()