Etsy + Payhip profit digest (what you kept after fees)

For: Etsy and Payhip sellers

The job: Sales look fine until fees, ads and refunds come off - and the statement is a long CSV nobody reads.

You give it: Etsy orders CSV, Etsy monthly statement CSVs (Finances > Monthly statements), Payhip sales export.

It is one agent of the free AI OS starter (https://subzero.bodymindbeing.me/free/ai-os-starter/) - download the whole starter, it runs on sample data out of the box. Nothing is sent anywhere: replies come out as drafts.

Download the AI OS starter (zip) 65 KB

MIT licence. Python 3.10+, no other installs. Unzip, then follow README.md.

Run this one

1. Set up the starter (the steps on the starter page).
2. python -m aios run profit_digest
3. Edit config.json: point "profit_digest" at your own files (paths relative to config.json).

Check it yourself

1. Open the Etsy statement CSVs, keep the last 30 days and add up the Net column - it must equal 'you kept'.
2. Sales + fees + marketing + refunds (all from the statement) must equal 'kept'.
3. No AI at all in this agent: money numbers are never guessed.

agents/profit_digest.py

"""ETSY + PAYHIP - what you really kept. Reads your order exports (Etsy: Shop Manager > Settings > Options > Download
Data > Orders; Payhip: your sales export) and your Etsy monthly statement CSVs (Shop Manager > Finances > Monthly
statements > Download CSV). Plain code, no AI - money numbers should never be guessed:
  sales last 7 days vs the 7 before, best sellers, refunds,
  and from the statements: Etsy fees + ads + refunds, so you see what you kept after Etsy took its share.
Payhip fees are not in its sales export, so Payhip is shown as sales only (add a "Fee" column if your export has one)."""
from collections import defaultdict
from datetime import timedelta

from aios.config import path

from ._common import col, money, table, to_date, today

TITLE = "Shop profit digest"


def orders(cfg, s):
    """Every order export -> [(date, platform, item, amount, refunded)]."""
    out = []
    for src in s["orders"]:
        for r in table(cfg, src["file"]):
            d = to_date(col(r, "sale date", "date", "order date", "created at", "date paid"))
            amount = money(col(r, "item total", "amount", "total", "price"))
            status = (col(r, "status", "order status") or "").lower()
            if d:
                out.append((d, src.get("platform", ""), col(r, "item name", "product name", "product", "title"),
                            amount, "refund" in status or amount < 0))
    return out


def statement(cfg, s):
    """Etsy monthly statement rows -> [(date, type, net)]. Type = Sale / Fee / Marketing / Refund / Tax / ..."""
    out = []
    folder = path(cfg, s.get("statements", "."))
    for f in sorted(folder.glob(s.get("statement_files", "etsy_statement*.csv"))):
        for r in table(cfg, str(f)):
            d = to_date(col(r, "date"))
            if d:
                out.append((d, col(r, "type").strip().lower(), money(col(r, "net"))))
    return out


def run(cfg, data, s):
    now = today(cfg)
    cur = s.get("currency", "$")
    rows = orders(cfg, s)

    def window(rs, a, b):  # rows a..b days ago (inclusive)
        return [r for r in rs if now - timedelta(days=b) <= r[0] <= now - timedelta(days=a)]

    week, prev = window(rows, 1, 7), window(rows, 8, 14)
    sales = lambda rs: round(sum(r[3] for r in rs if not r[4]), 2)
    best = defaultdict(lambda: [0, 0.0])
    for r in week:
        if not r[4]:
            best[r[2]][0] += 1
            best[r[2]][1] += r[3]
    top = sorted(best.items(), key=lambda kv: kv[1][1], reverse=True)[:5]
    refunds = [r for r in week if r[4]]
    change = (sales(week) - sales(prev)) / sales(prev) * 100 if sales(prev) else 0
    by_platform = defaultdict(float)
    for r in week:
        if not r[4]:
            by_platform[r[1]] += r[3]
    items = [f"Last 7 days: {cur}{sales(week):,.2f} sales ({change:+.0f}% vs the 7 days before: {cur}{sales(prev):,.2f}) - "
             + ", ".join(f"{p} {cur}{v:,.2f}" for p, v in sorted(by_platform.items()))]
    summary = f"Last 7 days {cur}{sales(week):,.2f} in sales ({change:+.0f}%), {len(refunds)} refund(s)."
    st = statement(cfg, s)
    if st:
        days = s.get("profit_days", 30)
        month = [r for r in st if now - timedelta(days=days) <= r[0] < now]
        part = defaultdict(float)
        for _d, kind, net in month:
            part[kind] += net
        etsy_sales = part.get("sale", 0)
        taken = sum(v for k, v in part.items() if k != "sale")
        kept = etsy_sales + taken
        share = -taken / etsy_sales * 100 if etsy_sales else 0
        items.append(f"Etsy, last {days} days (statements): sales {cur}{etsy_sales:,.2f}, "
                     + ", ".join(f"{k} {cur}{v:,.2f}" for k, v in sorted(part.items()) if k != "sale")
                     + f" -> you kept {cur}{kept:,.2f} ({share:.1f}% went to fees, ads and refunds)")
        summary += f" Etsy kept-after-fees, last {days} days: {cur}{kept:,.2f} of {cur}{etsy_sales:,.2f}."
    items += [f"Best seller: {k} - {v[0]} sold, {cur}{v[1]:,.2f}" for k, v in top]
    attention = [f"Refund: {r[2]} ({r[1]}, {r[0]})" for r in refunds]
    if sales(prev) and change <= -25:
        attention.append(f"Sales down {change:.0f}% vs the week before")
    return {"summary": summary, "items": items, "attention": attention}