Metrics and Dashboard Design Questions

-- Current month MRR (context: month selected or current date context)
Current Month MRR =
VAR CurrentMonthStart = STARTOFMONTH(SELECTEDVALUE(DimDate[Date]))
RETURN
COALESCE(
    CALCULATE(
        SUM(Fact_Revenue[MRR]),
        DATESINPERIOD(DimDate[Date], CurrentMonthStart, 1, MONTH)
    ),
    0
)

-- Same month last year MRR
Prior Year Same Month MRR =
VAR LastYearPeriod = DATEADD(DimDate[Date], -1, YEAR)
RETURN
COALESCE(
    CALCULATE(
        SUM(Fact_Revenue[MRR]),
        LastYearPeriod
    ),
    0
)

-- YoY MRR Growth (%) for the month
YoY MRR Growth % =
VAR Curr = [Current Month MRR]
VAR Prior = [Prior Year Same Month MRR]
RETURN
IF(
    Prior = 0,
    BLANK(),                 -- or return 1 (100%) or 0 depending on business rule
    DIVIDE(Curr - Prior, ABS(Prior), 0)
)

-- Parameters
WITH params AS (
  SELECT 0.10::numeric AS annual_discount_rate  -- 10% annual
),

-- 1) Cohort: account signup month (first day of month)
cohorts AS (
  SELECT
    a.account_id,
    date_trunc('month', a.signup_date)::date AS cohort_month
  FROM accounts a
  WHERE a.status = 'active' OR a.status IS NOT NULL
),

-- 2) Normalize transactions to month and join cohort
tx_months AS (
  SELECT
    c.cohort_month,
    t.account_id,
    date_trunc('month', t.transaction_date)::date AS tx_month,
    SUM(t.amount) AS net_amount -- net captures expansions / contractions
  FROM transactions t
  JOIN cohorts c USING (account_id)
  GROUP BY 1,2,3
),

-- 3) Compute months_since_signup (integer: 0 = signup month)
cohort_monthly AS (
  SELECT
    cohort_month,
    tx_month,
    EXTRACT(YEAR FROM age(tx_month, cohort_month)) * 12
      + (date_part('month', tx_month) - date_part('month', cohort_month))::int
      AS months_since_signup,
    SUM(net_amount) AS revenue
  FROM tx_months
  GROUP BY 1,2,3
),

-- 4) Prepare discount factor per months_since_signup
discount AS (
  SELECT
    p.annual_discount_rate,
    (POWER(1 + p.annual_discount_rate, 1.0/12) - 1)::numeric AS monthly_rate
  FROM params p
),

-- 5) Discount revenue and aggregate per cohort-month and compute cumulative LTV
discounted AS (
  SELECT
    cm.cohort_month,
    cm.months_since_signup,
    cm.revenue,
    d.monthly_rate,
    -- discount factor = 1 / (1 + monthly_rate) ^ months_since_signup
    (cm.revenue / POWER(1 + d.monthly_rate, cm.months_since_signup))::numeric(18,4) AS discounted_revenue
  FROM cohort_monthly cm
  CROSS JOIN discount d
),

-- 6) Summarize: cohort x month rows and cumulative discounted LTV
cohort_ltv AS (
  SELECT
    cohort_month,
    months_since_signup,
    SUM(revenue) AS gross_revenue,
    SUM(discounted_revenue) AS discounted_revenue,
    SUM(SUM(discounted_revenue)) OVER (PARTITION BY cohort_month ORDER BY months_since_signup) AS cumulative_discounted_ltv
  FROM discounted
  GROUP BY cohort_month, months_since_signup
)

SELECT
  cohort_month,
  months_since_signup,
  gross_revenue,
  discounted_revenue,
  cumulative_discounted_ltv
FROM cohort_ltv
ORDER BY cohort_month, months_since_signup;

CREATE MATERIALIZED VIEW mv_daily_metrics AS
SELECT date, acct_id, SUM(revenue) revenue FROM raw_events GROUP BY date, acct_id;

WITH cohorts AS (
  SELECT user_id, date_trunc('week', signup_at) AS cohort_week FROM users
),
revenue AS (
  SELECT user_id, date_trunc('week', paid_at) AS week, SUM(amount) AS revenue
  FROM invoices WHERE status='paid' GROUP BY 1,2
)
SELECT c.cohort_week, r.week, SUM(r.revenue) AS revenue, COUNT(DISTINCT r.user_id) AS paying_users
FROM cohorts c
LEFT JOIN revenue r ON c.user_id = r.user_id
GROUP BY 1,2 ORDER BY 1,2;