Skip to main content
skill CX Codex

spending-analysis — Claude Code skill for revenue analysis

The spending-analysis skill queries Tinybird production data to examine Stripe revenue, pack purchases, and Pollen balance spending. Use it to reconcile payment sources or assess conversion cohorts.

MIT 1 installs ★ 5,322 updated 2026-10-24

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add pollinations/pollinations --skill spending-analysis -a claude-code
Open on GitHub

What spending-analysis does

The skill provides queries for stripe_event and generation_event_v2, covering weekly revenue, recent purchases, revenue by customer, and spend by balance bucket. It also lays out checks for pricing changes and conversion-cohort comparisons.

Run the queries from the pollinations repository root, with jq and sops installed. The enter.pollinations.ai/observability/scripts/tb-prod.sh script reads a production token from SOPS; staging has no real revenue. Set set -o pipefail before piping results to jq, or a failed query may not fail the full pipeline.

Pollen consumption is not cash revenue: balances can also receive auto-top-ups and rewards. Keep principal, gross payment, taxes, refunds, and net revenue separate. For cohort claims, align observation windows and count each user once.

What it helps with

  • calculate weekly revenue from paid pack purchases
  • inspect recent purchases and revenue by customer
  • group Pollen consumption by balance bucket
  • reconcile Stripe purchases with auto-top-ups and rewards
  • test whether a reward is linked to payment conversion

Example prompts for the agent

  • Calculate weekly revenue from paid pack purchases over the last 90 days.
  • Group Pollen spending by balance bucket for the last 60 days.
  • Help me test whether a reward is linked to conversion into paid usage.

How to install spending-analysis

1Skills CLI (Vercel)

npx skills add pollinations/pollinations --skill spending-analysis -a claude-code

2Manually: put the files in place

~/.claude/skills/spending-analysis/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/spending-analysis/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about spending-analysis

How do I install spending-analysis in Claude Code?

From the repository root, run npx skills add pollinations/pollinations --skill spending-analysis -a claude-code. The skill file is at ~/.claude/skills/spending-analysis/SKILL.md.

What does spending-analysis do?

It provides Tinybird queries for Stripe revenue and Pollen consumption, plus guidance on reconciling funding sources, investigating price changes, and comparing conversion cohorts.

What does spending-analysis need to run?

Run it from the pollinations repository root and install jq and sops. Its query script uses a production read token from SOPS; staging has no real revenue.

Source text

SKILL.md · MIT
---
name: spending-analysis
description: Analyze Pollinations Stripe revenue, pack purchases, and balance-bucket spending patterns with Tinybird production data.
---

# Requirements

- Run from the `pollinations` repository root.
- Install `jq` and `sops`.
- Query with `enter.pollinations.ai/observability/scripts/tb-prod.sh "<sql>"` (below written
  as `tb-prod.sh`). It uses the production read token from SOPS; the staging workspace has
  no real revenue.
- Enable `set -o pipefail` before piping query output so a failed query fails the whole pipeline.

Revenue queries must filter successful Stripe checkout events so asynchronous payment
methods are counted exactly once.

# Weekly pack revenue

```bash
tb-prod.sh "SELECT toStartOfWeek(timestamp) AS week, round(sum(amount_cents) / 100, 2) AS revenue_usd, count() AS purchases FROM stripe_event WHERE payment_status = 'paid' AND event_type IN ('checkout.session.completed', 'checkout.session.async_payment_succeeded') AND timestamp >= now() - INTERVAL 90 DAY GROUP BY week ORDER BY week DESC FORMAT JSON" | jq '.data'
```

# Recent pack purchases

```bash
tb-prod.sh "SELECT timestamp, user_id, session_id, amount_cents / 100 AS amount, currency, payment_method FROM stripe_event WHERE payment_status = 'paid' AND event_type IN ('checkout.session.completed', 'checkout.session.async_payment_succeeded') ORDER BY timestamp DESC LIMIT 100 FORMAT JSON" | jq '.data'
```

# Revenue by customer

```bash
tb-prod.sh "SELECT user_id, round(sum(amount_cents) / 100, 2) AS revenue_usd, count() AS purchases FROM stripe_event WHERE payment_status = 'paid' AND event_type IN ('checkout.session.completed', 'checkout.session.async_payment_succeeded') AND timestamp >= now() - INTERVAL 30 DAY GROUP BY user_id ORDER BY revenue_usd DESC LIMIT 50 FORMAT JSON" | jq '.data'
```

# Weekly spend by balance bucket

```bash
tb-prod.sh "SELECT toStartOfWeek(start_time) AS week, splitByChar(':', selected_meter_slug)[-1] AS meter_source, sum(total_price) AS total_spend, count() AS requests FROM generation_event_v2 WHERE start_time >= now() - INTERVAL 60 DAY AND environment = 'production' GROUP BY week, meter_source ORDER BY week DESC FORMAT JSON" | jq '.data'
```

# Where the money came from vs what was spent

`generation_event_v2` spend (`pack_spend`, `selected_meter_slug`) says which bucket was
*consumed*, not how it was funded. `pack_balance` is credited by Stripe purchases and
auto-top-ups, but also by BYOP markup and community-model rewards (paid into the payer's
bucket, `shared/billing/track-helpers.ts`) and some quest rewards. So pack spend ≠ cash.
And Stripe checkout rows alone miss auto-top-ups, which never create a `checkout.session.*`
event. To separate cash from earned funding, reconcile each source on its own and don't
add figures with different monetary bases:

- Stripe checkout `pollen_credited` (`stripe_event`, `payment_status = 'paid'`)
- Auto-top-up `amount_usd` (principal credited — see below)
- Historical Polar credits (Nov 2025–Jan 2026)
- Claimed community/BYOP rewards, by bucket

No cash purchase ≠ non-payment or abuse; pack spend ≠ proof of cash.

# Auto-top-up: principal vs gross payment

Auto-top-up `amount_usd` in D1 is the Pollen principal credited; Stripe fees and taxes sit
on top. Gross cash is in Tinybird `stripe_event` as `payment_intent.succeeded` rows with an
empty `user_id` (auto top-ups are not attributed there); per user, fetch the invoice by
`stripe_invoice_id` and use `amount_paid`. Keep principal, gross payment, tax, refunds and
net revenue as separate numbers.

# Historical pricing changes

Define a past price change from the code deployed at the time, not the PR title or
announcement — an "X% cheaper" announcement can coexist with a same-day multiplier change
that cancels it. Confirm billed unit prices in `generation_event_v2`:

```bash
tb-prod.sh "SELECT toDate(start_time) AS day, round(avg(total_price), 6) AS avg_unit_price, count() AS requests FROM generation_event_v2 WHERE model_used = '<model>' AND start_time BETWEEN '<before>' AND '<after>' GROUP BY day ORDER BY day FORMAT JSON" | jq '.data'
```

Provider cost, `priceMultiplier` markup and pack-credit promotions move independently;
check all three. Keep cash paid separate from Pollen credited — a Pollen discount doesn't
change cash buying power. Note who was exposed before the payment event you're explaining
and any simultaneous changes.

# Conversion cohort analysis

For "these users pay more" or "X caused conversion" claims, define the comparison first:

- **Pick the cohort without looking at the outcome.** Don't select on payment or on
  rewards that require a payment. Contribution rewards (PR/quest merges) are fine;
  payment-triggered rewards are not.
- **Freeze a cutoff date** for both membership and the payment check.
- **Match observation windows.** A last-7-days cohort has had 0–7 days to convert;
  compare it only with another cohort measured the same way, or use a fixed per-user
  window ("first 7 days after signup").
- **Check ordering.** For a reward-linked cohort, count who paid *before* the reward vs
  *after*. If most paid first, the reward didn't drive conversion.
- **Count each user once.**
- **Report group sizes and caveats** with the percentage.

# Notes

- `stripe_event` is the source of truth for pack-purchase revenue analytics.
- `generation_event_v2` records Pollen consumption, not cash revenue.
- Both datasets use `user_id`, so revenue and usage can be joined directly.
- The dashboard's `daily_stripe_revenue` pipe applies the same paid-event filter.
- For pre-migration revenue history, note that Polar was the pre-Stripe merchant
  of record (Nov 2025–Jan 2026) and is retired. Do not combine historical Polar
  and Stripe totals without checking the cutoff for overlap.
- Request-count shares (e.g. BYOP share) are dominated by free traffic and drift fast.
  Always state the date window, and prefer revenue share (% of `total_price`) for
  strategic claims.

© pollinations · MIT · pollinations/pollinations

Similar skills

All skills for Finance and business →

From the same set3

Project sponsors

Companies that keep this analytics open