MRR 추적 + 성장 대시보드

MRR    Monthly Recurring Revenue (월 반복 수익)
ARR    Annual Recurring Revenue (= MRR × 12)
Churn  월 이탈률 (Pro → Free / 해지)
NRR    Net Revenue Retention (확장 - 이탈)
LTV    Lifetime Value (LTV = ARPU / Churn)
CAC    Customer Acquisition Cost
LTV/CAC = 3+ (건강한 SaaS)
ARPU   Average Revenue Per User

create or replace view mrr_summary as
select
  count(*) filter (where plan = 'pro') as pro_count,
  count(*) filter (where plan = 'team') as team_count,
  (count(*) filter (where plan = 'pro') * 19) +
  (count(*) filter (where plan = 'team') * 49) as mrr_dollars
from subscriptions
where status = 'active';

create table mrr_snapshots (
  date date primary key,
  mrr int not null,
  arr int generated always as (mrr * 12) stored,
  active_subs int,
  new_subs int,
  churned_subs int,
  expansion_mrr int,
  contraction_mrr int
);

-- 매일 자정 cron
insert into mrr_snapshots (date, mrr, active_subs)
select current_date, mrr_dollars, pro_count + team_count
from mrr_summary
on conflict (date) do update set
  mrr = excluded.mrr,
  active_subs = excluded.active_subs;

-- 월 Churn Rate
with monthly as (
  select
    date_trunc('month', current_date) as month,
    (select count(*) from subscriptions where status = 'active' and current_period_start < date_trunc('month', current_date)) as start_count,
    (select count(*) from subscriptions where status = 'canceled' and updated_at >= date_trunc('month', current_date)) as churned
)
select churned::float / nullif(start_count, 0) * 100 as churn_pct from monthly;

// LTV = ARPU / Monthly Churn
const arpu = mrr / activeSubscriptions
const churnRate = churnedSubs / activeSubsAtStart  // 0.05 = 5%
const ltv = arpu / churnRate

// 예: ARPU $25, Churn 5% → LTV $500

// CAC = (마케팅 + 영업 비용) / 신규 유료 사용자
const monthlyMarketing = 500  // PH·광고·콘텐츠
const newPaidUsers = 50
const cac = monthlyMarketing / newPaidUsers  // $10

// LTV/CAC = $500 / $10 = 50 (매우 좋음, 보통 3+)

-- 가입 월별 retention
select
  date_trunc('month', u.created_at) as cohort,
  count(distinct u.id) as cohort_size,
  count(distinct u.id) filter (where exists(
    select 1 from generations g
    where g.user_id = u.id
    and g.created_at >= now() - interval '7 days'
  )) as active_d7,
  count(distinct u.id) filter (where exists(
    select 1 from generations g
    where g.user_id = u.id
    and g.created_at >= now() - interval '30 days'
  )) as active_d30
from users u
group by 1
order by 1 desc;

// src/app/admin/page.tsx
import { createServiceClient } from '@/lib/supabase/service'

export default async function AdminPage() {
  const supabase = createServiceClient()

  const { data: mrr } = await supabase.from('mrr_summary').select().single()
  const { data: snapshots } = await supabase
    .from('mrr_snapshots')
    .select()
    .gte('date', new Date(Date.now() - 90 * 86400e3).toISOString().split('T')[0])
    .order('date')

  return (
    <div className="p-6">
      <h1 className="text-2xl font-bold">MRR Dashboard</h1>

      <div className="grid grid-cols-4 gap-4 mt-6">
        <Stat label="MRR" value={`$${mrr.mrr_dollars}`} />
        <Stat label="ARR" value={`$${mrr.mrr_dollars * 12}`} />
        <Stat label="Active" value={mrr.pro_count + mrr.team_count} />
        <Stat label="Pro" value={mrr.pro_count} />
      </div>

      <MRRChart data={snapshots} />
      <CohortTable />
    </div>
  )
}

// 매일 자정 → Slack 보고
async function dailyReport() {
  const today = new Date().toISOString().split('T')[0]
  const supabase = createServiceClient()

  const { data: snapshot } = await supabase
    .from('mrr_snapshots')
    .select()
    .eq('date', today)
    .single()

  await fetch(process.env.SLACK_WEBHOOK!, {
    method: 'POST',
    body: JSON.stringify({
      text: `📊 일일 MRR 보고
MRR: $${snapshot.mrr}
신규: ${snapshot.new_subs}
이탈: ${snapshot.churned_subs}`,
    }),
  })
}

[Month 1-3] $0~500 MRR
- 검증·MVP
- D30 retention 30%+

[Month 4-12] $500~5K MRR
- PMF 확인
- Churn < 5%/mo

[Month 13-24] $5K~50K MRR
- 가격 인상·B2B
- Net Revenue Retention 100%+