{"queries":[{"id":"current_state","compiledQueryString":"select\n round(sum(monthly_price_chf), 2) as mrr_chf,\n count(*) as active_subscriptions\nfrom pulscheck.subscriptions\nwhere started_at <= '2026-04-30 23:59:59+02:00'\n and (canceled_at is null or canceled_at > '2026-04-30 23:59:59+02:00')","inputQueryString":"select\n round(sum(monthly_price_chf), 2) as mrr_chf,\n count(*) as active_subscriptions\nfrom pulscheck.subscriptions\nwhere started_at <= '2026-04-30 23:59:59+02:00'\n and (canceled_at is null or canceled_at > '2026-04-30 23:59:59+02:00')","compiled":false,"inline":true},{"id":"mrr_history","compiledQueryString":"with months as (\n select\n date_trunc('month', d) as month\n from generate_series(\n timestamp '2025-05-01',\n timestamp '2026-04-01',\n interval '1 month'\n ) as t(d)\n)\nselect\n m.month,\n round(sum(s.monthly_price_chf), 2) as mrr_chf,\n count(s.id) as active_subscriptions\nfrom months m\nleft join pulscheck.subscriptions s\n on cast(s.started_at as timestamp) <= m.month + interval '1 month' - interval '1 second'\n and (s.canceled_at is null\n or cast(s.canceled_at as timestamp) > m.month + interval '1 month' - interval '1 second')\ngroup by 1\norder by 1","inputQueryString":"with months as (\n select\n date_trunc('month', d) as month\n from generate_series(\n timestamp '2025-05-01',\n timestamp '2026-04-01',\n interval '1 month'\n ) as t(d)\n)\nselect\n m.month,\n round(sum(s.monthly_price_chf), 2) as mrr_chf,\n count(s.id) as active_subscriptions\nfrom months m\nleft join pulscheck.subscriptions s\n on cast(s.started_at as timestamp) <= m.month + interval '1 month' - interval '1 second'\n and (s.canceled_at is null\n or cast(s.canceled_at as timestamp) > m.month + interval '1 month' - interval '1 second')\ngroup by 1\norder by 1","compiled":false,"inline":true},{"id":"plan_distribution","compiledQueryString":"select\n current_plan,\n count(*) as customers\nfrom pulscheck.customers\ngroup by current_plan\norder by customers desc","inputQueryString":"select\n current_plan,\n count(*) as customers\nfrom pulscheck.customers\ngroup by current_plan\norder by customers desc","compiled":false,"inline":true},{"id":"churn_history","compiledQueryString":"with months as (\n select date_trunc('month', d) as month\n from generate_series(timestamp '2025-05-01', timestamp '2026-04-01', interval '1 month') as t(d)\n)\nselect\n m.month,\n count(s.id) as churned_customers,\n round(sum(s.monthly_price_chf), 2) as lost_mrr_chf\nfrom months m\nleft join pulscheck.subscriptions s\n on cast(s.canceled_at as timestamp) >= m.month\n and cast(s.canceled_at as timestamp) < m.month + interval '1 month'\n and cast(s.started_at as timestamp) < m.month\ngroup by 1\norder by 1","inputQueryString":"with months as (\n select date_trunc('month', d) as month\n from generate_series(timestamp '2025-05-01', timestamp '2026-04-01', interval '1 month') as t(d)\n)\nselect\n m.month,\n count(s.id) as churned_customers,\n round(sum(s.monthly_price_chf), 2) as lost_mrr_chf\nfrom months m\nleft join pulscheck.subscriptions s\n on cast(s.canceled_at as timestamp) >= m.month\n and cast(s.canceled_at as timestamp) < m.month + interval '1 month'\n and cast(s.started_at as timestamp) < m.month\ngroup by 1\norder by 1","compiled":false,"inline":true},{"id":"cohort_retention","compiledQueryString":"with cohorts as (\n select\n date_trunc('month', cast(c.signup_date as timestamp)) as cohort_month,\n c.id as customer_id,\n s.id as subscription_id,\n cast(s.started_at as timestamp) as started_at,\n cast(s.canceled_at as timestamp) as canceled_at\n from pulscheck.customers c\n left join pulscheck.subscriptions s on s.customer_id = c.id\n where c.signup_date >= '2024-06-01'\n and c.signup_date < '2026-02-01'\n)\nselect\n strftime(cohort_month, '%Y-%m') as cohort,\n count(distinct customer_id) as cohort_size,\n count(distinct case\n when subscription_id is not null\n and started_at is not null\n and (canceled_at is null\n or canceled_at > started_at + interval '90 days')\n then customer_id\n end) as retained_after_90d,\n round(100.0 * count(distinct case\n when subscription_id is not null\n and started_at is not null\n and (canceled_at is null\n or canceled_at > started_at + interval '90 days')\n then customer_id\n end) / nullif(count(distinct customer_id), 0), 1) as retention_pct\nfrom cohorts\ngroup by 1\norder by 1","inputQueryString":"with cohorts as (\n select\n date_trunc('month', cast(c.signup_date as timestamp)) as cohort_month,\n c.id as customer_id,\n s.id as subscription_id,\n cast(s.started_at as timestamp) as started_at,\n cast(s.canceled_at as timestamp) as canceled_at\n from pulscheck.customers c\n left join pulscheck.subscriptions s on s.customer_id = c.id\n where c.signup_date >= '2024-06-01'\n and c.signup_date < '2026-02-01'\n)\nselect\n strftime(cohort_month, '%Y-%m') as cohort,\n count(distinct customer_id) as cohort_size,\n count(distinct case\n when subscription_id is not null\n and started_at is not null\n and (canceled_at is null\n or canceled_at > started_at + interval '90 days')\n then customer_id\n end) as retained_after_90d,\n round(100.0 * count(distinct case\n when subscription_id is not null\n and started_at is not null\n and (canceled_at is null\n or canceled_at > started_at + interval '90 days')\n then customer_id\n end) / nullif(count(distinct customer_id), 0), 1) as retention_pct\nfrom cohorts\ngroup by 1\norder by 1","compiled":false,"inline":true}]}

Subscription Health

Subscription Health

MRR-Verlauf (12 Monate)

Plan-Verteilung

Churn-Verlauf (12 Monate)

Cohort-Retention (Anmeldemonat → Status nach 90 Tagen)