My Dashboard Was Lying About Churn, Revenue, and Lifetime — Here's What I Found

Hey IH! Week 5 update on Underpriced AI — the AI pricing tool for resellers.

The Short Version

Spent the week making my admin dashboard trustworthy. Found 6+ data integrity issues across churn, timezone handling, revenue tracking, and lifetime calculations. Also removed scan expiration after a user email exposed it as a bad policy.

What Was Wrong

Churn was 4x overstated

Dashboard showed 4 churns / $141 lost MRR. Real number: 1 churn / $9. The query was counting:

• Trial cancellations (never paid)

• Refunded users (complained about no 30-day trial)

• Banned accounts

Fix: aligned dashboard with the forecasts page — both now exclude trials (stripeTrialEnd: null), non-subscribers (subscribedAt: not null), and banned users.

Every date was wrong

All SQL DATE() calls used UTC. PostgreSQL stores Prisma timestamps as timestamp without time zone. A single AT TIME ZONE 'America/Los_Angeles' tells PG "this value IS in LA time" — the opposite of what I wanted. Needed double conversion: AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles'.

Then the frontend had its own bug: new Date("2026-04-03") parses as UTC midnight, and toLocaleDateString({timeZone: "America/Los_Angeles"}) shifts it back to April 2nd. Fixed by appending T12:00:00 before formatting.

3 separate timezone bugs, each causing off-by-one errors in different directions.

Customer lifetime was absurd

Showing 37 months for a 4-month-old product. Formula was 1 / monthly_churn_rate which is theoretically correct but unproven. Capped at actual product age — you earn credibility as retention is demonstrated.

What I Built

Revenue intelligence. Stripe Revenue card now shows 3 metrics: Collected (90 days), Past Due (failed payment count + amount), Expected/Month (MRR from active subs, yearly normalized).

MRR Movement fix. Day selector had two bugs: shared state with the activity chart (clicking 7D on MRR reloaded the whole page), and churned MRR always used calendar month regardless of selection. Now independent state + consistent time window for both new and churned MRR.

Growth Drivers on forecasts. Integrated Google Analytics 4 directly. Shows monthly organic/direct/referral/social sessions with stacked bars, month-pace extrapolation, organic share %, and MoM growth. Organic search: 2 sessions (Jan) → 41 (Feb) → 1,303 (Mar) → pacing 1,620 (Apr).

Info tooltips. New MetricTip component (Radix portal tooltip) on every metric across dashboard and forecasts. Hover to see what it means. Sounds small but I was forgetting my own metric definitions.

Scan packs in activity chart. The Activity Overview now tracks scan pack purchases alongside signups, items, scans, and subscriptions.

Scans activity fix. "Scans" line was querying ItemAnalysis (saved items only) instead of QuickScan (all scans). Was mirroring the Items line exactly.

The Scan Expiration Story

User emailed: "I saved my scans knowing I'd come back to list them. Now they're expired and gone."

Scans expired after 24 hours. To get them back, he'd have to re-scan — burning credits he already paid for. That's punishing users for not working fast enough.

Removed expiration entirely. Scans persist forever. Storage cost is negligible ($0.10/mo S3 + $0.81/mo DB). Credited him 7 scans, sent a personal email.

Dependency cleanup

• Removed canvas and @napi-rs/canvas (neither was imported anywhere — dead weight)

• Upgraded otplib v12 → v13 (eliminated 3 deprecation warnings, had to migrate 2FA API: authenticator.verify() → verifySync().valid)

• 4 fewer npm deprecation warnings on deploy

Numbers (Week 5)

• $589 MRR / $7.1K ARR

• 36 paying subscribers, 842 total users

• 66% gross margin, 2.7% monthly churn

• Organic search: 1,303 sessions in March, #1 acquisition channel

• SEO blog content driving high-intent traffic (antique identifier apps, thrift store apps, worthpoint alternatives)

• 19 commits this week

Questions for IH

1. How do you handle timezone in your dashboards? The PostgreSQL timestamp without time zone + AT TIME ZONE gotcha cost me half a day. Curious if others have cleaner patterns.

2. Do you expire user-generated content? I removed scan expiration but wondering if there's a middle ground at scale (millions of records).

Building in public at underpricedai.com