spending-analysis

Requirements

Before using this skill, ensure you have:

• curl: For API requests
• jq: brew install jq (for parsing JSON)
• sops: brew install sops (for decrypting secrets)

Must run from the pollinations repo root with access to enter.pollinations.ai/.

---

Data Sources

Polar API

• Orders: Payment history for pack purchases
• Products: Tier subscriptions and pollen packs
• Customers: User payment info linked by external_id

Tinybird

• generation_event: Usage data with user_tier, total_price, user_id
• Tracks all API requests with pricing and tier info

---

Quick Commands

Get Polar Access Token

# From sops-encrypted secrets
export POLAR_ACCESS_TOKEN=$(sops -d enter.pollinations.ai/secrets/prod.vars.json | jq -r '.POLAR_ACCESS_TOKEN')

# Or from .testingtokens (if available)
export POLAR_ACCESS_TOKEN=$(grep POLAR_ACCESS_TOKEN enter.pollinations.ai/.testingtokens | cut -d= -f2)

Get Tinybird Token

export TINYBIRD_TOKEN=$(sops -d apps/operation/kpi/secrets/env.json | jq -r '.TINYBIRD_TOKEN')

---

Polar API Queries

List Products (Tiers & Packs)

curl -sL "https://api.polar.sh/v1/products" \
  -H "Authorization: Bearer $POLAR_ACCESS_TOKEN" | jq '[.items[] | {name, id, recurring: .is_recurring}]'

Get Pack Purchases (Last 100)

# 5 pollen pack product ID
PRODUCT_ID="bcdde7f7-129e-4ec1-abc3-d4e0c852fa68"

curl -sL "https://api.polar.sh/v1/orders?limit=100&product_id=$PRODUCT_ID" \
  -H "Authorization: Bearer $POLAR_ACCESS_TOKEN" | \
  jq '[.items[] | {date: .created_at[0:10], amount: (.total_amount / 100), customer: .customer.email}]'

All Pack Product IDs

Pack	Product ID
5 pollen + 5 FREE	bcdde7f7-129e-4ec1-abc3-d4e0c852fa68
10 pollen + 10 FREE	cebeb680-4ac3-4f73-9ce7-6bc06a5f21e1
20 pollen + 20 FREE	8164c20f-8429-437e-b1a2-616ae89f114e
50 pollen + 50 FREE	2cb5ca34-d505-450d-a1d4-94e3bb0c1f68
10 pollen (pack)	ca4cc8bc-694d-4710-8451-1dcb4979fbc7
20 pollen (pack)	679a9fd3-be30-4552-8b2e-f825103c42b7
50 pollen (pack)	bf48ded2-19ca-4d28-bca9-a91fff2dd0a0

Weekly Revenue from All Packs

.claude/skills/spending-analysis/scripts/weekly-pack-revenue.sh

---

Tinybird Queries

User Count by Tier

curl -sL "https://api.europe-west2.gcp.tinybird.co/v0/sql" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN" \
  --data-urlencode "q=SELECT argMax(user_tier, start_time) as tier, count() as users FROM generation_event WHERE start_time >= now() - INTERVAL 60 DAY AND environment = 'production' AND user_id != 'undefined' GROUP BY user_id FORMAT JSON" | \
  jq '.data | group_by(.tier) | map({tier: .[0].tier, users: length})'

Weekly Spending by Tier

curl -sL "https://api.europe-west2.gcp.tinybird.co/v0/sql" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN" \
  --data-urlencode "q=SELECT toStartOfWeek(start_time) as week, user_tier, sum(total_price) as total_spend, count() as requests FROM generation_event WHERE start_time >= now() - INTERVAL 60 DAY AND environment = 'production' GROUP BY week, user_tier ORDER BY week DESC FORMAT JSON" | jq '.data'

---

Analysis Scripts

Weekly Pack Revenue

.claude/skills/spending-analysis/scripts/weekly-pack-revenue.sh

Shows weekly breakdown of actual pack purchases (real revenue, not free tier usage).

Pack Purchases by Tier

.claude/skills/spending-analysis/scripts/pack-purchases-by-tier.sh

Cross-references Polar pack purchasers with Tinybird tier data to show which tiers buy most pollen proportionally.

---

Key Findings (Jan 2026 Analysis)

Pack Purchases by Tier (Weighted by User Count)

Tier	Revenue	Purchasers	Total Users	% Who Buy	$/User
nectar	$146	10	23	43.5%	$6.37
flower	$564	18	218	8.3%	$2.59
seed	$1,173	38	575	6.6%	$2.04
spore	$1,657	106	6,757	1.6%	$0.25

Key Insight: Higher tiers buy MORE pollen proportionally, not less.

Revenue Trend (9 Weeks)

Week	Orders	Revenue
Jan 13-19	51	$573
Jan 6-12	83	$928
Dec 30-Jan 5	59	$928
Dec 23-29	21	$432
Dec 16-22	22	$276
Dec 9-15	16	$141
Dec 2-8	17	$293
Nov 25-Dec 1	10	$293
Nov 18-24	1	$10

---

Notes

• Free tier spending in Tinybird includes tier pollen allocation - not real revenue
• Pack purchases in Polar are actual paid revenue
• Cross-reference by external_id (Polar) = user_id (Tinybird)
• Polar API returns 307 redirects - use curl -sL to follow