Stripe KPI Dashboard - Specifications
Overview
The Stripe KPI Dashboard provides real-time analytics and business intelligence for Pivot's subscription business. It tracks customer lifecycle, revenue metrics, and billing activity by querying data synced from Stripe to BigQuery.
Dashboard URL: https://kpi.pivotdev.ca
Data Source: BigQuery pivot-inc.stripe_data dataset (synced from Stripe)
Customer Classification
Customers are classified into four types based on their subscription status and payment history relative to their ARR:
Customer Types
| Type | Definition | Subscription Status | Total Charged |
|---|---|---|---|
| Tryout | Active subscription, paid more than $0 but 30% or less of ARR | active | More than $0 and 30% or less of ARR |
| Subscriber | Active subscription with more than 30% ARR paid, OR $0 (HubSpot migration) | active | More than 30% ARR or $0 |
| Churned | Had subscription, now canceled | canceled | Any |
| Prospect | Customer record, no subscription | null | Any |
Tryout Definition (December 2025 - Updated)
A Tryout is a customer who:
- Has an active subscription in Stripe
- Has paid something (total_charged is more than $0)
- Has paid 30% or less of their ARR (Annual Recurring Revenue)
Key Logic:
- Customers with $0 charged are classified as Subscriber (HubSpot migrations - established customers migrated from another system)
- Customers who have paid more than 30% of ARR are classified as Subscriber (converted/paying customers)
- Only customers in the middle (paid something, but 30% or less) are Tryout
Why 30%? This threshold captures customers who have made an initial payment but haven't yet committed to a full billing cycle. For example:
- A customer on a $960/year plan who paid $100 upfront (10.4% of ARR) is a Tryout
- Once they pay more than $288 (30% of $960), they become a Subscriber
Tryout Fields:
is_tryout: Boolean flag indicating Tryout statustryout_end_date: Date of next payment (when they might convert)tryout_days_remaining: Days until next paymentcharged_percent_of_arr: Percentage of ARR already paid
Billing & Payment Tracking
Invoice Types
Stripe invoices can be categorized into two types:
Total Charged Calculation
total_charged represents the total amount collected from a customer across ALL paid invoices, regardless of whether they are linked to a subscription.
-- Total charged = Sum of ALL paid invoices for the customer
SELECT
customer,
SUM(amount_paid) / 100.0 as total_charged
FROM invoices
WHERE status = 'paid'
GROUP BY customer
Why this matters:
- Some customers pay via one-time invoices before their subscription starts
- Setup fees are often invoiced separately from subscriptions
- This gives a complete picture of revenue collected from each customer
Example Scenarios
Customer: La Gargouille (Tryout)
- Subscription: $960/year (12 seats x $80/seat)
- Total charged: $275.94 (28.7% of ARR)
- Subscription status: Active
- Customer type: Tryout (paid more than $0 but 30% or less of ARR)
This customer:
- Is classified as Tryout because $275.94 is 28.7% of their $960 ARR
- The
charged_percent_of_arrfield shows 28.7 - Once they pay more than $288 (30% of $960), they become a Subscriber
Customer: HubSpot Migration (Subscriber with $0)
- Subscription: $960/year
- Total charged: $0 (migrated from HubSpot)
- Subscription status: Active
- Customer type: Subscriber (not Tryout!)
This customer:
- Is classified as Subscriber because $0 charged indicates HubSpot migration
- They are established customers, not new trials
- The
tryout_convertedflag is false (they never went through tryout)
Subscription Amount Display
The Subscription column shows the subscription value with billing interval:
| Format | Example | Meaning |
|---|---|---|
$960.00 CAD/yr | Annual billing | Total per year |
$80.00 CAD/mo | Monthly billing | Total per month |
Calculated as: unit_amount_cents × quantity / 100
KPI Metrics
Active Subscribers
Customers with subscription_status = 'active' or 'trialing'
Note: This counts all active subscriptions, including Tryouts. To see only paying subscribers, filter by customer_type = 'Subscriber'.
Total ARR (Annual Recurring Revenue)
-- ARR calculation (only from active subscriptions)
SELECT SUM(
CASE
WHEN billing_interval = 'year' THEN (unit_amount * quantity)
WHEN billing_interval = 'month' THEN (unit_amount * quantity * 12)
END
) as total_arr
FROM subscriptions
WHERE status = 'active'
Conversion Rate
Conversion Rate = Subscribers (with tryout_converted=true) / (Subscribers + Active Tryouts) × 100
Measures what percentage of customers have converted from tryout to paying subscriber status. The tryout_converted flag identifies subscribers who previously went through the tryout phase.
Tryouts Starting Within 30 Days
Tryout customers whose current_period_end (first billing date) is within 30 days. These are "urgent" because they will soon convert or churn.
Data Flow
BigQuery Tables Used
| Table | Purpose |
|---|---|
stripe_data.customers | Customer records (email, name, created date) |
stripe_data.subscriptions | Subscription details (status, plan, quantity) |
stripe_data.invoices | Invoice records (amount_paid, status, subscription link) |
stripe_data.products | Product names and details |
Dashboard Views
Available Views
| View | Filter | Description |
|---|---|---|
| All Customers | None | Complete customer list |
| Active Subscribers | subscription_status = 'active' | All active subscriptions (Subscribers + Tryouts) |
| Active Tryouts | customer_type = 'Tryout' | Paid 30% or less of ARR, not yet fully converted |
| Starting Soon | Tryouts within 30 days | Urgent tryouts nearing next payment |
| Subscribers | customer_type = 'Subscriber' | Paying customers (more than 30% ARR or $0 HubSpot) |
| By Type | Grouped by customer_type | Overview of customer distribution |
| By Product | Grouped by product_name | Revenue by product/plan |
| By Billing | Grouped by billing_interval | Monthly vs annual breakdown |
| Churned | customer_type = 'Churned' | Canceled subscriptions |
| Prospects | customer_type = 'Prospect' | No subscription yet |
Columns Available
| Column | Source | Description |
|---|---|---|
| customer.email | Links to Stripe customer page | |
| Name | customer.name | Links to Stripe customer page |
| Type | Calculated | Customer classification |
| Product | product.name | Subscription product name |
| Qty | subscription.quantity | Number of seats/units |
| Subscription | Calculated | unit_amount × qty with interval |
| Total Charged | Calculated | Sum of ALL paid invoices |
| Billing | subscription.interval | Monthly/Yearly |
| Status | subscription.status | active/trialing/canceled |
| Starts In | Calculated | Days until first payment (tryouts) |
Invoice Sections
Upcoming Invoices
Invoices Stripe will generate based on active subscriptions:
- This Week: Next 7 days
- This Month: Next 30 days
- Total count and amount
Accounts Receivable
Open invoices with failed or pending payments:
- Failed Payments: Invoice attempted but payment failed
- Overdue: Past due date
- Outstanding Amount: Total amount still owed
Technical Implementation
SQL Query Location
pivot-kpi/sql/get-stripe-customers.sql
Key CTEs in Query
- customer_subscriptions: Gets best subscription per customer, calculates ARR
- customer_total_charged: Sums ALL paid invoices per customer
The customer type is calculated inline based on:
- Subscription status (active/trialing vs canceled vs null)
- Total charged vs ARR percentage (for Tryout vs Subscriber distinction)
Integration Tests
Located in: pivot-kpi/tests/integration/stripe-customers-query.test.ts
19 tests covering:
- Customer type classification (Tryout, Subscriber, Churned, Prospect)
- HubSpot migration detection ($0 charged = Subscriber)
- 30% ARR boundary edge case
- ARR calculation for yearly and monthly subscriptions
- Total charged calculation (subscription-linked and unlinked invoices)
- charged_percent_of_arr calculation
- tryout_end_date and tryout_days_remaining fields
- tryout_converted flag behavior
- product_name extraction
- Active subscriber counts
- Subscription status by customer type
Glossary
| Term | Definition |
|---|---|
| ARR | Annual Recurring Revenue - total yearly subscription value |
| MRR | Monthly Recurring Revenue - ARR / 12 |
| Tryout | Customer with active subscription who has paid more than $0 but 30% or less of ARR |
| Subscriber | Customer with active subscription who has paid more than 30% of ARR, OR $0 (HubSpot migration) |
| Churned | Customer whose subscription was canceled |
| Prospect | Customer record with no subscription |
| charged_percent_of_arr | (total_charged / ARR) × 100 - percentage of annual value already paid |
| tryout_converted | Boolean flag: true if customer paid more than 30% of ARR (converted from tryout) |
| Total Charged | Sum of amount_paid from ALL paid invoices for a customer |
| HubSpot Migration | Established customer migrated from HubSpot with $0 charged in Stripe |
Change Log
| Date | Change | Author |
|---|---|---|
| Dec 2025 | Initial specification document | Team |
| Dec 2025 | Added total_charged for unlinked invoices | Team |
| Dec 2025 | Added billing interval to subscription display | Team |
| Dec 2025 | Major Update: New Tryout definition based on % of ARR paid | Team |
| Dec 2025 | Added HubSpot migration detection ($0 = Subscriber) | Team |
| Dec 2025 | Removed "Converted" customer_type (now uses Subscriber + tryout_converted flag) | Team |
| Dec 2025 | Added charged_percent_of_arr, tryout_end_date fields | Team |
| Dec 2025 | Expanded integration tests to 19 test cases | Team |