Daily Stripe revenue tracker with weekly trend email

I want an agent workflow that grows my own revenue trend ledger out of Stripe and emails me a weekly written report off it. The trigger is a cron schedule that runs every day at 8am America/New_York.

On every run (daily job), the agent should:

1) Compute the prior calendar day in America/New_York (yesterday's 00:00 to 23:59:59 in NY time, converted to UTC unix timestamps for the Stripe filters).

2) Use the Stripe List Balance Transactions operation, filtered by created.gte / created.lte to that day, to compute yesterday's gross volume, net volume (after Stripe fees), and refund total. Paginate fully with starting_after until has_more is false so the totals are correct. Amounts are in the smallest currency unit; convert to dollars when displaying.

3) Use the Stripe List Charges operation, filtered to yesterday, to compute the new-customer charge count (count of successful charges whose customer_id was first seen yesterday). Also use Stripe List Disputes filtered to yesterday for the dispute count.

4) Upsert ONE row keyed by date into a table called daily_revenue_snapshots in my General Input Database. Columns: snapshot_date (TEXT PRIMARY KEY, ISO yyyy-mm-dd), gross_volume_cents INTEGER, net_volume_cents INTEGER, refund_total_cents INTEGER, dispute_count INTEGER, new_customer_charge_count INTEGER, currency TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP. Use INSERT ... ON CONFLICT(snapshot_date) DO UPDATE SET ... so re-runs and backfills are idempotent. Create the table on the first run if it does not exist.

Then, ONLY if the run date in America/New_York is a Monday, additionally do the weekly trend report. For the weekly report do NOT call Stripe again. Read it entirely from the daily_revenue_snapshots table:

a) Select the last 28 snapshot_date rows from daily_revenue_snapshots ordered by date descending.

b) Bucket them into last-7-days vs prior-7-days vs the 14 days before that. Compute: week-over-week delta on gross volume, net volume, refunds, and disputes (both absolute and percent); the prior week's average daily gross; and the single largest mover day in the last 7 days (the day whose gross volume deviated the most from the trailing 28-day average, with the direction).

c) Send me a written trend email via the Gmail Send a Message operation, to my own address. Subject line like "Weekly revenue trend, week of <Mon date>". Body should have a short narrative paragraph at the top in plain language explaining what changed and why it might matter, followed by clean bullet highlights for each metric and the largest-mover callout. Format the body as HTML so the bullets render nicely. Construct the RFC 2822 message and base64url-encode it for the raw field, per Gmail's send semantics.

On non-Monday days the workflow just writes the snapshot and exits silently with no email.

Important behavior notes: the General Input Database is the multi-week persistent store. Stripe is only ever called for one day at a time (yesterday). The weekly report is always served from the database, never by re-paginating Stripe. If yesterday's snapshot row already exists (e.g. manual backfill), overwrite it with the freshest numbers rather than skipping. If the database table is empty on a Monday, send a short "not enough history yet" email instead of failing.

Additional information

What does this prompt do?

Every morning at 8am ET, pulls yesterday's gross volume, net volume, refunds, dispute count, and new customer charges from Stripe.
Saves one clean row per day into your own General Input database so the history keeps growing forever, even when Stripe's dashboard timeframes don't go back far enough.
On Mondays, reads the last 28 days from your database and emails you a written trend report with week-over-week deltas, the prior week's daily average, and the biggest single-day mover.
Builds a permanent revenue history you fully own, ready for future dashboards, board updates, or ad-hoc lookbacks.

What do I need to use this?

A Stripe account you can read with a secret API key.
A Google account so the workflow can send the weekly report email to you through Gmail.
Your General Input account, which already includes the built-in database the daily snapshots are stored in.

How can I customize it?

Change the daily run time (default 8am ET) or move the weekly report off Monday to any day that fits your team rhythm.
Pick which metrics get stored each day. Add subscription churn, payout totals, or average order value if you want them in the ledger.
Swap Gmail for a different inbox, or send the Monday trend report to a finance distribution list instead of just yourself.

Frequently asked questions

Why store the numbers in a separate database instead of just calling Stripe each time?

Stripe's reporting is great for the last few days, but pulling clean multi-week trends on demand means re-paginating the API every time. Saving one row per day into your own database means trend questions answer instantly, the history keeps growing forever, and you can reuse the same ledger in future dashboards or board prep.

What if a daily run is missed or fails?

The daily snapshot is keyed by date, so a later run simply fills in the missing day without creating duplicates. You can also re-run the workflow manually for any backfill date.

Do I need to set up the database table myself?

No. The workflow creates the daily snapshots table the first time it runs and adds new columns if you ask for extra metrics later.

Can I change which day the weekly trend report is sent?

Yes. Monday is the default because most teams plan around the start of the week, but you can move it to Friday for a wrap-up, or even send it twice a week.

Will this work for a business that processes payments in multiple currencies?

Yes. You can extend the daily snapshot to store one row per currency, or pick a base currency and have the workflow convert as it writes.