Plan metrics

1-- core.chartmogul.plan_metrics
2-- ChartMogul's key metrics (MRR, ARR, ARPA, ASP, customers, churn rates, LTV)
3-- per plan per day, as ChartMogul computes them.
4-- Grain: day x plan. Values are as at the end of the day.
5--
6-- Amounts are in major units of the ChartMogul account currency. Churn rates are
7-- percentages as ChartMogul reports them for each daily interval.
8--
9-- Roll-ups: mrr and arr add up across plans. customers does not (a customer on
10-- two plans appears under both), and neither do arpa, asp, ltv or the churn
11-- rates. For an account-wide ARPA, divide summed MRR by a distinct customer count
12-- rather than averaging this column.
13--
14-- Freshness: the stream re-fetches from the day of the previous sync onwards, so
15-- older days are not refreshed if ChartMogul later restates them (for example
16-- after a billing-system re-import). A full resync brings them back in line.
17
18SELECT
19    m.metric_date,
20    m.plan_uuid,
21    COALESCE(p.plan_name, m.plan_name)               AS plan_name,
22    COALESCE(p.plan_external_id, m.plan_external_id) AS plan_external_id,
23    p.interval_count,
24    p.interval_unit,
25    m.mrr,
26    m.arr,
27    m.customers,
28    m.arpa,
29    m.asp,
30    m.ltv,
31    m.customer_churn_rate_pct,
32    m.mrr_churn_rate_pct,
33    -- Month-end flag: the last synced day of each calendar month, for monthly
34    -- reporting without a separate model.
35    m.metric_date = MAX(m.metric_date) OVER (
36        PARTITION BY m.plan_uuid, DATE_TRUNC(m.metric_date, MONTH)
37    )                                                AS is_last_day_of_month
38FROM {{staging.chartmogul.all_metrics}} m
39-- One row per plan_uuid in staging, so no fan-out.
40LEFT JOIN {{staging.chartmogul.plans}} p USING (plan_uuid)