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 enter.pollinations.ai/secrets/prod.vars.json | jq -r '.TINYBIRD_ACCESS_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

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)

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

Revenue Trend (9 Weeks)

Notes

• Free tier spending in Tinybird includes daily 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