Your dashboard has thirty widgets. Your team opens it twice a year. That dashboard cost you weeks of engineering time and gives you nothing.
Strip it down to ten. Ten that answer the only question that matters: is the business getting healthier or sicker?
A SaaS business moves through four stages with every user: acquisition, activation, revenue, and retention. Each stage demands specific metrics. Every metric here ships with real SQL you can run on PostgreSQL. The queries come from dashboards we deploy for live SaaS products, including the pipelines behind SaaS Retention Pulse. No hand-waving.
1. DAU, WAU, MAU
Daily, weekly, and monthly active users tell you one thing: do people open the product? Track all three on the same chart and the gap between them reveals retention depth. A wide DAU-to-MAU gap means users come back weekly but rarely daily. A narrow gap means daily habit.
The SQL uses a rolling window. It counts distinct users per day, per rolling 7-day span, and per rolling 30-day span, all in a single pass.
with daily as (
select
date_trunc('day', event_time) as day,
count(distinct user_id) as dau
from events
where event_name = 'session_start'
group by 1
),
wau as (
select distinct
d1.day,
count(distinct d2.user_id) over (
partition by d1.day
order by d2.day
range between interval '6 days' preceding and current row
) as wau
from daily d1
join daily d2
on d2.day between d1.day - interval '6 days' and d1.day
),
mau as (
select distinct
d1.day,
count(distinct d2.user_id) over (
partition by d1.day
order by d2.day
range between interval '29 days' preceding and current row
) as mau
from daily d1
join daily d2
on d2.day between d1.day - interval '29 days' and d1.day
)
select
d.day,
d.dau,
w.wau,
m.mau,
round(d.dau::numeric / nullif(m.mau, 0), 3) as stickiness
from daily d
join wau w on w.day = d.day
join mau m on m.day = d.day
where d.day >= current_date - interval '90 days'
order by d.day;Stickiness (DAU/MAU) above 20% marks a daily-use product. Below 10% means you built a utility people reach for once a month. Neither is wrong, but you need to know which one you are.
2. MRR by Plan Tier
Monthly recurring revenue broken by plan answers two questions: which tier drives growth, and where does concentration risk live? If Enterprise contributes 80% of MRR, losing two accounts sinks the quarter.
The query below groups MRR by plan tier and month. It includes a cohort column so you can track whether revenue from users who signed up in January grew in March.
with subscriptions as (
select
s.user_id,
s.plan_tier,
s.mrr_amount,
date_trunc('month', s.start_date) as cohort_month,
date_trunc('month', d.month) as revenue_month,
sum(s.mrr_amount) as monthly_mrr
from subscriptions s
join generate_series(
date_trunc('month', s.start_date),
coalesce(s.ended_at, current_date),
interval '1 month'
) as d(month)
on true
where s.status in ('active', 'past_due', 'cancelled')
group by 1, 2, 3, 4, 5
)
select
revenue_month,
plan_tier,
sum(monthly_mrr) as mrr,
count(distinct user_id) as accounts,
cohort_month
from subscriptions
group by 1, 2, 5
order by revenue_month desc, plan_tier;Plot this as a stacked area chart in Grafana. The area shape shows whether your lower tiers feed the upper tiers or stay flat. A flat bottom tier with a growing enterprise tier means your sales team carries the business. A rising bottom tier means the product sells itself.
3. Trial-to-Paid Conversion
Signups mean intent. Paid means conviction. The gap is your conversion rate. Track it weekly because a 2% drop across thousands of trials costs you real revenue before you notice.
with trials as (
select
user_id,
date_trunc('week', trial_start) as trial_week,
case when p.user_id is not null then 1 else 0 end as converted
from trials t
left join lateral (
select distinct user_id
from subscriptions
where status = 'active'
and user_id = t.user_id
and start_date between t.trial_start
and t.trial_start + interval '30 days'
) p on true
where t.trial_start >= current_date - interval '180 days'
)
select
trial_week,
count(*) as trials_started,
sum(converted) as converted_to_paid,
round(100.0 * sum(converted) / count(*), 1) as conversion_pct
from trials
group by 1
order by trial_week desc;A healthy B2B SaaS converts 4-8% of trials. A PLG product hits 10-20%. Numbers below 4% mean either your trial experience hides the product’s value or your ICP qualification needs tightening.
4. Churn Rate and Early Warning Signals
Churn rate alone is a lagging indicator. By the time you calculate it, those users already left. The smarter dashboard pairs churn rate with leading indicators: accounts whose session count dropped 50% in 14 days, accounts with support tickets containing “cancel” or “competitor”, and accounts that went from daily to weekly usage.
with churn as (
select
date_trunc('month', ended_at) as churn_month,
count(distinct user_id) as churned,
(
select count(distinct user_id) from subscriptions
where status = 'active'
and date_trunc('month', start_date)
<= s.churn_month
and (ended_at is null
or ended_at >= s.churn_month)
) as beginning_mrr_accounts
from subscriptions s
where status = 'cancelled'
and churn_type = 'voluntary'
group by 1
),
signals as (
select
user_id,
date_trunc('day', event_time) as signal_date,
'session_drop' as signal
from sessions
where user_id in (
select user_id from subscriptions where status = 'active'
)
group by 1, 2
having count(*) < (
select avg(cnt) * 0.5
from (
select count(*) as cnt
from sessions s2
where s2.user_id = sessions.user_id
group by date_trunc('day', event_time)
)
)
)
select
c.churn_month,
c.churned,
c.beginning_mrr_accounts,
round(100.0 * c.churned / nullif(c.beginning_mrr_accounts, 0), 2)
as churn_rate_pct,
count(distinct s.user_id) as at_risk_accounts
from churn c
left join signals s
on s.signal_date between c.churn_month
and c.churn_month + interval '1 month'
group by 1, 2, 3;Pair churn rate with the signal count. When at-risk accounts spike but churn stays flat, your retention team has time to act. When both spike together, you are already bleeding.
5. Customer Lifetime Value
The standard SaaS LTV formula: ARPU divided by churn rate. Simple enough. But a single LTV number hides the difference between a $29/mo user who stays three years and a $499/mo user who cancels after four months. Segment it.
with plan_metrics as (
select
plan_tier,
avg(mrr_amount) as arpu,
count(distinct case when status = 'cancelled'
and churn_type = 'voluntary' then user_id end) * 1.0
/ count(distinct user_id) as monthly_churn_rate
from subscriptions
where status in ('active', 'cancelled')
group by 1
)
select
plan_tier,
round(arpu, 2) as arpu,
round(monthly_churn_rate, 4) as churn_rate,
case
when monthly_churn_rate > 0
then round(arpu / monthly_churn_rate, 2)
else null
end as ltv
from plan_metrics
order by ltv desc;Churn rate here uses monthly_churn_rate — the percentage of active accounts that cancel per month. LTV = ARPU / monthly churn. A $100 ARPU with 5% monthly churn gives you $2,000 LTV. Spend less than that to acquire a customer and the unit economics work. Spend more and you lose money on every sale.
6. Net Revenue Retention
NRR measures whether existing customers pay you more over time. It captures expansion revenue (upgrades, add-ons) against contraction and churn. An NRR above 120% means your product grows inside accounts faster than you lose them. That is the single strongest signal a SaaS investor looks for.
with cohort_revenue as (
select
date_trunc('month', start_date) as cohort,
date_trunc('month', month_series) as period,
month_series - date_trunc('month', start_date) as month_number,
sum(mrr_amount) as total_mrr
from subscriptions s
cross join lateral generate_series(
date_trunc('month', start_date),
least(
coalesce(ended_at, current_date),
date_trunc('month', start_date) + interval '12 months'
),
interval '1 month'
) as month_series
where start_date >= current_date - interval '18 months'
group by 1, 2, 3
)
select
cohort,
month_number,
total_mrr,
first_value(total_mrr) over (
partition by cohort order by month_number
) as starting_mrr,
round(
100.0 * total_mrr / first_value(total_mrr) over (
partition by cohort order by month_number
),
1
) as nrr_pct
from cohort_revenue
order by cohort desc, month_number;Plot NRR as a cohort grid. Each row is a signup month. Each column is a month of tenure. A green diagonal means expansion. A red column means churn. You spot problems in a specific cohort before they spread.
7. Session Duration Distribution
Average session duration lies. Five users who spend two seconds and one who spends thirty minutes average five minutes. The histogram tells the truth: a spike at zero-to-five seconds means users bounce. A fat tail above ten minutes means stickiness.
with session_length as (
select
user_id,
extract(epoch from (max(event_time) - min(event_time))) as duration_s
from events
where event_name in ('session_start', 'session_end')
group by user_id, session_id
having count(*) >= 2
),
buckets as (
select
case
when duration_s <= 5 then '0-5s'
when duration_s <= 30 then '5-30s'
when duration_s <= 60 then '30-60s'
when duration_s <= 300 then '1-5m'
when duration_s <= 600 then '5-10m'
when duration_s <= 1800 then '10-30m'
else '30m+'
end as bucket
from session_length
)
select
bucket,
count(*) as session_count,
round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
from buckets
group by 1
order by
case bucket
when '0-5s' then 1
when '5-30s' then 2
when '30-60s' then 3
when '1-5m' then 4
when '5-10m' then 5
when '10-30m' then 6
else 7
end;If 40% of sessions end in under five seconds, your login flow or initial load screen has a problem. Fix it. Run the query again. The histogram should shift right.
8. Feature Adoption Rate
You shipped a feature. Now measure whether anyone uses it. Track feature adoption as a rolling percentage of weekly active users who triggered the feature’s event at least once.
with wau as (
select
date_trunc('week', event_time) as week,
count(distinct user_id) as weekly_active
from events
where event_name = 'session_start'
group by 1
),
feature_usage as (
select
date_trunc('week', event_time) as week,
event_name as feature,
count(distinct user_id) as adopters
from events
where event_name like 'feature_%'
group by 1, 2
)
select
f.week,
f.feature,
f.adopters,
w.weekly_active,
round(100.0 * f.adopters / nullif(w.weekly_active, 0), 1)
as adoption_rate_pct
from feature_usage f
join wau w on w.week = f.week
order by f.week desc, f.feature;If a feature reaches 30% adoption in four weeks, you built something people need. If it stays below 5% after two months, either the feature lacks discoverability or it solves a problem nobody has. Both ask for a decision before you invest more.
9. Support Ticket Volume by Account Tier
Support tickets spike before churn. They also spike when you ship a confusing feature or break the checkout flow. Group ticket volume by account tier and track the trend. An Enterprise tier with zero tickets is not a healthy account. It might be an account that already decided to leave.
select
s.plan_tier,
date_trunc('week', t.created_at) as ticket_week,
count(distinct t.id) as ticket_count,
count(distinct t.user_id) as accounts_with_tickets,
round(
count(distinct t.id) * 1.0
/ count(distinct t.user_id),
1
) as tickets_per_account,
string_agg(distinct t.category, ', ' order by t.category)
as categories
from support_tickets t
join subscriptions s on s.user_id = t.user_id
and s.status in ('active', 'past_due')
where t.created_at >= current_date - interval '90 days'
group by 1, 2
order by ticket_week desc, plan_tier;Watch the ratio of billing-related tickets to total tickets. When it climbs, your checkout or invoice system has a problem. When it climbs inside a specific tier, that tier’s pricing likely reads as opaque to your users.
10. Pipeline Velocity
Time from signup to first paid invoice measures how fast your product delivers value. Long pipelines mean onboarding friction. Short pipelines mean the product sells itself. Measure the median, not the average, because one enterprise deal that took 90 days skews the mean.
with funnel as (
select
u.id as user_id,
u.signed_up_at,
min(case when e.event_name = 'onboarding_complete'
then e.event_time end) as activated_at,
min(case when s.status = 'active'
then s.start_date end) as paid_at
from users u
left join events e on e.user_id = u.id
left join subscriptions s on s.user_id = u.id
where u.signed_up_at >= current_date - interval '180 days'
group by 1, 2
),
stages as (
select
user_id,
signed_up_at,
activated_at,
extract(epoch from (activated_at - signed_up_at)) / 3600
as signup_to_activation_hours,
paid_at,
extract(epoch from (paid_at - activated_at)) / 3600
as activation_to_paid_hours,
case when paid_at is not null then 1 else 0 end as converted
from funnel
)
select
percentile_cont(0.5) within group (
order by signup_to_activation_hours
) as median_hours_to_activate,
percentile_cont(0.5) within group (
order by activation_to_paid_hours
) filter (where converted = 1)
as median_hours_to_paid,
count(*) as total_signups,
sum(converted) as paid,
round(100.0 * sum(converted) / count(*), 1) as overall_conversion
from stages;Pipeline velocity degrades silently. A new auth requirement or a mandatory integration step adds hours to activation. Track the median weekly and investigate any week where it climbs more than 10%.
The Python Pipeline That Feeds It
The SQL queries assume clean, event-level data. The Python below shows the extract-load pattern that produces it. It reads raw events from your application database, denormalizes into analytics tables, and writes to PostgreSQL. The whole thing runs on a cron or a scheduled Cloudflare Worker.
import psycopg2
from datetime import datetime, timedelta
def build_daily_rollups(db_url: str, target_date: str = None):
"""Denormalize raw events into daily aggregate tables."""
if target_date is None:
target_date = (
datetime.utcnow() - timedelta(days=1)
).strftime("%Y-%m-%d")
conn = psycopg2.connect(db_url)
with conn, conn.cursor() as cur:
cur.execute("""
insert into analytics.daily_active_users
(day, user_id, session_count, total_duration_s, plan_tier)
select
date_trunc('day', event_time) as day,
user_id,
count(distinct session_id),
extract(epoch from
max(event_time) - min(event_time)),
u.plan_tier
from events e
join users u on u.id = e.user_id
where date_trunc('day', event_time) = %s
and event_name = 'session_start'
group by 1, 2, u.plan_tier
on conflict (day, user_id) do update
set session_count = excluded.session_count,
total_duration_s = excluded.total_duration_s;
""", (target_date,))
cur.execute("""
insert into analytics.daily_mrr
(day, plan_tier, account_count, total_mrr)
select
%s,
plan_tier,
count(distinct user_id),
sum(mrr_amount)
from subscriptions
where status in ('active', 'past_due')
and start_date <= %s
and (ended_at is null or ended_at > %s)
group by plan_tier
on conflict (day, plan_tier) do update
set account_count = excluded.account_count,
total_mrr = excluded.total_mrr;
""", (target_date, target_date, target_date))
conn.close()Hooking It Up to Metabase or Grafana
You have the SQL. Now you need the surface.
Metabase connects to PostgreSQL natively. Paste each query into a Metabase SQL question, set the refresh schedule to hourly, and pin all ten to a collection. Metabase caches results so dashboards load in under a second even on large event tables.
Grafana needs the PostgreSQL data source plugin. Create a dashboard, add a panel for each metric, and paste the query. Grafana’s time-range picker passes $__timeFilter(event_time) as a macro, so wrap your where clauses accordingly. Set auto-refresh to 60 seconds if your pipeline writes near-real-time.
We compared both tools in detail at Metabase vs Grafana vs Superset. The short version: Metabase for SQL-first teams who want speed. Grafana for teams that already run Grafana for infra monitoring and want a single pane.
The Data Architecture That Makes This Work
A dashboard of live SaaS metrics needs a pipeline that runs without your attention. We wrote about the patterns at Real-Time Data Pipelines. The key decision: materialize daily aggregates in a separate analytics schema. Query raw event tables on every dashboard load and you pay in latency. Pre-compute nightly and your dashboards stay fast regardless of event volume.
PostgreSQL handles every query in this post without extensions. Partition your events table by month and index on (event_name, event_time) and the queries stay under 500ms on tables with tens of millions of rows. More on that at PostgreSQL for BI.
What Your Dashboard Should Tell You in 30 Seconds
Open the dashboard. Scan the top row. You see:
- DAU direction (up or down, compared to same day last week)
- MRR trend (are you at 90% of plan this month?)
- Churn rate (did it cross 5%?)
- At-risk accounts (a count, clickable)
If all four signals read green, close the dashboard and build. If one reads red, drill into the metric and find the account or the feature. A dashboard that demands 30 minutes of interpretation is a report, not a tool.
The SaaS Retention Pulse dashboard we built follows this exact pattern. Synthetic data flows through Docker, Python, and SQLite into Grafana. Every panel refreshes every few seconds. We deploy the same architecture for live production systems with PostgreSQL replacing SQLite and Airbyte or Fivetran replacing the synthetic generator. The queries are identical.
Build these ten. Ship them. Your team opens the dashboard every morning and knows within seconds whether the product gained or lost health overnight. That is the only benchmark that matters.