ContextFlo

Metric Formulas

Common metric formulas and SQL patterns

Last updated: 1/17/2025

Ready-to-use SQL formulas for common business metrics.

Revenue Metrics

Monthly Recurring Revenue (MRR)

SELECT SUM(amount) as value
FROM subscriptions
WHERE status = 'active'
  AND billing_period = 'monthly'

Annual Recurring Revenue (ARR)

SELECT SUM(
  CASE
    WHEN billing_period = 'monthly' THEN amount * 12
    WHEN billing_period = 'annual' THEN amount
    ELSE 0
  END
) as value
FROM subscriptions
WHERE status = 'active'

Average Revenue Per User (ARPU)

SELECT
  SUM(amount) / NULLIF(COUNT(DISTINCT user_id), 0) as value
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'

Growth Metrics

User Growth Rate

WITH current_month AS (
  SELECT COUNT(*) as count
  FROM users
  WHERE created_at >= DATE_TRUNC('month', NOW())
),
last_month AS (
  SELECT COUNT(*) as count
  FROM users
  WHERE created_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
    AND created_at < DATE_TRUNC('month', NOW())
)
SELECT
  ((c.count - l.count) * 100.0 / NULLIF(l.count, 0)) as value
FROM current_month c, last_month l

Churn Rate

SELECT
  (COUNT(*) FILTER (WHERE churned_at IS NOT NULL) * 100.0 /
   NULLIF(COUNT(*), 0)) as value
FROM subscriptions
WHERE created_at < NOW() - INTERVAL '30 days'

Net Revenue Retention (NRR)

WITH cohort AS (
  SELECT SUM(amount) as start_mrr
  FROM subscriptions
  WHERE created_at >= NOW() - INTERVAL '1 year'
    AND created_at < NOW() - INTERVAL '11 months'
),
current AS (
  SELECT SUM(amount) as current_mrr
  FROM subscriptions
  WHERE user_id IN (
    SELECT user_id FROM subscriptions
    WHERE created_at >= NOW() - INTERVAL '1 year'
      AND created_at < NOW() - INTERVAL '11 months'
  )
  AND status = 'active'
)
SELECT
  (c.current_mrr * 100.0 / NULLIF(co.start_mrr, 0)) as value
FROM cohort co, current c

Engagement Metrics

Daily Active Users (DAU)

SELECT COUNT(DISTINCT user_id) as value
FROM events
WHERE created_at >= NOW() - INTERVAL '24 hours'

Monthly Active Users (MAU)

SELECT COUNT(DISTINCT user_id) as value
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'

DAU/MAU Ratio (Stickiness)

WITH dau AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM events
  WHERE created_at >= NOW() - INTERVAL '24 hours'
),
mau AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM events
  WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT
  (d.count * 100.0 / NULLIF(m.count, 0)) as value
FROM dau d, mau m

Average Session Duration

SELECT AVG(duration_seconds) / 60.0 as value  -- Convert to minutes
FROM sessions
WHERE created_at >= NOW() - INTERVAL '7 days'

Sales Metrics

Conversion Rate

WITH visitors AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM page_views
  WHERE created_at >= NOW() - INTERVAL '30 days'
),
conversions AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT
  (c.count * 100.0 / NULLIF(v.count, 0)) as value
FROM visitors v, conversions c

Average Deal Size

SELECT AVG(amount) as value
FROM deals
WHERE status = 'closed_won'
  AND closed_at >= NOW() - INTERVAL '90 days'

Sales Cycle Length

SELECT AVG(
  EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400
) as value  -- Days
FROM deals
WHERE status = 'closed_won'
  AND closed_at >= NOW() - INTERVAL '90 days'

Product Metrics

Feature Adoption Rate

WITH total_users AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM users
  WHERE created_at < NOW() - INTERVAL '7 days'  -- Active users
),
feature_users AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM events
  WHERE event_name = 'feature_used'
    AND created_at >= NOW() - INTERVAL '30 days'
)
SELECT
  (f.count * 100.0 / NULLIF(t.count, 0)) as value
FROM total_users t, feature_users f

Time to Value

SELECT AVG(
  EXTRACT(EPOCH FROM (first_value_at - signed_up_at)) / 3600
) as value  -- Hours
FROM users
WHERE signed_up_at >= NOW() - INTERVAL '30 days'
  AND first_value_at IS NOT NULL

Customer Metrics

Customer Lifetime Value (LTV)

WITH customer_revenue AS (
  SELECT
    user_id,
    SUM(amount) as total_revenue,
    MIN(created_at) as first_purchase,
    MAX(created_at) as last_purchase
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
),
lifetime AS (
  SELECT
    AVG(EXTRACT(EPOCH FROM (last_purchase - first_purchase)) / 86400) as avg_lifetime_days,
    AVG(total_revenue) as avg_revenue
  FROM customer_revenue
)
SELECT avg_revenue as value
FROM lifetime

Customer Acquisition Cost (CAC)

WITH marketing_spend AS (
  SELECT SUM(amount) as total_spend
  FROM expenses
  WHERE category = 'marketing'
    AND created_at >= NOW() - INTERVAL '30 days'
),
new_customers AS (
  SELECT COUNT(*) as count
  FROM users
  WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT
  (m.total_spend / NULLIF(n.count, 0)) as value
FROM marketing_spend m, new_customers n

System Metrics

API Error Rate

SELECT
  (COUNT(*) FILTER (WHERE status >= 400) * 100.0 /
   NULLIF(COUNT(*), 0)) as value
FROM api_requests
WHERE created_at >= NOW() - INTERVAL '1 hour'

Average Response Time

SELECT AVG(response_time_ms) as value
FROM api_requests
WHERE created_at >= NOW() - INTERVAL '1 hour'

System Uptime

WITH total_time AS (
  SELECT EXTRACT(EPOCH FROM (NOW() - NOW() + INTERVAL '24 hours')) as seconds
),
downtime AS (
  SELECT SUM(EXTRACT(EPOCH FROM (ended_at - started_at))) as seconds
  FROM incidents
  WHERE started_at >= NOW() - INTERVAL '24 hours'
)
SELECT
  ((t.seconds - COALESCE(d.seconds, 0)) * 100.0 / t.seconds) as value
FROM total_time t, downtime d

Advanced Patterns

Cohort Analysis

WITH cohort AS (
  SELECT
    DATE_TRUNC('month', created_at) as cohort_month,
    user_id
  FROM users
),
retention AS (
  SELECT
    c.cohort_month,
    COUNT(DISTINCT CASE
      WHEN e.created_at >= c.cohort_month + INTERVAL '1 month'
        AND e.created_at < c.cohort_month + INTERVAL '2 months'
      THEN c.user_id
    END) * 100.0 / NULLIF(COUNT(DISTINCT c.user_id), 0) as month_1_retention
  FROM cohort c
  LEFT JOIN events e ON c.user_id = e.user_id
  GROUP BY c.cohort_month
)
SELECT AVG(month_1_retention) as value
FROM retention
WHERE cohort_month >= NOW() - INTERVAL '6 months'

Percentile Calculation

SELECT
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) as value
FROM api_requests
WHERE created_at >= NOW() - INTERVAL '1 hour'

Tips for Writing Formulas

1. Always Handle NULL

-- Good
SELECT COUNT(*) / NULLIF(total, 0) as value

-- Bad
SELECT COUNT(*) / total as value  -- Can cause division by zero

2. Use Time Ranges

-- Good
WHERE created_at >= NOW() - INTERVAL '30 days'

-- Bad
-- No time filter (scans entire table)

3. Filter Early

-- Good
SELECT COUNT(*)
FROM users
WHERE status = 'active'
  AND created_at >= NOW() - INTERVAL '30 days'

-- Bad
SELECT COUNT(*)
FROM (SELECT * FROM users) u  -- Fetches all rows first
WHERE status = 'active'

Next Steps