ing by 100.0 forces floating-point arithmetic, preventing integer truncation. If your schema supports per-seat billing, aggregate at the account_id level first to prevent overcounting.

2. Churn Rate

Churn measures customer attrition velocity. The calculation requires strict cohort alignment: the denominator must reflect the active base at the start of the period, while the numerator captures terminations within that same window.

WITH period_start AS (
  SELECT COUNT(DISTINCT account_id) AS base_count
  FROM billing_plans
  WHERE state = 'active'
    AND activated_at < DATE_TRUNC('month', CURRENT_DATE)
),
period_losses AS (
  SELECT COUNT(DISTINCT account_id) AS lost_count
  FROM billing_plans
  WHERE state = 'terminated'
    AND terminated_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND terminated_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
)
SELECT
  ROUND(ps.base_count * 100.0 / NULLIF(ps.base_count, 0), 2) AS start_pct,
  ROUND(pl.lost_count * 100.0 / NULLIF(ps.base_count, 0), 2) AS churn_pct
FROM period_start ps, period_losses pl;

Rationale: NULLIF() guards against division-by-zero during low-volume months. The INTERVAL syntax ensures precise boundary matching. B2B SaaS benchmarks typically target sub-3% monthly churn; sustained rates above 5% indicate pricing misalignment or product-market fit degradation.

3. Stickiness Ratio (DAU / MAU)

Stickiness measures habit formation. The ratio compares daily active accounts against monthly active accounts. The architectural choice is to compute both metrics in parallel CTEs rather than nested subqueries, improving readability and execution plan stability.

WITH daily_active AS (
  SELECT COUNT(DISTINCT account_id) AS dau
  FROM user_actions
  WHERE triggered_at::date = CURRENT_DATE
),
monthly_active AS (
  SELECT COUNT(DISTINCT account_id) AS mau
  FROM user_actions
  WHERE triggered_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  ROUND(da.dau * 100.0 / NULLIF(ma.mau, 0), 1) AS stickiness_ratio
FROM daily_active da, monthly_active ma;

Rationale: COUNT(DISTINCT account_id) is mandatory. Counting raw rows inflates engagement metrics when power users generate high event volumes. Healthy B2B products typically maintain 25–40% stickiness; consumer applications often exceed 50%.

4. Average Revenue Per User (ARPU)

ARPU reveals pricing tier effectiveness. Instead of a single aggregate, segmenting by tier exposes which customer segments drive margin.

SELECT
  a.tier,
  COUNT(DISTINCT bp.account_id) AS active_subscribers,
  ROUND(AVG(bp.price_cents) / 100.0, 2) AS avg_revenue
FROM billing_plans bp
JOIN accounts a ON a.id = bp.account_id
WHERE bp.state = 'active'
GROUP BY a.tier
ORDER BY avg_revenue DESC;

Rationale: Joining accounts to billing_plans enables tier-based segmentation. AVG() calculates mean revenue per active subscriber, highlighting whether premium tiers justify their cost or if mid-tier plans carry disproportionate support overhead.

5. Trial-to-Paid Conversion

Conversion tracking fails when cohorts are misaligned. Trials initiated this month rarely convert within the same window. The solution is a lagged cohort analysis.

WITH trial_cohort AS (
  SELECT COUNT(DISTINCT account_id) AS initiated
  FROM free_periods
  WHERE opened_at >= CURRENT_DATE - INTERVAL '60 days'
    AND opened_at < CURRENT_DATE - INTERVAL '30 days'
),
converted_cohort AS (
  SELECT COUNT(DISTINCT account_id) AS paid
  FROM free_periods
  WHERE opened_at >= CURRENT_DATE - INTERVAL '60 days'
    AND opened_at < CURRENT_DATE - INTERVAL '30 days'
    AND converted_at IS NOT NULL
)
SELECT
  ROUND(cc.paid * 100.0 / NULLIF(tc.initiated, 0), 1) AS conversion_rate
FROM trial_cohort tc, converted_cohort cc;

Rationale: The 30–60 day lookback window accounts for evaluation cycles, budget approvals, and onboarding friction. Comparing same-month trials to same-month conversions artificially depresses conversion rates and masks onboarding bottlenecks.

6. Month-over-Month Revenue Growth

Growth velocity requires sequential comparison. Window functions eliminate the need for self-joins or application-side calculations.

WITH monthly_payouts AS (
  SELECT
    DATE_TRUNC('month', processed_at) AS payout_month,
    SUM(value_cents) / 100.0 AS gross_revenue
  FROM transactions
  WHERE outcome = 'completed'
  GROUP BY 1
)
SELECT
  payout_month,
  gross_revenue,
  LAG(gross_revenue) OVER (ORDER BY payout_month) AS prior_month,
  ROUND(
    (gross_revenue - LAG(gross_revenue) OVER (ORDER BY payout_month)) * 100.0
    / NULLIF(LAG(gross_revenue) OVER (ORDER BY payout_month), 0),
    1
  ) AS mom_growth
FROM monthly_payouts
ORDER BY payout_month DESC
LIMIT 6;

Rationale: LAG() accesses the previous row without a self-join, reducing I/O and simplifying the execution plan. Filtering outcome = 'completed' ensures failed or pending transactions do not inflate growth metrics.

7. Feature Adoption Rate

Adoption tracking measures whether new capabilities drive engagement. The query calculates the percentage of monthly active users who triggered specific actions.

WITH active_base AS (
  SELECT COUNT(DISTINCT account_id) AS total_active
  FROM user_actions
  WHERE triggered_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  ua.action_type,
  COUNT(DISTINCT ua.account_id) AS adopters,
  ROUND(COUNT(DISTINCT ua.account_id) * 100.0 / NULLIF(ab.total_active, 0), 1) AS adoption_pct
FROM user_actions ua
CROSS JOIN active_base ab
WHERE ua.triggered_at >= CURRENT_DATE - INTERVAL '30 days'
  AND ua.action_type IN ('generate_report', 'add_team_member', 'build_dashboard', 'sync_external_db')
GROUP BY ua.action_type
ORDER BY adoption_pct DESC;

Rationale: CROSS JOIN attaches the denominator to every row, avoiding repeated subquery execution. Filtering action_type ensures only intentional product interactions are measured, excluding passive page loads or health checks.

Pitfall Guide

1. Timezone Truncation Drift

Explanation: CURRENT_DATE and DATE_TRUNC() evaluate against the database server's timezone. If your infrastructure spans multiple regions or your users operate in different zones, date boundaries shift, causing events to bleed into adjacent periods. Fix: Store all timestamps in UTC. Explicitly cast or convert at query time: triggered_at AT TIME ZONE 'UTC'. Never rely on implicit server timezone settings for business reporting.

2. Event Inflation vs User Counting

Explanation: Using COUNT(*) or COUNT(id) measures transaction volume, not user engagement. A single power user generating 500 actions will artificially inflate DAU, MAU, and adoption metrics. Fix: Always use COUNT(DISTINCT account_id) for engagement and adoption queries. Reserve raw counts for volume-based metrics like API throughput or log generation.

3. Internal/Test Data Contamination

Explanation: Seed accounts, QA environments, and internal team logins pollute every metric. Test subscriptions inflate MRR, while QA events distort stickiness and adoption rates. Fix: Add an is_internal boolean column to accounts and filter WHERE is_internal = FALSE. Alternatively, exclude known domains: AND email NOT LIKE '%@yourcompany.com'. Route test traffic to a separate schema or database instance.

4. Payment Status Blindness

Explanation: Calculating revenue from the transactions table without filtering outcome includes pending, failed, and refunded payments. This creates phantom revenue that disappears during reconciliation. Fix: Always apply WHERE outcome = 'completed' or WHERE status = 'succeeded'. For financial reporting, join against a refunds table to calculate net revenue rather than gross.

5. Cohort Misalignment in Trials

Explanation: Comparing trials started this month to conversions completed this month ignores evaluation latency. Most B2B trials require 14–30 days for team onboarding and budget approval. Fix: Implement a lagged cohort window (30–60 days). Track conversion rate per cohort start date rather than calendar month. This reveals whether onboarding improvements actually move the needle.

6. Multi-Seat Double Counting

Explanation: If your billing model supports per-seat or per-project subscriptions, a single account may hold multiple active billing_plans. Summing price_cents across all plans overstates MRR and ARPU. Fix: Aggregate at the account level first using GROUP BY account_id, then sum the maximum or total per account. Alternatively, maintain a billing_summary table that normalizes multi-seat pricing before analytical queries run.

Production Bundle

Action Checklist

• Audit timestamp storage: Verify all *_at columns use UTC and lack implicit timezone conversions.
• Implement internal account filtering: Add is_internal flag or domain exclusion to every analytical query.
• Standardize distinct counting: Replace all COUNT(*) engagement metrics with COUNT(DISTINCT account_id).
• Validate payment outcomes: Ensure revenue queries filter outcome = 'completed' and exclude pending/failed states.
• Align trial cohorts: Replace same-month conversion tracking with 30–60 day lagged cohort analysis.
• Normalize multi-seat billing: Aggregate subscription values at the account level before summing MRR.
• Schedule read-replica routing: Direct analytical queries to a read replica to prevent blocking OLTP workloads.
• Materialize heavy aggregations: Convert weekly dashboard queries into materialized views with nightly refreshes.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Early-stage startup (<10k users)	Direct SQL on primary database	Low query volume; simplicity outweighs optimization needs	$0 incremental
Scaling B2B SaaS (10k–100k users)	Read replica + CTE-based queries	Isolates analytical load from transactional writes	+15–30% infra cost
Enterprise compliance requirements	Materialized views + scheduled refresh	Ensures deterministic snapshots for audit trails	+10% storage, reduced compute spikes
High-frequency event tracking	Pre-aggregated summary tables	Prevents full table scans on user_actions	+5% write overhead, -60% read latency

Configuration Template

Copy this template into your analytics schema. It establishes a reusable foundation for metric computation with built-in safeguards.

-- Analytics Schema Setup
CREATE SCHEMA IF NOT EXISTS analytics;

-- Materialized View: Daily Engagement Snapshot
CREATE MATERIALIZED VIEW analytics.daily_engagement AS
SELECT
  triggered_at::date AS event_date,
  COUNT(DISTINCT account_id) AS dau,
  COUNT(DISTINCT CASE WHEN triggered_at >= CURRENT_DATE - INTERVAL '30 days' THEN account_id END) AS mau
FROM user_actions
WHERE triggered_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1;

-- Refresh Strategy (run via cron or scheduler)
-- REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.daily_engagement;

-- Parameterized Churn Query Template
WITH period_bounds AS (
  SELECT
    DATE_TRUNC('month', CURRENT_DATE) AS period_start,
    DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' AS period_end
),
base_cohort AS (
  SELECT COUNT(DISTINCT account_id) AS active_base
  FROM billing_plans
  WHERE state = 'active'
    AND activated_at < (SELECT period_start FROM period_bounds)
),
loss_cohort AS (
  SELECT COUNT(DISTINCT account_id) AS terminated_count
  FROM billing_plans
  WHERE state = 'terminated'
    AND terminated_at >= (SELECT period_start FROM period_bounds)
    AND terminated_at < (SELECT period_end FROM period_bounds)
)
SELECT
  ROUND(lc.terminated_count * 100.0 / NULLIF(bc.active_base, 0), 2) AS monthly_churn_pct
FROM base_cohort bc, loss_cohort lc;

Quick Start Guide

1. Verify Schema Contract: Ensure your accounts, billing_plans, transactions, user_actions, and free_periods tables exist with the columns outlined in the Core Solution. Adjust column names to match your codebase.
2. Route Analytical Traffic: Configure your BI tool or reporting script to connect to a read replica. If running on a single instance, schedule queries during off-peak hours to avoid blocking user transactions.
3. Execute Baseline Queries: Run the MRR, Churn, and Stickiness queries against your current data. Validate results against known financial reports or internal dashboards to confirm alignment.
4. Automate Refresh: Wrap heavy queries in materialized views or scheduled jobs. Set up nightly refreshes for weekly dashboards and hourly refreshes for real-time monitoring.
5. Monitor Query Plans: Use EXPLAIN ANALYZE on new metrics. Add indexes on account_id, state, and triggered_at where sequential scans appear. Tune until execution time stays under 2 seconds for dashboard loads.